営業リストをエクセルで作る｜先頭の0が消える列と、項目名のないCSV

テンプレートより先に、列の型を決める

営業リストをエクセルで作るとき、多くの場合はテンプレートを探すところから始まります。会社名、住所、電話番号、担当者名。並べる項目は、だいたい決まっています。

ところが項目を揃えても、値のほうが壊れます。郵便番号の先頭の0が消える。コードの桁が足りなくなる。社名のはずのセルが日付になっている。

どれも、入力の間違いではありません。表計算ソフトが数値として読んだ結果です。だから何度やり直しても、同じところで同じように壊れます。

この記事が扱う層

この記事は3つ目だけを扱います。どの項目を持つかではなく、持った項目が壊れない形にする話です。

先に結論

決めるのは4つ。1行が何を表すか。各列の型。取り込みの手順。そして、壊れたことに気づく仕掛けです。

この4つを決めてからテンプレートを作ってください。逆にすると、500行入れたあとに先頭の0が全部消えていることに気づきます。

入れた瞬間に値が変わる列

まず、何が起きるのかを実際に確かめました。Googleスプレッドシートに値を貼り、入ったあとの中身を読み戻す形で検証しています。

検証した結果

1つ、先頭が0の列は必ず落ちます。郵便番号、都道府県コード、市区町村コード、ハイフンなしの電話番号。営業リストの中核が全部これです。

2つ、日付に見える文字列は表示が変わりません。画面には4-5と出ているのに、中身は日付。見た目では気づけません。

先頭の0は、数値として読まれた時点で消える。桁が固定の列なら桁数から戻せるが、全行に当て直す作業が要る。

エクセルでは、もう1つ起きる

検証したのはスプレッドシートなので、エクセル側は公式の記述で確認しました。

Microsoftは、16桁以上の数値について「Excel has a maximum precision of 15 significant digits」と書いています。そして16桁目以降は0に丸められる、と。

さらに「for any number codes that are 16 digits or larger, you must use a text format」とあります。16桁以上のコードは、書式をテキストにする以外に保つ方法がありません。

先頭の0についても「Excel automatically removes leading zeros」と明記されています。つまりエクセルでもスプレッドシートでも、0は落ちます。

営業リストで扱うコードは、法人番号の13桁が最大でしょう。だから桁落ちより先に効いてくるのは、先頭の0のほうです。

法人番号は0から始まらない

ここは根拠を押さえておきます。国税庁の資料に、法人番号は「12桁の基礎番号及びその前に付された1桁の検査用数字（チェックデジット）の数字のみで構成される13桁の番号」とあります。

検査用数字の算式は、基礎番号の各桁に1と2を交互に掛けた合計を9で割った余りを、9から引いたものです。余りは0から8なので、検査用数字は1から9にしかなりません。

つまり法人番号の先頭に0が来ることはない。だから法人番号の列については、先頭の0が落ちる心配をしなくて済みます。

逆に言えば、法人番号の列が12桁になっていたら、それは0が落ちたのではなく検査用数字が付いていない基礎番号です。別の問題として扱ってください。

1行が何を表すかを決める

列の型より前に決めることがあります。1行が何を表すか、です。

ここが決まっていないと、同じ表に法人の行と支店の行と担当者の行が混ざります。混ざった表は、件数も数えられません。

最初は1社で始めてください。法人番号を主キーにできるのは、1行が1社のときだけです。

支店を同じ表に足すと、法人番号が重複します。重複した時点で、主キーとして使えなくなる。そして重複を潰す処理が、支店を消してしまいます。

1社の表、拠点の表、人の表。足す場所を先に決めておけば、あとで分けられる。

表を3つに分ける

拠点や担当者が必要になったら、表を分けます。1つの表に列を足して横に伸ばすのではなく、別の表を作って縦に持つ形です。

会社の表    法人番号 / 商号 / 本店所在地 / 郵便番号 / 取得元
拠点の表    拠点ID / 法人番号 / 拠点名 / 所在地 / 電話番号
人の表      連絡先ID / 法人番号 / 拠点ID / 氏名 / 部署 / 役職

つなぐのは法人番号と拠点ID。
会社の表に担当者名の列を足さない

最初から3つ作る必要はない。足す場所だけ決めておけば足りる。

担当者名を会社の表に置くと、担当が2人いる会社で行が2つになります。そして1人が異動したときに、どちらの行を直すか迷う。

異動で変わる項目は、変わらない項目と同じ行に置かない。これが表を分ける理由です。

分けないという選択

300件程度で担当者も1社1人なら、分けなくて構いません。ただし列の順番だけは分けておいてください。左から、変わらない項目、数年で変わる項目、数か月で変わる項目の順。あとで表を分けるときに、右側を切り出すだけで済みます。

列の型を先に指定する

値が壊れるのは、貼ったあとではありません。貼った瞬間です。だから貼る前に書式を決めておきます。

テキストに固定する列

手順は、列を選んで書式をテキストに変える。それから貼る。この順番だけです。

先に貼ってから書式をテキストに変えても、0は戻ってきません。書式は、そのあとに入る値の扱いを決めるだけです。

落ちた0を戻せる場合と、戻せない場合

桁が決まっている列なら、戻せます。郵便番号は7桁、都道府県コードは2桁、市区町村コードは3桁、法人番号は13桁。期待する桁までゼロ埋めすれば、元の値になります。

Microsoftも、桁数を指定した表示形式と文字列に変換する関数を回避策に挙げています。桁が固定なら、落ちた0の数は引き算で出ます。

戻せないのは、桁が決まっていない列です。自社で振ったIDや、海外の番号のように長さがばらつくもの。この場合、元が何桁だったかを値から決められません。

そして戻せるとしても、全行に当て直す作業が要ります。だから先に書式を決めるほうが速い。

日付にする列

取得日、最終確認日、最終接触日、次回予定日。この4つは日付にします。そして表記を1つに決めてください。国税庁の公的データはYYYY-MM-DD形式なので、これに合わせておくと変換が要りません。

桁を揃えない形でテキストに持つと、並べ替えが文字順になります。月を1桁で書いた日付と2桁で書いた日付が混ざると、9月が10月より後に来る。ハイフンで桁を揃えておけば文字順でも正しく並びますが、日付の計算はできません。

選択肢を固定する列

取得元、状態、次のアクション。この3つは自由入力にしないでください。

取得元        公的データ / 公式サイト / 名刺 / 他社提供 / 問い合わせ
状態          未接触 / 接触中 / 商談 / 時期をおく / 送らない
次のアクション  架電 / メール / フォーム / なし

入力規則で選択肢に固定する。
自由入力だと「公式HP」「公式サイト」「HP」が別の値として残る

選択肢を固定しておくと、1年後に集計できる。自由入力だとできない。

自由入力の列は、入れた本人以外には読めません。半年後には本人にも読めなくなります。

社名の列で起きること

社名は文字列なので、数値化の心配はありません。ただし別の問題があります。

前後に空白が入っていても、見た目では分かりません。そして空白が入っていると、照合が一致しません。検証では、前後の空白はそのまま残りました。

対策は、取り込んだ直後に空白を落とす列を1つ作り、照合にはそちらを使うこと。元の列は書き換えないでください。元の表記が必要になる場面があります。

公的データには項目名が入っていない

土台に公的データを使う場合の話をします。国税庁の法人番号公表サイトは、法人番号と商号と所在地を公開していて、ダウンロードも用意されています。

ダウンロードする前に読むもの

このデータには、仕様を書いた資料が付いています。リソース定義書という名前で、ダウンロードファイルとウェブ経由の取得の両方を扱っています。

この資料にこう書かれています。「公表サイトの基本3情報画面よりダウンロードしたダウンロードファイル及びWeb-APIにより取得したデータには、項目名が入っておりません」

つまり、ヘッダー行がありません。開くと、名前のない列が30本以上並んだ状態になります。

どの列が何なのかは、この資料を見ないと分かりません。そして列の数は項目の追加で増えてきているので、古い資料だと合わないことがあります。

資料の36項目と、ファイルの30列

資料には36の項目が並んでいますが、ダウンロードファイルに入るのは30列です。

先頭の2項目には「CSV形式で取得する場合は設定されない」とあります。XMLの階層を作る要素です。続く4項目は、ウェブ経由で取得したときにヘッダーとして出る総件数や分割の番号で、ファイルには入りません。

残る30列が、一連番号から検索対象除外までです。列の数が合わないときは、まずここを確かめてください。

ファイルの形式

文字コードは2種類から選べます。Unicodeを選んでください。Shift-JISは第一・第二水準までなので、社名に使われている文字が落ちることがあります。

資料には、商号に第一・第二水準以外の文字が使われている場合、画面で確認するための識別子が別の項目に入る、という記述もあります。つまり、文字が落ちる場合があることが仕様として書かれています。

桁の決まりが書いてある

この資料には、項目ごとの桁数と形式が書かれています。営業リストに関係するものを挙げます。

ここが大事なところです。ファイルの側には、先頭の0が入っています。資料の凡例に、上位の桁が0の場合も数字が設定される、と書かれています。

つまり落とすのは表計算ソフトのほうです。ファイルは正しく、開き方が間違っている。これが先頭0の問題の正体です。

郵便番号の注意書き

郵便番号の項目には注意書きが付いています。所在地の文字情報を基に設定しているため、外字や誤字脱字があると正確な値にならない場合がある、と。そして「ビルや大口事業所に係る個別郵便番号には対応していません」。公的データの郵便番号は、住所から機械的に付けた値です。

取り込みの手順

1  リソース定義書を開いて、列の順番を確認する
2  空のシートを作り、使う列ぶんだけ書式をテキストにする
3  CSVを開かずに、テキストとして取り込む
4  取り込み後に、先頭0が残っているかを1行目で確認する
5  ヘッダー行を自分で1行足す

ダブルクリックで開かない。
開いた時点で、書式を指定する機会が失われる

4番を飛ばさない。ここで気づかないと、500行入れたあとに気づく。

30列から、使う列だけを残す

30列を全部持つと、表が読めなくなります。使う列だけ残してください。

残す列

コードの列を残すのが要点です。都道府県名で絞ると表記の揺れで漏れますが、コードなら一致します。

消す前に意味を知っておく列

使わない列の中にも、知っておくと役に立つものがあります。

閉鎖の年月日が入っている行は営業先として使えないので、先に外してください。

そして承継先法人番号が入っている行は、合併で消えた会社です。営業先としては、消えた側ではなく承継先に当たることになります。

自社で足す列

取得元        どのデータ源から来たか（選択肢で固定）
取得日        最初に表に入れた日
最終確認日    公式サイトを開いて確かめた日
最終接触日    実際に送った、または掛けた日
状態          未接触 / 接触中 / 商談 / 時期をおく / 送らない
次回予定日    次に動く日
正規化社名    空白と記号を落とした照合用の値

取得日と取得元は、あとから復元できない。
最初に入れておかないと、永久に分からない

7列。これ以上足すなら、何に使うかを先に決めてから足す。

エクセルか、スプレッドシートか

どちらでも作れます。違いが出るのは、件数と人数が増えたときです。

複数人で回すなら、スプレッドシートのほうが事故が少ないでしょう。ファイルを配ると、配った数だけ別のリストができます。1人で手元で加工するだけなら、エクセルで構いません。

管理ツールに移す時期

合図は3つです。同じ会社に2人が接触する事故が起きた。履歴の行が会社の行より多くなった。誰がいつ何を変えたかを追う必要が出た。この3つが出るまでは表計算ソフトで足ります。件数ではなく、人数と履歴の量で決まります。

壊れたことに気づく仕掛け

最後が要点です。壊れること自体は防げません。人が貼り付ける以上、いつか起きます。だから気づく仕掛けを入れます。

桁数を数える列

テキストに固定した列の横に、桁数を数える列を1つ置いてください。

法人番号      13桁でなければ異常
郵便番号      7桁でなければ異常
都道府県コード  2桁でなければ異常
市区町村コード  3桁でなければ異常

文字数を数えて、期待する桁と違う行に印を付ける。
貼り付けた直後に、この列を見る

0が落ちた行は、桁数が1つ減っている。だから桁数を見れば見つかる。

この列があれば、500行入れたあとでも異常な行だけ抽出できます。

法人番号を検算する

法人番号には検査用数字が入っているので、計算で正しさを確かめられます。桁数が合っているだけでは、途中の数字が違っていても気づけません。

算式は、基礎番号の最下位から奇数桁に1、偶数桁に2を掛けた合計を9で割り、その余りを9から引いたものです。これが先頭の1桁と一致すれば、番号として整合しています。

A列に法人番号13桁、B列に下12桁（基礎番号）を入れておく

=9-MOD(SUMPRODUCT(VALUE(MID(B2,ROW(INDIRECT("1:12")),1)),
  {2;1;2;1;2;1;2;1;2;1;2;1}),9)

この値が、A列の先頭1桁と一致すれば整合している

国税庁の計算例と、同庁自身の法人番号の2つで一致を確認した数式。

検算に引っかかる行は、入力の誤りか桁が落ちた行です。他社から受け取ったリストにそれが混ざっているかも、これで分かります。

貼り付けの手順を1枚に書く

仕掛けを入れても、手順が共有されていないと崩れます。書くのは4行で足ります。テキストに固定してある列はどれか。CSVはどう取り込むか。貼ったあとにどの列を見るか。異常があったらどうするか。これを表と同じ場所に置いてください。別の場所にある手順は読まれません。

月に1回、桁数の列を見る

そして月に1回だけ、桁数の列の異常を数えてください。増えているなら、取り込みの手順が守られていません。この確認は5分で終わります。

まとめ

• テンプレートより先に決める4つ。1行が何を表すか／列の型／取り込みの手順／気づく仕掛け
• 先頭が0の列は、貼った時点で0が落ちる。郵便番号・都道府県コード・市区町村コード・電話番号
• 落ちたあとに書式を変えても戻らない。桁が固定の列はゼロ埋めで戻せるが、全行に当て直す作業が要る
• 日付に見える文字列は表示が変わらないまま中身だけ日付になる
• Microsoftは有効桁数は15桁で、16桁目以降は0に丸められると明記。16桁以上はテキスト書式が必要
• 法人番号は13桁なので値は保たれる。検査用数字は1から9なので、先頭に0は来ない
• 1行＝1社で始める。法人番号を主キーにできるのはそのときだけ
• 拠点と担当者は別の表に持つ。異動で変わる項目を、変わらない項目と同じ行に置かない
• 公的データのダウンロードファイルには項目名が入っていない。定義書の36項目のうち、ファイルに入るのは30列
• 文字コードはUnicodeを選ぶ。Shift-JISは第二水準までなので社名の文字が落ちる
• ファイルの側には先頭の0が入っている。落とすのは表計算ソフト
• 残す列は7つ。都道府県コードを残すと、表記の揺れで漏れない
• 取得日と取得元は、あとから復元できない。最初に入れておく
• テキストの列の横に桁数を数える列を置く
• 法人番号は検査用数字で検算できる。入力の誤りや桁落ちが混ざっているかが分かる

営業リストが壊れる場所は、項目の選び方ではありません。値を入れる瞬間です。だから決めるのは、どの項目を持つかより先に、どう入れるか。この順番にしておけば、500行入れたあとに作り直す場面がなくなります。

よくある質問