Excelピボットテーブルの自動更新でセルとは別にデータを保存する

「知っていると得するかもしれないExcelの癖」シリーズ第二十回、テーマは「ピボットテーブルへのデータ保存」になります。
Excel 2016バージョン1903を使用して確認しています。

本サイトではタイトルは簡潔にすることをモットーにしています。
ただし今回のテーマは一言で説明するのは難しいので趣旨をまとめて置きます。

• xlsxファイルを開いた時点でのセルの値をピボットテーブルを更新することで保存して置きます。
• そうすることでピボットテーブルが参照しているセルの値が変更された後に元の値が何であったかを?確認することができます。
• この操作をxlsxファイルを使っている人の手をわずらわせずに行いたいです。

Excelで「更新」操作がある機能

この見出しも表現が伝わり難い事と存じます。
例えば「計算方法の設定」で「手動」に設定すると「再計算」という更新操作が必要になったしますが、今回話題にするのはセルにセットされた値とは別の場所にセットされたデータを「更新」する操作になります。

この観点に立つと、Excelで現在使用できる機能としては下記の2つになる認識です。

• ピボットテーブル
• Power Query

なおPower Queryにつきましては、30年を超えるExcelの歴史の中では比較的最近の機能であり、いままでとは違った形のデータ集計が可能になるのですが、それにつきましては別なテーマで取り上げてみたいと思います。

ピボットテーブルのオプションで「ファイルを開くときにデータを更新する」

ピボットテーブルの範囲内のセルをクリックすると「ピボットテーブル ツール」として「分析」「デザイン」の2つのタブが表示されますが、「分析」タブをクリックしピボットテーブルリボングループの「オプション」→「オプション」をクリックするとオプション設定ウインドウが開きます。

その中の「データ」タブをクリックすると左図のような画面になります。

赤枠でくくった「ファイルを開くときにデータを更新する」はデフォルトではチェックされていないので、ここにチェックを入れます。

またその2つ上の「ファイルに元のデータを保存する」のチェックは外さないようにします。

これにより、該当ファイルを一旦閉じてから開き直すことにより、ピボットテーブルが「更新」されるようになります。

ピボットテーブルのデータを参照する数式

ピボットテーブルのデータはGETPIVOTDATA関数で参照することができます。
ただし引数の数が多いので使うのが面倒臭いように感じますが、次のように設定して置くと簡単に数式を作ることができます。

先ほどご紹介した「ピボットテーブル ツール」の「分析」タブの「オプション」をクリックすると左図のようなプルダウンメニューが開きますが、そこで一番したの「GetPivotDataの生成」にチェックを付けるようにします。

上図のようにチェックが付いた状態であれば、ピボットテーブルの値にセットされたデータに関しては、空いているセルに「=」だけ入力した状態で、参照したいピボットテーブルの値をクリックすると、引数が設定されたGETPIVOTDATA関数が貼り付きます。

これをコピペすれば簡単にピボットテーブルのデータを参照する数式をセットする事ができます。

タイムスタンプのような機能を実装する

ここまでお伝えした機能を組み合わせて見出しのような機能を実装してみたいと思います。
やりたい事は下記になります。

• 1から5までの設定値を保存するテーブルがあります。
  • テーブルの項目は、項番、設定値、更新日時の3項目です。
• 設定値を更新すると、更新日時が自動的にセットされます。
  • ただし、更新日時が確定するのはピボットテーブルが「更新」された時点の日時とします。
  • 設定値が何も入力されていない時は更新日時を「2000/1/1 12:00:00」にセットします。

項番[1]から[3]まではピボットテーブルの「更新」がされて確定しています。
項番[4]はテーブルの設定値に値を入力したので、テーブルの更新日時は入力した時の日時がセットされていて、見た目は確定しているように見えますが、実際はピボットテーブルの「更新」がされていないので、まだ未確定な状態です。
ちなみに数式タブ→「計算方法」リボングループの「再計算実行」または「PF9」を押すと更新日時は変わって行きます。

なお、残念ながらピボットテーブルが含まれるシートを保護するとピボットテーブルの「更新」をすることができません。
そのためピボットテーブルを保護するためには、ピボットテーブルが含まれるシートを別にして非表示にしたうえでブックを保護する必要があります。
この場合、手動でピボットテーブルの「更新」をするにはファイルを開き直すか、またはデータタブ→「接続」リボングループの「すべての更新」をクリックするかどちらかの方法を選ぶしかありません。

このようにすれば、その時点で更新日時は確定されますが、これをタイムスタンプ機能として実用化するのは難しい認識です。

セットされている数式

ピボットテーブルの設定で、「値」の設定のところは、今回の場合は「合計」でも「最大」でも「平均」でも1レコードしかないのでどれにしても結果は同じになります。

テーブルの更新日時の数式はすべて同じで下記になります。

=IF([@設定値]="",DATE(2000,1,1)+TIME(12,0,0),IF([@設定値]<>GETPIVOTDATA("設定値",$E$1,"項番",[@項番]),NOW(),GETPIVOTDATA("更新日時",$E$1,"項番",[@項番])))

なお上記数式に含まれている、テーブルの構造化参照につきましては下記の記事をご参照いただければ幸いです。

Excelでテーブルに数式をセットする際の注意点

「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。 Excel 2016バージョン1902を使用して確認しています。な...

neverendworks.com

2019.03.30

まとめ

「ピボットテーブルが含まれるシートを保護するとピボットテーブルの更新ができない」ことを考えと、ピボットテーブルをこのような裏方の仕組みで使うことは想定されていない事と存じます。

ただ、タイプスタンプ機能は難しいとしても、変更された箇所を条件付き書式で背景色を付けて教えてあげるような機能として使う分には、今回のやり方は実用に耐えるのではないか?と考えます。

以上、最後までご一読いただきありがとうございました。