久しぶりの更新です。
今回は複数のクエリを好きな順番で更新する方法についてお届けしたいと思います。
※方法だけ読みたい方は目次より「クエリ更新ボタンの設置」からどうぞ。
Contents
この記事で解決したいこと
今回のケースについて
まずは、クエリを順番に実行しないといけないケースについて説明しておきましょう。
こんな表があったとします。
これをまずパワークエリで取得・加工し、任意のセルに書き出します。
そして書き出したテーブルに対して累計列などエクセルの関数を使って列を追加したとします。
さらにこれをパワークエリで取得し、加工して書き出したとします。
この流れをまとめるとこんな感じです。
- テーブル1からクエリ「テーブル１」で「テーブル1_2」を作成。
- テーブル1_2にSUM関数で累計列を追加。
- テーブル1_2からクエリ「テーブル1_2」で「テーブル1_3｣を作成。
データを更新して起きる問題
このようなステップをたどるとき、データをするとどうなるのでしょうか。
上の図のように、4行目のみ変更したうえで、データタブ「すべて更新」を押してみましょう。
するとどうでしょう。
テーブル１からテーブル1_2には更新されましたが、テーブル1_3には更新されていません。
クエリ1とクエリ1_2が同時に実行され、更新されてない状態のテーブル1_2を取得するためこのような問題が発生します。
めんどくさい解決策
クエリ１とクエリ1_2が同時に実行されるため今回のような問題が発生します。
であれば、順番に実行すればいいのです。
画面右のクエリフィールドより、クエリを実行する順番で更新ボタンを押します。
これでテーブルを確認すると・・・
無事更新できました。
が・・・！
いちいちクエリフィールドを開いて順番にクリックとかめんどくさくないですか？
できればボタンひとつで更新できないものでしょうか。
これ、マクロを使えば、それも比較的容易に作ることができます。
ではその方法を見ていきましょう。
クエリ更新ボタンの設置
マクロの登録
コードを実際に書いてもいいのですが、ここはノンコードプログラミングならぬノンコードマクロでやってみましょう。
まず開発タブ「マクロの記録」を押します。
そして、クエリ名・ショートカットキー、保存先、説明を必要に応じて入力・変更し、OKを押します。
これでここからの作業がマクロとして記録されます。
クエリフィールドより、クエリを順番に更新します。
マクロとして記録するのはこの作業のみなので、「記録終了」を押します。
これでマクロの登録は完了です。
クエリ実行ボタンの設置
開発タブ「挿入」より、フォームコントロールのボタンを選択します。
図形を挿入する際のカーソルになるので、設置する場所でクリックします。
するとマクロの登録画面が開きますので、ボタンに登録するクエリ（今回は「クエリ実行」）を選択してOKします。
ボタンが設置されますので、必要に応じてボタン名や位置を修正します。
これでボタンの設置完了です。
バックグラウンド更新設定の変更
あとはバックグラウンドでの更新設定を変えておきましょう。
画面右のクエリ一覧から、クエリを右クリックしてプロパティを開きます。
プロパティを開いたら、コントロールの更新の「バックグラウンドで更新する」のチェックを外してOKをクリックします。
これでクエリ１、クエリ1_2の両方の設定を変えておきます。
この設定を変えておかないと、クエリ１の読込に時間がかかった場合、クエリ1がバックグラウンドで処理を続行しながらクエリ1_2が実行されてしまうため、テーブル1_2の更新が終わる前にテーブル1_3の更新が始まってしまいます。
結果、クエリを「すべて更新」した時と同じ問題が起きてしまいます。
ちなみに、バックグラウンドでの更新を止めたとしても、「すべて更新」で更新するとすべてのクエリが同時に実行されるためテーブルの更新が正しく処理されません。
クエリ実行
では、更新されてないこの状態で実行してみましょう。
すると・・・
ご覧の通り、テーブル1_3まで更新できました！
これでボタンひとつでクエリを順番に実行することができたわけです。
余談、注意点もろもろ
これ、いくつか気を付けておくことがあります。
保存方法
このエクセルファイルはマクロを含んでますので、それに合わせた形式で保存しないといけません。
すなわち、通常「.xlsx」形式で保存されますが、マクロ有効ブック「.xlsm」形式で保存しないといけません。
開発タブが無い場合
開発タブが無い場合、開発タブを追加する必要があります。
ファイルタブよりオプションを選択し、
「リボンのユーザー設定」より「開発」タブを追加してOKします。
この時コマンドの選択を「メインタブ」にして開発タブを選択してください。
コードの確認
一応コードも確認しておきましょう。
開発タブ「マクロ」より、「クエリ実行」を選択して「編集」をクリックします。
すると、今回登録したマクロのコードが表示されます。（これをプロシージャ、と言うそうです）
ざっくりした見方ですが、ピリオド以前を主語、ピリオド以降を述語と考えれて問題ありません。
- Active~･･テーブル1”）：クエリ「テーブル1」を、
- Refresh：更新する。
これを訳すとこんな感じです。
注意点としては、このマクロはクエリ名（固有名称）を参照していることから、クエリ名を変更した場合、ここに記述されたクエリ名も変更する必要があります。
ですのでコードを直接編集したくない場合はクエリ名を安易に変更しないことをお勧めします。
一括更新するマクロ
マクロでクエリを一括更新する方法だってあります。
コードを見ておきましょう。
対象を指定して（ActiveWorkbook.)RefreshAll、これだけです。
一応、紹介まで。
最後に
いかがだったでしょうか。
クエリを順番に実行したいケースはあまりないかもしれませんが、マクロを使うとそれも単純化できるということで、知っておいて損はない方法かなと思います。
最後までお読みいただきありがとうございました。
