ピボットテーブル · スプレッドシートのデータ分析
Pivot Table
ピボットテーブルとは?
- ドラッグで作るクロス集計表
- 元データは1行に1件でなければならない
- 質問を変えて、複数の角度から見やすい
ピボットテーブルを使い始めると、最初に変わるのはレポートを作る順番です。普通は"部署別に出してください"、"今度は四半期別で"といった要望が来るたびに、新しい表を一つずつ作ります。ピボットは元データはそのままに、見る角度だけを差し替える方式なので、要望一つに表一つを追加していた構造が、元データ一つに複数の画面を付ける構造に変わります。結果として担当者の時間は、表を作ることから数字を読み解くことへ移っていきます。
値の領域は合計のほか、件数・平均・最大・最小・比率などに変えられ、日付フィールドは右クリック一度で月・四半期・年単位にまとめられます。ただし、'受講者数何名'のような重複を除いた一意の件数は標準のピボットテーブルでは出ず、作成時にデータモデルへ追加して初めて集計オプションが表示されます。似たことはSUMIFSでもできますが、用途が違います。書式が固定された定例レポートは数式、基準を何度も変えて見る探索的な作業はピボットテーブルが向いています。複数のファイルを毎月まとめる必要がある場合や、行数が数十万件を超える場合は、Power QueryやBIツールに移るタイミングです。
現場でピボットの数字が狂う理由は機能ではなく、ほとんど元データにあります。外部から受け取った金額がテキストとして保存されていると、その行は合計からこっそり抜け落ち、元データに行を追加しても範囲を表(Ctrl+T)として指定していない限り、新しいデータは反映されません。ここに更新を忘れたり、フィルターのかかった画面をそのままキャプチャしたりすると、会議資料の総額が財務の締め数値と数千万ウォンずつずれてしまいます。さらに厄介なのは、ピボットはエラー表示なしにもっともらしい表を見せてしまうという点で、報告の前に総計を元データの合計と一度照合する手順が必要です。
1991年、Lotus Improvがデータを複数の軸で再構成する概念を初めて示し、Microsoftが1994年のExcel 5.0で「ピボットテーブル」という名前で導入して広く普及しました。その後、ほとんどの表計算ソフトが同じ機能を提供しています。
役職員620名規模の自動車部品製造業A社の話です。人材開発チーム3名が年間教育費4億3千万ウォンを執行していますが、四半期ごとに経営会議用の部署別・課程別集計表を作るのに丸4日かかっていました。受講履歴がチームごとのExcelファイル12個に散らばっていたためです。(仮想の例)
- 人材開発チームのキム課長が12個のファイルを1つのシートにまとめる際、結合セルとチーム別の小計行47個を取り除き、日付・部署・課程名・受講者・金額が1件1行になるよう元データを組み直しました。
- 範囲を表として指定したうえで、行に部署、列に月、値に金額の合計を置いたところ、3分で最初のクロス集計表が出来上がり、隣の主任が「これを4日がかりで作っていたのか」と言いました。
- 日付を四半期でグループ化し、値を1人あたりの平均に変えてみたところ、生産本部リーダーシップ課程の単価が事務職より1人あたり18万ウォン(約2.2万円)高いという事実が、そこでようやく目に入りました。
- 財務チームのイ次長が"総額が2,800万ウォン(約335万円)合わない"と指摘して確認したところ、委託機関から受け取った精算表の金額の一部がテキストとして保存され、合計から抜けていました。数値に変換すると締め処理の数字と一致しました。
- 四半期の集計作業が4日から半日に減りました。役員が会議中に「役職別にも見てみよう」と言うと、キム課長がフィールドを入れ替えて3分で画面に表示しました。
ク・ジャリョン
金鍾赫