마스터 피벗 테이블:강력한 데이터 통찰력을 위한 5가지 고급 기술

피벗 테이블은 데이터 분석을 위한 Excel의 가장 강력한 도구 중 하나이며 대화형 대시보드를 만드는 데 도움이 됩니다. 그러나 대부분의 사용자는 자신의 잠재력을 거의 활용하지 못합니다. 단순한 합계 및 개수 연산 외에도 피벗 테이블은 원시 데이터를 대화형의 통찰력 있는 보고서로 전환할 수 있는 고급 기능을 제공합니다.

이 튜토리얼에서는 데이터에 대한 더 깊은 통찰력을 얻기 위한 다섯 가지 필수 피벗 테이블 요령을 살펴보겠습니다.

샘플 데이터 세트(판매 테이블)

Excel에 판매 데이터가 있다고 가정해 보세요. 아래의 모든 예에 이 데이터 세트를 사용하겠습니다.

데이터를 표로 변환:

• 전체 데이터 세트를 선택합니다.
• 삽입으로 이동 탭>> 표 선택 .
• '내 테이블에 헤더가 있습니다'를 확인하세요. .
• 확인을 클릭하세요. .
• 테이블 디자인으로 이동>> 테이블 이름을 "SalesData"로 지정합니다.

기본 피벗 테이블 만들기:

1. SalesData에서 아무 셀이나 클릭하세요.
2. 삽입으로 이동 탭>> 피벗 테이블 선택 .
3. 새 워크시트를 선택하세요. 확인을 클릭하세요. .

피벗 테이블 필드에서 목록:

• 지역 드래그 행으로 .
• 카테고리 드래그 열로 .
• 수익을 드래그하세요. 값 .

이제 지역 및 카테고리별 수익에 대한 표준 보고서가 제공됩니다. 훨씬 더 스마트하게 바꿔드리겠습니다.

트릭 1:대화형 필터링을 위한 슬라이서 삽입

슬라이서는 클릭 가능한 대화형 필터링 도구입니다. 정적 보고서나 대시보드를 대화형 보고서나 대시보드로 바꿀 수 있습니다. 슬라이서는 모든 필터링 옵션을 버튼으로 표시하여 데이터 탐색을 직관적이고 빠르게 만듭니다.

단계:

1. 피벗 테이블 내부 아무 곳이나 클릭하세요.
2. 피벗 테이블 분석으로 이동 탭>> 슬라이서 삽입을 선택합니다. .
3. 영업사원 선택 , 고객 및 카테고리 을 클릭한 다음 확인을 클릭하세요. .

Excel에는 버튼이 있는 세 개의 슬라이서 상자가 표시됩니다.

• 영업사원을 클릭하면 해당 영업사원의 데이터만 표시되도록 피벗 테이블을 필터링할 수 있습니다.

• Ctrl을 길게 누르세요. 여러 이름을 선택하려면 클릭하세요.
• 필터를 지우려면 슬라이서(오른쪽 상단)에 있는 깔때기 아이콘을 클릭하세요.

슬라이서를 여러 피벗 테이블에 연결:
가장 강력한 기능 중 하나는 단일 슬라이서를 여러 피벗 테이블에 연결하는 기능입니다.

• 슬라이서를 선택합니다.
• 마우스 오른쪽 버튼 클릭>> 연결 보고 선택 .

• 모든 피벗 테이블 선택>> 확인 클릭 .

이것이 하나의 시트에 실제 대시보드를 구축하는 핵심입니다. 관리자는 슬라이서를 사용하여 피벗 테이블 필드를 건드리지 않고도 영업사원, 고객 또는 제품별 측정항목을 빠르게 확인할 수 있습니다.

슬라이서 모양 맞춤설정:

• 슬라이서를 선택합니다.
• 슬라이서로 이동 탭이 나타납니다.
• 다양한 슬라이서 스타일 중에서 선택 갤러리에 있습니다.
• 열 조정 여러 열에 버튼을 표시합니다.
• 슬라이서 설정 사용 (마우스 오른쪽 버튼 클릭> 슬라이서 설정) 헤더 캡션을 변경하세요.
• 슬라이서의 크기를 조정하고 위치를 조정하여 깔끔한 대시보드 레이아웃을 만듭니다.

비법 2:타임라인을 사용하여 즉시 시간 추세 분석

타임라인은 날짜 필드용으로 특별히 설계된 특수 슬라이서입니다. 피벗 테이블 데이터를 기간별로 필터링하여 일, 월, 분기, 연도 간에 빠르게 전환할 수 있습니다.

단계:

• 피벗 테이블을 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 타임라인 삽입을 선택합니다. .
• 날짜 선택>> 그런 다음 확인을 클릭하세요. .

날짜 범위가 가로 막대로 표시됩니다.

• 연도, 분기, 월, 일 간 수준을 전환하려면 타임라인의 드롭다운을 사용하세요.
• 특정 기간을 확대하려면 왼쪽 또는 오른쪽 핸들을 드래그하세요.
• 2025년 1월과 같은 한 달을 클릭하면 해당 달의 매출만 볼 수 있습니다.
• 타임라인에서 필터 아이콘을 클릭하여 필터를 지웁니다.

판매 동향을 즉시 검토할 수 있습니다. 슬라이서와 결합하면 "1월 Alex의 북부 지역 전자제품 판매량은 어땠나요?"와 같은 질문에 답할 수 있습니다. 단 몇 번의 클릭만으로 가능합니다.

비법 3:데이터를 연령 또는 판매 등급으로 그룹화

그룹화를 사용하면 데이터를 더 크고 의미 있는 버킷으로 분류할 수 있습니다. 이는 분포 분석, 연령대 생성, 범위별 분류 또는 날짜를 사용자 정의 기간으로 구성하는 데 매우 중요합니다.

고객을 연령대별로 그룹화:

• 새 피벗 테이블을 만듭니다.
• Customer_Age를 드래그하세요. 행으로 .
• 수익을 드래그하세요. 값 .
• 마우스 오른쪽 버튼 클릭 행 라벨의 연령을 선택하고 그룹을 선택하세요. .

• 그룹화 대화상자에서 다음을 설정합니다.
  • 시작 시간: 20
  • 종료 시간: 70
  • 작성자: 10
• 확인을 클릭하세요. . 피벗 테이블에 연령 그룹이 표시됩니다.

이제 가장 많은 수익을 창출하는 연령 그룹을 확인할 수 있습니다. 가장 큰 주문이 나이가 많은 고객에게서 나오나요, 아니면 젊은 고객에게서 나오나요?

수익을 판매 금액 밴드로 그룹화:

• 다른 피벗 테이블을 만듭니다.
• 수익을 드래그하세요. 행으로 .
• 수익을 드래그하세요. 값 .
• 지역 드래그 열로 .
• 행 라벨에서 수익 값을 마우스 오른쪽 버튼으로 클릭하고 그룹…을 선택합니다. .
• 합리적인 경계 설정:
  • 시작 시간: 0
  • 종료 시간: 40000
  • 작성자: 2000
• 확인을 클릭하세요. .

다음 밴드가 표시됩니다.

제품 가격 등급:

• 시작 시간: 0
• 종료 시간: 500
• 작성자: 100

예:0–100(예산), 100–200(경제), 200–300(표준), 300–400(프리미엄), 400–500(럭셔리).

비법 4:계산된 필드를 사용하여 맞춤 측정항목 구축

때로는 원시 필드만으로는 충분하지 않습니다. 계산된 필드를 사용하면 기존 피벗 테이블 필드를 기반으로 하는 수식을 사용하여 새 필드를 만들 수 있습니다. 즉, 소스 데이터에 추가 열이 필요 없이 피벗 테이블 내에서 직접 이익, 이익 마진, 차이, 전환율 및 기타 사용자 정의 지표를 계산할 수 있습니다.

단계:

• 피벗 테이블의 아무 곳이나 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 필드, 항목 및 세트를 클릭합니다.>> 계산된 필드를 선택합니다. .

• 계산된 필드 삽입 대화상자:
  • 이름을 입력하세요. 해당 분야에 대한 정보(예:'이익률')
  • 수식을 클릭하세요. 상자를 클릭하고 공식을 만드세요.
  • 필드 목록에서 필드 이름을 두 번 클릭하여 삽입하세요.
  • 표준 연산자 사용:+(더하기), −(빼기), *(곱하기), /(나누기)
• 확인을 클릭하세요. .

이익:

• 이름: 이익
• 수식:

이익률 비율:

• 이름: 이익마진
• 수식:

= (Revenue - Cost) / Revenue

• 백분율 형식 .

수익의 30%로 '이익' 창출:

• 이름: 수익의 30%를 이익
• 수식:

계산된 필드를 사용하면 피벗 테이블 내에서 바로 비즈니스별 KPI를 정의할 수 있습니다. SalesData에 행을 더 추가하고 새로 고치면 계산된 필드가 자동으로 업데이트됩니다.

트릭 5:"값 표시 형식"을 사용하여 백분율 및 누계 보기

피벗 테이블은 일반적으로 원시 숫자를 표시하지만 상대적인 보기가 더 통찰력 있는 경우가 많습니다. 피벗 테이블은 전체 비율, 행/열 비율, 시간 경과에 따른 누계 등 여러 표시 옵션을 제공합니다.

지역별 총 수익 비율:

• 지역을 사용하여 피벗 테이블 구축 행 및 수익 가치.
• 수익 열의 숫자를 마우스 오른쪽 버튼으로 클릭하세요.
• 값 표시 형식을 선택하세요.>> 총합계의 % 선택 .

이제 각 지역은 전체 수익의 몇 퍼센트를 차지하는지 보여줍니다. 세그먼트를 비교할 때 실제 금액보다 훨씬 쉽게 해석할 수 있습니다.

다음을 선택할 수도 있습니다:

• 열 합계의 % 각 지역을 해당 열의 백분율로 표시합니다.
• 행 합계의 % 각 카테고리를 각 지역 내 백분율로 확인하세요.

팁:명확한 통찰력을 위해 피벗 테이블 형식 사용

형식이 잘 지정된 피벗 테이블을 사용하면 통찰력을 더 쉽게 이해할 수 있습니다.

숫자 형식 추가:

• 값을 마우스 오른쪽 버튼으로 클릭>> 숫자 형식>> 통화, 백분율 등을 선택하세요.

상위 5명의 기여자 표시:

1. 행 라벨 옆에 있는 드롭다운을 클릭하세요.
2. 값 필터 선택>> 상위 10개 선택>> 상위 5위로 변경합니다.
3. 수익 합계를 선택합니다. .

조건부 서식 사용:

1. 피벗 테이블 값을 선택합니다.
2. 홈으로 이동 탭>> 조건부 서식을 선택합니다. .
3. 색상 스케일, 데이터 막대 또는 아이콘 세트를 선택하세요.

통계: 최고 실적을 강조하면 베스트셀러 품목이나 가장 강력한 지역을 빠르게 식별하는 데 도움이 됩니다.

모든 것을 하나로 묶기:간단한 대화형 판매 대시보드

이러한 5가지 트릭을 사용하면 단일 워크시트에 미니 대시보드를 구축할 수 있습니다. 하나의 시트에 요소를 함께 배치하여 응집력 있는 대시보드를 만드세요.

• 영업사원과 고객을 위한 슬라이서를 사용한 지역 및 카테고리별 수익
• 트릭 3을 사용하여 그룹화한 연령대별 수익
• 타임라인은 모든 시간 기반 피벗 테이블의 기간을 제어합니다.
• 슬라이서는 보고서 연결을 사용하여 모든 피벗 테이블에 연결됩니다. 이므로 클릭 한 번으로 전체 보기가 변경됩니다.
• 계산된 필드는 모든 보고서의 이익과 평균 가격을 제공합니다.

모든 것이 설정되면 SalesData에 새 행을 붙여넣고 새로고침을 클릭하기만 하면 됩니다. . 피벗 테이블, 계산된 필드, 슬라이서, 타임라인 및 그룹화가 자동으로 업데이트되므로 보고서를 다시 작성하지 않고도 새로운 통찰력을 얻을 수 있습니다.

결론

이러한 5가지 피벗 테이블 요령을 사용하면 데이터에 대한 심층적인 통찰력을 얻고 정적 보고서를 대화형 분석 대시보드로 변환할 수 있습니다. 슬라이서, 타임라인, 계산된 필드 및 그룹화를 사용하면 패턴을 훨씬 빠르게 해석하고 결과를 명확하게 제시할 수 있습니다. 자신의 데이터로 연습하고 대시보드에서 이를 결합하여 실험해 보세요. 사용 중인 Excel 버전에 기능이 부족한 경우(타임라인에는 Excel 2013 이상이 필요함) 업데이트를 확인하세요.