AVERAGE

AVERAGE関数をarrayformula内で使用する時のhow-toです。

※参考：AVERAGE - ドキュメント エディタ ヘルプ (google)

例えば、

のように、同一の行にある3列のセルの平均を計算したい、という場合です。

$を利用して固定するかは人によって別れると思いますが、通常の全てのセルに書いていく形式だと以下のようになります。

ただし、Arrayformulaは自動的に列ごとに計算してくれるわけではないため、A2からC6のセルを全て引数に渡しても自動的に行ごとに展開してくれるわけではありません(bad example 1行目)。

また、AVERAGE関数は1つ以上の引数の平均を1つのセルに返す関数のため、引数を列ごとに分けて渡しても同じ結果になってしまいます(bad example 2行目)。

AVERAGE関数に似た関数として"条件を満たしたセルの平均を返す"というAVARAGEIFという関数がありますが、これもAVERAGEと同様1つのセルに結果を返す関数のため1番上の行しか計算してくれません(bad example 3行目)。

そこで、かなり強引にはなりますが、ここでは

• "行列の積を返す"MMULT関数

• "行列の転置を行う"TRANSPOSE関数

• "条件を満たしたセルの個数を返す"COUNTIF関数

• "そのセルの行番号を返す"ROW関数

• IF関数

の5つを利用します。

具体的に何をしているかというと、

• TRANSPOSE関数で平均して欲しいセルのサイズ(ここでは3行1列)との積の結果が1行1列になるような行列を作る(explain 1番目)

ここで作った行列と平均したいセルの内容の積をArrayformulaを利用して計算するとArrayformula内で3行5列のセルを自動的に3行1列の行列5列分と認識して積を計算し、合計(＝分子)を計算してくれます(explain 2番目)。

更に、

• SUMIFの1つ目の引数でA2からC6までの全てのセルにそれぞれの列番号を割り振る

• SUMIFの2つ目の引数でそれぞれの列番号を渡す

以上のようにすることで、explain3番目のresultのように特定の行のセルにのみTRUEに、それ以外の行のセルには全てFALSE になるテーブルを作り、COUNTIF関数でこれがTRUEとなるようなセルの個数を数えることで分母を計算しています。

参考：arrayformula for average - Google プロダクト フォーラム
https://productforums.google.com/forum/#!topic/docs/fjoHsgc9URE;context-place=topicsearchin/docs/arrayformula$20average