Excel触っているとたくさん(3つ以上)のセルが一致しているかどうかを数式で書きたい場面出てこないだろうか、自分はたまにある。まあ5,6個程度ならAND関数を連結してもいいんだけど、もっと数が増えると？ググると解決策は割と簡単に出てくるんだが、どうもExcelの数式はちょっと複雑になるともう何だかよく分からなくなる。
そこでふとそう言えばlambda使ってユーザ定義関数作れないだろうかと思い作ってみた。
作り方の基本はこれもググれば出てくるので割愛。
2024/05/27 記事更新
5/23 上手く行かないケースの指摘を受けて確かにと思い記事更新。
5/27 セル配列で列を渡した際上手く行かなかったケースに対応。
色々とご指摘頂いたので対応できるように更新。
ついでに指摘頂いた方ほど汎用的なわけではないが、再帰使ったコードに変えてみた。
これで{FALSE,FALSE,FALSE}ケースでも TRUE返すようになった。
名前: _countall(cells)
=REDUCE(0, cells, LAMBDA(x, _, x + 1))
名前: _eqall(cells)
=LAMBDA(cells,
LET(
_eq2, LAMBDA(x, y, x = y),
arr, TOCOL(cells),
SWITCH(
_countall(arr),
1, TRUE,
2, _eq2(INDEX(arr, 1), INDEX(arr, 2)),
IF(_eq2(INDEX(arr, 1), INDEX(arr, 2)), _eqall(DROP(arr, 1)))
)
)
)
再帰の上限
公式のLAMBDA関数の説明では数は明記されてないが、
再帰が多すぎるとエラーとなると書いてあったので試してみると、どうやら1639個目で#NUM!となった。どういう上限設定なんだろ。
1048576個(Excel2007以降の行上限数)対応版
色々試してると、セル配列ってそのものを=比較できる事を知ったので、それを利用した版も書いてみた。
2024/05/29 更新
- _andall は通常の AND でも同等の動作をするとアドバイス頂いたので更新
名前: _andall
通常のANDで行けるので未使用
=LAMBDA(cells,REDUCE(, cells, LAMBDA(x,y, IF(x = FALSE, FALSE, AND(x, y)))))
名前: _eqall2
=LET(arr, TOCOL(cells), AND(DROP(arr, 1) = DROP(arr, -1)))
こっちは_eqall2(A:A)としてもTRUEを返してくれる。
これどこまでも行けるのかなと_eqall2(A:B)を試したがこちらは#NUM!が返ってくる。
考えてみればTOCOL関数で行配列に変換してるからそこの上限に引っかかるのかな。
旧コード
指摘を頂く前の旧コードは以下に残しておく。
旧コード部分
名前の管理画面で以下のように入れる。使うのはeqの方。
| 名前 | 参照範囲 |
|---|---|
| eqfn | =LAMBDA(x,y, IF(x=y, x, FALSE)) |
| eq | =LAMBDA(cellrange, REDUCE(,cellrange,eqfn) |
テスト。以下のような値が入ってる場合
| A1 | B1 | C1 |
|---|---|---|
| 1 | 2 | 3 |
=eq(A1:C1)
-> FALSE
こちらだと、
| A1 | B1 | C1 |
|---|---|---|
| 1 | 1 | 1 |
=eq(A1:C1)
-> 1
ごく簡単なものだけど個人的に初めてlambda使って実用し得るユーザ定義関数作れたと思うので記事をアップ。
毒。Windowsだからって不必要にベタベタ画面コピー貼らなくていいと思う。視認性下がる。