스프레드시트의 가장 강력한 도구, 피벗 테이블 (2020)

• 피벗 테이블은 코딩이나 고급 수학 없이도 대규모 데이터를 빠르게 요약하게 해, 스프레드시트를 분석 도구로 만든 핵심 기능임
• Lotus의 Pito Salas는 사용자가 범주별 요약 통계와 크로스탭을 만들려고 복잡한 수식을 쓰는 불편에서 클릭 기반 도구를 구상함
• Steve Jobs는 NeXT 컴퓨터의 수요를 만들 킬러 소프트웨어를 찾는 과정에서 이 기능의 가능성을 보고 Lotus Improv 개발로 이어짐
• Lotus 팀의 flexible views는 이후 Lotus 1-2-3와 Excel에 반영됐고, Excel에서 pivot table이라는 이름이 자리 잡음
• 오늘날 피벗 테이블은 Excel과 Google Sheets에서 널리 쓰이며, 수십만 행 데이터도 몇 번의 클릭으로 한 페이지 요약 보고서로 압축할 수 있음

---

수식 없이 데이터를 요약하는 도구

• 피벗 테이블은 일반 사용자가 대규모 데이터셋을 빠르게 분석할 수 있게 해주는 스프레드시트 기능임
• 코딩 실력이나 높은 수학 지식이 없어도, 사용자는 마우스로 가리키고 클릭해 데이터를 요약할 수 있음
• Apple 창업자 Steve Jobs도 이 기능의 가치를 빠르게 알아본 인물로 등장함

NeXT와 Lotus Improv로 이어진 개발사

• 1985년 Jobs는 비즈니스 컴퓨터 시장에서 IBM을 이기지 못한 뒤 Apple 이사회 의장 자리에서 밀려났고, 곧바로 NeXT를 세워 IBM에 다시 도전하려 함
• 1988년 출시될 NeXT 컴퓨터를 준비하던 Jobs는 제품 수요를 만들 킬러 소프트웨어를 찾고 있었음
  • Jobs는 1979년 출시된 Apple II의 큰 성공에 첫 널리 쓰인 스프레드시트 소프트웨어 VisiCalc가 핵심이었다고 봄
• IBM 컴퓨터용 인기 스프레드시트 Lotus 1-2-3를 만든 Lotus와 만나는 과정에서 Jobs는 피벗 테이블의 초기 형태를 접함
• 당시 Lotus R&D의 Pito Salas는 사람들이 스프레드시트로 범주별 요약 통계, 즉 크로스탭을 계산하는 방식을 관찰함
  • 자전거 판매 회사라면 월별 판매 수량이나 국가별 매출을 보고 싶어 할 수 있음
  • 당시에는 복잡한 수식을 직접 작성해야 해 번거롭고 오류가 나기 쉬웠음
• Salas는 수식 입력 대신 클릭으로 요약 통계를 얻는 소프트웨어가 필요하다고 판단함
• Lotus 팀은 이 도구를 flexible views라고 불렀고, 오늘날 Microsoft Excel과 Google Sheets의 유사 기능은 피벗 테이블로 불림

Jobs의 반응과 제품화

• Lotus 팀이 초기 프로토타입을 Jobs에게 보여주자, Salas에 따르면 Jobs는 그것을 “가장 멋진 것”으로 여김
• Jobs는 Lotus가 피벗 테이블 소프트웨어를 NeXT 컴퓨터 전용으로 개발하도록 설득함
• 그 결과물이 Lotus Improv였음
  • NeXT 컴퓨터는 상업적으로 실패했지만, Lotus Improv는 큰 영향을 남김
  • Improv의 flexible views 요소는 이후 Lotus 1-2-3와 Excel에 들어감
  • Excel은 실제로 pivot table이라는 용어를 처음 사용한 제품임
• Excel 전도사이자 Pivot Table Data Crunching 공동 저자인 Bill Jelen은 Salas를 “피벗 테이블의 아버지”로 평가함
• Salas는 피벗 테이블에 대한 자신의 기여를 인생에서 가장 만족스러운 성과 중 하나로 여기지만, 많은 사람의 기반 위에 만든 결과라고 봄

Excel과 Google Sheets에서의 활용

• 피벗 테이블은 현재 스프레드시트 고급 사용자의 도구함에서 가장 중요하고 흔히 쓰이는 기능 중 하나임
• Jelen은 피벗 테이블이 수십만 행 데이터에서 4~6번의 클릭만으로 한 페이지 요약 보고서를 만들 수 있게 해주며, 대규모 데이터셋에서 답을 얻는 가장 빠른 방법이라고 말함
• Excel의 정확한 일상 업무 사용자는 알기 어렵지만, 전 세계 Excel 사용자는 수억 명 규모임
• 피벗 테이블은 Excel의 가장 유용한 기능 목록에서 보통 최상위권 또는 그 근처에 위치함
• 데이터 분석가들은 공중보건, 경제 성장, 광고 효과 분석 등 여러 목적에 피벗 테이블을 사용함
• 비영리 노동권 단체 Verité의 선임 프로그램 매니저 Justine Shakespeare는 글로벌 공급망의 이주 노동자 인터뷰 설문 데이터를 분석하는 데 피벗 테이블을 사용함

실제 예시와 학습 자료

• Quartz 기자는 1995년부터의 미국발 멕시코 송금 월별 데이터에서 각 연도 상반기 총 송금액 요약이 필요했음
• 필요한 숫자는 피벗 테이블 삽입, 필요한 데이터 선택, 제외할 월 필터링으로 얻을 수 있었음
• 피벗 테이블 사용법을 더 배우려면 “Excel ninja” Cody Baldwin의 영상을 볼 수 있음
• Google Sheets 사용자는 Sheets 전문가 Ben Collins의 피벗 테이블 소개를 참고할 수 있음

[GN⁺]

Hacker News 의견들

• 이번 주말에 배운 건데, 셀 값 기준으로 행/셀을 그룹으로 나누려면 FILTER 함수를 쓸 수 있음
  =FILTER(Stories!B2:D13,Stories!F2:F13=A2)
  첫 번째 인수 Stories!B2:D13은 스토리들이 들어 있는 셀 범위이고, 두 번째 인수 Stories!F2:F13=A2는 각 셀을 A2 값과 비교하는 열임. 일치하는 행이 =FILTER 수식이 놓인 위치로 복사됨
  스토리 목록을 자동으로 스프린트별로 정리하는 데 쓰고 있고, 프로그램 증분 계획 같은 작업에 유용함
  또 최근에 배운 유용한 Excel 수식은 =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jira-instance.atlassian.net/browse/…),"")임
  A2가 비어 있지 않으면 그 값을 URL 뒤에 붙이고, PROJECT-에 A2 값을 붙인 텍스트를 링크로 보여줌. Emacs와 org-mode로 더 멋지게 만들 수도 있었겠지만, 업무 담당자들과 공유해야 해서 Excel이 맞았음

  • 이런 예시가 정말 유용함. Google Sheets의 QUERY 함수도 많이 도움이 됐고, IMPORTRANGE도 마찬가지임
    행 개수를 얻기 위해 어떤 작업을 했는지 문서화하는 데 도움이 됨
  • 스프레드시트에서 “story”가 뭔지 궁금함

• SSAS 큐브 기반 피벗 테이블은 지금까지 본 최고의 셀프서비스 분석 도구일 수 있음. 여기서 최고란 실제 사용자가 얼마나 쓰느냐로 본 기준임
  업무 사용자가 이미 있는 환경에서 바로 쓰게 만드는 능력이 실제 도입에는 엄청나게 크고, Looker 같은 것과 비교하면 특히 그렇다. Looker는 다른 이유로도 엉망임
  게다가 10번 중 9번은 사람들이 데이터에 가벼운 변환이나 계산을 추가하고 싶어 하니, 이미 Excel 안에 있는 것만큼 좋은 게 없음
  아쉬운 건 OSX 지원이 사실상 없고, MDX 작성이 정말 고통스럽다는 점임

  • 동의하고, 나는 이걸 Power BI로 하고 있음. Power BI 보고서에 데이터를 가져오고 DAX로 계산 측정값이 있는 데이터 모델을 만든 뒤 온라인 서비스에 게시하면, 사용자가 “Analyze in Excel”을 눌러 그 데이터 모델에 연결된 피벗 테이블이 들어 있는 Excel 통합 문서를 내려받을 수 있음
    내가 담당하는 제품의 PM들에게 이걸 제공하는데, 별도 SQL을 작성하지 않고도 피벗만으로 많은 질문에 답할 수 있음
  • 첫 인턴십 중 한 회사에서 OLAP 큐브 + 피벗 테이블 조합을 썼는데 정말 충격적이었음
    데이터 분야의 훌륭한 분석 도구를 이야기할 때 꽤 자주 떠올림. metabase 같은 잘 발전한 BI 도구조차 피벗 테이블만큼 차원을 잘 다루지 못함
  • 2000년대 중반에 우리의 킬러 앱은 MSDN 매거진의 예제 앱이었음. 간단한 웹 페이지 안에 Excel을 임베드하고, 피벗 테이블로 SSAS 큐브를 다룰 수 있게 한 형태였음
    권한 처리와 뷰 저장을 위해 약간의 웹 작업을 했지만, 전체 개발 기간은 아마 일주일도 안 됐을 것임
  • Easy Data Transform을 써봤는지 궁금함. Excel, CSV, 여러 파일 형식에 대해 피벗 같은 데이터 변환을 하는 가벼운 ETL 도구이고 Windows와 Mac에서 네이티브로 실행됨
  • Looker도 Explore 인터페이스에서 피벗 테이블을 만들 수 있었던 걸로 기억함
    Looker Explore에서 CSV나 Excel로 내려받을 수도 있는데, 거기서 부족했던 게 있었는지 궁금함

• 피벗 테이블은 진짜 다차원 스프레드시트의 빈약한 근사치에 가깝고, 예를 들면 Lotus Improv가 있었음: https://instadeq.com/blog/posts/no-code-history-lotus-improv...

  • Microsoft Office의 “범죄” 중 하나는 과장해서 말하자면, 그래픽 아티스트가 아닌 사무 생산성 영역에서 Office나 거의 직접적인 모방품이 아닌 것들이 숨 쉴 공간을 거의 없애버렸다는 점임
    스프레드시트 모델이 좋은 예임. Excel이 Microsoft Office에서 아마 제일 나은 물건일 수는 있지만, 그 결과 Word로 데스크톱 출판을 충분히 잘 못 하면 보통 InDesign으로 가야 하고, 출판 전문가가 아니라면 그건 과한 선택일 때가 많음
  • Improv가 있었다는 걸 몰랐음. Improv의 비전과 비슷한 도구는 있음
    나는 일에서 매일 Anaplan을 쓰는데, 현대적인 클라우드 기반 SaaS 버전의 Improv라면 딱 이런 느낌일 것임. 수십억 달러 규모 회사가 된 이유는 스프레드시트 시장을 정면으로 노리지 않고, 스프레드시트와 잘 어울리게 접근했기 때문으로 보임
    Improv 글의 결론은 기존 스프레드시트 시장에 Improv를 팔려고 한 게 핵심 전략 실수였다는 것임. 더 구조화된 모델이 ‘버그’가 아니라 ‘기능’인 세그먼트에 팔았다면 Lotus가 모델을 배우고 개선하고 다듬어 더 큰 시장도 만족시킬 시간을 벌었을 거라는 내용인데, Anaplan은 이 실수를 하지 않은 듯함. EPM, 즉 기업 성과 관리 시장에서 틈새를 만들었음
  • 그 자체도 가난한 사람의 groupby에 가까움
    어느 순간에는 추상화를 잘못 잡았다는 걸 인정해야 함. 강한 Zalgo 느낌이 남
  • 1990년대 중반 이후로 스프레드시트를 자주 쓰지 않았음. 이유 중 하나는 Improv의 가능성에 기대했지만 미래가 없다는 걸 깨닫고 실망했기 때문임
    피벗 테이블이 그 개념에 대한 다른 접근이었다는 건 당시에는 몰랐음
    스프레드시트를 버린 또 다른 이유는 성능이었음. Improv가 이 면에서 얼마나 좋거나 나빴는지는 기억나지 않지만, 당시 다루던 데이터셋은 스프레드시트에 잘 맞지 않았기 때문에 계속 쓰지는 않았을 듯함
  • 신입 기자였을 때 Lotus Improv의 영국 출시 행사에 갔던 게 기억남. 다른 제품과 비교해 얼마나 화려하고 전문적인지 꽤 두드러졌고, 돌이켜보면 Jobs의 영향이었을 거라고 추정함
    그래도 피벗 테이블이 무엇인지, 그리고 그 기능 하나만으로 새 애플리케이션과 새 하드웨어 플랫폼으로 옮길 만큼 충분한 이유가 되는지를 설명하는 데는 상당히 어려움을 겪었던 것 같음

• Google Ads에서 멋져 보이는 대시보드를 잔뜩 만들었지만, 사용자 조사를 해보면 결국 사람들이 원하는 건 자기 데이터를 피벗 테이블로 보는 것뿐이라는 게 꽤 분명했음

  • 멋져 보이는 대시보드는 너무 유연하지 않음. 사소한 수준을 넘는 집계를 직접 추가할 수 없고, 신경 쓰이는 특정 값 하나에 색을 칠할 수도 없고, 읽기 쉬운 보고서와 설명 텍스트를 바로 만들 수도 없음
    스프레드시트 내보내기 + 피벗 테이블은 이걸 전부 가능하게 해줌. 어느 정도 능숙한 사무직이면 끝없는 백로그-명세-스프린트-개발-테스트-재명세-스프린트 루프를 거치지 않고도 할 수 있음
  • 사용자는 매일 상사가 묻는 업무 질문에 답하기에 그 대시보드가 부족하다고 알려주려 했던 셈인데, 그 신호를 못 받은 것처럼 들림
    일을 처리하는 데 쓸 수 없다면 멋져 보인다는 건 아무도 신경 쓰지 않음. 게다가 멋짐은 매우 주관적임. 팀이 가치를 더한다고 생각한 곳에서 실제로는 시간을 낭비하고 있었던 것임
  • 멋져 보이는 대시보드를 만드는 일을 했는데, 어느 날 사용자들이 “SUM 같은 함수를 넣을 수 있는 열”을 추가해달라고 했음
    얼마 뒤 그만두고 Google Sheets 팀으로 옮겼음

• Microsoft는 이전 직장인 Brio Technology와 그 제품 DataPivot에서 피벗 테이블을 직접 훔쳤음: https://en.wikipedia.org/wiki/Brio_Technology
  CEO에 따르면 Microsoft가 그를 Redmond로 불러 회사 인수를 위해 모욕적으로 낮은 제안을 했고, 거절하면 자체 제품을 만들어 Brio를 망하게 하겠다고 위협했음. 그는 거절했고, 이후 MS는 Excel에 피벗 테이블을 추가했음
  글에는 Lotus도 나오는데, 비슷한 시기에 유사 제품을 만든 듯함. Brio 창업자들은 Metaphor라는 회사에도 관여했고, 아마 일부 아이디어는 거기서 발전했을 수 있음
  Brio 제품에는 CEO의 와인 저장고 내용을 담은 샘플 데이터베이스가 함께 제공됐는데, 바로 그 데이터가 초기 Microsoft Office 박스에서도 보였음

  • Stac Electronics에도 비슷한 일이 있었음: https://en.wikipedia.org/wiki/Stac_Electronics
    다만 Stac은 특허가 있었고, 변호사를 붙여 Microsoft를 특허 침해로 고소했음. Microsoft도 순순히 물러서지는 않았지만, 법원이 양쪽 모두 제품 출시에 라이선스가 필요하다고 판단한 뒤 Stac은 대형 OEM 고객들에게 가서 라이선스를 제안했음
    특허 라이선스를 받거나 제품을 출하하지 못하거나 둘 중 하나였고, OEM들이 Microsoft에 압력을 넣으면서 Stac에 아주 나쁘지 않은 합의로 이어졌음. 소프트웨어 특허가 경쟁에 도움이 될 수 있는 가장 좋은 예 중 하나로 봄
  • MS가 정확히 어떤 부분을 훔쳤는지 궁금함. 행 범주별 데이터 요약 같은 피벗 테이블 개념 자체는 아주 오래전부터 있었으니, 그걸 말하는 건 아닐 것 같음
  • 무엇을 훔쳤다는 건지 궁금함. 기반 기술인지, pivot table이라는 이름인지
    범주화된 데이터 열을 집계하는 개념은 Brio Technology가 발명한 게 아님
  • Microsoft가 CPM을 IBM에 라이선스하려 했지만 소유하고 있지 않아서, 누군가에게 클론을 만들게 한 뒤 그걸 IBM에 라이선스했던 일도 떠오름
  • Lotus Improv가 사실 Brio 제품보다 먼저 나왔음. Improv가 나왔을 때를 분명히 기억하는데, 당시에는 정말 흥미로운 돌파구였음
    하지만 큰 문제는 Lotus 123의 일부가 아니었다는 점이라고 봄. 그래서 Excel이 피벗 테이블을 스프레드시트 안에 직접 넣었을 때 훨씬 더 유용해졌음. 적어도 나에게는 그랬음

• 엔지니어이고 SQL에도 익숙하지만, Excel에서 빠르게 작업하고 피벗 테이블을 즉석에서 만드는 능력은 커리어에 큰 도움이 됐음. 특히 제품과 비즈니스 관리 쪽으로 더 옮겨가면서 그랬음
  피벗 테이블은 여러 차원별 분해를 아주 빠르게 보고, 그 분석을 이리저리 바꿔보며 의사결정을 빠르게 할 수 있게 해줌. 잘 정의된 영역을 위한 기존 도구를 제외하면 이에 맞먹는 게 많지 않음

  • 나는 반대 느낌임. 데이터를 빠르게 BigQuery에 올리고 바로 쪼개 보기 시작함
    Excel에서 클릭하는 것보다 SQL 쿼리를 훨씬 빠르게 날릴 수 있고, 나중에 Excel 시트에서 모든 서식을 다시 맞추는 것보다 기록에서 쿼리를 복사해 붙이는 게 쉬워 재현성도 좋음
  • 피벗 테이블에 깊이 들어가본 적은 없음. 커리어 내내 데이터가 대체로 관계형 데이터베이스에 있었기 때문임
    하지만 스프레드시트의 데이터를 데이터베이스로 옮기는 수고는 과할 때가 많으니, 엔지니어라도 피벗 테이블을 쓰는 게 나쁜 선택은 아니라고 봄

• Pandas에서 이걸 항상 씀. 방금 관련 수업도 가르쳤는데, 문법을 이해하고 나면 아주 유용함
  DuckDB 같은 똑똑한 도구는 그 유용성과 일반 SQL에서 이걸 하기 어려운 고통을 알아보고 구현에 PIVOT을 추가했음. 매우 유용함

  • R로 작업하는 걸 좋아하지는 않지만, tidyr의 피벗/재구성 함수는 이기기 어려움. Python의 비슷한 함수들보다 훨씬 쓰기 편함
  • R의 dplyr 구현이 마음에 듦. pivot_longer와 pivot_wider가 있는데, Postgres에도 이런 게 들어오면 좋겠음
  • 예전에는 Postgres에서 피벗 테이블을 질의하는 플러그인이 있었던 걸로 기억하는데, 쓰기가 너무 고통스러워서 Python/SQLAlchemy 연결 코드로 먼저 열 이름을 질의한 다음 그 열 이름을 바탕으로 두 번째 쿼리를 생성하는 2단계 방식이 더 쉽고 어쩌면 성능도 더 좋았음
    상황이 나아졌기를 바람. 피벗 테이블은 정말 유용하기 때문임
  • Microsoft Access의 시각적 쿼리 도구를 좋아했음. 아주 직관적이었지만 일반인에게는 약간 지나치게 추상적이었을 수도 있음
    SQL도 생성해줬고, 그 덕분에 SQL을 조금 배웠음
  • Oracle에도 PIVOT이 있음. 마지막으로 썼을 때는 열을 수동으로 지정해야 했음

• 그래서 피벗 테이블이 정확히 뭔지 궁금함. 써본 적도 본 적도 없고, 브라우저에서 내장 영상도 로드되지 않았음
  이 글에서 배운 건 스프레드시트 안의 데이터를 보여주는 쉬운 방법이고, Lotus에서 발명됐을 수도 있다는 정도임
  실제로 무엇을 하고 어떻게 쓰는지는 감도 안 옴

  • 테이블의 항목을 범주별로 묶는 드래그 앤 드롭 UI임. 업무 사용자가 보통 이해하는 데 어려움을 겪어서 YouTube에 짧은 영상이 많음. 예시는 여기 있음: https://www.youtube.com/watch?v=qu-AK0Hv0b4
    개인적으로는 역설적으로 아주 유용하면서 동시에 완전 엉망이라고 봄

• 이건 피벗 테이블 일반보다 특정 구현에 대한 얘기일 가능성이 크지만, 조금 더 고급 작업을 하려 하면 자주 부족함을 느낌
  몇 가지를 들면, Excel과 Google Sheets 모두 “값”이 생성되는 방식이 서로 다른 방식으로 매우 제한적임
  피벗 테이블에서 필터링/정렬이 동작하는 방식도 가장 직관적이거나 유연하지 않음
  헤더, 스타일 같은 UI 요소를 제어하기가 매우 어렵고, 피벗 테이블을 만든 뒤 다른 곳에 복사해 수동으로 이것저것 고치는 일이 많음. 그러면 더 이상 동적으로 업데이트할 수 없어서 목적이 희석됨
  스프레드시트 전문가가 아니라서 내가 뭔가를 놓치고 있을 수도 있음

  • 나는 스프레드시트 전문가에 꽤 가까운데, 피벗 테이블은 빠르고 지저분한 임시 데이터 시각화 정도로 분류함. 발표용이거나 스타일이 중요한 곳에는 쓰지 않음
    주로 원본 데이터 열에서 예상치 못한 값을 빠르게 찾는 데 씀. 다만 =unique() 같은 넘침 수식이 생기면서 이 용도로도 점점 덜 쓰고 있음
    사람들이 피벗 테이블에서 더 많은 걸 원할 때는 대개 Power Query를 요구하는 것에 가깝다고 봄. 기능은 이미 있지만 많은 사람이 겁내는 편임. HN은 아닐 수 있어도 일반 사용자층에서는 그렇다
  • 동의함. 현재 구현들은 완전히 깨지기 쉽고, 숙련자나 파워 유저가 기대할 만한 기능 완성도와는 거리가 멂
    이게 너무 거슬려서 직접 스프레드시트 엔진을 쓰기로 했고, 고치고 싶은 통증 지점 중 하나가 이 부분임
  • 이런 온라인 피벗 테이블을 써볼 수 있음: https://www.seektable.com
    헤더를 클릭하는 것만으로 행/열 순서를 바꿀 수 있고, 간단한 입력으로 유지하거나 제외할 항목을 지정해 필터를 적용할 수 있음

• 피벗 테이블은 확실히 강력하지만 이름이 아쉽고, Excel에서 UI를 제대로 다루는 데는 항상 시간이 좀 걸림
  MacOS Numbers의 Categories 기능은 훨씬 쓰기 쉬워서 마음에 들지만, 한계에 닿으면 꽤 짜증나는 제약이 있음
  요즘 복잡한 작업은 스프레드시트를 CSV로 내보낸 뒤 CSVQ로 SQL 쿼리를 돌리는 게 가장 쉬울 때가 많음