[VBA] PowerQueryとピボットテーブルを順序を指定して更新する方法

ここではクエリの名前を指定して更新した後、RefreshAllでピボットを一括更新しています。

ピボットの元データに複数のPowerQueryデータを使っている場合は、先に元の表だけ更新したいですよね。クエリが別のクエリを参照している場合もこの方法は有効です。

目次

サンプルコード

元データの順序を指定して更新した後、複数存在するピボットテーブル全て一括更新する。

クエリ名:

  • テーブル1
  • テーブル2
Sub RefreshSample()
  With ThisWorkbook
    .Connections("クエリ - テーブル1").Refresh ’PowerQueryの指定したテーブルだけを更新
    .Connections("クエリ - テーブル2").Refresh 
    .RefreshAll                  ’ピボットテーブルだけを更新
    .Save
  End With
End sub

・「テーブル1」には、クエリ名が入ります。

クエリ名の調べ方
できる方は「マクロの記録」を実行し、テーブル右クリック「更新」で記録されるコードを確認してみましょう。

RefreshAllは便利ですが、更新の順序が想定外のことがありますので、明示的に順序をしておくといいですね。ピボットも順序指定したいことがありますね。この部分はまた、追記しようと思います。

急ぐ方はマクロの自動記録でピボットを更新してコードを確認してみると良いでしょう。

注意点

この方法を使う時に以下の設定をしておくと良いでしょう。

「全て更新」でクエリの更新を除外しておく

.RefreshAllの時点では既にクエリの更新が終わっているので、クエリの除外してピボットだけが更新されるようにしておきます

ピボットテーブルだけ更新されるようにする

各テーブルのプロパティで設定します。

  1. テーブルの中でクリックしてテーブルを選択する
  2. メニューバーの「データ」→ 「クエリと接続」 → 右側に表示されるクエリペインから該当のクエリ名を選択して右クリック
  3. プロパティをクリック
  4. 「使用」タブ → 「すべて更新でこの接続を更新する」のチェックを外しておきます。
    (通常デフォルトではチェックが入ります)

おまけ:テーブルのおすすめ設定

テーブルを扱う際に私がよく使う設定をご紹介します。

クエリの列幅を自動で調整させない

PowerQueryでデータが更新されるたびに列幅が更新されるのが煩わしいので、自動で調整させないようにしています。欲しいのは値だけですので、この方が私はすっきりします。

設定方法

  1. PowerQuery(取得と変換)で取得した表のセルをクリックする。
  2. メニューバーの「データ」からプロパティをクリックする。
  3. 「外部データのプロパティ」から「列の幅を調整する」のチェックをはずす

ピボットテーブルの列幅を自動で調整させない

ピボットテーブルも列幅の自動調整をはずしています。

設定方法

  1. ピボットテーブルを選択する。
  2. 右クリック→「ピボットテーブルオプション」→「レイアウトと書式」タブを選択する。
  3. 「更新時に列幅を自動調整する」のチェックをはずす。

好みのテーブルデザインをデフォルトに設定しておく

PowerQueryで取得した表は自動的にテーブルとなります。その時に設定されるデザインをデフォルト登録することができます。設定しておくとテーブル作成時にそのデザインが適用されます。

そうでないと、あの緑色のシマシマ模様になります。私はすっきりとしたのが好きなので、タイトルだけに色がついたものをデフォルトにしています。ファイルごとの設定となりますが、簡単ですのでよく使う方は設定しておくと良いでしょう。

設定方法

  1. テーブルを選択する。
  2. メニューバーの「デザイン」から規定にしたいデザインの上で右クリックする
  3. 「規定に設定」をクリックする。
なごみのアイコン画像なごみ

おまけの部分お好みです。ご参考として紹介しました。
気になる方はぜひお試しを。

では、また。

目次
閉じる