見積もりの自動化はエクセルでどこまでできる？単価表から作る手順

「見積もりを自動化すれば、見積書を出すまでの日数も短くなるはず」。前の見積書から単価を探して電卓で確かめる作業に毎回時間を取られていると、その計算さえ自動になれば早く出せると考えたくなります。ところが、現地調査のメモの整理や社内の確認待ちに日数がかかっている会社では、計算を自動にしても見積書を出す日は早まりません。計算の部分なら、単価表を作って項目を選ぶだけで金額が出る所まで、エクセルの関数で作れます。

本記事では、見積もりが遅れる原因の切り分け方から、単価表の作り方、XLOOKUPの式、PDFで送るまでの手順とつまずく所まで、社内業務の自動化の仕組みを作る会社の視点で順を追ってお伝えします。

前の見積書をコピーする見積もりで起きること

前の見積書をコピーして数字を打ち替える作り方では、毎回、単価を探して直す手間がかかります。コピーした見積書に入っている単価は、その見積書を作った時点の単価だからです。

宛名や日付、前の現場だけの項目を消し忘れたまま送ると、取引先から問い合わせが来て作り直しになります。

とりわけ見落とされがちなのが、合計の欄に数字を手で打ち込んだ見積書をコピーしたときです。明細を直しても合計が変わらないため、明細と合計が合わない見積書を送ることになります。

経営者

前の見積書をコピーすれば、項目はほとんどそのまま使えます。一から作るより早いのではないですか。

Writers-hub
編集部

項目の並びを使い回せるのは、コピーの良いところです。ただ、コピー元の単価が今も同じかは、毎回思い出して確かめることになります。単価を別の表の1か所にまとめ、見積書では項目を選ぶだけにすると、単価を探す手間と直し忘れがなくなります。

ただし、単価を1か所にまとめても、見積書を出す日が早まるとは限りません。見積もりにかかる日数のうち、単価探しと計算に使っている時間が短ければ、縮む日数も短いためです。

見積もりが遅れる原因は計算か材料集めか

見積もりが遅れる原因が計算か材料集めかは、次の5件の見積もりで、工程ごとにかかった時間を記録すると分かります。記録する工程は、次の表の4つです。

時間は、見積書の控えの余白に、工程ごとに分単位で書き込めば足ります。確認待ちは作業の時間ではないので、回した日時と戻ってきた日時を書きます。5件の記録は、見積書を作り替える前の時間として残しておきます。

5件の合計で単価探しと計算と打ち込みが多ければ、単価表と式を入れた見積書で縮められます。メモの整理と確認待ちが多ければ、エクセルを作り込んでも見積書は早く出せません。

見積もりを出すまでの日数と、受注できなかった案件との関係まで確かめたいときは、見積の自動化はソフトを選ぶ前に｜返事の遅れで逃した案件を数えるの数え方を使えます。

過去の見積書20件から作る単価表

単価表は、過去の見積書20件のうち2回以上出た項目の項目名・単位・単価を、見積書とは別のシートに1行ずつ並べた表です。見積書は単価表から単価を引くので、単価を変えるときは単価表の1か所を直すだけで済みます。単価表は次の5つの手順で作ります。

書き写しと項目名の統一にかかる時間は、1件に10分かかるとすると、20件で200分ほどです。塗装工事の会社なら、できあがる単価表は次のような形になります。単価は説明のための例です。

製造業なら、「鉄板の切断」「曲げ加工」のように加工ごとに項目を分け、単位を「回」や「箇所」にします。

項目を選ぶだけで金額が出るエクセルの見積書

項目を選ぶだけで金額が出る見積書は、ドロップダウンで項目を選び、XLOOKUPで単価表から単位と単価を引き、数量を掛けて合計まで出す式で作れます。マクロ（VBA）は使いません。見積書のシートは単価表と同じブックに作り、明細を15行目から34行目までの20行にして、5つの列を次のように使います。

項目を選ぶドロップダウン

項目の欄にドロップダウンを付けるには、B15からB31を選び、「データ」タブの「データの入力規則」を開きます。「設定」タブの「入力値の種類」で「リスト」を選び、その下の欄で単価表の項目名の列を選んでOKを押します。

項目の欄に矢印が出て、単価表にある項目名だけを選べるようになります。項目名を手で打ち込むと、単価表と1字違うだけで次の式が単価を見つけられないため、項目は選ぶだけにします。

単位と単価を引くXLOOKUPの式

XLOOKUPは、指定した値を表の中から探し、同じ行の別の列の値を返す関数です。D15とE15に次の式を入れ、31行目までコピーします。1行目がD15の単位、2行目がE15の単価の式です。

=IF(B15="","",XLOOKUP(B15,単価表!$A:$A,単価表!$B:$B,"未登録"))
=IF(B15="","",XLOOKUP(B15,単価表!$A:$A,単価表!$C:$C,"未登録"))

項目の欄が空の行は空のままになり、単価表に無い項目名が入ると「未登録」と出ます。

XLOOKUPは、Excel 2019とExcel 2016では使えません。自分や社員のパソコンのExcelの版は、「ファイル」タブの「アカウント」にある製品情報で確かめられます。

（参考：XLOOKUP 関数（Microsoft サポート））

古い版で使うVLOOKUPの式

Excel 2019やExcel 2016で使う見積書では、単価の式をVLOOKUPで次のように書きます。

=IF(B15="","",VLOOKUP(B15,単価表!$A:$C,3,FALSE))

VLOOKUPは、範囲の左端の列から項目名を探し、3を指定すると3列目の単価を返します。単位を引くときは3を2に変えます。最後のFALSEを省くと、項目名が完全に一致しなくても近い行の値を返すことがあるので、必ず付けます。

金額と消費税と合計の式

金額の式はF15に入れて34行目までコピーし、明細の下の3行に小計と消費税と合計の式を入れます。

F15（金額）　=IF(B15="","",ROUNDDOWN(C15*E15,0))
F35（小計）　=SUM(F15:F34)
F36（消費税）=ROUNDDOWN(F35*0.1,0)　※単価表の単価が税抜で、すべて標準税率10％の場合の式
F37（合計）　=F35+F36

ROUNDDOWNは、指定した桁で切り捨てる関数で、桁数に0を指定すると1円未満を切り捨てます。数量に12.35㎡のような小数が入ると、単価250円なら3,087.5円のように1円未満が出るため、金額の式で切り捨てます。

消費税は行ごとに出さず、小計に1回だけ税率を掛けて端数を処理します。請求書を適格請求書として出すときは、消費税の1円未満の端数を、一つの適格請求書につき税率ごとに1回だけ処理する決まりです。行ごとに消費税を出して端数を処理し、その合計を書く方法は認められていません。端数を切り捨てるか四捨五入にするかは会社で選べるので、見積書も請求書と同じ出し方にすると、見積と請求の消費税が1円ずれることを防げます。

（参考：消費税の仕入税額控除制度における適格請求書等保存方式に関するQ&A（国税庁））

軽減税率（8％）の品目が混ざる見積書では、税率ごとに小計を分けて、それぞれに税率を掛けます。見積書の上にある「御見積金額」の欄には「=F37」と入れ、合計と同じ数字が出るようにします。

PDFで送るまでの手順

見積書をPDFで送るまでの手順は4つで、ひな形のブックを上書きしないことから始めます。

取引先にはPDFを送り、エクセルのブックは送りません。ブックのまま送ると、単価表のシートも取引先に渡るためです。

作り替えた後の時間の記録

作り替えた見積書で作った最初の5件も、作り替える前と同じ4つの工程で時間を記録します。前後の5件で単価探しと計算と打ち込みの合計を比べると、作り替えで縮んだ時間が分かります。縮んだのに見積書を出す日が変わらなければ、計算以外の工程に時間がかかっている可能性があります。まず、現地調査のメモの整理と社内の確認待ちを確かめます。

作ってみてつまずく4つの場面

エクセルの見積書を使い始めてつまずく場面は4つあり、どれも見積書のシートか単価表の作りで防げます。

単価表に無い項目を入れたいとき

現場ごとに中身が違う「一式」の項目や、その現場で初めて使う材料は、単価表に無いのでドロップダウンで選べません。明細の下の3行（32〜34行目）は、B列とD列とE列に入力規則と式を入れず、項目名・単位・単価を手で打ち込む行にします。F列の金額の式は、この3行にも残します。手で打ち込んだ項目が別の見積もりでもう一度出てきたら、単価表に足します。

明細の行が足りないとき

明細が20行に収まらないときは、明細の途中の行で行を挿入し、上の行をコピーして式ごと貼り付けます。明細の途中に挿入した行は、小計のSUMの範囲に自動で入ります。明細の最後の行と小計の行の間に挿入すると、小計の範囲に入らず、その行の金額が合計から漏れます。

式が数字で上書きされたとき

社員が単価や金額の欄に数字を打ち込むと、その欄の式が消え、単価表を直してもその行だけ金額が変わらなくなります。宛名や日付、項目、数量のように手で入れる欄だけロックを外してからシートを保護すると、式の入った欄には打ち込めなくなります。手で打ち込む3行の項目・単位・単価の欄も、ロックを外す欄に含めます。

ロックは、手で入れる欄を選んで「セルの書式設定」の「保護」タブで外し、保護は「校閲」タブの「シートの保護」で掛けます。

材料の値上がりで単価を変えるとき

材料の値上がりで単価を変えるときは、ひな形のブックの単価表だけを直します。見積もりを1件ずつ別のブックに保存していれば、送った見積書の金額は変わりません。1つのブックに見積書のシートを足していく作り方だと、単価表を直した時点で、過去の見積書もすべて新しい単価で計算し直されます。

エクセルで足りない作業の任せ方

エクセルで足りない作業は現地調査のメモの整理と社内の確認待ちで、この2つはエクセルの外の仕組みを作って縮めます。記録した時間がこの2つの工程に多かった場合に考える仕組みは、次のとおりです。

現場で入れた項目と数量から下書きを作る仕組みでも、単価はエクセルで作った単価表から引けます。現場のメモから見積もりの下書きを作る所を無料のAIで試す手順と、試して合わないときの原因は、見積の自動化を無料のAIで試して合わない原因｜単価表の作り方と頼み方にまとめています。

現地調査のメモから見積もりの下書きを作る所まで自動にしたい場合や、作る時間がない場合は、私たち合同会社Writers-hubが業務の自動化として、今の見積もりの作り方に合わせて仕組みを作ります。料金は個別にお見積もりします。