ChatGPTで、Excelにない関数を作ろう！たった66字のプロンプトの魔力
目黒区 データ活用チームの武山です！
仕事でExcelを使えるようになった、と思えるのって、やっぱりVLOOKUP関数がわかった頃あたりから、ですかね。
データの取り出し→集計には必須の関数ですが、普段データ入力や書類作成だけをしている人にとって、初級から中級へステップアップするにあたっての高い壁になっているのではないでしょうか。そこが理解できたときの達成感は大きいですよね。
また、Excelはバージョンアップのたびに新しく便利な関数を搭載してくれるので、最新の使いこなせたらとても便利ですよね。
ただ、組織で働いていると、いろいろな事情もあって、便利な機能を使えない場面があることも事実です。
今回は、そんな状況をAIを活用して乗り切ろう！という取組を紹介します。
1.Excel関数、使いたくても使えない事情
ある日の場面。
Aさんが、Excelとにらめっこしています。
自治体別のデータを、VLOOKUP関数を使って必要なぶんだけ取り出しリスト化したいのですが、キーとなる項目（自治体コード）の列が、左側にないので困っている様子。
「よし！こんなときは、こう！」
と、意を決して、キーとなる自治体コードの列を、切り取り＆貼り付けして、左側に移動させようとするAさん。
Aさんが最終的にやりたいことを画像で説明しますと。
それを見たExcelに詳しい新採用のBさんが、すかさずツッコミます。
Bさん「ちょっと待って！元データを直接加工すると何がなんだか分からなくなりますし、データの行がズレたり、データの入っているセルを消してしまったり、関数が入っていたらエラーになったりする可能性がありますよ！あぶないです！」
Aさん「え、でも、VLOOKUP関数で参照したい項目が、一番左の列にないとデータを抽出できなくて…」
Bさん「そんなときはXLOOKUP関数を使うといいですよ！キー項目が左側になくてもVLOOKUPと同じようにデータ抽出できます！最新のExcelは便利ですからね」
Aさん「あ…、うちの職場で使っているの、Excel 2016なんで…」
ふたり「……」
XLOOKUP関数 は Excel 2016 および Excel 2019 では使用できません
完。
なんて状況、あるあるなのではないでしょうか？
実際は、WEB検索や書籍で便利な関数を見つけたけれども、自分の職場で使っているExcelでは使えなくてガックリ、というケースも多いですよね。
2.Excelのカスタム関数（ユーザー定義関数）を活用しよう
しかし、ここで諦めていたら現代人ではありません。
自分の使うExcelにない関数は、作ればいいのです。
Excelには「カスタム関数」という機能があります。世間では「ユーザー定義関数」と呼ばれることも多いです。
ExcelVBA（マクロ）でコードを書いて、それをオリジナル関数として実行させる、という、普段であれば非常にハードルの高い上級者向けの機能なのですが。
AI、ChatGPTの助けを借りて、実現してしまいましょう！
ここから先、やや専門的な内容が続きますが、やることは単純なので、VBAなんて触ったことないよ！という方も、ひとまず読んでみてください。
（なお、「INDEX／MATCH関数で同じこと出来るよ！」と声を上げずにはいられないExcel好きの方、今回はVLOOKUP覚えたての初心者にもわかりやすく、XLOOKUPの再現で行かせてくださいね）
3.VBAのコードはAIに書いてもらおう
1.プロンプト
XLOOKUP関数と同じ役割を持つユーザー定義関数を作りたい
いつものようにシンプルなプロンプトです。
これをChatGPTに投げかけると、コードがすぐに出力されました。
（それにしても、「作りたい」という願望しか伝えていないのに、AIはこのプロンプトから”おまえに作って欲しい”というハイコンテクストなメッセージを読み取って（？）くれているわけで、コミュニケーション能力がかなり高いですね）
2.AIが書いてくれたコード
XLOOKUP関数をエミュレートするVBAコードは以下の通りになります。
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からの回答はこちら。
1. Forループ内での値の取り出し方法を修正しました。元のコードでは"arr(i)"となっていましたが、これは一次元配列から値を取り出す正しい方法です。しかし、このコードで使用されている配列は2次元配列なので、値を取り出すためには"arr(i, 1)"と指定する必要があります。
2. `Search_Mode`と`Match_Mode`のオプションを削除しました。これらはExcelのXLOOKUP関数内で使用される引数ですが、この自作VBA関数では使用されていませんでした。
3. 元のコードでは、見つからなかった場合にエラーメッセージが表示されるようになっていましたが、これを修正して指定された要素が見つからなかった場合に、If_Not_Foundの値を返すようにしました。「IsMissing(If_Not_Found)」によりIf_Not_Foundが指定されていない場合はエラー値を返すようにしました。
修正後のコードで、ExcelのXLOOKUP関数と同様の機能を持つVBAの関数を作成できるようになりました。
修正内容について、詳しい説明は割愛しますが、「今のままでも動かなくはないけど、より正確かつスマートな記述に直したよ」ということです。
VBAの勉強をされている方は、修正内容を今後の参考にしてみてください。
4.AIに作ってもらったコードをExcelで使ってみた
XLOOKUP関数を使いたいExcelファイルを開いて、「Alt」＋「F11」のショートカットキーでVBE画面を展開し、標準モジュールを作成します。
モジュールの画面へ、AIに作ってもらったコードを貼り付けます。
このまま、Excelの画面に戻ってみましょう。（「保存」ボタンなどは押さなくてOKです）
セルに「=x」と入力すると、関数の予測変換で、「XLOOKUP_Emulated」という関数が増えているのがわかりますね！
書式の通りに関数式を設定してみましょう。
=XLOOKUP( 検索値 , 検索範囲 , 戻り配列 , [見つからない場合] , [一致モード] , [検索モード])
これで、元データの項目列をいちいち左に寄せなくても大丈夫ですね！
5.使ったプロンプトまとめ
今回使ったプロンプトは、この３つだけですね。
文字数にすると66文字です。
XLOOKUP関数と同じ役割を持つユーザー定義関数を作りたい
このコードのおかしなところ、冗長なところを修正して
どこを修正したか教えて
いずれもシンプルかつ直接的なプロンプトとなっています。
こちら側が実現したいこと（やりたいこと）をシンプルに伝えることができれば、AIが望み通りの動きをしてくれる確率が高まるのではないかと思います。
6.注意事項
コードを貼り付けたままExcelファイルを保存しようとすると、警告画面が出ます。
これは、ExcelVBA（マクロ）を設定しているため、ファイル拡張子が普段使っている「.xlsx」ではなく、マクロを含む「.xlsm」でないと保存できませんよ、というものです。
いつも通り「.xlsx」で保存するとカスタム関数の設定は消えてしまう（＝使えなくなる）ので「.xlsm」で保存したくなりますが、マクロを含むExcelファイルが必要以上に増えてしまうことは、セキュリティの観点から望ましくないため、この場限りの一時的な関数として使うことをおすすめします。
もちろん、もう一度VBEからモジュールを作成して設定すれば、再度カスタム関数を使うことが可能です。（アドインとして登録する方法もありますが、こちらもセキュリティの観点からNGという組織も多いかと）
また、出力されたコードをそのまま使うと予期せぬ動きをする場合もありますので、VBAに対する一定の理解も必要です。今回のような一時的に使う関数でトライしつつ「どのようなコードが組まれているかを眺めながら、VBAがどういうものかを知っていく」という使い方がいいのではないでしょうか。
ちなみに、WEBで調べると、GoogleスプレッドシートのGoogle Apps Script（GAS）で出来ることをExcelVBAで再現する、もしくはその逆など、「別のツールでなら出来るんだけど…」という知識を使って、スキルを補完するという使い方をしている方が多くいらっしゃるようです。
AIを活用することによって、リスキリングの幅も広がりそうですね！
ではまた次回に。
（今回サンプルとして活用したデータは、目黒区オープンデータカタログサイトにある、ふるさと納税に関する令和３年度データです。視覚的にわかりやすくするため、一部データを抜粋、置き換えを行っています）
過去の記事はこちらから