「既存顧客ルート営業用」テンプレートの使い方
テンプレートのダウンロード
無料でお使いいただけます。会員登録は必要ありません。VBA とGASは使っていないので、マクロが禁止されている環境でもそのまま開けます。Googleスプレッドシートで使う場合は、下のCSVを1つのスプレッドシートにシート名そのままで取り込んでください。
どんなときに使うか
既に取引が始まっている顧客を、定期訪問・定期接触で維持し、取引を広げていく担当者向けのテンプレートです。ルート営業、既存顧客担当のフィールドセールス、カスタマーサクセス寄りの営業企画の方を想定しています。
次のような場面で開きます。
- 週明けに訪問計画を立てるとき:決めた訪問間隔を過ぎている顧客を抽出し、エリアと最寄駅で並べ替えて当日の動線を作る。
- アップセル・クロスセルを仕込む月:未導入の製品(ホワイトスペース)が多い顧客を上に出して、提案商材を決める。
- 四半期の更新フォロー:契約更新日までの残日数が短い順に並べ、更新交渉を前倒しで組む。
他のテンプレートとの使い分けは、「まだ取引が無い相手を開拓する」なら新規開拓テレアポ用や購入リスト取り込み用、「一度取引が切れた・失注した相手を掘り起こす」なら休眠顧客掘り起こし用です。このテンプレートは契約更新日・年間取引額・製品導入状況といった「取引中であること」を前提にした列を持っているので、まだ受注していない見込み客を混ぜると、更新まで日数や未導入数が意味を持たない行になります。
シート構成
ブックには7枚のシートが入っています。
| シート名 | 役割 | 最初に何をするか |
|---|---|---|
| 使い方 | このブックの要点をまとめた案内シート | 最初に一読する。入力はしない |
| 顧客リスト | 主シート。1行=1顧客(取引単位)。訪問計画・ホワイトスペース・契約更新をこの1枚で回す | 取引中の顧客を1社1行で貼り付ける |
| 訪問履歴 | 1行=1回の接触(訪問・電話・メール等)。記録はすべてここに積む | 訪問したら1行追加する。最初は空のままでよい |
| 接触NGリスト | 参照用マスタ。訪問・連絡をしてはいけない先を登録する | ここに自社のお断り情報・訪問NG情報を貼る。空のままだと接触NG判定が一切働かない |
| 選択肢マスター | 各列のプルダウンの元になる選択肢一覧 | 自社の言葉に直したいときだけ編集する |
| 設定 | しきい値と製品名の設定 | いちばん最初にここを埋める |
| 列定義 | 全列(3シート分)の列名・型・必須・入力例・説明の一覧 | ここで列の意味を調べる。本ドキュメントで触れていない列もすべて載っている |
顧客リストは列数が多いため、この文書では肝になる列だけを扱います。個々の列の意味は「列定義」シートを見てください。
参照先を同じブックに同梱している理由
接触NGリストと訪問履歴は、別ファイルに分けず同じブックの中に置いています。これは設計上の判断です。
Excel の COUNTIF / COUNTIFS は、参照先のブックが閉じているとエラーになり再計算できません。接触NG判定のためにNGリストを別ファイルにすると、そのファイルを開いていない限り判定列が壊れます。全員が常に2つのファイルを開いている運用は現場で維持できないため、照合先をすべて同じブック内のシートにしました。
そのため、接触NGリストのシートを削除したり、別ファイルに移したりしないでください。顧客リストの「接触NG判定」列が機能しなくなります。
Googleスプレッドシートで使う場合
CSV版(このページのCSV一式)はシートごとに1ファイルに分かれています。1つのスプレッドシートの中に、同じシート名で5枚すべてを取り込んでください(顧客リスト/訪問履歴/接触NGリスト/選択肢マスター/設定)。シート名が変わると、数式の中の '接触NGリスト'!$E:$E のような参照が解決できず #REF! になります。
取り込みは「ファイル」→「インポート」→「インポート場所:新しいシートを挿入する」で行い、取り込み後にシート名を上記のとおりに直します。
使い始める手順
最初の30分でここまで終わらせれば運用に入れます。
- 設定シートの製品名を埋める(5分)
製品1_名称〜製品5_名称 に自社の製品・サービス名を入れます。自社で5つも扱っていない場合は、使わない番号の製品名を空欄にしてください。未導入数の数式は設定シートの製品名が入っているものだけを数える作りなので、ここを空欄にすれば自動的に数えなくなります。列を非表示にしただけでは除外されません。
- 顧客リストの製品列の見出しを自社製品名に書き換える(3分)
顧客リストの 製品1_導入〜製品5_導入 の見出しセルを、設定シートに入れた製品名に直接書き換えます。数式は列の位置($O2 など)で動いているので、見出しの文字を変えても数式は壊れません。設定シート側の製品名は、見出しと数式をつなぐ対応表として残しておきます。
- しきい値を自社のルールに合わせる(5分)
設定シートの 訪問間隔_既定(日)、更新アラート_日数、最終接触_放置アラート(日)、未導入数_提案推奨しきい値、年間取引額_重点顧客しきい値(円) を調整します。何がどう変わるかは後述の「設定シートで調整できること」を見てください。
- 接触NGリストに自社のお断り情報を貼る(5分)
訪問お断り・配信停止の申出があった先を登録します。企業名 と NG区分 が必須です。NG区分はチャネル別(訪問NG/架電NG/メールNG/DM・郵送NG/全接触NG/一時停止)に分かれているので、「架電は不可だがメールは可」といった状態もそのまま登録できます。ここが空のままだと、顧客リストの接触NG判定は何も出ません。
- 顧客リストに顧客を貼り付ける(10分)
1社1行です。顧客ID と 企業名 が必須です。顧客IDは訪問履歴と紐づける唯一のキーなので、社名が変わっても変えずに使い続けてください。法人番号を入れる場合は、セルの書式を「文字列」にしてから13桁で入力します(数値で入れると先頭ゼロが落ちます)。
- 製品の導入状況を埋める(5分)
導入済みの製品に ○ を入れます。導入していないものは空白のままにしてください。 空白=提案余地として青く表示される設計です。「検討中」は ○ にせず、提案候補商材 列に書きます。
- 訪問間隔ルール・前回訪問日・契約更新日を入れる
訪問間隔ルール(日数) は顧客ごとの訪問サイクルです(重点顧客は30、小口は120など)。空欄の行は設定シートの既定値が使われます。ここまで入れれば、次回訪問予定日・訪問間隔超過・更新まで日数・未導入数が自動で埋まります。
- 当日の訪問先を決める
顧客リストを 訪問間隔超過 列でフィルタし、訪問期限超過 と 今週中に訪問 の行だけを表示します。そのうえで エリア → 最寄駅 → アクセス時間(分) の順に並べ替えると、移動しやすい順番になります。
- 訪問したら記録する
訪問履歴に1行追加し(訪問日・顧客ID・接触手段・面談結果・温度感・内容メモ)、顧客リストの 前回訪問日 と 最終接触日 も同じ日付に手で更新します。この2つは自動では入りません。顧客リストの 訪問回数 は自動で増えます。
自動で動く列
次の列は数式が入っています。手入力すると数式が消えて、その行だけ自動計算が止まります。 誤って上書きしたときは、同じ列の正常な行のセルをコピーして貼り付ければ復旧します。
xlsx版は500行目まで、CSV版は200行目まで数式が入っています。それより下の行を使うときは、最終行の計算列をまとめて下方向にコピーしてください。
顧客リスト
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 会社名_照合キー | 企業名から「株式会社」「(株)」「有限会社」などの法人格と空白を取り除き、全角英数を半角に揃えた突合専用の名前 | 直接は読まない。接触NG照合と重複チェックがこの列を見る |
| 未導入数 | 設定シートで製品名が入っている製品のうち、導入状況が空欄の数 | 降順に並べ替えると提案余地の大きい顧客が上に来る |
| 訪問経過日数 | 前回訪問日から今日まで何日経ったか | 降順に並べ替えると放置している顧客が上に来る |
| 次回訪問予定日 | 前回訪問日+訪問間隔ルール(空欄なら設定シートの既定値) | 昇順に並べ替えると訪問順になる |
| 訪問間隔超過 | 予定日を過ぎた行に「訪問期限超過」、7日以内の行に「今週中に訪問」 | この列でフィルタすれば当週の訪問先が決まる |
| 更新まで日数 | 契約更新日までの残り日数(マイナスは更新日超過) | 昇順に並べ替えると更新フォローの順になる |
| 訪問回数 | 訪問履歴シートにあるこの顧客IDの行数 | 0のまま動かない顧客は手付かずの印 |
| 接触NG判定 | 接触NGリストに有効な登録がある行に警告を出す | 「全接触NG」「訪問NG」が出た行には訪問・連絡をしない |
| データ警告 | 法人番号が13桁でない行と、社名が他の行と重なっている行を知らせる | 自動削除はしない。顧客IDを見て人が判断する |
訪問履歴
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 経過日数 | その接触から今日まで何日経ったか | 直近の履歴を拾うときの目安 |
| 顧客ID照合 | 顧客リストに無い顧客IDを書いた行を知らせる | 空欄なら正常。何か出たら顧客IDの打ち間違い |
接触NGリスト
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 会社名_照合キー | 顧客リストと同じ作り方で正規化した突合用の名前 | 顧客リストの接触NG判定がこの列を見る |
| 有効判定 | 「一時停止」で解除予定日を過ぎた行に「解除予定日経過(要確認)」を出す | これが出たら本人に確認してから行を外す |
主な数式
いずれも2行目のものです。列記号は、顧客リストでは $A=顧客ID、$B=法人番号、$C=企業名、$O〜$S=製品1〜5_導入、$U=会社名_照合キー、$V=契約更新日、$W=訪問間隔ルール(日数)、$X=前回訪問日、$AG=次回訪問予定日に対応します。
会社名_照合キー — 企業名から法人格の表記と空白を取り除き、全角英数を半角に揃えて突合用の名前を作ります。
=IF($C2="","",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($C2),"株式会社",""),"(株)",""),"(株)",""),"有限会社",""),"(有)","")," ","")))
次回訪問予定日 — 前回訪問日に訪問間隔を足します。訪問間隔ルールが空欄の行は、設定シートの既定値(設定!$B$2)を使います。
=IF($X2="","",$X2+IF($W2="",設定!$B$2,$W2))
訪問間隔超過 — 次回訪問予定日が今日より前なら「訪問期限超過」、7日以内なら「今週中に訪問」を出します。
=IF($AG2="","",IF($AG2<TODAY(),"訪問期限超過",IF($AG2-TODAY()<=7,"今週中に訪問","")))
未導入数 — 設定シートの製品1〜5の名称(設定!$B$7〜$B$11)が入っているものだけを対象に、導入状況が空欄の製品を数えます。
=IF($C2="","",IF(AND(設定!$B$7<>"",$O2=""),1,0)+IF(AND(設定!$B$8<>"",$P2=""),1,0)+IF(AND(設定!$B$9<>"",$Q2=""),1,0)+IF(AND(設定!$B$10<>"",$R2=""),1,0)+IF(AND(設定!$B$11<>"",$S2=""),1,0))
接触NG判定 — 接触NGリストの中で有効な登録を照合キーで探し、区分が「全接触NG」か「訪問NG」かを見分けて表示します。
=IF($U2="","",IF(COUNTIFS('接触NGリスト'!$E:$E,$U2,'接触NGリスト'!$J:$J,"有効")=0,"",IF(COUNTIFS('接触NGリスト'!$E:$E,$U2,'接触NGリスト'!$C:$C,"全接触NG",'接触NGリスト'!$J:$J,"有効")>0,"全接触NG",IF(COUNTIFS('接触NGリスト'!$E:$E,$U2,'接触NGリスト'!$C:$C,"訪問NG",'接触NGリスト'!$J:$J,"有効")>0,"訪問NG","NG登録あり(区分要確認)"))))
訪問回数 — 訪問履歴シートに積まれた、この顧客IDの行数を数えます。
=IF($A2="","",COUNTIF('訪問履歴'!$B:$B,$A2))
色が付いたときの意味
条件付き書式はすべて「カスタム数式」で書いてあります。Excelの「重複する値」などのプリセットはGoogleスプレッドシートに存在しないため使っていません。
顧客リスト
| 色 | 範囲 | 出る条件 | 何をすべきか |
|---|---|---|---|
| 濃い赤 | 行全体 | 接触NG判定に何か入っている | 訪問・連絡の対象から外す。区分が「NG登録あり(区分要確認)」なら接触NGリストで区分を確認する |
| 赤 | 行全体 | 更新まで日数がマイナス(更新日超過) | 更新手続きが終わっていない。最優先で連絡する |
| オレンジ | 行全体 | 次回訪問予定日が今日より前 | 決めた訪問間隔を過ぎている。今週の訪問候補に入れる |
| 黄 | 行全体 | 更新まで日数が0以上かつ設定の「更新アラート_日数」以内 | 更新交渉を前倒しで始める |
| オレンジ | 最終接触日のセル | 最終接触日から設定の「最終接触_放置アラート(日)」以上経過 | 訪問に限らず連絡が途切れている。電話かメールを入れる |
| 青 | 未導入数のセル | 未導入数が設定のしきい値以上 | アップセル・クロスセルの候補。提案候補商材を埋める |
| 青 | 製品1〜5_導入のセル | その製品が空欄(未導入) | ホワイトスペース。青いセルが提案できる製品 |
| 緑 | 年間取引額(円)のセル | 年間取引額が設定のしきい値以上 | 重点顧客。訪問頻度を落とさない |
| 赤 | データ警告のセル | 法人番号の桁数エラー、または社名の重複 | 後述の「重複が出たときの扱い」を参照 |
行全体の色は上から順に評価され、先に当たった1つが適用されます。 順番は「接触NG(濃い赤)→ 更新日超過(赤)→ 訪問期限超過(オレンジ)→ 更新が近い(黄)」です。この順序を入れ替えないでください。接触NGの行が訪問アラートのオレンジで塗られると、行ってはいけない先に訪問してしまいます。
条件付き書式の確認・編集は、Excelでは「ホーム」→「条件付き書式」→「ルールの管理」(新規作成時は『数式を使用して、書式設定するセルを決定』)、Googleスプレッドシートでは「表示形式」→「条件付き書式」(『カスタム数式』)です。
訪問履歴
| 色 | 範囲 | 出る条件 | 何をすべきか |
|---|---|---|---|
| 赤 | 行全体 | 次回アクション期日が今日より前 | 約束したアクションが遅れている。すぐ処理する |
| 緑 | 行全体 | 面談結果が「受注」 | 成果の記録。読み飛ばしてよい |
| グレー | 行全体 | 面談結果が「失注」 | 読み飛ばし用。行は消さない |
| オレンジ | 顧客ID照合のセル | 顧客リストに無い顧客ID | 打ち間違い。直さないと顧客リストの訪問回数に反映されない |
接触NGリスト
| 色 | 範囲 | 出る条件 | 何をすべきか |
|---|---|---|---|
| 濃い赤 | 行全体 | NG区分が「全接触NG」 | どのチャネルでも接触しない |
| 黄 | 行全体 | 解除予定日を過ぎている | 一時停止のまま放置されている。本人に確認してから扱いを決める |
| オレンジ | NG区分のセル | NG区分が「訪問NG」 | 訪問は不可だが他の手段は可。読み違いに注意 |
設定シートで調整できること
設定シートのB列の値を変えると、計算と色の出方が変わります。A列の項目名は書き換えないでください(数式が行位置で参照しています)。
| 項目 | 初期値 | 変えると何が変わるか |
|---|---|---|
| 訪問間隔_既定(日) | 60 | 顧客リストの 訪問間隔ルール(日数) が空欄の行の次回訪問予定日。自社の標準ルート訪問サイクルに合わせる |
| 更新アラート_日数 | 90 | 更新まで日数がこの日数以内の行が黄色になる。更新交渉の着手タイミングに合わせる |
| 最終接触_放置アラート(日) | 90 | 最終接触日のセルがオレンジになるまでの日数 |
| 未導入数_提案推奨しきい値 | 2 | 未導入数のセルが青くなる基準。扱う製品が2〜3種類なら1に下げる |
| 年間取引額_重点顧客しきい値(円) | 3000000 | 年間取引額のセルが緑になる基準 |
| 製品1_名称〜製品5_名称 | 自社製品1〜5 | 未導入数の計算対象。ここが空欄の製品は数えない。 使わない製品番号は必ず空欄にする |
製品名の扱いだけ補足します。列見出しに =設定!B7 のような数式を置く作りにはしていません。見出しが数式になるとフィルタ・並べ替え・条件付き書式の適用範囲が扱いづらくなるためです。そのため、見出しは手で書き換え、設定シートの製品名は「数式が数える対象かどうか」を決めるスイッチとして持たせています。見出しの文字を変えても数式は列の位置で動くので壊れません。
重複が出たときの扱い
顧客リストは1社1行が原則ですが、このテンプレートは重複を自動では削除しません。 データ警告 列が「同一社名の行が複数あり(顧客ID要確認)」と知らせるだけで、判断は人に残しています。
理由は、ルート営業では同じ企業の複数部署・複数拠点と別契約で取引していることがあり、それは正しい重複(消してはいけない行)だからです。機械的に消すと契約が1本消えます。
警告が出たときの手順です。
- 警告が出た行の
顧客IDを見る。 - 顧客IDが同じなら、本当の二重登録です。訪問履歴の件数(
訪問回数列)が多い方の行に情報を寄せ、古い行を削除します。削除する前に必ずシートを複製してバックアップを取ってください。 - 顧客IDが違うなら、別取引として両方残します。
部署名を埋めて区別できるようにしてください。
他のシートの重複については次のとおりです。
- 訪問履歴の重複は正常な状態です。 同じ顧客に何度も行くのが仕事なので、重複チェックは一切かけていません。
- 接触NGリストの重複も正常です。 NG区分をチャネルごとに1行ずつ登録する設計なので、同じ企業が「架電NG」「DM・郵送NG」で2行あるのは正しい状態です。
- NG・配信停止の記録は、取引が終わっても消さないでください。 別ファイルから同じ企業を再登録したときに、再訪・再送を防ぐ役目があります。
運用上の注意
計算列を手入力しない。 未導入数・訪問経過日数・次回訪問予定日・訪問間隔超過・更新まで日数・訪問回数・接触NG判定・データ警告・会社名_照合キーは数式です。値を直接打つと、その行だけ自動計算が止まり、しかも見た目では分かりません。訪問漏れの原因になります。
法人番号は文字列で入れる。 数値として入力すると先頭のゼロが落ち、データ警告 列に「法人番号桁数エラー」が出ます。貼り付け前にセルの書式を文字列にしてください。
前回訪問日と最終接触日は手で更新する。 訪問履歴に1行足しても、顧客リストのこの2つの日付は自動では動きません。訪問回数 だけが自動で増えます。前回訪問日が古いままだと次回訪問予定日がずれ、訪問済みの顧客がオレンジのままになります。訪問履歴を1行書いたら必ず同時に更新する、を習慣にしてください。
温度感と面談結果は顧客リストには無い。 どちらも「その訪問時点の評価」であって顧客の恒久的な属性ではないため、訪問履歴シート側に置いています。顧客リストに置くと訪問のたびに上書きされ、推移が消えてしまいます。現在の温度感を見たいときは、訪問履歴を顧客IDでフィルタして最新の行を見てください。最新値を顧客リストに自動で引いてくる数式は入れていません(この処理には MAXIFS や XLOOKUP が必要で、Excel 2019以前やGoogleスプレッドシートとの互換性のために使わない方針にしたためです)。
使わない製品列は設定シートの製品名を空欄にする。 列を非表示にしただけでは未導入数から除外されません。見た目を整えたい場合は、設定シートの製品名を空欄にしたうえで列を非表示にするか、列ごと削除してください(削除する場合は、未導入数の数式が参照する列がずれるため、数式を確認してください)。
シートの削除・移動をしない。 接触NG判定と訪問回数は、同じブック内の接触NGリスト・訪問履歴シートを参照しています。シート名を変えたり別ファイルに分けたりすると、参照が切れて判定が出なくなります。
条件付き書式の順番を変えない。 前述のとおり、接触NGの濃い赤が最優先です。
VBA・GAS は一切使っていません。 Excelとスプレッドシートの標準関数だけで組んであります。使っている関数は IF / AND / OR / IFERROR / COUNTIF / COUNTIFS / SUBSTITUTE / TRIM / ASC / LEN / TODAY などで、XLOOKUP / UNIQUE / FILTER / IFS / DATEDIF は意図的に使っていません。互換性のためと、DATEDIF については Microsoft 自身が誤った結果を返す場合があると案内しているためです。
書式設定上の注意。 日付列には日付として入力してください(文字列の「2026/9/18」だと TODAY()- の計算がエラーになります)。並べ替えをするときは、必ず見出し行を含めた表全体を選択してから行ってください。一部の列だけを選んで並べ替えると、行の対応が崩れてデータが別の会社と混ざります。
法令まわりの位置づけ
以下は調査に基づく実務上の整理であり、法的助言ではありません。最終判断は個人情報保護委員会のガイドラインおよび専門家にご確認ください。
メール配信可否列は特定電子メール法を踏まえた運用列です。既存の取引先は例外類型(取引関係にある者)に当たるのが通常と整理されていますが、本人から配信停止の申出があったら「停止」にして以後は送らない、という運用のための列として置いています。- 「断られたら再勧誘しない」ルールの根拠としてよく挙がる特定商取引法の再勧誘禁止は、電話勧誘販売=主にBtoC取引を対象としたもので、純粋なBtoB営業にそのまま適用されるわけではありません。接触NGリストを持つことは法令上の義務ではなく、運用上の定石として捉えてください。担当者が替わったときの再訪を防ぐ実利があります。
データ取得日データ取得元最終更新日は、保有データの棚卸しと、本人からの開示・利用停止の請求が来たときに「どのファイルのどの行にその人がいるか」を特定するための列です。鮮度管理の目安は、重要項目で3か月、その他で半年ごとの見直しです。
最終更新: / ListGoo 編集部