ピボットテーブルはまるで魔法のツールです。
何故ならマウス操作だけで様々な集計を瞬時にできてしまうからです。
この記事では集計の基礎から、ピボットテーブルの作成や操作の方法までを詳しく解説します。
ピボットテーブルを業務に活用したいと考えている方は是非参考にしてみて下さい。
そもそも集計とは？
Excelを使い始めてまず最初に覚える関数はSUM関数かもしれませんね。このSUM関数こそ集計の基本です。
SUM関数は表中の数値の列を全て合計するというような使い方をします。
集計対象 と 項目
しかし、実際の仕事になると全体の集計ではなく、項目ごとに分けて集計するとか、データに日付がある場合には、月ごと、年ごとの集計が必要になるケースが多くあります。
これは実際の例で見ると以下の図のようになります。
つまり、集計対象の数値の列と、集計区分を分ける項目の列と大きく2つに分類して考えることができます。
こうなってくるとSUM関数では対処できなくなってしまい、SUMIF関数などの更に高度な関数を使う必要が出てきます。SUMIF関数は SUM関数より設定が多くなりますし、使い方を誤ると全体の合計値が合わないという事にもなりかねません。
集計のニーズは様々
さらに困ったことに集計のニーズは色々と変化することがあり、「やっぱり四半期毎の集計が必要」とか、「その地区は支店ごとの集計も見てみたい」などと次々と異なる集計表を作成しなくてはならないことが良くあります。
そのたびにSUMIF関数を使って集計表を作成するとなると手間であることは勿論ですが、手作業となるためにミスが発生する可能性も高くなります。
ピボットテーブルで面倒は全部解決！
このような集計に関する面倒はピボットテーブルを使うことで全て解決できてしまいます。
しかも、Excelの関数は1つも覚える必要はありません。全部マウスで操作するだけです。
これは使わない手はありませんよね！？
しかし、Excelをもう何年も使っている人でも「ピボットテーブルって何？」という人がとても多いので筆者は「もったいないなぁ〜」と思うのです。
では、早速ピボットテーブルを学ぶ上で必須の基礎知識の解説から始めたいと思います。
【基礎知識】複雑そうで実はシンプル！集計の3つのパターン
世の中には様々な集計表があって難しそうに感じるかもしれませんが、本質は実にシンプルで、ここでご紹介する3つのパターンを知っておけばほぼ全ての集計表の構造が理解できると思います。
3つのパターンは次のようになります。
項目別集計
項目別集計はこの記事の冒頭でも紹介しましたように、下の図のような集計になります。
この表では仮想のとある会社の売上が支店ごとに分けて集計されています。元の表の赤枠で示した2列の情報を使って集計されていることが分かると思います。
項目が複数になるケースも
項目別集計は使用する項目が複数になることもあります。例えば、支店ごとに分けた後で、更に月ごとに集計する場合です。この集計は下の図のようになります。
少し縦長の表になってしまいました。もう少し見やすい形にはできないものでしょうか？
縦と横の集計
上の表をもう少し見やすくしたのが下の図になります。同じように項目別の集計となりますが、これが縦と横にそれぞれ項目が展開されています。
こうして縦と横に項目を展開することでかなり見やすい表になったかと思います。
私たちが通常見る画面や紙面は２次元ですので、表は縦と横に展開するのが限界です。数学的には３次元、４次元と増やすことはできますが、私たちにとって理解しやすいのは２次元までですので、「縦と横」まで知っていればOKです。
項目の絞り込み／フィルター
Excelを使いこなしている方の中にはフィルター機能というのを使ったことがある方もおられるかもしれません。
これは項目別集計をする際に、集計する項目を限定して絞り込むことです。
例えば下の図のようにいくつかある支店のうち、横浜支店と京都支店だけの集計表をまとめるようなケースです。
これは情報量が多い時に特定の範囲だけを確認したいケースに有用です。
集計パターンまとめ
いかがでしたでしょうか？言われてみれば「当たり前」と感じられたのではないでしょうか？
ここで理解頂いた基礎知識はピボットテーブルを使いこなす上で非常に役に立ちますので頭にとどめておいて下さい。
では、いよいよピボットテーブルの作成と基本操作の解説にいきましょう！
ピボットテーブルの作成と基本操作
ピボットテーブルを作成
その前に集計する表が必要
ピボットテーブルを作成するには集計対象となる表（テーブル）が必要です。ピボットテーブルが処理する表（テーブル）は以下の条件を満たしている必要があります。
- １行目は項目名
- ２行目からは値
- 列ごとに文字列、数値、日付などデータの種類が同じ
- セルが結合されていない
Excelで「テーブルとして書式設定」した表はこの条件を満たせるように設定されていますので、これを使うことをオススメします。
ここではサンプルとして以下のダウンロードリンクのExcelファイルを例に解説しますので、よろしければダウンロードしてお使い下さい。
ピボットテーブル作成は簡単４クリック！
集計対象の表が準備できたら下の図のように操作しましょう。すると新しいシートにピボットテーブルが作成されます。
ピボットテーブル集計の基本操作
集計対象を設定
集計するのは数値になりますので、サンプルの場合は「売上」になります。
「ピボットテーブルのフィールド」が表示されていると思いますので、そこで「売上」を「Σ 値」とあるエリアにドラッグ＆ドロップします。
集計項目を設定
続いて集計項目を設定します。最初は「店名」（支店）ごとの集計をするので、「店名」を「行」のエリアにドラッグ＆ドロップします。すると、「店名」（支店）ごとの集計表が自動で作成されたと思います。
例えば、続いて「月」を「列」のエリアにドラッグ＆ドロップしてみて下さい。すると、横に「月」が展開された表が作成されたと思います。
今度は「列」のエリアに入れた「月」を「行」のエリアに入れてみましょう。すると今度は、縦に「店名」（支店）ごと、更に「月」ごとの集計がされました。
更に、「月」を「店名」の上にドラッグ＆ドロップすると集計の順番が変わって、「月」ごとに続いて「店名」（支店）ごとの集計になりました。
下に動画も載せましたので操作の様子を眺めてみて下さい。マウス操作に合わせて瞬時に集計ができていますね！
項目を絞るフィルター
この例で例えば１月から３月の売上を集計したい場合は、フィルター機能を活用します。
「月」を「フィルター」のエリアにドラッグ＆ドロップします。
続いて「月」の項目の右端にある下矢印をクリックしてフィルター項目の選択ウィンドウを出します。ここで１，２，３にチェックを入れて「OK」を押すとフィルタの設定が完了します。
ピボットテーブルの更に高度な活用方法
基本操作だけでもかなり便利に活用できますが、ピボットテーブルには更に高度に活用するための機能がついていますので紹介させていただきます。
集計方法／統計計算
ここまでの操作では数値を合計するのみでした。しかし、設定を変更することで「データの個数」、「平均」、「最大」、「最小」という統計計算に変更することも可能です。
この設定にはまず、「Σ 値」エリアに入れた項目の右端にある下矢印をクリックして、出てきたメニューの「値フィールドの設定」をクリックしてサブウィンドウを出します。
ここで集計方法を選べるサブウィンドウが出たと思いますので、必要な集計方法を選んで「OK」を押します。
計算の種類
さらに複雑な計算として、「集計値に対する比率」や「基準値との差分」、「累計」などの計算方法も設定することが可能です。
集計方法の設定で出したサブウィンドウの「計算の種類」タブを選択するとこの設定ができます。
下の図は「累計」計算の例になります。毎月前月までの売上を合計した「累計」を計算しています。最後の12月の棒グラフが年間の売上と同じということになります。
スライサー
フィルター設定の操作が頻繁にある場合は「スライサー」を使いましょう。
図のようにしてスライサーを出すことでクリックだけで絞り込み項目を選ぶことができます。項目を切り替えて集計結果を確認する際に非常に便利です。
まとめ
魔法の集計ツール、ピボットテーブルについて集計に関する基礎知識から詳細な操作手順まで解説させて頂きました。
これまでSUM関数やSUMIF関数などを使っていた方には「目から鱗」かもしれません。
ピボットテーブルはより大規模なデータでも軽々と動きますのでかなり高度な分析作業にも使えます。
読者の皆様も是非ピボットテーブルを使いこなしてスキルアップを図ってみて下さい。
