【Excel】正規表現で一致する部分を抽出するカスタム関数の作り方
2024年5月にExcelのネイティブ関数で正規表現でパターンに当てはまる部分を抽出する関数が実装予定とニュースになりましたね。しかしながら、2024年7月現在、Excelのβ版でのみ使用可能なため業務には使えないという問題があります。そこでJavaScriptでカスタム関数を作る方法を解説します。（第３弾）
0.ScriptLabのインストール方法
ExcelでJavaScriptカスタム関数を作る＆使うにはマイクロソフトが提供する無料アドイン「Scriptlab」がひつようです。インストール方法は別記事に記載したのでご参照ください。
1.正規表現でパターンに当てはまる部分を抽出するコード
先にコードを書くと下のJavaScriptコードになります。JavaScriptにはmatchという機能があり、正規表現で当てはまる部分を抽出してくれるのでこれを使えばいいだけですが、ちょっとExcelに返り値を渡すために工夫が必要になります。
/**
* regex match
* @customfunction
* @returns {string[][]}
*/
function regexmatch(x1, y1) {
const str = x1;
const pattern = new RegExp(y1, 'g');
found = str.match(pattern) || ["null"];
return [found];
}動的配列を返すカスタム関数作る時の注意事項
今回のようなmatchを使う場合、普通は検索条件に当てはまるもの全てを抽出したいのでグローバルフラグをたてて検索をします。しかしながら、そうすると返り値が配列となります。
例えば、「ABCDEFGHIJKLMNOPQRSTUVWXYZ」という文字列から正規表現「/[A-E]/g」でmatchすると大文字のA～Eまでのいずれかの文字がヒットするので返り値は['A’, 'B’, 'C’, 'D’, 'E’]となります。このような返り値は配列といってExcelではそのまま一つのセルに入れることはできないのでエラーを吐き出します。
もちろん、このような配列を扱うケースはjavascriptでは普通なので対策は考えられています。以下の点に注意すれば大丈夫です。
- JSDocに「@returns {string[][]}」を入れる。
こうすることで返り値が動的配列であることが認識される - returnの部分を[]で囲む。
ScriptLabのConsoleLogを見ればわかるが、ScriptLabでは配列でも[]がない状態で扱っているっぽい。なのでreturnでExcelに渡すときは[]で囲まないと上手くデータの受け渡しがいかなくなる。 - Excelで出力するセルの右側や下側に十分な空白セルがある。
検索結果によっては数10個分セルを占領することになるので空白セルが必要となる。あらかじめテーブルとして書籍設定などをしていると空白セルが足りずにエラーを吐き出す場合がある
ちなみに、公式ドキュメントにも動的配列を使う場合のコード例が載っているので、参考にしてみてください。
2.カスタム関数として登録する
カスタム関数として登録する方法は別記事を参照ください。
3.動作確認結果
動作確認結果は以下の画像のようになります。やはり、マッチするものが複数ある場合に右側に余白セルがないとダメなので多数のセルで処理する場合に全てのセルに対して余白セルを用意しておくようにしないとエラーが発生する可能性があることは認識しておきましょう。
