データクレンジングと名寄せ

公開日：2017-10-20　最終更新日：2018-07-18

データクレンジングと名寄せ

複数の名簿を突き合わせて一つにする、いわゆる「名寄せ」

名寄せを行うためには、その前にデータクレンジングを行う必要があります、

データクレンジングとは、
データの中から、重複の排除、誤記、表記の揺れの修正などを行い、データの品質を高めることです。
データを整形して、扱いやすいデータに変換します。

本来は、データ発生時からIDで管理してあるべきものですが、
未整備のローデータを活用しようとする時には避けて通れない作業となります。

以下では話を簡単にして、社名、電話番号、日付を例に説明します。

・社名

株式会社　エクセルの神髄
（株）エクセルの神髄
(株) エクセルの神髄
㈱エクセルの神髄

当然ですが、これらの文字列比較（IFで=判定）では、別の文字列として認識されます。
人間が目視すれば同じ社名と認識できても、Excelのシート関数にしろVBAにしろ別物と判定されます。

これらを同一の社名と判断するのに、IFでAndやOrを延々つなげていては効率が悪く、保守性も悪くなります。

こういう時は、専用のFunction（関数）を作成するようにします。


  
    
      Function ChangeCompanyName(ByVal strName As String) As String

      　　strName = Replace(strName, "　", "") '全角空白を消す

      　　strName = Replace(strName, " ", "") '半角空白を消す

      　　strName = Replace(strName, "（", "(") '全角（を半角に変換

      　　strName = Replace(strName, "）", ")") '全角）を半角に変換

      　　strName = Replace(strName, "(株)", "株式会社")

      　　strName = Replace(strName, "(同)", "合同会社")

      　　strName = Replace(strName, "(名)", "合名会社")

      　　strName = Replace(strName, "(資)", "合資会社")

      　　strName = Replace(strName, "(有)", "有限会社")

      　　strName = Replace(strName, "㈱", "株式会社")

      　　ChangeCompanyName = strName

      End Function

このVBAはあくまで参考ですが、概ねこのようなFunctionを作っておけば良いでしょう。

日本の名前の場合、さらに厄介な問題も多々あります。
特にきりがないのが旧字体ですね。
旧字体の対応は、さすがに汎用的な対処は大変なので、必要に応じて置換を追加するしかないでしょう。

また、カタカナ社名でよくある間違いが、
キャ○ン、シャ○○タ
とかでしょうか。
これらの拗音は、正式社名としては大文字になってる場合があります。
しかし、手入力された社名なら、これらは間違っていても不思議ではないし、いや普通に間違えますよね。
これに対処する場合は、とりあえずカナ小文字を大文字にして比較すれば良いです。
strName = Replace(strName, "ャ", "ヤ")
これを「ァィゥェォャュョ」ぶん追加すれば解決します。
登記に置いて、大文字と小文字の違いだけで別会社の登録が出来るかどうか…と言う問題はありますが、
名寄せに置いて、そんなことが問題になる事はほとんどないと思います。
カナ社名ですと、さらに半角カナという事もありえますので、
そこは、StrConvで全て全角にしてしまいましょう。
ついでに、英小文字も英大文字に変換しておくと良いですね。

以上を全て組み込むと、


  
    
      Function ChangeCompanyName(ByVal strName As String) As String

      　　strName = Replace(strName, "　", "") '全角空白を消す

      　　strName = Replace(strName, " ", "") '半角空白を消す

      　　strName = Replace(strName, "（", "(") '全角（を半角に変換

      　　strName = Replace(strName, "）", ")") '全角）を半角に変換

      　　strName = Replace(strName, "(株)", "株式会社")

      　　strName = Replace(strName, "(同)", "合同会社")

      　　strName = Replace(strName, "(名)", "合名会社")

      　　strName = Replace(strName, "(資)", "合資会社")

      　　strName = Replace(strName, "(有)", "有限会社")

      　　strName = Replace(strName, "㈱", "株式会社")

      　　strName = Replace(strName, "ァ", "ア")

      　　strName = Replace(strName, "ィ", "イ")

      　　strName = Replace(strName, "ゥ", "ウ")

      　　strName = Replace(strName, "ェ", "エ")

      　　strName = Replace(strName, "ォ", "オ")

      　　strName = Replace(strName, "ャ", "ヤ")

      　　strName = Replace(strName, "ュ", "ユ")

      　　strName = Replace(strName, "ョ", "ヨ")

      　　strName = StrConv(strName, vbUpperCase)

      　　strName = StrConv(strName, vbWide)

      　　ChangeCompanyName = strName

      End Function

名前なので、いろんなパターンが考えられますので、これでも不足はあると思います。
最初は90%くらいを対応するつもりでVBAを書いて、
はじかれたものを目視で確認し、共通するものがあれば、随時それを追加していく位でちょうど良いと思います。
しかし、そもそも100%完全な名寄せは不可能に近いので、
最終的に99%が対応出来れば、残りの1%は手作業で行うということで良いはずです。

ただし、このVBAでは名寄せの為にデータ整形したのであって、
そもそも正式社名は別途調べなければならないものになります。

・電話番号

03-1234-5678
03(1234)5678
03 1234 5678

社名同様に、これらは違う文字列となってしまいます。
これらを同一の番号と認識するための処理としては、いくつかの方法が考えられます。
・社名同様に、(や-を統一した文字列に置換する。
・数値部分だけを取り出す。

前者の方法の場合は、社名のFunctionと同様のものになります。
ですが、区切り文字として使われる可能性がある文字がどれだけあるか・・・
これを考えるくらいなら、後者の数値部分だけを取り出す方法の方が汎用性がありそうです。


  
    
      Function ChangePhoneNumber(ByVal strTel As String) As String

　　Dim i As Integer

　　Dim strTemp As String

　　strTemp = ""

　　For i = 1 To Len(strTel)

　　　　If IsNumeric(Mid(strTel, i, 1)) Then

　　　　　　strTemp = strTemp & Mid(strTel, i, 1)

　　　　End If

　　Next

　　ChangePhoneNumber = strTemp

End Function

Functionの戻り値として、局番の区切りを入れたいと思われるかもしれませんが、これは結構大変です。
電話番号の局番としては、
2,4,4
3,3,4
4,2,4
5,1,4
これらがあり、実例としては、
04-
047-
0475-
こうなると、ロジックではどうしようもないので、
対応するとなると、全国の市外局番を調べて一覧にしなければならなくなります。
市外局番の一覧はネットに転がっているでしょうから、作成できないこともありませんが、その労力に見合う成果があるかどうかは疑問です。

・日付

2017/9/20
2017-09-20
2017.9.20
2017 9 20
2017年9月20日
平成29年9月20日
平成２９年９月２０日
㍻29年9月20日
H29年9月20日
h29年9月20日
h29/9/20
ｈ２９年９月２０日
㍼20年8月15日

日付の表記として、良くありそうなものはこんな感じでしょうか。
じつは上記のうち、VBAが日付と認識しないのは、
2017.9.20
㍻29年9月20日
㍼20年8月15日
この3つだけなんです。
IsDate関数

・IsDate関数・IsDate関数の使用例・Is○○関数一覧

CDate関数

・CDate関数・CDate関数の使用例・日付型 (Date)について・データ型変換関数一覧

この3つ以外は、これらの関数で日付として認識されます。
VBA関数は結構優秀なんです。
従って、この3つのパターンに対応するFunctionを作成すれば良いことになります。


  
    
      Function ChangeDate(ByVal strDate As String) As String

　　strDate = Replace(strDate, ".", "/")

　　strDate = Replace(strDate, "㍾", "明治")

      　　strDate = Replace(strDate, "㍽", "大正")

　　strDate = Replace(strDate, "㍼", "昭和")

　　strDate = Replace(strDate, "㍻", "平成")

　　If IsDate(strDate) Then

　　　　ChangeDate = Format(CDate(strDate), "yyyy/mm/dd")

　　Else

　　　　ChangeDate = strDate

　　End If

End Function

戻り値は日付文字列として、yyyy/mm/ddで戻しています。
Date型にしてしまうと、日付として認識できない文字列の場合に困りますので文字列型にしています。

ちなみに、VBAでは1900年以前も扱えますが、
ワークシートのセルに入れると日付として認識されませんので注意してください。

ここでは、社名、電話番号、日付での名寄せを想定し、これをデータ整形するFunctionのサンプルを提示しました。
名寄せする項目としては他にもいろいろありますが、データ型の分類としは、
・文字列
・数値
・日付
となりますので、代表的な項目を取り上げました。
ここに掲載したVBAを参考に、元データを精査して対応VBAコードを作成してみて下さい。

同じテーマ「マクロVBA技術解説」の記事

VBAでの括弧()の使い方、括弧が必要な場合
 VBAにおけるピリオドとカンマとスペースの使い方
 変数とプロシージャーの命名について
 文字列置換の基本と応用（Replace）
データクレンジングと名寄せ
ForとIfのネストこそがVBAの要点
 For Next の使い方いろいろ
 複数条件判定を行う時のコツ
 ブール型（Boolean）のis変数・フラグについて
 VBAのセキュリティリスクについて

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
データクレンジングと名寄せ

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
データクレンジングと名寄せ