外部データソースからのピボットテーブルの作成

ピボットテーブルを作成して分析する時は、同じブック内にソースデータのシートがあることが一般的ですが、Excelでは保存できない大量のデータを扱ったり、データとピボットテーブルを別のExcelファイルにしておいたほうが便利な場合もあるかと思います。

Excelには外部にあるソースデータを利用してピボットテーブルを作成できる機能が用意されています。外部のデータソースとしては、Excelファイルのシートのほか、MS Accessなどのデータベースソフトのファイルや、CSVファイルなどにも対応できますが、ちょっと高度な使い方になります。

ここでは、比較的わかりやすい、外部のExcelファイル（起動しているExcelファイルではない) にあるソースデータを参照して、ピボットテーブルを作成する例で解説します。

外部データソースとの接続

ピボットテーブルを作成したい場所のセルを選択しておいて、「挿入」⇒「ピボットテーブル」⇒「外部データソースから」を選びます。

「外部ソースからのピボットテーブル」ダイアログボックスで、「接続の選択」ボタンをクリックします。

最初は、「既存の接続」ダイアログボックスの中に、対象のデータソースがありませんので、「参照」ボタンを押して、「データファイルの選択」ダイアログボックスを出し外部のデータソースを探します。

該当のファイルを見つけたら「開く」ボタンを押すと、「テーブルの選択」ダイアログボックスが出ます。参照したいソースデータを保存しているシートを選び、「OK」ボタンを押します。

接続名に外部のExcelファイル名が表示されたことを確認し、ピボットテーブルを既存のワークシートか新規のワークシートのいずに作成するかを指定して、「OK」ボタンを押すと、空のピボットテーブルが挿入され、ピボットテーブルの作成（「ピボットテーブルのフィールド」）画面になります。

以降は、ソースデータが同じブック内にある場合と同じ操作で、ピボットテーブルを作成できます。

ピボットテーブルが作成できたら、ファイル名を付けて保存します。

外部データソースに接続して作成したピボットテーブルを含むExcelファイルを開いた時には、「外部データ接続が無効になっています」というセキュリティ警告が出ることがあります。これは、データ接続がマルウェアやその他のセキュリティ上の危険性を含んでいる恐れがあるという警告です。

接続先のファイルが信頼できる場合には、「コンテンツの有効化」をクリックします。これによりこの警告は消えます。

ソースデータに変更があった場合には、「更新」しないとピボットテーブルにその変更が反映されないことは、外部のデータソースの場合でも同じです。

【ファイルを開くときに自動更新】

ただ、外部データソースの場合には、変更の有無がわからないこともありますので、ピボットテーブルのExcelファイルを開いたら、必ず「更新」をすることをお勧めします。

【便利知識】

Excelにはファイルを開く度に、自動的に「更新」させる設定方法が用意されています。

具体的には、「ピボットテーブル分析」タブの「データソースの変更」のメニューから「接続のプロパティ」を選び、「接続のプロパティ」ダイアログボックスで「ファイルを開くときにデータを更新する」にチェックを入れて、「OK」ボタンを押します。

【定期的に自動更新】

また、頻繁にデータが更新される外部データソースへの接続に対応するために、一定間隔ごとに自動的にピボットテーブルを更新させる設定もできます。

具体的には、「接続のプロパティ」ダイアログボックス内の「定期的に更新する」にチェックを入れて、時間間隔を設定した上で「OK」ボタンを押します。