ExcelマクロVBA技術解説 | データクレンジングと名寄せ | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2017-10-20

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

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

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

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


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

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


・社名

株式会社 エクセルの神髄
(株)エクセルの神髄
(株) エクセルの神髄
潟Gクセルの神髄

当然ですが、これらの文字列比較(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日
~29年9月20日
H29年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関数
CDate関数
この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コードを作成してみて下さい。




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

マクロとは、VBAとは
コーディングとデバッグ
オブジェクトとプロパティの真実
WorksheetFunctionについて
RangeとCellsの深遠
Offset、Resizeを使いこなそう
マクロ作成後に、表位置がずれた場合の対処

新着記事 ・・・新着記事一覧を見る

ScreenUpdating=False時にエラー停止後にシートが固まったら|ExcelマクロVBA技術解説(11月21日)
データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.変数とデータ型(Dim)|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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

    ↑ PAGE TOP