root 
前回(「ChatGPTやCopilotで、VBAを学ばなくてもExcel自動化実現」)のテーマで、VBAマクロによるExcel自動化事例をご紹介しました。しかし、そもそも必ずVBAを使わないと、Excel作業は自動化できないんでしたっけ?
賢者の皆さまなら、もうお気づきですよね?
決してそんなことはないんです。
たとえば、VBAマクロでExcelを制御することが必要な自動化メニューとして、2024年現在も、
・タイマー設定(毎日、毎週など、時間をあらかじめ指定して自動起動、自動作業を進めるプログラム)
・ユーザー入力ダイアログボックスの内容によって処理を分岐させるような、インタラクティブ(対話的な)タスク
など、まだいくつもの作業がありますが、これら以外、デスクワーク中に思いつく自動化の多くは、実はVBAを介さなくても実現できるのです。
特に、最近Excelへ標準搭載されるようになった強力な新関数(XLOOKUPやFILTER、GROUPBY)などをうまく使いこなせば、VBAに頼らなくても、かなり高度な作業自動化を実現できるようになります。
今回は、「VBAじゃなくてもよいこと」、「VBAじゃできないこと」について取り上げます。
住所の分解は、VBAマクロを組めば簡単に自動化できますが...
多くの職場で発生するルーチンワークの1つに、「住所の分解」があります。
住所を含む顧客名簿や取引先名簿を、基幹系システムやリレーショナル・データベースなどへインポートするため、「都道府県名」「市区町村名」「それ以降の住所」に分割する作業のことです。
たとえば、
このように、”1セルに1住所”でまとまっている情報が、数100件あると仮定します。
(※この住所情報は、ジェネレーター”住所データのランダム作成”が自動生成したダミーデータで、実在の個人や企業などを特定するものではありません。)
住所分解のためのVBAが書ければ、このような住所データは100件でも1000件でも、数万件でも、簡単に分解することができます。
もし、元の住所で「都道府県」「市区町村」などの間に空白［スペース］があれば、それを区切り位置と判定します。空白がなくても、VBAの正規表現(RegExp)を参照すれば、一発でかなり正確な分解結果を返してくれます。あらかじめ、VBEの［ツール］→［参照設定］ダイアログボックスから、［Microsoft VBScript Regular Expressions 5.5］にチェックを入れて［OK］しておいてくださいね。
この記事で、下記プログラムを1行ずつ解説することはできませんが、これをコピーして、そのままVBEにペースト→実行すれば、ほんの数秒で、目的とする住所分割は完了です。
Sub 分解住所()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") '対象のシート名を設定
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'A列で最後の行を見つける
Dim i As Long
For i = 1 To lastRow
Dim fullAddress As String
fullAddress = ws.Cells(i, 1).Value 'A列の住所を取得
Dim prefectureName As String
Dim cityName As String
Dim restOfAddress As String
' ここで正規表現を使用して住所を分割する
' 正規表現パターンは、実際の住所データに合わせて調整してください
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.IgnoreCase = True
regex.Pattern = "(.?[都道府県])(.?[市区町村])(.*)"
Dim matches As Object
Set matches = regex.Execute(fullAddress)
If matches.Count = 1 Then
prefectureName = matches(0).SubMatches(0)
cityName = matches(0).SubMatches(1)
restOfAddress = matches(0).SubMatches(2)
ws.Cells(i, 2).Value = prefectureName '都道府県名
ws.Cells(i, 3).Value = cityName '市区町村名
ws.Cells(i, 4).Value = restOfAddress 'その他の住所
End If
Next i
End Sub
”市区町村”もしくは”郡”などの文字を含む行政区(「町田市」「郡山市」etc.)では、分割ミスとなることが多々ありますので、作業後の目視チェックが必要です。
Excel関数を複数組み合わせれば、VBAマクロを使わなくても住所分割はできます
マクロで簡単に成果物が得られるとはいえ、すべての職場でマクロOK、というわけではないですよね?
「プログラムを作成する能力」は別として、そもそも、セキュリティ・ポリシーで「マクロ使用禁止」を掲げている企業もあります。
また、職場の標準オフィスソフトが”オンライン版Microsoft 365”だとしたら、オンライン上ではVBEの起動も、.xlsmの保存もできません。
そのような場合には、Excel関数を組み合わせれば、上記VBAマクロと同じ成果物を得ることができます。
たとえば、都道府県名だけを抽出するには、
=IF(LEFT(A1,4)="神奈川県",LEFT(A1,4),IF(LEFT(A1,4)="鹿児島県",LEFT(A1,4),IF(LEFT(A1,4)="和歌山県",LEFT(A1,4),LEFT(A1,3))))
という、LEFT関数とIF関数を入れ子にした関数式を入力してみてください。
国内47都道府県のうち、漢字4文字県は3つ(神奈川県/鹿児島県/和歌山県)なので、いずれかに該当する場合のみ、住所文字列左から4文字を抽出し、そうでない場合には一律で左から3文字を抽出します。これなら、行政区の呼称が、”府”や”道”でも、”都”でも、間違わずに都道府県名を抽出できますね。
同じように、1セル内の文字列から、”市”もしくは”町”という漢字を見つけ、都道府県名(3文字、または4文字)を除き左から”市”もしくは”町”までの文字数をMID関数で抽出すれば、市区町村名だけが残る関数式となります。
=MID(A1,LEN(E1)+1,FIND("市",A1)-LEN(E1))
※〜郡〜町を検索する場合は、”市”を”町”に置き換えてください。
※前節のVBAマクロと同様、「町田市」などのレコードからは、
正確な抽出結果が得られないことがありますので、やはり目視確認は必要となります。 最後に、都道府県名、市区町村名を除いた残りの文字列を書き出せば、分割は完了です。
VBAマクロが使えない自動化? ならば生成AIにお願いしましょう
ここまでは、VBAマクロ”じゃなくても”できる自動化の内容です。しかし、VBAマクロ”じゃ”できない自動化についてはどうしましょうか?
そんなことあるの? いや、あるんです。
たとえば、上記住所録が、顧客の直接入力したデータで、以下のような表記だったらどうでしょう?
VBAマクロや文字列操作関数で、都道府県名や市区町村名なら、分割することはできるでしょう。
しかし、
(1)VBAマクロやExcel関数は”漢数字”を取り扱うことが苦手で、これを他ソフトに取り込める形式の算用数字に、自動変換することができません。
仮に”一 = 1”、”二 = 2”という変換付け合わせ表を作ったところで、では”一”ではなく”壱”と書かれていたら?
上記サンプル・リストでも、”三番町”の”三”は、算用数字”3”に変換するのでしょうか?
しないのでしょうか?
(2)番地の後ろに、建物名が入力されています。
多くのデータベースソフトは、番地と建物名を切り分けて保存する仕様になっていますが、VBAマクロやExcel関数では、建物名を切り分けることはできません。
さらに、”技術者泣かせ”な京都洛中の複雑な住所、”西堀川通御池下る西三坊堀川町”などでは、「御池下る」は建物名ではないの? 住所ではないの?
こんなこと、VBAマクロやExcel関数には、判定することができません。
もうお手上げ...!?
ここで、ChatGPTやCopilotなどの生成AIをうまく活用するのです。
上記サンプル住所を、Copilotのチャット窓にリクエストを入れてみました。
<プロンプト>(Question)
<フィードバック>
さすがに、VBAマクロのように、数万件のデータを一気にCopilotで自動変換することは、今のところできないでしょう。
数百件でも、Copilotは途中でギブアップする可能性が高いと見込まれます。
しかし、目的の成果物に近いデータは、かなり正確にCopilotがフィードバックしてくれていることがわかります。
VBAマクロでもExcel関数でも得られない住所分割の成果物をゲットするには、いまや私たちには「生成AI」という強い味方ができたのです。 ※ただし、この場合もやはり、最終的には担当者が必ず目視チェックをかけましょう。
まとめ:Excel作業自動化の手段は、マクロだけじゃありません
目的のためには、関数や生成AIもうまく活用しましょう
Excelで業務を自動化するには、まずVBAマクロのプログラムを書くことを考えるビジネス・パーソンも多いでしょう。しかし、「マクロ禁止」の環境や、VBAマクロで”できない”データを加工するには、ほかの手段を考えざるをえません。
関数やAI活用など、深く調べてみると、マクロ以外にも代替手段があることがわかります。
これからの業務効率化には、最終的な目的(成果物)にあわせ、どの手段(ツール)を活用していくか、その選球眼を磨いていくことが求められます。
忙しいビジネス・アワーの合間にも、ぜひ少し時間を確保して、新しいツールやソフトをどんどん使ってみることをおすすめします!
