Microsoft Excel에서 피벗 테이블 작업

피벗 테이블은 Microsoft Excel의 가장 강력한 기능 중 하나입니다. 이 도구를 사용하면 몇 번의 마우스 클릭만으로 많은 양의 데이터를 분석하고 요약 할 수 있습니다. 이 기사에서는 피벗 테이블을 탐색하고, 피벗 테이블을 이해하고, 피벗 테이블을 만들고 사용자 지정하는 방법을 배웁니다..

참고 :이 문서는 Excel 2010 (베타)을 사용하여 작성되었습니다. 피벗 테이블 개념은 지난 몇 년 동안 거의 변하지 않았지만 Excel을 반복 할 때마다 피벗 테이블을 만드는 방법이 변경되었습니다. 2010 년이 아닌 Excel 버전을 사용하는 경우이 기사에서 볼 수있는 것과 다른 화면이 필요합니다..

작은 역사

스프레드 시트 프로그램의 초기에 Lotus 1-2-3이 그 주춧돌을 지배했습니다. 그 지배력은 너무나도 완벽해서 사람들은 Microsoft가 자신의 스프레드 시트 소프트웨어 (Excel)를 개발하여 Lotus와 경쟁하는 것이 시간 낭비라고 생각했습니다. 2010 년으로의 플래시 포워드와 스프레드 시트 시장에서의 Excel의 우위는 Lotus의 이전 버전보다 컸지 만 Lotus 1-2-3을 계속 실행하는 사용자의 수는 0에 가까워졌습니다. 어떻게 이런일이 일어 났습니까? 그러한 운명의 극적인 역전을 초래 한 원인?

업계 분석가들은 두 가지 요소에 대해 다음과 같이 설명했습니다. 첫째, 로터스는 "Windows"라고 불리는이 새로운 GUI 플랫폼이 결코 벗어날 수없는 지나친 유행 이었음을 결정했습니다. 그들은 DOS 버전의 소프트웨어가 누구나 필요로 할 것이라고 예측하면서 Lotus 1-2-3의 Windows 버전을 만드는 것을 거부했습니다 (몇 년 동안, 어쨌든). Microsoft는 당연히 Windows 용 Excel을 독점 개발했습니다. 둘째, Microsoft는 Lotus가 1-2-3에서 제공하지 않은 Excel 용 기능을 개발했습니다. 피벗 테이블.  Excel 전용 PivotTables 기능은 사람들이 전혀 새로운 프로그램 (Excel)을 배우지 않고 (1-2-3) 프로그램을 고수하기보다는 기꺼이 유용하다고 판단했습니다. 이 기능 중 하나는 Windows의 오판과 함께 Lotus 1-2-3의 죽음이었고 Microsoft Excel의 성공의 시작이었습니다.

피벗 테이블 이해

따라서 피벗 테이블이란 정확히 무엇입니까??

간단히 말하면 피벗 테이블은 해당 데이터를 쉽게 분석 할 수 있도록 작성된 일부 데이터의 요약입니다. 그러나 수동으로 만든 요약과 달리 Excel 피벗 테이블은 대화 형입니다. 생성 한 후에는 원하는 데이터에 대한 정확한 통찰력을 제공하지 않으면 쉽게 변경할 수 있습니다. 몇 번의 클릭으로 요약본을 "피봇 팅 (pivoted)"할 수 있습니다. 즉, 열 표제가 행 표제가되고 그 반대의 경우도 마찬가지입니다. 할 수있는 일이 훨씬 더 많습니다. 피벗 테이블의 모든 기능을 설명하려고 시도하지 않고 간단히 설명합니다 ...

피벗 테이블을 사용하여 분석 한 데이터는 어떤 데이터 - 노골적인 이전에 처리되지 않은 (요약되지 않은) 데이터 - 일반적으로 일종의 목록. 예를 들어 지난 6 개월 동안 회사의 판매 거래 목록 일 수 있습니다.

아래에 표시된 데이터를 검사하십시오.

이것이 아니 원시 데이터. 사실, 그것은 이미 어떤 종류의 요약입니다. 셀 B3에서 우리는 3 월에 제임스 쿡의 매출 총계 인 3 만 달러를 볼 수 있습니다. 원시 데이터는 어디에 있습니까? 우리는 어떻게 3 만 달러의 인물에 도착 했습니까? 이 수치가 생성 된 판매 거래의 원래 목록은 어디에 있습니까? 지난 6 개월 간 모든 판매 거래를 위에서 요약 한 내용과 대조해야하는 누군가가 있어야한다는 것은 분명합니다. 얼마나 걸릴 거라고 생각하니? 한시간? 십?

아마, 예. 위의 스프레드 시트는 실제로 아니 피벗 테이블. 그것은 다른 곳에 저장된 원시 데이터로부터 수동으로 생성되었고 실제로 컴파일하는데 2 시간이 걸렸습니다. 그러나 그것은 정확하게 할 수 있었다 피벗 테이블을 사용하여 만들면 몇 초 만에 완료됩니다. 방법을 찾아 봅시다 ...

판매 거래의 원래 목록을 추적하면 다음과 같이 보일 수 있습니다.

Excel의 피벗 테이블 기능을 사용하면 마우스 클릭 몇 번만으로 몇 초 만에 위와 유사한 월별 판매 요약을 만들 수 있다는 사실에 놀랄 것입니다. 우리는 이것을 할 수 있습니다.!

피벗 테이블을 만드는 방법

먼저 Excel의 워크 시트에 원시 데이터가 있는지 확인하십시오. 금융 거래 목록은 전형적이지만 회사의 차량 목록에 대한 직원 연락처 세부 정보, CD 수집 또는 연료 소비 수치 등 모든 것에 대한 목록 일 수 있습니다..

그래서 우리는 Excel을 시작하고 우리는 그런 목록을로드합니다 ...

Excel에서 목록을 열면 피벗 테이블을 만들 준비가 된 것입니다..

목록에서 하나의 셀을 클릭하십시오 :

그런 다음 끼워 넣다 탭에서 피벗 테이블 상:

그만큼 피벗 테이블 만들기 상자가 나타나면 두 가지 질문을합니다. 새로운 피벗 테이블의 기반은 무엇이며 어디에 만들어야합니까? 위의 단계에서 이미 목록 내의 셀을 클릭 했으므로 해당 셀을 둘러싼 전체 목록이 이미 선택되어 있습니다 ($ A $ 1 : $ G $ 88 ~에 지불 시트,이 예제에서). 다른 워크 시트의 다른 영역이나 심지어 Access 데이터베이스 테이블 또는 MS-SQL Server 데이터베이스 테이블과 같은 일부 외부 데이터 원본에서도 목록을 선택할 수 있습니다. 또한 새로운 피벗 테이블을 만들 것인지 여부를 선택해야합니다. 새로운 워크 시트 또는 기존의 하나. 이 예에서는 새로운 하나:

새 워크 시트가 생성되어 해당 워크 시트에 빈 피벗 테이블이 만들어집니다.

또 다른 상자가 나타납니다. 피벗 테이블 필드 목록.  피벗 테이블 (위)에서 셀을 클릭 할 때마다이 필드 목록이 표시됩니다.

상자 상단의 필드 목록은 실제로 원래 원시 데이터 워크 시트의 열 머리글 모음입니다. 화면의 아래쪽에있는 네 개의 빈 상자는 피벗 테이블에서 원시 데이터를 요약하는 방법을 선택할 수있게 해줍니다. 지금까지이 상자에는 아무 것도 없으므로 피벗 테이블은 비어 있습니다. 우리가해야 할 일은 위의 목록에서 필드를 끌어와 더 낮은 상자에 드롭하는 것입니다. 그러면 피벗 테이블이 지침에 맞게 자동으로 만들어집니다. 우리가 잘못 이해한다면, 필드를 원래 위치로 드래그하거나 드래그해야합니다. 새로운 필드를 대체하기 위해 아래로.

그만큼 가치 상자는 틀림없이 4 가지 중 가장 중요한 것입니다. 이 상자에 끌어다 놓은 필드는 합산, 평균화, 최대 값, 최소값 찾기 등의 방법으로 요약해야하는 데이터를 나타냅니다. 거의 항상 그렇다. 수치의 데이터. 샘플 데이터에서이 상자의 가장 적합한 후보는 "금액"필드 / 열입니다. 해당 입력란을 가치 상자:

(a) 필드 목록의 "금액"필드가 이제 체크되고 "금액 합계"가 가치 상자에서 금액 열이 합산되었음을 나타냅니다..

피벗 테이블 자체를 살펴보면 원시 데이터 워크 시트의 모든 "양"값의 합계를 찾을 수 있습니다.

첫 번째 피벗 테이블을 만들었습니다! 핸디하지만 특히 인상적이지는 않습니다. 우리가 데이터보다 더 많은 통찰력을 필요로 할 가능성이 있습니다..

예제 데이터를 참조 할 때이 총계를 나눌 때 사용할 수있는 하나 이상의 열 머리글을 식별해야합니다. 예를 들어, 우리는 우리가 가지고있는 데이터의 요약을보고 싶다고 결정할 수 있습니다. 행 제목 우리 회사의 각기 다른 영업 사원에 대해 그리고 각각에 대한 합계를 표시합니다. 이를 달성하기 위해 "영업 사원"필드를 행 레이블 상자:

지금, 마지막으로 재미있는 일이 생기기 시작합니다. 피벗 테이블이 시작됩니다 ... .

몇 번의 클릭으로 우리는 수동으로하는 데 오랜 시간이 걸렸을 테이블을 만들었습니다..

그러면 우리는 또 무엇을 할 수 있습니까? 음, 어떤면에서는 피벗 테이블이 완성되었습니다. 소스 데이터에 대한 유용한 요약을 만들었습니다. 중요한 것들은 이미 배웠습니다! 이 기사의 나머지 부분에서는 더 복잡한 피벗 테이블을 만들 수있는 방법과 이러한 피벗 테이블을 사용자 지정할 수있는 방법을 살펴 보겠습니다..

먼저, 두-차원 테이블. "지불 방법"을 열 표제로 사용하여 해보 죠. '결제 수단'제목을 열 레이블 상자:

어떤 모습입니까?

시작하기 대단히 시원한!

그것을 세-차원 테이블. 그런 표는 어떻게 생겼을까요? 어디 한번 보자…

'패키지'항목 / 제목을 보고서 필터 상자:

그것이 어디에서 끝나는 지 알려줍니다 ... .

이렇게하면 "휴일 패키지"를 구매할 때 보고서를 필터링 할 수 있습니다. 예를 들어 영업 담당자와 지불 방법의 분류를 볼 수 있습니다. 모든 패키지를 클릭하거나 두 번 클릭하여 "Sunseekers"패키지에 대해 동일한 분류를 표시하도록 변경하십시오.

따라서 올바른 방법이라고 생각하면 피벗 테이블이 3 차원으로 바뀝니다. 계속 커스터마이징 ...

우리가보기를 원한다는 것이 밝혀지면 수표 및 신용 카드 거래 (예 : 현금 거래 없음) 인 경우 열 머리글에서 '현금'항목을 선택 취소 할 수 있습니다. 옆에있는 드롭 다운 화살표를 클릭하십시오. 열 레이블, untick "Cash":

어떻게 생겼는지 보자 ... 보시다시피, "현금"은 없어 졌어..

서식 지정

이것은 분명히 매우 강력한 시스템이지만, 지금까지 결과는 매우 평범하고 지루하게 보입니다. 처음에는 합계되는 숫자가 달러 금액처럼 보이지 않습니다. 단지 오래된 숫자입니다. 그 것을 바로 잡자..

유혹은 그러한 상황에서 우리가 해왔 던 일을하고 전체 표 (또는 전체 워크 시트)를 선택하고 도구 모음의 표준 숫자 서식 단추를 사용하여 서식을 완성하는 것일 수 있습니다. 이 방법의 문제점은 앞으로 피벗 테이블의 구조를 변경하면 (99 % 가능성이 있음) 해당 숫자 형식이 손실된다는 것입니다. 우리는 그들을 (반) 영구적으로 만들 수있는 방법이 필요합니다..

먼저, "금액 합계"항목을 가치 상자를 클릭하십시오. 메뉴가 나타납니다. 우리는 값 필드 설정 ... 메뉴에서

그만큼 값 필드 설정 상자가 나타납니다..

클릭 숫자 형식 버튼 및 표준 셀 서식 상자 나타납니다.

로부터 범주 목록에서 선택 (말하기) 회계, 소수점 이하 자릿수를 0으로 떨어 뜨리십시오. 승인 피벗 테이블로 돌아 가기 위해 몇 번 ...

보시다시피, 숫자는 달러 금액으로 올바르게 형식이 지정되었습니다..

서식 지정을하는 동안 전체 피벗 테이블을 포맷 해 봅시다. 이를 수행 할 수있는 몇 가지 방법이 있습니다. 간단한 것을 사용합시다 ...

클릭 피벗 테이블 도구 / 디자인 탭:

그런 다음 오른쪽 하단의 화살표를 피벗 테이블 스타일 목록 - 내장 스타일의 광대 한 컬렉션을 볼 수 있습니다 :

항소하는 사람을 선택하고 피벗 테이블에서 결과를 봅니다.

다른 옵션

우리는 날짜와 함께 일할 수 있습니다. 이제는 일반적으로 우리가 시작한 것과 같은 트랜잭션 목록에 많은 날짜가 있습니다. 그러나 Excel에서는 데이터 항목을 일별, 주별, 월별, 연도별로 그룹화하는 옵션을 제공합니다. 어떻게 수행되는지 살펴 보겠습니다..

먼저 '결제 수단'항목을 열 레이블 상자 (다시 필드 목록으로 끌어다 놓기 만하면됩니다)를 입력하고 "날짜 예약"열로 바꿉니다.

보시다시피, 이로 인해 피벗 테이블이 즉시 쓸모 없게되어 트랜잭션이 발생한 각 날짜에 대해 하나의 열을 제공합니다. 매우 넓은 테이블!

이 문제를 해결하려면 날짜를 마우스 오른쪽 버튼으로 클릭하고 그룹… 컨텍스트 메뉴에서 다음을 수행하십시오.

그룹화 상자가 나타납니다. 우리는 개월 확인을 클릭하십시오.

빌라! 에이 많은 더 유용한 테이블 :

(이 표는이 기사의 시작 부분에 표시된 것과 사실상 동일합니다. 수동으로 작성된 원래 판매 요약입니다.)

알고 있어야 할 또 다른 멋진 점은 두 개 이상의 행 머리글 (또는 열 머리글) 집합을 가질 수 있다는 것입니다.

이게 보이는 ... .

열 머리글 (또는 심지어 보고서 필터)과 유사한 작업을 수행 할 수 있습니다..

일을 다시 단순하게 유지하면서 음모를 꾸미는 방법을 알아 봅시다. 평균 합계가 아닌 값.

먼저 "Sum of Amount"를 클릭하고 값 필드 설정 ... 나타나는 컨텍스트 메뉴에서 다음을 수행하십시오.

에서 값 필드 요약 기준 목록에 값 필드 설정 상자, 선택 평균:

우리가 여기있는 동안, 사용자 정의 이름, "Amount of Amount"에서 조금 더 간결하게. '평균'과 같이 입력합니다.

딸깍 하는 소리 승인, 그것이 어떻게 생겼는지 보아라. 모든 값이 합계에서 평균으로 바뀌고 테이블 제목 (왼쪽 위 셀)이 "평균"으로 변경되었습니다.

우리가 원한다면 동일한 피벗 테이블에 합계, 평균 및 개수 (개수 = 판매 수량)가있을 수 있습니다.!

다음은 빈 피벗 (PivotTable)에서 시작하는 단계입니다.

1. "영업 사원"을 열 레이블
2. '금액'입력란을 가치 상자 세 번
3. 첫 번째 '금액'입력란의 맞춤 이름을 '전체'로 변경하면 숫자 서식이 회계 (소수점 이하 0 자리)
4. 두 번째 '금액'입력란의 맞춤 이름을 '평균'으로 변경합니다. 평균 그리고 그것의 숫자 형식은 회계 (소수점 이하 0 자리)
5. 세 번째 '금액'입력란의 이름을 '개수'로 변경하고 그 기능을 카운트
6. 자동으로 생성 된 에서 오는 분야 열 레이블 에 행 레이블

다음은 우리가 끝내는 것입니다 :

동일한 피벗 테이블의 총계, 평균 및 개수!

결론

Microsoft Excel로 만든 피벗 테이블에는 더 많은 기능과 옵션이 있습니다. 너무 많은 문서로이 목록에 포함됩니다. 피벗 테이블의 잠재력을 완전히 파악하려면 작은 책 (또는 대규모 웹 사이트)이 필요합니다. 용감한 독자 및 / 또는 괴상한 독자는 피벗 테이블을 더 쉽게 탐색 할 수 있습니다. 모든 것을 마우스 오른쪽 버튼으로 클릭하고 사용 가능한 옵션을 확인하십시오. 두 개의 리본 탭이 있습니다. 피벗 테이블 도구 / 옵션 과 디자인.  실수를 저 지르더라도 피벗 테이블을 삭제하고 다시 시작할 수 있습니다. Lotus 1-2-3의 이전 DOS 사용자는.

Office 2007에서 작업하는 경우 Excel 2007에서 피벗 테이블을 만드는 방법에 대한 기사를 참조하십시오..

피벗 테이블 기술을 연습하기 위해 다운로드 할 수있는 Excel 통합 문서가 포함되어 있습니다. 97 이후 버전의 모든 Excel 버전에서 작동해야합니다..

실습 Excel 워크 북 다운로드