첨부파일 피벗테이블팁.xlsx 파일 다운로드 그동안 필자가 포스팅해왔던 블로그 관련 포스팅을 한곳에 모았습니다. 앞의 파일을 다운받으시면 이 포스트를 찾아오시지 않아도 필요할 때마다 참조하실 수 있습니다. 피벗 테이블은 워낙 뛰어난 도구이고 그만큼 수요도 많아서 별도로 정리한 것입니다. 피벗 테이블에 익숙하지 않으신 분들은 이 팁들을 한번씩 쭉 읽어보시면 피벗 테이블을 빠르게 배우실 수 있을 것입니다. 도움이 되셨으면 좋겠습니다. 01 "GetPivotData 생성" 옵션의 의미 02 날짜를 기준으로 동적으로 피벗 테이블 데이터를 분석하려면 03 데이터 모델로 여러 개의 데이터 테이블을 연결해서 엑셀 피벗 테이블을 만들려면 04 동일한 데이터 원본으로 여러 개의 피벗 테이블을 만들었을 때 그룹을 서로 다르게 만들려면 05 엑셀 파일이 열릴 때마다 피벗 테이블이 자동으로 최신 상태로 업데이트되려면 06 여러 개의 데이터 표를 하나의 피벗 테이블에 통합하려면 07 여러 개의 소계를 표시하는 엑셀 피벗 테이블을 만들려면 08 여러 개의 엑셀 피벗 테이블 만들어 하나의 시간 표시 막대로 조절하려면 09 일부 데이터를 삭제한 후 [새로 고침]을 해도 삭제된 데이터가 계속 피벗 테이블에 표시되면 10 파워 피벗으로 피벗 테이블과 피벗 차트를 만들려면 11 피벗 테이블 [하위 수준 표시 사용] 옵션과 [나중에 레이아웃 업데이트] 기능 사용하기 1...
8첨부파일 피벗정렬.xlsx 파일 다운로드 피벗 테이블에 표시되는 데이터 항목을 내가 원하는 임의의 순서로 정렬하는 방법을 알아봅니다. 다음 그림을 보세요. 피벗 테이블이 처음 만들어질 때는 항상 데이터 항목이 오름차순으로 정렬되어 표시됩니다. 앞의 그림에서는 "제품" 필드의 데이터 항목들이 텍스트 오름차순으로 표시되었습니다. 이러한 정렬 순서는 "제품" 필드의 필터 버튼을 클릭하여 표시되는 메뉴에서 조절할 수 있습니다. 하지만 "텍스트 오름차순 정렬"과 "텍스트 내림차순 정렬" 이외에 내가 필요로 하는 다른 순서로 정렬하려면? 이럴 때는 "기타 정렬 옵션"을 사용해야 하는데 그 이전에 약간의 다른 작업이 필요합니다. 다음과 같이 작업하면 됩니다. 1 [파일][옵션]을 클릭해서 [Excel 옵션] 창을 부르고 [고급][일반]에서 [사용자 지정 목록 편집]을 클릭합니다. 2 [사용자 지정 목록]이 표시되면 [목록 가져올 범위] 상자 안을 클릭한 후, 시트에서 N3:N8 범위를 드래그합니다. 그리고 [가져오기] 버튼을 클릭하면 [목록 항목] 상자에 그 내용이 표시됩니다. 이제 [추가] 버튼을 클릭한 후 연속해서 [확인]을 클릭합니다. 여기서는 필자가 시트의 N3:N8 셀에 내가 원하는 정렬 순서를 미리 기록해두고 작업했습니다. 이렇게 데이터를 미리 시트에 기록한 상태가 아니라면 [사용자 지정 목록]에서 [목록 항목] 상자 안에 데이터 항목...
5첨부파일 피벗지연업데이트.xlsx 파일 다운로드 여기서는 피벗 테이블의 2가지 기능을 살펴볼 것입니다. 첫 번째는 [하위 수준 표시 사용] 옵션입니다. 다음과 같이 피벗 테이블에 표시된 값을 더블 클릭하면 새로운 시트가 추가되면서 그 값의 상세 내역이 표시됩니다. 피벗 테이블 작업을 하면서 실수로 더블클릭해서 이런 기능이 작동되는 경우가 있습니다. 데이터의 양이 많은 경우 이런 가벼운 실수가 시간을 소모하고 번잡하게 만듭니다. 다음과 같이 간단하게 옵션을 설정하면 이런 실수를 방지할 수 있답니다. 1 피벗 테이블 내의 셀을 클릭한 상태에서 빠른 메뉴를 불러 [피벗 테이블 옵션]을 선택합니다. 리본 메뉴에서 [피벗 테이블 분석][피벗 테이블][옵션]을 클릭해도 됩니다. 2 [피벗 테이블 옵션] 창에서 [데이터][하위 수준 표시 사용]의 체크 표시를 해제하고 [확인]을 클릭합니다. 3 이제 피벗 테이블의 값을 더블클릭해 보면 그림과 같이 알림 창이 뜨면서 [하위 수준 표시] 작업이 실행되지 않습니다. 두 번째 살펴볼 기능은 [피벗 테이블 필드] 목록에 관한 것입니다. [피벗 테이블 필드] 목록에서 [필터], [행], [열] 영역에 지정되어 있는 필드를 이동하면 그 즉시 시트의 피벗 테이블이 변경됩니다. 이 기능이 편하기는 하지만 데이터가 많고 영역에 지정된 필드가 많은 경우 모든 필드 이동 작업을 마친 후에 한꺼번에 피벗 테이블이 변경되...
6첨부파일 피벗고유개수.xlsx 파일 다운로드 다음 그림을 봅시다. 이 그림에는 2개의 피벗 테이블이 있습니다. "제품별 거래 건수" 피벗 테이블은 제품마다 몇 번 팔렸는지를 보여주고 있습니다. 예를 들어, 바인더는 10번 팔렸고, 보드마커는 8번 팔렸습니다. 이 피벗 테이블은 [행] 영역에 "제품"을 지정하고, [값] 영역에 "거래처" 필드를 지정했습니다. 만일 단독으로 [값] 영역에 텍스트인 "거래처" 필드를 지정하면 왼쪽 엑셀 표에서 "거래처" 열에 있는 데이터의 총 개수인 45가 계산됩니다. 그런데 여기서는 [행] 영역에 "제품" 필드가 지정되어 있어 제품별로 구분되어 표시된 것입니다. "제품별 거래처 개수" 피벗 테이블은 제품마다 제품을 구매한 거래처의 개수를 보여줍니다. 예를 들어, 바인더를 구매한 거래처는 7개이고, 보드마커를 구매한 거래처는 4개입니다. 이렇게 셈하려면 거래처를 중복으로 세지 않아야 할 것입니다. 예를 들어 "신일미디어"가 "바인더"를 3번 구매했어도 "바인더"의 거래처로는 1번만 계산되어야 하지요. 이렇게 피벗 테이블을 만들려면 [행] 영역에 "제품"을 지정하고, [값] 영역에 "거래처" 필드를 지정하는 전체적인 과정은 동일하나 약간의 추가 작업이 필요합니다. 이 포스트에서는 오른쪽에 있는 "제품별 거래처 개수" 피벗 테이블을 만들어봅니다. 1 엑셀 표 안의 셀을 하나 클릭하고 [테이블 디자인][도구][...
7첨부파일 피벗필드하위수준.xlsx 파일 다운로드 앞의 그림에 보듯이 오른쪽에 있는 엑셀 데이터를 기반으로 오른쪽에 "제품 / 영업사원"별 매출금액을 집계한 피벗 테이블이 있습니다. 우리는 이 완성된 피벗 테이블에 "제품"의 하위에 "거래처"를 추가하고 그 과정에서 표시되는 확장(+), 축소(-) 버튼과 "요약" 행을 조절하는 방법을 살펴볼 것입니다. 1 "제품" 필드의 항목을 하나 클릭하고 빠른 메뉴를 불러 [확장/축소][전체 필드 확장]을 클릭합니다. 2 [하위 수준 표시] 창에서 "제품"의 하위 수준으로 추가할 필드를 선택하고 [확인]을 클릭합니다. 3 그러면 그림과 같이 "제품"마다 "거래처" 정보가 표시되고 - (축소) 버튼과 제품별로 "요약" 행이 표시됩니다. 1 번 작업 과정에서 [전체 필드 확장]이 아니라 [확장]을 클릭했으면 그때 클릭된 1개 제품에 대해서만 거래처 정보가 표시됩니다. 4 이제 +, - 버튼을 숨기기 위해서 빠른 메뉴를 불러 [피벗 테이블 옵션]을 클릭합니다. 5 [표시] 탭에서 "확장/축소 단추 표시'에 체크하고 [확인]을 클릭합니다. 6 "요약" 행을 숨기기 위해서 [디자인][레이아웃][부분합]에서 "부분합 표시 안 함"을 클릭합니다. 7 그림과 같이 +, - 버튼과 "요약" 행이 없이 "거래처" 필드가 추가된 피벗 테이블이 만들어졌습니다.
11첨부파일 피벗여러개의소계.xlsx 파일 다운로드 피벗 테이블은 대개 행과 열로 구성되는 2차 테이블 형식이거나, 필터(페이지)를 추가하여 3차원으로 만들기도 합니다. 그 외 큰 항목 아래에 작은 항목들이 속해 있는 계층 구조로 표현할 수도 있습니다. 계층 구조의 경우 항목별로 합계 이외 여러 개의 소계 즉, 평균, 개수, 표준편차, 분산 등의 값을 함께 표시할 수 있습니다. 이 포스트에서는 여러 개의 소계를 표시하는 피벗 테이블을 만들어봅니다. 다음 그림을 보세요. 오른쪽의 피벗 테이블은 영업사원별 / 제품별로 매출 금액이 집계되어 있습니다. 각 영업사원마다 제품과 매출 금액이 나열되는 계층 구조이며, 영업사원별로 매출 금액의 "합계, 개수(건수), 평균" 등 3개의 소계가 표시됩니다. 또한 영업사원들 사이에는 빈 줄이 삽입되어 있고, 피벗 테이블 내에도 셀 구분선이 표시됩니다. 이 피벗 테이블을 만들어 볼 것인데 핵심은 여러 개의 소계를 표시하는 방법입니다. 1 엑셀 표 안의 셀을 하나 클릭하고 [테이블 디자인][도구][피벗 테이블로 요약]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 창에서 "기존 워크시트"에 체크하고 J2 셀을 클릭한 후 [확인]을 클릭합니다. 3 그림과 같이 [행] 필드에 "영업사원"과 "제품"을 차례대로 지정하고 [값] 필드에 "금액"을 지정하여 기본적인 피벗 테이블을 만듭니다. 4 "금액" 중 하나를 ...
5첨부파일 피벗데이터안없어짐.xlsx 파일 다운로드 피벗 테이블을 사용하다 보면 원본 데이터와 피벗 테이블의 데이터가 서로 일치하지 않는 경우가 발생합니다. 이런 문제는 대부분 피벗 테이블 캐시 때문에 발생하지요. 엑셀은 항상 2개의 피벗 테이블을 운영합니다. 엑셀이 동적으로 데이터의 변화를 수용하면서 만드는 원본 피벗 테이블과 사용자에게 보여주기 위해 화면에 표시하는 피벗 테이블 캐시가 있습니다. 처음 피벗 테이블을 만들면 원본 피벗 테이블과 피벗 테이블 캐시는 완전히 똑같습니다. 그러나 데이터를 수정하면 원본 피벗 테이블은 변하지만 피벗 테이블 캐시는 변하지 않습니다. 인위적으로 이 둘을 일치시키는 작업이 바로 [새로 고침]입니다. 이와 같이 2개 버전의 피벗 테이블을 운영하면 사용자가 피벗 테이블에서 필터링을 하거나 그룹을 만들어도 원본 피벗 테이블은 그대로 보존할 수 있습니다. 또한 사용자가 피벗 테이블에서 정렬, 필터링, 그룹, 확장, 축소, ... 등의 변화를 줄 때 이전에 변화된 피벗 테이블로부터 새로운 변화된 피벗 테이블을 만드는 것보다 원본 피벗 테이블을 다시 가져와 작업하는 것이 실행 속도가 빠릅니다. 데이터 사용의 효율과 실행 속도를 높이기 위해서 내부적으로 2개의 피벗 테이블을 운용하는 것입니다. 우리가 여기서 살펴볼 문제도 바로 이 2개의 피벗 테이블의 일치에 관한 문제이지요. 다음 시트를 보세요. 1 왼쪽의 엑...
12첨부파일 피벗필드_복수개_필터지정.xlsx 파일 다운로드 피벗 테이블은 기본적으로 행 필드나 열 필드에 필터 버튼을 표시합니다. 이 필터 버튼을 사용해서 필요한 데이터만 동적으로 추출해서 표시할 수 있지요. 그러나 이 필터 버튼은 동일한 필드에 한 개만 적용할 수 있지요. 다음 그림을 보세요. 왼쪽의 엑셀 표를 기반으로 [행]에 거래처를 지정하고, [열]에 영업사원을 배치한 피벗 테이블이 있습니다. 우리는 이 피벗 테이블에서 거래처 이름이 "문구"로 끝나면서 총합계 금액이 10만을 넘는 데이터만 피벗 테이블에 표시하려고 합니다. 다음과 같이 작업해 봅시다. 1 "거래처" 필터 버튼을 클릭하고 [레이블 필터][끝문자]를 선택합니다. 2 "문구"를 입력한 후 [확인]을 클릭합니다. 3 그러면 그림과 같이 거래처 이름이 "문구"로 끝나는 거래처만 표시됩니다. 4 이제 다시 "거래처" 필터 버튼을 클릭하고 [값 필터][보다 큼...]을 선택합니다. 5 100000을 입력한 후 [확인]을 클릭합니다. 6 피벗 테이블에는 다시 총합계 금액이 10만을 넘는 거래처만 표시됩니다. 이름이 "문구"로 끝나면서 총합계가 10만이 넘는 거래처를 표시하려고 했으나 첫 번째 필터링 작업은 무시되고, 다시 두 번째 필터링 작업이 새로 실행된 것입니다. 묘수가 있습니다! 이 문제는 간단하게 해결됩니다. 다음과 같이 작업하세요. 7 "거래처" 필터 버튼을 클릭하고...
8첨부파일 피벗데이터없는항목표시.xlsx 파일 다운로드 엑셀에서 피벗 테이블을 만들면 원본 데이터에 값이 존재하지 않는 데이터 항목들은 기본적으로는 피벗 테이블 내에 표시되지 않습니다. 그러나 다음 그림을 보면 왼쪽의 엑셀 표에 있는 데이터들을 원본 데이터로 사용하여 만들어진 오른쪽의 피벗 테이블에는 값이 없는 "제품"들도 모두 표시되어 있습니다. 또한 부수적으로 이 피벗 테이블에는 영업사원별로 "빈 줄"이 삽입되어 구분하고 있으며 피벗 테이블 내에는 "셀 구분선"이 표시되어 있습니다. 이 피벗 테이블을 만드는 작업을 해봅시다. 1 표 안의 셀을 하나 클릭한 상태에서 [테이블 디자인][도구][피벗 테이블로 요약]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 창에서 "기본 워크시트"에 체크한 후 N2 셀을 클릭하고 [확인]을 클릭합니다. 3 그림과 같이 "영업사원"과 "제품"을 차례대로 [행] 필드에 지정하고 "금액"을 [값] 필드에 지정합니다. 그러면 영업사원별로 판매 데이터가 있는 제품만 피벗 테이블에 표시됩니다. 4 "제품" 항목을 하나 클릭하고(반드시 제품 중 하나이어야 합니다) 빠른 메뉴를 불러서 [필드 설정]을 클릭합니다. 5 [필드 설정] 창에서 [레이아웃 및 인쇄]의 "데이터가 없는 항목 표시"에 체크한 후 [확인]을 클릭합니다. 그러면 영업사원별로 모든 제품이 표시됩니다. 6 "영업사원" 항목을 하나 클릭하고(반드시...
10피벗 테이블은 마치 데이터베이스처럼 여러 개의 데이터 테이블 간에 관계를 지정해서 연결할 수 있습니다. 연결된 데이터 테이블들은 피벗 테이블 필드 창에 표시되며 사용자는 각 데이터 테이블로부터 필요한 항목을 가져와 하나의 피벗 테이블을 만들 수 있습니다. 다음 그림을 보세요. 첨부파일 피벗테이블_데이터모델(완성).xlsx 파일 다운로드 이 파일에는 3개의 시트가 있고 각 시트에는 회원들에 대한 정보가 기록된 데이터 테이블이 하나씩 있습니다. 그 3개의 데이터 테이블을 서로 연결해서 피벗 테이블을 만들었습니다. 피벗 테이블 필드 창을 보시면 알 수 있을 것입니다. 여기서는 3개의 시트에 3개의 데이터 테이블이 있는 경우를 보이고 있으나, 3개 테이블이 하나의 시트에 있어도 됩니다. 단, 한 가지 유의할 것은 데이터 테이블이 엑셀 [표]로 구성되었다는 점입니다. 엑셀 [표]가 아니면 이 기능을 사용할 수 없습니다. 이 작업을 연습해 봅시다. 다음의 예제 파일을 열고 따라 하세요! 첨부파일 피벗테이블_데이터모델(연습).xlsx 파일 다운로드 1 표 안의 셀을 하나 클릭하고 [테이블 디자인][도구][피벗 테이블로 요약]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 창에서 "데이터 모델에 이 데이터 추가"에 체크하고, "기존 워크시트"에 체크한 후 G2 셀을 클릭하고 [확인]을 클릭합니다. "데이터 모델에 이 데이터 추가"에 체크하는 것...
5피벗 테이블은 다양한 옵션과 도구들을 제공하고 있어 이들을 잘 활용하면 효율적이고 멋있는 보고서를 작성할 수 있지요. 이 포스트에서는 피벗 테이블을 만들면 리본 메뉴에 추가로 표시되는 [디자인] 탭의 [레이아웃] 그룹에 제공되는 [보고서 레이아웃]의 특징과 기능을 살펴봅니다. 첨부파일 피벗레이아웃.xlsx 파일 다운로드 다음 그림에서는 2개 계층으로 구성된 피벗 테이블을 만들었습니다. 이 피벗 테이블은 "영업사원"을 [행] 필드에 지정하고, 그 "영업사원" 밑에 "제품"을 지정했습니다. 그래서 피벗 테이블에는 2개 계층으로 표시됩니다. 그런데 그 2개 계층이 모두 1개의 열에 표시되고 있습니다. "제품"이 약간 들여 쓰기가 되어 있을 뿐 모두 J 열에 표시됩니다. 이것이 "압축 형식"의 피벗 테이블입니다. 피벗 테이블을 만들면 기본적으로 "압축 형식"으로 작성됩니다. 다음 그림은 "개요 형식"을 지정한 예입니다. "개요 형식"은 "압축 형식"과 다르게 계층 구조를 표시할 때 계층별로 다른 열에 표시합니다. 앞의 그림을 보면 "영업사원"은 J 열에 표시되고, "제품"은 K 열에 표시됩니다. 하지만 여전히 계층 구조를 유지하고 있어 상위 계층인 영업사원별 합계가 위쪽에 표시되고 있습니다. 다음 그림은 "테이블 형식"을 지정한 예입니다, "테이블 형식"은 "압축 형식"이나 "개요 형식"과 다르게 계층 구조가 아닙니다. 그래서 "영업사원"과...
14다음 그림을 보세요. 첨부파일 피벗차액계산(완성).xlsx 파일 다운로드 왼쪽의 엑셀 [표]에는 1월과 2월의 매출 데이터가 기록되어 있고, 오른쪽에는 그 원본 데이터를 기반으로 거래처별로 매출 금액을 요약한 피벗 테이블이 있습니다. 피벗 테이블에는 거래처별로 1월과 2월의 매출액 차이도 표시되어 있고, 매출의 증가, 감소를 표시하는 이모지도 표시되어 있습니다. 이 시트를 만들어봅시다. 첨부파일 피벗차액계산(연습).xlsx 파일 다운로드 1 엑셀 표 안의 셀을 하나 클릭하고 [테이블 디자인][도구][피벗 테이블로 요약]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 창에서 "기존 워크시트"에 체크하고 J2 셀을 클릭한 후 [확인]을 클릭합니다. 3 그림과 같이 필드를 지정합니다. "금액"은 3번 지정합니다. 4 날짜 필드에서 "일(날짜)"와 "날짜"를 제거합니다. 5 [디자인][레이아웃][총합계]에서 "열의 총합계만 설정"을 클릭합니다. 6 [합계 : 금액2]의 셀을 하나 클릭하고 빠른 메뉴를 불러 [값 표시 형식][[기준값]과의 차이]를 클릭합니다. 7 표시된 창의 [기준 필드]에서 "개월(날짜)"를 선택하고 [확인]을 클릭합니다. 8 빈 셀이 표시되는 [합계 : 금액2] 열을 [숨기기]를 합니다. 9 그 옆의 [합계:금액3]도 동일하게 작업합니다(6-8 과정을 반복) 10 [합계 : 금액2]의 셀 위에서 빠른 메뉴를 부르고 [...
10첨부파일 피벗데이터막대.xlsx 파일 다운로드 다음 그림과 같이 엑셀의 피벗 테이블에도 [조건부 서식]에서 제공하는 [데이터 막대]를 추가하여 간단한 차트 기능을 만들 수 있습니다. 이 작업을 통해 몇 가지 피벗 테이블 테크닉을 살펴볼 수 있습니다. 제공되는 파일을 열고 따라 하세요! 1 시트에 있는 엑셀 표의 셀을 하나 클릭하고 [테이블 디자인][도구][피벗 테이블로 요약]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 창에서 "기존 워크시트"에 체크하고 J2 셀을 클릭한 후 [확인]을 클릭합니다. 3 그림과 같이 피벗 테이블을 만듭니다. "금액" 필드를 [값]에 2번 반복해서 지정합니다. 4 피벗 테이블의 필드명을 수정하고 숫자 서식을 지정합니다. "행 레이블"을 클릭하고 "거래처"를 입력합니다. "합계 : 금액"을 클릭하고 제일 앞에 한 칸의 공백을 주고 "금액"을 입력합니다. 그냥 "금액"을 입력하면 이미 필드에 있는 이름이라는 메시지가 표시되기 때문에 앞에 공백을 삽입한 것입니다. "합계 : 금액2"는 삭제해야 하는데 그냥 삭제가 안되므로 앞에 공백을 하나 두고 글자들을 삭제했습니다. 숫자를 클릭하고 단축 메뉴를 불러 [필드 표시 형식]을 클릭해서 [셀 서식] 창을 부르고 [숫자]에서 "1000 단위 구분 기호(,) 사용"에 체크하고 [확인]을 클릭합니다. 그냥 간단하게 K3 셀에서 L10 셀까지 범위를 지정하고 단축 메...
5엑셀 피벗 테이블은 동일한 데이터에 대해 얼마든지 여러 개를 만들 수 있습니다. 하지만 새로운 피벗 테이블을 만들 때 늘 처음부터 다시 시작해서 피벗 테이블을 만들지 않아도 되는 경우가 있습니다. 단지 몇 번의 클릭만으로도 여러 개의 세부적인 피벗 테이블을 만들 수 있습니다. 예를 들어, 다음의 시트를 봅시다. 앞의 시트에서는 211개의 데이터가 기록되어 있는 데이터 테이블을 기반으로 피벗 테이블을 만들었습니다. 이 피벗 테이블에는 데이터 전체에 대해 월별, 제품별로 매출액이 집계되어 있습니다. 그런데... 부장님이 전체 피벗 테이블 외에 영업사원별로도 이런 피벗 테이블을 만들어오라고 하십니다. ㅠㅠ 처음부터 다시 피벗 테이블을 만들기 시작해도 됩니다. 하지만 이럴 때 아주 간단하고 신속하게 부장님께 보고드릴 수 있는 방법이 있습니다. 첨부파일 피벗1.xlsx 파일 다운로드 1 [피벗 테이블 필드] 창에서 [필터]에 "영업사원"을 지정합니다. 그러면 피벗 테이블 위에 영업사원 필터가 표시됩니다. 2 [피벗 테이블 분석][피벗 테이블]에서 [옵션]의 버튼을 클릭하고 "보고서 필터 페이지 표시"를 클릭합니다. 3 [보고서 필터 페이지 표시] 창이 표시되면 [확인]을 클릭합니다. 피벗 테이블에 필터가 여러 개일 때는 이 창에서 원하는 필터를 골라서 클릭해야 합니다. 4 영업사원별로 시트가 추가되고 각 시트에 해당 사원의 피벗 테이블이 만들...
19엑셀은 하나의 원본 데이터 테이블로 여러 개의 피벗 테이블을 만들어 다양한 시각에서 데이터를 분석할 수 있습니다. 특히 한 개의 엑셀 시트에 여러 개의 피벗 테이블을 배치하면 한눈에 일목요연하게 파악할 수 있는 보고서를 작성할 수 있으며, 날짜나 시간 항목이 있는 경우는 시간 표시 막대를 사용해서 피벗 테이블의 분석을 동적으로 실행할 수 있습니다. 어러 개의 피벗 테이블을 만들고 이들을 하나의 시간 표시 막대로 조절하는 작업을 해봅시다. 다음의 예제 파일을 사용해서 연습합니다. 첨부파일 피벗시간막대.xlsx 파일 다운로드 1 데이터 테이블 내의 셀을 하나 클릭하고 Ctr+T 키를 누릅니다. [삽입][표][표]를 클릭해도 됩니다. [표 만들기] 창이 표시되면 [확인]을 클릭합니다. 2 [테이블 디자인] 탭에서 필요하면 [표 스타일]을 지정하고 [도구][피벗 테이블로 요약]을 클릭합니다. [표 또는 범위의 피벗 테이블] 창에서 [기존 워크시트]에 체크한 후 피벗 테이블을 시작할 J2 셀을 클릭하고 [확인]을 클릭합니다. 3 그림과 같이 필드를 드래그해서 피벗 테이블을 만듭니다. 4 피벗 테이블의 값 셀을 하나 클릭하고 단축 메뉴를 부른 후 [필드 표시 형식]을 클릭합니다. 5 [범주]에서 "숫자"를 클릭하고 "100 단위 구분 기호 사용"에 체크한 후 [확인]을 클릭합니다. 6 피벗 테이블의 "행 레이블","열 레이블"을 수정하기 위해 ...
7원본 데이터를 기반으로 피벗 테이블을 만들어 놓은 상태에서 원본 데이터에 데이터가 추가, 삭제되거나 열이 추가, 삭제되면 그 변경된 내용이 피벗테이블에 자동으로 반영되지 않지요. 그런 변화를 피벗 테이블에 반영하려면 다음과 같이 2가지 방법이 있습니다. ▶ 원본 데이터가 엑셀 [표]가 아닌 경우 [피벗 테이블 분석][데이터][데이터 원본 변경] 메뉴를 이용해서 데이터의 범위를 새로 지정해야 합니다. ▶ 원본 데이터가 엑셀 [표]인 경우 [피벗 테이블 분석][데이터][새로 고침]을 클릭하거나 피벗 테이블 내에서 단축 메뉴를 불러 [새로 고침]을 클릭하면 됩니다. 피벗 테이블을 사용할 때 가능한 원본 데이터를 엑셀 [표]로 구성할 것을 추천합니다. 엑셀 [표]를 사용하면 여러 가지 다른 장점도 있지만 원본 데이터의 변화를 피벗 테이블에 반영하기 위해 사용자가 [새로 고침] 메뉴를 클릭하는 것을 잊었더라도 자동으로 변화를 반영하는 방법도 있습니다. 피벗 테이블의 옵션을 하나 설정하기만 하면 됩니다. 다음의 예제 파일로 테스트해 봅시다. 첨부파일 피벗_새로고침.xlsx 파일 다운로드 1 예제 시트에는 엑셀 [표]와 그 [표]를 기반으로 작성된 피벗 테이블이 있습니다. 2 피벗 테이블 내의 셀을 클릭하고 [피벗 테이블 분석][피벗 테이블][옵션]을 클릭합니다. 또는 단축 메뉴를 불러 [피벗 테이블 옵션]을 클릭해도 됩니다. 3 [피벗 테이블 ...
10엑셀의 필터 기능을 이용하면 대량의 데이터를 기록한 데이터 테이블에서 원하는 데이터만을 자유롭게 추출할 수 있습니다. 필터도 편리한 기능이지만 슬라이서를 추가하고 SUBTOTAL 함수를 사용하면 한층 더 세련되고 편리한 데이터 추출 작업을 할 수 있습니다. 다음의 완성된 예제 파일을 봅시다. 첨부파일 슬라이서필터링_완성.xlsx 파일 다운로드 그림과 같이 "매출집계표" 시트에 211개의 데이터가 기술되어 있고, 그 데이터 위에는 3개 항목에 대한 집계값이 표시됩니다. 데이터의 오른쪽에는 필터 역할을 대신할 3개의 슬라이서가 있습니다. 오른쪽에 있는 슬라이서에서 원하는 항목을 클릭하면 시트에 표시되는 데이터가 달라지고 위쪽의 집계값도 알맞게 변합니다. 이 작업은 기본적으로 필터에 의해 행들이 숨겨지기 때문에 집계값들을 데이터의 위쪽에 표시해야 행들이 숨겨져도 영향을 받지 않습니다. 슬라이서 제목 줄에는 [다중 선택] 버튼과 [필터 해제] 버튼이 있습니다. 이제 다음의 연습 파일을 열고 시작해 봅시다. 이 작업에서 이름 정의를 사용하는데 이름은 파일 단위로 공유되기 때문에 별도의 연습 파일을 사용하고 있습니다. 첨부파일 슬라이서필터링_연습.xlsx 파일 다운로드 1 데이터 셀을 하나 클릭하고 Ctrl + T 키를 누릅니다. 메뉴에서 [삽입][표][표]를 클릭해도 됩니다. 표시되는 [표 만들기] 창에서 "머리글 포함"에 체크되었음을 확인하...
6피벗 테이블을 만들면 기본적으로 "행 레이블", "열 레이블", "합계 : 금액", "총합계"와 같은 필드 이름이 표시됩니다. 이 자동 표시된 필드 이름들을 수정하는 방법들을 알아봅시다. 다음의 예제 시트를 보세요. 1 "매출집계표" 시트에 제품별 금액과 수량을 집계한 피벗 테이블이 있습니다. 이 피벗 테이블의 필드 이름인 "행 레이블", "총합계", "합계 : 금액", "합계 : 수량" 등을 수정할 것입니다. 2 "행 : 레이블", "열 : 레이블"과 같은 이름을 수정하는 방법은 2가지입니다. 첫번째는 메뉴를 사용하는 방법입니다. 그림과 같이 피벗 테이블 내의 셀을 하나 클릭한 상태에서 [디자인][레이아웃][보고서 레이아웃]에서 [개요 형식으로 표시] 또는 [테이블 형식으로 표시] 중 하나를 클릭합니다. 3 그러면 알맞은 이름으로 자동 수정됩니다. 피벗 테이블은 처음 만들어질 때 [압축 형식]으로 표시됩니다. 이 형식을 바꾸면 알맞은 이름으로 변환됩니다. 두번째 방법은 아주 간단합니다. 그냥 "행 : 레이블" 글자를 클릭하고 새 이름을 입력합니다. 수정할 셀이 많지 않으면 이 방법이 더 간단합니다. 4 "총합계"도 그냥 글자를 클릭하고 새 이름을 입력하면 됩니다. 5 "합계 : 금액"을 "금액"으로 수정하거나 "합계 : 수량"을 "수량"으로 수정하면 그림과 같이 메시지 창이 표시됩니다. 수정된 필드 이름이 원본 데이터의 열제목과 같...
13사용자가 피벗 테이블을 만들면 엑셀은 내부적으로 피벗 캐시(Pivot Cache)라는 임시 메모리를 사용합니다. 피벗 캐시에는 피벗 테이블의 원본 데이터가 그대로 복사됩니다. 피벗 테이블을 만든 상태에서 사용자가 원본 데이터를 수정해도 그 수정된 내용이 즉시 피벗 테이블에 적용되지 않고 [새로고침] 버튼을 클릭해야 비로소 피벗 테이블도 수정됩니다. 이것은 피벗 테이블은 원본 데이터가 아니라 피벗 캐시에 있는 데이터를 사용해서 표시되기 때문입니다. [새로고침] 버튼은 원본 데이터를 피벗 캐시에 적용하는 역할을 합니다. 다시 말해서 [새로고침] 버튼은 피벗 캐시를 원본 데이터와 일치시키는 역할을 하지요. 왜 이렇게 복잡하게 했을까요? 그냥 원본 데이터 수정하면 바로 피벗 테이블이 수정되게 하면 될 것을... 이게 다 사용자의 편의를 위한 것이라는 걸 모르면 손해! 다음의 예제 파일을 보면서 알아봅시다. 첨부파일 피벗삭제복구.xlsx 파일 다운로드 1 "매출 집계표" 시트에는 211개의 매출 데이터가 날짜순으로 기록되어 있고, 그 옆에는 매출 데이터를 원본 데이터로 사용해서 제품별 영업사원별로 금액을 집계한 피벗 테이블이 있습니다. 2 원본 데이터에서 첫 3개 데이터의 제품을 "연필"로 수정했습니다. 하지만 피벗 테이블은 변화가 없습니다. 3 피벗 테이블의 셀을 하나 클릭하고 [피벗 테이블 분석][데이터][새로고침]을 클릭하면 비로소 피벗...
6엑셀의 피벗 테이블을 이용하면 함수를 사용하지 않고도 간단하게 다양한 개수 세기를 할 수 있습니다. 피벗 테이블의 [값] 필드에 텍스트 항목을 지정하면 그 항목의 개수가 구해지는 기능을 이용하는 것입니다. 다음의 예제 시트로 연습해 봅시다. 첨부파일 피벗개수세기.xlsx 파일 다운로드 예제 시트에는 211개의 데이터가 기술되어 있습니다. 먼저 피벗 테이블부터 만들어봅시다. 1 데이터 셀을 하나 클릭하고 [삽입][표][피벗 테이블]을 클릭합니다. 2 [표 또는 범위의 피벗 테이블] 상자에서 [기존 워크시트]에 체크하고 시트의 J3 셀을 클릭한 후 [확인]을 클릭합니다. 이제 거래처별로 거래건수를 구해봅시다. 3 [피벗 테이블 필드] 창에서 "거래처"를 [행]으로 드래그하고 "영업사원"을 [값]으로 드래그합니다. 그러면 그림과 같이 피벗 테이블에 거래처별로 거래건수가 표시됩니다. [행]에 "거래처"를 드래그했기 때문에 데이터에서 거래처를 하나씩 추출해서 행에 나열합니다. (이 기능은 데이터에서 유일하게 하나씩만 텍스트 항목을 추출하는 경우에도 사용할 수 있습니다) [값]에 "영업사원"을 드래그하면 "영업사원"은 텍스트이기 때문에 자동으로 영업사원의 개수가 구해집니다. 거래별로 영업사원의 개수를 구하면 거래처별로 거래건수를 구하는 것이 되지요. 물론 이 경우 [값]에 "영업사원" 대신에 다른 텍스트 항목인 "제품"을 드래그해도 됩니다. 이...