見積もりを出して返事が来なかった案件や、「今回は見送ります」と一行だけ返ってきた案件。そのたびに残念だと思っても、半年たって振り返ると、何件負けて何が理由だったのかは思い出せません。負けた理由が分からないままだと、値下げすべきなのか、提案を変えるべきなのかも決められません。

失注分析とは、受注できなかった商談の理由を記録して集計し、次の営業で何を変えるかを決めることです。結論を先に書きます。Googleスプレッドシートで作るなら、失注理由は自由記述にせず、8つ前後の選択肢からプルダウンで選ぶ形にしてください。そのうえで、理由ごとの件数を COUNTIFS、金額を SUMIFS で集計すると、どの理由で一番お金を落としているかが1枚で見えます。

この記事では、3枚のシートの列設計と、コピーして使える数式、理由の書き忘れを色で知らせる条件付き書式、月1回の見直し手順を書きます。

この記事の要点

  • 失注理由は8つ前後の選択肢に固定し、プルダウンで選ぶ。自由記述のままでは集計できない
  • 集計シートでは、理由別の件数を COUNTIFS、金額を SUMIFS で出し、金額の大きい順に見る
  • 月1回、上位2つの理由にだけ打ち手を決める。全部に手を打とうとしない
失注分析表の流れを示した図。商談シートに失注を1行ずつ記録し、理由はプルダウンで8つから選び、金額と競合も残すと、集計シートで理由別の件数(COUNTIFS)と金額(SUMIFS)が出て、月1回、上位2つの理由に打ち手を決める流れが確認できる

この記事の表と数式をそのまま入れたGoogleスプレッドシートを用意しました。失注分析表テンプレートをコピーするを開くと「コピーを作成」の画面になるので、自分のドライブに保存してから使ってください。「使い方」「商談」「理由」「集計」の4シート構成で、結果と8つの失注理由のプルダウン、理由別の件数・金額の集計と受注率、理由が空の失注や30日止まった商談を色付けする書式まで設定済みです。

失注分析表は「商談」「理由」「集計」の3枚で作る

失注分析表は、手で入力する2枚と、数式だけの1枚に分けて作ります。役割は次のとおりです。

シート中身入力のしかた
商談見積もりを出した商談を1行1件で並べる手入力(結果と理由はプルダウン)
理由失注理由の選択肢の一覧最初に1回だけ作る
集計理由別の件数・金額・構成比数式のみ。手で書かない

失注した商談だけを別の表に書くと、受注率が出せません。受注も失注も同じ「商談」シートに並べて、結果の列で分けます。

失注を記録する意味や、商談の振り返りで何を聞くかといった考え方は、失注分析のやり方にまとめています。この記事で扱うのは、それをスプレッドシートで形にする手順だけです。すでに見積もり管理表をスプレッドシートで作るの表を使っているなら、そこに「失注理由」と「競合・発注先」の2列を足すだけでも始められます。

失注理由は8つの選択肢に固定する

2枚目の「理由」シートには、A列に失注理由を1行ずつ並べます。受託の商談なら、次の8つで大半が分けられます。

行失注理由例
2価格が合わない相見積もりで他社のほうが安かった
3納期が合わない希望の公開日に間に合わなかった
4競合に決まった価格以外の理由で他社に決まった
5提案がずれた求められていた範囲と提案がかみ合わなかった
6予算化されなかった社内の稟議が通らなかった
7内製に切り替えた社内の担当者が作ることになった
8案件が中止になった計画そのものがなくなった
9連絡が途絶えた理由が分からないまま返事が来ない

選択肢を固定するのは、集計するためです。「高いと言われた」「予算オーバー」「金額NG」と書き方がばらばらだと、同じ理由なのに別々に数えられてしまいます。

「連絡が途絶えた」は必ず残してください。 理由が分からない失注は、受託ではかなりの割合を占めます。無理に別の理由へ振り分けると、集計が実態とずれます。この行が多いなら、それ自体が見積もり後の追客が弱いというサインです。追客の仕組みは見込み客の追客のやり方で書いています。

商談シートは1行1件で、結果と理由をプルダウンにする

1枚目の「商談」シートには、見積もりを出した商談を1行に1件ずつ書きます。列は次の10列です。

列項目入れ方
A商談名「会社名・案件名」で統一
B顧客会社名
C見積金額税抜の数値で入れる
D見積提出日日付
E結果プルダウン(受注・失注・検討中)
F結果が出た日日付
G失注理由プルダウン(理由シートのA2:A9)
H競合・発注先分かれば会社名
I顧客の言葉断りのメールや電話の一言をそのまま
J再連絡日次に連絡する日。なければ空欄

E列とG列はプルダウンにします。セルを選んで「データ」→「データの入力規則」→「ルールを追加」から設定できます(出典:Google ドキュメント エディタ ヘルプ「セル内にプルダウン リストを作成する」、2026年10月6日確認)。G列は、選択肢を手で打たずに「理由」シートの A2:A9 の範囲を指定してください。理由を足したくなったときに、理由シートに1行足すだけで済みます。

I列の「顧客の言葉」は、プルダウンでは拾えない情報を残す列です。「デザインの方向性が違った」「もう少し早く連絡がほしかった」のような一言が、半年後に提案を見直すときの一番の材料になります。

集計シートで理由別の件数と金額を出す数式

3枚目の「集計」シートでは、理由ごとに失注の件数と金額を並べます。A2〜A9 は理由シートをそのまま参照し(=理由!A2)、B列から右を数式で埋めます。

列項目2行目の数式
B失注件数=COUNTIFS(商談!$E:$E,"失注",商談!$G:$G,A2)
C失注金額=SUMIFS(商談!$C:$C,商談!$E:$E,"失注",商談!$G:$G,A2)
D件数の構成比=IFERROR(B2/SUM($B$2:$B$9),"")
E金額の構成比=IFERROR(C2/SUM($C$2:$C$9),"")
F直近90日の件数=COUNTIFS(商談!$E:$E,"失注",商談!$G:$G,A2,商談!$F:$F,">="&TODAY()-90)

COUNTIFS は、複数の基準に応じた範囲の個数を返す関数です(出典:Google ドキュメント エディタ ヘルプ「COUNTIFS」、2026年10月6日確認)。ここでは、結果が失注であることと理由が A2 と同じことの2つを条件にしています。SUMIFS は、同じ条件で見積金額を合計する関数です(出典:Google ドキュメント エディタ ヘルプ「SUMIFS 関数」、2026年10月6日確認)。

件数と金額を並べるのは、見える順位が変わるからです。たとえば「納期が合わない」は件数が少なくても、大きな案件ばかりなら金額では1位になることがあります。打ち手を決めるときは、金額の構成比が大きい理由から見てください。

集計シートの上には、受注率も1つ置いておきます。H2 に次の式を入れると、結果が出た商談のうち受注した割合が出ます。

H2: =IFERROR(COUNTIFS(商談!$E:$E,"受注")/(COUNTIFS(商談!$E:$E,"受注")+COUNTIFS(商談!$E:$E,"失注")),"")

「検討中」は分母に入れません。結論が出ていない商談まで含めると、受注率が実際より低く見えるためです。受注率を週次の指標として追いたい場合は、営業KPIダッシュボードをスプレッドシートで作るの集計シートに、この式をそのまま移せます。

理由の書き忘れを条件付き書式で色付けする

失注分析表が役に立たなくなる一番の原因は、理由の書き忘れです。結果を「失注」にしたのに G列が空の行を、条件付き書式で目立たせます。手順は次のとおりです(出典:Google ドキュメント エディタ ヘルプ「Google スプレッドシートで条件付き書式ルールを使用する」、2026年10月6日確認)。

  1. 商談シートの A2:J を選択する
  2. 「表示形式」→「条件付き書式」を開く
  3. 「セルの書式設定の条件」で「カスタム数式」を選ぶ
  4. 値の欄に =AND($E2="失注",$G2="") と入れ、背景色を薄い赤にして「完了」を押す

同じ範囲に =AND($E2="検討中",$D2<TODAY()-30) を薄い黄色で足しておくと、見積もりを出して30日たっても結論が出ていない商談が目に入ります。放っておくといずれ「連絡が途絶えた」の失注になるのが、こうした商談です。色が付いた段階で一度連絡を入れるほうが、受注にも失注理由の記録にもつながります。

月1回、上位2つの理由にだけ打ち手を決める

表を作っても、見る日を決めないと更新が止まります。月初に次の4つを順番にやります。

  1. 商談シートで色が付いた行を直す(理由を入れる・結果を更新する)
  2. 集計シートで、金額の構成比が大きい理由を上から2つ選ぶ
  3. その2つについて、I列の「顧客の言葉」を読み返す
  4. 次の1ヶ月で変えることを、理由ごとに1つずつ決める

打ち手は、理由によってだいたい決まっています。

上位に来た理由打ち手の例
価格が合わない松竹梅の3案で見積もりを出す。単価を下げる前に範囲を削る案を作る
納期が合わない稼働表を見てから納期を答える。急ぎの案件は特急料金を設ける
提案がずれた初回の商談で聞く項目を増やし、見積もり前に範囲を文章で確認する
連絡が途絶えた見積もり提出の1週間後と1ヶ月後に再連絡日を入れる

「価格が合わない」が上位に来たときに、すぐに値下げへ進むのは避けてください。単価を下げると、受注しても手元に残る粗利が薄くなります。値付けの考え方は受託の単価を上げる方法で、受注率を上げる他の打ち手は受注率の上げ方で書いています。

四半期や顧客ごとに切り分けて見たくなったら、商談シートを選んで「挿入」→「ピボット テーブル」を使うと、数式を書き足さずに集計の軸を変えられます(出典:Google ドキュメント エディタ ヘルプ「ピボット テーブルを作成、使用する」、2026年10月6日確認)。

商談の記録を案件までつなげるには

1〜数名のチームで、見積もりが月に十件前後までなら、この3枚で十分に回ります。苦しくなるのは、受注した商談を案件の管理表へ転記する作業が毎月発生するようになったときです。

ここからは自社製品の紹介です。Pipelia は受託事業者向けのSFA/CRMで、商談をカンバンで確度・金額とともに管理し、受注すると案件が自動で作られます。

Pipeliaの商談カンバン画面。商談がステージごとに並び、確度と金額が確認できる

よくある質問

エクセルでも同じ失注分析表が作れますか?

作れます。この記事で使っている COUNTIFS、SUMIFS、IFERROR、TODAY はエクセルにも同じ名前の関数があり、シートをまたぐ参照も「商談!$E:$E」の同じ書き方で動きます。プルダウンは「データ」タブの「データの入力規則」で、入力値の種類を「リスト」にして理由シートの範囲を指定してください。

失注理由が2つ以上あるときはどう記録すればいいですか?

一番大きかった理由を1つだけG列に選び、残りはI列の「顧客の言葉」に書いてください。理由を複数選べるようにすると、COUNTIFS で数えたときに1件の失注が2件に数えられ、構成比の合計が100%を超えてしまいます。迷ったときは、その理由がなければ受注できていたかどうかで決めると選びやすくなります。

失注した顧客には、その後どう連絡すればいいですか?

「案件が中止になった」「予算化されなかった」で失注した顧客は、時期が変われば再び相談が来ることがあります。商談シートのJ列に3ヶ月後や半年後の再連絡日を入れ、条件付き書式で今日以前の日付に色を付けておくと連絡を忘れません。「価格が合わない」「競合に決まった」の顧客も、次の案件では条件が変わるので、近況を聞く連絡は残しておく価値があります。