Power Queryとテーブルで定型書類作成の効率化【VBA不要】|さくっとエクセレート

概要

請求書、契約書、申請書などの定型書類を作成するときに、コピペ（コピー&ペースト）を多用していませんか？作業が手間だと感じたことはありませんか？

このような作業はVBAなしで、もっと効率化できます。データのまとめ表を作り、そこからデータを参照できるテンプレートを作成することで実現できます。 VBAなしでは完全自動化はできませんが、「すべて更新」操作だけで1ファイル分のデータ反映が完了します。

本記事では、

どのような解決策があるのか。
どうやってエクセルで実装するのか。

について説明します。

» 請求書のテンプレートの作成方法についてはこちら

エクセルのバージョン

本記事は、エクセル2024（ローカルまたは共有フォルダでの運用）を基に作成しています。
環境によっては、UIなどの細かな点が異なる可能性があります。

コピペを減らすためのアプローチ

定型書類を作成するときのコピペ作業を減らすためには、 「まとめ表のデータを参照し、フォーマットに埋め込む」 アプローチが有効です。

必要となるファイルは2つです。

まとめ表： 各書類の情報をまとめた表
書類テンプレート： まとめ表からのデータを埋め込んで使える定形書類フォーマット

書類を作成するときには、まとめ表に情報を追加し、書類テンプレートで読み込んで保存すれば完了です。各書類の情報は、まとめ表で閲覧できるため、書類間の比較もしやすくなり、ファイル間の整合性も取りやすくなります。

エクセルでの実装方法

エクセルで上記を実装する方法を説明します。

使用するエクセル機能

テーブル： データの格納、受け渡しに使用。
パワークエリ： 外部ファイルの読み込み、加工（必要行のみの抽出）に使用。
テンプレート： 形式を維持して新規ファイルの作成ができるように使用。

ファイル・テーブル構成

各ファイルとその中に作成するテーブルは以下のようになります（ファイル名やテーブル名は任意）。

ファイル1: まとめ表.xlsx
- T_まとめ表: 書類データをまとめて格納するテーブル
ファイル2: 書類テンプレート.xltx
- P_書類パラメータ: 書類のパラメータを記入するテーブル
- T_まとめ表: まとめ表.xlsxからインポートしたテーブル（必要データのみ抽出したもの）

書類テンプレート.xltxから、まとめ表.xlsx内のT_まとめ表を参照し、 P_書類パラメータに記入した書類番号に該当する行のみを取得します。取得したデータを使い、書類テンプレート.xltx内で書類の形式に落とし込みます。

テーブル名のプレフィックス

テーブル名に設定しているT_やP_などのプレフィックス（接頭語）は、
以下の意味でつけています。

T_ : トランザクションテーブル（時系列ごとのイベントを格納）
P_ : パラメータテーブル（パラメータを格納）

※ プレフィックスは必須ではありません

フォルダ構成

定型書類_作成/
 ├─ まとめ表.xlsx（ファイル1）
 ├─ テンプレート/
 │   └─ 書類テンプレート.xltx（ファイル2）
 └─ 作成書類/

まとめ表.xlsx（ファイル1）と同一フォルダにテンプレートフォルダを作成し、この中に書類テンプレート.xltx（ファイル2）を配置します。

まとめ表.xlsx（ファイル1）と同一フォルダに作成書類フォルダも作成し、この中に書類テンプレート.xltx（ファイル2）から作成した書類ファイルを保存します。

作成後のファイル名とフォルダ位置の変更は慎重に

まとめ表.xlsxのパスが変わると、書類テンプレート.xltxからの参照リンクが切れるため、
ファイルを作成後は、まとめ表.xlsxのファイル名や位置は基本的に変更しないようにしてください
（再設定すれば可能）。
書類テンプレート.xltxから作成される書類ファイルは、
まとめ表.xlsxより 1階層深いフォルダ（サブフォルダ）に配置すれば、
フォルダ名に関わらず機能します。

まとめ表.xlsxと書類テンプレート.xltx（から作成される書類ファイル）の
位置関係さえ維持していれば、
定型書類_作成フォルダ（ルートフォルダ）の名前や配置場所は変更可能です。

実装手順

2つのファイル（まとめ表、書類テンプレート）の作成手順を説明します。書類テンプレートからまとめ表を参照するので、先に参照先であるまとめ表から作成します。

名称は任意

ファイル名やテーブル名、列名、クエリ名、変数名は、任意に変更可能です。
好みの名称に置き換えて進めてもOKです。

フォルダの準備

作成していくファイルを配置するフォルダ（定型書類_作成）を用意します。

フォルダ構成の通りに、定型書類_作成フォルダの中に、次の2つを作成します。

テンプレートフォルダ
作成書類フォルダ

用意したフォルダの中にファイルを作成していきます。

ファイル1. まとめ表の作成

書類データをまとめたテーブルを1つだけ作成します。各行が、1書類のデータに相当するような構成にします。書類を特定できるように、書類番号列（列内で重複しない値を格納）は必須です。他にも、書類に必要なデータ項目を列として追加しておきます。

テーブル名は、T_まとめ表に変更します。

テーブルの作成方法

作成するセルを選択
挿入タブ > テーブルボタン
（Ctrl + t）

ファイルは通常のエクセルファイル形式（.xlsx）で保存します。ここでは、まとめ表.xlsxのファイル名にして定型書類_作成フォルダ（ルートフォルダ）に保存しています。

ファイル2. 書類テンプレートの作成

書類テンプレートでは、出力エリアと、作業エリアの2つに分けて作成します。

出力エリア : 書類として出力する領域。印刷やPDF化する場合は、このエリアのみが出力される。
作業エリア : 書類上には直接出力しない内容を記載する領域。データやパラメータなどを配置する。

2つのエリアは、別々のシートに分けることも可能ですが、同じシート上に並べると作業しやすいです。

手順0. テンプレートファイルとして保存

エクセルでファイルを新規作成したら、テンプレート（.xltx）ファイルとしてまとめ表.xlsxの配置場所（ルート）のサブフォルダに保存します。ここでは、書類テンプレート.xltxというファイル名で、定型書類_作成/テンプレート/に保存しています。

手順3でCELL関数を使ってファイルパスを取得しますが、ファイルを保存していないと、うまくパスを取得できないため、事前に保存しておきます（ファイルの作成中にこまめに保存することもバックアップの面で重要です）。

手順2以降を行い、保存するときに
「このブックには外部データが含まれています。テンプレートを保存する前にデータをクリアし、テンプレートを開くたびに自動的にデータを更新するようにしますか？」と聞かれた場合はいいえを選択しましょう。テンプレートから新規ファイルを作成した直後は、ファイルが保存されておらず、ファイルパスが取得できないため、データの更新を行うとエラーが発生するためです。

手順1. 出力エリアを作成する

書類として出力される領域を作成し、印刷領域の設定をしておきます。

目的の書類フォーマットを作成する。
- ※ シート左上に詰めて作成し、下に伸ばしていく。
印刷範囲を設定する。
1. 書類のフォーマットの列を選択 > ページレイアウトタブ > 印刷範囲プルダウン > 印刷範囲の設定
  ※ 改ページプレビュー（表示タブより）を使うと、設定範囲がわかりやすい
2. 書類のフォーマットの列を選択 > ページレイアウトタブ > 拡大縮小印刷欄の横幅：で1ページを選択
3. ページレイアウトタブ > 余白プルダウン > ユーザー設定の余白... > ページ中央：水平にチェック > OKボタン
出力エリアの境界線を視覚化 & 固定化する（任意）。
1. 印刷範囲（出力エリア）右端の1つ右の列を選択
2. 表示タブ > ウィンドウ枠の固定

手順2. 作業エリアにまとめ表データをインポート

パワークエリを使って、まとめ表.xlsx内のT_まとめ表を読み込み、作業エリアに書き出します。

まとめ表.xlsx 内の T_まとめ表 を読み込む。
1. データタブ > データの取得 > ファイルから > Excelブックからボタン
2. まとめ表.xlsxを選択
3. ナビゲーターが開くので、T_まとめ表を選択 > データの変換ボタンを押し、Power Query エディター起動
書類番号 の値（仮設定）でフィルタリングする。
1. Power Query エディター上で、書類番号見出しのプルダウン展開
2. すべて選択を未選択状態に > どれか1つ（すべて選択以外）を選択状態に > OKボタン
出力不要な列 書類番号 を削除する。
1. 書類番号列を選択
2. ホームタブ > 列の削除ボタン
クエリを保存し、テーブルを配置する。
1. ホームタブ > 閉じて読み込むプルダウン > 閉じて次に読み込む...ボタン
2. データのインポートウィンドウで以下の状態に > OKボタン
  - テーブルを選択
  - 既存のワークシートを選択、作業エリア中のテーブルを配置するセルを選択。
  - このデータをデータモデルに追加するを未選択

「セル連動フィルタ」「パラメータークエリ」

手順2～3のように、
セルの値をパワークエリ内で使用し、
該当データを抽出する手法を
「セル連動フィルタ」や「パラメータークエリ」と呼ぶことがあります。

手順3. インポートしたまとめ表データを加工

手順2で設定した「データをインポートする処理（クエリ）」に手を加え、次の2つを可能にします。

セルに記入した値で、フィルタリングできるように変更
データのインポート先のパスを、絶対パスから相対パスに変更

必要なパラメータをシート上に用意する必要があるので、作業エリア（出力エリアの外側）にP_書類パラメータテーブルを作成し、パワークエリに読み込ませ、手順2のクエリに使用します。

P_書類パラメータ テーブルを作成する。
1. 以下の列と要素を持つ1行のテーブルを作成
  - 書類番号列：（番号やIDなどを仮記入）
  - パス列： =LEFT(CELL("filename", $A$1), FIND("[", CELL("filename", $A$1)) - 1)
2. テーブル名をP_書類パラメータに変更（任意）

クエリを修正する。

手順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）形式で上書き保存しましょう。

エクセルで実装後の使い方

作成したエクセルファイルを使えば、「まとめ表のデータを参照し、書類フォーマットに埋め込む」を、「すべて更新」操作のみで可能になります（各書類ごとにすべて更新操作をする必要あり）。まとめ表に複数ファイル分のデータを記入しておけば、それぞれのデータを使った書類がすぐに作成できます。

テンプレートフォルダ内のテンプレート.xltxから新規ファイルを作成し、作成書類フォルダに保存する。
- テンプレートファイルをダブルクリックして新規ファイル作成。
- ファイル名は自由につけられる。
- ※「セキュリティの警告」が表示される場合は「コンテンツの有効化」を設定する。
まとめ表.xlsxにデータ（ファイル番号含む）を1行記入し、保存する。
新規ファイル内に、ファイル番号を記入する。
すべて更新ボタンを押してデータ読み込み（Ctrl + Alt + F5）。
- ※「データソースの認証ダイアログ」が表示される場合は、「信頼済み/実行許可」を設定する。
新規ファイルを上書き保存する。

ここまでの手順で、テンプレートから半自動的に書類を生成できるようになります。

まとめ

定型書類の作成作業では、 VBAなしでもコピペ（コピー&ペースト）回数を減らせます。

エクセルのテーブル、パワークエリ、テンプレートを組み合わせ、「まとめ表のデータを参照し、フォーマットに埋め込む」ことで可能になります。書類フォーマットであるテンプレートファイルから、「すべて更新」操作だけで、まとめ表から1ファイル分のデータが反映でき、書類作成の効率を大幅に改善できます。

» 請求書のテンプレートの作成方法についてはこちら

応用：複数の書類からまとめ表を作ることも可能

この記事では「まとめ表」から「複数の書類」を作成する方法を紹介しました。逆に、「複数の書類」から「まとめ表」を作成することもできます。こちらのパターンは、比較的レイアウトが自由な、定式化されていない文書（例：稟議書や企画書）に向いています。ただし「定式化されていない文書」と言っても、テンプレートの用意は必要になります。