目黒区　データ活用チームの武山です！

仕事でExcelを使えるようになった、と思えるのって、やっぱりVLOOKUP関数がわかった頃あたりから、ですかね。

データの取り出し→集計には必須の関数ですが、普段データ入力や書類作成だけをしている人にとって、初級から中級へステップアップするにあたっての高い壁になっているのではないでしょうか。そこが理解できたときの達成感は大きいですよね。

また、Excelはバージョンアップのたびに新しく便利な関数を搭載してくれるので、最新の使いこなせたらとても便利ですよね。

ただ、組織で働いていると、いろいろな事情もあって、便利な機能を使えない場面があることも事実です。

今回は、そんな状況をAIを活用して乗り切ろう！という取組を紹介します。

1.Excel関数、使いたくても使えない事情

ある日の場面。

Aさんが、Excelとにらめっこしています。

自治体別のデータを、VLOOKUP関数を使って必要なぶんだけ取り出しリスト化したいのですが、キーとなる項目（自治体コード）の列が、左側にないので困っている様子。

「よし！こんなときは、こう！」

と、意を決して、キーとなる自治体コードの列を、切り取り＆貼り付けして、左側に移動させようとするAさん。

Aさんが最終的にやりたいことを画像で説明しますと。

それを見たExcelに詳しい新採用のBさんが、すかさずツッコミます。

Bさん「ちょっと待って！元データを直接加工すると何がなんだか分からなくなりますし、データの行がズレたり、データの入っているセルを消してしまったり、関数が入っていたらエラーになったりする可能性がありますよ！あぶないです！」

Aさん「え、でも、VLOOKUP関数で参照したい項目が、一番左の列にないとデータを抽出できなくて…」

Bさん「そんなときはXLOOKUP関数を使うといいですよ！キー項目が左側になくてもVLOOKUPと同じようにデータ抽出できます！最新のExcelは便利ですからね」

Aさん「あ…、うちの職場で使っているの、Excel 2016なんで…」

ふたり「……」

完。

なんて状況、あるあるなのではないでしょうか？

実際は、WEB検索や書籍で便利な関数を見つけたけれども、自分の職場で使っているExcelでは使えなくてガックリ、というケースも多いですよね。

2.Excelのカスタム関数（ユーザー定義関数）を活用しよう

しかし、ここで諦めていたら現代人ではありません。

自分の使うExcelにない関数は、作ればいいのです。

Excelには「カスタム関数」という機能があります。世間では「ユーザー定義関数」と呼ばれることも多いです。

ExcelVBA（マクロ）でコードを書いて、それをオリジナル関数として実行させる、という、普段であれば非常にハードルの高い上級者向けの機能なのですが。

AI、ChatGPTの助けを借りて、実現してしまいましょう！

ここから先、やや専門的な内容が続きますが、やることは単純なので、VBAなんて触ったことないよ！という方も、ひとまず読んでみてください。

（なお、「INDEX／MATCH関数で同じこと出来るよ！」と声を上げずにはいられないExcel好きの方、今回はVLOOKUP覚えたての初心者にもわかりやすく、XLOOKUPの再現で行かせてくださいね）

3.VBAのコードはAIに書いてもらおう

1.プロンプト

いつものようにシンプルなプロンプトです。

これをChatGPTに投げかけると、コードがすぐに出力されました。

（それにしても、「作りたい」という願望しか伝えていないのに、AIはこのプロンプトから”おまえに作って欲しい”というハイコンテクストなメッセージを読み取って（？）くれているわけで、コミュニケーション能力がかなり高いですね）

2.AIが書いてくれたコード

Function XLOOKUP_Emulated(Lookup_Value As Variant, Lookup_Array As Range, Return_Array As Range, _
    Optional If_Not_Found As Variant, Optional Match_Mode As Integer, Optional Search_Mode As Integer)

    Dim rng As Range
    Dim arr As Variant
    Dim col As Integer
    Dim row As Long

    arr = Lookup_Array
    For row = 1 To UBound(arr, 1)
        For col = 1 To UBound(arr, 2)
            If arr(row, col) = Lookup_Value Then
                XLOOKUP_Emulated = Return_Array.Cells(row, col).Value
                Exit Function
            End If
        Next col
    Next row

    If Not IsMissing(If_Not_Found) Then
        XLOOKUP_Emulated = If_Not_Found
    Else
        XLOOKUP_Emulated = CVErr(xlErrNA)
    End If

End Function

無事コードが出力されました。

ちなみに「エミュレート」とは、「既にある仕組みを模倣し、代替として動作させること」を意味します。

このコードでも望み通りの動きをしてくれるのですが…。
VBAにそこまで明るくない私から見ても、結構ムダな記述が多い気がします。

例えば、「Match_Mode」、「Search_Mode」という変数が宣言されていますが、コードの中では使われていませんね。

せっかくなのでコード自体もブラッシュアップしてみましょう。
VBAの知識が足りなくても大丈夫です。AIに助けてもらえばいいので。

3.AIが修正したコード

問題点を指摘してくれるプロンプトを投げかけます。

出力結果を確認してみます。

Function XLOOKUP_Emulated(Lookup_Value As Variant, Lookup_Array As Range, Return_Array As Range, _
    Optional If_Not_Found As Variant, Optional Match_Mode As Integer)
    Dim arr As Variant
    Dim i As Long
    Dim IsFound As Boolean
    IsFound = False
    If Lookup_Array.Cells.Count = 1 Then
        arr = Array(Lookup_Array.Value)
    Else
        arr = Lookup_Array.Value
    End If
    For i = 1 To UBound(arr)
        If arr(i, 1) = Lookup_Value Then
            XLOOKUP_Emulated = Return_Array.Cells(i, 1).Value
            IsFound = True
            Exit Function
        End If
    Next i
    If Not IsFound Then
        If Not IsMissing(If_Not_Found) Then
            XLOOKUP_Emulated = If_Not_Found
        Else
            XLOOKUP_Emulated = CVErr(xlErrNA)
        End If
    End If
End Function

どこをどう修正したのかも確認しましょう。

ChatGPTからの回答はこちら。

修正内容について、詳しい説明は割愛しますが、「今のままでも動かなくはないけど、より正確かつスマートな記述に直したよ」ということです。

VBAの勉強をされている方は、修正内容を今後の参考にしてみてください。

4.AIに作ってもらったコードをExcelで使ってみた

XLOOKUP関数を使いたいExcelファイルを開いて、「Alt」＋「F11」のショートカットキーでVBE画面を展開し、標準モジュールを作成します。

モジュールの画面へ、AIに作ってもらったコードを貼り付けます。

このまま、Excelの画面に戻ってみましょう。（「保存」ボタンなどは押さなくてOKです）

セルに「=x」と入力すると、関数の予測変換で、「XLOOKUP_Emulated」という関数が増えているのがわかりますね！

書式の通りに関数式を設定してみましょう。

これで、元データの項目列をいちいち左に寄せなくても大丈夫ですね！

5.使ったプロンプトまとめ

今回使ったプロンプトは、この３つだけですね。
文字数にすると66文字です。

いずれもシンプルかつ直接的なプロンプトとなっています。

こちら側が実現したいこと（やりたいこと）をシンプルに伝えることができれば、AIが望み通りの動きをしてくれる確率が高まるのではないかと思います。

6.注意事項

コードを貼り付けたままExcelファイルを保存しようとすると、警告画面が出ます。

これは、ExcelVBA（マクロ）を設定しているため、ファイル拡張子が普段使っている「.xlsx」ではなく、マクロを含む「.xlsm」でないと保存できませんよ、というものです。

いつも通り「.xlsx」で保存するとカスタム関数の設定は消えてしまう（＝使えなくなる）ので「.xlsm」で保存したくなりますが、マクロを含むExcelファイルが必要以上に増えてしまうことは、セキュリティの観点から望ましくないため、この場限りの一時的な関数として使うことをおすすめします。

もちろん、もう一度VBEからモジュールを作成して設定すれば、再度カスタム関数を使うことが可能です。（アドインとして登録する方法もありますが、こちらもセキュリティの観点からNGという組織も多いかと）

また、出力されたコードをそのまま使うと予期せぬ動きをする場合もありますので、VBAに対する一定の理解も必要です。今回のような一時的に使う関数でトライしつつ「どのようなコードが組まれているかを眺めながら、VBAがどういうものかを知っていく」という使い方がいいのではないでしょうか。

ちなみに、WEBで調べると、GoogleスプレッドシートのGoogle Apps Script（GAS）で出来ることをExcelVBAで再現する、もしくはその逆など、「別のツールでなら出来るんだけど…」という知識を使って、スキルを補完するという使い方をしている方が多くいらっしゃるようです。

AIを活用することによって、リスキリングの幅も広がりそうですね！

ではまた次回に。

（今回サンプルとして活用したデータは、目黒区オープンデータカタログサイトにある、ふるさと納税に関する令和３年度データです。視覚的にわかりやすくするため、一部データを抜粋、置き換えを行っています）

過去の記事はこちらから