【Googleスプレッドシート】 スピる？スピらない？ COUNTIFS, SUMIFS の Arrayformula 対応
最近 「COUNTIFSは Arrayformulaが効かない。」「COUNTIFSをスピらせようとするとエラーが出る！ 」といった誤解のある 書き込みを何度か見かけたので、
COUNTIFS、Arrayformulaでスピるってよ
ってネタを書いておきます。（部活やめるってよ風）
ついでに 他のIFS系関数のARRAYFORMULAとの組み合わせや、SUMIFS、COUNTIFSの配列処理 の EXCELとの比較もまとめてみました。後半にはチラッと QUERY関数も登場！
現時点で 普通に COUNTIFSやSUMIFS関数は使えるけど、もう少し上のレベルの処理を理解したい人向けの内容です。
シリーズ前回の記事（IMPORTRANGEとQUER関数の 不具合があったので号外ネタを入れました）
〇〇IFS系の基本
〇〇IF や 〇〇IFS といった、いわゆる IF(S) 系関数は、指定した条件に合致した データを集計する際に用いる 関数です。
Googleスプレッドシートで使える 〇〇IFS系 関数 一覧
〇〇IFが 1つの条件、〇〇IFSだと複数条件を指定することができます。
COUNTIF, COUNTIFS
条件に合致する データの個数を返す
SUMIF, SUMIFS
条件に合致するデータの 値の合計を返す
〇〇IFS関数は複数条件が指定可能ですが、 1つだけの単体条件でCOUTIFSやSUMIFSを利用することも可能です。
というわけで、COUNTIFやSUMIFの 上位互換が COUNTIFS、SUMIFSと言えます。
Excelに 後から追加された MAXIFSやMINIFSは 最初からIFSのみで、1条件指定の MAXIF や MINIF という関数はありません。
じゃあ、〇〇IFは使わないで 〇〇IFS系だけ覚えればいいんじゃね？
と思うかもしれませんが、実はGoogleスプレッドシートの場合は、これらの関数と Arrayformulaと組み合わせた時の挙動が若干違います。
〇〇IFS系関数と ARRAYFROMRULAの 対応表
いきなり、今回の本題です。
Googleスプレッドシートにおいて、〇〇IF、〇〇IFS関数と Arrayformulaを組み合わせた際、結果を配列で返せるか？（スピるか？）をまとめてみました。
上のように 〇〇IF系は AVERAGEIFだけが Arrayformula が使えません。他は 機能します。
一方、〇〇IFS系は COUNTIFS以外は 全滅です。
SUMIFは 〇ですが、SUMIFSは ✖ なんですよね。というわけで、後ほど解説しますが 状況によってはSUMIFを使うべき時もあるってことです。
さらに COUNTIFSも 〇ではなく、△としています。これは、挙動に独特のクセ（仕様）がある為です。
「COUNTIFSは Arrayformulaが効かない。」「COUNTIFSをスピらせようとするとエラーが出る！ 」と言われる原因は、この独特な仕様のせいだと思われます。これも後ほどお題形式で説明していきます。
Googleスプレッドシートにおいては
〇〇IFS系は Arrayformulaと相性が悪くて、ほぼ スピらない
」（COUTIFSを除く）
ってことを、まずは覚えておきましょう。
〇〇IFS系関数の特徴 と 代替関数
〇〇IFS系関数には 以下のような特徴があります。
■ 条件部分ではワイルドカードが使える
■ IFS系の 複数条件は AND条件
■ 基本的には セル範囲のみを対象とし、配列には利用できない
（COUNTIF,COUNTIFSは配列にも対応）
■ 条件部分ではワイルドカードが使える
ワイルドカードが使えるので 条件で 〇〇という文字を 「含む」 が使えるのは便利ですね。
たとえば 名前（A列）に、G3セルの文字を 含む 人数を数えたい場合は、COUNTIF の 第2引数 条件部分の記述を
"*"&G3&"*"
と、このように &で "*" を セル参照の前後に連結することで、G3の文字を 含む を条件とすることができます。
■ IFS系の 複数条件は AND条件
COUNTIFS で このように 性別 と 血液型を条件とした記述をした場合は、
「性別が 男 かつ（AND） 血液型が A」
と AND条件になります。
OR条件による カウントがしたい場合は、COUNTIFSで出来ないこともないですが おススメしません。
■ 基本的には セル範囲のみを対象とし、配列には利用できない
（COUNTIF,COUNTIFSは配列にも対応）
引数は範囲である必要があります。
{ } で括ることで、範囲を配列化すると 上記のようなエラーが出ます。
配列を対象としたときに機能しないのは、 作業セル使わずに一つの式で完結させたい時に結構困りますね。
ちなみに IFS系で 第1引数である 集計範囲を C2:C → {C2:C} と配列化するとエラーになりますが、条件範囲である 第2引数(D2:D) や 第4引数(E2:E) の方は サイズが合っていれば配列利用が可能です。
■ 〇〇IFS系 代替関数
Arrayformula が効かなかったり、配列に対して使えないと困る、またはOR条件で集計したいといったケースで、〇〇IFS関数の代わりによく用いるのが以下の関数です。
SUMPRODUCT ・・・ OR条件もいけて 柔軟に使える レガシー集計関数
FILTER + SUM, AVERAGE, MAX, MIN ・・・ FILTERで絞り込んで集計
QUERY ・・・ グループ化とpivotで クロス表を一発生成
MAP ・・・ ARRAYFORMULAでスピらない式をスピらせる
特徴や 代替式を詳しく説明してしまうと、これだけで終わってしまうので、今回はこれくらいにして 本題にいきましょう。
〇〇IF系とArrayformula
まずは 単体条件の 〇〇IF系と Arrayformula の組み合わせを見ていきましょう。
COUNTIF と Arrayformula はスピる
=ARRAYFORMULA(COUNTIF(B2:B11,F3:F4))
条件部分を F3:F4 として Arrayformulaすることで、それぞれの条件毎の結果がスピって出力されます。
SUMIF と Arrayformula はスピる
=ARRAYFORMULA(SUMIF(B2:B11,F3:F4,C2:C11))
これも問題なくスピってますね。
SUMIF(範囲, 条件, [合計範囲])
この 第1引数の 範囲と 第3引数の 合計範囲は、どちらも 1列、または1行のデータを指定する必要があります。
そして、「特徴」で触れましたが、第3引数の合計範囲は 配列は指定ができません。ここも注意。
※第1引数の条件範囲は 配列でもOK。
=ARRAYFORMULA(SUMIF(INT(A2:A),D3:D4,B2:B))
SUMIFSの 第1引数の 条件範囲で配列を利用する例として、上のような 日時と個数のデータから 日付を指定して 個数の合計を出力したい。といったケースが考えられます。
第1引数の 条件範囲を INT(A2:A) とすることで、A列の日時 のシリアル値の小数点以下を除去することで、日付のみのデータ に変換という 配列処理をしています。（ここにも Arrayformulaが効いてる）
INTを使った日時の日付変換処理に関しては 過去noteを参照ください
〇〇IF系 や 〇〇IFS系は SUMPRODUCTやFILTERと違って、内部引数の配列処理をしてくれないので、結果をスピらせない 場合でも このようなケースでは Arrayformulaが必要になります。
AVERAGEIF と Arrayformula はスピらない
=ARRAYFORMULA(AVERAGEIF(B2:B11,F3:F4,C2:C11))
あとは 平均を出力する AVERAGEIFだけなんですが、なぜか SUMIFはArrayformulaでスピるのに AVERAGEIFはダメなんです。
1つ目だけ計算され、それ以降が出力されません。
スピらんな～。
って言いたくなりますねｗ
AVERAGEIF と Arrayformula の代わりの式を考える
じゃあ 平均を出すのに AVERAGEではなく、SUMIF（合計）したものを COUNTIF（人数）で割ればよいんじゃね？
という発想で
=ARRAYFORMULA(SUMIF(B2:B11,F3:F4,D2:D11)/COUNTIF(B2:B11,F3:F4))
こんな式を思いつくかもしれません。
先ほど スピることを確認した SUMIFとCOUNTIFを 組み合わせた式なんで、これはスピります！
でも、惜しくもこれだと セル範囲に空白が含まれた場合、AVERAGEIFで計算した結果とズレが生じます。
AVERAGE系関数は 平均を出す際に 空白や数値以外を自動で除外してくれますが、SUMFIFした結果をCOUNTIFした結果で割った場合は、そうなりません。
上のように 数学のテストの日に 休んだ為に結果が空欄の 柏崎さんを 上は含めて（0点扱いで）平均値を出している為、54.75 と AVERAGEIFの結果 73に比べ 大幅に下がってしまっています。
では、どうするか？
COUNTIFの方を COUNTIFSにして 性別の条件に加え、数学の点数列が 空白ではない という条件を追加すればよいです。
=ARRAYFORMULA(SUMIF(B2:B11,F3:F4,D2:D11)/COUNTIFS(B2:B11,F3:F4,D2:D11,"<>"))
空白ではないという条件の書き方は "<>" とします。
これで AVERAGEIF と同じ結果を 1つの式で得ることができました。でも、ちょっと長い式で大変ですよね。
LAMBDAヘルパー関数（MAP）で ARRAYFORMULAが効かない式をスピらせる
そこで登場するのが LAMBDAヘルパー関数 の一つである MAPです。（今回はBYROWでもOK）
=MAP(F8:F9,LAMBDA(v,AVERAGEIF(B2:B11,v,D2:D11)))
F8:F9 の要素を1つずつ取り出し vと置き、それぞれ計算して結果を返す。
Arrayformulaが効かない式も スピらせることが出来るようになったのは、この新関数 LAMBDAヘルパー関数が 2022年9月 に Excelから輸入されたおかげです。
単体条件の 〇〇IF関数 については以上です。
COUNTIFS と Arrayformula
続いて 複数条件をとれる 〇〇IFS関数 と Arrayformulaの組み合わせを見ていきましょう。
まずは、〇〇IFS系で 唯一 Arrayformulaが使える COUNTIFS です。お題形式でいきましょう。
Q1. リスト型集計で COUNTIFSをスピらせたい
まずは基本問題。上のような構成で、G3:H6 の性別と 血液型の条件をもとに、I列に その2つの条件に合致する 人数を出力させてみましょう。式を入れるのは I3のみです。
前提条件として LAMBDAを使うのではなく、Arrayformulaを使うこととします。
↓ 今回のお題は全てのこのサンプルデータを使います。（コピペでスプレッドシートに貼り付け）
氏名 氏名（ひらがな） 年齢 性別 血液型
中濱 賢 なかはま けん 53 男 A
工藤 真那 くどう まなみ 39 女 B
早川 隆則 はやかわ たかのり 69 男 A
佐々木 達也 ささき たつや 31 男 AB
高杉 恭子 たかすぎ きょうこ 27 女 B
安宅 佐知子 あたか さちこ 79 女 B
佐藤 彩子 さとう さいこ 56 女 A
松井 裕美 まつい ゆみ 62 女 AB
佐藤 裕子 さとう ゆうこ 38 女 A
大内 卓也 おおうち たくや 39 男 A
菅原 強 すがわら つよし 56 男 AB
澤 孝行 さわ たかゆき 24 男 A
安藤 直之 あんどう ただゆき 29 男 A
山本 吉史 やまもと よしふみ 62 男 A
藤原 直樹 ふじわら なおき 32 男 B
加瀬 香織 かせ かおり 56 女 AB
大城 はる おおしろ はる 25 女 A
梅野 健治 うめの けんじ 42 男 O
平澤 俊英 ひらさわ としひで 58 男 A
前田 久美子 まえだ くみこ 45 女 A
山口 明子 やまぐち あきこ 26 女 A
大森 陽子 おおもり ようこ 75 女 A
佐藤 沙織 さとう さおり 27 女 B
斎藤 剛 さいとう つよし 71 男 A
境田 千賀子 さかいだ ちかこ 41 女 AB
赤岩 賢 あかいわ けん 38 男 AB
森 賢一 もり けんいち 41 男 O
武田 杏子 たけだ きょうこ 24 女 O
川口 ゆかり かわぐち ゆかり 45 女 O
赤井 淳 あかい じゅん 36 男 A
石井 央樹 いしい ひろき 40 男 O
今泉 晋 いまいずみ すすむ 59 男 O
田村 文香 たむら あやか 36 女 O
佐々木 崇 ささき たかし 27 男 AB
秋山 恵子 あきやま けいこ 45 女 A
高野 和則 たかの かずのり 21 男 A
湯淺 円 ゆあさ まどか 62 女 B
石本 邦彦 いしもと くにひこ 58 男 O
荒木 周 あらき しゅう 35 男 O
関岡 千里 せきおか ちさと 43 男 B
藤原 博史 ふじわら ひろふみ 52 男 B
有田 孝 ありた たかし 77 男 O先ほどの 〇〇IF系と大きくは変わりません。簡単なので、まずはチャレンジしてみましょう。
↓↓↓
回答は以下
↓↓↓
A1. リスト型集計で COUNTIFSをスピらせる
=ARRAYFORMULA(COUNTIFS(D2:D,G3:G6,E2:E,H3:H6))
これは特に問題ないですね。COUNTIFSの 式構成に従って、普通に記述さえすれば動きます。
COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2, ...])
でも、集計するときにこういった形はあまり多くないですよね。クロス表で集計することが多いかと思います。
というわけで、次が本題です。
Q2. クロス表の集計で COUNTIFSをスピらせたい
上のようなクロス表で性別、血液型 を条件とし、H2セルにだけ 数式を入れて該当する人数（行数）をカウントすることは出来るでしょうか？
もちろんCOUNTIFSとArrayformulaを組み合わせよ！ってお題です。Q1 が解ける人は是非チャレンジしてください！
↓↓↓
回答は以下
↓↓↓
A2. クロス表の集計で COUNTIFSをスピらせる
答えの前に、まず失敗例を見てみましょう。
=ARRAYFORMULA(COUNTIFS(D2:D,G3:G4,E2:E,H2:K2))
シンプルに式を書いたら エラーになってしまいます。
COUNTIFS の配列引数のサイズが異なります。
と言われましても・・・。って感じですね。
このエラーのせいで「Googleスプレッドシートでは COUNTIFSは Arrayformula が使えない。（スピらない）」という 声が多いのかと思います。
ちなみに Excel（スピル対応バージョン）だと、これが問題なく動きます。
=COUNTIFS(D2:D50,G3:G4,E2:E50,H2:K2)
これを Googleスプレッドシートでも 求めてるのに、なぜ スピってくれないのか？
スピるのかい？
スピらないのかい？
どっちなんだい？
ヤー！！ってことで、これを解決する回答の式が以下になります。
=ARRAYFORMULA(COUNTIFS(D2:D,IF(H2:K2<>"",G3:G4),
E2:E,IF(G3:G4<>"",H2:K2)))
なにこれ？って感じですよね。
実は Googleスプレッドシートで COUNTIFSを クロス表でスピらせる為には、条件を 結果を出力する分だけ 用意する必要があるんです。
どういうことか？IF部分の式の挙動をみてみましょう。
このように 性別の条件、血液型の条件を 最終的に 結果を返すセル分だけ 用意するのが IFの式の部分です。
IF(H2:K2<>"",G3:G4)
H2:K2は 空白？
TRUE → G3:G4を返す
FALSE → 省略してるので FALSEが返る
このようにすることで、クロス表の中身を条件で埋めることができます。つまり 縦・横の条件を セルに展開させてるわけです。
この記述テクは結構使うケースがあるんで、覚えておくと良いでしょう。
こうして条件を用意してあげないと、Googleスプレッドシートの場合は 配列引数のサイズが違う と判断しちゃうんですね。。
だから COUNTIFSの ARRAYFORMULA対応を △ としています。
Excelの スピルに比べると、だいぶ手がかかる印象・・・。
でも、手がかかるけど COUNTIFSは Googleスプレッドシートでスピらないわけではないんです！（本当はいい子なんです）
ただ、ちょっと面倒な子ってだけなんです！！（暴力系 ツンデレヒロインみたいなもんってことで・・・）
SUMIFS とArrayformula
Arrayformula対応一覧表では ✖となっていた、COUNTIFS以外の〇〇IFS系の 動きをみていきましょう。
SUMIFSはどうやっても Arrayformulaが効かない
=ARRAYFORMULA(SUMIFS(C2:C,D2:D,G3:G4,E2:E,H2:K2))
上のようにクロス表で 性別と血液型が 合致する 人の 年齢の合計を出力したいって要望があったとします。（年齢の合計をなんに使うのかは不明ですがｗ）
しかし、Googleスプレッドシートの場合は SUMIFSをはじめとして、対応表で ✖とつけた AVERAGEIFSや MAXIFS、MINIFSは、どうやっても Arrayformula が効きません。
スピりません！
※厳密に言うと、SUMIFS内の 条件範囲への配列処理に対しては Arrayformulaは効きます。結果が配列で返らないというだけです。
クロス表ではなく、条件を横並びにした リスト形式でも無理です。これはかなり痛い。。
単体条件での集計でよければ、 Arrayformula が使える SUMIF を選ぶという方法がありますが、複数条件だと困ってしまいます。
Excelなら SUMIFSはスピる！
はっ！もしや、SUMIFSも Excelだったら・・・。
君のような勘のいいガキは嫌いだよ
（ラピュタと誤解してる人も多いですが、鋼の錬金術師 でのセリフです）
そうなんです。Excelだと SUMIFSが普通にスピるんです！！
=SUMIFS($C$2:C40,$D$2:D40,G8:G9,$E$2:E40,H7:K7)
=AVERAGEIFS($C$2:C45,$D$2:D45,G13:G14,$E$2:E45,H12:K12)
=MAXIFS($C$2:C50,$D$2:D50,G18:G19,$E$2:E50,H17:K17)
Googleスプレッドシート使いとしては
「なーにー、やっちまったな！！」
と叫びたくなりますよねｗ （Excel関連の Twitterで クールポコネタが プチバズだったんで）
これもあって、SUMIFSは EXCELにおいて 最強関数の一つと言われています。
公式でも 特設ページが用意されてる 好待遇ｗ
残念ながら Googleスプレッドシートでは、長らくこのクロス表で SUMIFS や MAXIFSをスピらせる方法がありませんでした。
しかし、先ほど AVERAGEIF でも登場した 新関数 MAP + LAMBDAの登場でようやく対応できるようになったわけです。
お題いってみましょう！
Q3. MAPを使って SUMIFSをスピらせたい
H3セルにだけ式を入れて、上のクロス集計表を埋めて条件に合致する人の年齢の合計を出力してみましょう。
つまり Arrayformulaでは出来かった SUMIFSをスピらせる処理に挑戦してみよう！ってことです。
もちろん使うのは LAMBDAヘルパー関数の MAP、そして SUMIFSです。
前半で登場した AVERAGEIF + MAPの応用です。MAP関数活用の練習になりますんで、是非挑戦してみてください。
↓↓↓
回答は以下
↓↓↓
A3. MAPを使って SUMIFSをスピらせる
いきなり回答です。
=ARRAYFORMULA(MAP(IF(H2:K2<>"",G3:G4),IF(G3:G4<>"",H2:K2),
LAMBDA(x,y,SUMIFS(C2:C,D2:D,x,E2:E,y))))
MAPの特徴は、
MAP(配列 1, [配列 2, ...], LAMBDA)
と、複数の配列を引数として使えることです。
今回は COUNTIFS をARRAYFORMULAした時と同じテクニックで、
配列1
配列2
このように設定しています。
この条件配列を生成する為に、MAPの外側に Arrayformulaを付けています。
これを LAMBDA( x , y として1つずつ取り出し SUMIFSの条件に使うことで
このように クロス表で SUMIFS の結果をスピらせることが出来ます。
SUMIFS以外の AVERAGEIFS や MAXIFS も ほぼ同じように記述できます。
■SUMIFS
=ARRAYFORMULA(MAP(IF(H7:K7<>"",G8:G9),IF(G8:G9<>"",H7:K7),LAMBDA(x,y,SUMIFS(C2:C,D2:D,x,E2:E,y))))
■AVERAGEIFS
=ARRAYFORMULA(MAP(IF(H12:K12<>"",G13:G14),IF(G13:G14<>"",H12:K12),LAMBDA(x,y,AVERAGEIFS(C2:C,D2:D,x,E2:E,y))))
■MAXIFS
=ARRAYFORMULA(MAP(IF(H17:K17<>"",G18:G19),IF(G18:G19<>"",H17:K17),LAMBDA(x,y,MAXIFS(C2:C,D2:D,x,E2:E,y))))
このようになります。
これで Googleスプレッドシートで 〇〇IFS系関数を クロス集計で使う際のスピル対応もバッチリですね！
Googleスプレッドシートなら クロス集計は QUERY関数があるんだからね！
LAMBDA関数の配列処理は Googleスプレッドシートの方が 配列のネストが出来るんで超優秀なんですが、〇〇IFS 系のスピルは EXCELの方が圧倒的に簡単で強い ことがわかりました。
↓ Googleスプレッドシートの LAMBDA処理が、なぜEXCELより優秀なのかは過去noteを参照
なぜ SUMIFSが Arrayformula でスピらないのかはわかりませんが、現状では仕方ないので 上で記載した MAPで対応する方法で回避しましょう。
しかーし、
そもそも Googleスプレッドシートでは クロス表で SUMIFSや 〇〇IFS系関数を使わないという選択肢があります。
最強の集計関数 QUERY関数 を使うことで、条件部分含め 瞬殺できるからです！
参考：QUERY関数で処理する場合の式
今回は QUERY関数はメインではないので、お題ではなく 参考という形で紹介だけしておきましょう。
■COUNTIFS代替
=QUERY(A:E,"select D,count(D) where D is not null group by D pivot E order by D desc")
■SUMIFS代替
=QUERY(A:E,"select D,sum(C) where D is not null group by D pivot E order by D desc")
■AVERAGEIFS代替
=QUERY(A:E,"select D,avg(C) where D is not null group by D pivot E order by D desc")
■MAXIFS代替
=QUERY(A:E,"select D,max(C) where D is not null group by D pivot E order by D desc")
order by D desc としているのは、これを入れないと自動では 女、男 の順で並んでしまうためです。音読みで 女（じょ ＝ さ行）が、男（だん = た行）より前だからでしょうか。 Ｄ列を降順と指定しています。
残念ながら pivot の方の並び A AB B O を 並び替えようとすると、結構面倒なんですよね。今回はここは触れずにいきましょう。
並びを気にしなければ、条件部分含め 一撃で 処理出来る QUERY関数は やはり最強ですね。
一方で、指定した項目だけ集計したい、並び順にこだわりたいって場合は SUMIFSをスピらせた方が良い場合もあるでしょう。
参考：関数ではなく ピボットテーブル 機能を使ってもよい
もちろん 関数ではなく、ピボットテーブルで処理するという方法もおススメです。関数苦手な方でも、これならポチポチと マウスで選択していくだけでクロス集計表が生成されます。
Googleスプレッドシートの ピボットテーブルは 関数と同じで リアルタイム更新 されるのが魅力です。（Excelは基本的には 更新ボタンを押す必要がある）
Googleスプレッドシートの ピボットテーブルも基本を説明しているサイトは多いけど、深い解説サイトがあまり無いんですよね。
機会があれば触れたいと思います。
SUMIFSをスピらせてもいいけど、QUERYもね
今回は Googleスプレッドシートの 〇〇IFS系関数 の ARRAYFORMULA 対応について、そして Excelでの 〇〇IFS系関数のスピルとの比較、さらに LAMBDAヘルパー関数 MAPを使う方法と QUERY関数を使う方法 について取り上げました。
ツンデレキャラ風にまとめると、
COUNTIFSは Arrayformula と組み合わせて使えないってわけじゃないけどー。（ただし Excelと違って 面倒な挙動を考慮した式にする必要あり）
Googleスプレッドシートでも MAPを使えば SUMIFSだってスピるもん！
Googleスプレッドシートには 必殺の QUERY関数があるんだからね！
という ポイントを理解できましたでしょうか？
タイトル画像も まとめも ふざけてますが、今回紹介した クロス集計の幾つかの手法を上手に使い分けできるようになれば、生産性爆上げになるのは 間違いないです！（筋肉では解決しません！）
次回こそは、プルダウンやろうかなと。
この記事が気に入ったらサポートをしてみませんか？