この記事では、それぞれの要素の作り方と、数式がスケールで破綻するポイント・代替手段まで、2026年現在の実践知識を順を追って解説します。
崩れないCRM営業パイプラインのデータ構造
最もよくある失敗は、CRMエクスポートの上に直接数式を組むことです。HubSpot、Salesforce、その他多くのCRMがエクスポートするCSVは、一見きれいに見えます。しかし実際に開いてみると、クローズ日が3種類の形式で混在し、ステージ名の末尾に空白があり、受注金額が「¥1,200,000」というテキスト文字列で格納され、担当者名は先週誰かがCRMの表示名を変えたせいで社内リストと一致しない--という状況になっています。
生データシートには以下の列を用意してください。
| 列 | 備考 |
|---|---|
| 案件ID | CRMのユニークキー。案件名をキーに使うのは禁物 |
| 案件名 | テキスト、空欄あり |
| 担当者 | 担当者名。エクスポートごとに表記ゆれが発生しやすい |
| ステージ | 管理語彙を使うが、CRMは予告なく新ステージを追加する |
| クローズ予定日 | 複数システムのエクスポートでは混在形式が確実 |
| 金額 | 通貨記号が混入したテキストになっていることが多い |
| 確度（%） | CRMが自動入力することもあれば、空欄のこともある |
| 最終活動日 | 停滞案件検出のキー項目 |
| ソース | どのシステムからエクスポートされた行か |
10〜20名の営業チームの場合、年間1,500〜4,000件の案件行が典型的で、このシートは高速に動作します。8,000行を超えるケース--複数年の履歴インポートや、M&A後のCRM統合など--では、クロスシートのルックアップを実行する前にフィルタリングしてください。
クローズ予定日は防御的にパースしてください。同じ列に「2024-01-15」「2024/1/15」「2024年1月15日」が混在することは実際によくあります。日付ロジックは必ずIFERRORでラップしてください。
=IFERROR(DATEVALUE(TEXT(A2,"YYYY-MM-DD")), IFERROR(DATEVALUE(A2), ""))
これですべての問題が解決するわけではありませんが、1行の不正データが3,000行にわたる数式全体を止めてしまうのを防ぎます。
金額列の通貨書式は、ヘルパー列（「金額（数値）」など）で除去してください。
=IFERROR(VALUE(REGEXREPLACE(B2,"[¥￥,]","")),0)
生データを上書きせずヘルパー列で処理するのがポイントです。翌週CRMが少し違う形式でエクスポートしてきても、修正は1か所の数式だけで済みます。
ステージ加重によるCRMパイプライン価値の算出
ステージ加重こそ、多くのパイプライントラッカーが崩壊するポイントです。よくある失敗は、確度をSUMIF数式に直接ハードコードしてしまうことです。営業企画が「提案中」の確度を40%から50%に変更した場合、3つのシートにまたがる8つの数式を手作業で修正することになります。
代わりに、Configシートに独立したステージ加重テーブルを作成してください。
| ステージ | 加重 |
|---|---|
| 見込み | 10% |
| 検討中 | 25% |
| デモ実施済 | 40% |
| 提案中 | 50% |
| 交渉中 | 75% |
| 口頭合意 | 90% |
| 受注 | 100% |
| 失注 | 0% |
数式アプローチの比較
パイプライン計算には主に3つの方式があります。行数・エラー耐性・保守コストの観点で比較すると、次のとおりです。
| 方式 | 行数の上限目安 | エラー耐性 | ステージ変更時の保守 |
|---|---|---|---|
| ネストSUMIF（ハードコード） | 〜2,000行 | 低（1行エラーで合計が狂う） | 高コスト（数式を直接編集） |
| SUMPRODUCT＋Configテーブル | 〜8,000行 | 高（IFERRORで保護） | 低コスト（Configテーブルのみ更新） |
| QUERY | 〜12,000行以上 | 高（集計クエリで安定） | 中コスト（列名変更時に修正要） |
加重パイプライン価値はSUMPRODUCTで計算します。
=SUMPRODUCT(
(Data!D2:D5000<>"受注")*(Data!D2:D5000<>"失注"),
IFERROR(VLOOKUP(Data!D2:D5000, Config!$A$2:$B$9, 2, FALSE), 0),
IFERROR(Data!E2:E5000*1, 0)
)
この数式は同じ行数でのネストSUMIFチェーンと比較して3〜5倍速く動作し、エラー時も安全に動作します。ステージ名がConfigテーブルと一致しない場合、IFERRORが#N/Aではなく0を返すため、合計値全体が壊れることがありません。
担当者別の加重パイプラインには、QUERYが適しています。ARRAYFORMULA系のSUMIFは参照セルが5万を超えると重くなりますが、QUERYは8,000行超でも安定して動作します。
=QUERY(Data!A:F,
"SELECT C, SUM(E) WHERE D<>'失注' GROUP BY C ORDER BY SUM(E) DESC",
1)
停滞案件の検出
45日間誰も触れていない案件を含むパイプラインは、絵に描いた餅です。Salesforce「State of Sales」レポートをはじめとする複数の営業調査によると、パイプライン精度が低い原因の約40%は、本来とっくに失注登録すべき案件が整理されずに残り続けていることにあります。
データシートにヘルパー列を追加して停滞案件にフラグを立てます。
=IF(
AND(
D2<>"受注",
D2<>"失注",
IFERROR(TODAY()-DATEVALUE(G2), 999)>45
),
"停滞",
""
)
G列は最終活動日です。活動日が空欄の場合、IFERRORが999日を代入するため、活動日未登録の案件は常に停滞フラグが立ちます。これは意図的な設計です--活動日がない案件は、200日前から活動がない案件と同じ問題だからです。
この列に条件付き書式（「停滞」で赤塗り）を設定すると、データシートが単なる記録ではなく、実際のレビューツールとして機能します。
CRM営業パイプラインのダッシュボードタブを作成する
ダッシュボードタブは、営業部長が実際に見るものです。ドリルダウンなしで3つの問いに答えられる設計にしてください。今四半期の加重パイプライン合計はいくらか、ステージ別にどう分布しているか、誰が最も多くの停滞案件を抱えているか。
ステージ別加重パイプライン：
=QUERY(Data!A:H,
"SELECT D, SUM(E), COUNT(A)
WHERE D<>'受注' AND D<>'失注'
GROUP BY D
ORDER BY SUM(E) DESC",
1)
出力列のラベルはステージ、合計金額、案件数とします。5,000行であれば2秒以内に表示されます。12,000行を超える場合は、QUERYを実行する前に年度でソースデータを絞り込んでください。
週次パイプライントレンド： =ISOWEEKNUM(クローズ日) でISO週番号に変換し、2つ目のQUERYで週ごとにグループ化します。クローズ日が整形されていない場合（よくあります）、上記のDATEVALUEラッパーを使ったヘルパー列で事前クレンジングしてから参照してください。
担当者別加重パイプライン上位5名：
=QUERY(Data!A:H,
"SELECT C, SUM(E*F) WHERE D<>'失注' GROUP BY C ORDER BY SUM(E*F) DESC LIMIT 5",
0)
F列は確度列です。CRMが確度をエクスポートしない場合は、ヘルパー列でステージ加重Configテーブルへのルックアップを行い、そのヘルパー列をQUERYで参照してください。
担当者別停滞案件数：
=COUNTIFS(Data!C:C, B2, Data!H:H, "停滞")
H列は停滞フラグ列です。B列の担当者リストに沿って下方向に展開します。5,000行であればほぼ一瞬で表示されます。
ダッシュボード自体は、スクロールなしで1画面に収まるように設計してください。上部に4つのサマリー数値（加重パイプライン合計、オープン案件数、今月クローズ予定件数、停滞件数）、その下に2つのテーブル。部長がスクロールしているようであれば、指標が多すぎます。
スキーマ変更時の修正コストを減らす
最もよく壊れるのは、数式そのものではなくスキーマです。CRMが新しいステージを追加したり、フィールド名を変更したり、確度をパーセントではなく小数でエクスポートし始めたりすると、ダッシュボードのQUERYが突然0行を返したり、空白のテーブルになったりします。
ModelMonkeyは、スキーマが変わったときに平易な日本語でQUERY数式を書き直すことができます。新しい列名やステージ名の変更を説明するだけで、6つのシートを横断して参照箇所を探し回ることなく、該当箇所の数式をその場で修正します。月曜の朝8時に「提案送付済」が「提案中」に静かに書き換えられ、加重パイプラインが0円になっていたとき--10分で直るか、2時間の原因調査になるかの差がここにあります。ModelMonkeyのプランを選ぶ
よくある質問
Q. Google SheetsのCRM営業パイプラインは何行まで安定して動作しますか？
SUMPRODUCT＋Configテーブル方式であれば8,000行、QUERY方式であれば12,000行程度まで実用的な速度で動作します。それ以上の場合は、年度フィルターで有効案件のみを対象にするか、Google BigQueryと連携することを検討してください。
Q. HubSpotやSalesforceのエクスポートをそのまま数式のソースにしても問題ありませんか？
推奨しません。CRMエクスポートはステージ名・日付形式・金額書式が予告なく変わります。生データシートをバッファとして設け、数式はそこだけを参照する設計にしてください。エクスポートの形式が変わっても修正箇所が1か所で済みます。
Q. 確度をCRMがエクスポートしない場合はどうすればよいですか？
Configシートのステージ加重テーブルから、VLOOKUP・INDEX/MATCHでステージ名に対応する加重値を引くヘルパー列を設けてください。そのヘルパー列をQUERYまたはSUMPRODUCTで参照すれば、確度列がなくても加重パイプラインを計算できます。
Q. 停滞判定の45日という基準は変更できますか？
変更できます。Configシートに「停滞判定日数」セルを追加し、数式の>45の箇所を>Config!$D$2のようなセル参照に置き換えてください。営業サイクルが短い業種（例：インサイドセールス）は30日、長い業種（例：製造業の設備投資案件）は60〜90日が目安です。
Q. 年度（4月始まり）でパイプラインを集計したい場合はどうしますか？
クローズ予定日に対して =YEAR(DATE(YEAR(A2), MONTH(A2)-3, 1)) で日本の年度（4〜3月期）を算出するヘルパー列を追加し、QUERYのWHERE句でその列を参照してください。四半期（Q1〜Q4）も同様のロジックで算出できます。
まとめ
Google Sheetsで機能する営業パイプラインには4つのレイヤーが必要です。防御的にパースされた生データシート、ステージ加重のConfigテーブル、加重パイプライン価値を計算するSUMPRODUCTまたはQUERY数式、そして定例ミーティングで部長が聞く3つの問いに答えるダッシュボードタブです。ここで紹介した数式は、一般的なCRMエクスポートの汚れたデータを処理し、2026年現在のGoogle Sheetsで8,000〜12,000行まで安定して動作し、CRMがステージ名を予告なく変更した場合でも#N/Aではなく0を返すように設計されています。