皆さんも重々ご承知のように、会社にとって見積はとても大事です。
見積のやり方次第で、儲かる会社・儲からない会社が決まると言っても、過言ではありません。
儲けすぎ、ぼったくりは良くないですが、取らなすぎ、取り忘れはもったいないですし、せっかく仕事をするのであれば、適正な利益を取りたいですよね。
見積の計算は、エクセルでやっている人が大半だと思いますので、見積金額の算出時に使えるエクセルテクニックを紹介していきます。
なお、見積に関しては、そんなに複雑なエクセルのテクニックは必要ないと私は思っています。
基本的に見積は積み上げ式なので、足し算掛け算が主です。
複雑な関数を使うよりも、ベタな計算式を使っていって、計算結果が目に見えた方が良い、透明性が高い方が良いと思います。
とはいえ、知っておいた方が良いテクニックもいくつかありますので、この記事で紹介していきますね。
見積金額算出フォーマットを作ろう
エクセルのテクニックに入る前に、まず自社の見積作成用フォーマットを作りましょう。
毎回毎回、ゼロから計算していると、計算の過程もブレますし、時間もかかります。
また、見積時に反省事項があれば、（この分の金額を見積に入れてなかったな、等）フォーマットの項目を修正するなど反映して、次回からの見積に活かしていくと、より損をしない見積金額になっていきます。
最初はざっくりでも良くて、徐々に良くしていけば良いので、まずは見積金額計算用のフォーマットを作るのが、ファーストステップです。
人件費の上昇、物価の上昇が激しい昨今、取引先に値上げを交渉しようにも、根拠がはっきりしていないと交渉が難しいですよね。
そのためにも、フォーマットが役に立ちます。
今回は、この記事を作成する用に、練習用のエクセルフォーマットを作っていますので、必要に応じてダウンロードしてください。
セルの参照
まずは「セルの参照」です。
セル参照自体は、テクニックということはなく、基本機能ですが、見積の計算をする際には、セル参照はとても重要です。
例えば、仕入れ値があって、販売時に2割の利益を取るように、売価を計算することがあったとします。
その際に、こんな計算式を入れたりしていませんか？
仕入れ値であるA3セルを、1 – 20％の0.8で割る、この計算式自体は間違っていません。
ですが、このやり方だと、目標利益率を25％にした時に、計算式に0.8が入っているセルを、全て0.75に書き換えしなければいけません。
また、他の人がこの計算結果を見た場合に、すぐには利益率が分かりません。
こういった場合は、目標利益率のセルを別途作って、計算式はそのセルを参照するようにした方が良いです。
こうしておけば、目標利益率のセルB1を変更するだけで、すべての値がいっきに変わります。
為替レート、労働者の賃金、消費税等の税率等、基準となる値は、別途セルを作って、そのセルを参照する形にすることが大事です。
見積をする際には「原単位（げんたんい）」を決めて行う場合が多いですが、原単位の改訂があった際でも、一つのセルの値を変えるだけで、すべての値が変わるようになります。
「計算を楽にする、改訂を楽にする、変更漏れ等のミスを減らす」ためにも、値を直接入力することは極力なくして、セル参照を使うようにしましょう。
見積の計算の際は、セルに直接、値を入力することは極力避けて、セル参照を多用する
なお、セル参照を使うためには、「セルの絶対参照」という考え方を身に着けておく必要があります。
絶対参照が分からない方は、Youtube等で「絶対参照」を検索して、ぜひ習得してください。
エクセルの機能に関するテクニック
次は、エクセルの機能に関するテクニックです。
見積計算用のフォーマットに、エクセルの機能を埋め込んでおくことで、毎回決まった手順で計算を行うことができるようになります。
データの入力規則（リスト）
データの入力規則は、「データに入力規則（ルール）を作って、それ以外のデータを入力できなくする」機能です。
見積を決まった手順で行うためには、決まったセルに決まった値を入力していく必要があります。その方が、後から検索しやすい意味もあります。
また、マスタを作って、マスタから対応する値を呼び出してくることも重要です。（後述しますが、VLOOKUPという関数を使って、対応する値を呼び出します）
例えば上記の画像ですと、取引先ごとに、細かい条件があるはずです。（この会社は、何個以上数量がまとまったら値引きする、とか、自社配送するのかヤマトや佐川を使って出荷するのか等々）
そうした条件設定のミスを防ぐためにも、自分自身のストレスを減らす意味でも、引継ぎを出来る体制を作るためにも、セルの値を制限して、マスタから呼び出すことが重要です。
（なお、単純な「有り無し」「YesNo」「男女」といった選択肢が2つしかない場合は、チェックボックスやラジオボタンで設定することもあります）
設定方法については、最近はYouTubeで見るのが一番早いと思いますので、「データの入力規則」で調べてみてください。（以下の機能も同様に、使い方の詳細は、YouTubeで調べてください）
私の過去の記事ですと、こちらの記事にデータの入力規則について、詳しめに紹介しています。
条件付き書式
「条件つき書式」は、条件を満たしたセルの書式を設定する機能です。
一般的なシステムには、必須項目（必ず入力しないといけない値）があって、その値を入力しないと登録ができないようになっています。
エクセルにはそんな機能はないので、条件付き書式を使って、空白であってはならないセルを目立たせるために使用します。いわゆる「ポカヨケ」機能です。
設定方法は簡単で、「指定の値を含むセルだけを書式設定」で、空白セルを条件にして、お好みの書式を設定します。
また、会社で原価率を設定している場合に、原価率を上回った場合に、警告の意味で文字を赤色にする、等にも使えます。
「何か問題があったら一目でわかる」ようにするため、色を付けることは結構大事です。
人間なので、急いでいるときに設定漏れをしてしまうことはありますし、そういったことに気を付けるのも結構ストレスになります。
細かいテクニックですが、「神は細部に宿る」とも言いますので、細かいところも知っておきましょう。
リンク
見積をする際に、よく参照するWEBサイトがあったりします。
例えば、貴金属を扱う商売でしたら金の価格とか、輸出入をする商品でしたら為替レートとか、農産物でしたら市場の価格とか、そういった基本情報を参照する必要がありますよね。
そんな場合は、見積算出フォーマットに、あらかじめWEBサイトのリンクを貼っておくと、わざわざブラウザを開く手間が減るので、便利です。参照忘れも減ります。
（なお、こういった値は、先ほど書いたように特定のセルに値を入れて、セル参照するようにしましょう）
設定方法は簡単で、セルの上で右クリックして、「リンク」を選んで、URLを設定するだけです。
このリンクは、ホームページだけではなく、パソコン内のファイルにリンクすることもできます。
図面があって見積する場合は、図面と見積がセットになっていますよね。
その場合は、図面のPDFをリンクしておけば、「この図面に対する見積である」ことが明確になって、後から見返したときにも分かりやすいです。
これも細かいテクニックですが、覚えておいて損はないでしょう。
見積に活用できるエクセルの関数
冒頭に述べたように、見積の計算は透明性が高い方が良いので、複雑な処理を行う関数は必要ないと私は考えています。基本的な関数を組み合わせて、分かりやすい計算を行っていきましょう。
合計するのは、SUM関数
見積は積み上げ式ですので、やはり一番使うのは基本のSUM関数です。
SUM関数の入力方法についてはわざわざ紹介することも無いと思いますが、合計値はリストの下に入れるだけではなく、リストの外に置いたり、別シートの合計もできる、ということは書いておきます。
見積の計算の場合は、いろいろ確認する値が一覧で見えた方が良いので、合計値は上側に表示する方が良いです。
材料費等、量がたくさんあって、計算書のシートがごちゃごちゃする場合は、材料費は別シートで計算して、合計だけを計算書に表示させる、ということも、よく行います。
四捨五入するのは、ROUND関数
見積においては、四捨五入を行うこともよくあります。
単純に割り算、掛け算をすると、1円未満の端数がでてしまうこともよくあります。
小数点ありで見積をする業界もありますが、だいたいは1円単位での見積だと思いますので、そんな時はROUND関数を使って、四捨五入をします。
また、見積時に100円未満等の細かい金額にならないようにする場合は、ROUND関数を使って、四捨五入の桁数を設定して算出します。
ちなみに、切捨てにする場合はROUNDDOWN、切り上げにする場合はROUNDUP関数です。
項目数を数えるのは、COUNTA関数
数量の合計はSUM関数で良いのですが、項目数を数えるのはCOUNTA関数を使います。
単にCOUNT関数だけですと、範囲内に数字のセルがいくつあるかを数えます。
この辺、エクセルの歴史の問題もあると思いますが、項目（文字列）の数を数える時には、最後にAがついて、COUNTA関数になります。
（実際のところは、COUNT関数はほとんど使うことが無いので、数を数えるのはCOUNTA関数と覚えておいて問題ありません）
見積金額算出の際、どんな時に必要になるかというと、固定費を按分（あんぶん）する際に使用します。
例えば、案件1件につき、見積作成・請求書作成・入金管理等々の事務作業料として、1万円を基本的に取ることにしている場合、この1万円を項目ごとに割り振って、見積単価に含めるということをします。
COUNTA関数で項目数を調べて、固定費を項目数で割って、それを項目ごとの見積単価に加える、という処理になります。
簡単な条件分けは、IF関数
条件によって処理を分ける場合はIF関数を使います。
例えば、製造業の場合は、発注先が材料を支給してくる場合があります。材料を支給してくる場合は材料費を0円にして、材料を自社で調達する必要がある場合は、材料費を計算する、というような場合に、IF関数を使います。
あとは、VIP顧客に特別条件を出している場合は「もしVIPであれば・・・」という条件設定、数量が100個以上で特別条件を出している場合は「もし数量の合計が100個以上であれば・・・」とかでしょうか。
IF文は、ネストといって、いくつものIF文を重ねた式を作ることが可能です。
が、経験則上、ネストは2回ぐらいまでにしておかないと、頭がこんがらがりますし、間違うと結構悲しいです。膨大な時間を無駄にします。
3つ以上の条件がある場合は、次のXLOOKUPという関数を使いましょう。
複雑な条件分け、マスタを参照する場合は、XLOOKUP関数
XLOOKUP関数は、リストから該当する値を探して、引っ張ってきてくれる関数です。
見積をする際には、取引先ごとの条件であったり、商品ごとの注意事項であったり、注意すべきことがありますよね。
「この取引先は、基本掛率がいくら」とか、「この商品はケース単位の場合は値引きOK」とか、取引には、いろんな条件があります。
そういう事柄を、忘れないようにメモをしていく、とか忘れないようにする、等は、とてもストレスがかかる作業です。
また、「忘れないようにする」というのは、人間に依存するので完全な対策にもならず、自分だけのことになるので（いわゆる属人化）、根本的な対策ではありません。
なので、そういった条件は「マスタ」という形で別途リストを作り、XLOOKUP関数で値を引っ張ってくるようにします。
（VLOOKUP関数でも良いですが、メンテナンスの容易さから、最近はXLOOKUP関数の方が使い勝手が良いでしょう）
こうしてマスタを作っていくと、細かい条件が乱雑に増えることも防ぐことができますよ。
履歴を確認するのは、FILTER関数
見積をしていて、過去の見積を参照することは結構あります。
履歴をとるやり方は、後ほど説明しますが、履歴から該当するリストを作成する際は、FILTER関数という関数を使います。
FILTER関数は比較的新しい（Excel2021から）スピルという機能を使っています。昔のエクセルファイルには取り入れられていない可能性がありますので、使っていなかったら使ってみてください。
「どこからどこまで値が入っているのか？」を気にする必要が無いので、とても便利ですよ。
オートフィルタの機能もありますが、わざわざ条件で指定しなくて良いのは地味に便利です。
項目別の集計は、SUMIF関数
見積の計算をしていると、項目別であったり、製品別であったり、何らかの区分ごとに集計して確認したい場合があります。
そんな時は、SUMIF関数を使います。
下記のように、別途小計欄を作って、普通にSUM関数で小計を計算しても構いません。この方が、視認性は良いです。
ただ、このやり方ですと、項目が順番ごとに並んでいる必要があるのと、小計欄にタイトルをつけ忘れて、データベース（テーブル）形式が崩れることもあるので、どちらかというとSUMIFの方をお勧めします。
まぁ慣れたら、同じ結果さえ得られれば、やり方はどちらでも良いと思います。
関数に関しては、他にもあるかもしれませんが、とりあえず良く使うもので思い出すのは、このぐらいです。
関数も毎回、数式を入れるのではなく、フォーマットを固定化させて、見積の工数を削減していきましょう。
見積に活用できるエクセルVBA（マクロ）
見積の計算とは直接関係はありませんが、せっかく計算をしたら、きちんと履歴をとって、後から検索できるようにしたいですよね。
同じような案件が出てきたときに、「過去のあの案件の計算をある程度生かそう」ということができると、工数の削減にもなりますし、見積の正確性も増します。
履歴をきちんととる、保存忘れを防ぐためには、ちょっとしたマクロを使うのが便利です。
Sub SaveTest()
Dim WB1 As Workbook 'ワークブック用変数
Dim WB2 As Workbook 'ワークブック用変数
Dim WS1 As Worksheet 'ワークシート用変数
Dim AName As String '案件名を格納する変数
Dim CName As String '顧客名を格納する変数
Dim FName As String '保存するファイル名を格納する変数
CName = Range("B2").Value '顧客名が記載されているセルをセット
AName = Range("B4").Value '案件名が記載されているセルをセット
'ファイル名は、日付＋顧客名＋案件名
FName = Format(Date, "YYMMDD") & "_" & CName & "_" & AName
Set WB1 = ActiveWorkbook '現在のブックをセット
Set WS1 = ActiveSheet '現在のシートをセット
Workbooks.Add '保存用に新規ブックを作成
Set WB2 = ActiveWorkbook '新規ブックをセット
WS1.Move before:=WB2.Worksheets(1) '計算用のシートを新規ブックに移動させる
'新規ブックを、現在のフォルダに指定のファイル名で保存する
WB2.SaveAs ThisWorkbook.Path & "\" & FName & ".xlsx"
End Sub説明も書いてありますが、計算したエクセルシートを、日付＋顧客名＋案件名で別ファイルとして保存するVBAです。
顧客ごとにフォルダを分けたり、PDFで出力したりも出来ますので、いろいろ試してみてください。（AIに聞けば教えてくれます）
見積書を作成する際のマクロについては、別途記事を作成していますので、ご参照ください。
まとめ
以上、見積を計算する際のエクセルテクニックについて書いてみました。
この記事に書いてあるのは、私が経験した範囲で使えるテクニックですので、他にも使えるテクニックがある可能性はありますが、冒頭にも書いたように、見積は基本的には積み上げ式で、足し算と掛け算の世界です。
テクニックに頼るのではなく、必要な項目を抜けもれなく網羅し、一つ一つの計算式を明らかにして、分かりやすい見積計算フォーマットを作成しましょうね。
自社の見積フォーマットの改良について、ご相談がある場合は、下記のお問い合わせよりお申込みください。ZOOM等で画面共有をしながら、1時間5,000円で承っています。
