Power Queryとテーブルで定型書類作成の効率化【VBA不要】
概要
請求書、
このような作業はVBAなしで、
本記事では、
- どのような解決策があるのか。
- どうやってエクセルで実装するのか。
について説明します。
エクセルのバージョン
本記事は、
環境によっては、
コピペを減らすためのアプローチ
定型書類を作成するときのコピペ作業を減らすためには、
必要となるファイルは2つです。
- まとめ表： 各書類の情報をまとめた表
- 書類テンプレート： まとめ表からのデータを埋め込んで使える定形書類フォーマット
書類を作成するときには、
エクセルでの実装方法
エクセルで上記を実装する方法を説明します。
使用するエクセル機能
- テーブル： データの格納、
受け渡しに使用。 - パワークエリ： 外部ファイルの読み込み、
加工（必要行のみの抽出） に使用。 - テンプレート： 形式を維持して新規ファイルの作成ができるように使用。
ファイル・テーブル構成
各ファイルとその中に作成するテーブルは以下のようになります
- ファイル1: まとめ表.xlsx
- T_まとめ表: 書類データをまとめて格納するテーブル
- ファイル2: 書類テンプレート.xltx
- P_書類パラメータ: 書類のパラメータを記入するテーブル
- T_まとめ表: まとめ表.xlsxからインポートしたテーブル（必要データのみ抽出したもの）
書類テンプレート.xltxから、
テーブル名のプレフィックス
テーブル名に設定しているT_やP_などのプレフィックス（接頭語）
以下の意味でつけています。
- T_ : トランザクションテーブル（時系列ごとのイベントを格納）
- P_ : パラメータテーブル（パラメータを格納）
※ プレフィックスは必須ではありません
フォルダ構成
定型書類_作成/
├─ まとめ表.xlsx（ファイル1）
├─ テンプレート/
│ └─ 書類テンプレート.xltx（ファイル2）
└─ 作成書類/まとめ表.xlsx（ファイル1）
まとめ表.xlsx（ファイル1）
作成後のファイル名とフォルダ位置の変更は慎重に
まとめ表.xlsxのパスが変わると、
ファイルを作成後は、
（再設定すれば可能）
書類テンプレート.xltxから作成される書類ファイルは、
まとめ表.xlsxより1階層深いフォルダ（サブフォルダ）
フォルダ名に関わらず機能します。
まとめ表.xlsxと書類テンプレート.xltx（から作成される書類ファイル）
位置関係さえ維持していれば、
定型書類_作成フォルダ（ルートフォルダ）
実装手順
2つのファイル（まとめ表、
名称は任意
ファイル名やテーブル名、
好みの名称に置き換えて進めてもOKです。
フォルダの準備
作成していくファイルを配置するフォルダ（定型書類_作成）
フォルダ構成の通りに、
- テンプレートフォルダ
- 作成書類フォルダ
用意したフォルダの中にファイルを作成していきます。
ファイル1. まとめ表の作成
書類データをまとめたテーブルを1つだけ作成します。
テーブル名は、
テーブルの作成方法
- 作成するセルを選択
- 挿入タブ > テーブルボタン
（Ctrl + t）
ファイルは通常のエクセルファイル形式（.xlsx）
ファイル2. 書類テンプレートの作成
書類テンプレートでは、
- 出力エリア : 書類として出力する領域。
印刷やPDF化する場合は、 このエリアのみが出力される。 - 作業エリア : 書類上には直接出力しない内容を記載する領域。
データやパラメータなどを配置する。
2つのエリアは、
手順0. テンプレートファイルとして保存
エクセルでファイルを新規作成したら、
手順3でCELL関数を使ってファイルパスを取得しますが、
手順2以降を行い、
「このブックには外部データが含まれています。
と聞かれた場合は
手順1. 出力エリアを作成する
書類として出力される領域を作成し、
- 目的の書類フォーマットを作成する。
- ※ シート左上に詰めて作成し、
下に伸ばしていく。
- ※ シート左上に詰めて作成し、
- 印刷範囲を設定する。
- 書類のフォーマットの列を選択 > ページレイアウトタブ > 印刷範囲プルダウン > 印刷範囲の設定
※ 改ページプレビュー（表示タブより）を使うと、 設定範囲がわかりやすい - 書類のフォーマットの列を選択 > ページレイアウトタブ > 拡大縮小印刷欄の横幅：で1ページを選択
- ページレイアウトタブ > 余白プルダウン > ユーザー設定の余白... > ページ中央：水平にチェック > OKボタン
- 書類のフォーマットの列を選択 > ページレイアウトタブ > 印刷範囲プルダウン > 印刷範囲の設定
- 出力エリアの境界線を視覚化 & 固定化する（任意）
。 - 印刷範囲（出力エリア）
右端の1つ右の列を選択 - 表示タブ > ウィンドウ枠の固定
- 印刷範囲（出力エリア）
手順2. 作業エリアにまとめ表データをインポート
パワークエリを使って、
- まとめ表.xlsx内のT_まとめ表を読み込む。
- データタブ > データの取得 > ファイルから > Excelブックからボタン
- まとめ表.xlsxを選択
- ナビゲーターが開くので、
T_まとめ表を選択 > データの変換ボタンを押し、 Power Query エディター起動
- 書類番号の値（仮設定）
でフィルタリングする。 - Power Query エディター上で、
書類番号見出しのプルダウン展開 - すべて選択を未選択状態に > どれか1つ（すべて選択以外）
を選択状態に > OKボタン
- Power Query エディター上で、
- 出力不要な列書類番号を削除する。
- 書類番号列を選択
- ホームタブ > 列の削除ボタン
- クエリを保存し、
テーブルを配置する。 - ホームタブ > 閉じて読み込むプルダウン > 閉じて次に読み込む...ボタン
- データのインポートウィンドウで以下の状態に > OKボタン
- テーブルを選択
- 既存のワークシートを選択、
作業エリア中のテーブルを配置するセルを選択。 - このデータをデータモデルに追加するを未選択
「セル連動フィルタ」
手順2～3のように、
セルの値をパワークエリ内で使用し、
該当データを抽出する手法を
「セル連動フィルタ」
手順3. インポートしたまとめ表データを加工
手順2で設定した「データをインポートする処理（クエリ）
- セルに記入した値で、
フィルタリングできるように変更 - データのインポート先のパスを、
絶対パスから相対パスに変更
必要なパラメータをシート上に用意する必要があるので、
- P_書類パラメータテーブルを作成する。
- 以下の列と要素を持つ1行のテーブルを作成
- 書類番号列： （番号やIDなどを仮記入）
- パス列： =LEFT(CELL("filename", $A$1), FIND("[", CELL("filename", $A$1)) - 1)
- 書類番号列： （番号やIDなどを仮記入）
- テーブル名をP_書類パラメータに変更（任意）
- 以下の列と要素を持つ1行のテーブルを作成
- クエリを修正する。
- 手順2で作業エリアに配置したT_まとめ表テーブル上のセルを選択
- クエリタブ > 編集ボタン でPowerQueryエディター起動
- 左のクエリ欄でT_まとめ表を選択状態で、
ホーム > 詳細エディター - 以下になるようにコードを追加・修正
let 書類番号 = Excel.CurrentWorkbook(){[Name="P_書類パラメータ"]}[Content]{0}[書類番号], // 追加 パス = Excel.CurrentWorkbook(){[Name="P_書類パラメータ"]}[Content]{0}[パス], // 追加 ソース = Excel.Workbook(File.Contents(パス & "..\まとめ表.xlsx"), null, true), // 修正（"パス &"の部分） ... フィルターされた行 = Table.SelectRows(変更された型, each ([#"書類番号"] = 書類番号)), // 修正（"= 書類番号"の部分） ... in 削除された列- ホームタブ > 閉じて読み込む
手順4. テーブルを参照して、
作業エリアに配置したまとめ表のデータ（と書類パラメータテーブル）
テーブルデータを参照するときは、
構造化参照の中に"@"は不要
テーブルセルをクリックして構造化参照の式を作成すると、
数式中に"@"が入ることがあります（=テーブル名[@列名]の形式）
これは「挿入するセル」
構造化参照での@は「同じ行」
@が数式に含まれたままセルを移動したときにエラーが発生します。
修正時に不便になるので、
今回のケースでは@を使わない構造化参照の式を使いましょう。
- ❌ =テーブル名[@列名]
- ✅ =テーブル名[列名]
ここまでで、
エクセルで実装後の使い方
作成したエクセルファイルを使えば、
- テンプレートフォルダ内のテンプレート.xltxから新規ファイルを作成し、
作成書類フォルダに保存する。 - テンプレートファイルをダブルクリックして新規ファイル作成。
- ファイル名は自由につけられる。
- ※「セキュリティの警告」
が表示される場合は「コンテンツの有効化」 を設定する。
- テンプレートファイルをダブルクリックして新規ファイル作成。
- まとめ表.xlsxにデータ（ファイル番号含む）
を1行記入し、 保存する。 - 新規ファイル内に、
ファイル番号を記入する。 - すべて更新ボタンを押してデータ読み込み（Ctrl + Alt + F5）
。 - ※「データソースの認証ダイアログ」
が表示される場合は、 「信頼済み/実行許可」 を設定する。
- ※「データソースの認証ダイアログ」
- 新規ファイルを上書き保存する。
ここまでの手順で、
まとめ
定型書類の作成作業では、
エクセルのテーブル、
応用：複数の書類からまとめ表を作ることも可能
この記事では
逆に、