目次
はじめに
Excelはビジネス現場で最も広く利用されている分析ツールの一つです。特に大量のデータを整理・可視化・分析するうえで、ピボットテーブルは欠かせない機能です。本記事では、イベントアンケートや社員属性データを用いたサンプルを題材にしながら、ピボットテーブルの使い方に関して、基本と応用について分かりやすく解説します。Excel初心者から、実務で分析を求められる方まで、すぐに使えるテクニックを紹介します。
ピボットテーブルとは
Excelには、表形式で蓄積されたデータを柔軟に集計・要約するための機能が用意されています。その中でも代表的なものが「ピボットテーブル」です。まず、ピボットテーブルとは、行と列を自由に入れ替えながら、数値を集計・平均・最大値・最小値などで要約できる機能です。
例えば、「アンケート回答」のデータを使って「部署ごとの満足度平均」や「役職別の参加者数」などを瞬時に計算できます。単に数値を並べるだけでなく、データの傾向を視覚的に把握し、意思決定につなげるための強力な武器となります。
テーブルをピボットテーブルに変換することの利点
Excel上での「テーブル」とは、行と列で構成された整形済みの範囲で、構造化参照や自動フィルタ、並べ替えなどが可能なデータ形式を指します。このテーブルを「ピボットテーブル」に変換することには以下のような明確な利点があります。
柔軟な集計方法
ピボットテーブルでは、合計だけでなく、平均・件数・最大値・最小値など、様々な集計方法を選択できます。特に「イベントへの費用感」のようなデータを扱う場合、平均費用や中央値などを算出して分布傾向を探ることができます。
ドラッグ＆ドロップで分析軸を切り替えられる
たとえば、「役職別の参加者数」と「新卒／転職別の満足度」のように、軸を簡単に切り替えることで多角的な分析が可能になります。手作業で関数を使っていたときと比べ、圧倒的に作業効率が上がります。
元データに影響を与えない安全性
ピボットテーブルは元データを直接編集することなく分析を行えるため、原本を守りながら何度でも集計を繰り返すことが可能です。
（「参照のみ」であるため、データを誤って編集してしまう危険性は無いです。）
フィルターやスライサーとの連携
フィルター機能やスライサーを使えば、「満足度が高い（4以上）のみ」や「参加時間が60分以上」など、条件を絞った分析が可能です。分析のスピードと精度が格段に上がります。
※スライサーは別の記事にて紹介します。
テーブルを活用してピボットテーブルを操作してみる
実際の「アンケート回答」と「社員マスタ」のデータを活用しながら、ピボットテーブルとクロス集計を作成してみましょう。
下記のファイルに「作業前のファイル」と「完成ファイル」を配置しています。お手元で画面操作の参考にしてください。
・作業前のファイル（準備まで、完了しています。）
・完成ファイル（すべて完了しています。）
準備
イベントマスタと社員マスタを「従業員ID」でVLOOKUP関数を用いて統合しておきます。これにより、「部署」や「役職」といった社員属性と、「イベント参加時間」や「満足度」などのイベント情報を同時に扱えるようになります。
上記赤枠内の関数
部署：=VLOOKUP([@従業員ID],社員情報[#すべて],2,FALSE)
役職：=VLOOKUP([@従業員ID],社員情報[#すべて],3,FALSE)
入社年度：=VLOOKUP([@従業員ID],社員情報[#すべて],4,FALSE)
新卒／転職：=VLOOKUP([@従業員ID],社員情報[#すべて],5,FALSE)
時短勤務：=VLOOKUP([@従業員ID],社員情報[#すべて],6,FALSE)
XLOOKUP関数に詳しい方は、1つ1つVLOOKUP関数を設定するよりも、XLOOKUP関数で配列数式（一括で複数の列を参照すること）が使えるのではないか？と疑問に思った方もいると思います。
下記のリンク先に記載されており、テーブルでは配列数式は対応していません。
スピル済みの配列数式は、Excel の表自体には対応していないため、表の外のグリッドに配置する必要があります。 表は、独立したデータの行と列を保持するのに最も適しています。
動的配列数式とスピル配列の動作 - Microsoft サポート
そのため、VLOOKUP(もしくは、XLOOKUP)で1列ずつ、関数を設定するしかないです。
テーブルをピボットテーブルに変換
1.「従業員ID（B24セル）」にセルを選択した後、「テーブル デザイン」タブから「ピボットテーブルで集計」ボタンを押下する。
2.以下3点を設定する。
・テーブル/範囲(T)
すでにテーブル化されている場合、テーブル名が設定されていることを確認します。
もし、テーブル化されていなく、表のままである場合は、セルの範囲を指定します。
・ピボットテーブルを配置する場所を選択してください
「新規ワークシート」を選択します。
・複数のテーブルを分析するかを選択
このデータをデータモデルに追加するは、チェックを外します。
※データモデルの話は、「Power Pivot」にて解説します。
3.画面右側に「ピボットテーブルのフィールド」が表示されることを確認する。
以上で、ピボットテーブルの設定は完了です。
集計してみる
実例①：「部署 × 満足度の平均」
1.ピボットテーブルへ以下値を設定する。
行：部署
値：満足度
このままでは「合計 / 満足度」は、回答の合計（回答者数）となるため、満足度の平均を設定します。
2.「合計 / 満足度」から「値フィールドの設定」を選択する。
3.「平均」を選択して、「OK」ボタンを押下する。
4.アンケートの回答結果が平均になったことを確認する。
5.小数点以下を調整するために、「ホーム」タブの「小数点以下の表示桁数を減らす」ボタンを押下する。
以上で完了です。
これにより、どの部署がイベントに満足していたかが一目で分かります。
実際にハンズオンしてみるとわかりますが、実際のデータ（データ可視化シート）のデータは全く変更を加えません。新規で作成したSheet1内でピボットテーブルを用いたデータ参照となるため、安心して操作できます。
実例②：「イベント参加有無 × 新卒／転職」でクロス集計
1.ピボットテーブルへ以下値を設定する。
フィルター：入社年度
行：新卒／転職
列：イベント参加有無
値：従業員IDの件数（=人数）
2.フィルターを選択して、「複数のアイテムを選択」にチェックを入れた後、「2024」を選択。OKボタンを押下する。
3.2024年の新卒と転職を比較すると、新卒よりも転職した方のほうが、参加率は高い傾向にある。
4.比率で出したい場合、表の中で「右クリック」を行い、「計算の種類」から「行集計に対する比率」を選択する。
5.比率で結果が表示されます。
このようにして、属性ごとの参加傾向を明確にできます。転職者の方がイベント参加率が高いといった傾向が見えてくるため、クロス集計にフィルターで絞りながら傾向を把握することができます。
これまでピボットテーブルを使わなかった方は、表に関数(AVERAGE関数やSUM関数など）を設定して、都度集計する手間が発生していたと思います。表は、人為的作業ミスが発生しやすくなるため、ピボットテーブルを使った、集計を行う方が、ミスを減らすうえでも非常に有効です。
まとめ
本記事では、Excelにおけるピボットテーブルとクロス集計の基本的な使い方から、実データを使った応用例までを解説しました。
ピボットテーブルは、複雑な集計をドラッグ＆ドロップで直感的に操作できる強力なツールであり、実務では数千・万行を超えるデータでも瞬時に集計できるため、意思決定のスピードが格段に向上します。さらに、テーブル形式に変換してからピボットテーブルを作成することで、データ管理がしやすくなり、将来的な更新や再集計もスムーズに行えます。
また、クロス集計はカテゴリ間の関係性や傾向を視覚的に把握するのに非常に有効であり、アンケートやマーケティングデータの分析に特に威力を発揮します。Excelのピボットテーブルを使いこなすことで、単なるデータの集まりが“ビジネスインサイト”へと変わります。
皆さんもぜひ、今回の解説を参考に、自分のデータで試してみてください！