「外部データソースから」複数のピボットテーブルを作成する際の留意点

データモデルに追加せず、Power Queryだけで複数のピボットテーブルを作成することもできる。但し、留意しなければならないことが、いくつかある。Power Queryだけ、というのはかなり面倒だ。モダンExcelは「一体理解」が不可欠だ。

ピボットテーブルの作成方法は4つ

「挿入」タブ「ピボットテーブル」をクリックすると、4つの方法でピボットテーブルを作成することができる。

図1

今回のモデルでは、データモデルに追加していないので、上図下から二つ目「データモデルから」がグレーアウトになっている。

なお、一般的なエクセルユーザーであれば、一番上「テーブルまたは範囲から」でピボットテーブルを作成することが多いと思う。

「外部データソースから」でピボットテーブルを作成する際の留意点

Power QueryでETLした結果をもとに、複数のピボットテーブルを作成することができる。
但し、留意すべき点が、いくつかある。

接続の選択

Power Queryで作成したデータから、ピボットテーブルを作るには「データのインポート」画面で「ピボットテーブルレポート」を選択し、ピボットグラフを作るのであれば「ピボットグラフ」を選択する。

図2

これで「一つ」のピボットテーブルまたはピボットグラフを作ることができる。

Power Query「だけ」で複数のピボットテーブルを作成するには、「挿入」タブ「ピボットテーブル」の「外部データソースから」を使う(図1)。
ピボットテーブルのほかにピボットグラフを作成するには、「挿入」タブ「ピボットグラフ」をクリック。

図3

「ピボットグラフ」もしくは「ピボットグラフとピボットテーブル」をクリックすると、「ピボットテーブルの作成」画面が現れる。

図4

「外部データソースを使用」直下「接続の選択」をクリック。

図5

上記図5「既存の接続」画面、「接続」「表示」横「このブック内の接続」を選択すると、ブック内の接続されているクエリが一覧表示されるので、ピボットテーブル等の作成に必要なクエリを選択すれば良い。

複製されるクエリと削除の影響

先の図5をよく見て欲しい。
「クエリ-納品管理」「クエリ-納品管理(2)」「クエリ-納品管理(3)」とあるが、実はこれらはいずれも同じクエリで、枝番は複製クエリなのだ。
つまり、Power Query「だけ」で複数のピボットテーブルを作成すると、クエリが増殖することになる。
そして、作成した覚えのない同じ名称で枝番の付いたクエリが「クエリと接続」にあれば「?」となり、消す。

図6

ピボットテーブル等で使われているクエリを消せば、当然、ピボットテーブルやピボットグラフは操作できなくなる。既に作成済みのグラフなどは表示が残るが、操作できない状態になる(これが混乱のもと………)。

今、次のように「納品管理(3)」クエリを使ったピボットテーブルがある。

図7

当該クエリを削除してみる。すると、次のように作成済みのピボットテーブルは残るが、ピボットテーブルではなくなってしまう(という表現が正しいかどうかは不明)ので、当然「クエリのプロパティ」も開けない。

図8

このようにPower Query「だけ」で複数のピボットテーブル等を作成するのは、意図しないクエリの増殖があり、実務では混乱のもとでしかない。

一体理解の必要性

では、どうするか?

Power QueryでETLしたデータを読み込む際、図2下部「このデータをデータモデルに追加する」に☑して、ピボットテーブル等を図1「データモデルから」で作成すればよい。データモデルに追加していれば、複数のピボットテーブル等を作成してもクエリが無限増殖することはない。

要するに、モダンExcelはPower Queryだけではなく「一体理解」が必要なのである。

クエリ名の確認法

ちなみに、ピボットテーブルやピボットグラフの作成元のクエリを確認するには、ピボットテーブルを選択し、「ピボットテーブル分析」タブ「データソースの変更」「接続のプロパティ」をクリック。

図9

現れる「クエリプロパティ」画面の一番上「クエリ名」で確認できる。

図10

基本が大切。

■■■■■

「正しいモダンExcel」の使い方の基本を学ぶには、Power Query(パワークエリ)とPower Pivot for Excel(パワーピボット)の両者を「一体理解」する必要があります。
ぜひ、拙著「モダンExcel入門」(日経BP)で学んでみて欲しいと思います。
サンプルデータで、実際に手を動かしながら、理解を深めることもできます。参考にしてください。

■■■■■

モダンExcel研究所

フォローお待ちしてます!

error: Content is protected !!