エクセルによる名寄せ

• 名寄せとは
• 名寄せの手順
• エクセルでの名寄せ例

名寄せとは

「名寄せ」とは、リスト内にある同じ人や同じ企業を一つにまとめて、重複を削除することをいい、データプレパレーションでよく行われる作業です。

リスト内にある同じ人や同じ企業ができる原因として以下のようなものがあります。
・別のソフトやシステムでリストを作成している
・引っ越しや異動の際、元のデータを変更せずに、新規に登録する　など

名寄せの手順

データ収集

名寄せ対象となるデータをエクセルで読み込めるように、エクセル形式やcsv形式で収集します。

名寄せ方針を決定

どのデータが一致する際に同じとするかの方針を決めます。例えば、「氏名が一致した場合に、同一とする」とした場合、同姓同名の人が同じ人としてまとめられますので、名寄せ作業は不完全となります。そこで、「氏名に加え、住所、電話番号、生年月日のすべてが一致する場合同一とする」とした場合、引っ越しなどで住所や電話番号が変わっている場合や、片方の生年月日が記載されていない場合は、同じ人と判定されませんので、同じ人がリスト内に残ることとあります。
名寄せ対象となるリストの内容と今後の活用方法により、その基準を決める必要があります。
また、名寄せ時片側だけにしかないデータを残すか、残さないかも合わせて決めておく必要があります。

名寄せ作業

データクレンジング

エクセルでの名寄せ作業は、名寄せ対象となる列のセルが完全に一致していることが前提となります。リストの作成方法や作成時期により、同じデータであってもリスト内での内容が違って、エクセルでは異なるものとして認識します。このような違いは、統一する必要があり、これをデータクレンジングといいます。

クレンジング対象例：

• 表記文字：全角・半角、ひらがな・カタカナ・アルファベット
• 日付：シリアル値か文字列か、和暦・西暦表記、●●年▲▲月■■日・●●/▲▲/■■、など
• 氏名：スペース区切りかどうか、ふりがながカタカナかひらがなか、など
• 企業名：株式会社・（株）、企業名と株式会社の間にスペースがあるか、社名変更など
• 住所：町村の場合、郡が記載されているか、丁目番地表記か、ハイフン区切りか
• 集合住宅：マンション、アパート名を記載しているか、101号室を101と記載しているか101号室と記載しているか
• 郵便番号、電話番号：ハイフン区切りか、ハイフンなしか
• 使用列数：1列で保存しているか、複数列で保存しているか（住所、郵便番号、電話番号などは）

データ統合

列数そろえ

住所、郵便番号、電話番号などは、1列で保存しているか、複数列で保存しているかは、そのデータソースにより異なります。結合対象のリストで違いがある場合には、同じ列構造にそろえる必要があります。

列並びそろえ

名寄せを行うために、1シート縦に結合する必要がありますので、作業をしやすいように、列の並びをそろえておく方が、効率的、確実に作業を行えます。

重複削除

エクセルの機能で「重複の削除」を行います。
また、昨日よる重複ができない場合は、並び替え、フィルタ機能なども活用し、ひとつずつ削除、調整します。

エクセルでの名寄せ例

利用シーン

ECショップを運営する小売店で、ECでの顧客情報と実店舗での顧客情報を統一し、顧客マスターを作成する。

データサンプル

リストA：ECショップの顧客情報

リストB：小売店の顧客情報

名寄せ後リストイメージ

名寄せ方針

名寄せ対象：リストA,B　使用ファイル（ダウンロード）
同じ人の条件：氏名、生年月日が同じ場合
使用データ：双方にデータがある場合には、リストAのデータを残す
その他：
名寄せ後のリストに統一会員番号を付与する。また、会員番号A、会員番号Bも残すようにする。また、データのない人は空白として作成する。
リストAにしかない「直近購入日」、リストBにだけある「性別」「保有ポイント」は、残さない。
リストBにある「姓（カタカナ）」、「名（カタカナ）」は、全角スペースを区切り文字とし、「氏名（カタカナ）」として残す。

エクセルの作業

データ確認

統合対象となる列の状況を確認します。
生年月日は、同じ用に見えますが、リストAの生年月日がシリアル値、リストBの生年月日が文字列として保存されています。
氏名は、リストAが1列、リストBが2列で作成されています。
生年月日：リストBの生年月日をシリアル値に変換
リストBの生年月日をシリアル値に変換
リストBの生年月日が文字列ですので、文字列として格納された日付を、Excel で日付として認識できるシリアル値に変換するDATEVALUE 関数を使用します。
(1)データの右に「生年月日（結合用）」といった名前の列（S列、列名は任意）を作成します。
(2)2行目に計算式「= DATEVALUE(Q2)」と入力します。
(3)シートの設定によっては、シリアル値が表示されますので、その場合は、人の目でも分かるように、表示形式を日付形式に変更します。
(4)関数を入力したセルS2をデータの一番下までコピーします。

リストBの姓、名を結合

文字列の結合は、&を使用するかCONCATENATE関数を使用します。今回は、&を使用して説明します。

(1)データの右に「氏名（結合用）」といった項目名の列（T列、列名は任意）を作成します。

(2)2行目に計算式「=B2&”　”&C2」と入力します。

(3)セルの書式を日付ケキ式に変更します。

(4)関数を入力したセルT2をデータの一番下までコピーします。

リストBの姓（カタカナ）、名（カタカナ）を結合

姓、名の結合の際と同様、文字列の結合を行いますので、&を使用します。
(1) データの右に「氏名（カタカナ）（結合用）」といった項目名の列（U列、列名は任意）を作成し、2行目に　=B2&”　”&C2　と入力します。

(2) 関数を入力したセルT2をデータの一番下までコピーします。

列並びそろえ

名寄せ後リストの列順に合わせるため、1列ずつ列順を入れ替えていきます。なお、関数で作成した新しい列は、元のデータが削除されると関数にエラーが発生しますので、列並び作業の前までに、シート全体をコピーし、「値」で貼り付けておく必要があります。

多数の列が多いと名寄せ後のリストとの比較が煩雑になるので、リストAの上に、名寄せ後リストをコピーします。少し空白行を作成したほうが後の作業がしやすいです。また、メニュー「表示」のウィンドウブロック「分割」を行うことで、最終行までの選択をしやすくしておきます。

一番上のセルを選択後、SHIFTボタンを押しながら、一番下のセルを選択すると、一番上から一番下までが一括選択されます。

列の並びを合わせるために、項目行から最終行のセルを選択し、列の挿入、削除、移動(切り取りして、挿入)を繰り返します。

リストA、リストBの列そろえが終わりましたら、新しいシートで縦に結合します。結合の際は、データ部分だけを結合するのではなく、一度、表題部分も含めて、結合してから、不要行を削除するほうがわかりやすいです。

重複の削除

データ結合が完了しましたら、重複の削除を行います。
メニュー「データ」のデータツールブロック内にある「重複の削除」をクリックします。

「重複の削除」ダイアログが表示されますので、「氏名」「生年月日」にチェックを入れ、「OK」ボタンをクリックします。

重複したデータを削除した旨のダイアログボックスが表示されます。
「OK」ボタンをクリックすると、操作ウィンドウに戻ります。

「重複の削除」で残るデータは一番上の行が残り、2番目以降のデータは削除されます。今回の場合は、リストAのデータが残り、下にコピーしたリストBのうち重複したデータが削除されました。

会員番号Bの追加

今回の方針は「会員番号A、会員番号Bも残すようにする。」でしたので、重複で、削除されたリストBの会員番号Bを追加します。今回の追加の作業は、VOOLKUP関数を使用して行います。
まず、重複を削除した列「氏名」「生年月日」と「会員番号B」の入ったシートを作成します。VOOLUP関数の参照値は、一番左の列のデータを参照する関数のため、「氏名」「生年月日」を結合した列を「会員番号B」の列より左に作成します。今回の生年月日はシリアル値で、保存されているため、単純に列結合を行った場合は、シリアル値が表示されます。

1セル内のVLOOKUP関数を簡単にするため、結合後のシートも「氏名」「生年月日」を結合した列を作成します。この時、結合する順番は同じであることが必須です。
VLOOKUP関数を作成し、下までコピーします。重複していない列は、エラー表示（＃N/A）となります。このエラー表示は、IFERROR関数で表示しないようにするか、コピー後削除します。
今回は、コピー後削除をする方法で説明します。

会員番号参照した列（Q）は、リストBの会員情報全体なので、C列に値をコピーします。

コピー後、エラー表示の＃N/Aは、フィルタ機能を活用して一括削除します。

この後、会員番号B追加に作成した列を削除し、統一会員番号を作成して、今回の名寄せ作業は終了です。

---

---

記事作成日：2018年5月1日　作成環境：Excel 2013、Windows8.1