非エンジニアにとって、Excelのピボットテーブルは神ツールといえるでしょう。
ただ、元データを更新するたびに、切り貼り・更新・体裁整え・関係者に通知etc...面倒くさいので、Power Automate Desktopを使って自動で更新する方法を考えました。
※ピボットテーブルを知らないエンジニア様はググってください。
更新のフロー概要
今回は2020-10～2021-2までが入っているピボットテーブルに2020-10～2021-3のデータを貼り付けて更新したいと思います・元データを特定のフォルダに保存
・保存したデータを読み取り
・レポート用のファイルを開く
・ピボットテーブル読み込み用のテーブルに元データを貼り付け⇒不要な行を削除
・ピボットテーブル更新
PAD上でのフローはこちら
解説
まずファイルの解説です。 ”売上レポート”というファイルがピボットテーブル作成用のファイルです。 同じ階層にある”売上レポート元データ”というフォルダの中に”元データ”というファイルを格納しています。
今回のデータはネットから適当なものを引っ張ってきて少し加工したものです。
（空白行も参照しているのでピボットに空白がありますが、完成品には無くなります。）
元データはテーブル化しており、ピボットテーブルの参照元も”テーブル1”としています。（※テーブルが分からない人はググってください。）
テーブルを作成する際に今回の例だと行を50000行にして明細を追加した際に絶対に上回らないような広さにしています。後ほど詳しく解説します。
現在、2021-2までの売上が入っているので今回の更新で2021-3のデータを追加したいと思います。
ピボットテーブルの参照範囲をテーブルにする
ピボットテーブルには色々な参照方法があるのですが、エクセル初心者の方がよく行っているのは、Sheet1!$A$1:$C$200みたいなシート名とセル範囲で参照の仕方です。 最初にピボットテーブルを作るときにデータがあるセルを選んだ状態で行うのですが、その際にエクセルが自動でデータが入っているエリアを指定してくれます。 セル範囲をピボットテーブルの参照場所としている場合に、新しい行が追加された時に”データソースの変更”をしなくてはいけません。 一方、ピボットテーブルの参照場所をテーブルにしておけば、行がいくら増えたとしてもその都度テーブル自体が拡張されるので、ピボットテーブルの更新をするだけで数値が変わります。なぜテーブル範囲を50000行までにしているのか？
結論から言うと、PADでテーブルより広いデータを貼り付けるとテーブルが消滅してしまうためです。 なので予め広めのテーブルを用意しておいて、新規データを貼り付けた後に不要行を削除します。 そのあとにピボットテーブルを更新すれば完成です。更新手順まとめ
広いテーブルにデータを貼り付ける ↓ 貼り付けるファイルの最初の空白行からテーブルの最終行までを削除 ↓ ピボットテーブルを更新する★11行の”キーの送信”で行の削除をしています。詳しくは別の記事で書いていますのでよろしければご覧ください。
★12-13行でピボットデーブルがあるセルをアクティブ化→14行でピボットデーブルを更新しています
★更新時に不要の行が削除されているのでピボットテーブルの空白もなくなります。