Power Queryで作成したテーブルからピボットテーブルを作ったときの「すべて更新」は2回実行しないとピボットテーブルが更新されない件
シート上に表を入力し、テーブル形式にしました。テーブルの名前は「Sample」です。
このテーブルから、ピボットテーブルを作成します。
表内のデータを書き換えます。なお、これ以降、毎回この「セルC2」の値を変化させます。ピボットテーブル側では、"田中"の"B"「3114」が更新されるかどうかに注目してください。
もちろん、元データを変えただけではピボットテーブルが自動的に更新されません。ピボットテーブルを更新するには、[データ]タブの[すべて更新]ボタンをクリックします。
[すべて更新]は、リボンの[クエリと接続]グループに配置されていることから分かるように、ブック内に存在する、すべてのクエリや接続を更新する機能です。この操作でピボットテーブルが更新されたということは、今回作成したピボットテーブルが、元のテーブルと"接続"されていたことを表しています。まぁ、この場合の接続は、外部接続ではなくブック内での内部接続ですけど。つまり、下図のようなイメージです。
さて、話を変えて。今度は、次のようなCSVデータがあったとします。
このCSVを、取得と変換(Power Query)で開きます。
このデータをシート上に読み込みます。
CSVのデータを書き換えます。
もちろん、この場合もテーブルは自動的に更新されません。先と同じように[すべて更新]を実行します。
テーブルが更新されました。"クエリ"と"接続"の違いについては、ちょっとややこしい概念なので、そのうち別のコンテンツで解説します。いずれにしても、下図のような状況だったわけです。
ここまではよろしいですか？本題はここからです。上図のように、取得と変換(Power Query)によってシート上に読み込んだテーブルから、ピボットテーブルを作成します。
つまり、現在は下図のような状態です。
CSVのデータを書き換えます。
ここで[すべて更新]を実行したら、どうなるでしょう？[すべて更新]は、ブック内に存在するすべての"クエリ"や"接続"を更新するのですから、取得と変換(Power Query)で読み込んだテーブルが更新されて、その結果ピボットテーブルも更新されるはずです。やってみましょう。
結果は上図のとおり。お分かりいただけただろうか…取得と変換(Power Query)で読み込んだテーブルは更新されましたが、そのテーブルから作成したピボットテーブルは更新されていません。ちなみにこれ、この状態でもう一度[すべて更新]を実行すると、今度はピボットテーブルも更新されます。
なぜ、こんなことが起きるのか
「ざけんなよ！[すべて更新]してんのに、なんでピボットテーブルが更新されないんだよ！2回実行すると更新って何だよ！」な～んて憤慨している方は、おそらく、この機能に関して「誤解」と「先入観」があるのでしょうね。そのへんを解説します。
まず、[すべて更新]という機能は、ブック内に存在しているすべての"クエリ"と"接続"を更新します。しかし、当たり前ですけど、すべての接続などが"同時"に更新されるのではありません。すべての接続などは"順番"に更新されるんです。もちろん、"順番に更新"とはいえ、それは「1分間隔で」みたく悠長な話ではありません。最近のパソコンは高性能ですから、その"順番に更新"も感覚的には「瞬時」に行われます。とはいえ、決して"同時"ではありません。どんなに高速であっても、必ず更新される順番があります。
そして、その"更新される順番"が問題なんです。現在、このブックには2つの接続があります。
- CSVとテーブル間の接続
- テーブルとピボットテーブル間の接続
データの流れ的には「CSV→テーブル→ピボットテーブル」なのですから、更新される順番も「1.→2.」のような気がします。てゆーか、そうあって欲しいです。人間の感覚としては。しかし、そうした"データの依存関係"というのは複雑です。今回のようにシンプルなケースばかりではありません。機械的に順番を決められないような、そんな複雑な依存関係だって存在するでしょう。であるなら、更新される順番が、必ずしも人間の望む順番にならなかったとしても、それはやむを得ないと思います。そう、カンのいい方でしたらお気づきですね。今回実行した[すべて更新]は、「2.→1.」の順番で更新されているんです。これ、VBAのイベントで確認しました。つまり、下図のような流れだったんです。
いかがでしょう。この流れで[すべて更新]が行われたのなら、今回の件も納得できませんか。
1回の[すべて更新]でピボットテーブルまで一気に更新する方法
最初は私も試してみるまでは「CSV→テーブル→ピボットテーブル」の順で更新されると思ってました。ところが、実際にやってみたら「へ？なんで？」って感じで、あれこれと原因を調べて理由が分かったわけです。で、ついでに「何か方法はないんかい？」と探ってみたら、意外と簡単な解決方法を見つけましたので、それをご紹介します。
まず、CSVを取得と変換(Power Query)で開きますよね。そのデータをシート上に読み込みます。そのとき、[データのインポート]ダイアログボックスの[このデータをデータモデルに追加する]チェックボックスをオンにします。
[このデータをデータモデルに追加する]チェックボックスをオンにして読み込んだテーブルからピボットテーブルを作成すると、一度の[すべて更新]で、テーブルとピボットテーブルの両方が更新されます。いろいろと試してみましたが、ポイントは取得と変換(Power Query)で行う「CSV→テーブル」側であり、「テーブル→ピボットテーブル」では、[このデータをデータモデルに追加する]チェックボックスがオンでもオフでも関係ありませんでした。
「データモデルって何ですか？」という質問は、私のセミナーでもときどき受けるのですが、簡単に言うと、高度なデータ分析をするために必要な仕組みです。少なくともExcelユーザーにとって、これが何なのか知る必要はありません。知ってて欲しいのは「データモデルに追加すると何ができるのか」です。これは、そのうち別のコンテンツとして書きます。
以下はVBAをやってる方へ向けての情報です。「CSV→テーブル」側の接続をデータモデルに追加しておくと、次の順序でイベントが発生します。
- Workbook_ModelChange
- Workbook_ModelChange
- Workbook_SheetChange
- Workbook_SheetTableUpdate
- Workbook_SheetChange
- Workbook_SheetPivotTableUpdate
すべてWorkbookオブジェクトのイベントです。書いていませんが、もちろんWorksheetオブジェクトでも、ChangeイベントやPivotTableUpdateイベントは発生します。そのへんは、各自確認してください。1.と2.が、おそらく「データモデルが変更された」というイベントでしょう。なぜ2回発生しているのかは分かりませんし、理由が分かったところで意味がないので調べていません。「CSV→テーブル」が更新されたことによって、まずセルの値が書き換わります。今回でしたらセルC2です。それが3.です。その結果、4.でテーブル全体が更新されます。ピボットテーブルの元データ(今回はセルC2)が変更されたので、ピボットテーブル内のセル(今回はセルG3)も変更されます。それが5.です。その結果、最終的に6.でピボットテーブルが更新されます。参考までに「CSV→テーブル」の[このデータをデータモデルに追加する]チェックボックスをオフにしておくと、次のイベントが発生します。こちらもWorkbookオブジェクトだけ。
- Workbook_SheetChange
- Workbook_SheetPivotTableUpdate
- Workbook_SheetChange
[このデータをデータモデルに追加する]チェックボックスがオフだと、そもそもTableUpdateイベントが発生しません。このへんの話も、そのうち別コンテンツとして書きます。