ねらい：データの「揺らぎ・欠損・重複・型違い」を一括で正すクレンジングツール
実務では「顧客名の表記揺れ」「日付が文字列」「数値が文字列」「重複行」「空白セル」などが頻出します。これを手作業で直すと膨大な時間がかかり、ミスも増えます。VBAなら“配列I/O＋正規化関数＋重複削除＋欠損補完”を一括で回すテンプレを作れば、毎回同じ品質でクレンジングできます。
入力仕様と共通基盤：配列I/O・正規化・安全変換
シート構成と列の意味
- Data（元データ）: A=日付, B=顧客名, C=商品名, D=数量, E=単価, F=備考
例：日付は文字列や空白が混在、顧客名は全角半角やスペース揺らぎ、数量・単価は文字列数値が混在。
ユーティリティ関数
' ModClean_Base.bas
Option Explicit
Public Function ReadRegion(ws As Worksheet, Optional topLeft As String = "A1") As Variant
ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function
Public Sub WriteBlock(ws As Worksheet, a As Variant, startCell As String)
ws.Range(startCell).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
Public Function NormText(v As Variant) As String
' 全角→半角、Trim、Lower化
Dim s As String: s = CStr(v)
s = StrConv(s, vbNarrow)
NormText = LCase$(Trim$(s))
End Function
Public Function ToNumberOrZero(v As Variant) As Double
If IsNumeric(v) Then ToNumberOrZero = CDbl(v) Else ToNumberOrZero = 0#
End Function
Public Function ToDateOrEmpty(v As Variant) As Variant
If IsDate(v) Then ToDateOrEmpty = CDate(v) Else ToDateOrEmpty = ""
End Function
Public Sub FormatBlock(ws As Worksheet, startCell As String)
With ws.Range(startCell).CurrentRegion
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
End With
End Sub
重要部分の深掘り
- NormTextで「全角→半角」「Trim」「小文字化」を一括。顧客名や商品名の揺らぎを減らします。
- ToNumberOrZeroで文字列数値を安全に数値化。失敗は0に落とす。
- ToDateOrEmptyで文字列日付をDate型に。失敗は空に落とす。これで集計が壊れません。
クレンジング処理：欠損補完・重複削除・型変換
一括クレンジングテンプレ
' ModClean_Process.bas
Option Explicit
Public Sub CleanseData(ByVal sheetName As String, ByVal outStart As String)
Dim ws As Worksheet: Set ws = Worksheets(sheetName)
Dim a As Variant: a = ReadRegion(ws)
Dim lastCol As Long: lastCol = UBound(a, 2)
Dim out() As Variant: ReDim out(1 To UBound(a, 1), 1 To lastCol)
Dim r As Long, c As Long
' ヘッダコピー
For c = 1 To lastCol: out(1, c) = a(1, c): Next
' 重複検知用
Dim seen As Object: Set seen = CreateObject("Scripting.Dictionary"): seen.CompareMode = 1
Dim rows As Long: rows = 1
For r = 2 To UBound(a, 1)
Dim dt As Variant: dt = ToDateOrEmpty(a(r, 1))
Dim cust As String: cust = NormText(a(r, 2))
Dim prod As String: prod = NormText(a(r, 3))
Dim qty As Double: qty = ToNumberOrZero(a(r, 4))
Dim price As Double: price = ToNumberOrZero(a(r, 5))
Dim note As String: note = Trim$(CStr(a(r, 6)))
' 欠損補完（数量・単価が空なら0）
If qty = 0 And Len(CStr(a(r, 4))) = 0 Then qty = 0
If price = 0 And Len(CStr(a(r, 5))) = 0 Then price = 0
' 重複キー（顧客+商品+日付）
Dim key As String: key = cust & "|" & prod & "|" & Format$(dt, "yyyy-mm-dd")
If Not seen.Exists(key) Then
rows = rows + 1
For c = 1 To lastCol
Select Case c
Case 1: out(rows, c) = IIf(IsDate(dt), dt, "")
Case 2: out(rows, c) = cust
Case 3: out(rows, c) = prod
Case 4: out(rows, c) = qty
Case 5: out(rows, c) = price
Case 6: out(rows, c) = note
End Select
Next
seen(key) = True
End If
Next
ReDim Preserve out(1 To rows, 1 To lastCol)
WriteBlock ws, out, outStart
FormatBlock ws, outStart
ws.Columns(1).NumberFormatLocal = "yyyy-mm-dd"
ws.Columns(4).NumberFormatLocal = "#,##0"
ws.Columns(5).NumberFormatLocal = "#,##0"
End Sub
重要部分の深掘り
- 重複は「顧客＋商品＋日付」でキー化し、Dictionaryで一度だけ残す。
- 欠損は0や空に落とす。補完ルールは現場で調整可能。
- 型変換は入口で済ませ、出力は日付型・数値型に統一。これでピボットや集計が壊れません。
例題：売上明細のクレンジング
実行例コード
Sub Demo_RunCleanse()
' DataシートのA1からの表をクレンジングしてZ1に出力
CleanseData "Data", "Z1"
MsgBox "データクレンジングが完了しました。", vbInformation
End Sub
期待動作：Dataシートの元データがZ1開始にクレンジング済み表として出力されます。日付は正しい型に、顧客・商品は正規化され、数量・単価は数値化され、重複行は削除されています。
落とし穴と対策（深掘り）
顧客名の揺らぎで集計が割れる
NormTextで全角半角・スペース・大小を統一。さらにマスタ参照で正式名に置換すると完璧。
日付が文字列で並び替えできない
ToDateOrEmptyでDate型に変換。出力列は必ず日付型に書式設定。
数値が文字列で合計できない
ToNumberOrZeroで数値化。失敗は0に落とす。これでSUMやピボットが壊れません。
重複行が残って二重計上
キー化してDictionaryで一度だけ残す。キー設計は「顧客＋商品＋日付」など業務ルールに合わせる。
セル逐次処理で遅い
配列で処理し、一括書き戻し。10万行でも短時間で完了します。
まとめ：正規化・型変換・欠損補完・重複削除の型で“壊れないデータ”にする
データクレンジングは「入口で正す→欠損補完→重複削除→型統一→書式適用」の流れを一括で回すのが鉄則です。これで集計・分析・ピボットが安定し、毎回同じ品質で処理できます。