Googleスプレッドシート ARRAYFORMULA関数 超応用例 2（スピらない関数を考察）
先週の続き ARRAYFORMULA関数の超応用例 第2回です。
Googleスプレッドシートを代表する ARRAYFORMULA関数。今回はARRAYFORMULA関数が効かない（適用してもスピらない）関数を どう突破するか（かつてはどう突破していたか？）をお題形式でみていきましょう。
MAP、BYROW、BYCOLなどの LAMBDAヘルパー関数登場前の古の時代（といっても 2022年以前ですが）、ARRAYFORMULAが効かない関数をいかにスピらせていたのか？
ぶっちゃけ実用性は微妙なんですがｗ興味があれば挑戦してみてください！
前回はARRAYFROMULA関数の1回目で、使い方の基本や使用例などを中心に書きました。
ARRAYFORMULAが効かない関数を理解する
今回は ARRAYFORMULAが効かない関数を取り上げていきます。
でも、その前に少しだけ 前回のおさらい。
おさらい ARRAYFORMULA関数の基本と使う上でのコツ
ARRAYFORMULA関数は Excelのスピル効果や 配列計算の効果を 付与する 結界魔法のような関数です。
スピル対応の Excel（365、Web、2021など）は、旧Excelから 世界の理が変わったというイメージです。
その理の中に スピル効果や配列計算効果があって、特に意識せず今まで単体セルや単体の値を入れていた引数に セル範囲や配列を入れるだけで自動でスピルする世界観。
一方 Googleスプレッドシートの世界では 一部の特権をもった新関数（FILTERやSORTなど）以外は、今まで通りの使い勝手で 旧関数（一般関数）に セル範囲や配列を引数に入れたからといって自動でスピルはしません。
ARRAYFORMULA の詠唱で生成された 結界空間の中でのみ、スピル効果や配列計算効果が 一般の関数にも 付与されるっていう世界です。
※全ての関数に効果があるわけではありません。
※スピルはExcelの用語で、厳密にはGoogleスプレッドシートの配列の出力はスピルとは表現しません。
この 特殊な ARRAYFORMULA関数を使いこなすためのコツが 前回紹介したコチラです。（あくまでも mirが考えるコツです）
■ARRAYFORMULAを使いこなすためのコツ
1. ARRAYFORMULAを使った際は 配列や範囲のサイズを意識する
2. ARRAYFORMULAは 式内で一つ一番外側にそえるだけ
3. ARRYFORMULA内では AND関数やOR関数が使えない
4. ARRAYFORMULAが不要になる関数を理解する
5. ARRAYFORMULAが使えない関数、使いにく関数を理解する
前回は、1～4を具体例を交えて紹介したので、今回は
5. ARRAYFORMULAが使えない関数、使いにく関数を理解する
こちらを中心に見ていきましょう。
ARRAYFORMULAが効かないを分類する
「ARRAYFORMULAが効かない」といっても、実は関数によって様々なケースがあります。
そもそも、「ARRAYFORMULAが効かない」と言われてるけど、実はARRAYFORMULAが効く（ただし扱いづらい）関数もあります。
もともと配列を引数としてとって結果を返す関数は、行毎にとか列毎にといった、こちらの思い通りには効かないってパターンだったりします。
もちろん、その関数自体が縦横にスピる配列を返す関数の場合は、さらにARRAYFORMULAを重ねても効果は得られませんし、それ以外にも理由はわかりませんが、どうやってもARRAYFOMRULAが 効かない関数が幾つか存在します。
実はARRAYFORMULAが効くが扱いづらい関数
COUNTIFS関数、IFS関数 など
こちらの思い通りにはARRAYFORMULAが効かない関数
SUM関数、MAX関数、MIN関数、SUBTOTAL関数
TEXTJOIN関数、AND関数、OR関数 など
縦横に配列を展開させる為ARRAYFORMULAが効かない関数
FILTER関数、SORT関数、INDEX関数、UNIQUE関数
IMPORT〇〇系関数、INDIRECT関数など
単体の結果を返すが、なぜか ARRAYFORMULAが効かない関数
SUMIFS関数、AVERAGEIFS関数、MAXIFS関数
FORMULATEXT関数、CELL関数など
他のサイトだと上が一括りでARRAYFORMULAが効かない関数と扱われていることも多いですが、このように違いがあります。
また、結構多くのサイトで COUNTIFS関数やIFS関数を ARRAYFORMULAが効かない関数と書いてたりしますが、これは誤りです！
これらはクセがあって ケースによっては扱いづらいだけで、ARRAYFORMULAが効く関数です。
効かないというケースは、正しいサイズの配列が与えられてなかったり、ANDやORなどの ARRAYFORMULAが効かない関数を組み合わせている為だと思われます。
この辺りは過去にも少し触れています。
本当は それぞれの分類の関数を取り上げて検証を書きたいところですが、超絶長くなってしまうので、今回は SUM関数、MAX関数、SUMIFS関数 に絞って ARRAYFORMULAとの組み合わせを説明していきましょう。
ARRAYFORMULA関数とSUM関数
こちらの思い通りにはARRAYFORMULAが効かない関数
この代表格が SUM関数です。
SUM関数はARRAYFORMULAと組み合わせても 行毎にや列毎に計算されない（当たり前）
たとえば 上のような点数表があったとして、個人ごと（行毎）の合計を一つの式で出力したいと思い H2セルに
=ARRAYFORMULA(SUM(B2:F5))
と入れても 行毎の合計は出ません。
これは当たり前ですよね。
=SUM(B2:F5) この時点で B2:F5の範囲を合計した結果を出力する っていう式が 成り立ってるわけですから、ARRAYFORMULAを付けたからといって「行毎に」というこちらの意図を気を利かせて汲み取ってくれることはありません。
これに対して「SUM関数にARRAYFORMULAが効いてくれない！」と憤ってる人は、じゃあ教科ごと（列毎）の合計を出すときも
=ARRAYFORMULA(SUM(B2:F5))
この同じ式で結果を出して欲しいってことでしょうか？まったく同じ式で結果が違うなんてことはありませんよね。
H2セルに入れようが、A7セルに入れようが ARRAYFORMULA(SUM(B2:F5)) の結果は SUM(B2:F5) の結果と同じ B2:F5範囲の数値を全て足した 1654 が返ります。
SUBTOTAL関数はスピらなくはない（そっちかーい）
SUMではなく SUBTOTAL関数と ARRAYFORMULAの組み合わせで考えるとわかりやすいかもしれません。
SUBTOTAL(関数コード, 範囲1, [範囲2, ...])
SUBTOTAL関数は 上のような構文の 関数で、関数コードを切り替えることで 合計や平均、個数、最大値、最小値など 色々な計算が出来る変わった関数です。
関数コードを 9 に指定すると SUMと 同じ効果（厳密には違う挙動の点がありますが）で 範囲の合計 を得られますが、ARRAYFORMULAを付けたからといって SUBTOTALが 行毎、列毎といった計算が出来るわけではありません。
だからといって、SUBTOTAL関数は ARRAYFORMULAが効かない関数か？というと、そんなことはありません。
もともと範囲を引数とする箇所には効果はありませんが、
SUBTOTAL(関数コード, 範囲1, [範囲2, ...])
↑ ここ！関数コードには ARRAYFORMULAを効かせられる
本来単体の数値を引数とする 関数コードには ARRAYFORMULAを効かせることが出来ます。
=ARRAYFORMULA(SUBTOTAL({9,1,4,5},B2:F5))
たとえば、このように ARAYFORMULAを付けた SUBTOTALの第1引数、関数コードを
9・・・SUM
1・・・AVERAGE
4・・・MAX
5・・・MIN
と配列{9,1,4,5} で指定することで、1つの式で 範囲全体の合計、平均、最大値（最高点）、最小値（最低点）を一気に出力できますｗ
ちなみに {9,1,4,5} としてるんで 横並びに出力してますが、{9;1;4;5} と縦並びの配列を与えれば 結果も縦に出力されます。
このような使い方をするケースは少ないかもしれませんが、とりあえずSUBTOTALはスピらない関数ではないってことです。
そして、範囲の合計や平均、最大値を返すような関数が、ARRAYFORMULAで行毎や列毎といった思い通りの処理にならないのは、当たり前だよね！ってことです。
行毎、列毎を解決する LAMBDAヘルパー関数
このARRAYFORMULAでは対応できない 行毎に、列毎に といったリクエストをかなえてくれるのが LAMBDAヘルパー関数の
BYROW関数（行ごと）
BYCOL関数（列ごと）
MAP関数 （セルごと）
です。
先行して Excelに導入された超絶便利な関数で、Googleスプレッドシートでは 2022年9月から使えるようになりました。
LAMBDAヘルパー関数を取り上げる回ではないので、こちらはお題にはしませんが、このような 行毎の合計、列毎の合計を一つの式で計算したい場合は
行毎に合計を計算
=BYROW(B2:F5,LAMBDA(r,SUM(r)))
列毎に合計を計算
=BYCOL(B2:F5,LAMBDA(c,SUM(c)))
このように簡単な式で記述が出来ます。
r や c は引数（プレースホルダー）を適当に設定してるだけなんで、名づけはなんでもいいんですが、わかりやすいように 行（row）を表す r、列（column)を表す cとしてます。
列のアルファベットと誤認されない為に頭にアンダーバーをつける
_r や _c とする人も多いです。
ちなみに 上はExcelでも同じように記述できますが、ExcelのInsider（先行お試し版）だと イータ縮小ラムダという記述方法があり、
=BYROW(B2:F5,SUM)
=BYCOL(B2:F5,SUM)
このように記述できるようです。めっちゃ短いですね！
では、LAMBDAヘルパー関数のBYROWやBYCOLの登場以前、2022年9月より前は 今回のような行毎、列毎の合計を一つの式で算出するのは無理だったのか？
そんなことはありません。
先人たちの知恵で、この手の行毎に列毎にといった 計算を突破する方法が生み出されていました！
Q1. 行毎、列毎の 合計を一つの式で計算したい（LAMBDAなし）
それではお題いってみましょう。
上の表の点数部分B2:F5を 行毎、また列毎に計算するにはどのような式を作ればよいか？
ただし、式は行毎は H2、列毎は B7 のそれぞれ1カ所のみに入れるとし、LAMBDAヘルパー関数以外を使うものとする。
こんな感じの問題です。いかがでしょうか？
正直、BYROW、BYCOLが登場した今となっては、これが解けたからといってメリットはあまり無いですｗ 趣味の世界と言えるかも。
まぁARRAYFORMULAの理解は深まると思うのでお付き合いください。
国語 数学 英語 理科 社会
田中 100 90 88 88 70
佐藤 85 95 70 88 89
山田 90 89 95 95 90
鈴木 70 55 62 80 65お題用のデータは こちらをコピペしてご利用ください。
考えてみましょう！ メジャーな解法が2通りあります。（知ってる人は知ってるでしょうが）
↓↓
ここから回答です。
↓↓
A1-1. 行毎、列毎の 合計を一つの式で計算する（SUMIF関数）
回答です。
1つはSUMIF関数を使う方法です。
あとで登場する SUMIFS関数は ARRAYFORMULAがまったく効かない関数ですが、SUMIF関数はARRAYFORMULAと組み合わせて使える貴重な関数です！
行毎に合計を計算
=ARRAYFORMULA(SUMIF(ROW(B2:F5)*COLUMN(B2:F5)^0,ROW(B2:F5),B2:F5))
列毎に合計を計算
=ARRAYFORMULA(SUMIF(ROW(B2:F5)^0*COLUMN(B2:F5),COLUMN(B2:F5),B2:F5))
SUMIF関数は
SUMIF(範囲, 条件, [合計範囲])
このような構文になっています。ARRAYFORMULAで効果を与えられるのは、第2引数の条件の箇所です。
ここに 行番号（列毎の場合は列番号）の縦配列を入れます。
ROW(B2:F5)
↓
2
3
4
5 という縦並びの配列
これによって「同じ行」であるを条件にして 2行目の合計、3行目の合計・・・と 行毎の合計を算出し、第2引数の条件と同じサイズの配列が結果として返ります。
ただし単純に
=ARRAYFORMULA(SUMIF(ROW(B2:F5),ROW(B2:F5),B2:F5))
という式にすると、行毎の合計の結果になりません。
一番左の B2:B5がそのまま出力されるだけです。
なんで？と思うかもしれませんが、これは
このように本来は 合計範囲の B2:F5と同じサイズに 行番号が展開された
こんな配列を条件にする必要があるのですが、ROW関数が=ARRAYFORMULA(ROW(B2:F5)) と 複数列の範囲を指定しても行番号を1列で返してしまう仕様である為です。
これを回避する方法として今回は
ROW(B2:F5)*COLUMN(B2:F5)^0
このような式を条件にしています。
COLUMN(B2:F5)^0 という式で、列番号をすべて 0乗で1とした 1を列数分 横に展開された配列を ROW(B2:F5)と掛け合わせることで、元の範囲と同じサイズの 行番号配列を生成しています。
この元の範囲と同じ行番号配列を生成する式は、IF関数を使う方法もあります。
=ARRAYFORMULA(IF(B2:F5,ROW(B2:F5),ROW(B2:F5)))
今回の場合は FALSEを考慮せず =ARRAYFORMULA(IF(B2:F5,ROW(B2:F5)))としてしまっても良いのですが、元の表に 欠席の為 点数欄が空欄だったり、0点だった場合、その部分が FALSE となってしまう為、TRUEでもFALSEでも行番号を返す式としています。
ARRAYFORMULA関数を SUMIF関数と組み合わせて、条件を 行番号（列番号）として、さらに条件配列部分を一工夫 することで、 一つの式で行毎（列毎）に SUMしたい という要件を満たす 代替式を作成することができました。
なかなか大変ですねｗ
行毎に合計を計算
=ARRAYFORMULA(SUMIF(ROW(B2:F5)*COLUMN(B2:F5)^0,ROW(B2:F5),B2:F5))
列毎に合計を計算
=ARRAYFORMULA(SUMIF(ROW(B2:F5)^0*COLUMN(B2:F5),COLUMN(B2:F5),B2:F5))
ちなみに Excelだと SUMIF関数は 第1引数の（条件）範囲に 配列が使えない（セル範囲のみ）という制約がある為、この方法が使えません。
Googleスプレッドシートの場合は、 SUMIFは 第1引数は セル範囲でなく配列でも大丈夫なんですが、
=ARRAYFORMULA(SUMIF(ROW(B2:F5)*COLUMN(B2:F5)^0,ROW(B2:F5),{B2:F5}))
このように 第3引数（合計範囲）はセル範囲のみという制約があります。
つまり IMPORTRANGE関数で 他のスプレッドシートの範囲（表）を呼び出して、そのまま式内で SUMIFといったことは出来ません。
この点は注意。
この 合計範囲が配列というケースにも対応できるのが、もう一つの解法です。
A1-2. 行毎、列毎の 合計を一つの式で計算する（MMULT関数）
もう一つの回答は MMULT関数を使う方法です。
行毎に合計を計算
=ARRAYFORMULA(MMULT(B2:F5,TRANSPOSE(COLUMN(B2:F5)^0)))
列毎に合計を計算
=ARRAYFORMULA(MMULT(TRANSPOSE(ROW(B2:F5)^0),B2:F5))
MMULT関数は 2つの行列の行列積を計算する関数です。ちょっと数学の理解が必要になるので難しいです。
ただ、行毎の合計、列毎の合計を算出する使い方だけであれば、
このように覚えるとよいです。
MMULTは SUMIF関数のように引数がセル範囲である必要があるといった制限はなく、範囲でも配列でも計算できるというメリットがあります。
ちなみに ARRAYFORMULAを組み合わせていますが、上の式でARRAYFORMULAが必要なのは COLUMN(B2:F5)^0) ここの処理だけです。
MMULT関数は配列を返せる関数ですが、FILTERやSORTのような 式内に配列計算効果を付与する機能はない為、引数に配列処理が必要となる場合は ARRAYFORMULAを組み合わせる必要があります。
でも、 TRANSPOSE(COLUMN(B2:F5)^0) は、B2:F5の列幅と同じ 行数だけ 1がある配列を生成しているだけなんで、
行毎に合計を計算
=MMULT(B2:F5,{1;1;1;1;1})
▼
=MMULT(B2:F5,SEQUENCE(COLUMNS(B2:F5),1,1,0))
このように、SEQUENCE(COLUMNS(B2:F5),1,1,0) を使って 1配列 を生成すれば、ARRAYFORMULAなしでシンプルに 記述することも出来ます。
ただし、MMULTは 範囲に空白セルが含まれると全てがエラーとなってしまうという弱点があります。
例えば 佐藤さんが国語のテストを受講してなくて、範囲の一部に空欄があった場合は
このように結果が #VALUEエラーとなってしまいます。
これを回避するには空白セルやテキストのセルを0にしてくれる N関数を組み合わせるのが便利です。
範囲内に空白があった時でも対応できる回答としては
=ARRAYFORMULA(MMULT(N(B2:F5),TRANSPOSE(COLUMN(B2:F5)^0)))
このようになります。こちらで覚えておいた方がいいかもしれません。
上の式ではARRAYFORMULAは、N関数をB2:F5の範囲に適用させる効果と COLUMN(B2:F5)^0 で列番号全てを 0乗させる効果、この2ヵ所に影響を与えています。
空白を0にするだけなら B2:F5*1 とか --B2:F5 でもいいんですが、その場合 セルに文字列が入っていた場合エラーとなってしまいます。
空白だけじゃなく文字列も0としてくれる N関数が便利ですね。
LAMBDAヘルパー関数登場で MMULTの出番はかなり減ってしまいましたが、かつては「一つの式で」という条件があった場合は高頻度で活躍する強力な関数でした。
この先人たちの知恵を記録して紡いでいくって感じが、「チ。」っぽくていいですね。
別にMMULTを使ったからといって異端審問官 に拷問されたりはしませんのでご安心をｗ
ARRAYFORMULA関数と MAX関数
さらに難しい 行毎のMAX関数にもチャレンジしてみましょう。
BYROW関数なら行毎のMAX（最大値取得）も簡単
「難しかった」と過去形なのは、もちろん今だったら BYROW関数でさくっと解決できちゃうからです。
=BYROW(B2:F5,LAMBDA(r,MAX(r)))
このようにLAMBDAヘルパー関数のよいところは、型にハメてしまえば既存の関数が行毎にとか列毎に処理されるというシンプルさですね！
Q2. 行毎の 最大値を一つの式で計算したい（LAMBDAなし）
国語 数学 英語 理科 社会
田中 100 90 88 88 70
佐藤 95 70 88 89
山田 90 89 95 95 90
鈴木 70 55 62 80 65それではお題です。上のデータを A1に貼って利用ください。
B2:F5の行毎の 最大値をH2に入れた一つの式で取得するには、どのような式を組めば良いでしょうか？ただしLAMBDAヘルパー関数は使用しないものとする。
結構難しいです。こちらを考えてみましょう！
↓↓
ここから回答です。
↓↓
A2. 行毎の 最大値を一つの式で計算する
回答です。
=ARRAYFORMULA(MMULT(B2:F5*NOT(COUNTIFS(B2:F5,">"&B2:F5,COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5)))/COUNTIFS(N(B2:F5),N(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5)),SEQUENCE(COLUMNS(B2:F5))^0))
ちょっと見づらいんでインデントつけましょう。
=ARRAYFORMULA(
MMULT(
B2:F5*NOT(COUNTIFS(B2:F5,">"&B2:F5,COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5)))
/COUNTIFS(N(B2:F5),N(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5)),
SEQUENCE(COLUMNS(B2:F5))^0
)
)解説します。
まず、同じ行の中で自分より大きい数値が幾つあるかを返す式をARRAYFORMULAとCOUNTIFSで組み立てています。
それが
COUNTIFS(B2:F5,">"&B2:F5,COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5))
この部分です。
COLUMN(B2:F5)^0*ROW(B2:F5) これは 行毎のSUMの時にも登場した 行番号を B2:F5の範囲に展開する式ですね。
この式の結果は B2:F5と同じサイズの配列となります。ここで 0を返している箇所が 行毎の最大値の位置となります。
注意点としてB3セルの佐藤の国語は 空白セルであるため、カウント対象外となり 0が返ってしまっていますが、最大値を求める場合は ここは気にしなくてよいです。
0は残したい、0以外は不要ってことで、これにNOT関数で反転させます。
すると、最大値の位置がTRUE、それ以外はFALSEとなりました。
これにそのまま同じサイズの範囲 B2:F5 の数値（点数）を掛けると
TRUEは1、FALSEは0という扱いになるので、最大値の箇所だけが数値が入った状態になります。
TRUEとなっていた空白セルはここで空白を掛けたことで 0になりました。
このまま行毎に合計を出せば最大値が出るんですが、3行目に 最大値であるセルが2つあります。このようなケースに対応する為に
COUNTIFS(N(B2:F5),N(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5),COLUMN(B2:F5)^0*ROW(B2:F5))
この式で同じ行に自分を含めて自分と同じ値が幾つあるかをカウントした結果を用意します。
ここで N関数を使っているのは、空白の場合 カウント 0となってしまうので、後で割り算をする際に 0で割る（分母 0）で発生する #DIV/0!エラーを回避する為です。
先ほどの最大値だけ残してあとは0にした配列
割る ( / )
同じ行に自分と同じ値が幾つあるかカウントした配列
とすることで、
最大値が2つ登場した行が 半分ずつになりました。
あとは行毎の合計を算出すればよいので、先ほどの配列にも使える MMULTの出番ですね！
1を配列の列数分縦に並べた、第2引数用の掛け合わせる配列は
SEQUENCE(COLUMNS(B2:F5))^0
で生成しておきましょう。
これで行毎の最大値を求めることが出来ました。
最小値の場合は空白が0とみなされのを避ける為に、最初にIF関数で空白を十分に大きい値（今回の場合は100点満点のテストなので1000とか）にして置く等で対応すれば、同じ手順で算出ことが可能です。
BYROWが無かった時代の面倒な手法の紹介でしたｗ
ちなみに BYROWが無い時代なんで当然 LET関数も無い時代だから、式が長くなってますが、この式は 重複箇所が非常に多いので LET関数を使うと一気に短くなります。
=ARRAYFORMULA(LET(a,N(B2:F5),c,COLUMN(B2:F5)^0,r,c*ROW(B2:F5),MMULT(a*NOT(COUNTIFS(a,">"&a,r,r))/COUNTIFS(a,a,r,r),TRANSPOSE(c))))
LAMBDAヘルパー関数にLET関数、これらの追加で本当に便利になったなぁと実感しますｗ
ARRAYFORMULA関数と SUMIFS関数
ARRAYFORMULAがなぜか効かないのが SUMIFS関数 他、〇〇IFS関数です。
特に実務でよく使う SUMIFS関数は Excelではいい感じにスピるのに Googleスプレッドシートだとスピらない。。
そりゃQUERY関数があるんで 別にいいや！って言ってもいいんですが、やっぱり使いたい時もあるんですよね。
LAMBDAヘルパー関数を使った解法は後で少し触れますが、今回はあくまでも LAMBDA無しで代替してきた時代のお話。
LAMBDAなしでSUMIFSをスピルした処理を代用する式に挑戦してみましょう！
Q3. クロス表で SUMIFSをスピらせたい（LAMBDAなし代替式）
お題用のデータです。こちらをコピーしてA1セルに貼り付け。
氏名 年齢 性別 血液型
涌井 真介 44 男 A
福澤 将史 20 男 O
佐藤 奈美 41 女 O
福澤 修 67 男 A
大久保 敏之 76 男 A
井村 聡 54 男 O
杉田 美子 14 女 O
小倉 雄三 23 男 B
金 達也 79 男 O
藤井 裕美子 18 女 O
大野 俊明 46 男 A
原 正弘 17 男 O
小島 久美子 25 女 A
福元 博章 55 男 A
川口 恵 75 男 A
二見 宏 53 男 O
佐藤 勝代 73 女 O
濱本 隆文 73 男 O
川原 綾華 44 女 B
宍戸 涼平 28 男 A
大山 達 22 男 AB
須貝 泰之 78 男 O
淺野 泰一 19 男 AB
田島 英理 27 女 O
吉田 一幸 25 男 O
加来 啓介 41 男 B
小野寺 健児 38 男 A
米原 美保 77 女 O
高橋 恵美子 57 女 B
芦田 和代 37 女 A
南谷 良子 73 女 O
和田 克彦 47 男 O
新田 美冴 62 女 O
橘 智恵 21 女 O
黒川 雅彦 58 男 A
杉原 聖 43 男 A
濱 敦史 52 男 A
糸井 友里 47 女 AB
中田 俊紀 48 男 B
本庄 宏光 50 その他・不明 B
冨高 芳夫 39 男 A
三田 みずほ 31 女 B
神田 賢 24 男 B
浅間 香織 60 女 AB
古川 由美子 70 女 B
廣澤 渚 28 女 A
佐藤 民子 59 女 A
横澤 信幸 68 男 B
志水 宏美 67 女 AB
古川 萌 59 女 B
柏崎 純 20 男 B
山田 徳彦 30 男 O
大江 健史 57 男 O
日比 真也 65 男 A
青木 孝信 54 男 A
岡 健太郎 77 男 A
浦郷 知香子 53 女 A
成田 一成 26 男 O
大沼 昌治 69 男 B
畠山 めぐみ 38 女 A
山崎 真実 30 女 AB
関口 久美子 74 女 O
木下 麻衣 76 女 O
島中 卓 38 男 B
濱田 恵理子 76 女 B
小田 千恵 49 女 B
石毛 聡介 47 男 A
林 麻由美 55 女 AB
清水 佑介 74 男 B
佐野 祐子 40 女 B
成瀬 雄介 52 男 A
高橋 章 62 その他・不明 B
中島 秀男 78 男 O
奥田 崇 50 男 A
山田 克之 43 男 B
滝沢 紗矢 26 女 O
仲原 浩幸 56 男 A
笛田 拓也 60 男 B
北山 利行 76 男 A
稲葉 弥生 42 女 B
飯嶋 一恵 79 女 O
上村 良仁 74 男 O
山下 理人 21 男 O
平田 みさと 20 女 O
松本 雅彦 32 男 A
三谷 貴子 67 女 B
大久保 圭祐 68 男 AB
浦野 哲也 29 男 AB
二宮 英樹 77 男 O
立石 直美 50 女 A
田宮 敏彰 13 男 O
三井 匡 56 男 A
加藤 みさ 60 女 A
後藤 あゆみ 61 女 O
矢島 久美子 68 女 B
穂坂 賢一 37 男 O
高橋 奈穂美 70 女 A
渡辺 俊雄 80 男 A
中村 利行 34 男 O
市村 和朗 42 男 Bこちらをコピーして F1セルに貼り付け。数字の部分はDelete
SUMIFS A B O AB
男 1266 550 893 138
女 420 603 742 259これで、G2セルに一つの式を入れて 縦・横の条件にマッチした人の年齢の合計を出してみましょう。（年齢の合計を出すのに意味があるか？という、イマイチよいお題ではないですが）
SUMIFS関数はスピらないので、当然違う関数で代用する必要があります。またLAMBDAヘルパー関数は使用しないという条件です。
やってみましょう！
↓↓
ここから回答です。
↓↓
A3. クロス表で SUMIFSをスピらせる式（LAMBDAなし代替式）
手順を解説しながら進めましょう。
=ARRAYFORMULA(SUMIF(C2:C&"_"&D2:D,F2:F3&"_"&G1:J1,B2:B))
今回のような一致を条件とする場合は SUMIFに落とし込むという方法があります。
Googleスプレッドシートの SUMIF関数は
ARRAYFORMULAの効果がある
第1引数に配列をとれる
これらの特徴を活かして、文字列を連結させて 複数条件を一つの条件にしてしまいます。
まず、第2引数の条件部分 F2:F3&"_"&G1:J1 で、以下のような条件を連結させた文字列を回答セル形に展開させた配列を用意します。
さらに第1引数でも同じく C2:C&"_"&D2:D として、 対象の表のC列（性別）とD列（血液型）を連結させた文字列の1列の配列を用意。
これを使って SUMIF関数を組み立てれば完成です。
これは割と簡単ですね。
しかし、この方法が使えるのは「一致」を条件にした SUMIFSの時だけです。
Q4. クロス表（不等号条件）で SUMIFSをスピらせたい（LAMBDAなし代替式）
例えば このような年齢が
20以上30未満
30以上40未満
40以上50未満
50以上
こんな条件の男・女 それぞれの年齢の合計を算出したい。といった場合は、どうすればよいでしょうか？
ちなみに一つの式でという条件がなければ G3セルに
=SUMIFS($B$2:$B$200,$C$2:$C$200,$F3,$B$2:$B$200,">="&G$2,$B$2:$B$200,"<"&MIN(H$2,500))
と入れて、G3:J5にコピペですね。
これをG3にだけ式を入れてスピらせたい、かつ LAMBDAを使わないという条件だった場合はどうすれば良いでしょうか？
対象の表は Q3と一緒です。
かなり難しくなってきました。自信のある人はチャレンジしてみましょう！
↓↓
ここから回答です。
↓↓
A4. クロス表（不等号条件）で SUMIFSをスピらせる（LAMBDAなし代替式）
複雑なので、今回も順を追って解説していきましょう。
行毎のMAXと同じように MMULTに落とし込むイメージです。まず、条件が縦横のマトリクスになっていると結果配列が展開できないので、
このように条件の全パターンを横に並べた 配列を用意します。2行4列なんで 8パターンですね。
男女の条件を横に並べる式
=ARRAYFORMULA(TRANSPOSE(FLATTEN(IF(G2:J2<>"",F3:F4))))
年齢の下限（〇〇以上）の条件を横に並べる式
=ARRAYFORMULA(TRANSPOSE(FLATTEN(IF(F3:F4<>"",G2:J2))))
年齢の上限（〇〇以下）の条件を横に並べる式
=ARRAYFORMULA(TRANSPOSE(FLATTEN(IF(F3:F4<>"",IF(H2:K2="",500,H2:K2)))))
新関数登場前の方法なので、TRANSPOSE関数 + FLATTEN関数 で横並びにしていますが、現在ならTOROW関数で一発ですね。
年齢の上限ですが、K列が空いてることを利用して先ほどは
MIN(H$2,500)
としましたが、MINを使うとARRAYFORMULAでスピらなくなってしまうので、ここはIF関数で
IF(H2:K2="",500,H2:K2)
としています。500は年齢の上限としてはありえない適当に大きい数値ならOK。
この横並びにした条件ごとに B2:Bの年齢と C2:Cの性別を 行毎に見て条件に一致するものを 1、そうでないものを 0と 演算子をARRAYFORMLAでスピらせて出力します。
=ARRAYFORMULA((C2:C=TRANSPOSE(FLATTEN(IF(G2:J2<>"",F3:F4))))
*(B2:B>=TRANSPOSE(FLATTEN(IF(F3:F4<>"",G2:J2))))
*(B2:B<TRANSPOSE(FLATTEN(IF(F3:F4<>"",IF(H2:K2="",500,H2:K2))))))これでMMULTで処理できますね！
年齢の列の空白セルを0埋めして 横にした 配列を TRANSPOSE(N(B2:B)) で用意して、これをMMULTの第1引数とすることで
=ARRAYFORMULA(MMULT(TRANSPOSE(N(B2:B)),
(C2:C=TRANSPOSE(FLATTEN(IF(G2:J2<>"",F3:F4))))
*(B2:B>=TRANSPOSE(FLATTEN(IF(F3:F4<>"",G2:J2))))
*(B2:B<TRANSPOSE(FLATTEN(IF(F3:F4<>"",IF(H2:K2="",500,H2:K2)))))))このように条件を満たす年齢の合計を横1行で出力できました。
最後にWRAPROWSで折り返したいところですが、過去には無かった関数なので、ここは LOOKUP関数を使いましょう。
このように
結果と同じサイズの連番配列
=SEQUENCE(ROWS(F3:F4),COLUMNS(G2:J2))
横並びにした条件に連番を振った配列
=SEQUENCE(1,ROWS(F3:F4)*COLUMNS(G2:J2))
▼
これを先ほど作成した 条件を満たす年齢の合計の配列の上に { ; } で縦結合
を用意して、最後にLOOKUP関数で
=ARRAYFORMULA(LOOKUP(SEQUENCE(ROWS(F3:F4),COLUMNS(G2:J2)),
SEQUENCE(1,ROWS(F3:F4)*COLUMNS(G2:J2)),
MMULT(TRANSPOSE(N(B2:B)),
(C2:C=TRANSPOSE(FLATTEN(IF(G2:J2<>"",F3:F4))))
*(B2:B>=TRANSPOSE(FLATTEN(IF(F3:F4<>"",G2:J2))))
*(B2:B<TRANSPOSE(FLATTEN(IF(F3:F4<>"",IF(H2:K2="",500,H2:K2))))))))このように引き当て処理をすれば完成です。
こちらで用意した連番なので、昇順の並びとか 近似値一致を気にせず LOOKUP関数が使えます。
というわけで不等号条件のSUMIFS関数をスピらせる LAMBDA無しの代替式、回答は ↓ こちら。
=ARRAYFORMULA(LOOKUP(SEQUENCE(ROWS(F3:F4),COLUMNS(G2:J2)),
SEQUENCE(1,ROWS(F3:F4)*COLUMNS(G2:J2)),
MMULT(TRANSPOSE(N(B2:B)),
(C2:C=TRANSPOSE(FLATTEN(IF(G2:J2<>"",F3:F4))))
*(B2:B>=TRANSPOSE(FLATTEN(IF(F3:F4<>"",G2:J2))))
*(B2:B<TRANSPOSE(FLATTEN(IF(F3:F4<>"",IF(H2:K2="",500,H2:K2))))))))スピらない関数を他の関数でスピらせる場合は、
IF関数 や 演算子
MMULT関数
LOOKUP関数やVLOOKUP関数、HLOOKUP関数など
この辺りのARRAYFORMULAが効く関数、行毎、列毎の合計が出せる関数 で代替させるという古のテクニックでした～。
ARRAYFORMULA関数とLAMBDAヘルパー関数
今回のような クロス集計表でSUMIFSをスピらせる技ですが、もちろんLAMBDAヘルパー関数を使えば対処できます。
このケースは行毎、列毎の処理ではなく セル毎に処理が必要なので、LAMBDAヘルパー関数のMAP関数を使います。
ただし、欲しい結果と同じサイズの条件配列を生成する必要があるので、そこが一工夫必要。
お題にはしませんが
=ARRAYFORMULA(MAP(
IF(G1:J1<>"",F2:F3),
IF(F2:F3<>"",G1:J1),
LAMBDA(v_1,v_2,SUMIFS(B2:B,C2:C,v_1,D2:D,v_2))))
このように
IF(G1:J1<>"",F2:F3)
▶ 男・女の条件を回答のセル範囲に展開した配列を生成する式
IF(F2:F3<>"",G1:J1)
▶ 血液型の条件を回答のセル範囲に展開した配列を生成する式
結果として欲しい配列のサイズと同じサイズの 条件配列を2つ生成して MAP関数に渡しています。
で、これらを配列として処理する為に MAPの外側に ARRAYFORMULAが必要になります。
つまり、
MAPやBYROWなどLAMBDAヘルパー関数があれば ARRAYFORMULAは不要になるか？
というと、そんなことは無くて、それぞれに適したケースがあり場合によっては併用する必要もあるってことです。
こちらの式のより詳しい解説は 過去noteを参照ください。
今回は登場しませんでしたが、QUERY関数の第1引数（範囲）を加工する為に、QUERY関数とARRAYFORMULAを組み合わせるケースもあります。
ARRAYFORMULA関数 と LAMBDAヘルパー関数、どちらも上手に使っていきたいですね！
ARRAYFORMULA関数のまとめ
シリーズ2回にわたって ARRAYFORMULA関数を取り上げました。
最後にまとめです。
■ARRAYFORMULAを使いこなすためのコツ
1. ARRAYFORMULAを使った際は 配列や範囲のサイズを意識する
2. ARRAYFORMULAは 式内で一つ一番外側にそえるだけ
3. ARRYFORMULA内では AND関数やOR関数が使えない
4. ARRAYFORMULAが不要になる関数を理解する
5. ARRAYFORMULAが使えない関数、使いにく関数を理解する
上に加えて
■ARRAYFORMULA関数が（思うように）効かない関数に対応するには
1. LAMBDAヘルパー関数を活用する
行毎 ・・・BYROW
列毎 ・・・BYCOL
セル毎・・・MAP （マトリクス表のケースなど）
2. ARRAYFORMULA関数が使える関数で代替する方法もある
行毎、列毎の SUM → SUMIF、MMULT
行毎、列毎の MAX → COUNTIFS + MMULT
SUMIFS → 文字列連結 + SUMIF
→ IF + MMULT + LOOKUP系
3. LAMBDAヘルパー関数とARRAYFORMULA の
両方を組み合わせて対応するケースもある
これを理解できれば、 ARRAYFORMULAマスターと言えるかも？
最初に言った通り、代替関数に関してはLAMBDAヘルパー関数が登場した今となっては 無理に覚える必要はありません。
でもLAMBDA登場前でも、こんな式で対処できていたってのを知ると面白いですよね！
特に MMULT関数は 結構沼なんでハマりすぎないように気を付けましょうｗ
次回は軽めのネタで、Googleスプレッドシートというか Googleドライブの プレビュー機能について書きたいと思います。
この記事が気に入ったらサポートをしてみませんか？