피벗 테이블 Excel에서 가장 강력한 도구 중 하나입니다. 몇 번의 마우스 클릭으로 대용량 데이터의 다양한 요약을 분석하고 요약할 수 있습니다. 이 기사에서는 피벗 테이블에 대해 알아보고 피벗 테이블이 무엇인지 이해하고 생성 및 사용자 정의하는 방법을 배웁니다.
이 문서는 Excel 2010을 사용하여 작성되었습니다. 피벗 테이블의 개념은 수년 동안 크게 변경되지 않았지만 새 버전의 Excel에서 피벗 테이블을 만드는 방법은 약간씩 다릅니다. 2010이 아닌 Excel 버전이 있는 경우 이 문서의 스크린샷이 화면에 표시되는 것과 다를 수 있습니다.
역사의 비트
스프레드시트 소프트웨어의 초창기에는 로터스 1-2-3이 룰볼이었습니다. 그 지배력은 너무나 완벽해서 로터스의 대안으로 자체 소프트웨어(엑셀)를 개발하려는 마이크로소프트의 노력은 시간 낭비처럼 보였다. 이제 2010년으로 빨리 가십시오! Excel은 역사상 Lotus 코드보다 더 많이 스프레드시트를 지배하고 있으며 여전히 Lotus를 사용하는 사람들의 수는 거의 XNUMX에 가깝습니다. 어떻게 이런 일이 일어날 수 있습니까? 이렇게 극적인 사건이 일어난 이유는 무엇일까요?
분석가는 두 가지 주요 요인을 식별합니다.
- 첫째, Lotus는 Windows라고 하는 이 새로운 GUI 플랫폼이 오래가지 못할 일시적인 유행이라고 판단했습니다. 그들은 DOS 버전의 소프트웨어가 소비자에게 필요한 모든 것이 될 것이라고 예측하면서 Windows 버전의 Lotus 1-2-3(그러나 몇 년 동안만)의 구축을 거부했습니다. Microsoft는 자연스럽게 Windows용 Excel을 개발했습니다.
- 둘째, Microsoft는 Lotus 1-2-3에서 사용할 수 없었던 PivotTables라는 도구를 Excel에 도입했습니다. Excel에 독점적인 피벗 테이블은 매우 유용하여 사람들이 새로운 Excel 소프트웨어 제품군이 없는 Lotus 1-2-3을 계속 사용하기보다는 계속 사용하는 경향이 있음이 입증되었습니다.
일반적으로 Windows의 성공을 과소평가하는 것과 함께 피벗 테이블은 Lotus 1-2-3의 죽음의 행진을 했으며 Microsoft Excel의 성공을 이끌었습니다.
피벗 테이블이란 무엇입니까?
그렇다면 피벗 테이블이 무엇인지 특성화하는 가장 좋은 방법은 무엇입니까?
간단히 말해서 피벗 테이블은 이 데이터의 분석을 용이하게 하기 위해 생성된 일부 데이터의 요약입니다. 수동으로 만든 합계와 달리 Excel 피벗 테이블은 대화형입니다. 일단 생성되면 원하는 그림이 표시되지 않는 경우 쉽게 수정할 수 있습니다. 마우스를 몇 번만 클릭하면 열 머리글이 행 머리글이 되거나 그 반대가 되도록 합계를 뒤집을 수 있습니다. 피벗 테이블로 많은 일을 할 수 있습니다. 피벗 테이블의 모든 기능을 말로 설명하려고 하는 대신 실제로 시연하는 것이 더 쉽습니다 ...
피벗 테이블로 분석하는 데이터는 임의적일 수 없습니다. 일종의 목록과 같은 원시 데이터여야 합니다. 예를 들어, 지난 XNUMX개월 동안 회사에서 만든 판매 목록이 될 수 있습니다.
아래 그림에 표시된 데이터를 보십시오.
이것은 이미 요약되었으므로 원시 원시 데이터가 아닙니다. 셀 B3에서 $30000를 볼 수 있으며 이는 아마도 James Cook이 30000월에 한 총 결과일 것입니다. 그렇다면 원본 데이터는 어디에 있습니까? XNUMX만원이라는 수치는 어디서 나온건가요? 이 월별 총계가 파생된 원래 판매 목록은 어디에 있습니까? 누군가가 지난 XNUMX개월 동안의 모든 판매 데이터를 정리하고 정렬하여 우리가 볼 수 있는 합계 테이블로 변환하는 일을 훌륭하게 수행했음이 분명합니다. 얼마나 걸렸어요? 시? 열시?
사실 위의 테이블은 피벗 테이블이 아닙니다. 다른 곳에 저장된 원시 데이터에서 손으로 제작되었으며 처리하는 데 최소 몇 시간이 걸렸습니다. 피벗 테이블을 사용하여 불과 몇 초 만에 이러한 요약 테이블을 만들 수 있습니다. 방법을 알아 봅시다 ...
원래 판매 목록으로 돌아가면 다음과 같습니다.
피벗 테이블을 사용하여 이 거래 목록에서 단 몇 초 만에 위에서 분석한 Excel에서 월별 판매 보고서를 생성할 수 있다는 사실에 놀랄 수 있습니다. 예, 우리는 그 이상을 할 수 있습니다!
피벗 테이블을 만드는 방법은 무엇입니까?
먼저 Excel 시트에 원본 데이터가 있는지 확인합니다. 금융 거래 목록이 가장 일반적으로 발생합니다. 실제로 직원 연락처 정보, CD 모음 또는 회사의 연료 소비 데이터와 같은 모든 목록이 될 수 있습니다.
그래서 우리는 Excel을 시작하고 ... 그러한 목록을 로드합니다.
이 목록을 Excel에서 연 후에는 피벗 테이블 만들기를 시작할 수 있습니다.
이 목록에서 아무 셀이나 선택하십시오.
그런 다음 탭에서 삽입 (삽입) 선택 명령 피벗 테이블 (피벗 테이블):
대화 상자가 나타납니다 피벗 테이블 만들기 (피벗 테이블 만들기) 다음 두 가지 질문이 있습니다.
- 새 피벗 테이블을 만드는 데 사용할 데이터는 무엇입니까?
- 피벗 테이블을 어디에 둘 것인가?
이전 단계에서 이미 목록 셀 중 하나를 선택했으므로 전체 목록이 자동으로 선택되어 피벗 테이블을 생성합니다. 다른 범위, 다른 테이블, 심지어 Access 또는 MS-SQL 데이터베이스 테이블과 같은 일부 외부 데이터 원본을 선택할 수 있습니다. 또한 새 시트 또는 기존 시트 중 하나에서 새 피벗 테이블을 배치할 위치를 선택해야 합니다. 이 예에서는 다음 옵션을 선택합니다. 새 워크 시트 (새 시트로):
Excel은 새 시트를 만들고 그 위에 빈 피벗 테이블을 배치합니다.
피벗 테이블의 셀을 클릭하자마자 다른 대화 상자가 나타납니다. 피벗 테이블 필드 목록 (피벗 테이블 필드).
대화 상자 상단의 필드 목록은 원래 목록의 모든 제목 목록입니다. 화면 아래쪽에 있는 XNUMX개의 빈 영역을 사용하면 데이터를 요약할 방법을 피벗 테이블에 알릴 수 있습니다. 이 영역이 비어 있는 한 테이블에도 아무 것도 없습니다. 우리가 해야 할 일은 상단 영역에서 아래의 빈 영역으로 제목을 드래그하는 것입니다. 동시에 지침에 따라 피벗 테이블이 자동으로 생성됩니다. 실수한 경우 하단 영역에서 제목을 제거하거나 다른 항목을 끌어 교체할 수 있습니다.
Area 마케팅은: (의미)는 아마도 네 가지 중 가장 중요할 것입니다. 이 영역에 배치되는 제목에 따라 요약될 데이터(합계, 평균, 최대값, 최소값 등)가 결정됩니다. 이는 거의 항상 숫자 값입니다. 이 지역의 좋은 후보는 제목 아래의 데이터입니다. 금액 (비용) 당점 원래 테이블. 이 제목을 영역으로 드래그 마케팅은: (값):
제목 참고하세요 금액 이제 체크 표시로 표시되고 해당 영역에서 마케팅은: (값) 항목이 나타났습니다 금액의 합계 (금액 필드 금액), 열을 나타내는 금액 요약했다.
피벗 테이블 자체를 보면 열의 모든 값의 합계가 표시됩니다. 금액 원래 테이블.
그래서 첫 번째 피벗 테이블이 생성되었습니다! 편리하지만 특별히 인상적이지는 않습니다. 우리는 현재 가지고 있는 것보다 데이터에 대한 더 많은 정보를 원할 것입니다.
원본 데이터로 돌아가 이 합계를 분할하는 데 사용할 수 있는 하나 이상의 열을 식별해 보겠습니다. 예를 들어 총 판매액이 각 판매자에 대해 개별적으로 계산되는 방식으로 피벗 테이블을 구성할 수 있습니다. 저것들. 행은 회사의 각 영업 사원의 이름과 총 판매 금액과 함께 피벗 테이블에 추가됩니다. 이 결과를 얻으려면 제목을 드래그하십시오. 영업 담당자 (영업 담당자) 지역에 행 레이블 (문자열):
더 흥미로워진다! 우리의 피벗 테이블이 모양을 갖추기 시작했습니다...
혜택이 보이시나요? 몇 번의 클릭으로 수동으로 생성하는 데 매우 오랜 시간이 걸렸을 테이블을 생성했습니다.
우리는 무엇을 더 할 수 있습니까? 어떤 의미에서는 피벗 테이블이 준비되었습니다. 원본 데이터의 유용한 요약을 만들었습니다. 중요한 정보는 이미 받았습니다! 이 문서의 나머지 부분에서는 더 복잡한 피벗 테이블을 만드는 방법과 사용자 지정하는 방법을 알아보겠습니다.
피벗 테이블 설정
먼저 XNUMX차원 피벗 테이블을 만들 수 있습니다. 열 머리글을 사용하여 이 작업을 수행해 보겠습니다. 지불 방법 (결제 방법). 제목을 드래그하면 됩니다. 지불 방법 지역으로 열 레이블 (열):
우리는 결과를 얻습니다:
아주 멋져 보인다!
이제 XNUMX차원 테이블을 만들어 보겠습니다. 그런 테이블은 어떻게 생겼을까요? 보자…
드래그 헤더 묶음 (복합) 지역으로 보고서 필터 (필터):
그가 어디에 있는지 주목하십시오 ...
이렇게 하면 "비용이 지불된 휴가 단지"를 기준으로 보고서를 필터링할 수 있습니다. 예를 들어 모든 컴플렉스에 대한 판매자별 및 지불 방법별 내역을 확인하거나 몇 번의 마우스 클릭으로 피벗 테이블의 보기를 변경하고 콤플렉스를 주문한 사람들에게만 동일한 내역을 표시할 수 있습니다. 선시커.
따라서 이것을 올바르게 이해하면 피벗 테이블을 XNUMX차원이라고 할 수 있습니다. 설정을 계속합시다...
갑자기 수표와 신용 카드 결제(즉, 무현금 결제)만 피벗 테이블에 표시되어야 하는 경우 제목 표시를 끌 수 있습니다. 현금 (현금). 이를 위해 옆에 열 레이블 아래쪽 화살표를 클릭하고 드롭다운 메뉴에서 확인란의 선택을 취소합니다. 현금:
이제 피벗 테이블이 어떻게 생겼는지 봅시다. 컬럼은 보시다시피 현금 그녀에게서 사라졌다.
Excel에서 피벗 테이블 서식 지정
피벗 테이블은 분명히 매우 강력한 도구이지만 지금까지는 결과가 약간 평범하고 지루해 보입니다. 예를 들어, 우리가 더한 숫자는 달러 금액처럼 보이지 않습니다. 단지 숫자일 뿐입니다. 이 문제를 해결합시다.
이러한 상황에서 익숙한 작업을 수행하고 전체 테이블(또는 전체 시트)을 선택하고 도구 모음의 표준 숫자 서식 버튼을 사용하여 원하는 서식을 설정하고 싶은 유혹이 있습니다. 이 접근 방식의 문제는 미래에 피벗 테이블의 구조를 변경하면(99%의 확률로 발생) 서식이 손실된다는 것입니다. 우리에게 필요한 것은 그것을 (거의) 영구적으로 만드는 방법입니다.
먼저 엔트리를 찾아보자 금액의 합계 in 마케팅은: (값)을 클릭하고 클릭합니다. 나타나는 메뉴에서 항목을 선택하십시오. 값 필드 설정 (값 필드 옵션):
대화 상자가 나타납니다 값 필드 설정 (값 필드 옵션).
버튼을 클릭 숫자 형식 (숫자 형식)을 선택하면 대화 상자가 열립니다. 셀 서식 (셀 형식):
목록에서 범주 (숫자 형식) 선택 회계 (재무) 소수점 이하 자릿수를 XNUMX으로 설정합니다. 이제 몇 번 누르십시오. OK피벗 테이블로 돌아갑니다.
보시다시피 숫자는 달러 금액으로 형식이 지정됩니다.
서식을 지정하는 동안 전체 피벗 테이블의 서식을 설정해 보겠습니다. 이를 수행하는 방법에는 여러 가지가 있습니다. 우리는 더 간단한 것을 사용합니다 ...
클릭 피벗 테이블 도구: 디자인 (피벗 테이블 작업: 생성자):
그런 다음 섹션의 오른쪽 하단 모서리에 있는 화살표를 클릭하여 메뉴를 확장합니다. 피벗 테이블 스타일 (피벗 테이블 스타일): 인라인 스타일의 광범위한 컬렉션을 보려면 다음을 수행합니다.
적합한 스타일을 선택하고 피벗 테이블에서 결과를 확인합니다.
Excel의 기타 피벗 테이블 설정
날짜별로 데이터를 필터링해야 하는 경우가 있습니다. 예를 들어, 거래 목록에는 많은 날짜가 있습니다. Excel은 일, 월, 연도 등으로 데이터를 그룹화하는 도구를 제공합니다. 어떻게 되었는지 봅시다.
먼저 항목을 제거하십시오. 지불 방법 지역에서 열 레이블 (열). 이렇게 하려면 제목 목록으로 다시 드래그하고 그 자리에서 제목을 이동합니다. 예약 날짜 (예약 날짜):
보시다시피, 이것은 일시적으로 피벗 테이블을 쓸모없게 만들었습니다. Excel은 거래가 이루어진 각 날짜에 대해 별도의 열을 만들었습니다. 결과적으로 우리는 매우 넓은 테이블을 얻었습니다!
이 문제를 해결하려면 날짜를 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 선택하십시오. 그룹 (그룹):
그룹화 대화 상자가 나타납니다. 우리는 선택합니다 개월 (월)을 클릭하고 OK:
짜잔! 이 표가 훨씬 더 유용합니다.
그건 그렇고,이 테이블은 판매 합계가 수동으로 집계 된 기사 시작 부분에 표시된 테이블과 거의 동일합니다.
당신이 알아야 할 또 다른 매우 중요한 포인트가 있습니다! 하나가 아닌 여러 수준의 행(또는 열) 머리글을 만들 수 있습니다.
... 그리고 그것은 다음과 같이 보일 것입니다 ...
열 머리글(또는 필터)에서도 동일한 작업을 수행할 수 있습니다.
테이블의 원래 형식으로 돌아가서 합계 대신 평균을 표시하는 방법을 살펴보겠습니다.
시작하려면 금액의 합계 그리고 나타나는 메뉴에서 값 필드 설정 (값 필드 옵션):
목록 값 필드 요약 (조작) 대화 상자에서 값 필드 설정 (값 필드 옵션) 선택 평균 (평균):
그와 동시에 우리가 여기 있는 동안 우리는 바꾸자 사용자 이름 (사용자 지정 이름) 금액의 평균 (금액 필드 금액) 더 짧은 것으로. 이 필드에 다음과 같이 입력하십시오. 평균:
PR 기사 OK 그리고 무슨 일이 일어나는지 보십시오. 모든 값이 합계에서 평균으로 변경되었으며 테이블 헤더(왼쪽 상단 셀)가 평균:
원하는 경우 하나의 피벗 테이블에 배치된 금액, 평균 및 숫자(매출)를 즉시 얻을 수 있습니다.
다음은 빈 피벗 테이블로 시작하여 이 작업을 수행하는 방법에 대한 단계별 가이드입니다.
- 드래그 헤더 영업 담당자 (영업 담당자) 지역에 열 레이블 (열).
- 제목을 세 번 드래그 금액 (비용) 지역에 마케팅은: (값).
- 첫 번째 필드의 경우 금액 제목을 로 변경 금액 (금액)이며 이 필드의 숫자 형식은 회계 (재정적인). 소수점 이하 자릿수는 XNUMX입니다.
- 두 번째 필드 금액 name 평균e, 그것에 대한 작업을 설정 평균 (평균) 및 이 필드의 숫자 형식도 다음으로 변경됩니다. 회계 (재무) 소수점 이하 자릿수 XNUMX입니다.
- 세 번째 필드의 경우 금액 제목을 정하다 카운트 그리고 그를 위한 수술 - 카운트 (수량)
- . 열 레이블 (열) 필드가 자동으로 생성됨 ∑ 값 (Σ 값) – 영역으로 드래그 행 레이블 (윤곽)
다음은 우리가 끝낼 내용입니다:
총금액, 평균가치, 판매횟수까지 모두 하나의 피벗테이블로!
결론
Microsoft Excel의 피벗 테이블에는 많은 기능과 설정이 포함되어 있습니다. 그런 작은 기사에서 그것들을 모두 다루기에는 가깝지 않습니다. 피벗 테이블의 모든 가능성을 완전히 설명하려면 작은 책이나 큰 웹 사이트가 필요합니다. 대담하고 호기심 많은 독자는 피벗 테이블을 계속 탐색할 수 있습니다. 이렇게 하려면 피벗 테이블의 거의 모든 요소를 마우스 오른쪽 버튼으로 클릭하고 어떤 기능과 설정이 열리는지 확인합니다. 리본에는 두 개의 탭이 있습니다. 피벗 테이블 도구: 옵션 (분석) 및 디자인 (건설자). 실수를 두려워하지 마십시오. 언제든지 피벗 테이블을 삭제하고 다시 시작할 수 있습니다. DOS와 Lotus 1-2-3을 오랫동안 사용해오던 사용자에게는 한 번도 없었던 기회가 있습니다.