Excelアドイン（Script Lab）で正規表現を使おう

前置き

動機は、Excelで気軽に正規表現を使いたい、JavaScriptの勉強がしたい、以上の二点です。もちろんVBAのUDFで正規表現が使えるのは知っていますが、最近社内でVBAへの締め付けが強くなっており、社内環境でリーガルな手段を探していました。

そんなとき見つけたのが、Script LabなるMicrosoft謹製のアドイン。これを使うとどうやらJavaScriptでカスタム関数を作成できるようです。スピルさせることも可能、WEB版Excelでも利用可能とのこと。とりあえずやってみるかという軽いノリで始め、一応は形になったので備忘のために記事にしてみたいと思います。

JavaScriptなんて入門編の講座しかやったことがないのにね。ばかですねー。

重要な前提・ルール

この記事では正規表現そのものについての解説はおこないません
Script Labの利用方法については触れません
利用方法等の解説はこちらの記事が分かりやすかったです
Script Labで関数を作成するためには以下の注意が必要です
- 冒頭のコメントに必ず@customfunctionを付ける
- 関数の引値にセル範囲を指定する際には@param {any[][]} arrayのようにで二次元配列であることを明示する
- 返り値をスピルさせたいときは冒頭のコメントに@returns {boolean[][]}のような記述を行い、で二次元配列であることをを明示する
- nullを出力しようとするとエラーになる
- ジャグ配列をExcelにスピルさせようとするとエラーになる
- Officeのバージョンによる制限があります
  Excel カスタム関数は現在、次ではサポートされていません。
  ・Office on iPad
  ・ボリュームライセンスの永続的なバージョンの Office 2019 以前
  ・永続的な Office 2013

REGEX_TEST…正規表現のTESTメソッド

/**
 * @customfunction
 * @param {string[][]} array
 * @param {boolean} [ignoreoption]
 * @returns {boolean[][]} A dynamic array with multiple results.
 */
function REGEX_TEST(array, pattern, ignoreoption) {
  const flags = ignoreoption ? "i" : "";
  const myregexp = new RegExp(pattern, flags);
  return array.map((subArray) => subArray.map((value) => myregexp.test(value)));
}

こちらは、判定対象の文字列に正規表現が含まれるか否かをTRUE/FALSEで返すTESTメソッドです。アルファベットの大文字と小文字を区別しないオプションは、三つ目の引数にTRUEを入力すれば使用可能です。

Excelのセルの値は二次元配列として認識されているため、配列を単純にmapで処理するのではなく、mapをネストして、子配列の各要素に対して正規表現のtestを行う必要があります。

引数の設定を@param {string[][]} array、戻り値を@returns {boolean[][]}としており、セル範囲を引数にすると返り値もスピルする仕様になっています。

スピルもできてますね。FILTER関数の引数に使いたかったこれはかなり便利そうです。

REGEX_REPLACE…正規表現のREPLACEメソッド

/**
 * @customfunction
 * @param {string[][]} array
 * @param {boolean} [ignoreoption]
 * @returns {string[][]} A dynamic array with multiple results.
 */
function REGEX_REPLACE(array, pattern, replacement, ignoreoption) {
  const flags = ignoreoption ? "gi" : "g";
  const myRegexp = new RegExp(pattern, flags);
  return array.map((subArray) => subArray.map((value) => value.replace(myRegexp, replacement)));
}

こちらは、対象の文字列から正規表現と一致するものを置換するREPLACEメソッドです。

アルファベットの大文字と小文字を区別しないオプションは、四つ目の引数にTRUEを入力すれば使用可能です。JavaScriptの正規表現は何も指定しないと正規表現に合致する一つ目のみを置換する仕様になっています。私個人の業務範囲においては、常に正規表現に一致するすべての文字列を置換したかったので、gオプションのフラグをデフォルトで設定しています。

Excelのセルの値は二次元配列として認識されているため、配列を単純にmapで処理するのではなく、mapをネストして、子配列の各要素に対して正規表現のREPLACEを行う必要があります。

引数の設定を@param {string[][]} array、戻り値を@returns {string[][]}としており、セル範囲を引数にすると返り値もスピルする仕様になっています。

キャプチャもできてますね。ぐっど。

REGEX_MATCH…正規表現のMATCHメソッド

/**
 * @customfunction
 * @param {string[][]} array
 * @param {boolean} [ignoreflag]
 * @param {boolean} [globalflag]
 * @returns {string[][]} A dynamic array with multiple results.
 */
function REGEX_MATCH(array, pattern, ignoreflag, globalflag) {
  const flags = `${ignoreflag ? "i" : ""}${globalflag ? "g" : ""}`;
  const myregexp = new RegExp(pattern, flags);
  const threeDimensionalArray = array.map(function(subArray) {
    return subArray.map(function(value) {
      return value.match(myregexp) || [""];
    });
  });
  const twoDimensionalArray = [].concat(...threeDimensionalArray);
  const lengthTwoDimentionalArray = twoDimensionalArray.map((subArray) => subArray.length);
  const maxLength = lengthTwoDimentionalArray.reduce((a, b) => (a > b ? a : b));
  for (const subArray of twoDimensionalArray) {
    while (subArray.length < maxLength) {
      subArray.push("");
    }
  }
  const lengthArray = array.map((subArray) => subArray.length);
  const selectedColumns = lengthArray.reduce((a, b) => (a > b ? a : b));
  let result;
  if (selectedColumns == 1) {
    result = twoDimensionalArray;
  } else if (selectedColumns > 1 && globalflag) {
    result = [["gオプション使用時はarrayの列数を1にしてください"]];
  } else {
    let oneDimensionalArray = [].concat(...twoDimensionalArray);
    result = [];
    for (let i = 0; i < twoDimensionalArray.length; i += selectedColumns) {
      result.push(oneDimensionalArray.slice(i, i + selectedColumns));
    }
  }
  return result;
}

こちらは対象の文字列から、正規表現に一致する文字列を抽出するMATCHメソッドです。

アルファベットの大文字と小文字を区別しないオプションは、三つ目の引数にTRUEを入力すれば使用可能です。また通常のMATCHメソッドは正規表現に一致した最初の文字列のみを抽出しますが、グローバルオプションをオンにすると正規表現に一致するすべての文字列を抽出できます。後述しますが、このグローバルオプションをスピルに組み込もうとして大変面倒なことになりました。

Excelのセルの値は二次元配列として認識されているため、配列を単純にmapで処理するのではなく、mapをネストして子配列の各要素に対して正規表現MATCHを行う必要があります。

正規表現の結果の返り値は配列ですが、一致するものがなかった場合にはnullが返ります。一致か不一致かで配列の深さが変わってしまい後の処理で困るため、論理和||を用いて正規表現が一致する場合には配列を、不一致だった場合には[""]を返す処理にしています。(対象の箇所：value.match(myregexp) || [""]) なお、""ではなくnullにしようとしたところエラーが出ました。

正規表現をmapで処理した結果、三次元配列になっております。Excelに出力するためには二次元配列にする必要があるのですが、Script Labではflatを行おうとするとエラーが発生します。

flat() メソッドは、すべてのサブ配列の要素を指定した深さで再帰的に結合した新しい配列を生成します。
出典：Array.prototype.flat()

flat()はやや新しめのものらしいのでその関係かもしれないですが、使えないものは仕方がありません。そこで、const twoDimensionalArray = [].concat(...threeDimensionalArray);として三次元配列を二次元配列に再構成しました。

重要な前提に書きましたが、Excelではジャグ配列（要素数の異なる配列を入れ子にした配列…？）を出力しようとするとエラーが出ます。グローバルオプションを使用した場合、正規表現の結果として何個の文字列が出力されるかは条件によって変化します。そのため、const maxLength = lengthTwoDimentionalArray.reduce((a, b) => (a > b ? a : b));で二次元配列中の子配列の要素最大値を求めたのち、for (const subArray of twoDimensionalArray) {while (subArray.length < maxLength) {subArray.push("");}}として、要素数が最大値に満たない子配列には""を加えて要素数を揃える処理を実施しました。

最終的な結果は下記のように三つの処理に分岐させています。

カスタム関数の引数の範囲が一列の際には、特別な処理は行いません。
正規表現の結果の三次元配列⇒二次元配列に変換⇒Excelの仕様に合わせるために子配列の要素数を整合させたtwoDimensionalArrayを出力します。グローバルオプションがTRUEの場合には、正規表現に一致した結果が横方向にスピルします。
引数範囲が一列の場合
カスタム関数の引数の範囲が二列以上かつグローバルオプションがTRUEの場合、元の引数の範囲が二次元配列になっているので三次元目のMATCH結果を出力できません。縦にも横にもスピルしているので、どこにも出力できないのです。配列の入れ子を表現できないExcelの限界ですね。どうしようもないのでresult = [["gオプション使用時はarrayの列数を1にしてください"]]としてエラー出力させました。
引数範囲が二列かつgオプションがTRUE
カスタム関数の引数の範囲が二列以上かつグローバルオプションがFALSEの場合の処理です。ここまで処理した結果として、二次元配列のtwoDimensionalArrayは[["a"],["b"],["c"],["d"]…]という形式になっています。カスタム関数の引数の範囲が一列であれば問題がないのですが、引数の範囲が二列以上である場合には、もとの引数の範囲が何列であろうと強制的に一列で縦にスピルダウンしてしまうのです。そこで、const lengthArray = array.map((subArray) => subArray.length)でカスタム関数の引数としたarrayの子配列の要素数=セル範囲の列数を配列として取得、const selectedColumns = lengthArray.reduce((a, b) => (a > b ? a : b));でその配列の最大値(=最終的に返す配列の列数)を取得します。その後、最終的にlet oneDimensionalArray = [].concat(...twoDimensionalArray)で二次元配列をいったん通常の配列に戻し、for分で元のセル範囲の列数になるように二次元配列に再構成します。(for (let i = 0; i < twoDimensionalArray.length; i += selectedColumns) { result.push(oneDimensionalArray.slice(i, i + selectedColumns)))この処理を終えると、やっとのことでExcelに元の引数の範囲にMATCHの結果をきれいにスピルさせることができます。
複数列選択、グローバル：オフ、

一見したところ一番簡単そうな、複数列のグローバルオプションなしパターンが一番面倒という落ちでした。

ChatGPTの威力

ここまでわかったようなまとめ記事を書いておりますが、私自身はPower BIやSQLを多少嗜む程度、ちゃんとしたコードなど書いたことがありません。慣れている方にとっては大したことないレベルなのでしょうが、今回のカスタム関数の作成は私にとってはなかなかの難題でした。Google検索でそれらしい単語検索を行っても解決策にたどりつけず、何度も行き詰まっております。しかし、そこで突破口になったのがChatGPTです。

権利問題の詳細は把握していませんが、WEB上にある膨大なコードを学習した結果としてか、ChatGPTに書きたい内容を細かく指示するともののそれほど時間をおかずにおおよそのコードの書き方が返ってきます。2023年2月時点において、ChatGPTの回答は怪しいものがそれなりにあるのも事実ですが、短めのコードであればかなり的確な回答を示してくれるようです。多分ChatGPTのサポートがなかったらこの関数を作るのにかなりの時間がかかっていたと思われます。

今回カスタム関数を作るにあたって、わかる範囲は自分なりに書いて満足していました。ただ、もっと短くならないのかなという漠然とした思いのもとでChatGPTに自分で書いたコードのリファクタリング指示を出すと、かなりすっきりしたコードを返してくれるんですよね。まだまだ勉強中ゆえ、こんな機能があるんだという新しい発見であったり、効果的な書き方を知ることができました。リファクタリングの結果を熟読して調べてというのを繰り返すしていけば、学習効果はけっこう高そうな気がします。

カスタム関数の不具合等

Script Labのカスタム関数機能自体がプレビュー版ゆえ仕方のないことですが、下記のような不具合がありました。

正規表現の戻り読みは不可
Script Labが対応しているJavaScriptのバージョンに関するドキュメントは見つけられませんでしたが、戻り読みは使えないようです。先読みは可能です。Flatが使えないケースにもありましたが、JavaScriptに新しく追加された機能は使えないようです。
WEB版Excelでの引数セル範囲上限
Script Lab特有なのかは定かではありませんが、引数のセル範囲が1万を超えるとエラーが発生します。頑張ってはいるようですが、WEB版はまだ発展途上の感が強いですね。WEB版リーガルな正規表現の関数を作ろうと意気込んでいたので少しがっかりです。デスクトップ版でやればいいだけですけど。
カスタム関数を LAMDAに組み込むとエラー
MAPなどのLAMBDAのヘルパー関数に組み込んでスピルさせようとしたところ、対応していないようでエラーが発生しました。