EXCEL VBA エクセルデータの標準化・データの統一・データの整備（テクニック）
EXCEL VBA エクセルデータの標準化・データの統一・データの整備（テクニック）
●はじめに
EXCELシートにデータベースを作成する際に、列ごとに統一された形式で文字列が入力されていないと、プログラムで作成した条件式などで正しく認識されない場合があります。呼び方は同じ文字列で入力されていても、全角と半角では別の文字として認識されてしまいます。また、文字列の間にスペースなどが文字列の間に入力されていても別の文字列として認識されてしまいます。このように、データベースを取り扱う際は、データの標準化を図らなければなりません。このようなデータの標準化作業もVBAプログラムを使う事で簡単に数千・数万のデータを整備する事が出来ます。それでは、EXCELデータの標準化（整備）の方法を順番に説明いたします。
EXCELデータを統一された表記に変える（文字列の置き換え：Replaceメゾット）
●プログラム説明 （サンプルプログラム①）
（画面クリックして拡大）
（画面クリックして拡大）
下記のプログラムは、下表のデータにあるB列「会社名」の㈱・（株）・（カブ）を一括に「株式会社」へ変換するプログラムです。データベースとして統一したデータを作成する際に、㈱・（株）・（カブ）など意味としては同じですが、データ（文字列）で表すと別の意味となってしまいますので、統一した文字列に変換する必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub Standardization01() '株式会社⇒変換 Dim KabuEx As Variant Dim CopData As Range Dim lRow As Long lRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得 Set CopData = Range("B2:B" & lRow) 'B列「会社名」を参照指定 For Each KabuEx In Array("(株)", "㈱", "（カブ）") '変換する文字列の指定「（株）", "㈱", "（カブ）」を「株式会社」へ変換 CopData.Replace what:=KabuEx, replacement:="株式会社" '("(株)", "㈱", "（カブ）") を⇒「株式会社」に統一 Next End Sub |
●実行前～実行後 ※プログラム実行後、B列の会社名の㈱・（株）・（カブ）を⇒統一して「株式会社」に変換しました。
（画面クリックして拡大）
（画面クリックして拡大）
EXCELデータを統一された表記に変える（特定の文字列を消去：Replaceメゾット）
●プログラム説明 （サンプルプログラム②）
（画面クリックして拡大）
（画面クリックして拡大）
下記のプログラムは、下表のデータにあるB列「会社名」の㈱・（株）・（カブ）を一括に「消去」するプログラムです。データベースとして統一したデータを作成する際、余計な文字や記号などがあると特にソートや文字列を並び替えした際に順番にならない不都合が生じます。そのため、余計な文字列や記号を消去する必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub Standardization02() '株式会社⇒消去 Dim KabuEx As Variant Dim CopData As Range Dim lRow As Long lRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得 Set CopData = Range("B2:B" & lRow) ''B列「会社名」を参照指定 For Each KabuEx In Array("(株)", "㈱", "（カブ）", "株式会社") '消去する文字列を指定します。 CopData.Replace what:=KabuEx, replacement:="" '（株）", "㈱", "（カブ）", "株式会社")を⇒ ""(消去) Next End Sub |
●実行前～実行後 ※プログラム実行後、B列の「会社名」にある「㈱・（株）・（カブ）・株式会社」が除去され、社名のみの表示になりました。
EXCELデータを統一された文字列に変える（複数指定した文字列の一括変換・置換：Replaceメゾット）
●プログラム説明 （サンプルプログラム③）
（画面クリックして拡大）
（画面クリックして拡大）
下記のプログラムは、下表のデータにあるB列「書籍名」・C列「Office」に登録されている「（エクセル・ｴｸｾﾙ・excel）」の複数文字列を「EXCEL」（英文字）へ一括へ変換（置換）を行うプログラムです。 黄色枠に文字列を指定し「変換元」と「変換後」の文字列を指定する複数文字列変更を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub Standardization03() '（エクセル・ｴｸｾﾙ・excel） ⇒ EXCEL（英文字）へ変換 Dim ShosekiEx, Henkan As Variant Dim BooksData As Range Dim MojiEX As String Dim lRow As Long lRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得 Set BooksData = Range("B2:C" & lRow) ''B列～C列「書籍名」及び「Office」を参照指定 Set Henkan = Range("E2:E4") '配列に指定（エクセル・ｴｸｾﾙ・excel） MojiEX = Range("F2") 'セルF2：変換する文字列として「EXCEL」英字に指定 For Each ShosekiEx In Henkan '変換する文字列を指定します。 BooksData.Replace what:=ShosekiEx, replacement:=MojiEX, MatchByte:=False '配列に指定した（エクセル・ｴｸｾﾙ・excel）⇒EXCELに変換します。 Next End Sub |
●実行前～実行後 ※プログラム実行後、B列「書籍名」・C列「Office」に登録されている（エクセル・ｴｸｾﾙ・excel） ⇒ EXCEL（英文字）へ一括変換（置換）されました。
EXCELデータの空白を削除（全角・半角「空白・スペースの削除」一括変換：Replaceメゾット）
●プログラム説明 （サンプルプログラム④）
（画面クリックして拡大）
（画面クリックして拡大）
下記のプログラムは、下表のデータにあるB列の「会社名」に社名が登録されている間に空白スペースが入っています。この社名の間のスペースを削除します。スペースを削除する際は、半角スペースと全角スペース両方に対応するプログラムにした方が、一括でスペースが削除されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub Standardization05() '文字列の空白（スペース）を削除 Dim L, I, M, lRow, mRow, xRow As Long Dim MojIEx, RETS As String RETS = InputBox("空白削除したい列指定（A-Z）") '削除したい列を指定します。 lRow = Cells(Rows.Count, RETS).End(xlUp).Row 'データ最終行を取得 For I = 2 To lRow 'データの最終行まで繰り返す。 MojIEx = Cells(I, RETS) '選択したセルの文字列データを取得 MojIEx = Replace(MojIEx, " ", "") '半角スペースを削除 MojIEx = Replace(MojIEx, " ", "") '全角スペースを削除 Cells(I, RETS) = MojIEx '変換後（スペースを削除した文字列データをセルに戻す。） Next I MsgBox "選択した列の空白削除しました。" End Sub |
●実行前～実行後 ※プログラム実行後、B列の「会社名」の間にある、スペースが削除されました。データベースを構築する際は、このように余計なスペースを削除しないと、並び替えや検索する際、正しく動作しない事がありますので、注意してください。
最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。