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活用など、深く調べてみると、マクロ以外にも代替手段があることがわかります。

これからの業務効率化には、最終的な目的(成果物)にあわせ、どの手段(ツール)を活用していくか、その選球眼を磨いていくことが求められます。