Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – クリーニングテンプレ

Excel VBA Excel VBA
スポンサーリンク

ねらい：どんな元データでも「まずはここを通す」クリーニングの型を持つ

現場のExcelって、きれいなデータの方が少ないですよね。
全角・半角が混ざっている、前後にスペースが付いている、「－」が3種類混在している、空行が紛れている…。

クリーニングテンプレのゴールは、こうです。
「どんな元データでも、まずこのマクロを通せば“JOINや集計に耐えられる状態”まで整う」という“前処理の型”を作ることです。

ここでは、実務でよくあるクリーニングを、再利用しやすい部品としてまとめていきます。

全体設計：1シート単位の「クリーニングパイプライン」を作る

クリーニングの考え方

クリーニングは、バラバラにやると抜け漏れが出ます。
なので、「この順番で流す」というパイプラインにしてしまうのがコツです。

例えば、こんな流れです。

不要な空行を削除する
前後スペースを削る（Trim）
全角・半角を統一する
記号やハイフンを統一する
NULL相当（”NULL”、”-” など）を空白に揃える

これを「1シートに対して一気にかける」テンプレを作っておき、
顧客マスタ、商品マスタ、取引データなど、どのシートにも同じ型で適用できるようにします。

コアとなる「シートクリーニングSub」のイメージ

標準モジュールに、こんなSubを用意します。

Sub CleanCustomerSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer")
    
    RemoveEmptyRows ws, 1          ' A列を基準に空行削除
    TrimRange ws, "A:C"            ' A〜C列の前後スペース削除
    NormalizeHyphen ws, "A:A"      ' 顧客コードのハイフン統一
    NormalizeNullLike ws, "B:C"    ' 顧客名・備考の「NULL」「-」などを空白に
End Sub
VB

ポイントは、「やりたいことごとに小さなSub/Functionに分ける」ことです。
RemoveEmptyRows、TrimRange、NormalizeHyphen、NormalizeNullLike が“クリーニング部品”になります。

コア部品1：空行削除テンプレ

基本の「基準列を見て空行を削除する」Sub

一番よく使うのが、「A列が空ならその行を削除」のようなパターンです。

Sub RemoveEmptyRows(ByVal ws As Worksheet, ByVal keyCol As Long)
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, keyCol).End(xlUp).Row
    
    Dim i As Long
    For i = lastRow To 2 Step -1
        If Trim(CStr(ws.Cells(i, keyCol).Value)) = "" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub
VB

初心者向けにポイントをかみ砕きます。

下から上に向かってループしているのは、「削除で行が詰まる」影響を避けるためです。
keyCol で「どの列を基準にするか」を渡せるようにしておくと、再利用性が高くなります。
Trim で前後スペースを削ってから空かどうかを判定することで、「スペースだけの行」も削除できます。

呼び出し側では、列番号を渡します。

RemoveEmptyRows ThisWorkbook.Worksheets("Customer"), 1   ' A列基準
VB

列記号で渡したい場合は、前に作った ColToNumber と組み合わせてもOKです。

コア部品2：前後スペース削除（Trim）テンプレ

指定範囲の全セルに Trim をかけるSub

文字列の前後にスペースが付いていると、JOINや比較で痛い目を見ます。
これを一気に削るテンプレです。

Sub TrimRange(ByVal ws As Worksheet, ByVal addr As String)
    Dim rng As Range
    Set rng = ws.Range(addr)
    
    Dim c As Range
    For Each c In rng.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = Trim(CStr(c.Value))
        End If
    Next c
End Sub
VB

ここでのポイントは、「必ず文字列に変換してから Trim する」ことです。
数値や日付も一応 Trim できますが、CStr を挟んでおくと型の扱いが安定します。

呼び出し側では、こう書きます。

TrimRange ws, "A:C"    ' A〜C列の前後スペース削除
TrimRange ws, "E:E"    ' E列だけ削除
VB

コア部品3：全角・半角・ハイフンなどの統一テンプレ

ハイフンを統一する関数

顧客コードや郵便番号でよくあるのが、「－」「―」「ー」「-」が混ざっている問題です。
これを全部「-」に揃える関数を作ります。

Function NormalizeHyphenText(ByVal s As String) As String
    Dim result As String
    result = s
    result = Replace(result, "－", "-")
    result = Replace(result, "―", "-")
    result = Replace(result, "ー", "-")
    result = Replace(result, "‐", "-")
    NormalizeHyphenText = result
End Function
VB

これを列全体に適用するSubがこちらです。

Sub NormalizeHyphen(ByVal ws As Worksheet, ByVal addr As String)
    Dim rng As Range
    Set rng = ws.Range(addr)
    
    Dim c As Range
    For Each c In rng.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = NormalizeHyphenText(CStr(c.Value))
        End If
    Next c
End Sub
VB

顧客コード列に対しては、こう呼びます。

NormalizeHyphen ws, "A:A"
VB

全角英数字を半角にする（簡易版）

本格的な全角→半角変換は少し長くなりますが、よく使う「英数字だけ半角にしたい」程度なら、簡易版でも十分です。

Function ToHalfWidthAscii(ByVal s As String) As String
    Dim i As Long
    Dim ch As String
    Dim code As Long
    Dim result As String
    
    result = ""
    For i = 1 To Len(s)
        ch = Mid$(s, i, 1)
        code = AscW(ch)
        If code >= &HFF10 And code <= &HFF19 Then
            result = result & ChrW(code - &HFEE0)   ' 全角数字
        ElseIf code >= &HFF21 And code <= &HFF3A Then
            result = result & ChrW(code - &HFEE0)   ' 全角大文字
        ElseIf code >= &HFF41 And code <= &HFF5A Then
            result = result & ChrW(code - &HFEE0)   ' 全角小文字
        Else
            result = result & ch
        End If
    Next i
    
    ToHalfWidthAscii = result
End Function
VB

これも列全体に適用するSubを用意します。

Sub NormalizeAscii(ByVal ws As Worksheet, ByVal addr As String)
    Dim rng As Range
    Set rng = ws.Range(addr)
    
    Dim c As Range
    For Each c In rng.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = ToHalfWidthAscii(CStr(c.Value))
        End If
    Next c
End Sub
VB

顧客コードや郵便番号など、「英数字は半角に揃えたい」列に対して使います。

コア部品4：「NULLっぽい値」を空白に揃えるテンプレ

「NULL」「-」「N/A」などを空白にする関数

実務データには、「空のつもりで ‘NULL’ や ‘-‘ を入れている」ケースがよくあります。
これを全部「本当の空白」に揃えておくと、後の処理が楽になります。

Function NormalizeNullLikeText(ByVal s As String) As String
    Dim t As String
    t = UCase$(Trim$(s))
    
    Select Case t
        Case "", "NULL", "ＮＵＬＬ", "-", "－", "N/A", "Ｎ／Ａ"
            NormalizeNullLikeText = ""
        Case Else
            NormalizeNullLikeText = s
    End Select
End Function
VB

これを範囲に適用するSubです。

Sub NormalizeNullLike(ByVal ws As Worksheet, ByVal addr As String)
    Dim rng As Range
    Set rng = ws.Range(addr)
    
    Dim c As Range
    For Each c In rng.Cells
        If Not IsEmpty(c.Value) Then
            c.Value = NormalizeNullLikeText(CStr(c.Value))
        End If
    Next c
End Sub
VB

顧客名や備考など、「空白扱いしたい値が紛れている」列に対して使います。

例題：顧客マスタをクリーニングしてからJOINに回す

想定する流れ

Customer シートを、JOINや集計に使う前に、次のように整えます。

A列（顧客コード）を基準に空行削除
A〜C列の前後スペース削除
A列のハイフンと全角英数字を統一
B〜C列の「NULL」「-」などを空白に

コード例

Sub CleanCustomerSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer")
    
    RemoveEmptyRows ws, 1              ' A列基準で空行削除
    TrimRange ws, "A:C"                ' A〜C列の前後スペース削除
    NormalizeHyphen ws, "A:A"          ' 顧客コードのハイフン統一
    NormalizeAscii ws, "A:A"           ' 顧客コードの全角英数字を半角に
    NormalizeNullLike ws, "B:C"        ' 顧客名・備考のNULLっぽい値を空白に
End Sub
VB

JOINマクロの入口では、こう書きます。

Sub JoinCustomer()
    Const MODULE_NAME As String = "JoinCustomer"
    On Error GoTo ErrHandler
    
    CleanCustomerSheet
    
    ' ここからJOIN本処理
    
    Exit Sub
ErrHandler:
    LogError MODULE_NAME, "MAIN", Err
End Sub
VB

「JOINの前に必ず Clean○○ を通す」というルールを決めておくと、
元データの揺れに悩まされる時間が一気に減ります。

重要ポイントの深掘り：クリーニングテンプレを“現場で回る型”にする

クリーニングは「一度に全部」ではなく「部品を組み合わせる」

いきなり完璧なクリーニングを目指すと、終わりません。
大事なのは、「よく出るパターンを小さな部品にしておく」ことです。

空行削除
Trim
ハイフン統一
全角→半角
NULLっぽい値の統一

このあたりを部品化しておけば、「このシートにはどの部品を組み合わせるか」を考えるだけで済みます。

「どの列に何をかけるか」をコメントで残す

CleanCustomerSheet のようなSubには、できるだけ「なぜそれをしているか」をコメントで残しておくと、未来の自分が助かります。

顧客コードはJOINキーなので、ハイフンと全角半角を統一
顧客名・備考はNULLっぽい値を空白にしておく

こういう“意図”を書いておくと、「このクリーニングを外していいかどうか」の判断がしやすくなります。

クリーニング後に「ざっくりチェック」する癖をつける

クリーニングは便利ですが、「やりすぎて必要な情報まで消してしまう」リスクもあります。
なので、クリーニング後に簡単なチェックを入れておくと安心です。

行数が極端に減っていないか
キー列に空白が増えていないか
想定外の文字が残っていないか

これは、簡単な集計やフィルタで目視確認するだけでも十分です。

まとめ：クリーニングテンプレは「全部の前処理をここに集める」ための型

今回のクリーニングテンプレを一言でまとめると、こうです。

シートごとに Clean○○Sub を用意し、その中で
RemoveEmptyRows
TrimRange
NormalizeHyphen / NormalizeAscii
NormalizeNullLike

といった部品を組み合わせる。
JOINや集計などの本処理の前に、必ず Clean○○ を通す——これを“お約束”にする。

これを一度仕組みとして作ってしまえば、
「この元データ、また変なスペース入ってる…」みたいなイライラが、かなり減ります。

スポンサーリンク
タイトルとURLをコピーしました