複数列対応のExcelによるクロス結合の別解

ExcelでREDUCEやらを使ってクロス結合を書いてみました。複数列*複数列でも対応してます。

=LAMBDA(range1,range2, DROP(REDUCE("",SEQUENCE(ROWS(range1)),LAMBDA(seed,num,VSTACK(seed,HSTACK(IF(SEQUENCE(ROWS(range2)),CHOOSEROWS(range1,num)),range2)))),1))

何の工夫もない完全な力業によるゴリ押し感ある数式に。上手い人はもっと短く書けるんでしょうね。

私なんかはこのへんが限界です。

スピルを使ってみよう

(この記事は会計系 Advent Calendar 2023の14日目の記事です。)

会計系アドベントカレンダーの14日目を担当するたきぞうです。

会計系Advent Calendarの企画として何を書いてもよいというお話でしたので、Excelの新しめの機能であるスピルについて書き散らしてみたいと思います。 完全な趣味です。ゴリゴリの実務家のため学術的な内容は一切含まれておりませんが、読まれた方の何らかのインプットになれば幸いです。

目次

はじめに

会計界隈において、Excelの操作は基本中の基本です。データをどれだけ効率的にミスなく捌けるかは非常に重要なスキルの一つです。Excelスキルがあればいいというわけではありませんが、Excelを使えないとお話にならないのもまた事実です。

そんなExcelですが、少し前から「スピル」という機能が実装されました。これは一つのセルに数式を書くと、計算結果が隣接するセルにこぼれるという機能です。聞いただけでは大したことがないように思えますが、スピル以前と以後ではExcelの使い方が劇的に変わりました。スピルの機能を最大限活用することで、これまでにはできなかった加工や集計が、メンテナンスフリーで実現可能になってしまったのです。

このスピルについて基本的な使い方はネット上で多くの解説記事があるものの、あまりにも基本的な内容であったり、X(旧Twitter)では曲芸まがいの投稿が多く、なかなか参考になる情報が少ないように思います。そこで本記事では、スピル機能の基本的な解説と、会計界隈の実務で使えそうなスピルの活用例についての紹介をしたいと思います


※注意事項

本記事は一般的な「Excelができます」のレベルを若干超えた内容になっております。SUMIFSが分からない方はこのページを閉じて基本的な解説サイトを読むことをお勧めします。

本記事で使用しているサンプルデータはChatGPTで雑に生成したものです。細かな点で不整合があるかもしれませんが、本筋には影響しないはずですのでツッコミ無用でお願いいたします。


スピルの基本的な解説

これまでのExcelでは、セルの参照先はセルのみでした 。しかし、スピルが実装されて以降は一つのセルで複数の範囲を参照することが可能になっています。 1

範囲の参照

D2に=B2:B10というセル範囲を指定すると下記のようにD3からD10に数字がこぼれます。これがスピルです。実際に数式が入っているのはD2だけで、D3からD10のセルには何も入力されていません。このスピルしている箇所(D3~D10)はゴーストと呼ばれるもので、D2の数式の結果がスピルしています。

まずは一番基本的な使い方

では、この数字がこぼれている場所に値を入れるとどうなるでしょうか?その結果がこちらです。本来ならば数値をスピルさせたいのに、数字が入力されてしまっているのでエラーが表示されてしまいます。スピルを使う場合、結果によってはスピル範囲が縦または横方向にかってに伸びるので、数式がスピルする範囲を想定してシートを作っていく必要があります。

スピルを邪魔してみる

範囲に100を乗じてみる

では、D2の数式=B2:B10に100を乗じてみましょう。参照範囲の各セルに対して100を乗じた結果が出力されます。

範囲に100を乗じる

スピル範囲演算子の使い方

#はスピル範囲演算子という名前の記号です。数式が入力されているセルに#を付けることで、数式が入力されたセルとゴーストの範囲をまとめて参照することができます。またセルの数式を入力している際に、スピルしているD2からD10の範囲をマウスまたはキーボードで選択しようとすると、D2:D10とはならず勝手にD2#という表記になります。

数式が入力されたセルに#を付ける

当然ながら、この=D2#に100を乗じると、もとの数式範囲であるB2:B10に100を乗じた結果がスピルします。

D2#に100を乗じる

このスピル範囲演算子は、後で非常に重要になりますのでしっかり覚えておいてください。

よく使う便利な関数の解説

会計・経理実務でスピルを使うにあたって重要な関数の説明に移ります。これらの関数は基本的にスピルすることが前提です。

  • UNIQUE関数
  • SORT関数、SORTBY関数
  • FILTER関数

XLOOKUPが便利だなんだともてはやされていますが、XLOOKUPは既存の関数でも代用可能なレベルであるのに対して、上記の三つの関数はそもそも代用ができなかったり、非常に面倒な手順を踏むことで再現可能だったりするものです。これらの専用の新しい関数が用意されたことで、Excelの使い勝手は大きく向上しています。

UNIQUE関数

UNIQUE関数の基本

指定した範囲から重複を除いたデータを返してくれます。

=UNIQUE(G2:G23)

上記の数式を入力することで、参照先から重複を取り除いた結果をスピルしてくれます。この関数が実行されるまではデータタブで「重複の削除」という機能でも同様のことはできましたが、元のデータを削除してしまう上に一部でバグが発生するという話もありました。しかし、このUNIQUE関数は元のデータに影響を与えずに重複のない一覧を新しく出力できます。

UNIQUEは基本的に単独では使いません。他の関数と組み合わせて用いることが大半かと思います。実務上でよく使うのはやはりSUMIFSでしょうか。SUMIFSの引数としてUNIQUEの結果であるL2#を指定することで、UNIQUEの結果に対応して伸び縮みするSUMIFSの表が完成します。

L2 = UNIQUE(G2:G23)
M2 = SUMIFS(J2:J23,L2#)

UNIQUEの結果を引数にしてSUMIFS

さて、勘がよい方はすでにお気づきでしょうがこの数式はまだまだ完璧ではありません。きっとこんなツッコミがあることでしょう。

  • 毎月データが増えるんだから行数が変わるけど、いちいち関数の範囲を変えるの?

そこで、テーブルの機能を使います。テーブルは構造化参照という仕組みを使う事ができ、スピルの機能と大変相性がよいです。まずは対象範囲をテーブルに変換します。範囲指定してCtrl+Tでテーブル化を実行しましょう。

範囲指定してCtrl+Tでテーブル化

先ほどの数式はセル範囲を指定しましたが、今回は対象がテーブルのデータですので列名を指定します。最上段の行からCtrl+Shift+↓で最下段までデータを選択すると構造化参照としてテーブルの列全体を指定できます。よって、テーブルを使う場合にはUNIQUEやSUMIFSの引数にこのような形になります。

L2  = UNIQUE(テーブル1[部門名])
M2 = SUMIFS(テーブル1[金額],テーブル1[部門名],L2#)

構造化参照とスピルを組み合わせる

もとのデータに新しく経営企画部門の外注費を追加したところ、L9M9に新しいデータが表示されました。テーブルは新しくデータが入力されると、その範囲をテーブルの一部として認識してくれます。そのため、スピル系の関数と組み合わせると、データの内容に応じて自動で変化してくれる表が完成します。2

経営企画部門の新規データが追加されるとスピルの範囲が拡張

UNIQUE関数の応用

ここまでは関数の解説サイトでよく出てくる内容です。ただ、現実の実務に対応しようとすると、これだけでは対応しきれませんし、わざわざ記事を書くまでもありません。せっかくなのでもう一歩踏み込んでみましょう。

実はUNIQUE関数は複数の列に対しても重複のない組み合わせを作成することができます。ただし、他の関数と組み合わせる必要があります。いくつか方法はありますが、今回はHSTACK関数を使って複数列での重複のない列を作成してみます。

HSTACK関数も最近追加された新しい関数で、複数の範囲を簡単に横につなげてスピルさせることができる関数です。テーブル次の数式をL2に入力することでとL2からM23までの範囲に数値がスピルしてくれます。

L2 = HSTACK(テーブル1[部門名],テーブル1[科目名称])

HSTACKは複数の範囲を横に繋げて出力する関数

次に、これをUNIQUEでくくってしまいます。L2のセルを次のように書き換えます。

L2 = UNIQUE(HSTACK(テーブル1[部門名],テーブル1[科目名称]))

そうすると、部門と科目の組み合わせで重複のないリストが出力されます。

部門と科目の組み合わせで重複のないリストを出力

では、これを最初のUNIQUEの場合と同様にSUMIFSの集計の引数として使ってみたいのですが、少し面倒な事態が発生していることにお気づきでしょうか?部門はL列に、科目はM列でそれぞれスピルしていますが、もともとこれらのゴーストはL2に入力された計算式の結果がこぼれたものです。

元データが変化しない限りは、SUMIFSの引数にはL2:L9M2:M9という入力で問題ないのですが、データ追加され部門や科目が増えた場合には、現在スピルしている範囲のL2~L9M2~M9はさらに下に伸びてしまいまいます。その結果、SUMIFSの集計範囲が適切ではなくなり集計漏れが発生してしまいます。つまり、SUMIFSの引数を指定するときには単純にL2:L9M2:M9という指定ではダメなのです。

そこで、 今回のケースでは複数の列範囲から特定の列を取得するためにCHOOSECOLS関数を使います。正確な構文は下記のとおりです。

=CHOOSECOLS(array,col_num1,[col_num2],…)

今回は複雑な使い方はしませんので、単純に一つ目の引数に対象の配列、二つ目の引数に取り出したい列番号を入力します。結果はこんな感じです。

複数列の配列から特定列を指定して抽出する

このCHOOSECOLS関数を使って、複数列の自動で伸縮するSUMIFSの表を作ると、このような形になります。

L2 = UNIQUE(HSTACK(テーブル1[部門名],テーブル1[科目名称]))
N2 = SUMIFS(テーブル1[金額],テーブル1[部門名],CHOOSECOLS(L2#,1),テーブル1[科目名称],CHOOSECOLS(L2#,2))

CHOOSECOLSを組み合わせてSUMIFSを実行

補足 ピボットテーブルとの使い分け

以上のような工夫により、UNIQUEとSUMIFSの組み合わせで対応力の高い集計表が作成できます。ただ、この程度ならピボットテーブルで十分ではないか?という意見もあるかもしれません。個人的な意見になりますが、次の二点のメリットを生かせるならば、今回のような手法を使ってスピルによる動的配列で集計表を作った方がよいのではないかと考えています。

ポイント 内容
データ更新が不要 計算結果が即座に反映される。
(ピボットテーブルのデータ更新漏れミスを防げます。人間はミスをする生き物です…)
FILTER関数との組み合わせが強力 集計表がスピルを用いた動的配列であれば、FILTER関数と組み合わせることで簡単に出力内容を制御できる。

逆に、データの内容を確認するなど探索的な作業を行う場合は、マウス操作で縦横の軸や粒度を自在に操作できるピボットテーブルを用いた方がよさそうです。

なお複数条件の集計は、GROUPBY関数という新しい専用の関数が将来的に実装されるようです。この記事を書いている2023年12月時点ではインサイダープログラムでの試行提供となっており、あと1年ほどすれば通常版に実装されるかと思います。この関数さえあれば、UNIQUE関数で出力した複数列の配列を個別にCHOOSECOLS関数で指定するなんていう面倒なことは不要になりそうです。

SORT関数、SORTBY関数

つづいてSORT関数とSORTBY関数です。これらの関数は指定した範囲について並び替えの軸となる列の昇順、または降順で並び替えた結果を出力します。これらの二つの関数は指定した配列を並び替えるという根本的な機能は同じですが、並び替えの基準とする列の指定の方法が異なります。どちらが便利かは場合によるので、状況によって使い分ける形となります。

SORT関数の基本

SORT関数の構文は次のとおりです。

=SORT(配列,[並び替えインデックス],[並び替え順序],[並び替え基準]) 

SORT関数は指定範囲をインデックス番号(何列目か)を使ってソートする基準を設定します。 (四つ目の引数は列と行を反転させて並び替えるために使用しますが、省略可能であり更に今回の記事では取り扱わないため以降は引数の入力と説明は割愛させていただきます。)

では、実際の関数の動きを確認してみましょう。 下記のケースでは、対象の配列(B2:D10)について、2列目の昇順(1)で並び替えています。

F2 = SORT(B2:D10,2,1)

並び替えの指定が一列である場合には、シンプルにSORT関数を使うと簡単かと思います。

SORTBY関数の基本

SORTBY関数の基本の書き方は以下の通りです。

=SORTBY(配列,基準配列1,[並び替え順序1],...) 

複数の並び替えの基準を設定したい場合には、基本的にはSORTBY関数を使います。SORT関数が並び替えの列をインデックス番号(何列目か)で指定したのに対して、SORTBY関数は並び替えの配列をダイレクトに指定します

実際のデータでSORTBY関数を使ってみましょう。下記のケースの場合、B2:D10という範囲について、まずB2:B10を基準に昇順(1)でソート、次にD2:D10を基準に降順(-1)でソートしています。

F2=SORTBY(B2:D10,B2:B10,1,D2:D10,-1)

最初にB2:B10を昇順ソート、次にD2:D10を降順ソート

SORT系関数の具体的な使い方

SORT系の関数も単独で用いることは少なく、他の関数との組み合わせで効果を発揮します。

何も考えずにUNIQUEだけを用いて集計表を作成した場合、こんな感じの表ができてしまうことがあります。経理や会計界隈の人間はおそらくこの表を見て強烈な違和感を覚えるのではないでしょうか ?販管費が最上段に来る表なんて、私には耐え難い代物です。

許しがたい表

そこで順番を入れ替えるSORT系関数の出番です。絶対とはいいませんが、勘定科目のコードはPLの上から順にソートがかかるように付番されているはずかと思います。そこで最初に科目コード順にソートした配列をUNIQUEして出力し、SUMIFSしたものがこちらです。

L2 = UNIQUE(SORTBY(テーブル1[科目名称],テーブル1[部門コード]))

トップラインたる売上高はやはり最上段
人間が目視で確認する表は、一定の基準や暗黙の了解で並んでいた方が視認性があがります。関数を使ってサクッと並び替えてしまいましょう。

FILTER関数

会計実務におけるスピル活用の本丸といっても過言ではない、それがFILTER関数です。私が最近作ったExcelのファイルですが、おそらくFILTER関数の出現率が90%を超えています。FILTER関数が無かった時代には絶対に戻りたくないと思えるほどに、この関数に依存しきっております。

Excelには抽出や並び替えのための機能としてオートフィルタが存在しておりますが、オートフィルタは設定できる条件が非常に限られています。もっと細かい設定で自由自在にデータを抽出したい、確認したいというときにはFILTER関数が効果を発揮します。3

条件が二つだけ?

FILTER関数の基本

FILTER関数の基本の構文は次のようになっています。

=FILTER(配列,条件,[空の場合])

作りとしては、対象となる「配列」に対して、「条件」で絞り込みの要件を設定する仕組みです。最後の引数は条件に合致する対象がなかった場合に表示するメッセージの指定です。あった方がより親切ですが、根本的な動きに関わるものではないので今回の記事では解説や記載は省略します。

では基本的な数式の書き方とその結果を見てみましょう。下のSSは大量のデータから科目名称が売上高であるもの抽出した表です。

L2 = FILTER(テーブル1,テーブル1[科目名称]= "売上高")

売上高のみを抽出

売上高で絞り込みをおこなうために、FILTER関数の二つ目の引数に、 テーブル1[科目名称]="売上高"という入力を行っています。簡単な絞り込みを行うだけならこれだけでよいのですが、FILTER関数の挙動に対して理解を深めるために、テーブル1[科目名称]="売上高"について深掘りをしたいと思います。

テーブル1[科目名称]="売上高"をもとのテーブルの横に貼り付けた結果が下のSSになります。

科目名称が売上高のものはTRUE、そうでないものはFALSE

テーブル1[科目名称]="売上高"は、テーブル1[科目名称]のすべての行に対してそれぞれの値が"売上高"と等しいかを判定し、その結果をTRUEFALSEで返す働きをしているのが分かります。これがFILTER関数の二つ目の引数の正確な内容です。…ここまで説明を聞けばもうお分かりかもしれませんが、FILTER関数の挙動は非常にシンプルです。

FILTER関数は二つ目の引数の配列がTRUEの行は表示、FALSEの行は非表示にする動きをします

そのため、FILTER関数の外のセルのTRUEFALSEが表示されている範囲を参照しても正しく抽出を行うことができます。

L28 = FILTER(テーブル1,L3#)

TRUEとFALSEの条件は他のセル範囲参照でもOK

なお、TRUEFALSEの行数については注意が必要です。 FILTER関数の最初に指定した配列の行数と、条件の絞り込みのためのTRUEFALSEの行数が一致しない場合にはエラーになります。 下記の例の場合、フィルターを行いたいテーブルが22行なのに対して、判定用のTRUE/FALSEの配列の行数が21行であるため、エラーが出ています。

FILTER対象の行数とTRUE/FALSEの配列の行数が不一致の場合はエラー

~また、TRUEFALSEはそれぞれ10で代用することもできます。1であれば表示、0であれば非表示という制御が可能ですので、覚えておくと大変便利です。

投稿後にお二人から指摘を受けまして、内容に誤りがありましたので修正いたします。

FALSE0TRUE0以外の数字で代用が可能です

TRUEとFALSEではなくてもOK

TRUEは0、FALSEは0以外の数字で代用可能

一つの条件で抽出する場合には特に使うことはないでしょうが、複数条件での抽出を行う場合にはこの前提が重要になります。…試したことはありませんでしたが、FALSEについてはマイナス値でもよいみたいですね。

FILTER関数の応用_複数条件によるフィルタリング

ここからはFILTER関数の真髄である、複数条件によるフィルタリングを見ていきたいと思います。先ほど、FILTER関数は二つ目の引数の配列がTRUEの行を表示し、FALSEの行は非表示になるという説明をしました。複数条件であってもこのルールは変わりません。 どのような複雑な条件であっても、フィルタリングを行いたい対象の配列(≒元の表)と同じ行数のTRUE/FALSEの配列、もしくは「TRUE=0ではない数値、FALSE =0」の配列を準備すればよいのです。

複雑な条件の結果を出力するための配列は、複数の配列の乗算または加算で作成します。4端的に表現すると、下記のようになります。

条件 記号 数式 意味
AND * 条件式1 * 条件式2 条件のいずれもTRUEならばTRUE
そうでなければFALSE
OR + 条件式1 + 条件式2 条件のいずれかがTRUEならばTRUE
そうでなければFALSE

さきほど、 数値が0のものはFALSE0以外のものはTRUEで代用できる という説明を行いました。

ここでもう一つ重要な前提条件を共有します。普段意識することはないかもしれませんが、ExcelにおいてFALSE0TRUE1を意味しています。

N関数による判定結果

FALSE0TRUE1という前提のもとで、AND(乗算)とOR(加算)は下記のように整理することが可能です。

区分 条件1 条件2 計算 結果 FILTER関数での挙動
AND 0 0 0*0 0 非表示
AND 0 1 0*1 0 非表示
AND 1 0 1*0 0 非表示
AND 1 1 1*1 1 表示
OR 0 0 0+0 0 非表示
OR 0 1 0+1 1 表示
OR 1 0 1+0 1 表示
OR 1 1 1+1 2 表示

上記の図を見てもピンとこない方も多いかもしれませんので、実際のデータを見てFILTER関数の挙動を確認してみましょう。

まずはAND条件で科目が売上高かつ製品名がマウスのものをFILTER関数で抽出してみます。 判定の途中が分かりやすいように条件をセルで表示させていきます。

セル 判定内容 数式
L3 科目が売上高と等しい テーブル1[科目名称]="売上高"
M3 製品名がマウスと等しい テーブル1[製品名]="マウス"
N3 L3#M3#のAND条件 L3# * M3#

L3からは科目が売上高と等しいか否かの判定結果、M3からは製品名がマウスと等しいかの判定結果がスピルします。これらを乗算した結果、N3から科目が売上高かつ製品名がマウスであることを示す1とそうではないことを示す0の判定結果がスピルします。

科目が売上高かつ、製品がマウス

では、FILTER関数で二つ目の引数にN3#を指定して結果を確認してみます。…よさそうですね。

A39 = FILTER(テーブル1,N3#)

引数にAND条件のN3#を指定

これをセル参照ではなくFILTER関数の数式に格納する際には、それぞれの条件を()で括り、*でつなげます。

A39 = FILTER(テーブル1,(テーブル1[科目名称]= "売上高")*(テーブル1[製品名]= "マウス"))

判定式をFILTER関数の中に格納

オートフィルタは条件を二つまでしか設定できませんでしたが、FILTER関数は下記のように三つ以上でも条件を設定することができます。科目が売上高かつ、製品名がマウスかつ、金額が150,000円以上と設定したい場合には次のようになります。

A39 = FILTER(テーブル1,(テーブル1[科目名称]= "売上高")*(テーブル1[製品名]= "マウス")*(テーブル1[金額]>=150000))

AND条件を追加

OR条件もまったく同じ考え方で処理できます。ANDとの違いは条件を繋げる記号を+にすることです。 まずはTRUE/FALSEの判定をわかりやすくするためにOR条件の判定計算式をN3に出して、それを参照してみます。

セル 判定内容 数式
L3 製品名がマウスと等しい テーブル1[製品名]="マウス"
M3 製品名がディスプレイと等しい テーブル1[製品名]="ディスプレイ"
N3 L3#M3#のOR条件 L3# + M3#

製品名がマウス、またはディスプレイと等しい

続いてFILTER関数の中にOR条件を直接書いてみます。意図通りに動いていることが確認できます。

A39 = FILTER(テーブル1,(テーブル1[製品名]= "マウス")+(テーブル1[製品名]= "ディスプレイ"))

FILTER関数の内部に式を埋め込み

もちろん、ANDとORを組み合わせることも可能です。製品名がマウス、またはディスプレイで、金額が100,000円以上のものを抽出してみましょう。まずは製品かマウスかディスプレイのOR条件を作り、その結果に対してAND条件で金額が100,000円以上となるように括弧の位置を付ける必要があります。

A39 = FILTER(
               テーブル1,
              (( テーブル1[製品名] = "マウス" ) + ( テーブル1[製品名] = "ディスプレイ" ) ) *
              ( テーブル1[金額] >= 100000 ) 
           )

製品がマウスかディスプレイで、どちらも100,000円以上のもの

やや長くなりましたが、以上がスピル機能を用いる際によく使う関数の基本的な解説です。

実践的な活用

ここからはスピルを用いた実践的な活用例を紹介します。今回は応用幅の広いFILTER関数について下記の二点の事例を解説したいと思います。5

  • メンテナンスしやすい複数条件の設定方法
  • 表示と非表示の切り替え

メンテナンスしやすい複数条件の設定方法

FILTER関数は、二つ目の引数のTRUE/FALSEの配列の取り扱いが肝要になります。繰り返しになりますが、次の二点を守れば自在にデータを抽出できるようになります。

  • 抽出前の表(配列)の行数と、条件となるTRUE/FALSEの配列の行数は必ず等しい
  • FILTER関数の二つ目の引数となる配列は、TRUEFALSE、または1(=TRUE)か0(=FALSE)で構成する

さて、先ほどのFILTER関数の解説では、OR条件で製品名を一つずつ数式内にべた書きしました。しかし、条件が二つくらいなら別に気になりませんが、大量に指定しようとすると数式が長くなってしまい内容が分かりにくくなります。また、メンテナンスも大変面倒なことになります。そこで、絞り込みの条件を関数を使って表現してみたいと思います。

ここで使う関数はXMATCHとISNUMBERです。

関数名 機能
XMATCH 検索値を対象セル範囲内で検索し、項目の相対位置を返す
(デフォルトで完全一致)
ISNUMBER 対象が数値であればTRUE、数値でなければFALSEを返す

サンプルケースとして、複数の勘定科目を機械的に抽出してみます。手順は下記のとおりです。

  1. 抽出したい項目を一覧リストにします(SSのセルJ4J7)
  2. XMATCHで科目名称がJ4J7に存在するかをチェックする。存在する場合は数値、存在しない場合は#N/Aが返る
  3. ISNUMBERを使ってXMATCHの結果が数値であればTRUE、数値でないならばFALSEを返す

これでFILTER関数の二つめの引数として使える、元の表と同じ行数のTRUE/FALSEの配列が完成しました。これをそのままFILTER関数の条件に使えば完成です。

XMATCHとISNUMBERでTRUE/FALSEの判定を行う

上記の例は分かりやすさを重視するために補助列を使いましたが、補助列を使わずにFILTER関数内部に入力するとこんな感じです。OR条件をダラダラと書くよりも非常にシンプルですし、抽出したい科目が増えたときにもメンテナンスがとても簡単になります。

J16 = FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7)))

補助列の条件式をFILTER関数の内部に直接書いた場合

さて、先ほどの出力結果は補助科目名の並びがバラバラで見にくいので、ソートしちゃいましょう。方法は色々ありますが、元の数式をいじらないシンプルな方法として今回はSORT関数を用います。

J16 = SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),3,1)

3列目の補助科目名称で昇順ソート

補助科目名称はきれいにソートできましたが、どうせなら部門名の並びもきれいにしたいですね。複数の列に対してソートを行う時、普通はSORTBY関数を使うのが基本です。しかしFILTER関数で抽出した結果は配列になっており列名が消失しているため、テーブルのように基準列名を直接入力することができません。この場合はCHOOSECOLS関数でインデックス番号を使って入力するのが真っ当な方法かと思いますが、式が長くなるので面倒です。そこで、ちょっとした小技の{}を使ってみます。(配列定数とかいうらしいです)

support.microsoft.com

配列を手作業で定義する機会は滅多にないので挙動が想像つきにくい方も多いかもしれません。配列を定義する{}の中に数字を記入するとこんな感じに出力されます。

{}の使い方

では、この配列定数とSORT関数を組み合わせてFILTER関数の結果をうまい具合にソートします

=SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),{3,4,5},{1,1,-1})

SORT関数の後ろ二つの引数が非常にわかりにくいかと思いますが、{3,4,5}{1,1,-1}で最初に3 列目で昇順(1)、次に 4列目で昇順(1)、最後に 5列目は降順(-1)でソートすることを意味します。

3列目と4列目は昇順、5列目は降順でソート

上記の方法がわかりにくいということであれば、SORT関数を複数回繰り返すことでも対応が可能です。一番優先したいソートの基準軸を外側に設定して、SORT関数を繰り返し使えば小技の{}を使わずとも同じことが実現可能です。(簡単ではありますが、ネストが深くなってしまって分かりにくいので個人的にはあまり好きではありません…)

=SORT(SORT(SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),5,-1),4,1),3,1)

なお、SORTBY関数で真っ当に書くとこんな感じです、多分。先ほどと結果は同じですが、式は冗長になります。

J16 =LET(
          _array,FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),
          SORTBY(_array,CHOOSECOLS(_array,3),1,CHOOSECOLS(_array,4),1,CHOOSECOLS(_array,5),-1)
         )

抽出条件の切り替え

基礎解説にて説明した複数列UNIQUEとSUMIFS、これにFILTERを組み合わせることで、任意のデータの絞り込みの表示・非表示切替機能の実装を行ってみます。

今回使うのはこのようなデータです。見切れていますが3,000件ほどあります。

サンプルデータ

販管費について、まずは科目別・各部門別での集計をおこなってサマリーを作ります。 次に科目別・各部門別での合計金額が閾値を超えるもののみを抽出するための切り替え機能を付けてみます。

まずは補助科目と部門名のユニークな一覧を作成するとともに、補助科目と部門名のユニークな一覧を軸にしてSUMIFSをおこないます。

F2=SORT(UNIQUE(HSTACK(テーブル3[部門名],テーブル3[補助科目名称])),{1,2},{1,1})
H2=SUMIFS(テーブル3[金額],テーブル3[部門名],CHOOSECOLS(F2#,1),テーブル3[補助科目名称],CHOOSECOLS(F2#,2))

サマリの作成

次に、セルL2に閾値を入力します。今回は適当に1,000,000とします。 そしてセルI2に、H2#>=L2と入力し、補助科目別・部門別で集計された金額が1,000,000以上の場合にFALSEを返す列を作成します。

閾値以上の時にTRUEを返す列を作成

続いて、L4に下記のような入力規則を設定します。これでセルL4はドロップダウンから「全表示」か「閾値以上」かを選択することができます。

ドロップダウンリスト用に入力規則を作成

入力規則を作成したら、先ほどセルI2に設定したH2#>=L4を次のように書き換えます。

I2 =IF(L4="閾値以上",  H2#>=L2,
       IF(SEQUENCE(ROWS(H2#)),TRUE)
      )

唐突に出てきたIF(SEQUENCE(ROWS(H2#)),TRUE)は、複数の関数を組み合わせて任意の値を任意の回数スピルさせる小技です。

IF(SEQUENCE(ROWS(H2#)),TRUE)の細かい説明

利用している関数の簡単な説明は下記のとおりです。

関数名 機能
SEQUENCE 連番を作る
ROWS 指定した範囲の行数を返す

単純化すると下記のようになります。

TRUEを五行スピルさせる場合はこんな感じ
まず、ROWS関数で出力したい行数(5)を取得します。これをそのままSEQUENCE関数に渡します。そうすると=IF(SEQUENCE(5),TRUE)という形になります。SEQUENCEは連番を返しますので、15の連番を作成します。これをIFの最初の引数にもってきた場合、結果は常に真になります。そのため、作成された15のそれぞれに対応してTRUEが返るため、五行のTRUEがスピルするという仕組みになっています。直感的ではないかもしれませんが、こういうことができるという小技の一つとしてご理解ください。他にも色々とやり方はあるらしいです。

2023年12月17日追記

光希桃さんからご指摘いただいたので補足いたします。FILTER関数の制御用に、常にすべての行を表示させるための配列を作成するために、もっとシンプルにしようとすると次のような書き方が可能です。

SEQUENCE(ROWS(H2#))

FILTER関数は0以外の数値であれば表示する仕組みになっていいます。SEQUENCEは一つ目の引数のみ設定した場合に、1から引数に設定した数値分だけ1刻みの連番を作成します。よって、SEQUENCE(ROWS(H2#))と記載した場合には、出力される配列は必ず0を超えた数字の連番になります。よって、FILTER関数で条件にこの配列を用いると、常に表示される仕組みになります。

参考

FILTER関数の表示・非表示を制御するTRUE/FALSEの配列は、対象となる表(配列)と同じ行数である必要があります。そこでまずはROWS(H2#)を使って対象となる表(配列)の行数を取得します。任意の値を任意の行数出力する小技を使って、TRUEH2#と同じ行数分スピルするIF(SEQUENCE(ROWS(H2#)),TRUE)を準備します。最後にIF関数を使って、ドロップダウンリストが閾値以上のときはH2#>=L2、そうでないときは元表の行数の数だけTRUEをスピルする配列をセルI2に設定しました。

セルL4のドロップダウンリストを切り替えると、セルI2からスピルした値は「全表示」の時にはすべての行がTRUE、「閾値以上」の時には閾値1,000,000円以上のものがTRUEでそれ以外はFALSEになることが確認できます。

全表示

閾値以上

これで準備は完了しましたので、最後の仕上げに移ります。 まずはN2に下記の数式を入力します。

N2 = HSTACK(F2#,H2#)

これでF2からスピルしたデータと、H2からスピルしたデータを横方向に合成した配列が出力されます。

HSTACKで合成

最後に、閾値チェックの数式がセットされたI2#を、FILTER関数の条件にセットします。

N2 = FILTER(HSTACK(F2#,H2#),I2#)

これで完成です。まずはドロップダウンリストで全表示を選択してみます。

I2に入力されたされた数式はすべてTRUEを返すのですべてのデータが表示されます

全表示

次に、ドロップダウンリストで閾値以上を選択してみると、無事に1,000,000円以上のものが表示されました。

閾値以上のみ表示

閾値を変更したい時はL2の金額を書き換えるだけでOKです。閾値を変えることが想定される場合には関数の中に直接条件数値を書きこまずにセル参照にすることでメンテナンス性が向上します。

800,000円以上のものを抽出

今回の解説では、UNIQUEでサマライズした集計結果を用いて解説を行いましたが、このFILTERによる自由なフィルタリングは仕訳単位のデータにも応用することができます。重要なのは、表示と非表示を制御するTRUEFALSEを返す数式をいかに組み立てるかです。ここさえできれば、自由自在なデータ抽出や表示の切り替えが可能です。アイディア次第ですが様々な応用が可能です。下記の使い方などは実務でかなり重宝しています。

  • FIND関数を用いて特定の文字を含むデータを抽出
  • 特定の科目を含む伝票を仕訳の一覧リストから貸借まるごと一括して抽出

終わりに

DXがどうのというご時世の中、組織の方針に則してノーコード・ローコード開発ツールやRPA、BIツールなどを活用することによって仕事の仕方を変えていくことが求められているかと思います。しかし、実際問題としてこれらのツールの開発や維持、運用コストに見合わないようなマイクロな業務は無数に存在します。細かい仕訳一つ一つについてツールを使った開発を行うなどはとても現実的ではありません。

長々と解説記事を書いておいて今更ですが、このような状況下において各種ツールではカバーしきれない細かな業務を埋める方策として、スピルの機能を活用するくらいでよいかな程度に思っています。所詮は単なるExcelですからね。カッターナイフで牛を捌くわけにはいきません。最近ローコードやらPower BIメインで仕事をしているため特にそう感じるのかもしれませんが、やはり各種ツールは適切な範囲で適切な使い方をすべきです。

なお、スピル系の数式はやりすぎると何をやっているのかまったくわからない特級呪物を生み出すことが可能です。今回の解説では取り上げませんでしたが、LAMBDA系を用いると作成者自身ですら内容を把握しにくくなるほどです。

個人で使う分には問題ありませんが、組織で共有するExcelファイルでスピル系の機能を用いる際には、加工の意図や処理過程の内容をドキュメントとして残すとともに、最悪のケースとして手作業で切り戻しができるような体制を整えておくことを強く推奨します。

最後までお読みいただき、誠にありがとうございました。



  1. 時間の制約上、CSEの解説は除外します。不可能ではないというだけでそれほど実用的ではないですし、私も実際に使ったことはありません。
  2. テーブルを用いずに列指定することもできますが、不要行の処理が必要になります。時間の制約上、今回は説明を割愛します。単にDROPやFILTER+ISNUMBER等で不要行を落とすだけですが。
  3. 以前から存在するフィルターオプションという機能を使えば制限のない自由なデータのフィルタリングができますが、設定がやや面倒で使い勝手がよいとは言えません。気軽に使える関数はやはり正義です。
  4. 気づいた方もおられるかもしれませんが、この書き方はSUMPRODUCTで複数条件の集計を使うときに使うあれです。ExcelにSUMIFSがまだ存在しなかった頃はSUMPRODUCTを使わざるを得ず、計算速度に悪態をつきながらF9ボタンで計算を手動更新していました。
  5. SCAN関数やREDUCE関数などを使えばさらに多くのことができますが、内容が難解になるため今回取り扱うトピックには含めないこととします。時間が足りません…。

Script Labのカスタム関数でクロス結合

クロス結合ってわりと使いませんか

機械的に二つのリスト(範囲)のすべての組み合わせをするためにはクロス結合を用います。(デカルト積や直積なんて呼び方もする模様)

SQLでクロス結合を解説しているネットの記事を見ると、あまり使わない的な解説をよく見ますが、個人的には左外部結合の次に使う機会が多いJOINです。

PowerQueryでももちろん可能です。

learn.microsoft.com

ただし、GUIのボタン操作では不可能で、すこし面倒な手順を踏みます。わざわざテーブル化してさらに数ステップ必要だなんて、なんて面倒。できるだけマシですが、もっとインスタントに、直感的に関数で処理したいのです。

そこでScript LabによるUDFの出番。コードは以下のとおり。

/**
 * @customfunction
 * @param {any[][]} array1
 * @param {any[][]} array2
 * @returns {any[][]} A dynamic array with multiple results.
 */
function CROSSJOIN(array1, array2) {
  // 空の新しい配列を作成
  const combinedArray = [];

  // 与えられた2つの配列をループ
  for (let i = 0; i < array1.length; i++) {
    for (let j = 0; j < array2.length; j++) {
      // 新しい要素を作成し、変換された配列に追加
      const newElement = [...array1[i], ...array2[j]];
      combinedArray.push(newElement);
    }
  }
  return combinedArray;
}

けっこう便利。

Script Labのカスタム関数でピボット解除

横持ちデータ死すべし

Excelで仕事をしているとよく出くわすのが横持ちデータ(縦横マトリクス)。集計結果としては別によいのですが、このデータをもとに何か作業をするためには非常に使いにくいことこの上ありません。

もちろん、Power Queryでピボット解除を行うことによって縦持ちデータ(整然形式)に変更することは可能です。ただ、わざわざそのためにテーブル化してエディタを開いて処理するのもまあまあ面倒で関数でさくっと処理したくなったりもします。LAMBDAで関数を定義することもできますが、処理するためにわざわざLAMBDAを定義するのも大変面倒です。

そこでScriptLabによるUDFの出番です。この手法ならいったアドインでコード実行してしまえば任意のタイミングでさくっとピボット解除を行えます。

コードは以下のとおり。

/**
 * @customfunction
 * @param {any[][]} array
 * @returns {any[][]} A dynamic array with multiple results.
 */
function UNPIVOT(array) {
  // 空の新しい配列を作成
  const transformedArray = [];

  // 与えられた配列の最初の要素(表のヘッダー)を取得
  const headers = array[0];

  // ヘッダーを除いた行の要素を処理
  for (let i = 1; i < array.length; i++) {
    const row = array[i];

    // ヘッダーに含まれる列の数だけ処理
    for (let j = 1; j < headers.length; j++) {
      // 新しい行の要素を作成し、変換された配列に追加
      const newRow = [row[0], headers[j], row[j]];
      transformedArray.push(newRow);
    }
  }
  return transformedArray;
}

本当はヘッダーやらなんやらをきれいに引数で指定するのが正しいのでしょうが、私の業務上ではきれいなマトリクスをアンピボットするだけの作業が必要なので、思い切って引数設定は一つにしてしまいました。

他人に渡す際には値コピペで数式をつぶすことを忘れないことだけが注意点ですかね。

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[][]}としており、セル範囲を引数にすると返り値もスピルする仕様になっています。

正規表現TESTメソッド

スピルもできてますね。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[][]}としており、セル範囲を引数にすると返り値もスピルする仕様になっています。

正規表現REPLACEメソッド
キャプチャもできてますね。ぐっど。

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("");}}として、要素数が最大値に満たない子配列には""を加えて要素数を揃える処理を実施しました。

子配列の要素が一致

子配列の要素が不一致

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

  1. カスタム関数の引数の範囲が一列の際には、特別な処理は行いません。
    正規表現の結果の三次元配列⇒二次元配列に変換⇒Excelの仕様に合わせるために子配列の要素数を整合させたtwoDimensionalArrayを出力します。グローバルオプションがTRUEの場合には、正規表現に一致した結果が横方向にスピルします。

    引数範囲が一列の場合

  2. カスタム関数の引数の範囲が二列以上かつグローバルオプションがTRUEの場合、元の引数の範囲が二次元配列になっているので三次元目のMATCH結果を出力できません。縦にも横にもスピルしているので、どこにも出力できないのです。配列の入れ子を表現できないExcelの限界ですね。どうしようもないのでresult = [["gオプション使用時はarrayの列数を1にしてください"]]としてエラー出力させました。

    引数範囲が二列かつgオプションがTRUE

  3. カスタム関数の引数の範囲が二列以上かつグローバルオプションが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のヘルパー関数に組み込んでスピルさせようとしたところ、対応していないようでエラーが発生しました。

正規表現の実装要望

2023年2月14日現在、MicrosoftのフィードバックコレクションでExcelのワークシート関数に正規表現の関数実装の投稿が出ております。3カ月経った状態で、「これを検討しています」のステータスになっておりますが、これは期待してもよいのでしょうか…?とりあえず私は投票しておきました。

Microsoftのフィードバック コレクション

参考にしたHP等

Script Lab で Excel のユーザー定義関数を書く
カスタム関数から複数の結果を返す
カスタム関数パラメーター オプション
ChatGPT

パワーピボットの不具合対応

一度拡張子をXLSXからZIPに変更して、中のコードを変えるという荒業が必要になるらしいです。これは公式でバグ直してほしいところですね。

 

画像

 

 

PowerQueryで正規表現を使う

JavaScript正規表現を呼び出す

残念ながら現時点では、PowerQueryのM言語では正規表現はサポートされていません。しかし、M言語でJavaScriptを呼び出すテクニックがあるそうで、これを使う事で正規表現が利用可能になるそうです。

replace(置換)とmatch(抽出)は先達の方々が日本語で解説記事を書いていたのでそのまま使えそうでしたが、test(True/False)は残念ながら見つけられませんでした。

仕事の関係でtestメソッドを使いたかったのでjavascriptのprogateの無料分だったり初心者向けサイトを見ながら書いてみました。下記を詳細エディタに貼り付ければ、そのままカスタム関数として使用可能です。

(string as text, reg_expression as text) as logical =>
let
  expressionText = Text.Replace(reg_expression, "\", "\\"),
  test =  Web.Page(
                "<script>
                        var originalText="&"'"&string&"'"&";
                        var re = new RegExp('"& expressionText & "');
                        var result=re.test(originalText);
                        document.write(result);
                </script>"){0}[Data]{0}[Children]{1}[Children][Text]{0},
  result = Logical.FromText(test)
in 
  result

利用例は下記のとおりです。

stringに判定したい文字列のカラムを指定、reg_expressionに正規表現を入れればTrue/Falseで結果を返してくれます。

上記の例においては、^\d{3}-\d{4}$ で、「三桁の数字で始まり、ハイフンのあとで四桁の数字で終わる」というパターンに合致するものがTrueとなっています。


セキュリティ対策の結果としてVBAに対する締め付けが日増しに強まる中、このテクニックを使うことでVBA無しのExcel正規表現を利用することができます。私自身、正規表現を勉強し始めたばかりで自由に使いこなすには程遠いレベルですが、ちょっとした文字列の操作・処理を行う際に正規表現を用いることで面倒なIFやSWITCH、FINDなどの関数をネストする必要がなく簡潔に処理を行うことができ、その便利さに感動しております。試しておりませんが、PowerBIでも使えるはずです。

パフォーマンスについて(2022/9/7 一部追記)

こちらの方法は大変便利なのですが、欠点(?)として処理が重いようです。10万件程度のデータで試した際にレスポンスがよくない感じがしました。都度JavaScriptを呼び出しているからでしょうか?方法自体邪道ですので、大規模なデータを扱う際には元から用意されているM言語の関数を組み合わせた方が効率が良いはずです。少なくとも上記で例示している郵便番号が正しく入力されているか程度のものならば、正規表現を用いずに既存のM言語の関数を組み合わせて正しく書いた方がパフォーマンスは高いはずです。

本手法はExcelで使い捨ての小規模なデータ処理などをする際に、簡便的にメタ文字で雑にデータを捌く際に利用する位が適切でしょうか。用法容量を守って限定的に使う事をお勧めします。

Web.Page関数の利用について(2022/9/7追記)

TwitterにてTakeshi KagataさんからWeb.Page関数についてのコメントをもらいました。こちらの手法はJavaScriptを呼び出すにあたってM言語上でWeb.page関数を利用していますが、この関数はIE10が利用可能であることが前提条件になっています。そのためPower BI Service などのようなWEBサービス上では動作しないのではないかとのことでした。これをクリアするためには、ウェブブラウザを備えたシステム(オンプレミス データゲートウェイ)を別途用意する必要があるそうです。

実際にPower BIをしっかりと運用したことがなかったので大変に勉強になりました。

また、Web.Page関数はIE10を利用しているため、当然ながら利用できる正規表現もIE10で利用可能なJavaScript由来に限定されます。従いまして、?<=肯定の後読み、?<!否定の後読みは残念ながら本手法では利用できません。残念、無念。

そもそも論として

スプレッドシートには正規表現の関数があるじゃないですか。Excelのワークシート関数、M言語、DAXのそれぞれで正規表現を実装してくれたらこんな面倒なことしなくても済むんですよね…。