ルール 39
ピボットテーブルの範囲は必ず列単位で
データが追加されても確実に更新できる

マウス操作で集計できるのがピボットの利点

ピボットテーブルは、Excelの中でも強力な機能の1つです。主な活用シーンとしては、管理端末やシステムからボリュームのあるCSVデータを取得し、それをワークシート上で目的別に集計する、といった場面が挙げられるでしょう。

ピボットテーブルを使うことで、項目別の集計や2つ以上の項目を掛け合わせたクロス集計を実現できますが、必要な現場と、そうでない現場が二極化しがちです。よって、Excelを使える人の中でもスキルに差があることが多いのですが、ピボットテーブルを使うなら誰でも必ず実践してほしいのが本記事のルールです。

ピボットテーブルの作成方法を踏まえつつ解説しましょう。以下はレストランのオーダーシステムから取得した1日分の注文ログです。システムの仕様上、メニュー名はカタカナになっています。

注文ログの例

このデータでも1日の売上合計を求めることはできますが、メニューごとの売上となると集計作業が必要です。注文ログの例をよく見ると、「Menu」列には同じメニューが複数回登場しています。このメニューごとの集計をピボットテーブルで行います。

ピボットテーブルを作成するには、基のデータとなる表の中にアクティブセルを置いた状態から操作しましょう。［ピボットテーブルの作成］ダイアログボックスで、表の範囲が自動的に取得されます。配置する場所は［新規ワークシート］がおすすめです。

ピボットテーブルの作成

［ピボットテーブルの作成］ダイアログボックス

［ピボットテーブルの作成］ダイアログボックスで［OK］ボタンを押すと、［ピボットテーブルのフィールド］作業ウィンドウが画面右側に表示されます。「フィールド」とは、ピボットテーブルにおけるデータの項目のことです。

フィールドは基の表の列に相当し、列見出しがフィールドの名前として並んでいます。ここでは「メニューごとの売上」を集計したいので、まずは売上に相当するフィールドである［Price］を［値］のボックスにドラッグします。フィールド一覧の［Price］にチェックを付けても、同様に［値］のボックスに入ります。

ピボットテーブルの［値］の設定

すると、シート内に以下のようなピボットテーブルが表示されます。現時点ではPriceを集計しているだけで、これは基の表で「Price」列をSUM関数で合計した結果と同じです。

作成したピボットテーブル

続いて、メニューに相当する［Menu］フィールドを［行］のボックスにドラッグします。すると、ピボットテーブルの行方向にメニューの一覧が並び、それぞれの売上合計が表示されました。

これと同じ集計はSUMIF関数でもできますが、ピボットテーブルならマウス操作だけで、一気に結果を求めることが可能です。

ピボットテーブルの［行］の設定

ピボットテーブルの合計値

データの更新時にミスが発生しやすい

ピボットテーブルでの集計方法はほかにもありますが、基本を踏まえたところで、本記事のルールについて見ていきます。

ピボットテーブルを日々扱っていると、基の表を差し替えたり、データを追加したりする作業がよくあります。ピボットテーブルでは、こうした更新時にミスが発生することがよくあります。

先ほどの例を続けましょう。その後、オーダーシステムのエラーで記録されなかった注文が2件見つかり、システムに手動で入力したうえで、注文ログを再出力したとします。

そして、ピボットテーブルの基の表を、再出力した注文ログで上書きしました。末行に2件分のデータが追加されています。

基の表にデータを追加

ピボットテーブルは自動的には再計算されないため、以下のように明示的に更新の操作を行います。作成時と同じく、基の表の中にアクティブセルを置いた状態で操作します。

更新の操作自体は正しいはずですが、更新前と更新後で集計値に変化がありません。いったい何がいけなかったのでしょうか？

ピボットテーブルの更新

ピボットテーブルの更新前（左）と更新後（右）

列単位で取得すれば確実に更新できる

ピボットテーブルの集計値が更新されない原因は、ピボットテーブルの範囲を確認すれば分かります。［データソースの変更］ボタンで範囲を表示してみましょう。

ピボットテーブルの範囲の表示

ピボットテーブルの範囲

［ピボットテーブルのデータソースの変更］ダイアログボックスを確認すると、範囲が絶対参照で固定されていました。そのため、基の表にデータの追加があっても、ピボットテーブルの集計対象に含まれず、更新もされないのです。

これはSUMIF関数などと同じく、範囲を列単位で指定することで解決できます。［テーブル/範囲］の入力欄にカーソルを置き、表の列全体（この例ではA～E列）を指定してください。

ピボットテーブルの範囲を変更

列単位で取得しておけば、基の表にデータが追加されても、［更新］ボタンで確実に再計算されるようになります。ピボットテーブルの作成時には、常にこのルールを意識してください。

補足として、列単位で範囲を指定すると、ピボットテーブルの末尾に「（空白）」という項目が表示されます。基の表の空行が集計された結果で、やや不格好ではありますが、実害はありません。そういうものだと思って、そのままにしておいてください。データの取得ミスがないよりはマシです。

列ごと取得した結果