들어가며
"집계 함수" 강의에서 '3차원 참조'에 대해 소개했습니다. 3차원 참조는 편리한 반면, 데이터 형태가 완전히 동일한 경우라야 사용할 수 있었습니다. 혹시라도 기억이 가물가물한 분은 [여기]로 가서 확인해 보고 오시기 바랍니다.
데이터 통합 기능을 이용하면 그 한계를 극복할 수 있습니다. 뿐만 아니라 이 기능을 사용하면 합계 외에도 개수, 평균, 최대, 최소 등 11가지 함수를 사용할 수 있습니다. "데이터 통합"과 "부분합"이라는 중요하고 재미있는 기능에 대해 소개합니다.
어느덧 [Excel 입문] 17번째 강의입니다. 이제 한 강의만 남겨두고 있습니다. 조금 더 힘을 내시기 바랍니다.
여러 시트의 데이터를 하나로 집계하기
필드(날짜, 상품명, 단가, 수량)는 같지만 행의 크기나 순서는 모두 다른 4개 팀의 실적 데이터가 있습니다. 각 팀의 실적을 '통합' 시트에 하나로 합치려면 어떻게 해야 할까요? 이런 경우 데이터 순서나 크기가 다르기 때문에 3차원 참조로는 해결할 수 없습니다.
데이터 통합 기능을 이용하면 간단하게 처리할 수 있습니다.
(1) 결과를 표시할 셀(여기서는 [통합] 시트의 B2 셀)을 선택합니다.
(2) [데이터] 탭 - [데이터 도구] 그룹 - [통합]을 클릭합니다.
(3) [통합] 대화상자에서 [참조]란을 클릭합니다. 첫 번째 통합할 범위를 지정합니다. [동부] 시트의 B1:D20 셀을 마우스로 드래그하면 [참조]란에 주소가 추가됩니다. 제대로 입력되었는 지 확인하고 [추가] 버튼을 클릭합니다.
(4) 동일한 방식으로 서부, 남부, 북부 시트에 있는 범위를 추가합니다. 제대로 했다면 [통합] 대화상자의 [모든 참조 영역]에는 이런 식으로 표시됩니다.
(5) 사용할 레이블의 [첫 행]과 [왼쪽 열], [원본 데이터에 연결] 항목을 클릭하여 체크 표시를 합니다.
(6) [확인] 버튼을 누르면 결과가 표시됩니다. 시트 왼쪽 상단에 있는 윤곽 버튼을 이용하여 데이터를 세부적으로 살펴볼 수도 있습니다. 원본 데이터의 성격상 '단가'는 의미가 없고(개별 단가의 합산이므로) 품목별 '수량'만 참고하면 되겠습니다.
[참고] 데이터 통합에서 사용할 수 있는 함수는 합계 말고도 10가지가 더 있습니다. 대화상자에서 [함수] 아래에 있는 드롭다운을 클릭하면 확인할 수 있습니다.(이미지: 아이엑셀러 닷컴)
화면에 보이는 셀만 복사하는 방법
데이터 통합으로 집계한 결과를 다른 곳에 사용하기 위해 범위를 지정한 다음 복사/붙여넣기 하면 숨겨져 있던 세부 데이터까지 복사됩니다. 결과값만 복사하려면 다음과 같이 해 보세요.
(1) 복사할 영역을 범위로 지정합니다. B2:B76 셀을 선택한 다음, Ctrl 키를 누른 상태에서 E2:E76 셀을 선택합니다.
(2) [홈] 탭 - [편집] 그룹 - [찾기 및 선택] - [이동 옵션]을 선택합니다.
(3) [이동 옵션] 대화상자에서 [화면에 보이는 셀만' 항목을 선택하고 [확인] 버튼을 클릭합니다.
(4) 이렇게 하면 말 그대로 숨겨진 셀을 제외하고 '화면에 표시되어 있는 셀'만 선택됩니다. Ctrl + C를 눌러 복사합니다.
(5) 붙여넣을 곳으로 가서 Ctrl + V를 누르면 숨겨진 셀을 제외하고 화면에 보이는 셀만 복사됩니다.
특정한 품목만 골라서 집계하는 방법
모든 품목이 아니라 사용자가 원하는 품목만 골라서 집계표를 만들 수도 있습니다. 방법은 거의 같으며, 약간의 사전 준비만 하면 됩니다.
(1) 집계할 형태를 만듭니다. 예를 들어 전체 품목 중에서 A, C, E 품목의 수량만 집계한다면 다음과 같은 형태로 작성합니다.
(2) (1)에서 작성한 집계 양식(여기서는 B89:C92 셀)을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [통합]을 클릭합니다. 참조 영역을 추가하는 것은 앞에서 진행한 것과 같습니다. 이전에 지정한 것이 있다면 그대로 표시될 수 있습니다.
(3) [확인] 버튼을 클릭하면 결과가 표시됩니다. 원하는 품목(A, C, E)에 대해서만 집계되었음을 알 수 있습니다.
특정 그룹별로 데이터 요약하기—부분합
특별한 순서 없이 나열되어 있는 자료를 품목별로 수량, 금액의 합계를 내고 요약하고자 합니다. 이 방법을 모른다면 일일이 행을 삽입한 다음 합계를 구해야 합니다. '부분합(SubTotal)' 기능을 알고 있다면 결과를 간단하게 구할 수 있습니다.
단, 부분합을 사용하기 위해서는 부분합을 구할 필드(열)을 기준으로 정렬을 먼저 해 두어야 한다는 사실을 반드시 기억해 두세요.
(1) A열의 셀을 하나 선택하고(예: A2 셀) [데이터] 탭 - [정렬 및 필터] 그룹 - [텍스트 오름차순 정렬]을 선택합니다.
(2) 데이터가 정렬되었다면 데이터 내부의 셀을 하나 선택하고 [데이터] 탭 - [개요] 그룹 - [부분합]을 선택합니다.
(3) [그룹화할 항목]은 [품목]을 선택합니다. [사용할 함수]는 데이터 통합에서와 마찬가지로 '합계'를 비롯한 11개 함수 중에서 선택할 수 있습니다. [부분합 계산 항목]에는 [수량]과 [금액]에 체크를 합니다.
(4) [확인] 버튼을 클릭하면 결과가 표시됩니다. 각 품목별 수량과 금액에 대한 부분합이 자동으로 구해졌습니다. 화면 왼쪽 상단의 숫자 아이콘('윤곽 아이콘'이라고 부릅니다)을 이용하여 수준별로 결과를 표시하거나 숨길 수 있습니다.
부분합 제거하기
부분합을 제거하려면 다음 순서를 따릅니다.
(1) 부분합이 삽입된 데이터 내부의 아무 셀이나 선택합니다.
(2) [데이터] 탭 - [개요] 그룹 - [부분합]을 클릭합니다.
(3) [부분합] 대화상자 하단의 [모두 제거] 버튼을 클릭합니다.
그룹별로 페이지를 나눠서 인쇄하는 방법
부분합이 적용된 결과를 인쇄하면 그룹별 구분 없이 인쇄됩니다. 그룹별로(여기서는 '품목' 별로) 페이지를 구분하여 인쇄할 수 있습니다.
(1) [데이터] 탭 - [개요] 그룹 - [부분합]을 클릭합니다.
(2) 대화상자에서 그룹화할 항목과 함수 등을 지정합니다. 다른 것은 앞에서 소개한 것과 같고, 여기서는 [그룹 사이에서 페이지 나누기] 항목을 체크하고 [확인]을 누릅니다.
(3) [파일] - [인쇄]를 선택합니다. 각 품목별로 별도 페이지로 구분되어 있음을 알 수 있습니다.
페이지마다 표 제목을 반복 인쇄하는 방법
그룹별로 페이지를 나눠서 인쇄하면 두 번째 페이지부터는 표의 제목이 나타나지 않습니다. 이런 경우에는 인쇄 제목을 반복할 행으로 지정해야 합니다. 이와 관련해서는 [여기]를 참고하세요.
나가며
데이터 '통합'과 '부분합'이라고 하는 중요한 것을 알게 되었습니다. 새로운 것을 아시게 된 것, 축하합니다. 이제 우리들의 강의도 끝을 향해 가고 있습니다.
'Excel' 카테고리의 다른 글
| [Excel 입문] 18. 엑셀 데이터 집계의 끝판왕—피벗 테이블 (7) | 2024.04.26 |
|---|---|
| TOP 3 자동 강조 Excel 차트 만드는 방법 (94) | 2024.04.23 |
| [Excel 입문] 16. 조건에 맞는 데이터 자동 강조하기—조건부 서식 (42) | 2024.04.15 |
| Excel에서 대소문자를 변경하는 3가지 방법 (7) | 2024.04.11 |
| Excel 뺄셈 마스터가 되는 방법 (7) | 2024.04.09 |
