不可思議なスプレッドシートの世界「ArrayFormula関数内でSUM関数を使いたい！」の謎を解明する

はじめに

Googleスプレッドシート はオンラインで利用できる表計算ソフトとして非常に有用なのですが、ARRAYFORMULA や QUERY といった複雑な関数が使われると、直感的に分からない挙動を示すことがあります。

私は最近「ArrayFormula関数内でSUM関数を使いたい！」という記事に出会いました。
記事の中でも「3. 行列積を使って各行の和を求める」の方法は非常に鮮やかで感動したのですが、どうも「2. SUMIF関数を使う」は何故うまく動作しているのか分からず、記事中でもそのメカニズムは解明されていませんでした。
本記事ではそのメカニズムを細かく解明していきたいと思います。

課題: 今回解明していく「謎の数式」

ARRAYFORMULAを使って、各行の合計値(SUM)を1セルだけ数式入力して求めたいという状況です。
⇒

上の例でF2セルに入力した数式と一般化した数式は以下の通りです。

入力した数式
=ARRAYFORMULA(SUMIF(IF(COLUMN(B2:E2), ROW(B2:B8)), ROW(B2:B8), B2:E8))
一般化した数式
=ARRAYFORMULA(SUMIF(IF(COLUMN(1番上の行), ROW(1番左の列)), ROW(1番左の列), 全範囲))

一般化した数式をある程度インデントを揃えて書いてみると以下のようになります。

=
ARRAYFORMULA(
  SUMIF(
    IF(COLUMN(1番上の行), ROW(1番左の列)),
    ROW(1番左の列),
    全範囲
  )
)

関数がネストしていて分かりにくいのは仕方ないとしても、謎ポイントがたくさんありますね。
さっそく謎を解明していきましょう！

謎1. IF関数の引数は3つのはずなのに2つしか書いてないんだけど...?

要旨: 第3引数を省略すると、第3引数にFALSEを指定した時と同じ挙動を示します。

1.1 IF関数の定義を調べる

Google公式ドキュメントで IF関数 のページを見てみると、

IF(論理式, TRUE値, FALSE値)
論理式 - 論理値（TRUE か FALSE）を表す式またはそうした式を含むセルへの参照です。
TRUE値 - 論理式が TRUE の場合に返される値です。
FALSE値 - [省略可 - デフォルトは空白] - 論理式が FALSE の場合に返される値です。

とありますね。
なるほど、FALSE値は省略すると空白扱いになるらしいので問題無いと。
では挙動を確かめてみましょう。

1.2 引数3つのIF関数の挙動

まず、次のような引数3つのIF関数を書いてみます。
=IF(FALSE, "真", "偽")

第1引数がFALSEなので第3引数の"偽"が出力されてますね。

1.3 引数2つのIF関数の挙動

では問題の引数2つのIF関数を書いてみます。省略すると空白扱いになるんでしたね。
=IF(FALSE, "真")

うん？省略したら空白ではなくFALSEが出力されちゃってますね...
実はこの現象、ドキュメント側が間違っているようで、IF関数の引数3つ目を省略すると、デフォルトでFALSE扱いになります。
(英語のドキュメントでも blank by default って書いてあるので日本語の誤訳でも無いようで...)

第3引数を空白にしたい場合には次のようにすると上手く動きます。
=IF(FALSE, "真",)

後ろに,が付いただけですが、これは「第3引数は存在するが何も書いていない」という書き方で、第3引数は空白であることを明示的に指定できる方法です。

1.4 引数が2つのIF関数まとめ

というわけでIF関数の引数を2つにすると、第3引数はFALSE扱いになるというお話でした。

これで、問題の式は、
IF(COLUMN(1番上の行), ROW(1番左の列))
　　　　　　　　　　　⇓
IF(COLUMN(1番上の行), ROW(1番左の列), FALSE)
と同じ意味になります。

これでもまだ「謎の数式」の全容はあまり見えてきませんが...
気を取り直して次に行きましょう。

謎2. IF関数の第1引数が真偽値じゃない...?

要旨: IF関数の第1引数は、0や空白、空文字列といった値はFALSE扱いになるという特性があります。

2.1 「そうした式」

IF関数 の公式ドキュメントをもう一度見てみましょう。

IF(論理式, TRUE値, FALSE値)
論理式 - 論理値（TRUE か FALSE）を表す式またはそうした式を含むセルへの参照です。

よく見ると、ものすごい曖昧な文言がありますね。 論理値を表す式「またはそうした式」？
詳しいことは分かりませんが、ここから読み取れるのはどうやら論理値以外を入れるケースもあると。

2.2 IF関数の第1引数に整数を入れてみる

今回の問題としている式では、IF関数の第1引数に、整数を返すCOLUMN関数が入っています。
というわけで、IF関数の第1引数に整数を入れた場合の挙動を見てみましょう。

以下の3通りを試してみます。
=IF( 3, "真", "偽")
=IF( 0, "真", "偽")
=IF(-1, "真", "偽")

結果は以下の通り。



プログラミングをある程度やったことある方々にはピンと来そうな結果が出ましたね。
C言語やJavaScriptと同様に、論理式に整数を入れると0の場合FALSE、0以外の場合TRUEという挙動を示すようです。
また、実際に試した画像は省略しますが、IF関数の第1引数を空文字列や空白にした場合もFALSE扱いになることも確認しています。

2.3 IF関数の第1引数における論理値判定まとめ

IF関数の第1引数に整数を入れる際には、

・0以外を入れる　→　TRUE
・0を入れる　　　→　FALSE

ということが分かりました。つまり、
IF(COLUMN(1番上の行), ROW(1番左の列))
の場合には、COLUMN関数は必ず1以上の数を返すので、結果としては必ずTRUEとなり、IF関数全体では常にROW(1番左の列)が返るわけですね。
謎1を考察した意味がねえ...！

謎3. 結局、IF(COLUMN(1番上の行), ROW(1番左の列)) って何が起きてるの？

要旨: IF関数の第1引数が常にTRUEなので、ROW関数により指定範囲のセルがそれぞれ何行目かの値が返ります。なお、ROW関数は行方向、COLUMN関数は列方向にしか展開されないのですが、ROW関数を2次元展開したいがために回りくどい書き方をしています。

3.1 取り敢えず結果を眺めてみる

「謎の数式」からSUMIFを取り除き、ARRAYFORMULAやIFはそのまま残した数式を入力してみましょう。
対象とする範囲はB2:E8とするので、「一番上の行」はB2:E2、「一番左の列」はB2:B8になりますね。

=ARRAYFORMULA(IF(COLUMN(B2:E2), ROW(B2:B8))))

何やら綺麗な結果が返ってきましたね。B2:E8の各セルの行数が出力されているようです。
2行目のセルであれば2と出力され、3行目では3と出力、8行目では8と出力されています。

先ほど2.3で触れた通り、COLUMN(B2:E2)は常に1以上の整数なのでIF関数は真、つまりROW(B2:B8)の結果を返します。
よってB2:E8の行数が出力されるのは当然と言えば当然です。が、ここで1つの疑問が生まれてきます。

3.2 ARRAYFORMULA(ROW(全範囲))って書いちゃダメなの？

ここまで紐解いてきてお分かりかと思いますが、この数式は非常に回りくどい書き方をしています。
=ARRAYFORMULA(IF(COLUMN(B2:E2), ROW(B2:B8))))

「指定範囲のセルがそれぞれ何行目かを示している」と言えば一発ですが、IF関数やCOLUMN関数が書いてあることによって、分かりにくい数式となってしまっています。
直感的に分かりやすい数式にするなら次のような形が良さそうです。
=ARRAYFORMULA(ROW(B2:E8))

では実際に入力してみましょう。

んんん？ARRAYFORMULAによって縦には展開されているけど横には展開されない...？

ちょっとROWをCOLUMNに変えてもう一回試してみましょう。
=ARRAYFORMULA(COLUMN(B2:E8))

これではっきりしましたね。
ARRAYFORMULAを利用した際にはROW関数は行方向、COLUMN関数は列方向にしか展開されないんです。
まあROWやCOLUMNの用途を考えれば2次元展開する必要はあまり無さそうではあるのですが...
だからROW関数を列方向にも伸ばしてやりたい場合には、COLUMN関数を併用して、IF関数で無理矢理繋げる必要があったんですね。
ARRAYFORMULAによる展開は、中身の関数によって2次元的に展開できるものだったり、縦か横にしか展開できないものだったり、縦にも横にも展開できないものだったりするので、ここはかなり理解が難しい仕様です。

もちろんIF関数などではARRAYFORMULAによる2次元展開が可能なですね。
=ARRAYFORMULA(IF(B2:E8 = 100, "満点！",))

3.3 IF(COLUMN(1番上の行), ROW(1番左の列)) のまとめ

ARRAYFORMULAで展開できる方向は、中身の関数によって異なるために注意が必要です。
そして IF(COLUMN(1番上の行), ROW(1番左の列)) はARRAYFORMULAでROW関数を2次元的に展開するためにわざわざ回りくどい書き方をしていたというお話でした。

謎4. SUMIF関数の挙動が理解できない...

要旨: SUMIF関数の第1引数に数式を入れた場合、範囲は単に第1引数に入力した範囲となりますが、第2引数の条件として判定される値は、第1引数の数式の結果となります。また、第3引数を指定しない場合、第1引数の数式の結果の総和がSUMIF関数の返り値となります。

4.1 SUMIF関数の定義を調べる

Google公式ドキュメントで SUMIF関数 のページも調べてみましょう。

SUMIF(範囲, 条件, [合計範囲])
範囲 - 条件と比較して検証する範囲です。
条件 - 範囲に適用するパターンまたはテストです。
合計範囲 - 合計する範囲です。範囲と異なる場合に指定します。

一部説明を省略していますが上記の通りです。
普通にSUMIF関数を使った場合の例を挙げます。
(画像では間違ってARRAYFORMULAが入ってますが、無くても同じ挙動です)

D3 → =SUMIF(B3:B21, ">0")
E3 → =SUMIF(B3:B21, "<0")

このように第1引数に範囲、第2引数に加算する値の条件を書けば条件に合ったものだけを全て加算するのがSUMIF関数です。

また、第3引数に範囲を指定した場合には、加算する対象だけ別範囲にすることが出来ます。
(こちらも画像では間違ってARRAYFORMULAが入ってますが、無くても同じ挙動です)

E3 → =SUMIF(B3:B12, ">0", C3:C12)
F3 → =SUMIF(B3:B12, "<0", C3:C12)

第1引数に条件用の範囲、第2引数に条件、第3引数は条件を満たしている場合に加算する範囲という形ですね。
ここまでが普通の使い方ですが、これだけでは今回の謎はまだ解けません。

4.2 SUMIF関数の第1引数に数式を入れてみる

今回の問題は、SUMIF関数の第1引数に、単なる範囲ではなく数式が入ってしまっていることです。
では、数式が入っているとどのような挙動になるのか見てみましょう。

=ARRAYFORMULA(SUMIF(A2:A11 + 3, ">10"))

ARRAYFORMULAが適用されているため、1, 2, ..., 10 はそれぞれ+3されて 3, 4, ..., 13 となります。
計算前に10より大きい数はありませんが、計算後の値で 11, 12, 13 の3つは10より大きいため、これらの総和の 11 + 12 + 13 = 36 が値として返ります。
第1引数の範囲は変わりませんが、第2引数の条件は「第1引数の数式の計算後の値」で判定されるのようですね。

ではさらに次の例です。
=ARRAYFORMULA(SUMIF(A2:A11 + 3, ">10", A2:A11))

先ほどの例の第3引数に A2:A11 を入れた形です。最終的な値が36から27に変わっていますね。
こちらは先ほど同様、3を足すと 11, 12, 13 が10より大きくなるため加算対象となりますが、第3引数があるため、実際に足すのは第3引数の値(つまり元の値)となります。
よって、3を足す前の 8 + 9 + 10 = 27 が返ります。
つまり、「3を足したら10を超えるものに対して、元の数の総和」を求めている式になるんですね。
うーんややこしい。

しかし、これでようやく元の式を紐解く準備が整いました。

4.3 SUMIF関数の第1引数に数式を入れた場合の挙動まとめ

通常のSUMIF関数

• 第2引数は条件
• 条件で判定される範囲は第1引数
• 総和の対象となる範囲は、第3引数が存在すれば第3引数、第3引数が存在しなければ第1引数

第1引数に数式が使われるSUMIF関数

• 第2引数は条件
• 条件で判定される範囲は第1引数で指定した範囲だが、条件判定の値は数式の計算結果
• 総和の対象となる範囲は、第3引数が存在すれば第3引数、第3引数が存在しなければ第1引数だが、総和の対象となる値は数式の計算結果

では最終決戦と行きましょう！

まとめ: 「謎の数式」を解明する

「謎の数式」を再掲します。
⇒

=
ARRAYFORMULA(
  SUMIF(
    IF(COLUMN(B2:E2), ROW(B2:B8)),
    ROW(B2:B8),
    B2:E8
  )
)

まず、ARRAYFORMULA化されている IF(COLUMN(B2:E2), ROW(B2:B8)) は、COLUMNが常に1以上でIF関数がTRUEになり、ROWの値が2次元的に返るんでしたね。

=ARRAYFORMULA(IF(COLUMN(B2:E2), ROW(B2:B8)))) (再掲)

次にSUMIF関数の視点から見ると、まず第1引数の範囲はB2:E8です。
そして第2引数の条件はROW(B2:B8)、これはイコールが省略された形で、"=" & ROW(B2:B8)と第2引数に書くことと同義です。
ただし第2引数の条件は、通常の場合は範囲ではなく単数で書くはずなので、ARRAYFORMULA関数の効果により複数行に展開されます。
つまり、2行目のF2では"=" & ROW(B2)、3行目のF3では"=" & ROW(B3)、8行目のF8では"=" & ROW(B8)が第2引数である際の結果が返ります。
そして第3引数は元の範囲B2:E8を指定しているため、第1引数のROW関数の値ではなく元のテストの点数の総和を返します。

よって、F2に返る値は、「2行目から8行目のROW関数の結果のうち、2行目のROW関数の値と一致する場所の元の値の総和」、F3に返る値は、「2行目から8行目のROW関数の結果のうち、3行目のROW関数の値と一致する場所の元の値の総和」、...というように、F2:F8から見て「自身の列と同じ列だけがSUMIFの条件に当てはまり、元の値の総和が返ってくる」のです。

(再掲)

おわりに

本記事ではGoogleスプレッドシートに関して、リファレンスに載ってない複雑な事例を実際に試し、1つ1つ挙動を確かめてみました。
予想以上に長くなってしまいましたが、スプレッドシートの挙動を深く理解したい方々の一助になれば幸いです。

ここまでお読みくださり、本当にありがとうございます！