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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日: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日
㍻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関数
IsDate関数は、式を日付に変換できるかどうかを調べ、結果をブール型(True,False)で返します。IsDate関数 StrConv(string,conversion,LCID) expression は必ず指定します。日付や時刻として認識できる日付式または文字列式を含むバリアント型(Variant)の式を指定します。
CDate関数
CDate関数は、引数の数値または文字列をDate型(日付型)に変換します。CDate関数 CDate(expression) 引数expressionには任意の文字列式または数式を指定します。この引数は必ず指定します。
この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技術解説」の記事

If条件式のいろいろな書き方:TrueとFalseの判定とは
VBAでの括弧()の使い方、括弧が必要な場合
VBAにおけるピリオドとカンマとスペースの使い方
変数とプロシージャーの命名について
文字列置換の基本と応用(Replace)
データクレンジングと名寄せ
ForとIfのネストこそがVBAの要点
For Next の使い方いろいろ
複数条件判定を行う時のコツ
VBAの省略可能な記述について


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

トランザクション処理|SQL入門(12月11日)
インデックスを作成して高速化(CREATE INDEX)|SQL入門(12月9日)
他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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



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