- 第1回：LETで計算式を整理
- 第2回：FILTER・VSTACK・HSTACKで明細を整理
- 第3回：XLOOKUPで品目コードから単価を自動参照
👉 完成版は BOOTHショップ 「P!rosh Lab」 にて公開中です。
[BOOTHショップはこちら]
第1回ではLET関数を使って複雑な数式を整理し、第2回ではFILTERで必要な行を抽出、VSTACK・HSTACKで表を結合する方法を学びました。
ここまでは「見積書の準備運動」として、関数そのものの仕組みを理解することを目的に進めてきました。
そして第3回では、いよいよXLOOKUPを組み合わせて、品目を選ぶだけで単価や単位が自動参照され、数量を入力すれば金額まで自動計算される「Excel見積書の自動化」に挑戦します。材料リストの準備
材料リストシートを作成
まずは「リスト」シートを作り、見積書で使う材料や品目を一覧化します。
品名・単価・単位など、必要な情報を列ごとに整理して入力しましょう。
テーブル化して管理しやすくする
Excelでは、材料リストを テーブル化 しておくと参照が簡単になります。
手順
- 範囲を選択
- メニューから「挿入」→「テーブル」をクリック
- テーブル名を「品目リスト」に変更
XLOOKUPでの参照がわかりやすくなる
テーブル化して名前を付けておくと、数式で列名を直接指定できます。
例：
=XLOOKUP(入力シート!A2, 品目リスト[品名], 品目リスト[単価])
👉 「品目リスト[品名]」「品目リスト[単価]」のように列名で指定できるため、式が直感的でわかりやすくなります。
テーブル名を確認する方法
- テーブル内のセルをクリック
→ テーブルを選択すると、リボンに「テーブルデザイン」タブ（または「テーブルツール」→「デザイン」）が表示されます。 - テーブルデザインタブを開く
→ 左上に「テーブル名」という入力欄があります。 - 現在のテーブル名を確認
→ 既定では「Table1」「Table2」などが表示されます。
テーブル名を修正する方法
- 「テーブル名」の欄に直接入力して変更できます。
- 例：
Table1→品目リストに変更すると、数式で次のように使えます。
=XLOOKUP(A2,品目リスト[品名],品目リスト[単位],"",FALSE)
👉 列名をそのまま参照できるので、式が直感的で読みやすくなります
メンテナンスのコツ
- 新しい品目を追加
テーブルの最下行に入力するだけで自動的に範囲が広がります。 - 単価の更新
このリストを修正すれば、見積書全体に反映されます。 - 未登録品目の防止
必ず材料リストに登録してから入力シートで選択する流れにすると安心です。
まとめ
「材料リスト」は見積書自動化の マスタデータ です。
ここを正しく管理することで、見積書全体の精度と効率が大きく向上します。
入力シートの設定
入力シートを作成
新しいシートを「入力」と名前を付け、以下の列を用意します。
| 品名 | 数量 | 単位 | 単価 | 金額 |
👉 「品目名」と「数量」はユーザーが入力する欄、それ以外は自動表示される欄です。
品目名をドロップダウンリストで選択
- 手順
- 「品名」列を選択
- メニューから データ → データの入力規則 を選択
- 「リスト」を選び、材料リストシートの「品名」列を参照範囲に指定
これで、ユーザーはプルダウンから品目を選べるようになります。
XLOOKUPで単位・単価を自動参照
- 単位を取得する式
=XLOOKUP(A2,品目リスト[品名],品目リスト[単位],"",FALSE) - 単価を取得する式
=XLOOKUP(A2,品目リスト[品名],品目リスト[単価],"",FALSE)
👉 "" を返すことで、セルが空欄になります
金額の自動計算
- 数量 × 単価 を計算する式を「金額」列に設定します。
=IF(A2="","",B2*D2)
👉 品目名が空欄なら金額も空欄にして、不要な行を作らないようにします。
まとめ
入力シートでは、ユーザーは品目をリストから選び、数量を入力するだけです。
- ドロップダウンリストで入力ミスを防止
- 単位・単価はXLOOKUPで自動参照
- 金額も自動計算される
これで「材料リスト → 入力シート → 自動計算」の流れが完成します。
内訳シートを印刷用に整える
内訳シートの役割
入力シートで作成した明細を 印刷用の見積書フォーマット に反映するシートです。ここで整えたデータが、そのまま見積書として出力されます。
FILTER関数で必要な行だけ抽出
入力シートから「品目名が空欄でない行」だけを抽出します。
内訳シートの A1 セルに次の関数を設定することで、入力シートで入力したデータがそのまま内訳として表示されます。
FILTER関数を使うことで、不要な行は自動的に除外され、印刷用にすっきり整います。
=FILTER(入力!A1:E100, 入力!A1:A100<>"")👉 この式により、入力済みの明細だけが抽出され、不要な空行は自動的に除外されます。
印刷用シートに必要な明細だけがすっきり表示されるので、見積書としてそのまま使いやすくなります。
式の構造と意味
=FILTER(入力!A1:E100, 入力!A1:A100<>"")入力!A1:E100
- 入力シートの A1:E100 範囲を対象にします。
👉 「品目・数量・単位・単価・金額」などの明細データが入っている想定。
入力!A1:A100<>""
- 条件部分。A列（品目名など）が 空欄でない行だけ を抽出します。
👉 「品目が入力されている行だけを取り出す」フィルター条件。
FILTER(範囲, 条件)
- 指定した範囲から、条件を満たす行だけを返す関数。
👉 この場合「入力済みの明細リスト」を抽出する動きになります。
全体の意味
- 入力シートの A1:E100 から、A列が空でない行だけを抽出
- 結果として「入力済みの明細リスト」が返る
- 空行は自動的に除外されるので、印刷用シートにすっきり表示できる
実務的なポイント
- FILTERを使うことで「入力済みだけを表示」できるので、見積書の内訳が整う
- 不要な行を除外できるので、印刷用フォーマットにそのまま活用可能
次のステップ
ここまでで「リスト表示」は完成しましたが、これだけでは見積書としては少し物足りません。
👉 最終行に材料の合計を自動で追加することで、見積書らしい完成形へと近づけていきます
ここからがPOINT
FILTER関数で商品のリストを表示するところまではできました。
ただ、それだけでは見積書としては少しさみしいですよね。
👉 ここからは、リストの最終行に 材料の合計を自動で表示させることで、見積書らしい形に仕上げていきます。
VSTACKだけだと不十分？
次の式は、VSTACK関数を使って明細の下に「合計行」を追加しようとしています。
=VSTACK(FILTER(入力!A2:E100, 入力!A2:A100<>""),{"","","","合計", SUM(入力!E2:E100)})前半：FILTERで「入力済みの明細」を抽出
後半：{"","","","合計", SUM(入力!E2:E100)} で合計行を作成（列数を合わせるために空欄 "" を3つ挿入）
👉 一見動きそうに見えますが、列数が合わないと縦配列が正しく整わないことがあります。
縦配列＋HSTACKで合計行を正しく追加
そこで、HSTACKを組み合わせて「合計行」を横方向に作り、列数を揃える工夫をします。
=VSTACK(FILTER(入力!A:E, 入力!A:A<>""), HSTACK("","","","合計", SUM(INDEX(FILTER(入力!A:E, 入力!A:A<>""),,5))))- FILTERで「入力済みの明細」を抽出
- HSTACKで「合計行」を横方向に構築し、列数を揃える
- VSTACKで明細と合計行を縦に結合
👉 この「縦＋横の組み合わせ」が、見積書らしい体裁を整えるポイントです
LETで拡張し、見積書らしくする
=LET(
明細, FILTER(入力!A1:E100, 入力!A1:A100<>""),
金額合計, SUM(INDEX(明細,,5)),
消費税, 金額合計*0.1,
合計行, HSTACK("","","","合計", 金額合計),
消費税行, HSTACK("","","","消費税", 消費税),
税込行, HSTACK("","","","税込", 金額合計+消費税),
VSTACK(明細, 合計行, 消費税行, 税込行)
)式の構造と意味
明細
FILTER(入力!A1:E100, 入力!A1:A100<>"")- 入力シートから「品目名が空でない行」だけを抽出
👉 「入力済みの明細リスト」を取り出す
金額合計
SUM(INDEX(明細,,5))- 明細の5列目（金額列）を合計
👉 材料費の合計金額を計算
消費税
金額合計*0.1- 合計金額の10%を消費税として計算
👉 税率はここでは固定で10%
合計行
HSTACK("","","","合計", 金額合計)- 横方向に「合計」ラベルと金額を並べた1行を作成
👉 明細の下に追加するための行
消費税行
HSTACK("","","","消費税", 消費税)- 横方向に「消費税」ラベルと金額を並べた1行
税込行
HSTACK("","","","税込", 金額合計+消費税)- 横方向に「税込」ラベルと合計＋消費税を並べた1行
VSTACK(明細, 合計行, 消費税行, 税込行)
- 縦方向に「明細 → 合計 → 消費税 → 税込」を結合
👉 見積書らしい内訳表が完成
まとめ
- FILTER → 入力済み明細だけ抽出
- SUM＋INDEX → 金額合計を計算
- HSTACK → 合計・消費税・税込の行を作成
- VSTACK → 明細の下に追加して縦配列に整える
👉 この流れで「単なるリスト表示」から 見積書らしい体裁 へと進化します。
見積書フォーマットを整える
表紙＋内訳の2枚構成の考え方
- 表紙シート
- 見積書のタイトル（御見積書）
- 顧客情報（宛名・会社名・担当者）
- 自社情報（会社名・住所・電話番号）
- 見積番号・日付・案件名
👉 表紙は 常に1枚だけ。リストが増えても表紙は増えません
- 内訳シート
- 商品リストや数量・単価・金額
- 合計・消費税・税込合計
👉 リストが長くなると、印刷時に2ページ目・3ページ目と「内訳部分だけ」が増えます。
- 表紙と内訳を別シートに分けるメリット
- 表紙は常に1枚目に固定
- 内訳はリストが増えれば自動的に2枚目・3枚目へ展開
👉 こうすれば「表紙は1枚だけ」「内訳は必要に応じて複数ページ」という自然な構成になります。
ここまでで、見積書の内訳自動化は完成です。
つまり、見積書として最も重要な「計算と集計の自動化」はもうできています。
あとは見た目を整えて表紙を作成すれば、正式な見積書として完成です。
この部分はデザインや体裁の仕上げ作業になるので、用途や自分の好みに合わせて自由にアレンジしてください。
完成版テンプレートについて
品目や単価の入力はご自身で行っていただきますが、完成版では 表紙・備考欄・署名欄 まで整った正式な見積書テンプレートをご用意しています。
👉 「入力するだけで完成形が出力される」学ぶステップを飛ばして、すぐに完成形を使いたい方へ、完成版テンプレートすぐに使いたい方へ BOOTH版をご用意しています。
販売版は表紙が付くだけではありません。
- 顧客情報や案件名を入力するだけで自動反映
- 備考欄や署名欄も整った正式な見積書体裁
- 長いリストでもページ分割されて印刷がきれいに出力
といった ＋αの機能 を備えています。
すぐに使える完成形のテンプレートになっていますので、ぜひチェックしてみてください。
👉 完成版は BOOTHショップ 「P!rosh Lab」 にて公開中です。
[BOOTHショップはこちら]
