CaN (Computer & Network) Tips

Excel(엑셀)에 입력된 데이터를 보면 아래 그림과 같이 같은 종류지만 여러 열에 걸쳐 왼쪽에서 오른쪽 방향으로 겹쳐진 경우가 있다.

보통 데이터 분석 등의 작업을 위해 이를 하나의 열로 정리해야 할 때 사용할 수 있는 방법이다. 

먼저 아래 그림과 같이 클립보드 메뉴의 확장 버튼을 클릭해 클립보드 창을 나타나게 한다..

그 다음 열 하나씩을 선택해 복사한다. [Ctrl]+[C] 키를 누르거나 단축 메뉴에서 복사 메뉴를 실행한다. 복사 명령을 실행할 때마다 복사한 내용이 클립보드 창에 하나씩 쌓인다.

모든 열을 복사해 모아두었으면 한 열로 정리를 시작할 셀을 선택하고(여기에서는 A1), 클립보드 창에서 [모두 붙여넣기] 단추를 선택한다.

이렇게 하면 복사해 둔 모든 데이터가 순서대로 나타난다. PC의 성능에 따라 하나하나 붙여지는 모습이 보인다.

이런 기능을 자주 사용한다면 클립보드 창 하단의 [옵션] 메뉴를 통해 "<Ctrl+C>를 두 번 눌러 Office 클립보드 표시"와 같은 기능을 켜두면 더 손쉽게 이 기능을 사용할 수 있다. 내 경우에는 [Ctrl]+[C]를 두 번 누르는 기능을 다른 용도로 사용하기 때문에 이 메뉴를 사용하고 있지는 않다(사실 이것 때문에 위 그림에서처럼 일부 내용이 붙여넣기에서 제외되기도 한다).

기타 매크로(VBA)를 사용하거나 다른 방법으로 이 일을 할 수 있지만, 이 방법이 꽤 편리하다.

 

일반적으로 아래 그림과 같이 Excel(엑셀) 워크시트의 일정 부분에 데이터가 입력되어 있을 때 이 부분을 선택하려면 마우스를 이용하는 방법 이외에 단축키(바로 가기 키) [Ctrl]+[Shift]+[8]을 이용하게 된다.

데이터 범위 안에 있는 아무 셀이나 선택한 상태에서 이 키 조합을 사용하게 되면 데이터 영역이 자동으로 선택이 되고, 가장 왼쪽 위의 셀이 대표셀로 지정된다.

이 방법 이외에 해당 데이터 범위 내의 아무 셀이나 선택한 상태에서 [Ctrl]+[A] 키를 사용해도 해당 범위가 선택된다.

단, 이때에는 위 그림처럼 미리 선택된 셀이 대표셀로 계속 유지된다. 이 셀을 중심으로 정렬(Sorting) 작업을 할 때 등 필요한 경우에 사용하면 무척 편리하다.

이 상태에서 다시 [Ctrl]+[A] 키를 누르면 이때에는 전체 워크시트가 선택된다.

따라서 한 번에 위크시트 전체를 선택하려면 데이터가 입력되지 않은 곳에서 이 단축키를 사용하거나 행 번호와 열 번호가 만나는 곳(A의 왼쪽과 1의 위쪽)을 클릭한다.

Excel(엑셀)에서 표 형태로 작성된 데이터는 시각적 이해를 높이는 등의 이유로 차트로 전환해 사용하는 경우가 많다.

보통 차트 삽입 기능을 이용해 기본으로 만든 차트는 실무에서는 용도에 따라 여러 가지 변화를 줘서 좀 더 세련된 모양으로 바꾸거나 사용자가 원하는 스타일로 조정하게 된다. 또, 사용자에 따라서는 자신만의 글꼴이나 색상을 주로 사용해서 차트를 만들 때마다 반복적인 수정 작업을 하게 된다.

만약 자주 사용하는 차트 스타일이 정해져 있다면 이를 지정해 두고, 필요할 때마다 불러 사용할 수 있는데 과거 버전부터 있던 기능이기는 하지만, 2007, 2010 버전에서는 서식 파일 형태로 저장하기 기능을 이용하면 된다.

예를 들어, 아래와 같은 표 형태의 데이터가 있다고 한다.

이 표를 막대형 차트로 변환하면 기본 모양을 아래와 같다.

이 모양을 수정하여 아래와 같이 바꾼다.

이제 이렇게 만든 모양을 앞으로도 비슷한 데이터를 차트로 만들 때 그대로 적용하고자 한다. 그렇다면 이 차트를 선택하고, 리본 메뉴의 [차트 도구]에서 [디자인] > [서식 파일로 저장]을 차례로 선택한다.

그다음 나타나는 대화 상자에서 적당한 이름으로 이 차트 서식을 저장한다.

여기에서는 Exam.crtx로 저장했다.

이후 새로운 데이터를 만들고 기본적인 차트 삽입 기능을 사용하면 아래와 같이 새로운 차트가 나타난다.

이런 모양이 아니라 미리 저장해 둔 자주 사용하는 차트 스타일을 사용한다면, 기본 모양을 만든 다음에 모양을 변경할 필요없이 데이터 범위를 선택한다음. 리본 메뉴에서 [삽입] > [기타] > [모든 차트 종류]를 선택한다.

이후 나타나는 차트 삽입 대화 상자에서 [서식 파일]을 선택한다. 이렇게 하면 "내 서식 파일" 창에 저장해 둔 서식이 나타난다. 여러 개가 있다면 이름순으로 정렬된다.

필요한 서식을 선택하고 [확인]을 누르면 해당 스타일의 차트가 만들어지는 것을 확인할 수 있다.

일정 서식의 차트를 자주 사용한다면 아주 유용한 기능이다.

Excel(엑셀)에서 작업을 할 때 한 셀에서 데이터를 입력한 다음 [Enter]키를 누르면 해당 셀에 입력이 마감되고, 그 다음 셀이 선택된다. 이것이 일반적인 활용법이기는 하지만, 경우에 따라 한 셀에 데이터를 여러 행으로 입력해야 할 때가 있다.

이때 사용하는 방법으로 한 셀에서 줄을 바꾸는 [Alt]+[Enter] 기능이 있다.

참고: "[Excel] 한 셀에 두 줄 이상 입력하기와 한 줄로 다시 만들기", 2007. 8. 28.

위 방법은 새로운 데이터를 입력할 때에는 유용하지만, 이미 따라 입력된 데이터를 한 셀에 여러 줄로 합쳐야 할 때에는 불편하다.

예를 들어, 아래와 같이 '이름'과 '소속'으로 분리되어 있는 데이터를 '이름(소속)' 형태로 다시 배열해야 할 때가 있다.

위 그림의 A9 셀에 입력된 내용은 아래와 같다.

=A2&CHAR(10)&"("&B2&")"

여기에서 CHAR(10)이 바로 공란이나 엔터 키처럼 줄 바꿈 기호에 해당한다.

이렇게 입력하면 우선은 아래 그림과 같이 한 줄로 모든 데이터가 나타난다. 만약 Char(10) 수식이 다른 셀에 들어 있고, 이 셀을 글자 셀과 결합하는 형태로 수식을 작성했다면 바로 줄이 바뀌어 나타나지만, 한 수식에 Char(10) 함수를 같이 쓰면 이처럼 한 줄로 나타나게 된다.

이를 제대로 표시하게 위해 같은 수식이 입력된 전체 범위를 선택한 다음 단축 메뉴에서 [셀 서식]을 선택한다. [셀 서식]을 부르는 단축키는 [Ctrl]+[1]인데 외워두는 것이 좋다.

그 다음 [맞춤] 탭에서 "텍스트 조정" 항목의 '텍스트 줄 바꿈'을 선택해준다.

이렇게 하면 아래 그림과 같이 Char(10)이 입력된 부분에서 줄이 바뀌는 것을 확인할 수 있다.

다시 한 줄로 하고 싶다면, 다시 셀 서식을 이용하거나 CLEAN 함수를 쓴다.

Excel(엑셀)에 입력한 데이터는 보통 같은 성격의 것(필드)이라면 주로 위에서 아래로 한 줄로 즉, 한 열로 정리하는 것이 일반적이다. 또, 데이터를 정리하고 관리하는 것이라면 이렇게 하는 것이 맞다. 그런데 이런저런 작업을 하다보면 모양이나 인쇄 등의 이유로 한 열로 정리된 데이터를 여러 열로 나누어 나열해야 할 때가 있다.

예를 들어, 아래 그림과 같이 A열에 위에서 아래로 입력된 데이터를 그 옆의 다른 테이블(표)처럼 여러 열로 정리하는 경우이다.

이렇게 정리할 때 보통은 다음 두 가지를 먼저 결정해야 한다.

- 몇 개의 열로 정리할 것인가?

- 데이터를 가로나 세로 중 어느 방향으로 입력할 것인가?

이런 작업을 할 때에는 위에서 예로 제시한 각각의 표 형태에 따라 다음과 같은 수식을 이용해 볼 수 있다.

같은 표에는 모든 셀에 같은 수식이 들어간다. 즉, 대표 셀에 하나의 수식을 만든 다음 필요한 만큼 복사하면 된다.

수식을 보면서 열 개수에 해당하는 숫자만 필요에 따라 조정하면 되고, 표가 어느 위치에 입력되는 지에 따라 기준 셀 들의 위치만 다시 잡아주면 된다. 또, 수식에 IF 등을 집어 넣어 좀 더 정교하고 더 실용적으로 만들 수도 있다.

다른 작업도 마찬가지이지만 다른 여러 가지 방법이 있을 수도 있다. 또, 대충 이 방법을 응용하면 다양한 형태의 보고서에 활용할 수 있을 것이다.

 

Excel(엑셀)이나 Word(워드), 한/글(아래아한글), PowerPoint(파워포인트) 등의 문서 작업을 열심히 하다가 가장 황당한 경우 중 하나가 작업 다 한 파일을 저장하지 않고 닫아버리는 것이다.

멀쩡히 두 눈 뜨고 저장하거냐 말거냐 물어보고 있느데 그냥 아니오를 선택할 때가 있다. 또는, 수정한 작업이 중요하지 않아 수정하기 전 상태로 만든다고  순간적으로 잘못 판단하여 파일을 저장하지 않고 닫은 다음 정신이 드는 경우도 있다.

애플리케이션에 따라서는 자동 저장 기능이라는 것이 있어 일정 시간 키보드를 사용하지 않거나 또는 일정 시간이 흐르면 강제로 현재 상태를 저장하기도 한다.

Excel 2010 버전에서는 작업 중간에 기존 파일을 덮어 써 저장하는 것이 아니라 일정 시간 간격으로 따로 저장하는 버전 관리 기능이 있다.

혹시 실수로 저장을 하지 않고 닫은 엑셀 파일을 살려야 하는 일이 있다면 여기를 확인하면 된다.

Excel은 따로 설정을 하지 않았다면 10분 간격으로 현재 문서를 저장한다. 그리고 버전 관리 항목에 저장된 시각을 확인해 편집 내역을 확인할 수 있다. 보통 자동 저장된 파일이 없다면, Excel을 닫을 때 다음과 같은 저장 안내 화면이 나타난다.

만약 저장된 버전이 있다면 아래와 같이 화면이 바뀐다.

저장되는 간격 등의 설정은 옵션 메뉴의 저장 항목에서 관리할 수 있다.

자동으로 저장된 임시 파일들은 4일 동안 보관된 다음 삭제된다.

이 기능을 제대로 사용하기 위해서는 새 문서를 연 다음 본격적으로 작업을 하기 전에 반드시 파일 이름을 지정해 저장을 먼저 하는 것이 좋다. 그리고 작업을 자주 하는 경우라면 저장 간격도 10분보다는 짧게 지정하는 것이 좋다. 그렇다고 너무 짧으면 대용량 파일을 다루는 경우 저장할 때마다 편집 작업에 방해를 받을 수 있으므로 적당하게 지정하도록 한다.

단순하게 저장하지 않은 파일을 복구할 때보다는 작업하는 내용의 변경 내역을 체계적으로 관리하는 용도로 사용하기를 권장한다.

 

 

 

2011년 6월 27일 자로 Microsoft Office 2010의 새로운 업데이트 버전인 Microsoft Office 2010 Service Pack 1(SP1)이 나왔다. 

이 중 Excel(엑셀)에서 고쳐진 내용은 아래와 같다. 이렇게 많은 문제를 안고 써온 것이다.

• Data is formatted incorrectly when conditional formatting (조건부 서식) rules that are based on standard deviation (specifically below 2 or 3 standard deviation) are applied.
• Pasting a lot of rows takes a very long time when the row height must be changed.
• Excel may stop responding when array formulas that are dependent on or are included in a circular reference (순환 참조) are calculated.
• Excel may crash or save an unreadable file if a sheet that contains defined names that refer to a worksheet are copied or moved to a different workbook.
• When an Excel chart in a PowerPoint presentation is inserted into a new presentation through the "Reuse Slides" feature, the chart does not adopt the theme of the target presentation even if "Keep Source Formatting" option is not selected.
• When a line chart is based on a named range for which at least one element evaluates to #N/A, the #N/A value appears as 0 on the chart and does not interpolate the #N/A data points as expected.
• Excel crashes when a table that has styles applied as block-level formatting is copied into another workbook.
• If an Excel 2003 chart or graph object in PowerPoint 2003 uses the accent colors from the color palette, these colors are converted to black when the file is opened in PowerPoint 2010.
• Excel crashes when you are using a PivotTable and PivotChart after you perform drill operations, undo multiple operations at once, modify the PivotChart, and then save the file.
• Hidden columns become visible when data is copied between worksheets that have different default column widths.
• Data labels on a chart can be incorrectly hidden when the label has a custom position, and the file is saved as .xls file type.
• When accessed programmatically, some methods return incorrect results when you have multiple conditional formats on different ranges that intersect.
• Cells that have “center across selection” formatting options applied are not correctly redrawn after a calculation. This can create "redraw artifacts" or the appearance of a recalculation problem.
• Data is formatted incorrectly when you apply conditional formatting rules to a range that includes a non-volatile function that is followed by a volatile function (such as RAND()),
• Excel crashes when you select the value field header cell of a PivotTable that has all data filtered out.
• The order of the legend entries that are displayed in the legend of a stack chart that was created in Excel 2003 is reversed when the chart is opened in Excel 2010 if the legend was modified to have a custom position.
• Data is not displayed correctly on a chart when you open a workbook that was created in a previous versions of Excel and that has the BIFF3, BIFF4, or BIFF5 file formats, if the chart is based on external data (such as a from a .dat file).
• When you open a hyperlink that points to an Excel file (.XLSX format file), the file opens in the "restored" state and the window is not maximized.
• After copying a sheet with an object that has a macro associated with it to another workbook, the macro does not work correctly because it is not correctly pointing at the source workbook.
• When you import a text file, certain decimal values are not recognized as numeric.
• A Pivot Table can become corrupted if member properties in the OLAP cube on which the Pivot Table is based are added, modified, or deleted.
• When multithreaded calculation is disabled, calculation is slow when the total number of formulas in all loaded workbooks is large, and a small subset of those formulas are repeatedly calculated.
• XMLSS type files do not open if the program is in Protected View mode.
• When cell editing is disabled and you double-click a cell that contains a reference to a cell in another workbook, the source workbook opens, but the specific cell is not activated,
• Documents that are saved in the Office 2003 file format that contain pie charts that had data labels outside the pie segments may display the labels inside the pie segments when the document is opened if the labels were edited before the file was saved.
• A cell that is part of a conditional format range is temporarily not updated to reflect the conditional formatting result when the contents of the cell is deleted.
• Excel crashes when you are using Remote Desktop and you try to perform a paste or print action, such as Paste As Picture Link.
• A stacked area chart always fills from the plane that intersects with the zero value on the vertical axis instead of from the X axis crossing point.
• Excel crashes when you open a file that contains a macro on an embedded object, and the file name contains a square bracket character ("[" or "]").
• If custom formatting is applied to data points in a chart or if the "Vary colors by point" setting is enabled for a chart, formatting may be lost when you hide the corresponding series data.
• A chart that was based on external data from a .DAT file will be missing from an .xlxs file if that file was an Excel 2003 file (.xls) that was saved in the Excel 2007 file format (.xlxs), moved to another directory, and then opened.
• If you open a workbook containing a slicer in Excel 2007 and then save it to the XLSB format and then XLSX format, the next time file is opened in Excel 2010 it is reported as corrupted.
• Excel crashes when you position the pointer over an area chart that has a date axis and a lot of blank cells in the chart data range.
• In PowerPoint, the size of the Excel OLE chart object changes after you right-click and then open the chart object by using the Open command on the Chart Object menu.
• A file that has a corrupted chart part is repaired silently. The user is not notified of the problem and prompted to approve the repair.
• When you have an OLAP PivotTable that contains a hierarchy that has a hidden level and you are trying to create a named set that is based on the other PivotTable axis, the Tuple Definition dialog box displays the wrong fields. Excel crashes when you try to create the named set after you load a workbook that has this kind of PivotTable.
• The SUMIFS, AVERAGEIFS, and COUNTIFS functions return incorrect results when the function contains two or more criteria ranges that come from at least two different sheets.
• When you open an Excel file from a network share, the file is not correctly updated by the "Update File" feature from the Quick Access toolbar after another user has edited the file.
• Certain array-entered formulas that reference ranges by using  formulas that reference operations or worksheet functions  that have not been fully calculated. The allocated strings (such as IF, CONCATENATE, or the "&" operator) may not calculate after certain operations, such as changing support cells, are performed.
• If a named set that is based on measures hierarchy and for which at least one of the measures does not have data in a default context is placed on a PivotTable axis, the measure that lacks data is not displayed. If none of the measures have data, Excel might crash.
• Excel stops responding or displays unpredictable behavior, such as incomplete calculations, when a workbook that has array formulas that contain VLOOKUP, LOOKUP, or HLOOKUP functions is calculated.
• Blank dates in "2-D" and "3-D" area and line charts that have a date axis are not correctly ignored and are instead plotted as zero values.
• You cannot install the Arabic - Saudi Arabia, Hebrew, Hindi, or Thai version of the Analysis Tool Pack add-in for Excel.
• Surface charts (both wireframe and solid) are not drawn properly from certain data sets.
• The camera tool and the "paste as picture" link are not resized when the cell range changes size in the linked worksheet.
• When there are merged cells in Excel, the wrong cell range from a different worksheet may be shown.
• A crash occurs when you open a workbook in Protected View but then cancel while the file is being opened.
• When opening a file with the same name from multiple locations, Excel goes into a state in which the program cannot be closed.
• Users cannot load certain previously saved Excel OLE objects in Excel 2010.
• You open a workbook that has surface charts in Protected View. When you immediately try to edit the workbook, the program crashes.
• A crash occurs when you open an Excel file that has surface charts in Protected View.
• SP1 introduces a new checkbox, "Excel Toolbars Files" in the File Block Settings tab of the Trust Center, to allow a user/corporation to prevent users from opening Excel Toolbars (.xlb) files in Excel because of security concerns. Checking it sets a new registry key, XlbFiles, which blocks Excel Toolbars (.xlb) files from being opened.

Excel(엑셀) 워크시트의 일정 영역에 데이터가 입력되어 있을 때, 여기에 입력된 값 중 특정 문구를 포함하는 셀이 몇 개인지를 확인하는 방법이다. 

우선 아래와 같이 데이터가 입력되어 있다고 한다.

E2셀에는 찾고자 하는 문구와 정확하게 일치하는 셀의 개수를 구하는 수식을 countif 함수를 이용해 아래와 같이 작성한다.

함수를 작성한 후 채우기 핸들을 더블 클릭하여 아래 셀까지 수식을 복사한다.

이번에는 찾는 문구를 포함하고 있는 셀의 개수를 구하는 함수를 G2셀에 작성한다.

같은 방법으로 채우기 핸들을 더블 클릭하여 아래 모든 셀에 같은 같은 수식을 복사한다.

핵심은 와일드카드를 사용하는 것으로 여기에서처럼 "*"(별표)를 사용할 수도 있고, "?"(물음표)를 이용해 찾는 단어의 자리수도 제어할 수 있다.

엑셀(Excel)이나 워드(Word)나 한/글(아래아한글)이나 입력된 내용을 조작할 때 유용하게 사용할 수 있는 기능으로 "찾기"가 있다. 원하는 내용만 찾아주는 것이 아니라 원하는 모양도 찾아주기 때문에 잘 활용하면 효율적인 편집 작업을 진행할 수 있다.

여기에서는 Excel에서 일명 볼드체(bold)가 적용된 굵은 글씨(한글 메뉴 이름: 굵게)가 들어있는 셀을 찾는 방법이다. 

우선 아래와 같은 데이터가 있다고 한다.

필요한 셀을 찾기 위해서 [찾기 및 선택] 메뉴에서 [찾기(F)...]를 선택하거나 범용 단축키인 [Crtl]+[F]를 누른다.

"찾기 및 바꾸기" 창에서 [옵션] 단추를 누른다.

[서식(M)...] 단추를 누른 다음 "글꼴} 탭에서 원하는 서식을 지정한다. 여기서는 "글꼴 스타일: 굵게"만 선택.

[미리 보기] 항목이 설정한 서식으로 바뀐 것을 확인할 수 있다. [모두 찾기]를 누른다.

아래 그림과 같인 해당 서식이 적용된 셀의 위치와 값이 나열된다.

이 목록에서 선택하고자 하는 셀을 선택하면 된다. 모두 선택하고 싶다면 범용 단축키인 [Ctrl]+[A]를 이용하는 것이 좋다. [Shift]키나 [Crtl]키를 활용한 선택 방법도 좋다.

필요한 부분을 선택해서, 삭제나 변형 등 필요한 작업을 해주면 된다.

 

해당 메뉴가 좀 다르기는 하지만, 워드나 한/글에서도 가능하다.

이전 글에서 Excel(엑셀) 워크시트에 셀 서식을 이용해 입력하는 데이터의 앞뒤로 따옴표를 넣은 방법을 소개했다. 셀 서식의 사용자 지정에 키보드로 입력하는 순수 따옴표는 입력을 할 수 없기 때문에 나름대로 단점은 있다.

셀 자체에 입력하는 따옴표가 반드시 키보드로 입력하는 따옴표여야 한다면 일단 수식을 이용하는 것이 좋다.

즉, 아래 그림처럼 A열에 있는 데이터를 B열에 있는 모양으로 만들어 주는 수식을 B열의 각 셀에 작성해 넣는 것이다.

A열에 데이터가 입력되어 있다면 B열 첫 번째 행에 아래와 같은 수식을 입력한다. 여기서 주의할 점은 해당 함수의 첫 번째 인수에 따옴표가 네 개 연속으로 들어간다는 것이다. 마지막 인수도 마찬가지이다.

그 다음 다시 그 셀을 선택하고, 우측 구석의 채우기 핸들을 더블 클릭한다.

이렇게 하면 A열에 데이터가 채워져 있는 만큼 B열에 자동으로 같은 수식이 채워진다. 간단하다.

또는 아래와 같은 수식을 사용할 수도 있다. 여기에서는 함수를 사용하지 않고 문자열을 결합하는 "&" 연산을 이용한다. 여기도 마찬가지로 큰따옴표를 연속으로 네 개씩 입력한다.

만약 큰따옴표(")가 아니라 작은따옴표(')를 입력해야 한다면 아래 그림과 같이 큰따옴표 두 개 사이에 작은따옴표를 간격없이 붙여서 입력하면 된다. 이게 일반적인 기호를 연결하는 방법인데 큰따옴표인 경우에만 위의 설명처럼 사용하면 된다.