「購入した営業リストCSV用(取り込み・重複チェック)」の使い方
テンプレートのダウンロード
無料でお使いいただけます。会員登録は必要ありません。VBA とGASは使っていないので、マクロが禁止されている環境でもそのまま開けます。Googleスプレッドシートで使う場合は、下のCSVを1つのスプレッドシートにシート名そのままで取り込んでください。
どんなときに使うか
リスト会社から購入したCSV、展示会主催者から納品された企業リスト、外注先が作成した企業リストなど、外部から受け取った企業データを自社リストに取り込む前の「検品台」として使うブックです。想定ユーザーは、リスト購入の意思決定や発注を行う営業企画・リスト管理者です。
開くタイミングは「CSVが納品された日」です。納品されたファイルをそのまま営業に配る前に、このブックへ一度通して、
- 既に自社が取引している企業が混ざっていないか
- 過去に「電話をしてくるな」と言われた企業が混ざっていないか
- 購入リスト自体の中で同じ会社が二重に入っていないか
- そもそもこのロットは、出所を確認したうえで受け取ったものか
を機械的に判定します。判定を通った行だけを企業マスタへ移し、そこから先の日々の営業活動は他のテンプレート(新規開拓テレアポ用、インバウンドリード育成用など)で回してください。このブックは日々の活動記録をつける場所ではなく、取り込み作業のたびに開いて使い捨てに近い形で運用するブックです。アタックリストとして毎日触るのは別テンプレートの役目になります。
なお、本テンプレートの法令関連の列構成は調査に基づく実務上の整理であり、法的助言ではありません。最終判断は個人情報保護委員会のガイドラインおよび専門家にご確認ください。
シート構成
| シート名 | 役割 | 最初に何をするか |
|---|---|---|
| 使い方 | 作業手順の要約 | 読むだけ。印刷して手元に置いても構いません |
| 取り込みシート | 入力(中心シート) | 購入CSVを貼り付ける。左半分が原文列、右半分が照合キーと判定列 |
| 企業マスタ | 参照マスタ | ここに自社の既存顧客・既存見込み企業を貼る。空のままだと「既存あり」が一件も出ません |
| お断りリスト | 参照マスタ | ここに自社の架電拒否・配信停止・クレーム先を貼る。空のままだとNG照合が効きません |
| 受領ロット台帳 | 台帳 | 受領したロット(契約単位)を1行登録する。ここが「可」にならないと取り込みシートが通りません |
| フォーム送信管理 | 台帳 | 問い合わせフォーム経由で接触する場合のみ使う。最初は空で構いません |
| 選択肢マスター | 参照 | 入力規則(ドロップダウン)の元データ。触る必要はありません |
| 設定 | 設定 | しきい値を自社の運用に合わせる |
| 列定義 | 参照 | 全列の説明がここにあります。本ページで触れていない列はここで確認してください |
参照先を同じブックに同梱している理由
企業マスタとお断りリストは「別ファイルで管理しているのに」と思われるかもしれませんが、同じブック内に置いてあります。これはExcelが、閉じたブックに対して COUNTIF / COUNTIFS を計算できないためです。照合先を別ファイルにすると、そのファイルを開いている間しか判定が出ず、閉じた瞬間に #VALUE! になります。判定が「ファイルを開いているかどうか」で変わる仕組みは現場で必ず事故を起こすため、照合先(企業マスタ・お断りリスト)と記録(受領ロット台帳)はすべて同じブックに入れてあります。
運用としては、マスタ管理用の原本を別に持っておき、取り込み作業のたびにその時点の最新版をこのブックへ貼り直す形になります。
使い始める手順
最初の30分で、ここまでやれば判定が動き出します。
- 〈設定〉シートを確認する。 5項目のしきい値が入っています。記録保存_月数(既定36)と契約終了後_削除猶予日数(既定30)は、自社の契約条項と法務の判断に合わせて上書きしてください。
- 〈企業マスタ〉へ自社の既存リストを貼る。 貼るのは企業名(正式名称)・法人番号・代表電話・メールアドレスの4列が中心です。照合キー列(会社名_照合キー以降)は数式なので触らないでください。貼り付け後、「マスタ内重複フラグ」が橙色で出ている行があれば、取り込み作業を始める前に統合して1行に寄せます。照合先が二重だと判定結果が信用できなくなります。
- 〈お断りリスト〉へ過去のNG先を登録する。 企業名・NG区分・NG登録日が必須です。NG区分はチャネル別(架電NG/メールNG/DM・郵送NG/訪問NG/全接触NG/一時停止)なので、「電話はNGだがメールは可」という状態をそのまま持てます。複数チャネルがNGなら行を分けて登録してください。全社のメールをブロックしたい場合は、メールアドレス欄に
@example.co.jpのように@以降だけを入れればドメイン照合が効きます。 - 〈受領ロット台帳〉へ、今回受け取ったロットを1行登録する。CSVを貼り付ける前に、です。ロットID(例: LOT-2026-003)・受領年月日・提供元の氏名・名称・提供元の住所・取得の経緯・提供根拠を埋めます。最終列の「ロット取り込み可否」が「可」になることを確認してください。取得の経緯や提供根拠に「不明」を選ぶと「不可」になり、このロット由来の行は全行が取り込み不可になります。
- 〈取り込みシート〉の法人番号列(D列)を、貼り付ける前に文字列書式にする。Excelは「セルの書式設定 → 表示形式 → 文字列」、Googleスプレッドシートは「表示形式 → 数字 → 書式なしテキスト」です。この一手間を飛ばすと先頭ゼロが落ちます。
- 購入CSVの中身を〈取り込みシート〉の2行目以降へ「値のみ」で貼り付ける。Excelは「形式を選択して貼り付け → 値」(Ctrl+Alt+V → V)、Googleスプレッドシートは「編集 → 特殊貼り付け → 値のみ貼り付け」(Ctrl+Shift+V)です。通常の貼り付けをすると、右側の照合キー列・判定列の数式を上書きして壊します。貼る対象はA〜P列の範囲だけです。
- A列「ロットID」に、手順4で登録したロットIDを全行入れる。ここが空のままだと、どれだけ内容が正しくても取り込み可否判定は通りません。
- 数式を最終行までコピーする。数式はデータ500行分(2行目から501行目まで)入っています。501行目以降に貼った場合は、Q列〜U列(照合キー列)と Z列〜AI列(判定列)の2か所を Ctrl キーを押しながら選択し、下方向にコピーしてください。この2つの間にある V〜Y列(アプローチ可否・ステータス・担当営業・備考)は手入力列なので、まとめて選択すると入力内容が消えます。
- 色を見る前に件数を数える。空いているセルで
=COUNTIF(取り込みシート!AI:AI,"取り込み可")を計算し、取り込み可が何件か、全体の何割かを把握します。いきなり色を眺めると、判定が間違っているのか現実が厳しいのかが区別できません。 - 数行だけ手で検算する。「既存あり」が出た行を2〜3件選び、本当に企業マスタにいるかを目視で確認します。ここで照合キーの効きが確認できたら、残りは信用して構いません。
- バックアップを取ってから抽出する。シート全体を別シートへコピーしたうえで、AI列「取り込み可否判定」でフィルタし(Excel「データ → フィルター」/Googleスプレッドシート「データ → フィルタを作成」)、「取り込み可」だけを〈企業マスタ〉へ追記します。追記した行はW列「ステータス」を「マスタ登録済」にしてください。
- メールを送る前にAH列を見る。「メール送信可否判定」が「送信不可(根拠なし)」の行にはメールを送りません。詳細は後述します。
自動で動く列
これらの列はすべて数式です。手入力すると、その行だけ判定が止まります。値を書き換えたくなったときは、数式のある判定列ではなく、元になっている原文列(企業名(原文)、法人番号、代表電話(原文)、メールアドレス(原文))を直してください。
列数が多いため、ここでは肝になる列だけを扱います。全35列の説明は〈列定義〉シートにあります。
| 列名 | 何を自動計算するか | どう使うか |
|---|---|---|
| 会社名_照合キー(Q列) | 企業名から「株式会社」「(株)」「有限会社」「(有)」とスペースを除き、全角英数を半角に揃える | 重複判定はすべてこの列に対して行われます。表示用の企業名ではなくこの列を見てください |
| 法人番号_照合キー(R列) | 法人番号から =・"・'・前後の空白を除き、13桁の数字だけを取り出す | 購入リスト特有の書式を吸収する列。照合はこの列で行われます |
| 電話_照合キー(S列) | 半角ハイフン・全角ハイフン・長音記号を除去した電話番号 | 社名の表記が揺れていても、電話番号で同一企業を捕まえられます |
| ドメイン_照合キー(U列) | メールアドレスの@以降を小文字・半角で取り出す | 企業単位のメールNG(全社ブロック)照合に使います |
| 社内重複フラグ(Z列) | 取り込みシート内での重複。1件目と2件目以降を区別して表示 | 「重複(2件目以降)」の行だけを削除対象にできます |
| 既存重複フラグ(AA列) | 企業マスタに既にいるか。会社名キー・法人番号・電話キーの3通りで照合 | 「既存あり」の行は企業マスタへ追記しません |
| NG照合フラグ(AB列) | お断りリストに当たるか。会社名・電話・メール・ドメインの4キーで照合 | 「NG」が出た行は取り込みません |
| NG区分(お断りリスト)(AC列) | どのチャネルがNGかを全件拾って「架電NG/メールNG」のように連結 | 架電だけNGでメールは可、という状態を潰さずに見られます |
| ロット照合エラーフラグ(AG列) | ロットIDが受領ロット台帳にあり、かつ「可」になっているか | 出所不明のリストを運用で止める仕組みです |
| メール送信可否判定(AH列) | オプトイン根拠・配信可否・NG照合から、メールを送ってよいかを判定 | 取り込み可でもメールは送れない、という状態を別列で示します |
| 取り込み可否判定(AI列) | 上記の判定をすべて1列に集約した総合判定 | 「取り込み可」と出た行だけを企業マスタへ移します |
主要な数式
会社名_照合キー(Q列)。法人格とスペースを除去し、全角英数を半角に揃えて突合専用の文字列を作ります。
=IF($C2="","",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC($C2),"株式会社",""),"(株)",""),"(株)",""),"有限会社",""),"(有)","")," ","")))
法人番号_照合キー(R列)。購入リストでよくある ="1234567890123" という書式から、イコール・引用符・アポストロフィ・前後の空白を取り除き、13桁だけを残します。
=IFERROR(IF($D2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(ASC($D2&"")),"=",""),CHAR(34),""),"'","")),"")
既存重複フラグ(AA列)。会社名キー → 法人番号 → 電話キーの順に企業マスタを探し、どれか1つでも当たれば「既存あり」にします。
=IFERROR(IF($Q2="","",IF(COUNTIF('企業マスタ'!$K:$K,$Q2)>0,"既存あり",IF(AND($R2<>"",COUNTIF('企業マスタ'!$L:$L,$R2)>0),"既存あり",IF(AND($S2<>"",COUNTIF('企業マスタ'!$M:$M,$S2)>0),"既存あり","")))),"")
ロット照合エラーフラグ(AG列)。ロットIDが入っているか、台帳に登録があるか、台帳側の「ロット取り込み可否」が「可」かを順に確認します。
=IF(COUNTA($A2,$C2,$E2,$N2)=0,"",IF($A2="","ロットID未入力",IF(COUNTIF('受領ロット台帳'!$A:$A,$A2)=0,"ロット台帳に未登録",IF(COUNTIFS('受領ロット台帳'!$A:$A,$A2,'受領ロット台帳'!$V:$V,"可")=0,"ロット要件未充足",""))))
メール送信可否判定(AH列)。オプトイン根拠が無い・配信停止・NG該当のいずれかに当たれば、理由付きで「送信不可」と出します。
=IF($T2="","",IF(OR($P2="",$P2="根拠なし(送信不可)"),"送信不可(根拠なし)",IF($O2="停止","送信不可(配信停止)",IF(LEFT($AB2,2)="NG","送信不可(NG)","送信可"))))
色が付いたときの意味
条件付き書式はすべて「カスタム数式」で書いてあります(Excelでは「数式を使用して、書式設定するセルを決定」、Googleスプレッドシートでは「カスタム数式」)。Excelの「重複する値」プリセットはGoogleスプレッドシートに存在しないため使っていません。
ルールは重い順に並べてあり、先に書いたルールが優先されます。NGの行が「取り込み不可」の赤に上書きされて見えなくなることはありません。
取り込みシート
| 色 | どんな条件で付くか | 何をすべきか |
|---|---|---|
| 濃い赤(行全体) | NG照合フラグが「NG」または「NG判定エラー」 | 取り込まない。NG区分(AC列)でどのチャネルがNGかを確認する |
| 黄(行全体) | 既存重複フラグが「既存あり」 | 企業マスタへ追記しない。購入リストにしか無い新しい電話番号・担当者だけを人が判断してマスタへ補記する |
| オレンジ(行全体) | 社内重複フラグが「重複(2件目以降)」 | この行は削除してよい。1件目(色が付かない行)は残す |
| 赤(行全体) | 取り込み可否判定が空欄でなく、かつ「取り込み可」でもない | AI列の文字列に理由が「/」区切りで出ているので、理由ごとに対処する |
| 緑(行全体) | 取り込み可否判定が「取り込み可」 | そのまま企業マスタへ移してよい |
| 濃い赤(AH列のセルのみ) | メール送信可否判定が「送信不可」で始まる | この行にメールを送らない。行全体が緑でもここが赤なら送れない |
| 濃い赤(D列のセルのみ) | 法人番号のセル自体が計算エラー | ="…" が数式として解釈されています。D列を文字列書式にしてから貼り直してください |
受領ロット台帳
| 色 | 条件 | 対応 |
|---|---|---|
| 赤 | ロット取り込み可否が「不可」 | 提供元の名称・住所・取得の経緯・提供根拠のどれかが空か「不明」。このロットIDの行は全行が取り込みシートで弾かれます |
| 濃い赤 | 記録保存期限または削除期限を過ぎた | 棚卸しの最優先対象 |
| 黄 | 期限まで設定日数以内 | 予告。削除・記録更新の段取りを始める |
| グレー | 削除実施日が入っている | 削除済み。記録だけ残っている状態です |
お断りリスト・企業マスタ
| 色 | 条件 | 対応 |
|---|---|---|
| 濃い赤(お断りリスト) | NG有効判定が「有効」 | 現在効いているお断り登録。誤って消さないこと |
| グレー(お断りリスト) | NG有効判定が「解除済」 | 一時停止の期間が明けた行。削除せず履歴として残す |
| 黄(お断りリスト・外部ツール同期日) | 有効なNGなのに同期日が空 | 営業代行やMAツールへ停止状態を反映し、日付を入れる |
| オレンジ(企業マスタ) | マスタ内重複フラグが「マスタ内重複」 | 取り込み作業の前に統合する |
| グレー(企業マスタ) | 鮮度アラートが「情報が古い(要更新)」 | 設定の鮮度確認_日数を超えた行。棚卸し対象 |
設定シートで調整できること
| 項目 | 既定値 | 変えると何が変わるか |
|---|---|---|
| 記録保存_月数 | 36 | 受領ロット台帳の「記録保存期限」。受領年月日にEDATEで加算されます。記録方式が「契約書等で代替(1年)」の行だけは、この値に関わらず12か月で計算されます |
| 保存期限アラート_日数 | 30 | 記録保存期限・削除期限の何日前から黄色の「間近」を出すか |
| 契約終了後_削除猶予日数 | 30 | 受領ロット台帳の「契約終了時の削除期限」=契約終了日+この日数。契約条項に合わせてください |
| 鮮度確認_日数 | 180 | 企業マスタの「鮮度アラート」が出るまでの日数。重要項目は90日に縮めても構いません |
| フォーム再送可能_日数 | 90 | フォーム送信管理の「再送可能日」=最終送信日+この日数 |
記録保存_月数は法令上の保存期間に直結する値です。変更する際は法務にご確認ください。
重複が出たときの扱い
このテンプレートの重複は、原則として「取り込まない」ための判定です。ただし、どの重複かによって行を消すか残すかが変わります。
1. 取り込みシート内の重複(社内重複フラグ・橙)— 2件目以降は消してよい
1件目には何も表示されず、2件目以降だけが「重複(2件目以降)」になります。橙色の行を削除すれば、1社1行に揃います。削除する前に必ずシート全体を別シートへコピーしてバックアップを取ってください。「重複の削除」機能を使う場合(Excel「データ → 重複の削除」/Googleスプレッドシート「データ → データクリーンアップ → 重複を削除」)、判定に使う列は必ず「会社名_照合キー」を選びます。表示用の「企業名(原文)」を選ぶと、「株式会社○○」と「(株)○○」が別物として残ります。
2. 企業マスタとの重複(既存重複フラグ・黄)— 消さずに「除外」にする
取り込みシート側の行は企業マスタへ追記しません。マスタ側を正とし、購入リストにしか無い新しい電話番号や担当者名があれば、人が判断してマスタへ補記します。取り込みシートの行自体は履歴として残し、W列「ステータス」を「除外」にしてください。「この企業は今回のロットにも入っていたが、既存なので取り込まなかった」という事実が残ります。
3. お断りリストとの一致(NG照合フラグ・濃い赤)— 取り込まない。お断りリストの行は絶対に消さない
お断りリストの行を消すと、別のファイルから同じ企業が再登録され、再架電・再送信してしまいます。一時停止は解除予定日を過ぎると自動的に「解除済」になりますが、その行も履歴として残してください。
4. 受領ロット台帳・フォーム送信管理 — 行の削除は行わない
履歴台帳です。同じ提供元から2回リストを買えば、同じ会社の行が2行並びますが、それは重複ではなく別ロットです。受領ごとに別のロットIDで積み上げてください。
5. 企業マスタ自身の重複(マスタ内重複フラグ・橙)— 取り込み前に統合する
照合先が二重だと、取り込み判定そのものが信用できなくなります。手順2で必ず潰してください。
運用上の注意
判定列を上書きしないこと。現場で最も多い壊し方は、購入CSVを通常の貼り付けでA列からそのまま貼り、Q列以降の数式を消してしまうことです。必ず「値のみ貼り付け」で、A〜P列の範囲に貼ってください。数式を消してしまった場合は、無事な行の数式をコピーして下方向に貼れば復旧できます。
法人番号の ="…" 書式に注意すること。購入リストの法人番号は、Excelで先頭ゼロが落ちないようにするため ="1234567890123" という書式で入っていることがあります。実際に検証した購入リストでは、300社すべてがこの書式でした。これをそのまま貼ると、Excelが数式として解釈してセルが #VALUE! になります。D列を文字列書式にしてから貼るのが正解ですが、万一そのまま貼ってしまっても、D列のセルが濃い赤で塗られるので気づけます。判定側は原文のD列ではなくR列「法人番号_照合キー」を見ているため、13桁さえ取り出せていれば照合は成立します。
照合キーは万能ではありません。除去しているのは「株式会社」「(株)」「(株)」「有限会社」「(有)」とスペースだけです。合同会社・一般社団法人などの法人格、支店名や事業部名の付いた表記は別物として扱われます。重要なロットでは、法人番号の付いている行を優先して照合し、社名だけの行は目視で確認してください。照合キーの信頼度は、法人番号 → ドメイン → 電話番号 → 社名の順です。電話番号のハイフン除去は断定された業界標準ではなく、推奨する前処理として採用しています。
ドメイン_照合キーの #VALUE! は仕様です。メールアドレスに@が含まれていない行では、この列がエラーになります。これは「原文を直すべき行」を可視化するために残してあります。下流のNG照合フラグ側はIFERRORで包んであるので、判定全体が壊れることはなく、「NG判定エラー」と表示されて赤く塗られます。原文のメールアドレスを直せば消えます。
「取り込み可」と「メールを送ってよい」は別物です。取り込み可否判定にオプトイン根拠は含めていません。特定電子メール法上メールを送れない相手でも、架電やDMは可能な場合があるためです。購入リスト由来のアドレスは、多くがオプトイン根拠なしで「送信不可(根拠なし)」になる想定です。行全体が緑でも、AH列が赤ければメールは送れません。
受領ロット台帳が取り込みのゲートになっています。ロットIDを入れ忘れた行、台帳に登録していないロットの行、取得の経緯や提供根拠に「不明」を選んだロットの行は、内容がどれだけ正しくても取り込み不可になります。これは出所不明のリストを運用で止めるための仕組みです。急いでいるときに「とりあえず取り込む」をできなくしているので、先に台帳を1行作ってください。
空行は塗られません。手入力列4つ(ロットID/企業名(原文)/代表電話(原文)/メールアドレス(原文))がすべて空の行は、判定列が空文字を返します。500行分の数式が入っていても、使っていない行が赤く染まることはありません。
電話もメールも無い行は弾かれます。必須項目エラーフラグは、企業名が空、取り込み日が空、または電話とメールの両方が空の行を「必須項目未入力」にします。連絡手段が1つも無い行は、取り込んでも営業できないためです。購入リストによっては数件出ます。
日付が文字列として貼り付いた行に注意すること。取り込み日が文字列になっていると日付計算が狂うため、AF列「日付書式エラーフラグ」がISNUMBERで検出します。「日付書式エラー」が出たら、その行のB列を日付として入力し直してください。
VBAもGASも使っていません。ExcelとGoogleスプレッドシートの標準機能だけで動きます。XLOOKUP・UNIQUE・FILTER・IFS・DATEDIF・TEXTJOINは意図的に使っていません(Excel 2019以前やGoogleスプレッドシートとの互換性のため。DATEDIFについては、Microsoft自身が誤った結果を返す場合があると警告しているため)。NG区分の連結はTEXTJOINではなく MID(IF(...)&IF(...),2,10000) という形の&連結で実装してあります。数式が長く見えるのはそのためで、動作に問題はありません。
VLOOKUPを使っていない理由。「企業マスタから既存担当営業を引く」といった処理はCOUNTIFS+文字列連結で実装しています。結果として、1社に複数チャネルのNG行がある場合も全部拾って「架電NG/メールNG」のように連結でき、先頭1件しか返さないVLOOKUPより実務に合っています。既存担当営業まで引きたい場合は、企業マスタの列範囲を直接指定したVLOOKUPをご自身で足してください(列の並びに依存するため標準には入れていません)。
Googleスプレッドシートで使う場合。このページのCSV一式の各CSVを、シート単位で1枚ずつ取り込んでください。数式は入ったまま取り込まれます。シート名はCSVのファイル名と同じにする必要があります(シート間参照が名前で書かれているため)。
検証状況について。架空データ201項目の検証をすべて通過し、LibreOffice headlessで実際に再計算して値を突き合わせています。実在の購入リスト300社でも検証済みで、法人格の表記ゆれ(株式会社○○/(株)○○/全角英数)を照合キーで正しく同一と判定し、企業マスタに登録した30社を30件とも「既存あり」として検出しました。="…" 形式の法人番号からの13桁抽出も、305行すべてで正常に動作しています。
最後に。法令関連の列構成(受領ロット台帳の記録事項、オプトイン根拠、記録保存期限など)は調査に基づく実務上の整理であり、法的助言ではありません。「提供先身元・利用目的の提示日」列は令和8年改正(2026年7月17日公布・施行日未定)で想定される事前確認義務の予備列として置いてありますが、施行日未定・ガイドライン検討中のため、判定には一切使っていません。最終判断は個人情報保護委員会のガイドラインおよび専門家にご確認ください。
最終更新: / ListGoo 編集部