【スプレッドシートの便利な使い方】ARRAYFORMULAでダメな時のBYROW関数

【スプレッドシートの便利な使い方】ARRAYFORMULAでダメな時のBYROW関数

要約

スプレッドシートで同じ計算を繰り返し行う場合、ARRAYFORMULA関数を使うとコピペを減らすことができます。
ただし、範囲を指定した関数をARRAYFORMULA関数の中で使うと、うまく動かないことがあります。
そんな時に使えるのが、BYROW関数です。

スプレッドシートで同じ関数を繰り返すにはARRAYFORMULA関数が便利

スプレッドシートで同じ計算を繰り返し行う場合は、ARRAYFORMULA関数が便利です。
以下の記事にARRAYFORMULA関数の使い方をまとめましたので、参考にしてください。
https://zenn.dev/kzautomation/articles/beecf960ac5521

ただし、欠点もあります。
例えば、SUM()関数は、それ自体が範囲指定をする関数ですが、ARRAYFORMULA関数の中で使うと、うまく動きません。
ARRAYFORMULA(SUM(L5:L35))と入力すると、L5:L35の合計値が、L5:L35のセル数分表示されてしまいます。
そのため、範囲を指定しない関数に限定して使う必要があります。
これを解決するのがBYROW関数です。

BYROW 関数とは

Google のヘルプでは、BYROW関数は次のように説明されています。

この関数は、LAMBDA関数を各行に適用して、配列を行単位でグループ化します。
使用例
BYROW(A1:C3,LAMBDA(行,MAX(行)))
BYROW(A1:C3,LAMBDA(行,SUM(行)))
参照：BYROW 関数

LAMBDA関数が分かっていないと、どう使えば良いのか分からない関数です。。。

LAMBDA 関数とは

Googleのヘルプでは、LAMBDA関数は次のように説明されています。

一連の名前とそれらを使用する数式を含むカスタム関数を作成して返すことができます。数式を計算するために、名前で宣言されている個数の値を指定して、返された関数を呼び出します。
使用例
LAMBDA(Salary, Salary*0.3)(1000)
LAMBDA(Temp, (5/9)*(Temp-32))(85)
参照：LAMBDA 関数

また、LAMBDA関数内の説明を読むと、BYROW関数は、LAMBDAヘルパー関数というものの一つということのようです。
LAMBDA関数とLAMBDAヘルパー関数は、従来のスプレッドシートの関数とは、少し違う使い方をする関数のようです。

使い方

LAMBDA関数と LAMBDAヘルパー関数はかなり奥が深いものであるようですが、ここでは、ARRAYFORMULA関数で実現が難しかった、SUM関数などとの組み合わせについて、ご紹介します。

この記事で使用したスプレッドシートは以下になります。
合わせてごらんください。
https://docs.google.com/spreadsheets/d/1gyRHe9obHOWCKaaWt8tk-34kMHWEpMODiEp4g2awyDc/edit#gid=0

ARRAYFORMULA 関数で実現できなかった計算

ARRAYFORMULA関数を使うと、配列の各要素に対して、同じ計算を行うことができます。
しかし、ARRAYFORMULA関数内では、SUM関数などの配列の集計を行う関数を使うことがうまくできませんでした。

例えば、次のような A〜C列のデータについて、各行の合計を求める場合、ARRAYFORMULA関数を使おうとする場合、次のように書きたくなると思います。

A	B	C	D
1	4	7	=ARRAYFORMULA(SUM(A1:C3))
2	5	8
3	6	9

しかし、この結果得られるのは、

A	B	C	D
1	4	7	45
2	5	8
3	6	9

という表になってしまい、各行の合計ではなく、全てのセルの合計がD1セルに表示されてしまいます。
これは、SUM関数自体が、ARRAYFORMULAで無くても、この配列の合計を求める関数であるためです。

BYROW 関数で実現できた計算

Google のヘルプにある、BYROW関数の使用例の一つを検証します。
改めて冒頭の BYROW 関数の説明について、もう一度見てみます。

この関数は、LAMBDA 関数を各行に適用して、配列を行単位でグループ化します。
使用例
BYROW(A1:C3,LAMBDA(行,MAX(行)))
BYROW(A1:C3,LAMBDA(行,SUM(行)))
参照：BYROW 関数

上記では、行が日本語になっていますが、英語のページでは rowと記載がありますので、rowのままの方が、変数名として読みやすいのではないかと思います。

BYROW(A1:C3,LAMBDA(row,MAX(row)))
BYROW(A1:C3,LAMBDA(row,SUM(row)))
参照：BYROW 関数

そういうことで、BYROW関数と、その中のLAMBDA関数を使うと、次のように書くことで、各行の合計を求めることができるようです。

A	B	C	D
1	4	7	=BYROW(A1:C3,LAMBDA(row,SUM(row)))
2	5	8
3	6	9

この結果得られるのは、以下の表です。

A	B	C	D
1	4	7	12
2	5	8	15
3	6	9	18

このように、各行の合計を求めることができました。
ARRAYFORMULA関数を利用して、このような形で各行の合計を出したかった方は多いのではないでしょうか。

ざっくりなイメージ

ざっくりなイメージとしては、BYROW関数の第一引数であるA1:C3は、この関数を適用する範囲を指定していますが、行と列で扱いが異なります。
1〜3 については、各行に適用するということを意味しています。
一方で、A〜C については、各行で計算する際の、LAMBDA関数の引数として渡される列を意味しています

1行目の値を計算される際には、
A1:C1の値が、LAMBDA関数の rowという引数に配列として渡され、SUM関数によって合計が求められます。

同様に、2行目には
A2:C2の値が、LAMBDA関数の rowという引数に配列として渡され、SUM関数によって合計が求められます。

3行目には
A3:C3の値が、LAMBDA関数の rowという引数に配列として渡され、SUM関数によって合計が求められます。

イメージを掴めたでしょうか？

まとめ

BYROW関数はARRAYFORMULA関数を適用できない場面で使うことができます。
ARRAYFORMULA関数と同様に、同じ計算を繰り返す際にコピペを減らすことができ、どこまでが同じ計算式を使っているのかが一目で分かるため、ミスを防ぐことができます。
スプレッドシートの作業効率を上げるために、ARRAYFORMULA関数と合わせてBYROW関数を使いこなしてみてください。