Excelのピボットテーブルで元の表にないフィールドの集計値をすばやく求めるテク

元の表にないフィールドで集計値を求めたい

　どんなデータでも分析するには時間がかかりますよね。フィルターをかけたり、集計用のフィールドを追加したりするでしょう。切り口を変えてデータを見たいなら、ピボットテーブルを利用することもあります。

　ただ、ピボットテーブルを作る場合、データ分析に必要なデータがまとめられている必要があります。以下は、日付単位で、席、販売店、チケットの販売数をまとめた表です。

チケットの販売数として［販売日］［席］［販売店］［販売数］が記録された表

　この表を元にしてピボットテーブルを作ると「日付・店舗ごとに販売数を集計する」「席別の販売数を各店舗で集計する」などに切り替えできるようになります。

ピボットテーブルを利用して、日付・店舗ごとに販売数を集計した例

ピボットテーブルを利用して、席別の販売数を各店舗で集計した例

　では、店舗や席単位の売上金額を知りたいときは？　残念ながらピボットテーブルの元になる表に“販売金額”は入力されていないので集計できません。

　販売金額を含めて集計するには、元の表を以下のように整えておく必要があります。単価表からVLOOKUP関数などを利用して［単価］列を挿入、［販売金額］列に計算式を挿入してコピーという作業の流れになるでしょう。

元の表に［単価］列と［販売金額］列を追加した状態

席ごとの販売数と販売金額を集計した例

　上記の作業はそれなりに時間がかかるため、今すぐ集計値が必要な状況では対応できません。今回は、時間がない時にすばやく集計するテクニックを紹介します。ただし、正攻法ではなく、応急的な処理ということを踏まえておいてください。

一意の値の組合わせのみ集計可能

　前提として、“一意の値の組合わせのみ”が集計可能です。先ほどの表であれば、［席］と［単価］の組合わせが該当します。［店舗］と［単価］、［店舗］と［販売数］などは変動するため、ここで紹介する方法では集計はできないことに注意してください。

［席］と［単価］は一意の値の組合わせになる

ピボットテーブルを作成する

　今回は、席ごとの販売金額を今すぐ集計します。販売金額は［単価］×［販売数］で求められますが、元の表に［単価］はありません。［席］の種類分、行を追加して、［単価］列を用意しておきます。

　ピボットテーブルは、元の表を選択して［挿入］－［ピボットテーブル］と選択すれば、すぐに作成できるので悩むことは少ないでしょう。ここでは、［行］（列見出し）に［席］のフィールド、集計値として［値］に［販売数］のフィールドを追加します。

［席］の種類分、行を追加して［単価］列を用意した。［販売日］［販売店］［販売数］は空欄のままにしておく

表を選択して［挿入］－［ピボットテーブル］をクリックする

［テーブル/範囲］が正しいことを確認する。［新規ワークシート］を選択して［OK］をクリックする

［ピボットテーブルのフィールド］ウィンドウが表示されるので、必要なフィールドを追加する。ここでは［行］に［席］のフィールド、［値］に［販売数］のフィールド（集計値のため「合計 / 販売数」と表示される）を追加した

元の表にないフィールドを集計する

　［販売数］×［単価］を計算するために［集計フィールド］を利用します。本来、フィールド同士の値や実数を演算するために利用する機能です。

　今回暫定で用意した［単価］列には、［席］の単価が入力されているので、集計された販売数と掛けあわせてしまえば、とりあえず“販売金額”は求められることになります。

ピボットテーブルを選択すると［ピボットテーブル分析］タブが表示される。［フィールド/アイテム/セット］－［集計フィールド］の順にクリックする

［集計フィールドの挿入］ダイアログボックスが表示される。任意のフィールド名を入力する（ここでは、販売金額）。数式に「= 単価 * 販売数」と入力して［OK］をクリックする

販売金額が集計された。ただし［総計］は正しくないことに注意

　冒頭の「席ごとの販売数と販売金額を集計した例」と同じ集計結果になっています。ただし、［日付］や［店舗］などのフィールドを追加したり、展開したりすると破綻することに注意してください。集計用のデータを整える時間がないときの手段として役立つことがあるかもしれません。