データ一覧からの請求書の作り方を徹底解説！エクセル関数とクエリ活用【マクロ不要】
- 「毎月、
大量のデータ一覧から手作業で請求書を作っていて時間がかかる…」 - 「マクロ/VBAを使えば自動化できるのは知っているけれど、
コードを書くのはハードルが高いし、 引き継ぎも不安。 」
Excel（エクセル）
本記事では、
Excelのバージョン
本記事は、
環境によっては、
Power Queryは「複数行を抽出する処理を、
Power Query（パワークエリ）
Power QueryはExcel標準機能の1つです。
「セル単位で処理をする関数」
Power Queryはデータ更新がリアルタイムではなく、
Power Queryを使う理由は、
- 複数セルを一度に出力できる
- 難易度が低い
1. 複数セルを一度に出力できる
「1つの条件（請求書Noなど）
- 関数の場合:
- VLOOKUP関数では1セルしか出力できない。
- スピル機能を使えば複数セルの出力が可能だが、
古いExcel（2016未満） では使用できない。 - スピル機能は、
出力範囲に記入済みセルがあると #SPILL! エラーが発生する。
- VLOOKUP関数では1セルしか出力できない。
- Power Queryの場合:
- 複数セルを一度に出力できる。
- Excel 2016以降で標準利用可能（2010、
2013もアドインで対応可能） 。 - 出力先に記入済みセルがあっても、
新規の行・列として挿入される。
- 複数セルを一度に出力できる。
今回はPower Queryを、
2. 難易度が低い
Power Query は、
一方で マクロ/VBA は自由度が高い反面、
ただし関数も併用する
単純な処理のみを行う場面では、
ここでは XLOOKUP 関数をメインに使用していますが、
ここでは、
具体的には、
VLOOKUP や FILTER 関数で行っている処理を
マスタテーブルも活用できるように
商品マスタ（M_商品テーブル）
- M_商品 → T_請求書明細 の紐づけ
- 商品IDをドロップダウンリストから選択可能（データの入力規則）
- 商品ID選択時に、
品名・単価などを自動入力（XLOOKUP関数）
- 商品IDをドロップダウンリストから選択可能（データの入力規則）
- M_得意先 → P_得意先ID の紐づけ
- 得意先IDをドロップダウンリストから選択可能
- 得意先情報（住所・担当者など）
を自動入力（XLOOKUP関数）
※ P_得意先ID ：書類で使用する得意先IDを1つ指定する欄
本記事では、
-
【メイン処理】工程1 Power Queryで明細データから必要項目の抽出 :
複数書類を含む明細一覧から、
特定の請求書Noに紐づくデータのみを抽出。 -
【レイアウト】工程2 書類フォーマットの整形 :
印刷・PDF出力したときに書類として使えるよう、
見た目を整える。 -
【補助機能1】工程3 関数で商品マスタデータを明細に自動記入 :
商品ID選択により、
品名・単価を自動入力。 -
【補助機能2】工程4 関数で得意先マスタデータを書類に自動記入 :
得意先ID選択により、
住所・担当者を自動入力。
この工程は最も重要です。
Power Queryを使い、
テーブルの準備
1つのExcelブックの異なるシートに、
- T_請求書明細 : 請求書の明細データ（複数分の書類データを含む）
- P_請求書No : 請求書Noの記入欄
テーブルの作成方法
【テーブル化の方法】
- テーブルにするセルを選択。
- 挿入タブのテーブル（Ctrl + t）
を選択。
【テーブル名の設定方法】
- テーブルのセルを選択。
- テーブルデザインタブのテーブル名欄にテーブル名を入力
T_請求書明細をPower Queryに事前取り込み
テーブルT_請求書明細を、
T_請求書明細とP_請求書Noをマージ
テーブルP_請求書NoをPower Queryで取り込み、
行っていることは VLOOKUP のように、
結合列の展開
P_請求書Noテーブルに、
出力テーブルの整形
必要に応じて、
出力テーブルの配置
Power Queryで作成したテーブルをシートに配置します。
Power Queryではデータ更新に「更新」
Power Queryは、
元のデータを変更した場合は、
- データタブのすべて更新を選択
- Power Queryで出力したテーブルを右クリック > 更新を選択
テーブルの近くに注意書きを残しておくと安心です。
工程1の出力テーブルを基に、
出力テーブルの列幅の自動変更をOFF設定
Power Queryで出力したテーブルは、
書類フォーマットの用意
請求書の書類フォーマットをブック内に用意し、
整形のコツ
文字の配置（右/左/中央揃え）
出力テーブルの列に拘束されない要素を挿入したい場合は、
入力エリアと出力エリアで分割
「入力エリア : 作業を行う場所」
出力エリアに印刷範囲を設定
入力用の設定欄などが印刷・PDF出力範囲に含まれないよう、
印刷設定の微調整
印刷・PDF出力時にレイアウトが崩れないよう、
ここからは「入力補助」
テーブルの準備
商品情報を管理するマスタデータを準備します。
テーブルの列の「名前定義」
商品ID列の「名前の定義」
- 名前 : 商品ID
- 参照範囲 : =M_商品[商品ID]
なぜ「名前の定義」
Excelの仕様上、
構造化参照 / テーブル参照（例：M_商品[商品ID]）
そのため、
構造化参照 / テーブル参照を使用すると、
データ範囲が自動的に拡張 します。
請求書明細の商品ID列にプルダウンメニュー設定
T_請求書明細テーブルの商品ID列に「データの入力規則」
- 入力値の種類 : リスト
- 元の値 : =商品ID
請求書明細テーブルに商品情報を自動記入
選択した商品IDの値に紐づく情報を、
記入する列と数式
- 品名列 : =XLOOKUP([@商品ID], M_商品[商品ID], M_商品[品名])
- 単位列 : =XLOOKUP([@商品ID], M_商品[商品ID], M_商品[単位])
- 税抜単価列 : =XLOOKUP([@商品ID], M_商品[商品ID], M_商品[税抜単価])
- 税抜金額列 : =[@数量]*[@税抜単価]
- 税区分列 : =XLOOKUP([@商品ID], M_商品[商品ID], M_商品[税区分])
工程3と同様に「入力補助」
テーブルの準備
得意先に関する情報を一元管理するマスタと、
- M_得意先 : 得意先の詳細データ（得意先ID、
得意先名、 住所、 電話番号、 敬称など） - P_得意先ID : 請求書を発行する相手を選ぶための入力欄（1セルのみのテーブル）
テーブルの列の「名前定義」
入力規則（プルダウン）
- 名前 : 得意先ID
- 参照範囲 : =M_得意先[得意先ID]
得意先IDの入力欄にプルダウンメニュー設定
P_得意先ID テーブルのセルに、
- 入力値の種類 : リスト
- 元の値 : =得意先ID
書類フォーマット中に得意先情報を自動記入
関数を使い、
記入する列と数式
- 得意先の社名セル : =XLOOKUP(P_得意先ID[得意先ID], M_得意先[得意先ID], M_得意先[得意先名])
- 得意先の氏名セル : =XLOOKUP(P_得意先ID[得意先ID], M_得意先[得意先ID], M_得意先[担当者名]) & " " & XLOOKUP(P_得意先ID[得意先ID], M_得意先[得意先ID], M_得意先[敬称])
上記で作成したシステムを使用する際は、
-
データの蓄積
売上が発生するごとに、T_請求書明細 テーブルに新しい行を追加。 -
発行情報の入力
「入力エリア」
にある以下のパラメータを書き換え： - P_請求書No : 発行したい番号
- P_得意先ID: 得意先のID
- P_請求書情報 : その他の情報（発行日や備考など）
-
データの更新
以下のいずれかを行い、
テーブルの更新操作を実行。 - データタブのすべて更新を選択
- Power Queryで出力したテーブルを右クリック > 更新を選択
-
出力
内容に間違いがないか確認し、
印刷・PDF出力を行う。
更新忘れに注意！
値を書き換えただけでは明細テーブルは変わりません。
「書き換えたら更新」
本記事では、
本システムは主に、
- 関数 : 単純な処理に使用。
- Power Query : 複数セルを一度に出力する場所に使用。
Power Queryは、
参考：» Excelのクエリとは？パワークエリでできること【活用例・業務改善方法】
当サイトでは、
具体的なPower Queryの活用方法が知りたい方は、