スプレッドシート最強のツール、ピボットテーブル（2020）
(qz.com)- ピボットテーブルは、コーディングや高度な数学なしに大規模データをすばやく要約できるようにし、スプレッドシートを分析ツールに変えた中核機能である
- Lotusの Pito Salas は、ユーザーがカテゴリ別の要約統計やクロス集計を作るために複雑な数式を書かなければならない不便さから、クリックベースのツールを構想した
- Steve Jobsは、NeXTコンピュータの需要を生み出す キラーソフトウェア を探す過程でこの機能の可能性に気づき、Lotus Improvの開発につながった
- Lotusチームの flexible views はその後Lotus 1-2-3とExcelに取り入れられ、Excelでpivot tableという名称が定着した
- 現在、ピボットテーブルはExcelとGoogle Sheetsで広く使われており、数十万行のデータでも数回のクリックで1ページの要約レポートに圧縮できる
数式なしでデータを要約するツール
- ピボットテーブルは、一般ユーザーが大規模データセットをすばやく分析できるようにするスプレッドシート機能である
- コーディング能力や高度な数学知識がなくても、ユーザーはマウスで指定してクリックするだけでデータを要約できる
- 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回のクリック だけで1ページの要約レポートを作成でき、大規模データセットから答えを得る最速の方法だと述べている
- Excelの正確な日常業務ユーザー数を知るのは難しいが、世界中のExcelユーザーは 数億人 規模である
- ピボットテーブルは、Excelの最も有用な機能リストで通常、最上位またはその近くに位置する
- データ分析者は、公衆衛生、経済成長、広告効果分析などさまざまな目的でピボットテーブルを使っている
- 非営利の労働権団体VeritéのシニアプログラムマネージャーJustine Shakespeareは、グローバルサプライチェーンにおける移民労働者へのインタビュー調査データの分析にピボットテーブルを使用している
実例と学習資料
- Quartzの記者は、1995年以降の米国発メキシコ向け送金の月次データから、各年の 上半期総送金額 の要約が必要だった
- 必要な数値は、ピボットテーブルの挿入、必要なデータの選択、除外する月のフィルタリングで得られた
- ピボットテーブルの使い方をさらに学ぶには、“Excel ninja” Cody Baldwinの 動画 を見るとよい
- Google Sheetsユーザーは、Sheets専門家Ben Collinsの ピボットテーブル入門 を参考にできる
1件のコメント
Hacker News のコメント
今週末に学んだことだが、セルの値を基準に行／セルをグループ分けするには FILTER 関数を使える
=FILTER(Stories!B2:D13,Stories!F2:F13=A2)1つ目の引数
Stories!B2:D13はストーリーが入っているセル範囲で、2つ目の引数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 が適していた行数を得るためにどんな作業をしたかを文書化するのに役立つ
SSAS キューブベースのピボットテーブルは、これまで見た中で最高のセルフサービス分析ツールかもしれない。ここでいう最高とは、実際のユーザーがどれだけ使うかという基準での話
業務ユーザーがすでにいる環境でそのまま使わせられる能力は、実際の導入では非常に大きく、Looker のようなものと比べると特にそうだ。Looker は別の理由でもひどい
さらに10回中9回は、人々はデータに軽い変換や計算を追加したがるので、すでに Excel の中にあるものほど良いものはない
残念なのは OSX 対応が事実上なく、MDX を書くのが本当に苦痛な点
私が担当する製品の PM たちにこれを提供しているが、別途 SQL を書かなくてもピボットだけで多くの質問に答えられる
データ分野の優れた分析ツールについて話すとき、かなり頻繁に思い出す。metabase のようなよく発展した BI ツールでさえ、ピボットテーブルほど次元をうまく扱えない
権限処理とビュー保存のために少し Web 側の作業をしたが、開発期間全体はおそらく1週間もかからなかったはず
Looker Explore から CSV や Excel としてダウンロードすることもできるが、そこで何が足りなかったのか気になる
ピボットテーブルは実のところ、多次元スプレッドシートの貧弱な近似に近く、たとえば Lotus Improv があった: https://instadeq.com/blog/posts/no-code-history-lotus-improv...
スプレッドシートモデルは良い例だ。Excel は Microsoft Office の中でおそらく一番ましなものかもしれないが、その結果、Word でデスクトップパブリッシングを十分うまくできない場合、たいてい InDesign に行くことになり、出版の専門家でなければそれはやり過ぎであることが多い
私は仕事で毎日 Anaplan を使っているが、現代的なクラウドベース SaaS 版の Improv ならまさにこういう感じだろう。数十億ドル規模の会社になった理由は、スプレッドシート市場を正面から狙わず、スプレッドシートとうまく共存する形でアプローチしたからだと思う
Improv の記事の結論は、既存のスプレッドシート市場に Improv を売ろうとしたことが核心的な戦略ミスだったというもの。より構造化されたモデルが「バグ」ではなく「機能」であるセグメントに売っていれば、Lotus はそのモデルを学び、改善し、磨き上げて、より大きな市場も満足させる時間を稼げただろうという内容だが、Anaplan はこのミスをしなかったようだ。EPM、つまり企業業績管理市場でニッチを作った
どこかの時点で、抽象化を間違えたと認める必要がある。強い Zalgo 感がある
ピボットテーブルがその概念への別のアプローチだったとは当時は知らなかった
スプレッドシートを捨てたもう一つの理由は性能だった。Improv がこの点でどれほど良かったか悪かったかは覚えていないが、当時扱っていたデータセットはスプレッドシートにはあまり合わなかったので、使い続けることはなかったと思う
それでも、ピボットテーブルとは何か、そしてその機能一つだけで新しいアプリケーションと新しいハードウェアプラットフォームへ移る十分な理由になるのかを説明するのには、かなり苦労していたように思う
Google Adsで見栄えのするダッシュボードを大量に作ったが、ユーザー調査をしてみると、結局人々が求めているのは自分のデータをピボットテーブルで見ることだけだ、というのはかなり明らかだった
スプレッドシートへのエクスポート + ピボットテーブルなら、これが全部可能になる。ある程度慣れたオフィスワーカーなら、終わりのないバックログ-仕様-スプリント-開発-テスト-再仕様化-スプリントのループを経なくてもできる
仕事を片付けるのに使えないなら、見栄えがいいことなど誰も気にしない。しかも見栄えの良さは非常に主観的だ。チームが価値を加えていると思っていたところで、実際には時間を無駄にしていたのだ
しばらくして辞め、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には特許があり、弁護士をつけてMicrosoftを特許侵害で訴えた。Microsoftもおとなしく引き下がったわけではないが、裁判所が双方とも製品出荷にはライセンスが必要だと判断した後、Stacは大手OEM顧客のもとへ行き、ライセンスを提案した
特許ライセンスを受けるか、製品を出荷できないかのどちらかで、OEM各社がMicrosoftに圧力をかけたことで、Stacにとってそれほど悪くない和解につながった。ソフトウェア特許が競争に役立ち得る最良の例のひとつだと思う
カテゴリ化されたデータ列を集計するという概念は、Brio Technologyが発明したものではない
ただ大きな問題は、Lotus 123の一部ではなかったことだと思う。だからExcelがピボットテーブルをスプレッドシート内に直接入れたとき、ずっと便利になった。少なくとも自分にとってはそうだった
エンジニアでSQLにも慣れているが、Excelで素早く作業し、その場でピボットテーブルを作る能力はキャリアに大いに役立った。特にプロダクトやビジネス管理寄りに移っていくにつれてそうだった
ピボットテーブルは、さまざまなディメンション別の分解を非常に素早く見て、その分析をあれこれ変えながら意思決定を速くできるようにしてくれる。よく定義された領域向けの既存ツールを除けば、これに匹敵するものは多くない
ExcelでクリックするよりSQLクエリをずっと速く投げられるし、後からExcelシートですべての書式を整え直すより、履歴からクエリをコピーして貼り付けるほうが簡単で、再現性も高い
とはいえ、スプレッドシートのデータをデータベースに移す手間は過剰なことが多いので、エンジニアであってもピボットテーブルを使うのは悪い選択ではないと思う
Pandasでこれはいつも使っている。ちょうど関連する授業も教えたところだが、構文を理解してしまえば非常に便利だ
DuckDBのような賢いツールは、その有用性と、通常のSQLでこれを行うつらさを理解して、実装にPIVOTを追加している。非常に便利だ
pivot_longerとpivot_widerがあり、Postgresにもこういうものが入るといい状況が改善していることを願う。ピボットテーブルは本当に便利だからだ
SQLも生成してくれ、そのおかげでSQLを少し学べた
それで、ピボットテーブルとは正確には何なのか気になった。使ったことも見たこともなく、ブラウザでは埋め込み動画も読み込まれなかった。
この記事で学んだのは、スプレッドシート内のデータを表示する簡単な方法で、Lotusで発明されたのかもしれない、という程度。
実際に何をして、どう使うのかはまったく見当がつかない。
個人的には、逆説的ですが、非常に便利であると同時に完全にめちゃくちゃだと思っています。
これはピボットテーブル一般というより特定の実装の話である可能性が高いけれど、少し高度なことをしようとすると、よく物足りなさを感じる。
いくつか挙げると、ExcelもGoogle Sheetsも、「値」が生成される仕組みがそれぞれ別の形でかなり制限されている。
ピボットテーブルでフィルタリングや並べ替えが動作する仕組みも、最も直感的でも柔軟でもない。
ヘッダーやスタイルのようなUI要素を制御するのは非常に難しく、ピボットテーブルを作った後で別の場所にコピーし、手作業であれこれ直すことが多い。そうすると、もはや動的に更新できなくなり、目的が薄れてしまう。
スプレッドシートの専門家ではないので、私が何か見落としているのかもしれない。
主に元データの列から想定外の値を素早く見つけるために使います。ただし、
=unique()のようなスピル数式が登場してからは、この用途でもだんだん使わなくなっています。人々がピボットテーブルにもっと多くを求めるとき、それはたいていPower Queryを求めているのに近いと思います。機能自体はすでにありますが、多くの人は怖がる傾向があります。HNでは違うかもしれませんが、一般ユーザー層ではそうです。
これがあまりに気になったので、自分でスプレッドシートエンジンを書くことにしました。直したい痛点の一つがまさにここです。
ヘッダーをクリックするだけで行/列の順序を変えられ、簡単な入力で保持または除外する項目を指定してフィルターを適用できます。
ピボットテーブルは確かに強力だけれど名前がいまいちで、ExcelでUIをうまく扱うにはいつも少し時間がかかる。
MacOS NumbersのCategories機能はずっと使いやすくて気に入っているが、限界に達するとかなりいら立つ制約がある。
最近、複雑な作業ではスプレッドシートをCSVにエクスポートしてから、CSVQでSQLクエリを実行するのが一番簡単なことが多い。