VBA処理の前にPower Queryでデータの入り口を整える話

どうも、だらず夫です。

ブログ自体は更新してたんですが、エクセルVBAのカテゴリだけは当分放置してました。仕事ではせっせとExcel VBAでデータ処理のマクロを組んでたんですけどね。

最初はCSVの中身をコード側で全部処理する作り方をしてたんですよね。それはそれで動くんですけど、困るのが「元データを見返したいとき」。いちいちCSVを開き直すのが地味に面倒だし、CSV自体を何度も更新する運用だと、そのたびにコード側の参照もあれこれ気にする羽目になる。

だったら、フォルダにデータを放り込んでおけば勝手に最新のものを拾ってきてくれる設計にした方が楽なんじゃないか、と。そこで最近は、VBAで処理する前段階として「Power Queryでデータの取り込み口をあらかじめ整えておく」のを原則にするようになりました。今日はその手順を、社内向けに作った資料をベースに書き残しておきます。

この手順で解決できること

毎回手作業でファイルを開いて処理するのをやめたい
フォルダごと別の場所に移動しても、パス設定が壊れないようにしたい
CSVでもExcelファイルでも、同じ手順で最新データを自動的に取り込みたい
Power Queryを使うと自動生成される「ヘルパークエリ」でクエリ一覧がごちゃつくのを避けたい

前提知識・用語集

Power Query
Excelに標準搭載されている、複数のファイルやデータを「自動で」取り込み・加工する機能。一度手順（クエリ）を作っておけば、「更新」ボタン一つで最新のデータに置き換えられる。

クエリ
Power Queryで作る「データの取得・加工手順」のこと。料理でいうレシピのようなもの。

M言語
クエリの中身を記述する専用の言語。普段Power Query画面でマウス操作をすると、裏側で自動的にこのM言語のコードが生成されている。今回は、画面操作の代わりにこのコードを直接貼り付けることでショートカットする。

詳細エディター
M言語のコードを直接見たり書き換えたりできる画面。Power Queryエディターの「ホーム」タブから開ける。

ヘルパークエリ
フォルダやファイルを取り込む操作をすると自動的に作られる「裏方」のクエリ（例：「サンプルファイル」「パラメーター1」など）。増えるとクエリ一覧が煩雑になる。今回の手順ではこれを作らない。

相対パス
「ブック自身が置かれている場所」を基準にしたファイルの位置指定方法。フォルダごと移動しても指定し直す必要がない。対義語は「絶対パス」（C:\Users\…のように固定された場所）。

全体の流れ

①フォルダ準備 → ②setupテーブル作成 → ③空クエリにコード貼付 → ④4か所を書き換え → ⑤完了・以後は更新のみ

Step1：フォルダとファイルを準備する

次のような構成でファイルとフォルダを配置してください。

［親フォルダ］（任意の場所でOK）
├── 処理用ファイル.xlsx  ← 本手順を作成・実行するブック
└── ［データ格納フォルダ］ ← 例：売上データ、月次データ など
      ├── データ1.csv
      └── データ2.csv

POINT：「処理用ファイル.xlsx」は、必ず一度「名前を付けて保存」をした状態で作業してください。保存前（タイトルバーが「Book1」などになっている状態）だと、次のStepでブックの場所を自動取得できません。

Step2：setupテーブルの作成

Power Query側から「このブックが今どこに保存されているか」を自動的に参照できるように、目印となる小さな表（テーブル）をブックの中に作っておきます。

ブック内に新規シートを追加し、シート名を「設定」に変更する
「設定」シートのA1セルに、次の数式を入力する。この数式は「このブックが保存されているフォルダのパス（場所）」を自動的に文字列として取り出すもの。ブックの保存場所が変わっても、この数式の結果は自動的に更新される

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

A1セルを選択した状態で「Ctrl + T」を押し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」をクリック（表をExcelの「テーブル」機能に変換する）
A1セルの見出し名を「path」に変更する
上部メニュー「テーブルデザイン」タブにある「テーブル名」を「setup」に変更する

POINT：ここで名前を「setup」「path」ぴったりに合わせておかないと、後で貼り付けるコードから参照できずエラーになります。半角・大文字小文字も含めて正確に入力してください。

Step3：Power Queryのクエリを作成する

「データ」タブ＞「データの取得」＞「その他のデータソースから」＞「空のクエリ」をクリック
Power Queryエディターの画面が開いたら、画面左上の「ホーム」タブの中にある「詳細エディター」をクリック
詳細エディターの中に元から入っているコードをすべて削除し、次のStep4の【テンプレートコード】をそのまま貼り付ける
Step5を参照し、自分の環境に合わせて4か所を書き換える
右下の「完了」をクリックし、データに問題がなければ「閉じて読み込む」をクリック

Step4：テンプレートコードの中身を理解する

以下がStep3で貼り付けるコードの全文です。長く見えますが、6つのブロックに分けて、それぞれ何をしているかを順番に説明します。

let
 // 1. 作成済みの「setup」テーブルの「path」列から1行目の値（フォルダパス）を取得
 CurrentPath = Excel.CurrentWorkbook(){[Name="setup"]}[Content]{0}[path],
 TargetFolder = CurrentPath & "データ格納フォルダ", //★環境に合わせて変更★
 // 2. フォルダ内のファイルを取得して最新【指定件数】に絞り込み
 ソース = Folder.Files(TargetFolder),
 並べ替えられた行 = Table.Sort(ソース,{{"Date modified", Order.Descending}}),
 保存された先頭行 = Table.FirstN(並べ替えられた行, 1), //★環境に合わせて変更★
 フィルター選択された非表示のFile1 = Table.SelectRows(保存された先頭行, each [Attributes]?[Hidden]? <> true),
 // 3. 拡張子（CSVかExcelか）を自動判別してインラインで展開
 データの展開処理 = Table.AddColumn(フィルター選択された非表示のFile1, "データの展開", each 
 let
  Extension = Text.Lower([Extension]),
  // 3. CSVの場合の読み込み
  CsvData = if Extension = ".csv" then Csv.Document([Content], [Delimiter=",", Columns=null, Encoding=932, QuoteStyle=QuoteStyle.None]) else null,
  CsvPromoted = if CsvData <> null then Table.PromoteHeaders(CsvData, [PromoteAllScalarTypes=true]) else null,
  // 4. Excelの場合：ブック内の全シートを結合して読み込み
  ExcelSheets = if Extension <> ".csv" then Table.SelectRows(Excel.Workbook([Content], true), each [Kind] = "Sheet") else null,
  ExcelCombined = if ExcelSheets <> null then Table.Combine(ExcelSheets[Data]) else null,
  Result = if Extension = ".csv" then CsvPromoted else ExcelCombined
 in
  Result
 ),
 // 5. 不要な列を削除し、展開
 削除された他の列1 = Table.SelectColumns(データの展開処理, {"データの展開"}),
 列名のリスト = Table.ColumnNames(削除された他の列1{0}[データの展開]),
 展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "データの展開", 列名のリスト),
 // 6. 列名を固定せず、展開されたすべての列に対して動的に型を「テキスト型」に変換
 現在のすべての列名 = Table.ColumnNames(展開されたテーブル列1),
 動的な型指定 = List.Transform(現在のすべての列名, each {_, type text}),
 動的な型の変換 = Table.TransformColumnTypes(展開されたテーブル列1, 動的な型指定)
in
 動的な型の変換

①パスを取得する部分
Step2で作った「setup」という名前のテーブルを探し出し、その中のpath列（1行目）の値を取り出しています。数式で自動計算しておいた「このブックが保存されているフォルダの場所」を、ここで呼び出しているだけです。

②対象ファイルを絞り込む部分
対象フォルダの中身をファイル一覧として取得し、更新日時が新しい順に並べ替えたうえで、指定した件数だけ取り出します。隠しファイルは対象から除外されます。

③CSVの場合：読み込む部分
ファイルの拡張子が「.csv」であれば、指定した文字コード・区切り文字でファイルを読み込み、1行目を見出し行として昇格します。

④Excelの場合：全シートを結合する部分
ファイルの拡張子が「.csv」以外（＝Excelファイル）であれば、ブックの中身を丸ごと取得し、「Kind」が「Sheet」のもの（＝実際のワークシート。名前付き範囲やテーブル定義などの余計な項目を除く）だけに絞り込みます。そのうえで、絞り込んだすべてのシートのデータをTable.Combineで1つの表として縦に結合します。つまり、「同じ表形式のデータが複数シートに分かれているExcelファイル」でも、シート数に関係なくすべて自動で1つの表にまとめられます。

POINT：この結合処理は「各シートとも1行目が見出し行である」ことを前提にしています（Excel.Workbook([Content], true)の第2引数trueが、各シートの1行目を自動的に見出し行として扱う指定です）。実際のファイルを確認して、もしタイトル行が挟まっているなど見出し行の位置がずれているシートがあれば、事前にシート側を整えるか、この部分の書き方を調整してください。

⑤表を実際の列として展開する部分
③④で取り出したデータは、まだ「表の中に表が入っている」ような状態です。この部分で、最初のファイルの列名を自動的に読み取り、それを使って実際の列（日付、金額など）として展開します。

⑥すべての列をテキスト型に統一する部分
列名を一つずつ指定するのではなく、展開されたすべての列に対してまとめて「テキスト型」に変換しています。これにより、元データの列が増減したり列名が変わったりしても、クエリの更新がエラーで止まりにくくなります。

POINT：数値や日付として計算に使いたい場合は、この後にもう1ステップ「型の変換」を追加してください。今回のテンプレートはあくまで『まずエラーなく取り込む』ことを優先した設計です。

Step5：4か所を書き換える

テンプレートのうち、以下の4か所だけを自分の環境に合わせて書き換えれば動作します。

該当箇所	テンプレート内の記述	設定内容・書き換え例
①参照先フォルダ名（3行目）	“データ格納フォルダ”	【必須】親フォルダ内にある対象フォルダ名に変更。例：”売上データ”や”月次データ”
②取得ファイル件数（8行目）	1	取得したいファイルの件数を指定。最新1件のみ→1／過去3ヶ月分など複数結合→3
③文字コード設定（15行目）	Encoding=932	標準（Shift-JIS）→932／文字化けする場合（UTF-8）→65001
④区切り文字（15行目）	Delimiter=”,”	カンマ区切り（CSV）→”,”／タブ区切り（TSV）→”#(tab)”

うまくいかないときは

症状・エラー	考えられる原因	対処法
「フィールドが見つかりません：’setup’」	setupテーブルの名前、またはpath列の見出し名が指定と違う	「設定」シートのテーブル名・見出し名を確認し、半角・大文字小文字まで正確に合わせる
「アクセスが拒否されました」(“Access to the resource is forbidden”)	対象フォルダやファイルへのアクセス権がない	共有フォルダのアクセス権設定を確認する
データが文字化けする	CSVの文字コードがShift-JIS（932）以外（UTF-8など）	テンプレートのEncoding=932をEncoding=65001に書き換える
「パスが見つかりません」	setupの数式を作った時点でブックが未保存だった	ブックを一度保存し直し、「設定」シートのA1セルを再計算（F9）する
取り込んだデータの列がずれる／足りない	フォルダ内のファイルによって列の並びや列数が異なる	対象フォルダ内のファイル形式を統一するか、展開後に列の調整ステップを追加する
Excelファイルの列がシートによってバラバラになる／余分な列が増える	各シートの見出し行の位置や列名が統一されていない（例：一部のシートだけタイトル行が挟まっている）	元のExcelファイル側でシート構成（見出し行の位置・列名）を揃える。Step4の④のポイントも参照

このやり方のメリット

フォルダ移動に強い（相対パス）：親フォルダごとデスクトップ・共有フォルダ・別端末へ移動しても、パス切れエラーが発生しません。
クエリ一覧が汚れない（1クエリ完結）：自動生成される「ヘルパークエリ」や「サンプルファイル」が作成されず、メンテナンスが非常に容易です。
列構成の変化によるエラーを防ぐ：元データの列増減や名前変更があっても、すべての列を動的に「テキスト型」として展開するため、クエリの更新処理が途中で停止しません。
Excelの複数シートにも対応：同じ表形式のデータが1つのExcelファイルの中で複数シートに分かれていても、ブック内の全シートを自動的に結合して1つの表として取り込めます。

今のところVBA側の処理はこの記事の範囲外ですが、この「入り口整備」を挟むようになってから後段のマクロがだいぶ安定するようになったので、そのうちVBA側の話も書こうと思います。