Excelの小ネタ　BYROW関数

スピル系の関数は、使いこなせれば便利ですが、慣れないとなかなか難しい。
今回は、私が勉強し始めて少なくとも３回は嵌っている問題を、備忘録として残します。

BYROW関数の基本的な使い方

まずはおさらい。
BYROW関数、BYCOL関数は、表の行や列に対して操作したいときに便利な関数です。
BYROWは各行に対して、BYCOLは各列に対して操作をするという違いがあるだけなので、今回はBYROWのみで説明します。

とりあえず、使い方を見てみましょう。
次のような表があったとします。

この表の各行ごとの合計を求めたい場合、普通ならSUM関数を使います。

この場合、E6セルに式を入力し、E10セルまでコピーします。

これを、BYROW関数で書くとこうなります。

式を入力しているのはE6セルのみです。
今回はSEQUENCE関数で表を作っていますので、範囲指定がB6#ですんでいますが、普通にB6:C10としても同じです。
BYROW関数で平均をとることもできます。

このほかにもCOUNTやMAXなど、標準的な関数を簡単に使用できますが、リストに無い関数を使いたい場合は、LAMBDA関数を使います。
試しにMOD関数を使ってみましょう。

このようにLAMBDA関数の中に組み込むことで、いろんな関数を使用できるようになります。

BYROWとオリジナル関数

もちろん、オリジナル関数も組み込めます。
次の関数を組み込んでみましょう。

FuncName : __Modpow

Discription : a^d (mod n) を求める

Argments : a, d, n, [result]

Function definition :
=LET(
    _r, IF(ISOMITTED(result), 1, result),
    IF(
        d = 0,
        _r,
        IF(
            MOD(d, 2) = 1,
            __Modpow(MOD(a * a, n), QUOTIENT(d, 2), n, MOD(_r * a, n)),
            __Modpow(MOD(a * a, n), QUOTIENT(d, 2), n, _r)
        )
    )
)

累乗数の剰余を求める関数です。
たぶんExcelには無い関数ですが、Pythonだと、$pow(a,d,n)$と、pow関数に第三引数を入れるだけで良いそうです。便利ですね。
さて、引数が３つですので、表も３列にしてみます。

各列の数字を取ってこないといけないので、ちょっと式が長くなりますが、無事に計算で来ているようです。
表は適当に作ったのですが、$14^{15} ≡ 0 (mod  16)$なのが、ちょっとビビりました＾＾；
よく考えれば、当たり前ですけど。

BYROW関数の注意点

さて、ここからが問題です。
次のオリジナル関数を、BYROW関数に組み込むことを考えてみましょう。

FuncName : __Decompose

Discription : 整数 n を d * 2^r の形に分解する。dは奇数。rは自然数。

Argments : n, [r]

Function definition :
=LET(
    _r, IF(ISOMITTED(r), 0, r),
    IF(
		MOD(n, 2) = 0, 
		__Decompose(QUOTIENT(n, 2), _r + 1), 
		CHOOSE({1, 2}, n, _r)
	)
)

decomposeとは、「分解する」という意味です。
整数を、$奇数 * 2^{整数}$という形に分解する関数ですね。
返り値は{ 奇数, 整数 }という横配列になります。

まず、BYROW関数に組み込まずに実行するとこんな感じ。

先に、B6:D6を合計して、__Decompose関数に渡しています。
SUM(B6:D6)は、$18$なので、$9*2^1$となります。
合っているようですね。
では、BYROW関数に組み込みます。

#CALC!エラーになりました。
詳細を見ると「入れ子になった配列」と表示されます。
つまり、スピルとは配列を作る行為であり、BYROW関数で作成される縦１列の配列の要素として、横１行の配列を入れることはできないということなんですね。
直観的には問題なさそうに見えますが、Excelはそれを許してくれない…。

同様のことはSCAN関数でも発生します。
SCAN関数のアキュムレータに配列を入れるとエラーになります。
SCAN関数の返り値が配列なので、要素として配列を入れるなということなんですね。

回避策

しかし、REDUCE関数はアキュムレータに配列を入れることができます。
こっちは返り値が配列ではないので、要素が配列でもいいよってことなんですね。
というわけで、無理やりREDUCE関数でスピルしたのがこちら。

やりようがあるのはいいのですが、すっきりしません。
Microsoftさんが何とかしてほしいですね。