基幹連携・データ移行読了約14分

Excel台帳をシステムへ移す前のデータの直し方【確認表つき】

顧客台帳や案件台帳をシステムに移す前に、何をどの順で直すか。判断者と確認方法を表にしました。

記事の概要図

営業の顧客台帳が、担当者ごとのExcelに分かれて十年近く使われてきた。今回、顧客管理のシステムに一本化することになり、開発会社から「台帳のデータを渡してください」と言われた。ところが開いてみると、同じ会社が「株式会社」「(株)」「㈱」の三通りで登録されていたり、電話番号の欄にメモが書かれていたり、住所が空欄の行が何百も並んでいたりする。

このまま渡せば、システムの中に「きれいな入れ物に、汚れたデータ」が入るだけです。かといって、全部を手で直す時間はありません。こんなことはないでしょうか。

この記事では、Excelの台帳をシステムに移す前に、何をどの順番で直すかを五つのステップにしました。各ステップで、誰が何をして、何で確かめるかを表にしてあります。当てはまるなら、ステップ1の棚卸しから始めてください。

この記事で分かること

  • 移行前に直すべきデータの汚れの種類と、機械で直せる範囲、人が判断する範囲
  • 直す作業の順番と、ステップごとの担当者・確認方法
  • 移行に使える確認表と、「直さずに移す」と決めてよいデータの見分け方

直す前に決めるのは、移行先で何に使う列かです。使う列だけを優先して直し、機械で直せるものと人が判断するものを分けると、作業量が現実的になります。

1. 台帳の汚れは4種類に分けられる

台帳のデータの汚れは、一見バラバラですが、大きく四つに分類できます。分類できると、「どの汚れは機械で直せて、どの汚れは人が判断するか」が決まります。

表記ゆれ株式会社/(株)/㈱、全角と半角の混在規則を決めて置換する業務担当者が規則を決め、機械が実行
重複同じ会社が2行ある、担当違いで複数行判定の列を決めて候補を出す業務担当者が残す行を決める
欠損・形式の混在住所の空欄、日付が「令和3年」と「2021/3/1」補う、または空欄のまま移す業務担当者
1セルに複数の意味電話番号の欄に「携帯:090…」「不在がち」項目ごとに分ける規則に外れる行だけ人が見る

IPAの「システム再構築を成功に導くためのユーザガイド」を紹介する資料では、再構築の課題として、現行の業務仕様の理解不足など、はっきりしない状況に伴うリスクが挙げられています。台帳の汚れも同じです。汚れの原因は、台帳に入力してきた人しか知らないことが多いので、システム担当だけで決めずに、台帳を使ってきた人を作業に入れてください。

台帳の汚れ4種類と、機械で直す範囲・人が判断する範囲

全部を一度に直そうとしない

四つの汚れのうち、機械で直せるのは表記ゆれと、一部の形式の混在です。重複と、1セルに複数の意味がある行は、機械で候補を出して人が確かめる形にします。全部を一度に終わらせようとせず、移行先で使う列から順に進めます。

五つのステップの全体像は次の図のとおりです。順番を入れ替えると、直した結果をもう一度直すことになるので、上から順に進めてください。

移行前の五つのステップの流れ

ステップ1 台帳の棚卸しをする

最初の作業は、直すことではなく、現状を数字で把握することです。IPAのセミナー資料でも、再構築の作業の先頭に、現行資産の棚卸しが置かれています。

棚卸しで、次の項目を台帳ごとに書き出します。

  • ファイルの数と、作った人・更新している人
  • 行数(データ件数)と、列の数
  • 各列が何のために使われているか(誰が見て、何に使うか)
  • 空欄の割合が高い列、同じ値ばかりの列
  • ほかのExcelや書類の元になっている列(参照されているか)
台帳の担当者列ごとに「何に使う列か」を一言で書く列の一覧表(列名・用途・使う人)
システム担当行数、空欄数、種類の数を数える件数のメモ。Excelの関数でも取れる
責任者移行する台帳・しない台帳を決める台帳ごとの判断が書かれた一覧

Excelでは、COUNTIF関数で条件に合う件数を数えられます。たとえば、ある列の空欄の数や、特定の表記が何行あるかを数える使い方です。件数が分かると、「表記ゆれは全体の何割か」を見積もれます。

この段階で、移行しない台帳を決めておくと、作業が一気に軽くなります。十年前の取引先で、今は連絡も取引もないものを、移行先に入れる必要があるのかを、ここで判断します。

ステップ2 移行先の項目と照らし合わせる

次に、台帳の列と、移行先のシステムの項目を対応づけます。データの移行でも、何をどこに入れるかを最初に決めます。

項目の対応表(マッピング表)を作ります。

会社名取引先名文字列必須「(株)」「㈱」を「株式会社」にそろえる
電話電話番号文字列(先頭0を残す)任意ハイフンを付ける・削る
初回取引日取引開始日日付任意和暦を西暦に直す
備考備考長い文字列任意そのまま移す

ここで注意するのは、台帳にあって移行先に項目がない列と、移行先で必須なのに台帳にない列です。前者は、使うなら項目を足すか、備考に入れるかを決めます。後者は、台帳のほかの列から補えるのか、空欄の行をどう扱うのかを決めます。

システム担当移行先の項目一覧(型・必須・桁数)を出す開発会社からの項目定義書
台帳の担当者台帳の列を、移行先の項目に当てはめるマッピング表
責任者当てはまらない列の扱いを決める判断を書いた行(捨てる・備考へ・項目を足す)

ステップ3 直す作業を3つに分ける

マッピング表ができたら、直す作業を次の三つに分けます。

機械で直す作業

規則が決まっているものは、Excelの機能や関数で一括で直せます。

  • 余分なスペースの除去:TRIM関数が使えます。ただしMicrosoftの説明では、TRIM関数が取り除くのは通常の半角スペースで、Webページなどに由来する改行しないスペースは除去されません。全角のスペースも別の扱いが必要です。置換機能と組み合わせてください。
  • 表記ゆれの置換:「(株)」を「株式会社」にするなど、置換の一覧表を作って一括で当てます。
  • 日付・数字の形式の統一:和暦や、全角数字の混在を整えます。

候補を出して人が決める作業

重複は、機械だけでは決められません。Excelの「重複の削除」機能は、選んだ列の値が同じ行を消しますが、Microsoftの手順でも、削除される前提の機能です。同じ名前でも別の会社はありますし、表記が違っても同じ会社はあります。

そのため、次の進め方を勧めます。

  1. 元の台帳を複製して、作業用のシートを作る
  2. 会社名と電話番号など、複数の列を組み合わせて、同じ組み合わせの行に印を付ける(削除はしない)
  3. 印の付いた行を、業務の担当者が見て、残す行・まとめる行を決める
  4. まとめた結果を、新しい列に「採用」「不採用」で記録する

人が直す作業

住所が空欄の行や、判読できない備考などは、担当者が判断します。ここで時間を使いすぎないために、「直せない行は空欄のまま移す」「移行後に使うときに直す」といった方針を、先に決めておきます。

機械で直すスペース・表記ゆれ・日付形式システム担当直した列と元の列を並べて抜き取り
候補を出して決める重複・名寄せ業務担当者印を付けた行の一覧を目視
人が直す空欄・判読できない備考台帳の担当者行ごとの判断メモ

直す作業を三つに分けた流れ図

ステップ4 元の台帳と突き合わせる

直した後に、「直したつもりで壊していないか」を確かめます。ここで大事なのは、元の台帳を上書きしないことです。元のファイルは、日付をつけて別の場所に保管します。

確認する数字を表にします。

総行数記録する記録する差は、まとめた行数と一致するか
取引先の種類の数記録する記録する名寄せ前後の差が、判断の記録と合うか
金額・件数の合計記録する記録する売上・案件数などの合計の差が0か
空欄の数(必須の項目)記録する記録する必須項目の空欄が減っているか
抜き取り20件目視目視元と直した後の値が、規則どおりか

総行数は、名寄せでまとめた分だけ減ります。減った分が、「まとめた」と記録した行数と一致すれば、意図しない削除はありません。一致しなければ、どこかで行が消えています。

システム担当件数・合計・空欄数を、元と直した後で数える数字を書いた確認表
業務担当者抜き取り20件を目で見て確かめる元の台帳との並べた画面
責任者差の説明がつかない項目が0であることを確認確認表の差の列

ステップ5 試験取り込みで確かめる

最後に、本番の全件を入れる前に、100件程度で試験取り込みをします。見るのは、取り込みが通ったかどうかではなく、移行先の画面で、業務に必要な操作ができるかです。

  • 取引先名で検索すると、期待した取引先が出るか
  • 電話番号の先頭の0が残っているか
  • 日付で並べ替えると、順番が正しいか
  • 1つの取引先を開いたとき、担当者や備考が正しい位置に入っているか

CSVで受け渡す場合は、文字コードや先頭の0の問題が出やすいので、受け渡しの形式を先に確認してください。

システム担当100件を試験で取り込む取り込みの結果ログ(件数・エラー行)
台帳の担当者画面で検索・並べ替え・詳細を確認するあらかじめ作った確認項目の表
責任者全件を取り込むかどうかを判断する確認項目がすべて合格かどうか

エラーが出た行は、その理由を記録します。同じ理由のエラーがまとまって出た場合は、規則の漏れなので、ステップ3に戻って直します。

2. 直さずに移すと決めてよいデータ

全部をきれいにするのは、現実的ではありません。次のようなデータは、直さずに移す、または移さないと決めて構いません。

長期間取引のない取引先移さず、元の台帳を保管保管場所と保管期間を決める
備考欄の自由記述直さずに備考へ移す検索・集計に使わない
判読できない住所空欄または「要確認」で移す使うときに直すと決めておく

個人情報が含まれる台帳では、不要なデータを移さないことが、情報の管理の面でも有利です。個人情報の取扱いについては、個人情報保護委員会が法令とガイドラインを公開しているので、台帳に個人情報が多く含まれる場合は、法令・ガイドラインの内容を確認してください。

3. 移行の作業が止まる三つの原因

台帳の移行がうまく進まない場合、原因は次の三つに集まります。

判断する人が決まっていない

重複や名寄せの判断を、「誰かがやるだろう」で放置すると止まります。作業の開始前に、判断者と期限を決めてください。

規則が口頭だけで共有されている

「(株)は株式会社にそろえる」などの規則を、口頭だけで共有すると、作業者によって結果が変わります。規則は置換の一覧表にして、全員が同じ表を使います。

元のデータを残していない

直した結果がおかしいと分かったときに、元に戻せないと、作業全体をやり直せません。元のデータは必ず日付つきで保管します。

4. 名寄せの判定ルールの書き方

重複の作業で一番迷うのは、「どこまで同じなら同じ取引先とみなすか」です。ここを人によって変えると、結果がばらつきます。判定のルールを、作業の前に表にして全員に渡します。次は、架空の台帳を例にした書き方です。

会社名と電話番号がともに一致同じ取引先とみなす「株式会社サンプル商事」と「(株)サンプル商事」で電話も同じまとめる行を担当者が選ぶ
会社名が一致し、電話番号が違う保留本社と支店の可能性がある住所を見て、別拠点として残すか決める
会社名が違い、電話番号が一致保留社名変更や、同じ建物の別会社の可能性担当者に確認する
会社名も電話番号も違う別の取引先何もしないなし

この表のポイントは、保留の行を必ず作ることです。機械が「同じ」と断定できないものは、人が見る行に回します。保留の行が全体の数割に達する場合は、判定の列に住所や担当者名を加えて、保留を減らす工夫をします。

まとめる行の選び方も決めておく

重複と判断した行のうち、どの行を残すかのルールも決めます。たとえば、「最終取引日が新しい行を残す」「備考が書かれている行を残す」「二つの行の備考は結合して残す」のように、優先順位を書いておきます。残さなかった行の情報(担当者名・取引履歴)が失われないように、備考へ転記するか、元の台帳に残すという扱いも決めてください。

記録の残し方

名寄せの結果は、行ごとに「元の行番号」「まとめた先」「判断した人」「日付」を記録します。後日、「この取引先がシステムに二つある(または見当たらない)」という問い合わせが来たときに、記録を見れば、経緯が分かります。記録がないと、元の台帳を一から見直すことになります。

元の行番号台帳A・152行目元データをたどるとき
まとめた先台帳B・37行目残した行を探すとき
判断した人営業部の担当者経緯を確認するとき
判断の理由電話と住所が一致判断を見直すとき

5. 作業の体制と進め方の決め方

台帳の整理は、一人で抱えると止まります。かといって人数を増やすと、規則がばらつきます。次の三つの役割を決めてから始めるのが、現実的な体制です。

規則の責任者置換の一覧表と判定ルールを決め、変更を管理する台帳を長く使ってきた業務担当者
実行担当規則どおりに直し、結果の件数を記録するExcelの操作に慣れた担当者
最終確認の担当ステップ4の突き合わせで、数字と抜き取りを確認する規則の責任者とは別の人

確認の担当を、直した人とは別の人にするのがポイントです。自分で直したものを自分で見ると、同じ思い込みで見逃します。人数が少なければ、確認だけでも別の人に頼んでください。

規則の変更を記録する

作業の途中で、「この表記も置換に加えよう」と規則が増えることはよくあります。そのたびに、置換の一覧表に追記し、追記した日付と理由を残します。すでに直した行にも新しい規則を当て直す必要があるので、一覧表を版で管理すると、やり直しの範囲が分かります。

作業の区切りで台帳を固定する

直す作業と、現場の台帳への入力が同時に進むと、直した後に新しい行が増えて、突き合わせが合わなくなります。作業の期間中は、現場の入力を止めるか、「この日付以降の入力は別シートに」と決めます。移行の当日までの間に増えた分は、最後に差分として取り込みます。

作業中の入力の扱い基準日以降は別シートに入力基準日を台帳の先頭に明記する
差分の取り込み移行の直前に、基準日以降の行を追加追加した行数の記録
規則の版管理一覧表のファイル名に版を付ける版ごとの変更履歴

まとめ

Excel台帳の移行前の作業は、次の流れで進めます。ステップ1で台帳を棚卸しし、ステップ2で移行先の項目と対応づけ、ステップ3で直す作業を三つに分け、ステップ4で元の台帳と突き合わせ、ステップ5で試験取り込みをします。機械で直せるものは規則で、判断が要るものは候補を出して人が決めるのが、作業量を減らす基本です。

台帳の整理やデータ移行の進め方に迷ったら、相談できます。

よくある質問

Excel台帳のデータは、全部きれいにしてから移行しないといけませんか。

全部を直す必要はありません。移行先で検索・集計・連絡に使う列だけを優先し、使わない列や古い履歴は「そのまま保管」か「移さない」と決めて構いません。ただし、移さないと決めた根拠と、元ファイルの保管先は記録に残してください。

重複の削除機能で、重複行を消してしまって問題ありませんか。

判定に使う列が不十分だと、別の取引先を同じ取引先として消す危険があります。削除の前に元の台帳を別シートや別ファイルに複製し、削除ではなく「候補に印を付ける」ところまでをExcelで行い、消すかどうかは業務の担当者が見て決める進め方を勧めます。

移行するデータの確認は、誰が行うのが適切ですか。

業務の中身が分かる人です。システム担当や開発会社は、形式や必須項目の確認はできますが、「この2件は同じお客様か」「この項目は何を意味するか」は台帳を使ってきた担当者にしか判断できません。確認の担当と期限を、作業の開始前に決めてください。

台帳に1つのセルへ複数の情報を書いています。そのまま移せますか。

そのままでは移せないことが多いです。たとえば、住所欄に電話番号や備考が混ざっている場合は、移行先の項目ごとに分ける作業が必要です。件数が多ければ規則を決めて機械的に分け、規則に当てはまらない行だけを人が見る進め方が現実的です。

参照元・出典

FREE CONSULTATION

「うちの場合はどうか」から

記事の考え方を、そのままご自身の事業に当ててみたい方へ。アイデア段階でも、機能リストがある段階でも構いません。 2営業日以内に、担当者からメールでご返信します。営業のお電話はしません。相談は無料です。

運営:株式会社miitaso(会社概要)

RELATED