Excelの新機能LAMBDA: VBA無しで関数を定義する

この記事はSpreadsheets/Excel Advent Calendar 2020の6日目の記事です。 (遅れてすみません。。もう1本の記事も近いうちに書きます。。)

予定を変更してExcelのすごい新機能が発表されていたので紹介します。

insider.office.com

上記の記事でLAMBDAという新しい関数が発表されました。 これは、Excelの数式を使って関数を定義できるという機能です*1

今までの関数の作成方法の問題点

今までExcelでは、ユーザが関数を定義するためにはVBAJavaScriptといったプログラミング言語を使うしかありませんでした。 しかし、これには以下のような問題点があります。

  • VBAはローカルのファイルアクセス等も自由に可能なため、セキュリティの問題がある。
  • ユーザがVBA等のプログラミング言語を覚える必要がある。

一方、Excelの数式ではこのような問題はありませんが、VBAのように関数を定義できないため、 複雑な数式であってもコピペ(またはオートフィル)して使わざるを得ませんでした。

みなさんも複雑な数式をデータの各行にコピペして、数式を修正したらまたコピペして、、ということをやった経験があるかもしれません。 しかし、これも以下のような問題点があります。

  • 数式を修正した場合、コピー漏れの危険性がある(VBAなら関数のコードを直せば良い)。
  • 数式が複雑になると、書いた本人以外が理解できなくなる(VBAなら関数に名前を付けたり、コメント書いて理解しやすくできる)。

新しい関数LAMBDAを使うと、Excelの数式を用いて再利用可能な関数を定義できるため、これらの問題点を解決できます

新関数LAMBDA

構文は以下のとおりです。

=LAMBDA([parameter1, parameter2, …,] calculation)

calculationに関数の本体となる数式を記述し、そこで使う引数を[parameter1, parameter2, …,]に必要な数だけ書きます。

例えば、1を足す関数は以下のように定義できます。

=LAMBDA(x, x + 1)

定義した関数の本体はx + 1、その引数はxです。

Excelの「名前の管理」のダイアログを使ってこの関数に名前をつけてみます。

f:id:kamocyc:20201207123417p:plain

「ADD_ONE」という名前をつけてみます。ここでコメントも保存できます。

f:id:kamocyc:20201207123543p:plain

すると、ワークシート上のセルで

=ADD_ONE(2)

のように利用することができます。 この書き方は通常のExcelの関数やVBAで定義した関数と同じですね。

この場合、定義した関数の本体において、x2が代入されて2 + 1の計算が行われ、計算結果3が返ります。

関数に名前をつけずに利用

実は定義した関数に名前をつけずに利用することもできます。

セルに

=LAMBDA(x, x + 1)(2)

と入力してみます(LAMBDA( ... ) の部分は先程のADD_ONEと同じです)。

この場合、LAMBDA(x, x + 1)の部分で定義した関数に、引数2が適用されて、3が返ります。

実際、LAMBDA(x, x + 1)の部分をADD_ONEに置き換えると、=ADD_ONE(2)という先程と全く同じ式になることを考えると理解しやすいかもしれません。

いつ使えるの?

LAMBDAはOffice Insiderの以下のバージョンで使えるということです。

  • Windows: Beta Channel Version 2012 Build 13519.20000 以上
  • Mac: Beta Channel Version 16.45 Build 1201.0 以上

ただし、アップデートのタイミングはユーザによって違うので、人によってはもう少しかかるかもしれません。 (私は記事執筆時点はまだ使えないようでした)

もう少し深入り

こちらの記事により詳しい説明もありました。

techcommunity.microsoft.com

再帰

実はLAMBDAでは、定義した関数の再帰的な呼び出しができます。 (これにより、循環参照に頼らなくても、Excelの数式がチューリング完全になります。)

実務上は何が嬉しいかというと、可変長のデータに対する処理など、ループ処理を必要とする関数が書ける点があります。

参照元の記事に載っていた以下の例では、A列の文字列から、B列の文字をすべて取り除いて、C列の文字列を返すような関数を定義することを考えます。

f:id:kamocyc:20201207130559p:plain

SUBSTITUTE関数によって、「A列の文字列中から、B列の1番目の文字を空白に置換」、「その結果の文字列から、B列の2番目の文字を空白に置換」... と行えばよいですが、B列のデータは可変長なので、ループ処理が必要になります。

この処理を行う関数REPLACECHARSを以下のように定義できます。

=LAMBDA(textString, illegalChars,
     IF(illegalChars="", textstring,
       REPLACECHARS( 
       SUBSTITUTE(textString, LEFT(illegalChars, 1), ""), 
       RIGHT(illegalChars, LEN(illegalChars)-1)
)))

REPLACECHARSの定義の中の3行目に、REPLACECHARS自身の呼び出しが含まれています。

どういう計算過程になるのか少し分かりづらいですが、textStringにA列のデータ、illegalCharsにB列のデータを代入した状態から始めて、再帰呼び出しごとのtextStringとillegalCharsの変化を順々に追っていくとわかると思います。

例えば、"Excel2003"から数字を取り除く例だと、次のようになります。

f:id:kamocyc:20201207134923p:plain

illegalCharsが再帰のたびに1文字ずつ減っていき、再帰の最後ではillegalCharsが空になるので再帰呼び出しが停止して、すべてのillegalCharsの文字が除外されたtextStringが返ります。

これによって、B列の文字を1文字目から順番に取り出して、A列の文字列から取り除く(空白に置換する)という処理が実現できます。

※追記: 再帰の制限

アップデートが来たので実際に試してみたところ、再帰呼び出しの深さの上限が511回になっているようです (それより深く再帰呼び出しをすると#NUM!エラーになります)。

末尾再帰最適化*2も行われないようです。

もちろん、これらの制限は今後変更される可能性があると思います。

試しに (今話題になっている) n番目の素数判定のナイーブなコードを書いてみましたが、68番目が限界でした。

nth_prime_naive.txt · GitHub

扱えるのは数値や文字列だけではない

LAMBDAで定義する関数で引数として取れるデータは、数値や文字列はもちろんですが、動的配列やカスタムデータ型なども使うことができます。 これらの値を返すこともできます。

なお、LAMBDAで定義した関数自体も引数や戻り値として扱えます関数型言語のような first-class function)。実際、LAMBDAの導入に伴い、関数データ型が新たに導入されたそうです。

今後

LAMBDA関数に関して、すでに多くの改良が計画されているということです。 例えば、「名前の管理」ダイアログでの関数定義のUIは改善の余地があると認めています。

また、配列の操作関数をこの先数ヶ月で追加することで、動的配列の利点をより活かしてLAMBDAで関数が定義できるようにしていくということです。

また、Excelの数式にLETやLAMBDAのような関数型言語のような機能が追加される一方で、開発やデバッグのための機能は主流のプログラミング言語に比べるとまだ貧弱と言わざるを得ないでしょう。

Microsoftの人も、数式の編集やデバッグのための必要とされるツールを追加できたらいいなということを書いているので、近いうちに改善されることを期待したいです。

*1:知っている人向けに書くと、これはラムダ式そのものです。

*2:関数型言語でよく行われる関数末尾の再帰呼び出しをループに変換する最適化