Excel効率化の第一選択――Power Query・スピル世代数式・VBAの役割分担

図解1｜「VBAでできる」の次に、役割を見る

Excel効率化の第一選択を、仕事の役割から考えます。取得・整形とシート上の計算には、Power Queryとスピル世代数式を先に検討できます。

解説1｜毎月のCSV処理を、三つの役割に分ける

ストーリー

淡水社の月次作業を題材にした物語です。コイ丸先輩が運用してきたマクロは、CSVの取込、列の整理、マスターとの照合、担当者別の一覧、提出用ファイルの保存までを引き受けています。シャケモンは改修の相談を受け、まず処理の中身を並べてみます。先輩は「提出先ごとの書式まであるぞ」と、コードだけでは見えない運用条件を持ち寄ります。

同じマクロに入っていても、仕事の役割は異なります。外部データを読み、使える形に整える部分。利用者の条件に応じて計算・抽出・表示する部分。結果をファイルにし、Excelや外部アプリケーションを動かす部分です。役割ごとに実装先を考えると、取込・整形にはPower Query、シート上の計算・表示にはスピル世代数式、出力や操作にはVBAという基本形が見えてきます。

「VBAでできる」という見通しに、保守のしやすさ、使える機能、実行環境、引継ぎ条件を加えて選びます。ここでいう第一選択は、デスクトップExcelで設計を考える際の提案です。三つの技術を毎回そろえる必要はなく、Excelの基本機能だけで済む作業もあります。

「スピル世代」は、動的配列とスピルを使える世代の数式をまとめて捉えるための呼び方です。公式名称や定着済みの総称ではありません。FILTERなどの新しい関数に加え、IFなど既存の関数を配列として使う数式も含みます。各関数が必ず複数セルへスピルする、という意味ではありません。

図解2｜毎月の取込・整形はPower Queryへ

同じ取込・整形手順をクエリとして残せば、翌月も更新によって実行できます。

解説2｜手作業の順番を、再利用できる変換へ

Power Queryは、データを取得し、必要な形に変換する仕組みです。CSVやExcelブックなどを読み込み、不要列の削除、型変換、行の抽出、表の結合、集計などを組み合わせます。Excelでは、その結果をテーブルとしてシートへロードできます。変換は取得したデータに対して行われ、元のCSVを書き換える処理ではありません。Microsoft公式のPower Query概要でも、取得・変換と更新による再実行が説明されています。

淡水社の例なら、毎月CSVを開いて列を消し、商品コードを手がかりにマスターと結合し、月ごとにまとめる処理が候補になります。コイ丸先輩の作業一覧は、何を残し、どういう状態へ整える必要があるかを確認する材料です。シャケモンは、その条件を変換手順に落とし込みます。

画面上で作った手順はM言語の式として保持されます。まず画面操作で処理を作り、必要になった部分をMで調整する進め方もできます。入力元にExcelアドインやエクスポート機能があるなら、その取得経路も含めて検討します。使えるコネクターや認証方式は、利用する製品・環境によって異なります。

更新できる仕組みになっても、入力条件の確認は残ります。列名やデータ型、ファイル構成が変われば、エラーや意図しない結果が生じます。フォルダーの全ファイルを取り込むなら、対象月やファイルの選定条件も必要です。旧処理と比較する際は、件数、金額合計、照合できなかったコードなど、業務上意味のある結果を確認します。

図解3｜更新で作る表、入力で変わる表示

Power Queryの更新と数式の再計算は、別の処理です。数式は、その時点でシートにある表を参照します。

解説3｜どのタイミングで結果を変えたいか

Power Queryは、更新を実行した時点で元データを取得し、変換結果を作り直します。一方、セル上の数式は、参照する値や条件に応じて計算結果を返します。自動計算の設定なら、担当者や対象月のセルを変えると、それを参照する数式の表示も変わります。手動計算の設定では、必要な再計算操作を行います。

この違いは、処理の置き場所を決める手がかりになります。複数ファイルの統合や型の統一など、データ全体の準備はPower Queryへ。利用者が条件を切り替える一覧や、シート上の計算は数式へ、という分担が考えられます。たとえばFILTERで条件に合う行を取り出し、SORTやUNIQUEで並べ替えや重複除去を行えます。複雑になった数式は、LETで途中結果に名前を付け、LAMBDAで処理を関数としてまとめる選択肢もあります。

コイ丸先輩が対象月を変えたとき、すでに読み込んだ表に各月のデータがそろっていれば、数式で表示を切り替えられます。しかし、CSVそのものを追加・差し替えたなら、Power Query側の更新が必要です。数式の再計算だけでは、取得元の新しいデータは読み込まれません。

逆に、条件をクエリのパラメーターとして渡し、取得段階から対象を絞る設計もあります。その場合は、条件変更後にクエリを更新する運用になります。データ量、取得時間、表示の応答性を見て選びます。Power Queryと数式には処理できる内容が重なるため、「何ができるか」に加えて「いつ変わる必要があるか」を考えると整理できます。

図解4｜テーブル・名前・#でつなぐ

処理の接続点を、名前付きの表や範囲として明確にします。スピル数式はExcelテーブルの外へ置きます。

解説4｜受け渡す表の形を決める

Excelテーブルは、見出し付きのデータを扱い、行や列に名前でアクセスするための接続点になります。Power Queryの結果を「売上データ」というテーブルへロードすれば、下流の数式は構造化参照によって、その表や列を参照できます。行数が変わるたびに固定のセル範囲を書き換える負担を減らせます。

スピル数式の出力先は、そのテーブルとは別に確保します。Excelテーブル内ではスピル数式がサポートされないため、数式はテーブル外のセルに置きます。また、結果が広がる場所に値や別の表があると、#SPILL!エラーの原因になります。Microsoftのスピル動作の説明も、テーブル外への配置と出力範囲の確保を案内しています。

図のA2#は、A2を起点として実際にスピルしている範囲全体への参照です。A2は数式を入力した起点セルで、#を付けると結果の行数・列数の変化に追随できます。さらに、その参照へ「提出一覧」のような意味のある名前を付ければ、下流の数式や出力処理から、何を受け取るのかを理解しやすくできます。名前を付けるだけで、値の種類や列順まで保証されるわけではありません。

先輩の旧マクロにある貼り付け処理も、ここで点検できます。単に中間結果を渡しているなら、参照に置き換えられるかもしれません。一方、月末の確定値を残すためのコピーには保存の役割があります。参照へ変えると翌月の更新で値も変わるため、その目的を確認してから判断します。表名、列名、列順、更新時点を決めることが、処理を分ける土台になります。

図解5｜更新は一操作、処理順は明確に

更新を始めたことと、更新が終わったことを区別します。後続処理は完了を確認してから進めます。

解説5｜ボタンを付けるなら、完了条件も持たせる

担当者がExcelを開いて更新し、結果を確認できれば、業務上十分な場合があります。繰り返していた取込・整形がまとまれば、手動の更新操作を一つ残す設計でも効果があります。無人実行を目指すかどうかは、実行頻度、担当者の作業、失敗時の対応から決められます。

「更新から保存までをボタン一つにしたい」という要件があれば、VBAで実行順序をまとめる方法があります。この役割に絞ったVBAを、本記事では「薄いVBA」と呼びます。Power Queryに任せた取込・変換をそのまま使い、必要なタイミングで再計算や出力をつなぎます。

ただし、更新命令の次に保存命令を書くだけでは不十分な場合があります。Workbook.RefreshAllは、BackgroundQueryがTrueの対象を背景で更新します。呼出しから戻った時点で、必要なデータの更新がすべて終わったとは限りません。MicrosoftのRefreshAll仕様に、この背景更新の条件が示されています。

実際の実装では、対象の接続・クエリに合った方法で完了とエラーを確かめます。そのあと、必要な数式を再計算し、結果を確認して出力します。固定の秒数だけ待つ方法では、データ量や通信状態の変化に対応できません。待機を打ち切る条件、失敗時の停止、利用者への表示も、運用に合わせて設計します。

先輩の「古い表のまま出力できない」という指摘は、この順序の根拠です。VBAの行数を少なくすることだけを目標にせず、更新の成否と出力可否を判断できる構成にします。本図は処理順の設計図であり、すべての接続方式に共通する実装コードではありません。

図解6｜外へ出す、Excelを動かす場面のVBA

シート上で結果ができたあとにも、ファイル作成、保存、書式、操作への応答などの仕事が残ります。

解説6｜具体的な出力条件が、VBAを選ぶ理由になる

数式が返すのは、基本的にセル上の値です。Power Queryの中心は、取得したデータを変換し、利用先へロードすることです。そこから先の「担当者ごとに別ブックを作る」「値と書式を転記する」「指定名のCSVを所定フォルダーへ保存する」といった要件には、別の操作が必要になります。デスクトップExcelでは、VBAが有力な選択肢です。

淡水社の旧マクロには、提出先ごとのファイル名や書式の違いがあります。シャケモンは、取込方法を変えても、その条件は残ると気付きます。整えた結果を受け取り、提出用ファイルを作る役割をVBAへ集めれば、データ準備と出力の責任を分けて説明できます。

VBAは、セル変更やブックを開く操作への反応、入力支援、UserForm、書式・図形・印刷の制御にも使えます。Officeや外部アプリケーションとの連携は、対応する環境や権限を確かめて選びます。Web中心の運用なら、Office ScriptsやPower Automate、外部ツールも比較対象になります。

API連携は、取得か送信かだけで機械的に分けられません。Power QueryのWeb.Contentsにも、Contentを指定してPOSTする機能があります。ただし、標準のWeb.ContentsではPOST要求は匿名認証の場合に限られます。MicrosoftのWeb.Contents仕様を踏まえ、認証方式や利用環境を確認する必要があります。

外部システムの更新や処理起動では、実行のタイミング、失敗時の再試行、重複実行への対処が選定理由になります。VBAを使う場合も、設定、エラー処理、ログなどの設計が必要です。「出力・制御を担当する」と役割を絞ることと、実装が簡単になることは別に考えます。

図解7｜新規は役割から、既存は段階的に

新規の選定と既存資産の見直しでは、出発点が違います。既存の業務条件を確認し、小さな範囲で結果を比べます。

解説7｜第一選択を変え、必要な技術を学ぶ

新規開発では、まずExcelの基本機能やテーブルで済む範囲を確かめ、取得・整形にはPower Query、シート上の計算・表示にはスピル世代数式を検討します。手動更新で運用できるかを考え、必要な出力、イベント、外部連携、全体制御をVBAなどへ割り振ります。すべてを同じ言語で実装する前に、処理の境界を決める進め方です。

既存マクロでは、安定稼働や引継ぎの条件も判断材料になります。取得、変換、表示、出力、イベント、UI、外部連携に分け、変更による効果と移行負担を比べます。先輩とシャケモンは、今月の締めを現行マクロで守り、次回に向けてCSV取込の一部を試すことにしました。比較用データをそろえ、件数や合計、例外処理が旧結果と一致するかを照合します。

AIには、M言語や数式の作成・修正を補助させることができます。依頼するときは、入力の列と型、必要な変換、期待する出力、例外条件を具体的にします。生成された式を採用する判断には、実際のデータでの確認が必要です。使い慣れたVBAだけに候補を絞らずに済む一方、技術選定や結果の正しさまで自動的に保証されるわけではありません。

学習も、今の仕事に必要な役割から始められます。表と参照を土台に、条件別の表示なら配列・FILTERなどの数式へ、反復する取込ならPower Queryへ進みます。LET・LAMBDAやMは処理を整理・拡張したいときに、VBAは出力・操作・制御の要件に応じて深めます。全員が同じ順番で全機能を覚える必要はありません。

まとめ

Excel効率化では、Power Query＋スピル世代数式を第一選択として検討し、必要な出力・操作・制御にVBAを組み合わせる。データ量、使える機能、権限、保守と引継ぎの条件を含めて選ぶ。この役割分担が、本記事の提案です。

出典メモ

仕様確認：Microsoft公式資料（本文のリンク、2026-10-15確認）。淡水社の月次処理は説明用の物語であり、実装や効果測定の実績を示すものではありません。

利用について

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。