購入した営業リストCSVの取り込み手順

購入・納品された営業リストCSVを、テンプレート「購入した営業リストCSV用(取り込み・重複チェック)」へ取り込む手順です。このテンプレートは、貼り付けた行を既存の企業マスタ・お断りリストと突き合わせ、最終列の「取り込み可否判定」が 取り込み可 になった行だけを企業マスタへ移す、という流れで使います。

VBA や GAS は使っていません。Excel と Googleスプレッドシートの標準関数だけで動きます。

使うファイル

形式パス備考
ExcelこのページのExcelブック数式・入力規則・条件付き書式がすべて入っています。通常はこちら
Googleスプレッドシート用このページのCSV一式の7ファイル数式は入っていますが、条件付き書式と入力規則は入りません

ブックの中身は、入力用の〈取り込みシート〉、照合先の〈企業マスタ〉〈お断りリスト〉、記録用の〈受領ロット台帳〉〈フォーム送信管理〉、それに〈選択肢マスター〉〈設定〉〈使い方〉〈列定義〉の9シートです。Excel は閉じたブックに対して COUNTIF / COUNTIFS が動かないため、照合先をすべて同じブックの中に入れてあります。別ファイルに分けないでください。


1. 取り込み前の準備

購入CSVを貼る前に、照合先と記録を先に用意します。ここを飛ばすと重複チェックは「エラーにならずに素通りする」ので、最も事故が起きやすい箇所です。

1-1. 〈設定〉シートのしきい値を確認する

B列の数値を自社の運用に合わせます。初期値は次のとおりです。

項目初期値効く場所
記録保存_月数36受領ロット台帳の「記録保存期限」
保存期限アラート_日数30受領ロット台帳の「保存期限アラート」「削除期限アラート」
契約終了後_削除猶予日数30受領ロット台帳の「契約終了時の削除期限」
鮮度確認_日数180企業マスタの「鮮度アラート」
フォーム再送可能_日数90フォーム送信管理の「再送可能日」

1-2. 〈企業マスタ〉に自社の既存データを入れる

既存顧客・既存見込み企業を、企業マスタのB列「企業名(正式名称)」以降に貼り付けます。ここが空のままだと「既存重複フラグ」は何も検出せず、購入リストの全行が「取り込み可」になります。 二重登録・二重アプローチを防ぐ仕組みが丸ごと働きません。

企業名・法人番号・代表電話・メールアドレスを入れれば、K〜O列の照合キー(会社名/法人番号/電話/メール/ドメイン)は自動で埋まります。照合キー列には手入力しないでください。

貼り終えたらV列「マスタ内重複フラグ」を確認し、マスタ内重複 が出ている行を先に1行へ統合します。照合先が二重になっていると、取り込み側の判定も信用できなくなります。

1-3. 〈お断りリスト〉に接触NG先を入れる

過去の架電拒否・配信停止・クレーム先を登録します。ここも空だとNG照合が一切効きません。

  • NG区分は 架電NG / メールNG / DM・郵送NG / 訪問NG / 全接触NG / 一時停止 のチャネル別です。1社で複数チャネルがNGなら、行を分けて登録します。
  • 企業単位でメールを全部止めたい場合は、メールアドレス欄に @midori-densou.co.jp のように @から始まるドメインだけ を入れればドメイン_照合キーが効きます。
  • 一時停止 のときだけ解除予定日を入れます。恒久NGは空欄のままにしてください。
  • 行は絶対に削除しません。 削除すると、別ファイルから同じ企業が再登録されたときに再架電・再送信してしまいます。

解除の判定は最終列「NG有効判定」が自動で行います。

=IF($N2="","",IF(AND($G2="一時停止",$L2<>"",$L2<TODAY()),"解除済","有効"))

NG区分が「一時停止」で解除予定日を過ぎた行だけを 解除済 に落とし、それ以外は 有効 のまま残す数式です。取り込み側は 有効 の行だけを数えます。

1-4. 〈受領ロット台帳〉に1行追加する

リストを受領したら、貼り付ける前にここへ1行書きます。このシートは、個人情報保護法の第三者提供の確認・記録義務にあたる記録事項に列名を合わせてあります(以下は調査に基づく実務上の整理であり、法的助言ではありません。最終判断は個人情報保護委員会のガイドラインおよび専門家でご確認ください)。

最低限、次の列を埋めます。

列例必須の理由
ロットIDLOT-2026-003取り込みシートの各行とこのIDで紐づきます
受領年月日2026/11/10記録保存期限の起点になります
提供元の氏名・名称株式会社データソース販売判定に使われます
提供元の住所東京都港区芝5-6-7 芝パークビル10F判定に使われます
取得の経緯企業公式サイトから収集不明 を選ぶとロット全体が取り込み不可
提供根拠オプトアウト提供不明 を選ぶとロット全体が取り込み不可
記録方式通常(3年)契約書等で代替(1年) を選ぶと保存期限が12か月で計算されます

入れ終えたら、V列「ロット取り込み可否」が 可 になっていることを確認します。

=IF($A2="","",IF(OR($C2="",$D2="",$G2="",$G2="不明",$H2="",$H2="不明"),"不可","可"))

提供元の名称・住所・取得の経緯・提供根拠が埋まっていて、かつ 不明 が選ばれていないロットだけを 可 にする数式です。不可 の行は赤く塗られ、そのロットID由来の行は取り込みシート側で全行弾かれます。出所不明のリストを運用で止めるための仕組みです。


2. CSVを開く前の注意 — 法人番号の ="..." 書式

購入リストの法人番号は、Excel で先頭ゼロが落ちないようにするための書式として

="1234567890123"

という形で入っていることがあります。これをそのまま Excel のセルに貼ると、Excel が数式として解釈してセルが #VALUE! になります。

対処は2通りあります。

方法A(推奨): 貼り付け先の列が文字列書式であることを確認してから貼る

テンプレートの〈取り込みシート〉では、A列「ロットID」・D列「法人番号」・E列「代表電話(原文)」に あらかじめ文字列書式(@)が設定済み です。そのため、通常はそのまま値のみ貼り付けで問題ありません。

念のため確認する場合は、D列を選択して

  • Excel: [ホーム]タブ → 数値グループの書式ボックスが「文字列」になっているか
  • Googleスプレッドシート: [表示形式]→[数値]→[書式なしテキスト]を選んでおく

を見てください。

方法B: Excel の[データ]→[テキストまたはCSVから]で取り込む

CSVファイルをダブルクリックで開くのではなく、

  1. [データ]タブ →[テキストまたはCSVから]
  2. CSVを選び、文字コード(Shift-JIS の納品物が多いので「日本語 (シフト JIS)」)と区切り記号「コンマ」を確認
  3. [データの変換]をクリック
  4. Power Query エディターで法人番号の列を選択 →[変換]タブ →[データ型]→[テキスト]
  5. [閉じて読み込む]

この方法では新しいシートに読み込まれるので、そこから改めて〈取り込みシート〉へ値のみ貼り付けてください。Power Query から直接テンプレートのシートへ流し込むことはできません。

書式が多少乱れていても吸収されます

テンプレートにはR列「法人番号_照合キー」があり、原文から13桁だけを取り出します。

=IFERROR(IF($D2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(ASC($D2&"")),"=",""),CHAR(34),""),"'","")),"")

法人番号の原文から =・二重引用符・アポストロフィ・前後の空白を取り除き、全角数字を半角に揃える数式です。突合も桁数チェックも、原文ではなくこの照合キー列に対して行っています。したがって ="..." 形式のまま文字列として貼られていても、先頭にアポストロフィが付いていても、全角で入っていても、正しく13桁が取れます。

それでもセルがエラーになった場合

D列「法人番号」のセルが #VALUE! などの計算エラーになると、そのセルだけが濃い赤(条件付き書式 =ISERROR($D2))で塗られます。

ここで注意が必要なのは、法人番号がエラーになっても「取り込み可否判定」は 取り込み可 のままになり得るという点です。照合キー側が IFERROR で空文字に落ちるため、桁数エラーとしては検出されません。貼り付け後にD列の濃い赤がないかを必ず目視で確認し、赤があればその行の法人番号を文字列として入れ直してください。


3. 貼り付け手順

3-1. 貼り付ける範囲

〈取り込みシート〉は、列の役割が次のように分かれています。

列役割扱い
A〜P(ロットID 〜 オプトイン根拠)購入CSVの中身を入れる取り込み列ここに貼る
Q〜U(会社名_照合キー 〜 ドメイン_照合キー)照合キー(自動計算)触らない
V〜Y(アプローチ可否 / ステータス / 担当営業 / 備考)運用メモ後から人が手入力
Z〜AI(社内重複フラグ 〜 取り込み可否判定)判定(自動計算)触らない

貼り付けるのは A列からP列まで です。Q列以降へはみ出して貼ると、照合キーと判定の数式が消えます。

3-2. 必ず「値のみ」で貼る

  • Excel: コピー元を選択 → 貼り付け先のセル(A2 など)で右クリック →[形式を選択して貼り付け]→[値のみ](アイコンの「123」)。ショートカットは Ctrl+Alt+V →「値」→ Enter
  • Googleスプレッドシート: [編集]→[特別な貼り付け]→[値のみ貼り付け]。ショートカットは Ctrl+Shift+V(Mac は ⌘+Shift+V)

通常の貼り付け(Ctrl+V)を使うと、コピー元の書式がテンプレートの文字列書式を上書きし、場合によっては右側の計算列まで巻き込んで数式が消えます。

3-3. 貼り付け後に必ずやる3つ

  1. A列「ロットID」を全行に入れる。 手順1-4で登録したロットIDをA2に入力し、最終行まで下へコピーします。ここが空だと「ロット照合エラーフラグ」が ロットID未入力 になり、取り込み可否判定が通りません。
  2. B列「取り込み日」を全行に入れる。 日付として入れてください。文字列として入ると「日付書式エラーフラグ」が 日付書式エラー を出します。
  3. O列「メール配信可否」・P列「オプトイン根拠」を確認する。 購入リスト由来のアドレスは、多くの場合オプトイン根拠がありません。該当する行は 根拠なし(送信不可) を選びます。

4. 取り込み後に見るところ

4-1. AI列「取り込み可否判定」

この1列に、重複・NG・エラーがすべて集約されています。まず COUNTIF で件数を数えて実数を把握してから、色を見てください。

=COUNTIF(取り込みシート!$AI:$AI,"取り込み可")

「取り込み可」の行数を数える数式です。空いているセルに入れて使います。

表示される値の意味は次のとおりです。

表示意味対応
(空欄)ロットID・企業名・電話・メールがすべて空の未使用行何もしない
取り込み可重複もNGもエラーもない。企業マスタへ移してよい手順5へ
社内重複この取り込みシート内で2件目以降の重複。1件目は別に残っている2件目以降を削除してよい
既存あり企業マスタに既にいる企業(会社名キー・法人番号・電話キーのいずれかで一致)マスタ側を正とし、新しい電話番号や担当者だけを人が判断して補記。この行はステータスを 除外 に
NGお断りリストの有効なNGに当たる(会社名・電話・メール・ドメインの4キーで照合)取り込まない
NG判定エラーメールアドレスに @ が無いなど、照合そのものが計算エラーになっている原文のメールアドレスを直す
必須項目未入力企業名が空、取り込み日が空、または電話もメールも両方空原文を補う
法人番号桁数エラー法人番号_照合キーが13桁になっていない原文の法人番号を直す
日付書式エラー取り込み日が文字列として入っている日付として入れ直す
ロットID未入力A列が空ロットIDを入れる
ロット台帳に未登録そのロットIDが受領ロット台帳に無い台帳に1行追加する
ロット要件未充足台帳にはあるが「ロット取り込み可否」が 不可台帳の提供元情報・取得の経緯・提供根拠を埋める
要確認(計算エラー)判定のどこかが計算エラーその行のセルを目視で確認

複数の理由が重なる行は 既存あり/NG のようにスラッシュで連結されて表示されます。

4-2. 条件付き書式の色

〈取り込みシート〉の行全体には5つのルールが、上から優先する順で入っています。

色条件意味
濃い赤NG照合フラグが NG で始まるお断りリストに当たる、またはNG判定がエラー。最優先
黄既存重複フラグが 既存あり企業マスタに既にいる
オレンジ社内重複フラグが 重複(2件目以降)シート内の重複のうち削除してよい側
赤取り込み可否判定が空欄でなく 取り込み可 でもない上記以外の理由で取り込めない
緑取り込み可否判定が 取り込み可そのまま企業マスタへ移せる

このほかに、セル単位のルールが2つあります。

  • AH列「メール送信可否判定」: 送信不可 で始まるセルが濃い赤
  • D列「法人番号」: セルが計算エラーのとき濃い赤(前述)

条件付き書式はすべて「カスタム数式」で書いてあります。Excel の『重複する値』プリセットは Googleスプレッドシートに存在しないため使っていません。Excel で中身を確認する場合は[ホーム]→[条件付き書式]→[ルールの管理]、Googleスプレッドシートでは[表示形式]→[条件付き書式]から見られます。

4-3. AH列「メール送信可否判定」は取り込み可否とは別物

取り込み可否判定にオプトイン根拠は含めていません。特定電子メール法上メールを送れない相手でも、架電やDMは可能な場合があるためです。メール配信を行うときは必ずこの列を見て、送信不可(根拠なし)・送信不可(配信停止)・送信不可(NG) の行には送らないでください。購入リスト由来のアドレスは、多くが 送信不可(根拠なし) になる想定です。


5. 企業マスタへの移し方

5-1. 先にバックアップを取る

シートタブを右クリック →[移動またはコピー](Googleスプレッドシートは[コピーを作成])で、〈取り込みシート〉の複製を作ってから作業してください。

5-2. 「取り込み可」だけを抽出する

  • Excel: 1行目を選択 →[データ]タブ →[フィルター]→ AI列「取り込み可否判定」の▼ → 取り込み可 だけにチェック
  • Googleスプレッドシート: [データ]→[フィルタを作成]→ AI列の▼ →[値でフィルタ]で 取り込み可 だけにチェック

5-3. 列をブロックに分けてコピーする

取り込みシートと企業マスタは列の並びが違うため、一度に全部は貼れません。次の3ブロックに分けると、それぞれ順番が一致します。

取り込みシート→企業マスタ
C〜H列(企業名(原文)/ 法人番号 / 代表電話(原文)/ WebサイトURL / 所在地(原文)/ 業種)→B〜G列(企業名(正式名称)/ 法人番号 / 代表電話 / WebサイトURL / 所在地 / 業種)
K〜L列(部署 / 担当者名)→H〜I列(部署 / 担当者名)
N列(メールアドレス(原文))→J列(メールアドレス)

貼り付けは必ず[形式を選択して貼り付け]→[値のみ](Googleスプレッドシートは[特別な貼り付け]→[値のみ貼り付け])です。

企業マスタ側のK〜O列(照合キー)とV〜W列(マスタ内重複フラグ・鮮度アラート)には絶対に貼らないでください。 ここは数式です。上書きすると、以後の重複チェックが機能しなくなります。

残りの列(企業ID、取引ステータス、自社担当営業、最終接触日、最終更新日、データ取得元、備考)は手入力です。データ取得元には 購入リスト LOT-2026-003 のようにロットIDを書いておくと、本人からの開示請求が来たときに出所をたどれます。

5-4. 貼った直後に起きる変化を知っておく

企業マスタへ追記すると、取り込みシート側の「既存重複フラグ」が追記した行の分だけ 既存あり に変わり、緑だった行が黄色になります。 既存重複フラグは企業マスタの照合キー列を COUNTIF で見ているので、これは仕様どおりの正常な挙動です。

そのため、作業順は「抽出 → コピー → 企業マスタへ貼る」を一気に済ませてください。先に企業マスタへ貼ってから取り込みシートを見直すと、どれを移したのか分からなくなります。

移し終えたら、取り込みシート側のW列「ステータス」を マスタ登録済 にします。これが、どの行をマスタへ移したかの記録になります。

5-5. 最後に企業マスタの重複を確認する

企業マスタのV列「マスタ内重複フラグ」に マスタ内重複 が新たに出ていないかを見てください。出ていれば統合します。

なお、Excel の[データ]→[重複の削除]を使う場合は、判定に使う列に必ず 「会社名_照合キー」 を選んでください。表示用の「企業名(正式名称)」を選ぶと、株式会社○○ と (株)○○ が別物と判定されて取り逃します。


6. 行数が足りないとき

数式は 500行目まで 入っています(xlsx版)。それより多い購入リストを扱う場合は、行を増やします。

  1. 501行目のA列からAI列までを選択します
  2. 選択範囲の右下のフィルハンドル(小さな四角)を、必要な行数ぶん下へドラッグします

これで数式と書式が一緒に伸びます。法人番号列・ロットID列・代表電話列の文字列書式も一緒にコピーされるので、ドラッグしてから貼り付けてください。Googleスプレッドシートでも同じ操作ができます(選択してから Ctrl+D でも下方向にコピーされます)。

照合キー列・判定列の数式はすべて列全体または相対参照で書かれているので、行を増やしても参照は壊れません。

CSV版(Googleスプレッドシート用)は 200行まで です。同じ手順で下へコピーして増やしてください。


7. Googleスプレッドシートで使う場合

7-1. 7ファイルを「同じブックに」「シート名そのままで」取り込む

このページのCSV一式には7つのCSVがあります。

設定.csv
選択肢マスター.csv
企業マスタ.csv
お断りリスト.csv
受領ロット台帳.csv
取り込みシート.csv
フォーム送信管理.csv

この7ファイルをすべて、1つのスプレッドシートに取り込んでください。 取り込みシートの数式は 企業マスタ・お断りリスト・受領ロット台帳 を、企業マスタと受領ロット台帳の数式は 設定 を、シート名で直接参照しています。1ファイルだけ取り込むと、参照先が存在せず #REF! になって数式が壊れます。

手順は次のとおりです。

  1. Googleドライブで新しいスプレッドシートを作ります
  2. [ファイル]→[インポート]→[アップロード]タブ → CSVをドラッグ
  3. インポート場所で 「新しいシートを挿入する」 を選びます
  4. 区切り文字は「カンマ」(自動検出のままで通常は問題ありません)
  5. 「テキストを数値、日付、数式に変換する」は 「はい」 にします。ここを「いいえ」にすると、CSVに入っている数式が =IF(...) という文字列のまま取り込まれ、計算されません
  6. [データをインポート]をクリックします
  7. 残りの6ファイルについて2〜6を繰り返します

参照先が先にある状態のほうが見た目の混乱が少ないので、設定 → 選択肢マスター → 企業マスタ → お断りリスト → 受領ロット台帳 → 取り込みシート → フォーム送信管理 の順をおすすめします。

取り込みが終わったら、シート名がファイル名どおり(取り込みシート、企業マスタ など)になっているかを確認してください。末尾に余計な文字が付いている場合はタブをダブルクリックして直します。シート名が違うと数式の参照が外れます。最後に、最初からあった空の「シート1」を削除します。

7-2. CSVに入らないもの

CSV形式には書式情報が入りません。そのため、Googleスプレッドシートへ取り込んだ状態では次の2つが入っていません。

  • 条件付き書式(濃い赤=NG、黄=既存あり、橙=シート内重複、赤=取り込み不可、緑=取り込み可など)
  • 入力規則のプルダウン(売上規模、メール配信可否、オプトイン根拠、NG区分、取得の経緯、提供根拠など)

判定列の文字(取り込み可 / 既存あり / NG など)は正しく計算されるので、色がなくても作業自体は進められます。色とプルダウンが必要な場合は、次のどちらかを選んでください。

  • xlsx版をGoogleドライブにアップロードして開く(条件付き書式と入力規則がそのまま引き継がれます)。こちらが簡単です
  • 〈選択肢マスター〉シートの値を使って、[データ]→[データの入力規則]でプルダウンを手動設定し、[表示形式]→[条件付き書式]→[カスタム数式]で色を手動設定する

条件付き書式の数式は本ページの4-2の表と、Excel版のルール管理画面で確認できます。Excel の『数式を使用して、書式設定するセルを決定』と Googleスプレッドシートの『カスタム数式』は同じ書き方なので、数式はそのまま転記できます。

7-3. 日付の列がシリアル値で表示されたとき

CSV は表示形式を持てないため、取り込んだ直後は日付を返す計算列(〈受領ロット台帳〉の記録保存期限・ 契約終了時の削除期限、〈フォーム送信管理〉の再送可能日など)が 47404 のような数値で表示されることがあります。 計算自体は正しく、期限アラートの判定も正常に動きますが、目で読めないので表示形式を直してください。

列を選択して、Googleスプレッドシートなら[表示形式]→[数字]→[日付]、Excel なら[ホーム]→[数値]グループで 「短い日付形式」を選びます。xlsx版は最初から日付書式が設定されているので、この操作は要りません。

7-4. Googleスプレッドシートでの貼り付け

  • 値のみ貼り付け: [編集]→[特別な貼り付け]→[値のみ貼り付け](Ctrl+Shift+V)
  • 法人番号列を文字列にする: 列を選択 →[表示形式]→[数値]→[書式なしテキスト]

8. 実際の検証結果

このテンプレートは、架空データ201項目の検証をすべて通過しています。検証は LibreOffice headless で実際に再計算し、出力された値を期待値と突き合わせる方法で行いました。

加えて、実在の購入リスト300社を使った取り込み検証も行っています。条件と結果は次のとおりです。

検証の条件

  • 実在の購入リスト300社を〈取り込みシート〉へ投入
  • そのうち先頭30社を、表記を意図的に変えて〈企業マスタ〉に登録(10社は 株式会社○○ を (株)○○ に、10社は社名を全角英数にして末尾に全角スペースを追加、半数は電話番号のハイフンを除去)
  • 〈お断りリスト〉に恒久NG(全接触NG)3件と、解除予定日を過ぎた一時停止2件を登録。恒久NGの3件にも、誤入力を再現するためあえて過去の解除予定日を入力
  • 末尾に、法人格の表記だけを変えた重複行を5件追加(合計305行)

結果

項目結果
総行数305行
既存あり(企業マスタと重複)30件(登録した30社を30件とも検出)
社内重複重複(1件目) 5件 / 重複(2件目以降) 5件
NG照合ヒット3件(恒久NGのみ。期限切れの一時停止2件は正しく除外)
必須項目エラー0件
法人番号桁数エラー0件(305行すべてで13桁を取り出せた)
取り込み可267件

確認できたことは次の3点です。

  1. 法人格の表記ゆれを同一と判定できた。 株式会社○○ と (株)○○、全角英数と半角、末尾の全角スペースの有無が混在していても、会社名_照合キーが同じ値に正規化され、企業マスタに登録した30社を30件とも「既存あり」として検出しました。シート内の重複5件も、法人格の表記だけ変えた行として正しく検出しています。
  2. 購入リスト特有の ="1234567890123" 形式を吸収できた。 法人番号を原文のまま文字列として入れた状態で、法人番号_照合キーが305行すべてで13桁を取り出し、桁数エラーは0件でした。
  3. NGの有効・無効を期限で正しく切り分けられた。 恒久NG3件を検出し、解除予定日を過ぎた一時停止2件は検出しませんでした。恒久NGの行に解除予定日を誤入力していても判定は反転せず、NG扱いのまま残りました。

補足

  • 本テンプレートの法令関連の列構成は、調査に基づく実務上の整理であり、法的助言ではありません。最終的な判断は個人情報保護委員会のガイドラインおよび専門家にご確認ください。
  • 受領ロット台帳の「提供先身元・利用目的の提示日」列は、令和8年改正個人情報保護法(2026年7月17日公布、施行日未定)で想定される事前確認義務に備えた予備列です。ガイドラインが検討中のため、判定には一切使っていません。運用は施行時のガイドラインに合わせてください。
  • 使用している関数は IF / AND / OR / IFERROR / COUNTIF / COUNTIFS / SUBSTITUTE / TRIM / LOWER / ASC / LEN / MID / FIND / CHAR / TODAY / EDATE / COUNTA / ISNUMBER / ISERROR などです。XLOOKUP / UNIQUE / FILTER / IFS / DATEDIF は、Excel 2019以前やGoogleスプレッドシートとの互換性のため意図的に使っていません(DATEDIF は Microsoft 自身が誤った結果を返す場合があると警告しているため使用していません)。

最終更新: / ListGoo 編集部

企業の電話番号・URLなど連絡先の表示には無料会員登録が必要です。

無料会員登録はこちら