[作って]データを整形するマクロ
【2025年9月】
先日のセミナーで「どんなときマクロを使っているか」の話題になりました。大規模や複雑な処理はもちろんですが、意外と多かったのは、受け取ったデータを"整形"するケースだとか。たとえば、どこかから送られてきたデータが「セル内改行されてた」「セルが結合されてた」「半角と全角が混在していた」「余分なスペースが含まれていた」などなど。そのままでは処理できないので、マクロで一気に整形してから先に進むと。これ実は、マクロの使い方として、とても多いです。いわゆる"小ネタ"ですね。今回は、そんな「データの整形」マクロを、AIに作ってもらいましょう。なお、別のコンテンツとして、同じことをワークシート関数でやる方法も聞いてみるつもりです。
ケース1：半角と全角の混在
セル範囲A2:A5にデータが入力されています。データは「ABC-100」のような形式ですが、"英字", "記号", "数値"の一部が全角で入力されているものがあります。それら「半角と全角が混在」されているデータを、すべて半角に統一して同じ行のB列に代入するVBAのコードを作って。
これ、ときどき出るんですよね。ちなみに何度かPythonで試してみましたが、望むような結果を得られたことは一度もありませんでした(当社比)。こんなんPython使うまでもなく、VBAで簡単にできるっしょ。プロンプトに「Pythonを使わずに」の一言を加えてみます。
セル範囲A2:A5にデータが入力されています。データは「ABC-100」のような形式ですが、"英字", "記号", "数値"の一部が全角で入力されているものがあります。それら「半角と全角が混在」されているデータを、すべて半角に統一して同じ行のB列に代入するVBAのコードを作って。Pythonは使いません！
Sub ConvertToHalfWidth()
Dim i As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
For i = 2 To 5
ws.Cells(i, 2).Value = StrConv(ws.Cells(i, 1).Value, vbNarrow)
Next i
End Sub
StrConv関数は、文字種を変換する関数です。第2引数に指定した定数"vbNarrow"は、全角文字を半角文字に変換しろという指示です。他にも次のような定数を使えます。
| 定数 | 値 | 内容 |
|---|---|---|
| vbUpperCase | 1 | 文字列を大文字に変換します |
| vbLowerCase | 2 | 文字列を小文字に変換します |
| vbProperCase | 3 | 文字列の各単語の先頭の文字を大文字に変換します |
| vbWide | 4 | 文字列内の半角文字を全角文字に変換します |
| vbNarrow | 8 | 文字列内の全角文字を半角文字に変換します |
| vbKatakana | 16 | 文字列内のひらがなをカタカナに変換します |
| vbHiragana | 32 | 文字列内のカタカナをひらがなに変換します |
| vbUnicode | 64 | システムの既定のコードページを使って文字列をUnicodeに変換します |
| vbFromUnicode | 128 | 文字列をUnicodeからシステムの既定のコードページに変換します |
定数vbWideと定数vbNarrowは、国別情報が中国、韓国、日本の場合に適用されます。引数vbKatakanaと引数vbHiraganaは、国別情報が日本の場合に適用されます。
さて、これは想定どおりというか、誰にでもできる簡単な仕事です。ちょっと難易度を上げて「一部の文字種だけ変換」したいときは、どうでしょう。ここでは"英字"と"数値"は変換するけど、"記号"はそのままにしてもらいます。
セル範囲A2:A5にデータが入力されています。データは「ABC-100」のような形式ですが、"英字", "記号", "数値"の一部が全角で入力されているものがあります。それら「半角と全角が混在」されているデータを、"英字"と"数値"だけを半角に変換し、"記号"はそのまま変換しない文字列を、同じ行のB列に代入するVBAのコードを作って。Pythonは絶対に使いません！
Sub ConvertAlphaNumToHalfWidth()
Dim i As Long, s As String, c As String, result As String
Dim code As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
For i = 2 To 5
s = ws.Cells(i, 1).Value
result = ""
For code = 1 To Len(s)
c = Mid(s, code, 1)
' 英字（全角）を半角に
If AscW(c) >= &HFF21 And AscW(c) <= &HFF3A Then
result = result & ChrW(AscW(c) - &HFF21 + &H41)
' 英字（全角小文字）を半角に
ElseIf AscW(c) >= &HFF41 And AscW(c) <= &HFF5A Then
result = result & ChrW(AscW(c) - &HFF41 + &H61)
' 数字（全角）を半角に
ElseIf AscW(c) >= &HFF10 And AscW(c) <= &HFF19 Then
result = result & ChrW(AscW(c) - &HFF10 + &H30)
' 英字・数字（半角）はそのまま
ElseIf (AscW(c) >= &H30 And AscW(c) <= &H39) Or _
(AscW(c) >= &H41 And AscW(c) <= &H5A) Or _
(AscW(c) >= &H61 And AscW(c) <= &H7A) Then
result = result & c
' 記号はそのまま
Else
result = result & c
End If
Next code
ws.Cells(i, 2).Value = result
Next i
End Sub
上記は、AIの提示したコードを、そのまま表示しています。このコードを、そのままモジュールにコピーしてもエラーになります。理由は「&」などの記号です。これは「実体参照」というHTMLのルールです。詳しくは、下記のページをご覧ください。
[作って]条件に一致したセルだけ操作するマクロの「特定の行だけコピーする」パート
コードをコピーするときは、「&」を「&」に、「<」を「<」に、「>」を「>」に置換してください。セルごとに、入力されている文字列を、左から1文字ずつチェックしています。AscW関数は、Unicode文字の文字コードを返します。判定している「&HFF3A」などは、数値の16進表記です。特定の文字種だけ変換すると、このようにメッチャ複雑になります。正規表現を使う手もありますけど、AIは提示してこないでしょうね。これをワークシート関数でやるのも、かなり難解です。にしても、文字種の判定をElseIfでやっているのは、いただけません。ここはDo Loopでしょ。そのためのステートメントでしょ。このような、複数判定をElseIfで書くと、可読性が低下します。お勧めしません。
ケース2：半角スペースと全角スペースの変換
苗字と名前の間に、半角のスペースがあります。この半角スペースだけを全角に変換してもらいましょう。
セル範囲A2:A5にデータが入力されています。データは「田中 亨」や「Bill Gates」のように、姓と名の間が半角スペースで区切られています。この半角スペースを全角スペースに置換した結果を、同じ行のB列に代入するVBAのコードを作って。Pythonには触れないで！
Sub ReplaceHalfSpaceWithFullSpace()
Dim i As Long
For i = 2 To 5
With Worksheets("Sheet2")
.Cells(i, 2).Value = Replace(.Cells(i, 1).Value, " ", " ")
End With
Next i
End Sub
これは簡単すぎましたね。先のStrConv関数を使っちゃうと、英字も全角になっちゃうというのを入れたのですが、引っかからなかったですね。さらに、珍しくWithステートメントを使ってきました。AIはオブジェクト変数が"お好み"のようで、あまり見かけません。じゃ、もっと難易度上げちゃいましょう。
セル範囲A2:A5にデータが入力されています。データは「田中 亨」や「Bill Gates」のように、姓と名の間が半角スペースで区切られています。このうち、姓と名がどちらも日本語の場合だけ、半角スペースを全角スペースに置換した結果を、同じ行のB列に代入するVBAのコードを作って。
日本語の判定は、おそらく先と同じように文字コードをチェックするんだろうな。
Sub ReplaceHalfSpaceWithFullSpace_OnlyJapanese()
Dim i As Long
Dim arr, lastName, firstName
For i = 2 To 5
With Worksheets("Sheet2")
arr = Split(.Cells(i, 1).Value, " ")
If UBound(arr) = 1 Then
lastName = arr(0)
firstName = arr(1)
If IsJapanese(lastName) And IsJapanese(firstName) Then
.Cells(i, 2).Value = lastName & " " & firstName
Else
.Cells(i, 2).Value = .Cells(i, 1).Value
End If
Else
.Cells(i, 2).Value = .Cells(i, 1).Value
End If
End With
Next i
End Sub
Function IsJapanese(str As String) As Boolean
Dim i As Long, ch As String
IsJapanese = True
For i = 1 To Len(str)
ch = Mid(str, i, 1)
If AscW(ch) < &H3000 Or AscW(ch) > &HFFFD Then
IsJapanese = False
Exit Function
End If
Next i
End Function
実行したら、珍しくエラーになりました。
AIは、比較的VBAが得意なので、コードの書き方はともかく、実行してエラーになることは珍しいです。エラーメッセージは上図のとおりです。このメッセージ、ビギナーは心臓に悪いですね。「ByRef引数の～」とのことですが、VBA学習者にとって、ByRefとByValってのは、最も難解な概念のひとつです。簡単に書きます。プロシージャに引数を渡すときは「値渡し(ByVal)」と「参照渡し(ByRef)」があります。値渡し(ByVal)で渡した引数は、その値が渡されますので、受け取ったプロシージャ側で引数を変更しても、元の渡したプロシージャ側では何も変化しません。対して参照渡し(ByRef)で渡すと、実際にはメモリ内への参照が渡されますので、もし受け取ったプロシージャ側で引数を変更してしまうと、元の渡したプロシージャ側でも内容が変わってしまいます。さて、今回のエラーですが、そうした引数の"渡し方"を間違えたのではなく、引数の"型"が違っていました。
上図のように、Finctionの引数は文字列型(String)で定義しています。それなのに、バリアント型の変数を渡しました。だからエラーです。エラー画面の図に、引数の宣言も含めていますので、ご確認ください。ちなみに「IsJapanese(str As String)」は、ByValもByRefも書いていません。省略するとByRefとみなされます。なので正解は、次のように正しく変数宣言をします。
Dim arr As Varinat, lastName As String, firstName As String
引数「arr」は、Split関数の結果を受け取って配列になりますので、バリアント型でなければなりません。「バリアント型って文字列も格納できるんじゃ？」って思った方に、ちょっと補足しますと、バリアント型変数に文字列を代入すると、その変数が文字列型になるのではなく「内部形式が文字列型のバリアント型変数」になります。純粋な文字列型とは異なります。意味の分からない方は、見なかったことにしてください。
ケース3：セル内改行
セル範囲A2:A4にデータが入力されています。データはセル内改行されているものもあります。セル内改行されていなかったら、そのまま同じ行のB列に代入し、もしセル内改行されていたら、セル内改行で区切った前の文字列をB列に、区切った後ろの文字列をC列に代入するようなVBAのコードを作って。
Sub SplitCellByLineBreak()
Dim rng As Range
Dim cell As Range
Dim arr As Variant
Set rng = Range("A2:A4")
For Each cell In rng
If InStr(cell.Value, vbLf) > 0 Then
arr = Split(cell.Value, vbLf)
cell.Offset(0, 1).Value = arr(0)
cell.Offset(0, 2).Value = arr(1)
Else
cell.Offset(0, 1).Value = cell.Value
cell.Offset(0, 2).Value = ""
End If
Next cell
End Sub
正常に動作します。しますけど、何だろう？この感覚はｗ どうして急にFor Each使ってきたんだろ。今までずっとFor Nextだったじゃん。だから「一貫性がない」とか「考え方がブレてる」とか言われるんすよ。そういうとこだよ？ちなみに長年VBAを教えてきましたけど、For Eachが分からないって人はメッチャ多いです。その前に、For Eachで必須のオブジェクト変数も、正しく認識してる人が少ないです。対してFor Nextは丁寧に説明すれば、比較的理解しやすいです。今回の場合、どちらも使えるのですから、より易しいFor Nextが良いでしょうね。また、突然「Range("A2:A4")」みたいに、シートの階層構造を省略してきましたけど、じゃ、今までの無駄な「Set ws = ActiveSheet」は何だったのかと。あ、でも、Offsetプロパティを使ってるのはGJです。B2とかC3などのアドレスを、何とかして文字列結合するような人はAIを見習って、ぜひOffsetの学習をしてくださいな。
ケース4：結合セル
A列のデータを、B列のように変換するVBAのコードを作って
最後は、思い切ってプロンプトを簡素化しました。どうせ、AIはブックの内容を見てるんだろうから、見れば分かるっしょ。
ちっ！
セル範囲A2:A7にデータが入力されています。データのいくつかは"セル結合"されています。もし、3つのセルが結合されたセルに文字列が入力されていたら、その文字列をB列に3行分割して代入するようなVBAのコードを作って。
Sub SplitMergedCellsToColumnB()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet4")
Dim rng As Range
Dim cell As Range
Dim i As Long
Set rng = ws.Range("A2:A7")
For Each cell In rng
If cell.MergeCells Then
If cell.MergeArea.Cells.Count = 3 Then
Dim val As String
val = cell.Value
For i = 1 To 3
cell.Offset(i - 1, 1).Value = val
Next i
End If
Else
cell.Offset(0, 1).Value = cell.Value
End If
Next cell
End Sub
あ、ダメだ。「3セルが結合されてる」って前提のコード作ってきた。もし、って意味分かんないのかな。If...Then...Elseステートメントの"If"ですよ。
セル範囲A2:A7にデータが入力されています。データのいくつかは"セル結合"されています。それぞれのセルで、結合しているセル数は異なります。仮に、3つのセルが結合されたセルに文字列が入力されていたら、その文字列をB列に3行分割して代入するようなVBAのコードを作って。
Sub SplitMergedCellsToColumnB_VarCount()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet4")
Dim rng As Range
Dim cell As Range
Dim i As Long
Set rng = ws.Range("A2:A7")
For Each cell In rng
If cell.MergeCells Then
If cell.Address = cell.MergeArea.Cells(1).Address Then
Dim val As String
val = cell.Value
Dim mergeCount As Long
mergeCount = cell.MergeArea.Cells.Count
For i = 1 To mergeCount
cell.Offset(i - 1, 1).Value = val
Next i
End If
Else
cell.Offset(0, 1).Value = cell.Value
End If
Next cell
End Sub
エラーにはなりませんけど、望んだ結果ではありません。
エラーにはならないけど望む結果にならない、ってのは最も困ります。どこが間違っているのか、コード全体を解析しなければなりません。ちなみに今回のは、かなり難しいです。一見すると、考え方は正しいように見えますよね。この件、詳しく解説するとメッチャややこしいので、すみません、ここでは割愛します。ヒントと修正だけ書きますね。たとえば、セル範囲A2:A4が結合されているとき、Range("A2")のOffset(1, 0)ってRange("A3")じゃなくてRange("A5")になります。これは「Excelの"結合セルに関する"仕様」ですけど、誤動作の原因はこれです。次のようにすると、正しく動作します。
Sub SplitMergedCellsToColumnB_VarCount()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet4")
Dim rng As Range
Dim cell As Range
Dim i As Long
Set rng = ws.Range("A2:A7")
For Each cell In rng
If cell.MergeCells Then
If cell.Address = cell.MergeArea.Cells(1).Address Then
Dim val As String
Dim target As Range
Set target = cell.MergeArea.Cells(1).Offset(0, 1)
val = cell.Value
Dim mergeCount As Long
mergeCount = cell.MergeArea.Cells.Count
For i = 1 To mergeCount
target.Offset(i - 1, 0).Value = val
Next i
End If
Else
cell.Offset(0, 1).Value = cell.Value
End If
Next cell
End Sub
まとめ
今までも、ずっと思っていましたが、今回の検証であらためて「AIは一貫性がない」と感じました。For NextでもFor Eachでも、どっちでもできる。オブジェクト変数でも、Withステートメントでも、どっちでもできる。そんな「どっちでもできる」ってのがプログラミングには多いです。どっちでもいいので、普通は作者が何らかの意図を見いだして選びます。その意図には、作者の"思い"が込められています。AIには"思い"がありません。あるのは"統計的な確率"です。だから、どっちでもいいケースは、ランダムに選ばれます。もちろん、それ自体は間違っていません。ただビギナーは、同じような処理で異なる書き方を見ると「何か重要な意味があるのでは？」って思います。ないんですよ、AIのコードには、そんなの。さらに、たとえば似たような処理で、2種類の書き方が提示されていたら、その2種類とも理解しなければなりません。学習量が2倍です。辛いですよね。とはいえ、AIが提示したコードを精査せず「動けばいいや」って使うと、何かあったら大変です。いやはや、AIを使えば使うほど、人間の学習が必要になってきますね。それが、現在のAIです。