エクセルで見積書を自動計算する作り方｜単価表と関数のテンプレート付き【製造業】

「見積もりをExcelで作っているけど、毎回同じ作業をしている気がする」—そう感じている製造業の方は多いと思う。

材質を確認して、単価表を探して、電卓で計算して、セルに手入力。これを月に何十件も繰り返していると、それだけで担当者の時間が埋まる。しかも、単価表が古かったり、担当者ごとに利益率の基準が違ったりすると、同じ案件なのに見積もり金額がバラつく。

この記事では、Excelの関数だけで見積書の作成を半自動化する方法を書いていく。特別なソフトは使わない。VLOOKUP、INDEX-MATCH、IF、ROUNDといった基本の関数と、単価表の設計だけで、単価の参照と計算ルールを揃えられる。導入効果は、変更前後の作成時間と差し戻し件数で確認する。

エクセルで見積書を作る手順—自動計算までの5ステップ

全体の流れを先に示す。細かい関数は後の見出しで1つずつ説明する。

1. シートを「見積書」と「単価表」に分ける

1枚のシートに単価と見積もりを混ぜると、単価を変えるたびに過去の見積書を探して直すことになる。単価は「単価マスタ」シートに1か所だけ置き、見積書シートはそこを参照するだけにする。

2. 見積書の枠を作る

見積書シートの上部には、客先に出す書類として必要な情報を並べる。配布テンプレートでは次の項目を置いている。

入力するセルと計算するセルは色で分けておく。テンプレートでは黄色が入力欄、水色が自動計算の欄だ。計算式のセルに手で数字を上書きされる事故を防げる。

3. 単価表を作る

材質・板厚ごとの材料単価と加工単価を1行ずつ並べる。作り方は次の「単価表（単価マスタ）の作り方」で詳しく書く。

4. 明細を関数で自動計算する

明細の行では、単価キーから材料単価と加工単価を引き、数量・加工時間・外注費と合わせて原価を出す。原価と粗利率から見積単価を出し、数量を掛けて金額にする。

5. 小計・消費税・合計を出し、PDFで保存する

明細の金額を合計して小計を出し、値引きと消費税を反映して合計にする。客先にはExcelのまま送らず、PDFで保存して送ると、受け取った側で数字が変わることがない。原価や粗利率の列が入ったシートをそのままPDFにしないよう、配布テンプレートでは品名・数量・見積単価・金額だけを並べた「提出用」シートを分けている。

Excelで見積書を作る前に知っておきたい限界

最初に正直に書いておくと、Excelの見積もりには限界がある。そこを理解した上で使うのが大事。

つまり、Excelは「1件の見積もりを正確に・速く作る」ための道具としては十分に使える。でも「過去のデータを活かして賢く見積もる」「チームで運用する」となると、スプレッドシートやデータベースへの移行が必要になる。

とはいえ、まずExcelで仕組みを作ることには意味がある。ここで「どんなルールで単価を決めているか」を言語化する作業をしておくと、後からシステム化するときにスムーズに移行できる。

単価表（単価マスタ）の作り方—まずここを整備する

Excel見積もりの自動化で一番大事なのは、単価表の設計。ここがしっかりしていれば、関数は後からいくらでも組める。

単価表は「見積書」とは別のシートに作る。シート名は「単価マスタ」でいい。構成はこうなる。

ポイントは3つある。

1つ目は、材質と板厚の組み合わせを1行にすること。SUS304の板厚1.0mmと3.0mmでは単価が違う。材質だけでは一意に決まらないので、「SUS304_1.0」のように材質コードと板厚を結合したキーを作っておく。A列に結合キーを入れるか、CONCATENATE関数で別列に作るかはどちらでもいい。キーは重複させない。

2つ目は、最終更新日を必ず入れること。材料費は市況で変わる。半年前の単価で見積もりを出していたら利益が飛ぶ。更新日があれば「この単価、半年以上更新されていないけど大丈夫？」と気づける。

3つ目は、加工単価も同じシートに持つこと。材料費と加工費を別々のシートに分けると管理が煩雑になる。1つのマスタシートに集約しておく方が、後から関数で参照しやすい。

VLOOKUPで単価表から単価を自動参照する

単価表ができたら、見積書シートから関数で参照する。まずはシンプルなVLOOKUPから。

見積書シートのC12セルに「SUS304_1.0」と入力するだけで、材料単価が自動で入る。手で単価表を探す必要がなくなる。

ただし、VLOOKUPには制約がある。検索キーは必ず「一番左の列」にないといけない。マスタのレイアウトを変えたくなったときに面倒になる。また、IFERRORで未登録のキーを0にしているので、0円の行が出たら単価キーの入力ミスか単価表の登録漏れを疑う。

INDEX-MATCHで材質×板厚の2条件から単価を引く

VLOOKUPの制約を超えたいなら、INDEX-MATCHの組み合わせを使う。特に、単価キーを作らずに「材質×板厚」の2条件で単価を引きたいときに役立つ。

この方法なら、検索する列が一番左になくてもいい。後から単価表に「表面処理」の列を足しても、参照する列の範囲を指定しているので関数を書き直す必要がない。

原価と粗利率から見積単価を自動計算する

単価を引けたら、明細の1行ごとに原価・見積単価・金額を計算する。配布テンプレートの12行目に入っている式はこうなる。

原価は「材料費＋加工費＋外注費」。加工費は数量×1個あたりの加工時間×1分あたりの加工単価で出す。見積単価は、1個あたりの原価を「1−粗利率」で割って出す。粗利率30%なら原価を0.7で割る形だ。原価に1.3を掛ける方法とは結果が違うので、社内でどちらの考え方を使っているかを先に揃えておく。

テンプレートに入っている練習用の値で確かめると、数量10個、材料単価350円、加工12分×80円、外注費5,000円の場合、原価は3,500円＋9,600円＋5,000円＝18,100円になる。見積単価は18,100÷10÷0.7を四捨五入した2,586円、金額は25,860円だ。これらはテンプレートの確認用の数字で、実際の価格の目安ではない。

数量別単価の自動切り替え—IF関数の活用

製造業の見積もりでは、数量によって単価が変わるのが普通。数量が増えると段取り替えにかかる費用を多くの個数で分けられるので、1個あたりの単価も変わる。

割引率は会社ごとに違うので、自社のルールに合わせて数字を変える。大事なのは、今まで担当者の頭の中にあった「100個超えたら少し下げる」というルールを、関数として明文化すること。これが属人化の解消につながる。

見積書の小計・消費税・合計を自動計算する

明細の金額がそろったら、見積書の上部で合計を出す。テンプレートでは次の式を使っている。

税率を式の中に「0.1」と直接書くと、税率を見直すときや軽減税率の品目が混ざるときに、すべての式を探して直すことになる。税率はセルに置いて参照する。端数を四捨五入にするか切り捨てにするかも会社のルールで決まるので、ROUNDをROUNDDOWNに変えるなど自社の決まりに合わせる。

なお、見積書は取引の前に条件と金額を示す書類で、インボイス制度で交付を求められる適格請求書そのものではない。同じExcelで請求書まで作る場合は、登録番号や税率ごとに区分した消費税額などの記載事項を、国税庁の案内で確認してから様式を作る。

テンプレート全体の構成

ここまでの関数を組み合わせて、見積もりテンプレートの全体構成を整理する。

配布テンプレートはこの8シート構成だ。担当者は「見積書」シートの黄色いセルに入力し、単価の変更時だけ「単価マスタ」を更新する。単価キーと数量を入力すれば、材料単価・加工単価・見積単価・合計が自動で計算され、客先には原価の出ない「提出用」シートをPDFにして送る。税率や粗利率の基準を変えるときは「設定」シートだけを直せばよく、式を書き換える必要はない。

見積管理もエクセルで行う場合—見積一覧表の作り方

見積書を1件ずつ作れるようになると、次に困るのは「どの見積もりを出して、どれが受注になったか」が追えないことだ。見積書のファイルが増えるほど、フォルダを開いて探す時間が増える。そこで、見積書とは別に1行1件の「見積一覧」シートを作る。

一覧表ができると、関数で状況を数えられる。たとえば、ある月の受注件数と、有効期限が切れたまま返事のない見積もりは次の式で拾える。

配布テンプレートには、この列構成の「見積一覧」と、一覧から月の受注率・粗利率・期限切れを集計する「ダッシュボード」も入れている。見積書の27行目が一覧へ転記する1行になっているので、コピーして値だけ貼り付ければよい。

ただし、エクセルでの見積管理は、転記を人が行う前提になる。複数人が同時に更新する、上長の承認を記録に残す、受注後の案件管理や請求とつなぐ、といった段階になると、転記漏れや二重入力が増えやすい。その段階が見えてきたら、共有できる環境や見積管理システムへの移行を検討する。

さらに自動化するなら—Excelの先にあるもの

Excelテンプレートで仕組みを作ったら、次のステップも見えてくる。

共同編集できる場所への移行。ローカル保存したExcelファイルは、複数人での同時編集に向かない。一方、対応するMicrosoft 365版のExcelでOneDriveまたはSharePointに保存すれば共同編集できる。Googleスプレッドシートへ移し、営業と工場が同時にアクセスする方法もある。

過去データのデータベース化。見積もりが100件、200件と溜まってきたら、1行1案件の一覧表に集約する。「SUS304で板厚2.0mmのレーザー加工、過去にいくらで出した？」が検索で出てくるようになる。この手順は見積もり自動化の全体ガイドで詳しく書いている。

AIによる単価提案。過去データが揃ったら、AIに「この条件に近い過去の見積もりを3件出して、推奨単価を提案して」と指示できるようになる。ただし、提案された単価は人が単価表や条件と照らして確認してから使う。

補助金を活用して初期費用を抑える

Excelテンプレート自体は社内で作れるので費用はかからない。ただ、「テンプレートの設計を外部に頼みたい」「スプレッドシートへの移行まで一緒にやってほしい」という場合は、国や自治体のIT導入・デジタル化を支援する補助制度が使える可能性がある。

補助制度の対象、補助率、申請期限は年度や枠によって変わる。利用を検討する場合は、公式の公募要領と支援事業者の登録状況を確認してから判断する。

まとめ

エクセルでの見積書の自動作成は、高いソフトを入れなくてもできる。

単価表を整備して、VLOOKUPかINDEX-MATCHで参照する仕組みを作る。原価と粗利率から見積単価を出し、小計・消費税・合計までを関数でつなぐ。提出した見積もりは一覧表で管理する。効果は、見積作成時間・単価の差し戻し・担当者間のばらつきを導入前後で比べて確認する。

もっと大事なのは、この作業を通じて「今まで頭の中にあった単価の決め方」が関数として明文化されること。これは、将来スプレッドシートやAIに移行するときの土台になる。

まずは単価表を1つ作ってみるところから始めてみてほしい。

よくある質問

見積もりデータのデータベース化は見積もり自動化の全体ガイドで詳しく扱っている。