Excelのピボットテーブルは非常に活用できる機能の1つです。
事務職やデータ分析を仕事としている方は、管理端末やシステムから膨大なCSVデータを取得し、それをワークシート上で目的別に集計する、といった経験があるでしょう。
そこで今回の記事では、Excelのピボットテーブルの活用方法や更新にエラーがでたときの対処法をご紹介していきます。
初心者の方だけでなく、ピボットテーブルを使いこなしている方も、ぜひおさらいしてくださいね。
目次
ピボットテーブルとは？
ピボットテーブルとは難しいプログラミングやコマンドといった専門知識がなくても、大量のデータを整理や集計、分析ができる強力な機能です。
数値が入力された一般的な表とは異なり、柔軟性に富んだ結果の表示に強く、昨今ではTableauなどのダッシュボードのローデータとしても活用されています。
営業事務などのオフィスワークでは、クライアントの売上表をもとにピボットテーブルを起動すれば「月別にいくら売上があったか」や「全クライアントの売上順位」などが一目でわかるようになります。
ピボットテーブルの作成・更新してみよう
それでは、実際にピボットテーブルを作成し、更新の仕方を解説していきます。
たとえば、上京してきた兄妹がこれからの生活を考えて3人で家計簿をつけることにしました。その際に、上図のように何日にいくら使っているかをExcelにしたとします。
これでも「何日に3人が合計していくら使ったか」はわかるのですが、「何曜日に多く使いがちなのか」などといった深い分析まではできません。
そうした分析に役立つのがピボットテーブルです。
ピボットテーブルを起動させるにはExcelの左上にある「ピボットテーブル」をクリックします。
表示された画面で「OK」をクリックするとピボットテーブルが起動します。
ピボットテーブルが起動できたら、表示させたい結果や目的に応じて項目を選択、または項目をドラッグすればピボットテーブルができます。
まず、次のようにフィールドで集計したい項目、今回は「日付」「曜日」「合計値」にチェックをいれましょう。
続いて、フィールド内の項目を下のボックスにドラッグします。
列に「日付」、行に「曜日」をドラッグしましょう。
すると、以下のように曜日別の総計が出てきます。
これを見ると、水曜日や金曜日にお金を使いすぎる傾向があることがわかりました。
この元データであるExcelに、どんどん3人の使用した金額が追加されていっても、ピボットテーブルを更新すれば最新のデータになります。
ピボットテーブルの更新方法は、[更新] をクリックするだけでOK。ブック内のピボットテーブルのデータが更新されます。
また、Excel以外のデータベース 、たとえばSQL Server、Oracle、AccessといったAnalysis Services キューブ、データフィード、そのほか多くのソースと連携していても、Power Queryからインポートされたピボットテーブルが更新されるようになっています。なお、 外部データソースに対するすべての変更が自動的に含まれるExcel テーブルからもデータを更新することができます。
また、通常、ピボットテーブルは自動的には更新されません。しかしながら、ピボットテーブルを含むブックを開いたときにピボットテーブルが自動的に更新されるように指定することが可能です。
補足
[このデータをデータ モデルに追加する] を選択すると、このピボットテーブルで使用されているテーブルまたは範囲がブックのデータ モデルに追加されます。
データモデルを使用すると、複数のテーブルのデータを統合し、Excel ブック内にリレーショナルデータソースを構築することができます。
Excelでは、データモデルが透過的に使用され、ピボットテーブルとピボットグラフで使用される表形式のデータが提供されます。
このデータモデルはフィールドリスト内のテーブルのコレクションとして視覚化され、ほとんどの場合、データ モデルが存在することさえわかりません。
出典：Microsoftサポート｜Excelでデータモデルを作成する
ピボットテーブルが更新されるとどうなる？
ピボットテーブルの作成後、テーブルの元データが変更されたらピボットテーブルも更新しなければ正しい分析はできません。
元データに変更があった場合、ピボットテーブルにはどのようなことが起こるのでしょうか。
ここでは、ピボットテーブルが更新された後の3つのケースについて解説していきます。
1.元データの数は同じで値が変更
1つ目は、元データの数が同じで値のみ変更があった場合です。ピボットテーブルの結果を更新することで同期することができます。
手順は次のフローで
［ピボットテーブル ツール］－［分析］タブ－［データ］グループ－［更新］ボタンより更新ができます。こちらはExcel2013での方法です。
Excel2007やExcel2010の場合は
［ピボットテーブル ツール］－［オプション］タブ－［データ］グループ－［更新］ボタンより更新、というフローになります。
2.元データより数が減少
2つ目は、元データより件数が減った場合です。
1つ目のケースと同じように［更新］を行うことで解決します。
しかし、ピボットテーブルに設定されている元データの範囲は変わらないため、テーブルには「(空白)」という項目が表示されます。
この空白の項目を削除したい場合、［行ラベル］または［列ラベル］のフィルターより「(空白)」をオフにして非表示にします。
ピボットテーブルのフィルタで「(空白)」をオフにすると、元データの範囲は変わりませんが、「(空白)」項目の非表示となります。
3.元データより数が増加
3つ目は、元データより件数が増えた場合です。
［更新］を行っただけでは、ピボットテーブルに設定されている元データの範囲は変わらないため、増えた分のデータはピボットテーブルには反映されないので注意が必要。
まずは、ピボットテーブル元のデータ範囲を変更する必要が出てきます。
たとえば、最初に作成したピボットテーブルの元データのセルA20以降に、同じデータが4件増えたとしましょう。（「aaa」×「ア」が4件増加）。
Excel2013では、［ピボットテーブル ツール］－［分析］タブ－［データ］グループ－［データソースの変更］ボタンをクリックします。
一方、Excel2007、Excel2010は、［ピボットテーブル ツール］－［オプション］タブ－［データ］グループ－［データソースの変更］ボタンが該当します。
そして、表示された［ピボットテーブルのデータ ソースの変更］ダイアログ ボックスで［テーブル/範囲］ボックスに範囲を指定し直し、［OK］をクリックしましょう。これでデータ範囲が変更されたので、ピボットテーブルも更新されました。
このように、ピボットテーブルに連携している元データの件数がよく変わる場合、その都度元データ範囲を変更するのは面倒ですよね。
減る分には更新ボタンを押せばOKですが、増える場合は範囲指定から始めなくてはなりません。
そのような場合は、ピボットテーブルの元データを、空白行（列）を含めた最大限のデータ範囲で作成しておくのがおすすめです。
「(空白)」を非表示に設定しておけば、元データの値に変更があっても［更新］を押すだけでいいので、作業効率はぐんとあがります。
最後にピボットテーブルの元データ範囲を変更する方法をまとめたので、きちんとおさえておきましょう。
- ピボットテーブル内にアクティブセルを置き、［ピボットテーブル］ツール バーの［ピボットテーブル ウィザード］をクリックします。もしくは、［データ］メニュー－［ピボットテーブルとピボットグラフレポート］をクリックします。
- 表示された［ピボットテーブル/ピボットグラフ ウィザード］の［戻る］ボタンをクリックします。
- 表示された［ピボットテーブル/ピボットグラフ ウィザード］のダイアログ ボックスの［範囲］を指定し直します。
ピボットテーブルが更新されないときの対処法
Excelでピボットテーブルが更新できない場合、最新のデータになっていないという事態になるので、クライアントや上司に報告する売上などについては大問題になるおそれもあります。そのような事態を防ぐために、ここからはピボットテーブルが更新されたときの対処法についてご紹介します。
原因としては主に2つで
- 更新ボタンを押していない
- 元データの範囲外に入力されている
があげられます。
1.ピボットテーブルを更新する
元データを変更しても、ピボットテーブルは自動では更新されません。そのため、ピボットテーブルを更新する場合は、「更新ボタン」をクリックする必要があります。
ピボットテーブル分析の下にある「更新」を押しましょう。これで解決です。
とはいえ、いちいち更新ボタンを押すのも面倒ですよね。
そこで、ピボットテーブルの更新をする際に便利なのが、「ファイルを開いたときに更新」するという機能です。
ピボットテーブルを右クリックし、ピボットテーブルオプションをクリックします。
青い四角で囲まれている箇所にチェックが入りましたね。
これで、ブックを閉じて開いてみると値が更新されるので、更新漏れを防ぐことができます。
2.ピボットテーブルの元データ範囲を確認する
2つ目にご紹介する原因は、ピボットテーブルの元データが「範囲外」に入力されているということです。
解決策は非常にシンプルで、元データの範囲を変更することです。
それではピボットテーブルを選択して、Excel上にある「ピボットテーブル分析」タブ→「データソースの変更」をクリックしてみましょう。
ピボットテーブルのデータソースの変更が表示されるので、全部の範囲を指定しているかを確認しましょう。
この範囲については自動反映する機能はありません。
ですから、あらかじめ多めに範囲指定しておくとよいでしょう。
上の図のようにセルにデータが入力されている10行目以降もあらかじめ指定しておくと、11行目や12行目に商品や店名、売上が追加されても、データソースから漏れることはありません。
あわせて読みたい｜ピボットテーブルのデータタブについて
前の章では、ピボットテーブルを右クリックして表示されるピボットテーブルオプションのデータタブについてふれました。
ここにはファイルを開くときに、データを更新する以外にも項目があります。デフォルトではチェックが入ったり、「自動」が選択されています。
基本的にはそのままで問題ありませんが、それぞれの意味をご紹介しますね。
ファイルに元のデータを保存する
オンまたはオフを選ぶことで、外部データソースのデータをブックと一緒に保存するか保存しないかの設定が可能になります。なおこの設定は、データのプライバシーの管理には使用しないでください。
詳細を表示可能にする
データソースからの詳細データへの深堀を有効にします。新しいワークシートにデータを表示するには、オンまたはオフにします。基本的にはオンのままで活用することが多いです。
フィールドに保持するアイテム数
フィールドごとに返されるアイテム数とは、Excelのブックで一時的にキャッシュする各フィールドの項目のことです。次のいずれかを選択できるようになっています。
自動：フィールドごとの一意の項目の既定の数。
なし：フィールドごとに一意の項目はありません。
MAX：フィールドごとの一意の項目の最大数で、1,048,576 個まで指定できます。
まとめ
いかがでしたでしょうか。
本記事では、Excelのピボットテーブルをテーマに、ピボットテーブルの作成方法や元の表のデータが変更された際の更新方法を解説しました。
ピボットテーブルは、データの集計や分析時に用いる便利な機能で、専門的な知識や高度な関数スキルがなくても、マウス操作で対応することができます。
事務職などの方で、ピボットテーブルを長く管理・運用していると、ピボットテーブルの元の表にデータが追加されたり、変更されたりすることがありますよね。
そんなとき、元の表のデータ変更がピボットテーブルの反映に常にリンクするよう、本記事の内容を参考にして業務の質をあげていってくださいね。
