Lucky Field

見積システムをエクセルで自作。単価表より先に入れる列

AI・データ活用 書いた人：東海市の中小企業診断士 吉田直樹（プロフィール）
見積システムをエクセルで自作。単価表より先に入れる列

結論から、いきます。見積をエクセルで自作するなら、単価表を作る前に入れる列があります。原価と、粗利額です。

見積書の多くは、合計金額しか見えません。だから「5%なら」と言われたときに、その場で判断できません。

5%の値引きは、粗利の5%ではありません。原価が7割の商売なら、粗利の16%以上が消えます。

今日は、その2列を持った見積の作り方を置いていきます。

見積システムは、エクセルで自作できるのか？

単価表から選んで組み立てる商売なら、できます。

品番と単価の一覧を1枚のシートに作り、見積書のシートから引っ張るだけです。金額の打ち間違いは、これでほぼ無くなります。

一方で、図面や現地を見て都度積算する商売は、自作の範囲を超えます。積算は職人の頭の中にある判断そのものなので、エクセルにもAIにも簡単には移せません。

なぜ、単価表より先に原価の列なのか？

あとから足せない列だからです。

単価だけの表で運用を始めると、半年後には見積書が数百枚たまります。そこから「原価も入れよう」となると、過去の見積を1枚ずつ開いて入れ直すことになります。

そして、原価の列が無い見積書では、こういう会話になります。

「5%だけ何とかならない？」 「……（いくら減るのか分からないまま）分かりました」

原価と粗利額が見えていれば、この場で答えが出ます。

値引き前5%値引き後
売価1,000,000円950,000円
原価700,000円700,000円
粗利（額）300,000円250,000円

売価は5%下がっただけですが、粗利額は50,000円、16.7%減っています。原価は1円も下がらないからです。

⚠ 判断するのは率ではなく額です。「50,000円を今回は出す」と決められるなら、値引きしてもかまいません。分からないまま受けるのが、いちばん危ないです。

表は、どう作ればいいのか？

シートは2枚です。

① 単価表シート（単価表）

A：品番B：品名C：単価D：原価
K-100取付金具 A1,200840
K-200取付金具 B1,8001,190

② 見積書シート

品番を入れたら、品名・単価・原価が自動で入る形にします。B列（品名）にはこの式を入れます。

=IFERROR(VLOOKUP($A2,単価表!$A:$D,2,FALSE),"")

単価はいちばん右の数字を 3、原価は 4 に変えるだけです。

  • 単価（D列）：=IFERROR(VLOOKUP($A2,単価表!$A:$D,3,FALSE),"")
  • 原価（F列）：=IFERROR(VLOOKUP($A2,単価表!$A:$D,4,FALSE),"")

そして、粗利額の列を1つ足します。

=(D2-F2)*C2        ← （単価 − 原価）× 数量

最後に、粗利額の合計を見積書の隅に出します（=SUM(G2:G50)）。ここが、値引きを求められたときに見る数字です。

⚠ 客先に出す印刷範囲からは、原価と粗利額の列を外してください。列を右のほうに置いて、印刷範囲を左側だけに設定します（ページレイアウト → 印刷範囲 → 印刷範囲の設定）。

VLOOKUPとXLOOKUP、どちらを使うのか？

会社のExcelのバージョンで決まります。

XLOOKUPはExcel for Microsoft 365・Excel 2024・Excel 2021などで使えますが、Excel 2016と2019では使えません（Microsoft「XLOOKUP 関数」）。

事務所のパソコンでだけ動いて、現場のパソコンで #NAME? になる。これが起きると、その表はもう信用されません。

⚠ 社内でバージョンがばらついているなら、全員が使えるVLOOKUPで作るのが確実です。

そして、式が壊れる原因のほとんどは関数ではありません。品番の表記ゆれです。「K-100」「K100」「Ｋ-100」が混ざると引けません。品番はプルダウンで選ぶ形にしてください（データ → データの入力規則 → リスト）。

値引きを求められたとき、何を見るのか？

残る粗利額と、そこから出ていく固定費です。

「この1件で、いくら残るのか」。それが分かったうえで、今回は取りにいくのか、断るのかを決めます。

⚠ 率で守ろうとしないでください。率を下げてでも額が増える取引はあります。考え方は建設・工務店の見積りで利益を守るに書きました。

値引きの限界を先に決めておきたい場合は、限界利益の考え方が使えます。

AIを使うと、何が変わるのか？

見積そのものより、その手前と後ろが変わります。

  • 過去の見積を探す … 「去年の◯◯商事の、あの案件」が出てこない。これは探し方の問題で、図面管理システムの自作と同じ形で解決できます
  • 請求書に転記する … 見積の内容を請求書に手で打ち直しているなら、そこは自動化できます。実際に、撮った伝票を読み取って請求書まで作る仕組みを納めました（町工場の請求書づくり）

⚠ 図面から自動で積算する仕組みは、勧めていません。専用の会社が取り組んでいる領域で、暗黙知の言語化も必要です。単価表から組み立てる部分だけを自動化するほうが、確実に効きます。

自作をやめたほうがいいのは、どんな場合か？

4つあります。

  • 図面や現地を見て、都度積算している … エクセルの範囲を超えます
  • 材料費が日々動く … 単価表の更新が追いつきません
  • 営業が複数人いて、同時に見積を作る … 同時編集の条件（Microsoft 365とOneDrive・SharePoint Onlineへの保存）が要ります
  • 社内に、表の面倒を見られる人がいない … 式が壊れたとき止まります

今日やること

直近の見積書を1枚開いて、隅に手書きで3つ足してください。

① この見積の原価はいくらか：__________
② 粗利額はいくらか（売価 − 原価）：__________
③ 5%値引きを受けたら、粗利額はいくら減るか：__________

③がすぐ出せなかったなら、その2列を入れる価値があります。

「原価なんて、うちはどんぶりだから出せない」と思われたかもしれません。それでも大丈夫です。正確な原価でなくていいので、主要な10品目だけ、仕入値を入れるところから始めてください。そこから見えてきます。応援しています！

よくある質問

見積システムはエクセルで自作できますか？

できます。品目の単価表を1枚のシートに作り、見積書のシートからVLOOKUPやXLOOKUPで引っ張る形にすれば、金額の打ち間違いはほぼ無くなります。ただし単価だけを引っ張る作りにすると、値引きのときに利益がいくら減るのかが見えません。単価表には原価の列も持たせて、見積書の側に粗利額を表示させてください。作る順番を間違えると、後から全部作り直しになります。

見積書のエクセルには、どんな列が必要ですか？

品番・品名・数量・単価・金額に加えて、原価と粗利額の2列です。原価は単価表から引っ張り、粗利額は「（単価−原価）×数量」で計算します。粗利額の合計を見積書の隅に出しておくと、値引きを求められたときに、いくらまでなら受けられるかがその場で分かります。客先に出す印刷範囲からは、原価と粗利額の列を外しておきます。

VLOOKUPとXLOOKUPは、どちらを使えばいいですか？

会社のExcelのバージョンで決まります。XLOOKUPはExcel for Microsoft 365、Excel 2024、Excel 2021などで使えますが、Excel 2016と2019では使えません（Microsoft「XLOOKUP 関数」）。社内でバージョンがばらついているなら、全員が使えるVLOOKUPで作るのが確実です。式が壊れる原因のほとんどは関数の種類ではなく、単価表の品番の表記ゆれです。

見積の自作をやめたほうがいいのは、どんな場合ですか？

図面や仕様書から都度積算が必要な場合、材料費が日々動いて単価表が追いつかない場合、営業が複数人いて同時に見積を作る場合です。特に図面からの積算は、暗黙知の言語化が必要で、エクセルでもAIでも簡単には置き換わりません。単価表から選んで組み立てられる商売なら自作で足ります。それ以外は既製の見積ソフトのほうが確実です。

あわせて読みたい

小さな会社の売上アップ・利益改善のご相談を承っています。

お問い合せはこちら