Posted
Filed under MS Excel
|| English || 中文 || view 8378 ||

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

사용자 삽입 이미지
사용자 삽입 이미지

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

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

사용자 삽입 이미지

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

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

사용자 삽입 이미지

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

"MS Excel " 분류의 다른 글
[MS Excel 2007] 77.1 곱하기 850은? 큰일 날 뻔 했다  (0)2007/09/27  
[Excel] 텍스트 나누기 기능으로 일자 데이터를 연, 월, 일로 분리하기  (0)2007/07/09  
[MS Excel] 각 달의 마지막 날짜 구하기  (0)2007/12/22  
[Excel] 이메일 주소를 골뱅이(@) 앞뒤로 분리하기  (2)2007/07/02  
[MS Excel 2007] 리본의 데이터 탭에 데이터 분석 도구 메뉴가 보이지 않을 때  (0)2008/12/06  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 8361 ||

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

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

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

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

사용자 삽입 이미지

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

사용자 삽입 이미지
사용자 삽입 이미지

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

사용자 삽입 이미지

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

사용자 삽입 이미지

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

사용자 삽입 이미지사용자 삽입 이미지

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

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

사용자 삽입 이미지

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

사용자 삽입 이미지

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

사용자 삽입 이미지

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

사용자 삽입 이미지

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

"MS Excel " 분류의 다른 글
[MS Excel] 주소로 위도, 경도 찾고, 두 지점 간 직선 거리 계산하기  (2)2010/08/06  
[Excel] 데이터 영역에서 마우스 두 번 클릭으로 활성 셀 얼른 이동하기  (2)2007/07/07  
[Excel] 메모나 도형의 모양을 다른 것으로 간단하게 바꾸기  (0)2011/02/28  
[Excel 2007] 두 개 이상의 문서를 서로 다른 창에서 보기  (5)2009/05/30  
[Excel(엑셀)] 피벗 테이블의 빈 셀을 특정 값으로 채우기  (1)2011/01/31  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 14360 ||

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

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

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

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

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

[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 입력하기

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

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

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

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

[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 입력하기

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

[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 입력하기

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

셀 서식 > 맞춤

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

[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 입력하기

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

"MS Excel " 분류의 다른 글
[MS Excel] 같은 도형을 연속으로 그리기  (2)2008/02/24  
[Excel] 열의 순서를 정리하는 세 가지 방법  (0)2007/07/18  
[Office 2013] 프로그램 인터페이스를 마우스와 터치 모드로 번갈아 사용하기  (2)2013/02/19  
[Excel] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기  (0)2011/02/28  
[MS Office] Office 2003 서비스 팩 3(SP3) 출시  (0)2007/09/19  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 9701 ||

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

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

[Excel] 한 열로 입력된 데이터를 여러 개의 열로 다시 정리하는 방법

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

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

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

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

[Excel] 한 열로 입력된 데이터를 여러 개의 열로 다시 정리하는 방법
[Excel] 한 열로 입력된 데이터를 여러 개의 열로 다시 정리하는 방법
[Excel] 한 열로 입력된 데이터를 여러 개의 열로 다시 정리하는 방법
[Excel] 한 열로 입력된 데이터를 여러 개의 열로 다시 정리하는 방법

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

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

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

 

"MS Excel " 분류의 다른 글
[Excel] 메모나 도형의 모양을 다른 것으로 간단하게 바꾸기  (0)2011/02/28  
[Excel] 특정 기간의 일자 데이터를 자동으로 채우기  (0)2007/07/04  
[MS Office] Office 2003 서비스 팩 3(SP3) 출시  (0)2007/09/19  
[MS Excel] 셀에 분수(分數)를 바로 입력하기  (0)2008/08/21  
[MS Excel] 피벗 테이블에서 동적 범위 사용하기  (0)2005/07/03  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 18470 ||

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

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

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

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

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

[Excel 2010] 파일 > 정보 > 버전

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

[Excel 2010] 저장 않고 닫은 파일 살려내기

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

[Excel 2010] 저장 않고 닫은 파일 살려내기

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

[Excel 2010] 옵션 > 저장

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

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

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

 

 

 

"MS Excel " 분류의 다른 글
[Excel] 특정 셀을 한글 또는 영문 전용 입력 모드로 만들기  (0)2007/07/31  
[Excel] 시트 전체를 선택하는 단축키인 Crtl+A의 활용  (0)2011/12/03  
[Excel] 이메일 주소를 골뱅이(@) 앞뒤로 분리하기  (2)2007/07/02  
[Excel] 데이터 연결을 유지하면서 행/열 바꾸기  (1)2010/10/21  
[Excel 2016] 새로 추가된 차트 유형  (0)2015/09/23  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 9281 ||

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

엑셀 2010
이 중 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.
 
 
"MS Excel " 분류의 다른 글
[Excel] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기  (0)2011/02/28  
[Excel 2013 Preview] 리본 메뉴에 개발 도구 탭 표시하기  (0)2012/07/23  
[Excel 2013] 피벗 테이블에서 시간 표시 막대로 데이터 필터하기  (0)2013/02/19  
[Excel] 반복해 입력된 셀 데이터 중 상위 하나만 남겨두고 모두 감추기  (2)2012/12/17  
[Excel] 메모나 도형의 모양을 다른 것으로 간단하게 바꾸기  (0)2011/02/28  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 11757 ||

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

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

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

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

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

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

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기
[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

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

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

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

[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기
[Excel] 목록에서 특정 문구가 들어간 셀의 갯수 구하기

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

"MS Excel " 분류의 다른 글
[Excel] 시트의 일정 영역을 그림으로 복사하기  (0)2007/07/24  
Word, Excel, PowerPoint 2007 리본 메뉴에 각 프로그램 학습용 시작 메뉴 달기  (2)2009/01/10  
[Excel] 고급 필터에서 텍스트 값과 관련된 조건 만드는 방법  (0)2009/01/20  
[Excel 2016] 웹 페이지의 데이터 표를 엑셀에 연결하여 사용하기  (0)2015/10/27  
[MS Excel 2007] 중복된 값들을 하나만 남겨놓고 모두 정리하기  (0)2008/08/12  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 8672 ||

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

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

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

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

[Excel] 굵은 글꼴이 적용된 셀만 찾아 모두 선택하기

 

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

"MS Excel " 분류의 다른 글
[Excel] 고급 필터에서 텍스트 값과 관련된 조건 만드는 방법  (0)2009/01/20  
[MS Excel] 셀에 입력하는 수식 자체에 주석 달기  (0)2010/11/24  
[Excel 2007 이상] 두 열 이상의 값을 동시에 조회하여 필요한 열 합계내기  (0)2010/08/12  
[Excel] 특정 셀에 특정 범위의 숫자만 입력되도록 하기  (2)2007/07/30  
[Excel] 데이터 표로 동적 범위 활용하기  (1)2010/10/17  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 19226 ||

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

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

즉, 아래 그림처럼 A열에 있는 데이터를 B열에 있는 모양으로 만들어 주는 수식을 B열의 각 셀에 작성해 넣는 것이다.
[Excel] 입력되어 있는 데이터의 앞뒤로 따옴표 넣기

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

그 다음 다시 그 셀을 선택하고, 우측 구석의 채우기 핸들을 더블 클릭한다.
[Excel] 입력되어 있는 데이터의 앞뒤로 따옴표 넣기

이렇게 하면 A열에 데이터가 채워져 있는 만큼 B열에 자동으로 같은 수식이 채워진다. 간단하다.
[Excel] 입력되어 있는 데이터의 앞뒤로 따옴표 넣기

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

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


"MS Excel " 분류의 다른 글
[Excel] 이메일 주소를 골뱅이(@) 앞뒤로 분리하기  (2)2007/07/02  
[Excel] 두 개의 주사위를 던진 무작위 결과 만들기  (2)2007/07/01  
[Excel] N 함수 마법사의 설명 오류(?)  (0)2007/07/28  
[Excel] 이미 지정된 선택 영역의 범위를 상하좌우 원하는 방향으로 조절하기  (0)2007/07/13  
[Excel] 데이터 목록과 차트 축의 데이터 나열 순서가 서로 다를 때  (0)2010/11/07  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 15121 ||

Excel(엑셀) 워크시트에 데이터를 입력하면서 계산이나 분석을 위한 데이터 값 이외에 다른 내용을 같이 넣는 것은 별로 바람직하지 않다. 예를 들어, 금액을 넣으면서 '100원', '200원' 하는 식으로 단위인 '원'도 같이 입력하는 것은 좋지 않다는 이야기이다.

그래도 작업을 하다보면 어쩔 수 없이 이와 유사한 형태를 입력을 해야 하는 경우가 없지 않다. 이런 일 중에 "따옴표"를 입력하는 경우도 있는데, 물론 처음부터 입력할 때 따옴표도 같이 입력해주면 상관이 없지만, 이미 입력되어 있는 셀 데이터에 앞뒤로 따옴표를 넣거나, 입력할 때 따옴표를 제외한 부분만 입력하면 자동으로 알뒤에 따옴표를 붙여야 한다면 약간의 우회 작전이 필요하다.

예를 들어, 아래와 같은 그림과 같은 방식이다.
[Excel] 셀 서식에 따옴표 자체를 입력하기

A열에 입력된 데이터를 C열에 있는 모양으로 바꾸는 것이다. 그림에서 보는 것처럼 따옴표는 입력을 하는 것이 아니라 자동으로 들어가는 것이다.

보통 "셀 서식" 메뉴를 이용해 입력하는 데이터를 원하는 모양으로 지정할 수 있어, 여러 가지 특수 기호 및 문자를 활용할 수 있다. 그런데 문제는 따옴표 자체는 기호나 문자로 인식하지 않고 특수한 형태의 명령어로 취급해 버리는 데에 있다.

즉, 아래 그림과 같이 셀 서식의 사용자 지정 항목에서 셀에 표시될 모양을 지정하게 되는데 다른 문자나 기호는 표시가 되지만, 따옴표는 표시가 되지 않는다.
[Excel] 셀 서식에 따옴표 자체를 입력하기
[Excel] 셀 서식에 따옴표 자체를 입력하기

이때에는 따옴표를 키보드의 [Enter] 키 왼쪽에 있는 작은따옴표를 [Shift]와 같이 눌러 입력하는 따옴표가 아니라 특수 문자로 삽입해 사용하는 기호로 입력해 사용해야 한다.

이를 위해 아래 그림과 같이 적당한 셀에 한글 자음 "ㄴ"을 입력한 다음 바로 키보드의 [한자] 키를 눌러 특수 기호 목록을 부른다. 이중 가장 처음 나오는 따옴표 말고 아래로 몇 칸 내려가면 아래 그림과 같이 두 개의 작은따옴표 아래 두 개의 큰따옴표를 볼 수 있다. 위에 있는 것이 여는 따옴표이고, 아래 있는 것이 작은 따옴표이다.
[Excel] 셀 서식에 따옴표 자체를 입력하기

글꼴(폰트)에 따라서 이 두 따옴표의 모양이 같을 수도 있고, 다를 수도 있다. 가급적이면 여는 용도와 닫는 용도를 따로 사용하는 것이 좋다.

이제 두 개의 따옴표를 입력하고, 이를 각각 복사하여 셀 서식에서 필요한 모양으로 만들 때 붙여 넣는다. 이렇게 하면 해당 영역의 데이터 모양이 변경된다.
[Excel] 셀 서식에 따옴표 자체를 입력하기

아래 그림과 같이 글꼴을 바꿔 보면 여는 따옴표와 닫는 따옴표의 차이를 확실하게 구분할 수 있다.
[Excel] 셀 서식에 따옴표 자체를 입력하기

"셀 서식" 기능을 사용하지 않아도 함수와 수식을 이용한 문자열 병합 등의 방법을 쓸 수도 있지만, 이 방법도 써볼만 한다.
"MS Excel " 분류의 다른 글
[Excel] 이름 목록에서 랜덤하게 이름 뽑아내기  (0)2013/01/06  
[Excel] 차트 작성 시 숫자로 된 X축이 데이터로 처리되는 것을 방지하기  (0)2007/07/29  
[MS Excel 2007] 가나다 순이 아니라 셀 배경색이나 글꼴 색으로 정렬하기(Sorting)  (0)2008/08/06  
[Excel] 워크시트에 달력 집어 넣기  (0)2007/08/11  
[MS Excel] 같은 도형을 연속으로 그리기  (2)2008/02/24  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 9275 ||

Excel(엑셀) 워크시트의 A열에 데이터가 위에서 아래로 각 셀에 입력되어 있다고 하자. 각 셀에 있는 값을 B열에 두 번이면 두 번, 세 번이면 세 번 연속 입력하면서 전체 데이터를 다시 입력해야 할 때가 있다.

즉, 아래 그림과 같은 경우이다.

[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기

이 작업을 간단하게 하는 방법도 여럿 있겠지만, 아래와 같은 방법도 있다.

우선 아래 그림과 같이 B열에 1번 행부터 A열의 첫 번째 데이터를 반복이 필요한 만큼 입력해준다. 직접 입력해도 되고, 간단한 수식을 써도 된다.
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기

그 다음 A열의 두 번째 값이 입력되는 첫 번째 셀에 아래와 같은 수식을 입력한다.
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기

이 수식의 의미는 이렇다. 현재 자신의 위치(B5)에서 반복하고자 하는 횟수(여기서는 네 번이므로 4)만큼 위에 위치한 셀의 값이 원본 A열의 몇 번째에 있는 지를 알아내고, 이 위치에는 그 다음 번 행에 있는 값을 불러와 적으라는 뜻이다.

이렇게 입력한 다음 해당 셀의 데이터가 정확한지를 확인하고, 아래 그림과 같이 채우기 핸들을 필요한 만큼 잡아 끌어내린다.
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기

이렇게 하면 끝난다.

Excel 모든 버전에서 같다.

"MS Excel " 분류의 다른 글
[MS Excel] 열 머리글이 A, B, C가 아니라 1, 2, 3으로 나타날 때  (1)2006/02/17  
[Excel 2007] 두 개 이상의 문서를 서로 다른 창에서 보기  (5)2009/05/30  
[Excel] 날짜에서 요일 뽑아내는 몇 가지 방법  (0)2007/07/22  
[Excel] 주어진 데이터로 막대 그래프 얼른 그리기  (0)2007/07/15  
[Excel(엑셀)] 일련번호를 넣는 몇 가지 방법  (0)2007/07/25  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 21295 ||

Excel(엑셀)에서 두 개의 계열(열)로 데이터를 정리했는데 이 데이터의 숫자 규모가 너무 달라 하나의 Y축을 갖는 차트를 그리면 한 쪽이 너무 작거나 너무 커서 한눈에 흐름을 파악하기 어려운 경우가 있다.

이때는 보통 Y축을 "기본 축"과 "보조 축"으로 분리해 차트의 왼쪽과 오른쪽에 Y축을 각각 하나씩 두는 방법을 택하게 된다. 이렇게 하면 해당 데이터의 숫자 규모에 맞게 적당한 크기로 차트가 그려지기는 하지만, 하나의 데이터를 다른 모양의 차트로 하지 않고, 모두 막대 그래프로 한다면 서로 겹치는 문제가 나타난다. 일반적으로는 서로 다른 모양의 차트(하나는 막대, 하나는 꺾은선)를 사용하는 것이 좋기는 한데, 꼭 막대 그래프로 그려야 한다면 수고스럽지만 약간의 작업을 더 해주면 된다.

[Excel] Y축 두 개짜리 막대 그래프를 겹치지 않게 그리는 방법

[동영상 강좌]


"MS Excel " 분류의 다른 글
[MS Office] 엑셀, 워드, 파워포인트에 플래시 파일 삽입하기  (0)2007/07/20  
[Excel 2013] 피벗 테이블에서 시간 표시 막대로 데이터 필터하기  (0)2013/02/19  
[Excel] 일부러 숨겨 놓은 워크시트 완전하게 감추기  (0)2015/01/20  
[Excel] 피벗 테이블에서 집계할 데이터가 없는 항목도 나타나게 하기  (0)2011/02/22  
[Excel] 행과 열의 선택, 삽입, 숨기기 등과 관련된 단축키 사용하기  (0)2009/01/18  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 7125 ||

Excel(엑셀)에서 각 셀에 직접 입력할 필요는 없지만 뭔가 적어 놓을 때 사용하는 메모 기능이 있다. 이 메모는 기본적으로 노란색 네모 형태로 만들어지는데 이 모양을 바꾸고 싶다면 Excel 2007 이상에서는 빠른 실행 도구 모음에 [도형 모양 변경] 단추를 끌어다 놓으면 무척 편리하다.

도형 모양 변경


이전 버전의 Excel에서는 메뉴에서 쉽게 도형을 바꿀 수 있지만, 더 높은 버전에서는 이렇게 단추를 끌어오는 것이 좋다.

[동영상 강좌]


"MS Excel " 분류의 다른 글
[Excel] 특정 셀에 특정 범위의 숫자만 입력되도록 하기  (2)2007/07/30  
[MS Excel] 오류로 파일이 열리지 않을 때  (0)2005/08/03  
[Excel] 텍스트 나누기 기능으로 일자 데이터를 연, 월, 일로 분리하기  (0)2007/07/09  
[MS Excel 2007] 중복된 값들을 하나만 남겨놓고 모두 정리하기  (0)2008/08/12  
[Excel] 시트의 일정 부분만 사용할 수 있도록 하기  (0)2007/07/30  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 9411 ||

Excel(엑셀)에서 여러 개의 워크시트에 걸쳐 정리된 데이터를 하나의 시트에 종합해 정리하는 경우가 있다. 같은 형태로 만들어진 워크시트를 이용하는 것이라면 일반적으로 데이터 통합 기능을 사용하는 것이 좋다.

그래도 가끔은 여러 개의 워크시트의 이름을 이용해 셀 참조를 만들어야 하기도 한다. 워크시트가 연속으로 배열되어 있는 경우라면 셀 범위 지정할 때 사용하는 참조 연산자인 콜론(:)을 이용하는 방법이 있다. 배열이 연속적이지 않으면서, 워크시트 이름에 약간의 패턴이 있다면 물음표(?), 별표(*)와 같은 와일드카드를 이용할 수 있다.

[Excel] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기

[동영상 강좌]


"MS Excel " 분류의 다른 글
[Excel] 데이터 연결을 유지하면서 행/열 바꾸기  (1)2010/10/21  
[Excel 2010 TP] 리본 메뉴에 개발 도구 탭 표시하기  (1)2009/05/17  
[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 다시 입력하기  (0)2011/07/28  
[Excel] 중복되어 입력된 데이터를 고급 필터 기능으로 하나씩 뽑아 내 정리하기  (0)2008/01/24  
[Excel 2016] 3D 맵 맛보기  (0)2015/09/23  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 6839 ||

Excel(엑셀)을 사용하다 보면 수많은 명령과 기능에도 뭔가 이런 것은 없을까 궁금할 때가 있다. 있는 줄 몰라서 다른 기능을 우회해서 사용하거나, 다른 기능들을 조합해서 사용하기도 한다.

추가로 옵션의 [빠른 실행 도구 모음]에 보면, [리본 메뉴에 없는 명령]이라는 항목이 있다. 상단 리본 메뉴를 뒤져도 볼 수 없는 명령들이다. 이 목록을 잘 살펴보면 그동안 필요해도 몰라서 사용할 수 없었던 메뉴도 발견할 수 있을 것이다. 또한, Excel을 제대로 쓰기 위해 꼭 꺼내 사용해야 하는 중요한 명령도 있다.
 
사용자 삽입 이미지

참고용이다. 시간이 날 때 한 번씩 다 써보면 좋다.

"MS Excel " 분류의 다른 글
[Excel 2013 Preview] 추천 차트 기능  (0)2012/10/15  
[MS Excel] 여러 열에 골고루 흩어져 있는 값들을 한 열로 정리하고 어디 어디에 있는지 정리하기  (1)2008/02/20  
[Excel(엑셀)] 피벗 테이블의 빈 셀을 특정 값으로 채우기  (1)2011/01/31  
[MS Excel 2007] 가나다 순이 아니라 셀 배경색이나 글꼴 색으로 정렬하기(Sorting)  (0)2008/08/06  
[Excel] N 함수 마법사의 설명 오류(?)  (0)2007/07/28  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 8916 ||

Excel(엑셀) 피벗 테이블은 잘 정리된 데이터를 일정 기준에 맞게 집계해 보여주는 중요한 기능이다. 사용자가 보고싶어 하는 데이터를 원하는 위치로 쉽게 이동하면서 보여주는데, 이 작업을 하다 보면 같은 열에 있는 데이터 중 같은 것끼리를 모아 집계해 보여줄 때, 기본적으로 필터링 조건에 따라 보여줄 데이터가 없는 항목은 누락되어 결과표에 나타나지 않는다.

이렇듯 전체 데이터 목록에는 존재하지만, 필터링 조건 때문에 결과표에 나타나지 않는 항목을 항상 나타나게 하는 방법이다.

[Excel 피벗 테이블 필드 설정] 데이터가 없는 항목 표시


[동영상 강좌]



"MS Excel " 분류의 다른 글
Excel 사용 중 리소스 부족 메시지가 뜰 때  (0)2009/01/29  
[MS Excel] 한 셀에 들어가 있는 데이터에서 스페이스를 제외한 글자 수 세기  (0)2012/09/28  
[Excel/Google] Excel과 Google을 간단한 계산기로 활용하기  (0)2007/08/01  
[Excel] N 함수 마법사의 설명 오류(?)  (0)2007/07/28  
[Excel 2013] 빠른 채우기의 몇 가지 예  (0)2015/01/20  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 8650 ||

Excel(엑셀)에서 막대 그래프나 꺾은선 그래프 등을 그리는 차트 기능은 Excel 버전이 올라가면서 괜찮게 진화하고는 있다. 그래도 뭔가 색다르고 멋있는 결과물을 얻으려면 상당한 공부를 해야 하는 것이 사실이다.

그냥 기본 기능만으로 차트를 뚝딱 만들면서 남들과 다른 보고서를 만들고 싶다면 이런 추가 기능을 하나 써도 좋을 듯하다.

Clean Charts라는 것으로 별 기능을 없고, 이미 만들어진 차트를 조금 더 깔끔하게 보이게 다듬어 준다.

Clean Charts 다운로드 페이지로 가기

위 페이지에서 압축 파일을 내려 받으면 그 안에 두 개의 파일이 있다. 이 중 확장자가 xls인 파일을 실행한다. 2003, 2007, 2010 버전 모두에서 사용할 수 있다.

이 파일을 실행하면, 해당 메뉴를 설치하는 단추가 들어있는 워크시트가 나타난다.

CleanCharts on Excel 2010

매크로를 사용할 수 있도록 하고, [Install Clean Charts]를 누른다. 나중에 삭제도 이 화면에서 하면 된다.

CleanCharts on Excel 2010

설치가 끝나면 추가 기능에서 해당 메뉴를 확인할 수 있다.

CleanCharts on Excel 2010

아래와 같은 차트를 하나 선택하고 [Clean this chart]를 선택한다. [Clean all charts]를 선택하면 같은 시트에 있는 차트가 순차적으로 선택되면서 아래와 같은 설정 창이 나타난다.

CleanCharts on Excel 2010

각 항목을 모두 선택해 [Clean]을 누르면 원본 차트가 아래와 같이 바뀐다.

CleanCharts on Excel 2010
CleanCharts on Excel 2010

바뀐 차트를 원래대로 돌리는 기능은 없다. Excel의 실행 취소 명령도 사용할 수 없다. 신중하게 진행하거나 복사본에 작업을 하는 것이 좋다.

남과 다른 보고서를 만들고 싶다면 이런 거라도 써보는 것이 좋겠지만, 그냥 차트를 잘 다루는 공부를 하는 것이 더 좋겠다.
"MS Excel " 분류의 다른 글
Microsoft "Data Explorer" Preview for Excel  (0)2013/04/11  
[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로  (7)2010/09/05  
[Excel 2010 TP] 리본 메뉴에 개발 도구 탭 표시하기  (1)2009/05/17  
[MS Excel] 같은 열에 중복되어 입력된 값들을 단일 레코드로 집계하기  (0)2008/02/20  
[Excel] 막대 그래프에서 최댓값, 최솟값 등 특정 값의 막대만 다른 스타일로 표현하기  (0)2017/09/08  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 17415 ||

MS Excel(엑셀)의 막강한 기능 중의 하나로 피벗 테이블(Pivot Table)이 있다. 잘만 쓰면 훌륭한 분석 및 보고서 작성 툴이 된다.

피벗 테이블을 통해 집계된 결과 표를 보면 데이터에 따라서 군데군데 빈 셀이 나오는 경우가 있다. 이때 이 셀을 0(영)이나 다른 특정 내용으로 채워주어야 하는 일이 있다. 그렇다면 아래 그림과 같이 옵션에서 빈 셀을 채워주는 기능을 사용하면 된다.

Excel 2007 피벗 테이블 옵션

Excel 2007 피벗 테이블 옵션

Excel 2010 피벗 테이블 옵션

Excel 2010 피벗 테이블 옵션



[동영상 강좌]


※ Excel 동영상 강좌는 별도 마련된 Excel Korea(http://excelkorea.blogspot.com ) 블로그에서도 제공하고 있습니다.
"MS Excel " 분류의 다른 글
[Excel] 목록에서 특정 문구가 들어간 셀의 개수 구하기  (0)2011/06/27  
[Excel] 주어진 데이터로 막대 그래프 얼른 그리기  (0)2007/07/15  
[Scrap] Excel 2007 커닝 페이퍼  (0)2007/08/03  
Excel 2003과 Excel 2007 비교 표  (2)2009/01/17  
Excel 2013에 새로 추가된 함수 목록  (0)2013/02/01  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 12561 ||

Excel(엑셀) 시트에 입력된 데이터 표에서 행과 열을 동시에 만족하는 데이터를 찾아오는 방법으로 vlookup과 match 등의 함수를 이용하는 방법에 대해 글을 올린 적이 있다.

참고: [Excel] 행과 열을 동시에 만족하는 데이터 찾아오기, 2009. 1. 18.

index 함수를 활용하는 등 약간 다른 방법을 적용할 수도 있지만, 여기에서는 "이름"을 활용하는 방법을 소개한다.

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

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

데이터 표 전체를 선택하고, [수식] 메뉴에서 이름 관리와 관계된 항목 중 [선택 영역에서 만들기]를 선택한다.

선택 영역에서 만들기

이미 입력되어 있는 행과 열의 제목으로 이름을 만든다.

선택 영역에서 이름 만들기

아래 그림과 같이 [이름 관리자] 메뉴를 보면 이름이 지정되어 있는 것을 확인할 수 있다.

이름 관리자

그 다음 아래 그림과 같이 조회를 위한 표를 작성한다. 찾을 이름과 과목에는 이미 지정되어 있는 목록이 나타나도록 "데이터 유효성" 기능을 사용한다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2
데이터 유효성 검사
데이터 유효성
데이터 유효성
[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2
데이터 유효성
[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

입력할 내용의 목록을 지정하는 것은 반드시 필요한 과정은 아니지만, 사용자의 입력을 편리하게 하고, 오류를 없애기 위한 절차이므로 실무에서는 해주는 것이 좋다.

일단, 이름과 과목에 찾을 내용을 입력하지 않았다 하더라도 결과 셀(점수)에 사용할 수식을 점검하면 아래와 같다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

위 그림에 있는 것처럼 "=나다라 영어"와 같이 행과 열에서 찾을 데이터를 빈 칸 하나를 사이에 두고 입력한다. 이렇게 하면 아래 그림과 같이 결과가 나타난다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

찾을 항목을 이름과 과목 셀에서 동적으로 지정해야 하므로 아래 그림과 같이 셀 주소를 포함하는 수식으로 변경한다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

그런데 이렇게 하면 아래 그림과 같이 오류가 나타나면 제대로 된 결과가 나타나지 않는 것이 정상이다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

이를 해결하기 위해 수식에 입력한 두 개의 셀 주소에 indrect 함수를 추가하여 아래와 같이 전체 수식을 수정한다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

이렇게 하면 제대로 된 결과가 나타나는 것을 확인할 수 있다.

[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2

indirect 함수를 사용하면 해당 셀에서 참조하는 값 자체를 가져올 수 있다.

수식 안에서 공란을 사용하는 이유는 다음 글을 참고한다.

참고: [Excel] 수식에서 셀 주소로 셀 범위 지정할 때 사용하는 기호 3가지, 2009. 1. 21.
"MS Excel " 분류의 다른 글
[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로  (7)2010/09/05  
[MS Excel] 같은 도형을 연속으로 그리기  (2)2008/02/24  
[MS Excel] 한 셀에 들어가 있는 데이터에서 스페이스를 제외한 글자 수 세기  (0)2012/09/28  
[Excel] 그림, 도형 등의 개체를 눈금선(셀 구분선)에 자석처럼 달라붙게 하기  (2)2007/08/03  
[Excel] 셀에서 함수 입력할 때 나오는 툴팁 활용하기  (0)2011/01/05  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 6981 ||

Excel(엑셀)의 워크시트에서 셀에 등호(=)를 입력하고 함수를 쓰고 난 후 인수를 넣기 위해 여는 괄호(()를 쓰면 입력하는 셀 바로 아래 간단한 함수 사용법을 확인할 수 있는 툴팁(tooltip)이 나타난다.

[Excel] 셀에서 함수 입력할 때 나오는 툴팁 활용하기

이 툴팁은 함수를 사용할 때 많은 도움을 준다. 보통은 함수의 여러 인수를 입력하면서 참고용으로 사용하지만, 아래 동영상에서 보는 바와 같이 이 툴팁에 있는 함수 이름을 클릭하면 도움말을 볼 수도 있고, 이 툴팁이 필요는 하지만, 다른 셀의 내용을 가려 답답하다면 마우스로 끌어 다른 곳으로 옮겨 놓을 수도 있다.


별로 중요한 기능이 아닐 수도 있지만, 엑셀을 자주 쓴다면 유용할 때가 있다.

"MS Excel " 분류의 다른 글
[MS Office] 다음 tv팟 동영상을 엑셀, 워드, 파워포인트에서 재생하기(동영상 내려받기 포함)  (1)2007/07/22  
[Excel] 고급 필터에서 텍스트 값과 관련된 조건 만드는 방법  (0)2009/01/20  
[Excel] 이메일 주소를 골뱅이(@) 앞뒤로 분리하기  (2)2007/07/02  
[MS Excel] 피벗 테이블에서 계산 필드 사용하기  (0)2005/07/03  
[MS Excel] 같은 열에 중복되어 입력된 값들을 단일 레코드로 집계하기  (0)2008/02/20  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 14872 ||

일반적으로 Excel(엑셀) 워크시트를 사용하다 보면 데이터 자체와는 상관 없이 설명 등의 주석을 달아야 하는 일이 생긴다.

이때는 데이터 주변 적당한 셀에 설명을 적어 놓거나 특정 셀에 대한 것이면 메모 삽입 기능을 사용한다.

그외에 셀에 입력하는 수식에는 영향을 주지는 않으면서 수식과 같은 줄에 주석을 입력하고 싶은 경우도 있다.

그런 경우는 아래 그림과 같이 N 함수를 활용하면 된다.

[MS Excel] 셀에 입력하는 수식 자체에 주석 달기

그림에서 처럼 처리하고자 하는 수식 뒤에 +N("주석 내용")과 같은 형식으로 필요한 내용을 적어 넣으면 된다.
"MS Excel " 분류의 다른 글
[MS Office] 다음 tv팟 동영상을 엑셀, 워드, 파워포인트에서 재생하기(동영상 내려받기 포함)  (1)2007/07/22  
[Excel 2013 Preview] 리본 메뉴에 개발 도구 탭 표시하기  (0)2012/07/23  
[MS Office] Office 2003 서비스 팩 3(SP3) 출시  (0)2007/09/19  
Excel 2003과 Excel 2007 비교 표  (2)2009/01/17  
[Excel] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기  (0)2011/02/28  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 11093 ||

Excel(엑셀)에서 데이터 표를 이용해 차트(피벗 차트 포함)를 그릴 때 아래 그림과 같이 표에 만들어진 데이터 목록과 차트에 표현된 데이터 목록이 거꾸로 나타날 때가 있다.

[Excel] 데이터 목록의 순서와 차트 축의 순서가 다를 때

이것을 제대로 표시한다고 다시 정렬(Sort)하면 차트에서도 다시 순서가 바뀐다.

[Excel] 데이터 목록의 순서와 차트 축의 순서가 다를 때

이때에는 우선 차트의 해당 부분에서 단축 메뉴를 부르고 [축 서식]을 선택한다.

[Excel] 데이터 목록의 순서와 차트 축의 순서가 다를 때

그 다음 [축 옵션]의 설정 창에서 "항목을 거꾸로" 항목을 선택하여 켜준다.

축 서식 > 축 옵션

이렇게 하면 표의 목록와 차트 축의 목록에서 데이터의 순서가 같아진다.

[Excel] 데이터 목록의 순서와 차트 축의 순서가 다를 때

"MS Excel " 분류의 다른 글
[MS Excel] 셀 안에 특정 문자를 반복해서 가득 채워넣기  (4)2008/11/21  
[MS Excel] 피벗 테이블에서 동적 범위 사용하기  (0)2005/07/03  
[Excel] N 함수 마법사의 설명 오류(?)  (0)2007/07/28  
[Excel] 특정 셀을 한글 또는 영문 전용 입력 모드로 만들기  (0)2007/07/31  
[Excel] 시트 전체를 선택하는 단축키인 Crtl+A의 활용  (0)2011/12/03  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 25315 ||

Excel(엑셀) 워크시트에 가로 세로로 입력된 데이터를 필요에 따라 가로와 세로를 바꾸는 행/열 바꾸기 기능을 사용하는 경우가 있다.

아래 그림과 같이 가로로는 분기별 데이터, 세로로는 이름이 적혀있는 데이터가 있다고 한다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

이를 가로로는 이름, 세로로는 분기가 표시된 형태로 바꾸려면 일반적으로 "선택하여 붙여넣기" 메뉴에 있는 '행/열 바꿈' 기능을 사용하게 된다.

선택하여 붙여넣기 > 행/열 바꾸기

이렇게 하면 간단하게 모양을 바꿀 수는 있지만, 원본 데이터 모양도 가지고 있는 필요가 있고, 각종 수식이 들어 있는 상태라면 가끔 곤혹스러울 때가 있다.

따라서 원본과 연결은 유지한 상태로 행/열이 바뀐 또 다른 표도 별도로 유지해야 할 필요가 있을 때에는 다음과 같은 순서를 따른다.

원본 데이터가 "Sheet1" 시트에 있다고 하고, 다른 시트의 A1에 아래와 같은 수식을 입력한다. 시트 이름은 상관이 없으나. 수식이 입력되는 셀의 위치(여기서는 A1)는 원본 시트의 위치와 같아야 한다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

INDEX 함수에 원본 데이터의 위치를 입력할 때에는 절대 주소를 사용한다. 입력을 마치면 아래와 같이 해당 위치의 값이 나타난다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

그 다음 A1 셀에서 채우기 핸들을 적당한 범위로 끌면 아래와 같이 행/열이 바뀐 표를 확인할 수 있다. 행 번호를 불러오는 ROW 함수와 열 번호를 불러오는 COLUMN 함수를 거꾸로 사용한 결과이다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

즉, 다른 시트의 같은 범위에 있는 데이터를 불러올 때 행과 열을 바꿔 가져오도록 한 것이다. 따라서 시트는 달라도 범위는 같아야 한다.

이를 같은 시트 내에서 구현하고 싶다면 불러오는 위치를 상황에 맞게 수정하면 된다. 아래 그림과 같이 같은 시트의 A10 셀을 기준으로 바뀐 표를 입력하고 싶다면 A10에 수식을 입력한다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

위와 같이 입력하면 아래와 같이 참조 범위가 잘못 되었다는 "#REF!" 메시지가 나온다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

수식을 아래와 같이 변경한다. 원본의 위치가 열은 같지만, 행이 현 위치보다 9칸 위로 올라가 있으므로 ROW 함수의 결과에서 9를 빼는 것이다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기
[Excel] 데이터 연결을 유지하면서 행/열 바꾸기
[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

이렇게 하면 제대로 나오는 것을 확인할 수 있다.

원본과 연결되어 있으므로, 원본의 내용이 바뀌면 새로운 표의 내용도 따라 바뀐다.

[Excel] 데이터 연결을 유지하면서 행/열 바꾸기

"MS Excel " 분류의 다른 글
[Excel] 일부러 숨겨 놓은 워크시트 완전하게 감추기  (0)2015/01/20  
MS Press에서 무료로 배포하는 PDF 전자책 "First Look: Office 2010"  (0)2009/12/18  
[Excel] 셀 서식에 따옴표 자체를 입력하기  (0)2011/05/28  
[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기  (0)2009/01/18  
[Excel] 데이터 목록과 차트 축의 데이터 나열 순서가 서로 다를 때  (0)2010/11/07  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
네티즌

좋은 글 감사합니다. 출처 밝히고 퍼갈게요. ^^

Posted
Filed under MS Excel
|| English || 中文 || view 10430 ||

Excel(엑셀)을 사용하면서 이것 저것 만지다가 자신도 모르게 비정상적인 상황에 빠지고, 이를 해결하지 못해 난감해 하는 경우가 있다.

그 중 하나가 수식이나 참조 등을 수정하고 적용을 하면, 해당 셀의 결과값이 새로 계산되어 나와야 하는데 그냥 그대로 멈춰버려 갱신이 되지 않는 경우이다.

예를 들어, A1셀과 A2셀을 더한 값을 계산하는 합계 수식을 A3에 적어두었다면, A1이나 A2셀의 값이 변경되면 자동으로 A3의 값이 재계산되어야 하는데 그렇지 않는 경우가 있다는 말이다.

이 문제는 주로 옵션에서 자동으로 하는 이 기능이 꺼져 있기 때문에 나타난다.

Excel 옵션 > 수식 > 계산 옵션

계산 옵션에서 통합 문서 계산을 '자동'으로 하면 된다. 위 그림은 2010 버전이지만 엑셀 전 버전에 해당 항목이 있다.

가끔 작업의 편의상 이를 '수동'으로 할 때도 있는데, 이때는 파일을 열고 닫을 때 재계산이 되고, 필요할 때마다 [F9] 키를 누르면 바로 계산값이 변경된다.
"MS Excel " 분류의 다른 글
[Excel] 두 열 이상의 값을 동시에 조회하여 필요한 열 합계내기  (2)2009/05/10  
[Excel 2010 TP] 리본 메뉴에 개발 도구 탭 표시하기  (1)2009/05/17  
[Excel] 시트 전체를 선택하는 단축키인 Crtl+A의 활용  (0)2011/12/03  
[MS Excel] 같은 열에 중복되어 입력된 값들을 단일 레코드로 집계하기  (0)2008/02/20  
[Excel] 데이터 영역에서 마우스 두 번 클릭으로 활성 셀 얼른 이동하기  (2)2007/07/07  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Toney

매우 감사합니다. 간단한 문제인데 해결 못하고 있었어요

Posted
Filed under MS Excel
|| English || 中文 || view 12753 ||

엑셀(Excel)에 데이터를 입력해 일종의 데이터셋이나 데이터베이스로 사용하는 경우 개별 항목이 추가되거나 삭제가 되면, 이를 참조해 사용하던 수식이 영향을 받게 된다. 이런 점을 고려해서 2003 버전 이전에서는 데이터 범위를 참조할 때 Offset 함수 등을 이용동적 범위를 인식하는 간단한 수식을 만들어 사용하기도 했다.

엑셀 2007 버전 이후로는 데이터 표 기능을 이용해 간단하게 해결할 수 있다.

우선 아래 그림과 같은 표가 있다고 가정한다. A열은 명단이고, B열은 매출액 데이터라고 한다.

[Excel] 데이터 표로 동적 범위 활용하기

전체 데이터 영역을 선택([Ctrl] + [Shift] + [8])하고, 삽입 탭의 표 메뉴를 선택하거나, 바로 가기 키인 [Ctrl] + [L]을 누른다.

[Excel] 데이터 표로 동적 범위 활용하기

[표 만들기] 대화 상자에서 범위나 머리글 포함 여부를 확인하고 [확인] 단추를 누른다.

표 만들기

이렇게 하면 아래 그림과 같이 범위 전체에 서식이 적용된 윤곽이 나타나고 상단에 이 범위 이름이 "표1"이라는 이름으로 지정된 것을 볼 수 있다.

[Excel] 데이터 표로 동적 범위 활용하기

그 다음 단계는 범위에 이름을 지정하는 것이다. 2007 이후에서는 추천하는 방법은 아니다. 예전부터 비슷하게 써온 적이 있다면 이와 같이 진행할 수 있다.

우선 A열의 열 제목을 제외한 명단 부분을 선택하고, 이름 지정 상자에서 적당한 이름을 지정한다. 여기에서는 "명단"이라고 입력하고 [Enter]를 누른다.

[Excel] 데이터 표로 동적 범위 활용하기

B열의 매출 부분도 같은 방법으로 "매출"이라는 이름을 지정해 준다.

[Excel] 데이터 표로 동적 범위 활용하기

이제 이렇게 지정한 범위를 참조하는 수식을 작성해 보도록 한다. 아래 그림과 같이 명단에서 특정 이름을 선택하면, 그 이름에 해당하는 매출액을 보여주는 조회 프로그램을 만든다고 가정한다.

[Excel] 데이터 표로 동적 범위 활용하기

A2셀에 명단에 적힌 이름이 펼쳐지는 선택 상자를 만들기 위해 아래 그림과 같이 [데이터] 탭에서 [데이터 유효성 검사...] 메뉴를 찾아 실행한다.

데이터 유효성 검사

데이터 유효성

"제한 대상"에서 '목록'을 선택하고, "원본" 상자에 직접 '=명단'(등호 포함)이라고 입력한다.

데이터 유효성

이렇게 하고 [확인]을 누르면 A2셀 오른쪽 외곽에 아래 방향 화살표가 나오고 이를 펼치면 명단에 들어있는 이름들을 확인하고 선택할 수 있다.

[Excel] 데이터 표로 동적 범위 활용하기

[Excel] 데이터 표로 동적 범위 활용하기

이번에는 B2셀에 해당 이름의 매출액을 조회하는 수식을 작성해 입력한다. 이때 조회하는 원본 데이터의 범위는 "표1"이라는 이름으로 지정한다.

VLOOKUP
[Excel] 데이터 표로 동적 범위 활용하기

추가로 매출액 전부를 합계한 총매출 항목도 만들어 본다. 이때에도 미리 지정해 둔 "매출"이라는 이름을 수식에 이용한다.

[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기

이렇게 데이터 범위를 이름으로 지정해 놓고, 원본 데이터에 새로운 항목이 추가되었을 때도 그대로 반영이 되는지 확인하기 위해 아래 그림과 같이 원본 데이터에 항목을 하나 추가한다.

[Excel] 데이터 표로 동적 범위 활용하기

그 다음 바로 만들어 놓았던 수식 페이지를 보면 이름도 추가되어 있고, 총매출도 자동으로 재계산이 이루어진 것을 확인할 수 있다.

[Excel] 데이터 표로 동적 범위 활용하기

따라서, 엑셀에 자료용으로 데이터를 입력해 둘 때에는 항상 데이터 표로 만들어 두는 것이 요긴하다.

또한, 앞서 말한 바와 같이 필요한 부분별로 이름 지정할 필요 없이 표의 구조를 참조로 이용하면 더 간단하게 동적 범위를 활용할 수 있다.

예를 들어, 위에서 총매출액을 구할 때 사용했던 수식인 "=SUM(매출)"은 아래와 같이 바꿔 사용할 수 있다.

[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기
[Excel] 데이터 표로 동적 범위 활용하기

데이터 표의 구조와 각 요소들을 어떻게 참조하는 지는 위 그림과 같이 사용 중에 나오는 도움말이나 자동 입력 기능으로 비교적 쉽게 알 수는 있지만, 필요하다면 따로 확인해 두면 좋다.
"MS Excel " 분류의 다른 글
[Excel 2010 새 기능 1] 셀 내부에 그리는 차트 스파크라인(Sparkline)  (0)2010/07/03  
[Excel] 빈 셀만 골라 한 번에 선택해 동시에 같은 내용으로 채우기  (0)2015/02/05  
[Excel] 입력되어 있는 데이터의 앞뒤로 따옴표 넣기  (0)2011/05/29  
[Excel] 주어진 데이터로 막대그래프 얼른 그리기는 방법 두 가지  (0)2012/12/16  
[Excel] 텍스트 나누기 기능으로 일자 데이터를 연, 월, 일로 분리하기  (0)2007/07/09  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
김형욱

군대에서꼭해보고싶었던기능이었는데..
몰라서못했었어요ㅠ

감사합니다~

Posted
Filed under MS Excel
|| English || 中文 || view 14874 ||

엑셀(Excel)을 사용하다 보면 간단한 계산이 필요할 때 번거롭게 수식이나 함수를 사용해야 하는 일이 있다. 이를 줄여주는 것 중 하나는 합계, 평균, 개수 등 선택 영역에 대한 간단한 집계 정보를 하단 상태 표시줄에 보여주는 것이다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

즉, 위 그림과 같이 데이터가 입력되어 있을 때, 특정 영역을 선택하면 아래 그림과 같이 하단 상태 표시줄에 간략한 집계 정보를 표시한다. 이 위치에서 마우스 오른쪽 단추를 클릭하여 메뉴를 부르면 최소값, 최대값 등 다른 몇 가지 정보도 확인할 수 있다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

예를 들어, 이런 방식과는 다르게 여기 나타난 합계 정보를 특정 셀에 바로 입력하고자 한다면, 매크로를 하나 작성해 두면 편하다.

매크로를 작성하기 위해 [개발 도구] 탭에서 [Visual Basic] 메뉴를 선택한다. 바로 가기 키인 [Alt] + [F11]을 사용하는 것도 좋다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

나타나는 VBA 편집기 창에서 다음과 같은 모듈을 하나 작성한다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

그 다음 편집기 창을 닫고 엑셀 시트로 돌아가 해당 매크로를 실행할 단추를 만든다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

매크로 지정
[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

각 단추와 매크로를 적절하게 연결한 후 계산이 필요한 영역을 선택하고, 복사 단추를 클릭한다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

그 다음 결과를 입력할 셀을 선택하고 붙여넣기 단추를 클릭한다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로
[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

다양한 함수(WorksheetFunction)를 응용하여 사용할 수 있다.

[Excel] 선택 영역의 합계나 평균 등을 복사 후 붙여넣기 방식으로 계산하는 매크로

또한, 단추 형태가 아니라 바로 가기 키 형태로 사용할 수도 있다.



"MS Excel " 분류의 다른 글
[Excel] 행과 열을 동시에 만족하는 데이터 찾아오기 2  (0)2011/01/06  
[Scrap] Excel 2007 커닝 페이퍼  (0)2007/08/03  
[MS Excel] 피벗 테이블의 빈 셀을 특정 값으로 채우기  (0)2007/10/31  
[Excel] 다른 셀에 입력된 데이터를 한 셀에 여러 줄로 다시 입력하기  (0)2011/07/28  
Excel 차트를 조금이라도 깔끔하게 정리할 수 있는 추가 기능 하나  (0)2011/02/08  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Hanskim

안녕하세요.
해당 VBA코드로 단순 계산은 가능하나,
금액이 커지는 경우 Overflow 에러가 뜹니다 ㅜ

혹시나 해결 가능한 방법 아시는지요?
감사합니다.

Park Chulwoo (박철우)

해당 문제에 대한 해결 방법을 새로 올렸습니다.

http://cantips.com/2636

Posted
Filed under MS Excel
|| English || 中文 || view 16048 ||

Excel(엑셀) 워크시트의 기능 중에 마우스 커서를 셀 위로 갖다대면 각 셀에 필요한 주석 등을 표시해 주는 메모(Comment)라는 것이 있다. 메모를 작성해 두면 셀의 오른쪽 상단에 빨간색 딱지가 하나 붙어 메모가 표시되어 있음을 보여주게 된다. 보통은 메모에 텍스트 위주의 정보를 입력해서 활용하게 되는데 사실 다양한 형태로 이용할 수 있다.

그 중 하나로 메모에 그림을 입력할 수도 있는데 다음 글을 참조하면 된다.

[Excel] 메모에 그림 넣기, 2007. 7. 27.

이렇게 하면 시각적으로 정보의 폭이나 재미를 넓힐 수 있어 좋다. 하지만, 그림을 여러 개 입력해야 하는 경우에는 상당히 지루한 작업이 되고, 이런 이유 때문에 필요하지만, 사용을 꺼리는 기능이 되기도 한다.

예를 들어, 각 셀에 사람 이름이 들어가 있는 명단이 있고, 각 사람에 대한 사진 파일을 특정 폴더에 정리해 두었다면 간단한 매크로(macro)를 작성하여 이름이 적힌 각 셀 위에 마우스 커서를 갖다대면 간단하게 해당 인물의 사진이 팝업창처럼 나타나도록 할 수 있다.

일단, 아래 그림과 같이 엑셀의 워크시트에 이름이 입력되어져 있고, 사진 파일의 이름은 “이름.png”로 저장되어 있다고 가정한다.

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

이제 Excel에서 [Alt] + [F11] 키를 눌러 VBA 편집기를 실행한다.

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

그 다음 왼편 창 상단에서 작업 중인 Excel 파일 이름이 괄호 안에 들어가 있는 VBAProject 항목을 확인하고 [현재 통합 문서]를 더블클릭하여 코드 입력 창을 부른다.

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

빈 창이 나타나면 위와 같이 코드를 입력한다. 위 그림에 있는 내용은 아래와 같다.

Sub AddPhoto()
For Each cell In Selection
    CurrentFolder = ActiveWorkbook.Path
    EmployeePhoto = CurrentFolder & "\인물\" & cell.Value & ".png"
    With cell.AddComment
        .Shape.Fill.UserPicture EmployeePhoto
        .Shape.Height = 100
        .Shape.Width = 100
    End With
Next cell
End Sub

사진 파일을 담은 폴더가 현재 작업 중인 Excel 파일의 바로 아래에 "인물"이라는 이름으로 지정된 경우이다. 사용 환경에 따라 적당하게 수정하면 된다. 확장자도 jpg라면 png를 jpg로 바꾸면 된다. 들여쓰기는 편의상 한 것으로 하지 않아도 되지만, 될 수 있으면 하는 것이 좋고, [Tab] 키를 쓰면 된다. 그림의 크기도 필요에 따라 수정하면 된다.

입력을 마쳤으면 편집기 창을 닫고 Excel 워크시트로 돌아온다. 그 다음, 사진을 넣을 이름 목록을 선택하고, [개발 도구] 메뉴에서 [매크로] 단추를 클릭한다. 만약 리본 메뉴에서 개발 도구 탭이 보이지 않는다면 다음 글을 참고한다.

[Excel 2007] 리본 메뉴에 개발 도구 탭이 보이지 않을 때, 2008. 10. 5.
[Excel 2010] 리본 메뉴에 개발 도구 탭 표시하기, 2010. 5. 1. 

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로


[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

매크로 목록에서 방금 작성한 해당 매크로를 선택하고, [실행(R)]을 클릭한다. 그러면 아래 그림과 같이 모든 셀에 각각 메모 표식이 나타나고 마우스 커서를 갖다대면 이름에 맞는 그림이 나타나는 것을 확인할 수 있다.

[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로
[MS Excel] 여러 셀의 메모에 서로 다른 그림을 한 번에 넣는 매크로

여러 모로 다양한 경우에 응용할 수 있다.
"MS Excel " 분류의 다른 글
[Excel(엑셀)] 일련번호를 넣는 몇 가지 방법  (0)2007/07/25  
[Excel] 일정 영역의 셀에 중복된 값이 입력되지 않도록 하기  (0)2007/08/01  
[Excel(엑셀)] 차트에서 특정 구간의 모양을 다른 부분과 다르게 만들기  (0)2012/06/06  
[Excel] 워크시트 이름을 포함하는 셀 참조를 만들 때 와일드카드 이용하기  (0)2011/02/28  
[Excel] 고급 필터에서 텍스트 값과 관련된 조건 만드는 방법  (0)2009/01/20  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
린양

엑셀2007사용자 입니다
알려주신대로 했는데 메모리가 부족하다고 그러네요 ㅠ
이런경우에는 어떻게 해야 하나요??

Pak Chulwoo (박철우)

잘 모르겠지만 시스템의 가상 메모리를 늘려보시기 바랍니다.

안군

안녕하세요 2010 사용자인데..
매크로를 다루고 싶은데 어쩔줄 몰라하다 이렇게 글을남기게 되었습니다.

제가 만들고 싶은 메크로는
도형을 클릭하면 하이퍼링크 되어있는 문서가 프린트 되게 하는것입니다.
가능할까요??
가능하다면 어떻게 해야되는지 시간이 되신다면 조금이나마 조언좀 주시면
감사하겠습니다^^; 수고하셔요~

Pak Chulwoo (박철우)

불가능하지는 않겠지만 어렵겠습니다. 하나의 도형에 하이퍼링크와 매크로를 둘 다 연결해야 하고, 파일은 실행되지 않고 인쇄가 바로 되어야 하고, 더군다나 파일의 종류가 불특정이면 쉽게 만들 수는 없습니다. 차근하근 공부하면서 만들어가는 것이 좋을 것 같습니다.

김민호

좋은 글 감사합니다. 하다가 문제점이 있는것이요!!

위와 같이 파일명을 "가나다.png" "라마바.png" "사아자.png" 이렇게 하면

잘되는데 파일명을 영문으로 하니깐 에러가 납니다.

어디 설정을 바꿔줘야 하나요?

Pak Chulwoo (박철우)

파일명이 한글이든 영어든 상관없습니다. 셀에 들어 있는 값과 파일 이름으로 사용한 값이 같은 지 비교해 보시고, 만약 다르다면 거기에 맞게 로직을 만들면 됩니다.

김민호

네 감사합니다. 잘되네요...좋은 정보 다시 한번 감사합니다.

항상 좋은 일만 가득하세요^^

Posted
Filed under MS Excel
|| English || 中文 || view 10867 ||

Excel(엑셀)을 쓰다보면 필요에 따라 첫 번째 열인 A열을 [숨기기] 하는 경우가 있다. 그런데 이외로 이렇게 숨긴 A열을 다시 보이게 할 수 없어 해매는 사용자들이 많다.

더 복잡하고 어려운 방법도 있지만 일반적으로 다음 두 가지 방법을 사용하면 된다.

방법 하나

원래 숨겨진 열을 보이게 할 때에는 숨겨진 열 앞과 뒤의 열을 동시에 선택하여 숨겨진 부분이 그 사이에 들어가게 한 다음 단축 메뉴에서 [숨기기 취소] 메뉴를 이용한다. A열을 숨겼을 때에는 A열 앞에 선택할 수 없는 열이 없어서 당황하게 되는데, 이때는 B열을 마우스 왼쪽 버튼으로 선택한 상태에서  왼쪽으로 행 머리글 부분까지 끈다. 그러면 단축 메뉴의 [숨기기 취소]  메뉴로 A열을 찾을 수 있다.

[MS Excel] 숨겨진 A열 다시 보이게 하기
[MS Excel] 숨겨진 A열 다시 보이게 하기

방법 둘

마우스 커서를 B열의 열 머리글 왼쪽 경계로 가져가면 그 모양이 아래 그림과 같이 이중 수직선을 가진 크기 변경 모드로 바뀌게 된다. 이때 마우스 왼쪽 버튼을 누른채 오른쪽으로 끌어주면 A열이 나타난다.

[MS Excel] 숨겨진 A열 다시 보이게 하기

예전에 소개했던 아래 방법으로도 가능하다.

[MS Excel] 숨겨 놓은 여러 열 중 특정 열만 나타나게 하기, 2008. 11. 25.
"MS Excel " 분류의 다른 글
[MS Excel] 주소로 위도, 경도 찾고, 두 지점 간 직선 거리 계산하기  (2)2010/08/06  
[MS Excel] 각 장바구니에 우리 회사 물건들이 몇 개 들어가 있는지 확인하기  (0)2008/12/06  
[MS Excel 2007] 중복된 값들을 하나만 남겨놓고 모두 정리하기  (0)2008/08/12  
[Excel] 차트 제목과 특정 셀 연결하기  (0)2015/01/31  
[Excel 2016] 새로 추가된 차트 유형  (0)2015/09/23  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 10262 ||

일단 아래 그림과 같은 조회 업무가 필요하다고 가정한다.

Excel SUMIFS 함수 활용

특정 제품이 어떤 지역에서 얼마만큼 팔렸는지 정리한 것이다. 날짜나 순서별로 같은 품명과 지역 조합이 중복되어 들어가 있고, 이 중복되어 있는 조합의 판매량을 총계 내는 업무이다.

이 일을 하는 방법으로 예전에 "[Excel] 두 열 이상의 값을 동시에 조회하여 필요한 열 합계내기(2009. 5. 10.)"라는 글을 올린 적이 있다. 이 글에 적힌 방법은 어떻게 보면 조금 복잡하고 어렵게 느껴질 수 있는데, Excel 2007 버전에서 새로 생긴 함수sumifs를 이용하면 쉽게 해결할 수 있다.

SUMIFS

[수학 및 삼각 함수] 여러 조건을 충족하는 범위의 셀을 더한다. 예를 들어, B1:B20 범위의 해당 숫자가 0보다 크고 C1:C20 범위의 해당 숫자가 10보다 작은 경우에만 A1:A20 범위에 포함된 수의 합계를 구할 때 사용할 수 있다. 이 함수와 SUMIF 함수는 인수 순서가 서로 다르다.

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2,...])

  • sum_range: 필수 요소로 합계를 계산할 한 개 이상의 셀이다. 여기에는 숫자나 이름 또는 범위가 포함될 수 있고 숫자가 들어 있는 셀 참조도 포함될 수 있다. 빈 값이나 텍스트 값은 무시된다.
  • criteria_range1, criteria_range2, ...: 관련 조건을 평가할 1개에서 127개 사이의 범위. 적어도 1개는 있어야 한다.
  • criteria1, criteria2, ...: 숫자, 식, 셀 참조 또는 텍스트 형식으로 된 1개에서 127개 사이의 조건으로서, 합계를 계산할 셀을 정의한다. 예를 들어 21, "21", ">21", "복숭아" 또는 B15와 같이 지정할 수 있다. .
Excel SUMIFS 함수


이 예제에서는 D2 셀에 아래와 같이 입력한다.

=SUMIFS(D5:D14,B5:B14,B2,C5:C14,C2)

Excel SUMIFS 함수 활용



"MS Excel " 분류의 다른 글
[Excel] 행과 열의 선택, 삽입, 숨기기 등과 관련된 단축키 사용하기  (0)2009/01/18  
[Excel] 수식에서 셀 주소로 셀 범위 지정할 때 사용하는 기호 3가지  (0)2009/01/21  
[Excel] 셀 데이터를 도형 내부의 문자열과 연동하기  (0)2007/08/06  
[MS Excel] 숨겨 놓은 여러 열 중 특정 열만 나타나게 하기  (0)2008/11/25  
[MS Excel] 각 달의 마지막 날짜 구하기: 복잡한 방법과 간단한 방법  (0)2008/04/14  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
Posted
Filed under MS Excel
|| English || 中文 || view 32545 ||

일을 하다 보면 출장비 계산이나 기타 등등의 이유로 지도 상에서 두 지점 간의 직전 거리를 알아야 하는 경우가 있다. 유류비처럼 꼬불꼬불하더라도 실제 움직인 거리가 중요할텐데 아무튼 이런 정보가 필요할 때가 있다.

가장 간단한 방법은 PC에 설치한 지도 프로그램을 이용하거나 포털 사이트의 지도 서비를 이용하는 것이다. 아래는 각각 다음 네이버 에서 확인한 것이다.

다음 지도에서 거리 계산

네이버 지도에서 거리 계산

위 예는 서울역에서 한강 대교 중간 지점 정도까지의 거리를 확인한 것이다. 대략 4.1에서 4.2km의 수치를 보여주고 있다.

이런 식으로 다수의 지점을 Excel(엑셀) 워크시트에 입력해 두고 필요한 지점끼리 짝을 지어 거리를 계산하는 방법 하나를 소개하고자 한다.

우선 Excel 워크시트에 원하는 지점 또는 주소의 위도와 경도를 입력해야 한다. 필요한 데이터를 알아내는 방법도 몇 가지 있지만 여기에서는 구글 맵 사이트를 이용한다.

구글 맵 사이트 로 이동한다.

Google 지도

[지도 검색] 창에 "서울역"을 입력하고 해당 위치를 찾는다.

Google 지도

위치를 표시하는 핀에서 마우스 오른쪽 버튼을 누르고, [지도 중앙으로 설정]을 선택한다.

Google 지도

해당 지점이 화면 가운데에 자리를 잡는다. 이렇게 해야 해당 지점의 정확한 좌표를 확인할 수 있다.

Google 지도

위치를 찾을 때 특정 이름을 사용할 수도 있지만, 번지까지 주소를 알고 있다면 주소를 입력해도 된다. 개인 주택 등 특정 이름이 없는 경우는 주소가 더 요긴하다.

Google 지도
Google 지도

해당 지점을 중앙에 놓았으면, 우측 상단의 "링크" 메뉴를 클릭하여 아래 그림과 같이 링크 정보를 불러온다.

Google 지도

"이메일 또는 메신저에 링크 붙여넣기" 항목에 보면 아래와 같이 좌표처럼 생긴 숫자를 찾을 수 있다. 이를 복사하고 Excel에 정리한다. 쉼표로 구분되어 있는데, 앞이 위도고 뒤가 경도이다.

Google 지도

다른 지점의 좌표도 위와 같은 방법으로 정리한다.

Google 지도

자신의 활용 목적에 맞게 자료를 정리하고 거리를 계산하는 수식을 이용하면 된다.

[MS Excel] 주소로 위도, 경도 찾고, 두 지점 간 직선 거리 계산하기

예를 들어, 위 시트에서 2행의 경우는 서울역에서 한강 대교 간의 거리를 계산하는 것이다. 상대 지점 열에 상대 지점의 번호를 입력하면 F열에 자동으로 지점명이 입력되도록 한다. index나 vlookup 함수를 사용하면 된다.

거리1과 거리2가 있는데, 거리1은 지구가 둥글다는 점을 감안한 거리 계산법이고, 거리2는 위도 간의 거리와 경도 간의 거리를 두 변으로 하는 직각 삼각형의 빗변의 길이를 구하는 피타고라스의 정리를 이용한 것이다. 이 공식을 더러 이용하는 경우도 있어서 적어둔 것이고, 거리1 방식을 추천한다. 서울역과 한강 대교 간의 거리는 4.13km로 다음이나 네이버의 결과와 유사하다.

거리1의 각 셀에 들어가는 공식의 형태는 아래와 같다.

=ACOS(COS(RADIANS(90-지점1의위도))*COS(RADIANS(90-지점2의위도))+SIN(RADIANS(90-지점1의위도)) *SIN(RADIANS(90-지점2의위도))*COS(RADIANS(지점1의경도-지점2의경도)))*6371

G2셀에 실제 입력된 내용:
=ACOS(COS(RADIANS(90-C2))*COS(RADIANS(90-INDIRECT("C"&E2+1)))+SIN(RADIANS(90-C2))*SIN(RADIANS(90-INDIRECT("C"&E2+1)))*COS(RADIANS(D2-INDIRECT("D"&E2+1))))*6371

이렇게 입력하면 E열 상대 지점 셀에서 다른 지점 번호로 바꾸더라도 자동으로 거리를 다시 계산할 수 있다.

H2셀에 실제 입력된 내용:
=SQRT((((C2-INDIRECT("C"&E2+1))*92)^2)+(((D2-INDIRECT("D"&E2+1))*114)^2))

92는 위도 한 단위의 실제 거리, 114는 경도 한 단위의 실제 거리를 표시한 것이다.

모든 수식은 첫 셀에 한 번만 입력한 후 아래로 복사해 사용하면 된다. 이런 방법을 바탕으로 다양한 형태의 양식에 사용할 수 있을 것이다.

참고로, 위 공식들은 예전에 어디서 찾아둔 것으로 비슷한 다른 공식이 여럿 있으므로 필요하다면 웹에서 찾아 다양하게 적용해 보면 좋을 것이다.
"MS Excel " 분류의 다른 글
[Excel(엑셀)] 피벗 테이블의 빈 셀을 특정 값으로 채우기  (1)2011/01/31  
[초등 수학을 위한 엑셀 활용 v0.8] 약수  (0)2013/02/28  
[Excel] 메모에 그림 넣기  (4)2007/07/27  
[Excel 2013] 빠른 채우기의 몇 가지 예  (0)2015/01/20  
[Excel] A열 각 셀에 있는 값을 B열에 일정 횟수 반복하여 입력하기  (0)2011/05/26  
  ◐ 관련 글 ◑   ◐ 100일간 인기 글 ◑
 
Creative Commons License 이 저작물은 크리에이티브 커먼즈 코리아 저작자 표시-비영리-변경 금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.


RSS 2.0 feed
ATOM 1.0 feed
Tag , , , , , , , , , , , , , , ,
Response
You can track responses via RSS / ATOM feed
RSS 2.0 feed
ATOM 1.0 feed
treecap

안녕하세요 위도 경도를 이용한 거리계산에 대한 자료 찾다가 찾아왔어요~
써주신 글 잘 읽었습니다. 죄송하지만 하나만 여쭤봐도 될까 해서요

=ACOS(COS(RADIANS(90-C2))*COS(RADIANS(90-INDIRECT("C"&E2+1)))+SIN(RADIANS(90-C2))*SIN(RADIANS(90-INDIRECT("C"&E2+1)))*COS(RADIANS(D2-INDIRECT("D"&E2+1))))*6371

여기서 "C"&E2 는 어떻게 함수식에 어떤 셀을 어떤식으로 적어야 되는지 궁금해서
뜸금없이 질문올립니다.

바쁘신데 귀찮게 해드려 죄송합니다.

Pak Chulwoo (박철우)

각 지점의 코드명에 해당하는 숫자 번호보다 각 지점의 정보가 입력된 행의 숫자가 1만큼 커서 사용하는 수식입니다. 즉, 서울역은 코드로 숫자 1이지만 2행에 있으므로 서울역의 위도는 C열의 "코드 + 1"행에 있는 셀에 입력되어 있습니다. 서울역의 코드(No.)가 1이므로 서울역의 위도는 C열 1+1행인 C2 셀에 있습니다. 이를 INDIRECT로 표현한 것입니다.