営業KPIダッシュボードをスプレッドシートで作る——関数だけの集計シート

営業KPIダッシュボードとは、営業活動の量と結果を1画面にまとめて、今どこで詰まっているかを一目で分かるようにした集計表です。スプレッドシートなら、ピボットテーブルもマクロも使わずに作れます。使う関数は COUNTIFS・SUMIFS・QUERY の3つだけで足ります。

作るうえで大事なのは1点だけです。ダッシュボードのセルに数字を手で書かないこと。手入力するのは「活動ログ」と「商談」の2枚だけにして、ダッシュボードは全て数式が出す形にします。ここを崩すと、月末に数字を埋める作業が増えて、2〜3ヶ月で更新が止まります。

この記事では、2枚のシートの列設計と、コピーしてそのまま貼れる数式を全部載せます。何をKPIに選ぶかという話は受託の営業KPIの決め方にまとめてあるので、指標そのものを迷っている場合は先にそちらを読んでください。この記事は「決めたKPIを、どう自動で出すか」の作り方です。

この記事の要点

入力するシートは「活動ログ」と「商談」の2枚だけ。ダッシュボードは全て数式が出す
使う関数は COUNTIFS・SUMIFS・QUERY の3つ。集計月は1セルで切り替える
受注件数と受注率は、ステージの現在値ではなく「受注日・失注日」から数える。ここを間違えると過去の月の数字が毎月変わる

営業KPIダッシュボードの構成図。手で入力するのは活動ログと商談の2枚で、COUNTIFS・SUMIFS・QUERYの3つの関数を通して、今月の活動量・受注件数と受注金額・月別の推移がダッシュボードに出ることが確認できる

営業KPIダッシュボードに載せる数字

受託事業者の場合、載せる数字は4ブロックに分かれます。全部を最初から作らなくても、活動量と成果の2ブロックだけで十分に機能します。

ブロック	指標	何が分かるか
活動量	接触数・架電数・アポ数	今月ちゃんと動けているか
成果	新規商談数・受注件数・受注金額・平均単価	出た結果
効率	アポ率・受注率	活動のどこで落ちているか
先行指標	進行中商談の加重見込み	来月以降に何が残っているか

効率の数字は、活動量が少ない月に大きく振れます。月10件しか接触していない月のアポ率は指標として読めません。母数が二桁前半のうちは、率より件数を見たほうが判断を誤りません。

入力するシートは2枚だけにする

1枚目は「活動ログ」です。1行＝1回の接触にします。架電・メール・フォーム営業・紹介など、相手に何かした記録を全部ここに1行ずつ足していきます。

列	項目	入れ方
A	日付	接触した日
B	会社名	営業リストからコピー
C	チャネル	架電／メール／フォーム／紹介（プルダウン）
D	結果	接触／返信／アポ／見送り（プルダウン）
E	担当	1人なら空でよい
F	メモ	次アクションを1行

2枚目は「商談」です。1行＝1商談にします。

列	項目	入れ方
A	商談名	「会社名／案件名」で統一
B	顧客	会社名
C	発生日	商談として立てた日
D	ステージ	初回／提案中／見積提出／受注／失注（プルダウン）
E	金額	税抜の想定受注金額
F	確度	0.2／0.5／0.8 などの数値
G	受注日	受注したときだけ入れる
H	失注日	失注したときだけ入れる
I	失注理由	価格／時期／競合／音信不通など
J	受注年月	数式
K	加重見込み	数式

C列のチャネルとD列の結果は、必ず「データ」→「データの入力規則」でプルダウンにしてください。COUNTIFS が壊れる原因のほとんどは表記ゆれです。「架電」と「電話」が混ざった時点で、件数は正しく出なくなります。

会社名の書き方や、どこまで情報を持つかは営業リストのスプレッドシートテンプレートの列設計に合わせておくと、活動ログへコピーするときに揺れません。

J列とK列は数式です。2行目に入れて下までコピーします。

J2: =IF($G2="","",TEXT($G2,"yyyy-mm"))
K2: =IF(OR($D2="受注",$D2="失注"),0,IFERROR($E2*$F2,0))

J列は受注した月を 2026-09 の形の文字列にする列です。あとで月別集計に使います。K列は進行中の商談だけを金額×確度にした列で、受注・失注した商談は0にしています。

集計月は1セルで切り替える

3枚目に「ダッシュボード」シートを作り、B1に集計したい月の1日を入れます。2026/9/1 と入力すればそれで構いません。

これ以降の数式は全て $B$1 を見る形にします。翌月になったらB1を書き換えるだけで、シート全体の数字が入れ替わります。数式を毎月直す作りにすると、ほぼ確実に途中で放置されます。

月の終わりの判定には EDATE($B$1,1) を使います。B1が月初なので、これで翌月の1日になります。「未満」で切るので、月末が30日でも31日でもそのまま動きます。

活動量は COUNTIFS で数える

ダッシュボードのB4から下に並べます。

B4 接触数: =COUNTIFS(活動ログ!$A:$A,">="&$B$1,活動ログ!$A:$A,"<"&EDATE($B$1,1))
B5 架電数: =COUNTIFS(活動ログ!$A:$A,">="&$B$1,活動ログ!$A:$A,"<"&EDATE($B$1,1),活動ログ!$C:$C,"架電")
B6 アポ数: =COUNTIFS(活動ログ!$A:$A,">="&$B$1,活動ログ!$A:$A,"<"&EDATE($B$1,1),活動ログ!$D:$D,"アポ")
B7 アポ率: =IFERROR(B6/B4,"")

条件を足したいときは、同じ形で 範囲, 条件 のペアを後ろに増やすだけです。担当者別に見たいなら 活動ログ!$E:$E,"田中" を足します。

範囲を $A:$A と列まるごとで書いているのは意図的です。行を足すたびに数式の範囲を直す作業が発生すると、そこで運用が止まります。

架電に絞ったKPIの置き方は電話営業のリスト管理とKPI に、接触から再連絡までの回し方は見込み客の追客のやり方にまとめています。

受注件数と受注金額は「受注日」から数える

ここが一番間違えやすいところです。受注件数を「ステージ列が受注になっている行の数」で数えてはいけません。ステージは現在の状態なので、先月受注した商談も今月受注した商談も同じ「受注」になります。月別に分けられません。

数えるのは G列（受注日）とH列（失注日） です。

B9  新規商談数: =COUNTIFS(商談!$C:$C,">="&$B$1,商談!$C:$C,"<"&EDATE($B$1,1))
B10 受注件数:  =COUNTIFS(商談!$G:$G,">="&$B$1,商談!$G:$G,"<"&EDATE($B$1,1))
B11 失注件数:  =COUNTIFS(商談!$H:$H,">="&$B$1,商談!$H:$H,"<"&EDATE($B$1,1))
B12 受注金額:  =SUMIFS(商談!$E:$E,商談!$G:$G,">="&$B$1,商談!$G:$G,"<"&EDATE($B$1,1))
B13 平均単価:  =IFERROR(B12/B10,"")
B14 受注率:    =IFERROR(B10/(B10+B11),"")

受注率（B14）の分母を「受注＋失注」にしているのにも理由があります。進行中の商談を分母に入れると、商談を増やした月ほど受注率が下がります。営業を頑張るほど数字が悪化する指標は、見るのをやめてしまいます。決着した商談だけで割れば、増やしても下がりません。

そのかわり、失注したときにH列へ日付を入れる運用が要ります。返事が来なくなった商談を放置すると分母に入らないので、四半期に一度は古い商談を見て、失注日と失注理由を入れて閉じてください。理由の分類は失注分析のやり方が使えます。

進行中の商談は確度を掛けて1行で出す

商談シートのK列（加重見込み）を足すだけです。

B16 進行中の加重見込み: =SUM(商談!$K$2:$K)

K列の数式で受注・失注を0にしてあるので、SUM 1本で「今パイプラインに乗っている金額の期待値」になります。確度の置き方と、月別の見込みへの落とし込みは受託の売上予測のやり方に書いています。

SUMPRODUCT で1つの数式にまとめることもできますが、おすすめしません。条件を1つ増やすたびに式が長くなり、半年後に自分で読めなくなります。作業列を1本足すほうが、直すときに安全です。

月別の推移は QUERY 1本で出す

12ヶ月分の受注件数と受注金額は、QUERY で一発で出ます。ダッシュボードの空いているセル（D1など）に1つ入れるだけで、下に表が展開されます。

=QUERY(商談!$A$2:$K,"select J, count(A), sum(E) where J is not null group by J order by J label J '受注年月', count(A) '受注件数', sum(E) '受注金額'",0)

J列（受注年月）でグループ化しているので、受注日が入っている商談だけが月ごとに集計されます。order by J で 2026-08、2026-09 と並びます。

QUERY で月別集計をするとき、month(G) のような日付関数を直接使う書き方も見かけますが、スプレッドシートの QUERY の month() は1月が0、12月が11を返します。表示が1ヶ月ずれて、原因が分かるまでしばらく悩みます。J列のように TEXT(日付,"yyyy-mm") で文字列にしてからグループ化すれば、この罠を踏みません。年をまたいでも正しい順に並ぶという利点もあります。

グラフは2つに絞る

QUERY の結果を範囲に指定して、縦棒グラフを1つ作ります。横軸が受注年月、縦軸が受注金額です。これで「先月より良いのか悪いのか」が一目で分かります。

もう1つ作るなら、活動量のファネルです。接触数・アポ数・新規商談数・受注件数の4つを横棒グラフにすると、どこで落ちているかが見えます。アポは取れているのに商談が立たないのか、商談は立つのに受注しないのかで、次に直す場所が変わります。受注の直前で止まっている場合は受注率の上げ方が参考になります。

グラフを3つ以上置くと、開いたときに見る順番が決まらなくなって、結局どれも見なくなります。2つで足ります。

更新が止まらないようにする3つのこと

1. 既存の行を書き換えない。 活動ログは追記だけにします。商談シートで後から入れるのは、ステージ・受注日・失注日・失注理由の4つだけと決めます。書き換える箇所が多いほど、触るのが億劫になります。

2. プルダウンにできる列は全部プルダウンにする。 前述のとおり、COUNTIFS は文字列の完全一致で数えます。表記ゆれが1つ混ざるだけで数字が合わなくなり、原因を探すのに時間を取られます。

3. 見る時間を決める。 金曜の15分、ダッシュボードを開いてアポ数と加重見込みだけ確認する、くらいで十分です。B1を書き換えるのは月初の1回だけです。ダッシュボードは「作るのが大変」で止まるより、「見る習慣がない」で止まるほうが圧倒的に多いです。

この作り方で足りなくなるところ

この構成で1年くらいは普通に回ります。ただし、次の3つは関数では解決できません。

活動ログと商談が手作業で紐づいている。 同じ会社名を2枚に書いているだけなので、表記が1文字違うと別会社になります
受注のあとが繋がらない。 受注金額は出ますが、そこから外注費を引いた粗利は別の表になります。案件が増えるほど転記が増えます
同時に触ると壊れる。 人が2人以上になると、誰かが行を挿入した瞬間に他の人の数式がずれます

粗利まで1枚で見たい場合は、粗利管理のはじめ方の考え方をこのシートに足すか、道具を変えるかの判断になります。

ここからは自社製品の紹介です。Pipelia は受託事業者向けのSFA/CRMで、営業リスト・商談・受注・案件・外注・粗利が1本につながっているため、今回作ったKPIは入力なしでダッシュボードに出ます。受注すると案件が自動で作られ、外注費を入れれば粗利まで同じ画面で見えます。Freeプラン（0円・1ユーザー・営業リスト100件）でKPIの部分は試せます。

Pipeliaのダッシュボード画面。今月の受注・粗利・回収遅延・今日の要対応が1画面に並んでいるのが確認できる

とはいえ、商談が月に数件で、1人で完結している段階なら、スプレッドシートのほうが速くて融通が利きます。この記事のシートで回してみて、転記が面倒になってから乗り換えを考えれば十分です。

よくある質問

エクセルでも同じダッシュボードが作れますか？

COUNTIFS と SUMIFS はエクセルにも同じ名前・同じ引数であるので、活動量と成果のブロックはそのまま使えます。違うのは QUERY がない点です。月別の推移は、ピボットテーブルを使うか、A列に12ヶ月分の月初日を並べて各行に SUMIFS を書く形で代用してください。行が固定なので、むしろ壊れにくいという利点もあります。

受注率はどの数字で割るのが正しいですか？

この記事では「受注件数 ÷（受注件数＋失注件数）」、つまり決着した商談だけで割っています。進行中の商談を分母に入れると、新規商談を増やした月ほど受注率が下がり、営業を頑張るほど数字が悪く見える指標になってしまうためです。この計算にする場合、失注した商談に失注日を入れて閉じる運用が前提になります。

スプレッドシートは何件くらいまで耐えられますか？

明確な上限は決まっていませんが、重くなる原因はだいたい決まっています。列全体を参照する数式の数、ARRAYFORMULA の多用、QUERY の本数の3つです。この記事の構成なら重い数式はダッシュボードの十数個と QUERY 1本だけなので、商談が年に数百件の規模で困ることはまずありません。動作が重いと感じたら、まず不要な条件付き書式とグラフを減らすのが効きます。