Excel Pivot Tables: Beginner to Advanced

나는 내가 3년 동안 데이터 분석을 잘못하고 있었다는 것을 깨달았던 순간을 여전히 기억한다. 2009년이었고, 나는 오하이오의 중소 제조 회사의 비좁은 회의실에 앉아 내 동료인 사라가 50,000행의 스프레드시트를 4분도 안 되어 종합적인 판매 분석으로 변환하는 것을 지켜보고 있었다. 나는 이전 오후에 수동으로 수식을 만들고, 시트 간에 데이터를 복사하고, 차트를 만드는 데 6시간을 보냈다. 사라는 피벗 테이블이라는 것을 사용했고, 그 순간 내 데이터 작업 방식이 근본적으로 바뀌었다.

오늘날, 포춘 500대 기업부터 소규모 비영리 단체까지 다양한 고객과 함께 일한 금융 분석가 및 데이터 컨설턴트로서 15년을 보낸 후, 나는 피벗 테이블이 조직에 수천 시간과 수백만 달러를 절약하는 것을 보았다. 나는 2,000명 이상의 전문가에게 Excel 기술을 교육했으며, 피벗 테이블은 내가 가르치는 가장 혁신적인 도구로 남아 있다. 그러나 나는 Excel 사용자 30% 이하가 실제로 효과적으로 사용하는 방법을 이해한다고 추정한다.

이 기사는 이를 변화시키기 위한 내 시도다. 나는 2009년에 누군가 나에게 가르쳐 주기를 바랐던 모든 것—절대적인 기초부터 동료들에게 데이터 마법사처럼 보이게 만드는 고급 기술까지—를 안내할 것이다.

피벗 테이블이란 무엇이며, 왜 중요한가?

실제로 의미가 있는 정의로 시작하겠다: 피벗 테이블은 단일 수식 없이 대량의 데이터셋을 재조직하고 분석할 수 있게 해주는 동적 요약 도구다. 데이터 분석을 위한 스위스 군용 나이프라고 생각하라—간단한 드래그 앤 드롭 인터페이스를 통해 수십 가지 방법으로 데이터를 조작, 그룹화, 필터링 및 계산할 수 있다.

이것이 실제로 중요한 이유는 다음과 같다. 작년에 나는 3년 동안의 127,000개의 거래 기록이 있는 소매 고객과 작업했다. 그들의 마케팅 이사는 다음과 같은 질문에 답해야 했다: 각 지역에서 가장 잘 팔린 제품은 무엇인가? 계절적인 추세는 무엇인가? 어떤 고객 세그먼트가 가장 많은 수익을 창출했는가? 전통적인 Excel 수식과 수동 분석을 사용했다면 이 작업은 며칠 걸릴 것이었다. 하지만 피벗 테이블을 사용하여 우리는 약 45분 만에 모든 질문에 답했다.

피벗 테이블의 진정한 힘은 유연성에 있다. 새로운 데이터를 추가할 때 깨지는 정적 수식과는 달리, 피벗 테이블은 한 번의 클릭으로 새로 고칠 수 있다. 데이터를 다르게 보고 싶을 때 재구성이 필요한 수동 요약과는 달리, 피벗 테이블은 분석을 즉시 재배치할 수 있게 해준다. 나는 분석가들이 수식 기반 보고서에서 피벗 테이블 대시보드로 전환함으로써 월간 보고 시간을 2일에서 2시간 미만으로 줄이는 것을 보았다.

그러나 대부분의 초보자가 깨닫지 못하는 것은 피벗 테이블이 대량 데이터셋만을 위한 것이 아니라는 것이다. 몇 백 행의 데이터로 작업하더라도 피벗 테이블은 시간을 절약하고 오류를 줄일 수 있다. 나는 개인 비용 분석부터 고객을 위한 프로젝트 시간 추적에 이르기까지 모든 것을 위해 피벗 테이블을 사용한다. 이 논리를 이해하면 피벗 테이블을 사용할 기회를 어디에서든 볼 수 있게 될 것이다.

데이터 설정: 모두가 건너뛰는 기초

이곳이 대부분의 피벗 테이블 튜토리얼이 실패하는 지점이다. 그들은 분석의 용이함을 결정하는 데이터 구조에 대해 설명하지 않고 피벗 테이블 만들기로 바로 들어간다. 나는 피벗 테이블로 어려움을 겪고 있는 많은 기업과 상담했으나, 그들은 도구를 이해하지 못해서가 아니라 처음부터 데이터가 잘못 설정되어 있었기 때문이다.

"피벗 테이블은 기본적으로 데이터 분석에서 수작업의 90%를 없애는 동적 요약 엔진이다. 수식으로 몇 시간이 걸리는 작업이 피벗으로는 몇 분 안에 끝난다."

피벗 테이블 준비가 완료된 데이터에 대한 비협상 규칙은 다음과 같다. 첫째, 데이터는 첫 번째 행에 헤더가 있고 빈 행이나 열이 없는 올바른 표 형식이어야 한다. 모든 열에는 명확하고 설명적인 헤더가 필요하다—"Column1"이나 빈 셀은 안 된다. 나는 한 번 클라이언트의 피벗 테이블을 문제 해결하는 데 3시간을 소비했는데, 그들 데이터셋 중간에 단 하나의 빈 행이 있었고 그로 인해 모든 계산이 실패하고 있었던 것이다.

둘째, 각 열에는 하나의 데이터 유형만 포함되어야 한다. 이는 자명해 보이지만, 나는 종종 사람들이 "Q1 2023"를 하나의 셀에 결합한 스프레드시트를 본다. 데이터가 올바르게 분리될 때는 강력한 방식으로 그룹화하고 필터링할 수 있다. 결합되면 제한된 선택 사항에 묶인다.

셋째, 데이터 범위 내에서 병합된 셀, 소계 및 형식을 피하라. 이것들은 피벗 테이블의 적이다. 나는 20줄마다 병합된 헤더와 소계가 있는 아름답게 형식화된 보고서를 가진 금융 서비스 회사와 작업한 적이 있다. 사람에게는 훌륭하게 보였지만 피벗 테이블에 대해서는 전혀 쓸모가 없었다. 우리는 의미 있는 분석을 수행하기 전에 모든 형식을 제거하고 데이터를 재구성해야 했다.

실용적인 예를 들어보자. 나쁜 데이터 구조: "Sales Info"라는 열에 "John Smith - $45,000 - Northeast"와 같은 텍스트가 포함되었다. 좋은 데이터 구조: "Sales Rep", "Revenue", "Region"이라는 세 개의 개별 열에 데이터를 올바르게 분리했다. 두 번째 버전은 처음 설정하는 데 30초 더 걸리지만 결국 수시간의 좌절감을 덜어 준다.

첫 번째 피벗 테이블 만들기: 단계별 안내

이제 함께 첫 번째 피벗 테이블을 만들어 보자. 나는 지난 달 전자상거래 회사를 위해 완료한 프로젝트에 기반한 현실적 시나리오를 사용할 것이다. 5,000개의 주문 데이터를 포함하고 있는 데이터셋이 있다고 상상해보라. 열은 주문 날짜, 고객 이름, 제품 범주, 제품 이름, 수량, 단가 및 총 판매액을 포함한다.

데이터 범위 안의 아무 곳이나 클릭으로 시작하라—전체 데이터셋을 선택할 필요는 없다. 삽입 탭으로 가서 "피벗 테이블"을 클릭하라. Excel은 자동으로 데이터 범위를 감지하고 피벗 테이블을 어디에 두고 싶은지 묻는다. 나는 첫 몇 개의 피벗 테이블에 대해 항상 "새 워크시트"를 선택할 것을 추천한다. 이렇게 하면 원본 데이터를 분리할 수 있고, 어떤 것을 망칠까 걱정하지 않고 실험하기가 더욱 쉬워진다.

확인 버튼을 클릭하면 좌측에 빈 피벗 테이블이 보이고 우측에는 피벗 테이블 필드 창이 보인다. 여기가 마법이 일어나는 곳이다. 필드 창에는 모든 열 헤더가 표시되고 아래에는 필터, 열, 행, 값의 네 영역이 있다. 이를 분석의 구성 블록으로 생각하라.

간단한 질문에 답해 보자: 각 제품 범주에 대한 총 판매액은 얼마인가? "제품 범주"를 행 영역으로 드래그하고 "총 판매액"을 값 영역으로 드래그하라. 즉시 Excel이 각 범주에 대한 판매 합계를 계산한다. 수식도 필요 없고, 수동으로 더할 필요도 없다—그저 드래그 앤 드롭이다. 초보자들에게 이것을 보여줄 때, 그들이 지금까지 얼마나 많은 시간을 낭비했는지 깨닫는 순간을 실제로 볼 수 있다.

이제 조금 더 흥미롭게 만들어 보자. "주문 날짜"를 열 영역으로 드래그하라. Excel은 날짜를 자동으로 월 및 연도로 그룹화하여 각 범주의 성과를 시간에 따라 보여준다. 전통적인 방법으로 사용하면 복잡한 수식과 여러 단계가 필요했던 것이지만, 피벗 테이블을 사용하면 대략 5초가 걸린다.

내가 힘들게 배운 프로 팁은 다음과 같다: 항상 Excel이 값 영역에서 어떤 계산을 사용하는지 확인하라. 기본적으로 숫자를 합산하고 텍스트를 계산한다. 그러나 때때로 평균, 백분율 또는 다른 계산이 필요할 수 있다. 값 영역의 필드 옆에 있는 작은 "i" 아이콘을 클릭하여 계산 유형을 변경하라. 나는 "판매 수" 대신 "판매 합계"를 보여주는 보고서를 경영진에게 발표한 적이 있는데, 이 설정을 확인하는 것을 잊어버렸다. 정말 부끄럽고 완전히 예방 가능한 일이었다.