Excel에서 피벗 테이블을 만드는 방법: 단계별 자습서

피벗 테이블은 Microsoft Excel의 가장 강력하고 위협적인 기능 중 하나입니다. 대용량 데이터 세트를 요약하고 이해하는 데 도움이 되기 때문에 강력합니다. 당신이 Excel 전문가가 아니기 때문에 두렵고 피벗 테이블은 항상 복잡하다는 평판을 받았습니다.

좋은 소식: Excel에서 피벗 테이블을 만드는 방법을 배우는 것은 생각보다 훨씬 쉽습니다.

그러나 생성 과정을 안내하기 전에 한 걸음 물러나서 피벗 테이블이 무엇인지, 왜 피벗 테이블을 사용해야 하는지 정확히 이해했는지 확인하겠습니다.

즉, 피벗 테이블은 화면에서 끝없이 뒤섞여 있는 것처럼 보이는 숫자에서 의미를 추출합니다. 더 구체적으로 말하면 데이터를 다양한 방식으로 그룹화할 수 있으므로 유용한 결론을 더 쉽게 도출할 수 있습니다.

피벗 테이블의 "피벗" 부분은 테이블의 데이터를 회전(또는 피벗)하여 다른 관점에서 볼 수 있다는 사실에서 비롯됩니다. 분명히 말해서, 피벗을 만들 때 데이터에 더하거나 빼거나 변경하는 것이 아닙니다. 대신 데이터에서 유용한 정보를 표시할 수 있도록 데이터를 재구성하기만 하면 됩니다.

피벗 테이블은 무엇에 사용됩니까?

피벗 테이블이 실제로 하는 일에 대해 여전히 약간 혼란스럽다고 느끼더라도 걱정하지 마십시오. 이것은 실제로 실제로 보고 나면 훨씬 이해하기 쉬운 기술 중 하나입니다.

피벗 테이블의 목적은 많은 양의 데이터를 신속하게 요약할 수 있는 사용자 친화적인 방법을 제공하는 것입니다. 수치 데이터를 자세히 이해, 표시 및 분석하는 데 사용할 수 있으며 데이터를 둘러싼 예상치 못한 질문을 식별하고 답하는 데 도움이 될 수 있습니다.

다음은 피벗 테이블이 솔루션이 될 수 있는 7가지 가상 시나리오입니다.

1. 다른 제품의 판매 합계를 비교합니다.

세 가지 다른 제품(제품 1, 제품 2, 제품 3)에 대한 월별 판매 데이터가 포함된 워크시트가 있고 세 제품 중 어떤 제품이 가장 많은 돈을 벌어들이고 있는지 파악하려고 한다고 가정해 보겠습니다. 물론 워크시트를 살펴보고 제품 1이 나타날 때마다 해당 판매 수치를 누계에 수동으로 추가할 수 있습니다. 그런 다음 제품 2 및 제품 3에 대해 동일한 작업을 수행하여 모든 제품에 대한 합계가 나올 때까지 할 수 있습니다. 조각 케이크 맞죠?

이제 월별 판매 워크시트에 수천 개의 행이 있다고 상상해 보십시오. 수동으로 모든 항목을 정렬하려면 평생이 걸릴 수 있습니다. 피벗 테이블을 사용하면 제품 1, 제품 2, 제품 3에 대한 모든 판매 수치를 자동으로 집계하고 각각의 합계를 1분 이내에 계산할 수 있습니다.

2. 제품 판매를 총 판매액의 백분율로 표시합니다.

피벗 테이블은 생성할 때 각 행 또는 열의 합계를 자연스럽게 표시합니다. 그러나 자동으로 생성할 수 있는 수치는 이것만이 아닙니다.

3개의 개별 제품에 대한 분기별 판매 수치를 Excel 시트에 입력하고 이 데이터를 피벗 테이블로 변환했다고 가정해 보겠습니다. 테이블은 각 제품의 분기별 매출을 합산하여 각 열의 맨 아래에 3개의 합계를 자동으로 제공합니다. 그러나 해당 제품의 총 판매액이 아니라 이러한 제품 판매가 모든 회사 판매에 기여한 비율 을 찾으려면 어떻게 해야 합니까?

피벗 테이블을 사용하면 열 합계 대신 세 열 합계에 대한 열의 백분율을 제공하도록 각 열을 구성할 수 있습니다. 예를 들어 3개의 제품 판매 합계가 $200,000이고 첫 번째 제품이 $45,000인 경우 이 제품이 전체 회사 판매의 22.5%를 기여했다고 표시하도록 피벗 테이블을 편집할 수 있습니다.

제품 판매를 피벗 테이블에서 총 판매의 백분율로 표시하려면 판매 총계가 있는 셀을 마우스 오른쪽 버튼으로 클릭하고 다음으로 값 표시 > 총계의 %를 선택하기만 하면 됩니다.

3. 중복 데이터 결합.

이 시나리오에서는 블로그 재설계를 완료하고 많은 URL을 업데이트해야 했습니다. 불행히도 귀하의 블로그 보고 소프트웨어는 이를 잘 처리하지 못했고 결국 두 개의 서로 다른 URL 간에 단일 게시물에 대한 "보기" 측정항목을 분할했습니다. 따라서 스프레드시트에는 각 개별 블로그 게시물에 대한 두 개의 개별 인스턴스가 있습니다. 정확한 데이터를 얻으려면 이러한 각 중복 항목에 대한 보기 합계를 결합해야 합니다.

피벗 테이블이 작동하는 곳입니다. 중복에서 모든 측정항목을 수동으로 검색하고 결합하는 대신 블로그 게시물 제목별로 데이터를 요약할 수 있습니다(피벗 테이블을 통해). 짜잔: 해당 중복 게시물의 보기 측정항목이 자동으로 집계됩니다.

4. 별도의 부서에 대한 직원 인원수 얻기.

피벗 테이블은 기본 Excel 테이블에서 쉽게 찾을 수 없는 항목을 자동으로 계산하는 데 유용합니다. 그 중 하나는 모두 공통점이 있는 행을 계산하는 것입니다.

예를 들어 Excel 시트에 직원 목록이 있고 직원 이름 옆에 해당 부서가 있는 경우 각 부서 이름과 직원 수를 보여주는 이 데이터에서 피벗 테이블을 만들 수 있습니다. 해당 부서에 속하는 것입니다. 피벗 테이블은 Excel 시트를 부서 이름별로 정렬하고 각 행을 수동으로 계산하는 작업을 효과적으로 제거합니다.

5. 빈 셀에 기본값 추가.

Excel에 입력하는 모든 데이터 세트가 모든 셀을 채우는 것은 아닙니다. Excel에 데이터를 입력하기 전에 새 데이터가 수신되기를 기다리는 경우 관리자에게 이 데이터를 표시할 때 혼란스러워 보이거나 추가 설명이 필요한 빈 셀이 많이 있을 수 있습니다. 그것이 피벗 테이블이 들어오는 곳입니다.

$0 또는 TBD("미정")와 같은 기본값으로 빈 셀을 채우도록 피벗 테이블을 쉽게 사용자 정의할 수 있습니다. 큰 데이터 테이블의 경우 이러한 셀에 빠르게 태그를 지정할 수 있는 기능은 많은 사람들이 동일한 시트를 검토할 때 유용한 기능입니다.

피벗 테이블의 빈 셀에 자동으로 서식을 지정하려면 테이블을 마우스 오른쪽 버튼으로 클릭하고 피벗 테이블 옵션을 클릭합니다. 표시되는 창에서 빈 셀 형식으로 레이블이 지정된 상자를 선택하고 셀에 다른 값이 없을 때 표시할 항목을 입력합니다.

피벗 테이블을 만드는 방법

1. 행과 열 범위에 데이터를 입력합니다.
2. 특정 속성을 기준으로 데이터를 정렬합니다.
3. 셀을 강조 표시하여 피벗 테이블을 만듭니다.
4. 필드를 "행 레이블" 영역으로 끌어다 놓습니다.
5. 필드를 "값" 영역으로 끌어다 놓습니다.
6. 계산을 미세 조정합니다.

이제 피벗 테이블을 사용할 수 있는 방법에 대해 더 잘 이해했으므로 실제로 만드는 방법의 핵심으로 들어가 보겠습니다.

1단계. 행과 열 범위에 데이터를 입력합니다.

Excel의 모든 피벗 테이블은 모든 데이터가 보관되는 기본 Excel 테이블로 시작합니다. 이 테이블을 만들려면 특정 행과 열 집합에 값을 입력하기만 하면 됩니다. 맨 위 행 또는 맨 위 열을 사용하여 값이 나타내는 항목에 따라 값을 분류합니다.

예를 들어 블로그 게시물 성능 데이터의 Excel 테이블을 생성하기 위해 각 "상위 페이지"를 나열하는 열, 각 URL의 "클릭"을 나열하는 열, 각 게시물의 "노출"을 나열하는 열 등을 가질 수 있습니다. (다음 단계에서 해당 예제를 사용할 것입니다.)

2단계. 특정 속성을 기준으로 데이터를 정렬합니다.

Excel 시트에 입력하려는 모든 데이터가 있으면 이 데이터를 어떤 방식으로든 정렬하여 피벗 테이블로 전환하면 관리하기가 더 쉬워집니다.

데이터를 정렬하려면 상단 탐색 모음에서 데이터 탭을 클릭하고 그 아래에 있는 정렬 아이콘을 선택합니다. 표시되는 창에서 원하는 열을 기준으로 데이터를 정렬하도록 선택할 수 있습니다.

예를 들어, Excel 시트를 "현재까지의 조회수"로 정렬하려면 열 아래에서 이 열 제목을 선택한 다음 게시물을 가장 작은 것부터 큰 것 순으로 정렬할지 아니면 가장 큰 것에서 가장 작은 순으로 정렬할지 선택합니다.

정렬 창의 오른쪽 하단에서 확인 을 선택하면 각 블로그 게시물이 받은 조회수에 따라 Excel 시트의 각 행이 성공적으로 재정렬됩니다.

3단계. 셀을 강조 표시하여 피벗 테이블을 만듭니다.

Excel 워크시트에 데이터를 입력하고 원하는 대로 정렬했으면 피벗 테이블에서 요약할 셀을 강조 표시합니다. 상단 탐색을 따라 삽입 을 클릭하고 피벗 테이블 아이콘을 선택합니다. 워크시트의 아무 곳이나 클릭하고 "피벗 테이블"을 선택한 다음 피벗 테이블에 포함할 셀 범위를 수동으로 입력할 수도 있습니다.

이렇게 하면 셀 범위를 설정하는 것 외에도 이 피벗 테이블을 새 워크시트에서 시작할지 아니면 기존 워크시트에 유지할지 여부를 선택할 수 있는 옵션 상자가 열립니다. 새 시트를 열면 Excel 통합 문서 맨 아래에 있는 시트로 이동하거나 시트에서 멀리 이동할 수 있습니다. 선택했으면 확인을 클릭합니다.

또는 셀을 강조 표시하고 피벗 테이블 아이콘 오른쪽에 있는 권장 피벗 테이블 을 선택한 다음 각 행과 열을 구성하는 방법에 대한 미리 설정된 제안이 있는 피벗 테이블을 열 수 있습니다.

참고: 이전 버전의 Excel을 사용하는 경우 "피벗 테이블" 이 "삽입"이 아니라 상단 탐색의 테이블 또는 데이터 아래에 있을 수 있습니다. Google 스프레드시트 의 상단 탐색을 따라 데이터 드롭다운 에서 피벗 테이블을 만들 수 있습니다 .

4단계. 필드를 "행 레이블" 영역으로 끌어다 놓습니다.

3단계를 완료하면 Excel에서 빈 피벗 테이블을 만듭니다. 다음 단계는 스프레드시트의 열 이름에 따라 레이블이 지정된 필드를 행 레이블 영역으로 끌어다 놓는 것입니다. 이렇게 하면 블로그 게시물 제목, 제품 이름 등의 고유 식별자가 피벗 테이블에서 데이터를 구성할 기준이 결정됩니다.

예를 들어, 게시물 제목별로 많은 블로깅 데이터를 구성하려고 한다고 가정해 보겠습니다. 그렇게 하려면 "상위 페이지" 필드를 클릭하고 "행 레이블" 영역으로 끌어다 놓기만 하면 됩니다.

참고: 피벗 테이블은 작업 중인 Excel 버전에 따라 다르게 보일 수 있습니다. 그러나 일반적인 원칙은 동일하게 유지됩니다.

5단계. 필드를 "값" 영역으로 끌어다 놓습니다.

데이터를 구성할 기준을 설정했으면 다음 단계는 필드를 값 영역으로 끌어서 일부 값을 추가하는 것입니다.

블로깅 데이터 예제를 고수하면서 블로그 게시물 조회수를 제목별로 요약하고 싶다고 가정해 보겠습니다. 이렇게 하려면 "보기" 필드를 값 영역으로 끌어다 놓기만 하면 됩니다.

6단계. 계산을 미세 조정합니다.

특정 값의 합은 기본적으로 계산되지만 계산하려는 항목에 따라 이를 평균, 최대 또는 최소와 같이 쉽게 변경할 수 있습니다.

Mac에서는 "값" 영역의 값 옆에 있는 작은 i 를 클릭하고 원하는 옵션을 선택한 다음 "확인"을 클릭하면 됩니다. 선택을 마치면 그에 따라 피벗 테이블이 업데이트됩니다.

PC를 사용하는 경우 값 옆에 있는 거꾸로 된 작은 삼각형을 클릭하고 값 필드 설정 을 선택하여 메뉴에 액세스해야 합니다.

데이터를 원하는 대로 분류했으면 작업을 저장하고 마음대로 사용하세요.

피벗 테이블로 더 깊이 파고들기

이제 Excel에서 피벗 테이블 생성의 기본 사항을 배웠습니다. 이러한 이해를 바탕으로 피벗 테이블에서 필요한 것을 파악하고 원하는 솔루션을 찾을 수 있습니다.

예를 들어, 피벗 테이블의 데이터가 원하는 방식으로 정렬되지 않은 것을 볼 수 있습니다. 이 경우 Excel의 정렬 기능이 도움이 될 수 있습니다. 또는 다른 소스의 데이터를 보고에 통합해야 할 수도 있습니다. 이 경우 VLOOKUP 기능이 유용할 수 있습니다.

편집자 주: 이 게시물은 원래 2018년 12월에 게시되었으며 포괄성을 위해 업데이트되었습니다.