ここでは Offset × 条件分岐 × Replace × Split × Format × IfEmpty を組み合わせて「入力データを検証 → 不要文字を置換 → 分割 → フォーマット統一 → 欠損補完」まで一気通貫で処理する複合黄金パターンをまとめます。
これにより、売上表・勤怠表・在庫表などの 複合データクレンジング＋正規化＋補完 を自動化できます。
基本パターン 10選
1. 氏名を分割＋空欄補完＋ProperCase整形
Dim r As Range, parts As Variant
For Each r In Range("A2:A20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "未入力"
r.Offset(0, 2).Value = "未入力"
ElseIf InStr(r.Value, " ") > 0 Then
parts = Split(Trim(r.Value), " ")
r.Offset(0, 1).Value = StrConv(parts(0), vbProperCase)
r.Offset(0, 2).Value = StrConv(parts(1), vbProperCase)
End If
Next r
👉 氏名を「姓」「名」に分割し、空欄は「未入力」で補完。
2. 商品コードを分割＋Replace＋ゼロ埋め
Dim r As Range, parts As Variant
For Each r In Range("B2:B20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "N/A"
r.Offset(0, 2).Value = "0000"
ElseIf InStr(r.Value, "-") > 0 Then
parts = Split(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = UCase(parts(0))
r.Offset(0, 2).Value = Format(parts(1), "0000")
End If
Next r
👉 商品コードを「カテゴリ」「番号」に分割し、欠損は補完。
3. 日付文字列を分割＋Format＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("C2:C20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = Format(Date, "yyyy")
r.Offset(0, 2).Value = Format(Date, "mm")
r.Offset(0, 3).Value = Format(Date, "dd")
ElseIf InStr(r.Value, "/") > 0 Then
parts = Split(r.Value, "/")
r.Offset(0, 1).Value = Format(parts(0), "0000")
r.Offset(0, 2).Value = Format(parts(1), "00")
r.Offset(0, 3).Value = Format(parts(2), "00")
End If
Next r
👉 日付を「年・月・日」に分割し、空欄は今日の日付で補完。
4. 勤怠時刻を分割＋Format＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("D2:D20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "00:00"
r.Offset(0, 2).Value = "00:00"
ElseIf InStr(r.Value, "-") > 0 Then
parts = Split(r.Value, "-")
r.Offset(0, 1).Value = Format(parts(0), "hh:nn")
r.Offset(0, 2).Value = Format(parts(1), "hh:nn")
End If
Next r
👉 勤怠時刻を「開始」「終了」に分割し、空欄は「00:00」で補完。
5. 在庫情報を分割＋Replace＋Format＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("E2:E20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "不明"
r.Offset(0, 2).Value = 0
ElseIf InStr(r.Value, ":") > 0 Then
parts = Split(Replace(r.Value, " ", ""), ":")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = Format(parts(1), "0")
End If
Next r
👉 在庫情報を「商品名」「数量」に分割し、欠損は補完。
6. メールアドレスを分割＋小文字化＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("F2:F20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "unknown"
r.Offset(0, 2).Value = "domain.com"
ElseIf InStr(r.Value, "@") > 0 Then
parts = Split(LCase(r.Value), "@")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
End If
Next r
👉 メールアドレスを「ユーザー名」「ドメイン」に分割し、欠損は補完。
7. 住所を分割＋Trim＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("G2:G20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "不明"
r.Offset(0, 2).Value = "不明"
ElseIf InStr(r.Value, " ") > 0 Then
parts = Split(Trim(r.Value), " ")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
End If
Next r
👉 住所を「都道府県」「市区町村」に分割し、欠損は補完。
8. 電話番号を分割＋Replace＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("H2:H20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "000"
r.Offset(0, 2).Value = "0000"
ElseIf InStr(r.Value, "-") > 0 Then
parts = Split(Replace(r.Value, " ", ""), "-")
r.Offset(0, 1).Value = parts(0)
r.Offset(0, 2).Value = parts(1)
End If
Next r
👉 電話番号を「市外局番」「番号」に分割し、欠損は補完。
9. タグを分割＋Trim＋空欄補完
Dim r As Range, parts As Variant, i As Integer
For Each r In Range("I2:I20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "none"
ElseIf InStr(r.Value, ",") > 0 Then
parts = Split(r.Value, ",")
For i = LBound(parts) To UBound(parts)
r.Offset(0, i + 1).Value = Trim(parts(i))
Next i
End If
Next r
👉 タグを分割し、欠損は「none」で補完。
10. フルパスを分割＋Replace＋ProperCase＋空欄補完
Dim r As Range, parts As Variant
For Each r In Range("J2:J20")
If IsEmpty(r.Value) Then
r.Offset(0, 1).Value = "UnknownFolder"
r.Offset(0, 2).Value = "UnknownFile"
ElseIf InStr(r.Value, "\") > 0 Then
parts = Split(r.Value, "\")
r.Offset(0, 1).Value = StrConv(parts(UBound(parts) - 1), vbProperCase)
r.Offset(0, 2).Value = StrConv(parts(UBound(parts)), vbProperCase)
End If
Next r
👉 フルパスを「フォルダ」「ファイル名」に分割し、欠損は補完。
✅ まとめ
- Offset → 正規化結果を隣列へ展開
- 条件分岐 → 空欄判定・区切り文字判定・数値/日付/文字列の型判定を組み合わせて安全に処理
- Replace → 不要文字や表記揺れを削除・置換（例：全角スペース→半角、NG→※）
- Split → 複合データを分割して正規化（氏名・商品コード・日付・時刻・住所・タグなど）
- Format → 数値・日付・文字列を統一フォーマット化（ゼロ埋め、yyyy/mm/dd、hh:nn、#,##0 など）
- IfEmpty → 欠損データを補完（未入力、0、DEFAULT、今日の日付など）
この複合黄金パターンの強み
- 検証＋整形＋補完＋正規化 を一括で処理できるため、実務でのデータ品質を一気に改善
- 柔軟性：条件分岐で「空欄なら補完」「入力済みなら整形」と切り替え可能
- 汎用性：売上表・勤怠表・在庫表・顧客リストなど、あらゆる業務データに適用可能
- 再利用性：Replace・Split・Format・IfEmpty の組み合わせをテンプレ化すれば、どんなデータでも即対応
💡 この「データ検証＋正規化 × Offset × 条件分岐 × Replace × Split × Format × IfEmpty」パターン集を使えば、 データクレンジング → 欠損補完 → 正規化 → フォーマット統一 を一瞬で自動化できます。
