Googleスプレッドシート ARRAYFORMULA関数 超応用例
今さらながら Googleスプレッドシートを代表する関数の一つ、ARRAYFORMULA関数にフォーカスをあてた noteを書いてみたいと思います。
さすがに人気関数なんで解説しているサイトも多くて目新しいネタも少ないんですが、検索上位のサイトでも 情報が断片的だったり薄かったり、さらには誤った情報が掲載されていたりするんで、mirなりにまとめておこうかなと。
タイトル画像の型が使いまわせるのと、マガジン分けの都合上、「関数 超応用例」シリーズに入れてますが、正直 ARRAYFORMULA関数が メインとなる超応用例ってのはあんまないですｗ
ARRAYFORMULA関数というよりは、組み合わせて使う他の関数の超応用例ですし、正直 MAPやBYROWなどLAMBDAヘルパー関数が使える現在では、不要な知識かもしれませんｗ
それでも多少は新しい発見があるかもしれないので、是非最後までお読みください。（超応用例は今回はあまり登場せず、ほぼ次回となります）
前回は「画像にまつわるエトセトラ」シリーズで、Googleスプレッドシートの図形描画を取り上げました。
ARRAYFORMULA関数を使いこなすコツ
ちなみに noteで ARRAYFORMULAをハッシュタグ検索していただくと、かなりの量の mirの記事がヒットしますｗ
ほぼシート関数が登場する回には ARRAYFORMULAが出てくるってことですね。
それくらい使う頻度の高い関数といえます。
とりあえず最初に、 mirが考える ARRAYFORMULA関数を使いこなすためのコツをお伝えします。
■ARRAYFORMULAを使いこなすためのコツ
1. ARRAYFORMULAを使った際は 配列や範囲のサイズを意識する
2. ARRAYFORMULAは 式内で一つ一番外側にそえるだけ
3. ARRYFORMULA内では AND関数やOR関数が使えない
4. ARRAYFORMULAが不要になる関数を理解する
5. ARRAYFORMULAが使えない関数、使いにく関数を理解する
これらのポイントの中身については、お題や解説の中で触れていきます。
ARRAYFORMULA関数の基本を理解する
まずは既に知ってるよ！という方も多いでしょうが、ARRAYFORMULA関数の基本を理解していきましょう。
原初のスピル ARRAYFORMULA
あえて Googleスプレッドシートではなく Excelの話から入りましょう。
いまでこそ スピル対応の Excelが一般的になりましたが、かつて Excelは
セルに入れた数式は、そのセルにのみ結果を出力する
これが当たり前でした。
「スピル」（他のセルにも結果がこぼれるの意）という動作（機能）が実装されたことで、FILTERやSORTなどの動的配列数式が登場し、スピル非対応のこれまでのExcelでは難しかった処理が簡単に出来るようになりました。
たとえば、B2:C4 の各セルの数値を2倍にしたものをE2:F4に出力したい。といった場合も、1セル1数式だった これまでのExcelでは、
E2に =B2*2 と入れて 右と下にフィルコピーとしていましたが
スピル対応の Excelであれば、
起点となる 左上端の E2セルに =B2:C4*2 と入れるだけで、B2:C4の各セルを2倍にした結果が B2:C4と同じサイズ（3行2列）でE2:F4 に出力されます。
これが Excelのスピルです。
この 1つの式で 複数セルに結果を返せるスピル の実装に合わせて、数式の内部での配列計算が出来たり、引数に入れられるのが単体の値（もしくは単体のセル）のみだった関数が セル範囲や配列を使用できるようになりました。※すべての関数がそうなったわけではありません
たとえば、こんな感じで 昔ながらの SUM関数内で =SUM(B2:C4*2)
といった形で 内部で 各セルを2倍してから合計 といった処理や
TEXT関数の第1引数を単体セルではなくセル範囲にした =TEXT(B2:C4,"0個") といった記述、こんなことが可能になったわけです。
これら Excelのスピルや配列処理の実装に大きな影響を与えたと思われるのが、Googleスプレッドシートの台頭と ARRAYFORMULA関数です。（あくまでも mirの所感です）
ARRAYFORMULA関数は「原初の悪魔」・・・ではなく、原初のスピルと言っていいかもしれません。（厳密には Excelのスピル と Googleスプレッドシートの 結果配列のセル出力は 違うものです）
ARRAYFORMULAは Googleスプレッドシートで
・結果配列を複数セルへ展開する（スピル）
・数式内で 配列計算をしたり、引数に配列やセル範囲がとれるようにする
これらを行うために使う関数です。ARRAYFORMULAを使うことで
・一つの数式で処理できるので 動作が軽くなり メンテナンスも楽
・式内の配列計算により、作業列が不要になる
・最終行(列) を意識しなくてよい A2:A指定で 範囲拡張に対応出来る
このようなメリットがあります。
Excelは（たぶん ARRAYFOMRULAやGoogleスプレッドシートの配列の扱いを参考にして）、これらを関数ではなく意識せずに使える機能として実装したってことですね。
ARRAYFORMULAの基本的な使い方（演算子 同じサイズ）
ARRAYFORMULAの出番で多いのが
たとえば、こんな感じの 単価 × 数量 を行毎に計算するケース。
通常はD2 セルに
=B2*C2
として 下にフィルコピー、もしくは 上の画像のように「自動入力の候補」が表示されるので、ここで ✓を選択すると、D2以下のセルに 相対参照された同じ式が入ります。
対象の範囲が決まっていたり、数十行程度であればこの方法で問題ないです。
しかし、データが数千行、数万行だったり、範囲が決まってないからあらかじめシートの末端（最終行）まで式を入れた状態にしておきたい。といった場合は、この方法で1つずつセルに式を入れてしまうと非常に重くなります。
Googleスプレッドシートは Excel以上に、数式が多いと重くなっていきます。
もちろん 最新のテーブル機能を使って データが追加される度に 数式を自動拡張させる方法もありますが、
=ARRAYFORMULA(B2:B*C2:C)
このようにすると 1つの式なので処理は軽くなり、自動で最終行まで行毎に B列とC列をかけた結果を返します。
このように ARRAYFORMULA関数は 同じサイズの配列（範囲）を与えて計算、同じサイズの結果を返すことが出来ます。
ARRAYFORMULAの基本（演算子 配列と単体）
このD列の結果を使って、たとえば J1セルに入った 10%という数値を使って 消費税額を出したい場合は
=ARRAYFORMULA(D2:D*J1)
このように ARRAYFORMULAでは配列（範囲）と単体の値（セル）を演算子で掛け合わせて、配列と同じサイズの結果を返すことが出来ます。
通常 フィルコピーして利用する式を作る場合は
=D2*$J$1
このように動かしたくない方のセルを絶対参照にしますが、ARRAYFORMULAの式では絶対参照にする必要はありません。（そもそも意味がありません）
ARRAYFORMULAの基本（演算子 縦1列と横1行）
10%だけでなく8%のパターンも計算したい、こんな時に上のように J2セルに8%を入れて
=ARRAYFORMULA(D2:D*J1:J2)
ARRAYFORMULAでこのような式を作ると、「MULTIPLY の配列引数のサイズが異なります」というエラーメッセージが出て #N/Aエラーとなります。
これは、
D2:D ・・・ 999行1列のデータ
J1:J2 ・・・ 2行1列のデータ
と、計算させる配列のサイズが違うのでエラーが発生します。
冒頭の コツの1番目。
■ARRAYFORMULAを使いこなすためのコツ
1. ARRAYFORMULAを使った際は 配列や範囲のサイズを意識する
これですね。
しかし、J2に8%を入れるのではなく 10%が入っているJ1の右、K1セルに8%を入れて 横並びにして
=ARRAYFORMULA(D2:D*J1:K1)
このように（感覚的に縦・横に）計算する 式を作った場合は、正しく計算されます。
つまり、ARRAYFORMULAは 縦1列の配列と横1行の配列同士であれば、それらを掛け合わせた縦横サイズ（縦1列の行数と横1行の列数）の結果を返すということです。
これを活用した代表的な例が 掛け算九九表ですね。
=ARRAYFORMULA(A2:A10*B1:J1)
まとめると ARRAYFORMULAを使った計算は、配列（範囲）のサイズに着目すると
・縦横同じサイズの配列（セル範囲）どうし
・配列（セル範囲）と単体の値（セル）
・縦一列 の配列と横一行の配列
これらのパターンだと機能するということです。
ARRAYFORMULA化するショートカット SCE
「ARRAYFORMULAって関数名長くて入力するの厳しいよー。」という方は、公式ページにも載ってますが
数式の編集中に Ctrl+Shift+Enter を押すと、数式の先頭に ARRAYFORMULA( を追加できます。
このように、懐かしのExcelのレガシ配列数式の入力方法 （Ctrl+Shift+Enter、略して CSE）を使うことで、ショートカットでARRAYFORMULAを付与する方法があります。
mirはあまり使いませんが、関数名のスペルの打ち間違い防止にもなるんで好んで使ってる人も多いです。
関数が展開される範囲のセルには 何も入力しない
ARRAYFORMULAで結果を展開するセル範囲に 直接入力されたセルがあると、結果は一つも展開されず #REF!エラーとなります。
たとえば 上のように 9行目の高級マンゴーに対して D9セルに「時価」と打ち込んでしまうと、他のセルの価格が消えてしまいます。
ARRAYFORMULAなど 配列結果が展開されるセル範囲には何も入力しないようにしましょう。
しかし裏技があって、Googleスプレッドシートは 配列どうしの上書きという方法が可能だったりします。
このように D2に入れた =ARRAYFORMULA(B2:B*C2:C) で 計算結果がD2:Dに展開されている上から A9セルに入れた
={"高級マンゴー","","","時価"}
という式で D9セルを上書きしています。
Excelだと スピル範囲のセルにスピルを重ねると片方はエラーになるんですが、Googleスプレッドシートだとこれが出来ます。
さらに D9セルを Delteすると 上書きされた 値をひっぺがせるという謎の挙動。
なかなか面白いですし、便利に使えそうな雰囲気ではあるんですが、残念ながらこの挙動は非常に不安定なので 遊びでは使えても 実務にはおススメできませんｗ
この特殊な挙動は SEQUENCEの回でも触れています。
基本はこんなもんです。あとは お題を解きながら実践で学んでいきましょう！
ARRAYFORMULAとIF関数
ARRAYFORMULAと組み合わせてよく使う関数の一つが IF関数です。
たとえば先ほど登場した
こちら。
これだと、なにもデータが無い行に 0が表示されてカッコ悪いですよね。これをIF関数を組み合わせて 0を無くして空白を返すようにするにはどうすればよいでしょうか？
お題いってみましょう。
Q1. A列が空だったら D列も空としたい
まずは簡単なお題から。
A列が空だったら 空白を返すように =ARRAYFORMULA(B2:B*C2:C)
の式をアレンジしたい場合、どうすればよいでしょうか？
さくっと考えてみましょう！
↓↓
ここから回答です。
↓↓
A1. A列が空だったら D列も空とする ARRAYFORMULAの式
回答です。
=ARRAYFORMULA(IF(A2:A="",,B2:B*C2:C))
これは大丈夫ですよね。
IF関数で 条件部分を A2:A="" として、空白だったら 空白を返す、空白でなければ B2:B*C2:C の計算結果を返すとしています。
前々回の noteで登場しましたが、
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))
と空文字 "" を返すのではなく、Googleスプレッドシートなら空白を返しておきましょうね！
ポイントですが、ARRAYFORMULAの位置です。
=IF(A2:A="",,ARRAYFORMULA(B2:B*C2:C))
このように元の ARRAYFORMULA(B2:B*C2:C) という式を 単純に IF関数に包んでも動きません。
これだと IF関数の第1引数である 条件式の部分 A2:A="" が配列として計算されないから当然です。
だからといって、
=ARRAYFORMULA(IF(A2:A="",,ARRAYFORMULA(B2:B*C2:C)))
このように 元の ARRAYFORMULA(B2:B*C2:C) をそのままにして、さらにIF関数用に ARRAYFORMULA をもう一つ使うのは、意味がないのでやめましょう。
ARRAYFOMRULAは 結界というか、転スラだと ホーリーフィールドのようなもので、その範囲内にある全てに効果があります。
=ARRAYFORMULA( この中の全ての関数、演算に配列効果を与える ）
つまり 冒頭のコツの
■ARRAYFORMULAを使いこなすためのコツ
2. ARRAYFORMULAは 式内で一つ一番外側にそえるだけ
これです。
式の途中で使うのではなく、どんなに長い式でも、LETやLAMBDAが登場する複雑な式でも、とにかく一番外側に ARRAYFORMULAは一つだけ添えましょう！
Q2. B列とC列 AND条件やOR条件で Ｄ列も空にしたい
続けて同じ表で お題2パターンいってみましょう。
=ARRAYFORMULA(B2:B*C2:C) の式をアレンジして
2-1 B列とC列の どちらも（両方）空だったら 空を返す式
2-2 B列とC列の どちらか（片方）空だったら 空を返す式
空を返す条件を変えたこの2パターンを作成してみてください。やってみよう！
↓↓
ここから回答です。
↓↓
A2. B列とC列 AND条件やOR条件で Ｄ列も空にする ARRAYFORMULAの式
通常であれば、
どちらも ・・・ AND関数
どちらか ・・・ OR関数
このような場面ですが、ARRAYFORMULAを使った配列式の場合 AND関数やOR関数を使うと・・・
AND関数を使った場合は 全て FALSEとして処理された計算結果が返り、OR関数を使った場合は、全て TRUEとして処理され 空白が返ります。
これが 冒頭のコツの
■ARRAYFORMULAを使いこなすためのコツ
3. ARRYFORMULA内では AND関数やOR関数が使えない
これです。ARRAYFORMULAに限らず FILTER関数などでも同様です。
なぜか？ AND関数、OR関数の部分だけARRAYFORMULAと組み合わせた式を見てみましょう。
このように
=ARRAYFORMULA(AND(B2:B="",C2:C="")) は FALSE
=ARRAYFORMULA(OR(B2:B="",C2:C="")) は TRUE
と単体の結果を返しています。これはANDやORが配列（範囲）全体を評価して単体の結果を返す関数だからです。
つまりARRAYFORMULA(AND(B2:B="",C2:C="")) は、B列、C列が全て埋まっている（一つも空白がない）状態だと TRUEになりますが、一つでも空白があると FALSEを返す。
また ARRAYFORMULA(OR(B2:B="",C2:C=""))は、B列、C列のうち1つでも空白があれば TRUEを返し、一つも空白がない（全て埋まっている）と FALSEを返す。
こんな動作をしており、ARRAYFORMULAを付けてたからといって こちらの意図する「行毎に」という動作にはならない為です。
ではどうするか？代わりに演算子を使います！
このように数値化した時に TRUEは 1、FALSEは 0という性質を利用して
AND関数 ▶ (条件1)*(条件2) ・・・
乗算（掛け算） 1つでも 0（FALSE）があれば0（FALSE）となる
全て1（TRUE）なら 1（TRUE）を返す
OR関数 ▶ (条件1)+(条件2) ・・・
加算（足し算） 1つでも 1（TRUE）があれば 1以上（TRUE）となる
全て0（FALSE）の時だけ 0（FALSE）となる
このように演算子を使います。
演算子であれば、基本で学んだ通り 同じサイズどうしの配列の演算は、それと同じサイズの結果配列を返すってことですね！
というわけで回答は
B列とC列 どちらも空だったら D列も空としたい
=ARRAYFORMULA(IF((B2:B="")*(C2:C=""),,B2:B*C2:C))
B列とC列 どちらかが空だったら D列も空としたい
=ARRAYFORMULA(IF((B2:B="")+(C2:C=""),,B2:B*C2:C))
このようになります。条件部分がイコールの入った数式の場合は、カッコで括るのを忘れずに！
ARRAYFORMULAとCOUNTIF関数、COUNTIFS関数
ARRAYFORMULAと組み合わせてよく使う関数、続いて COUNTIF を見ていきましょう。
Q3.A列のデータでC2:C4の商品名を含むものがそれぞれ幾つあるかカウントしたい
A列にこのようなフルーツを含む商品名があった時、C2:C5の 商品（フルーツ）を含むセルがそれぞれ幾つあるかカウントしたい。ただし、式はD2に1つだけ入れるものとする。
こんな時どんな式を組めばよいでしょうか。
データを使い方い方は以下をコピペで貼り付けて使用ください。
商品 商品 カウント
りんごジュース りんご ここに式を入れる
ばななシェーク ばなな
氷めろん めろん
りんご飴
種なしぶどう
冷凍みかん
焼きりんご
りんごシャーベット
チョコばなな
めろんアイス
りんごのタルト
皮ごとぶどう
いちごミルク
氷いちご
丸ごとりんご
フレッシュばなな
めろんソーダ
りんごのお酒
産直ぶどう
いちごケーキ
すいかの名産地
やってみましょう！
↓↓
ここから回答です。
↓↓
A3.A列のデータでC2:C4の商品名を含むものがそれぞれ幾つあるかカウントするARRAYFORMULAの式
回答です。
=ARRAYFORMULA(COUNTIF(A2:A,"*"&C2:C4&"*"))
これは簡単ですね。COUNTIFの 構文 COUNTIF(範囲, 条件) の第2引数 「条件」の方を ARRAYFORMULAで 配列をとれるようにして C2:C4 を入れます。
ただし、今回は条件が「含む」である為ワイルドカード * と組み合わせて "*"&C2:C4&"*" とします。
では、これを少し応用した問題です。
Q4. A列のデータで C2:C4 の商品名のいずれかを含むものが全部で幾つあるかカウントしたい（なるべく短い式で）
先ほどのお題の続きです。 C2:C4のセル「りんご、ばなな、めろん」のいずれかの言葉を含むセルが A列に幾つあるかカウントしたい。
さらに出来るだけ短い式でまとめたい。こんな時、どのような式を記述すればよいでしょうか？
前提条件として今回は 重複、つまり「りんごとばななのクレープ」のような C2:C4セルの言葉を2種類以上含むセルは存在しないものとします。
データは先ほどのものを利用ください。考えてみましょう！
↓↓
ここから回答です。
↓↓
A4. A列のデータで C2:C4 の商品名のいずれかを含むものが全部で幾つあるかカウントする式
先ほどの お題3の結果を使えば簡単ですね。
このように りんご、ばなな、めろん を含むセルはそれぞれ 7,3,3 個ずつあって、重複は考慮不要なので これを足した 13が正解になります。
式としては SUMを使って
=ARRAYFORMULA(SUM(COUNTIF(A2:A,"*"&C2:C4&"*")))
このような式で 13は導き出されます。
しかし、お題は「なるべく短い式で」とありました。これより短い式は存在しないのか？
実はこの式は ARRAYFORMULAを無くすことができます。
=ARRAYFORMULA(SUM(COUNTIF(A2:A,"*"&C2:C4&"*")))
▼
=SUMPRODUCT(COUNTIF(A2:A,"*"&C2:C4&"*"))
この =SUMPRODUCT(COUNTIF(A2:A,"*"&C2:C4&"*")) が短いという条件を満たした回答となります。
このように ARRAYFORMULA + SUM の部分を SUMPRODUCT関数で代用できるわけです。
SUMPRODUCTは、本来は2つの配列のそれぞれの要素を掛けて最後に合計する関数ですが、古来Excelの時代から 内部で配列計算が出来る関数として、SUMIFSやCOUNTIFSの代わりとするトリッキーな使い方をしたりと 重宝されてきました。
最終的に合計（一つの値）を返す関数なので スピらない関数ではありますが、内部では配列計算が出来る。つまり ARRAYFORMULAして最終的にSUMするなら、SUMPRODUCT関数にまとめられるということです。
これが コツの4番
■ARRAYFORMULAを使いこなすためのコツ
4. ARRAYFORMULAが不要になる関数を理解する
です。
ちなみに スピル対応の Excelだと SUMで十分なので、SUMPRODUCTは出番がほぼありません。
しかし Googelスプレッドシートでは、ARRYFORMULA ＋SUMはSUMPRODUCT に集約した方がスッキリした式になる ことが多いで、まだまだ活躍してくれます。
以前も同じこと書きましたが、SUMPRODUCT関数は 転スラだと いぶし銀 の ハクロウ的イメージですｗ
ARRAYFORMULA的な効果のある関数
■ARRAYFORMULAを使いこなすためのコツ
4. ARRAYFORMULAが不要になる関数を理解する
この ARRAYFORMULA が不要になる関数、ARRAYFORMULAに近い効果のある関数には、他に何があるかを見ていきましょう。
ARRAYFORMULAの効果である、配列計算と複数セルへの結果出力（スピル）という視点で関数を分けると
■内部で配列計算できるが、一つの値を返す関数
▶ ARRAYFORMULA的な効果のある関数（ただしスピらない）
（SUMPRODUCTなど）
■内部で配列計算できないが、結果を複数セルへ展開（スピル）できる関数
▶ ARRAYFORMULAと組み合わせれば、内部の配列処理が出来る
（TOCOLやCHOOSECOLS、UNIQUEなど）
■内部で配列計算できて、結果が複数セルへ展開できる（スピル）する関数
▶ ARRAYFORMULAは不要となる関数
（FILTERやSORT、INDEXなど）
この3つに分類されるかと思います。
内部で配列計算できるが、一つの値を返す関数
先ほど 登場した SUMPRODUCTは、ARRAYFORMULAで配列計算した結果を最終的にSUMで合計するのであれば代用できます。
もし 先ほどのお題が 重複ケース、つまり 「りんご、ばなな、めろん」の複数を含む セルを考慮する必要がある場合、 COUNTIFでそのまま SUMPRODUCTをすると 1つのセルを 重複カウントしてしまい、誤った結果になります。
重複をマイナスしようとすると、かなり面倒な式になってきます。
しかし、正規表現を使った「または」の記述が使える REGEXMATCH関数を SUMPRODUCTと組み合わせれば
=SUMPRODUCT(REGEXMATCH(A2:A,TEXTJOIN("|",TRUE,C2:C4)))
このように記述することで 「または」でCOUNTIFをしたような、重複を考慮した正しい結果を得ることができます。
余談ですが、 REGEXMATCH関数が返す結果は TRUE、FALSEで これは SUM関数だと数値として見なしてくれません。
=ARRAYFORMULA(SUM(REGEXMATCH(A2:A,TEXTJOIN("|",TRUE,C2:C4))*1))
このように *1で TRUE,FALSEを数値化してあげる必要があり手数が増えます。
ARRAYFORMULAいらずで、そのまま TRUEを1、FLSEを0として計算してくれる SUMPRODUCRが 便利ですね。
SUMPRODUCTと同じような 「内部で配列計算をして一つの値を返す関数」が他にもあったような気がするんですが、どうにも思い出せません・・・。思いついたら補足修正します。
内部で配列計算できないが、結果を複数セルへ展開（スピル）できる関数
後から追加された 配列操作系の関数 TOCOL / TOROW、CHOOSECOLS / CHOOSEROWS、WRAPCOLS / WRAPROWS や UNIQUE関数などは スピる関数（結果を複数セルに書き出しできる関数）ではあるんですが、式内での配列計算（ARRAYFORMULA効果）はありません。
なので、
A列の りんご を除いて上詰めした 結果を出力したい場合、TOCOLの第2引数 1指定で 空白除外を利用して
=TOCOL(IF(A2:A="りんご",,A2:A),1)
こんな式でいけそうなんですが、内部のIF関数に配列計算の効果が付与されていないので、正しい結果になりません。
式が長くなってしまいますが、ARRAYFORMULAを付けて
=ARRAYFORMULA(TOCOL(IF(A2:A="りんご",,A2:A),1))
このようにすると正しい結果が返ります。
スピルする関数だからと言って ARRAYFORMULAがいらない。というわけでは無いのです。
ちなみに 関数ではありませんが、{ } を使った配列化も、スピル効果（複数セルへの展開効果）があります。
単純に同じスプレッドシート内の他のセル範囲をそのまま出力したい場合、
=ARRAYFORMULA(A2:A4) でも出来ますが、={A2:A4} と記述してもOKです。
=A2:A4 ✖
=ARRAYFORMULA(A2:A4) 〇 セル参照配列
={A2:A4} 〇 配列
中カッコ記述が短くて良さそうですが、実は 同じように見えて ARRAYFOMRULAの方の結果は セル参照の配列で、中カッコを使った方の結果は シート上の位置情報を失った配列となります。
つまり {A2:A4} に対して ROW関数やOFFSET関数は使えません。あまり困ることはありませんが 知っておくと役に立つことがあるかも。
内部で配列計算できて、結果が複数セルへ展開できる（スピル）する関数
これに該当する代表的な関数が FILTER関数、SORT関数、INDEX関数です。
たとえば FILTER関数であれば、先ほどのTOCOLの りんご（と空白）を 除外する処理も
=FILTER(A2:A,A2:A<>"りんご",A2:A<>"")
このようにFILTER関数のみで記述できますし、後ろに「ジュース」を付けて結果出力したいといった場合も
=ARRAYFORMULA(FILTER(A2:A,A2:A<>"りんご",A2:A<>"")&"ジュース")
このように結果をさらに ARRAYFORMULAで括って &"ジュース" の処理をする必要はなく、
=FILTER(A2:A&"ジュース",A2:A<>"りんご",A2:A<>"")
FILTER関数内であれば ARRAYFORMULA と同じ効果があるので、第1引数で A2:A&"ジュース" とすることで対応できます。非常に便利ですね。
つまり、FILTERやSORT関数のついでに結果の配列を加工したいって場合は、ARRAYFORMULAは不要ってことです。
無駄になんでもかんでも ARRAYFORMULAを付けるのではなく、このように不要になるケースも多いので、処理の書き方を工夫して、ARRAYFORMULAを外して動くか試すことで 式をスッキリと記述できます。
ARRAYFORMULAを完全に代替できる INDEX関数
ARRAYFORMULA と同じ効果がある 内部で配列計算できて、結果が複数セルへ展開できる関数の中でも、ARRAYFORMULAと同じように使える関数が INDEX関数です。
他の FILTER関数やSORT関数は 条件による絞り込みや、並べ替えといった、その関数ならではの効果がありますし、引数の指定も決まっています。
たとえば
=ARRAYFORMULA(IF(A2:A="",,B2:B*C2:C))
こちらの式と同じ効果を得ようとしても
=FILTER(IF(A2:A="",,B2:B*C2:C))
単純に ARRAYFORMULAを FILTER関数に変えた場合は、
引数が2個以上必要であると #N/Aエラーになりますし、
ARRAYFORMULAを SORT関数に変えた場合は、
SORT関数の第2引数、第3引数省略時の挙動となり、配列計算と出力はされるものの、合わせて昇順で並び替えもされてしまいます。
しかし、本来
INDEX(参照, [行], [列])
このように範囲から行、列で指定したセルのコンテンツを返す INDEX関数は、Googleスプレッドシートでは 第2引数、第3引数を まるっと省略すると 第1引数をそのまま返す & 第1引数内では 配列計算となる 特性から
=ARRAYFORMULA(IF(A2:A="",,B2:B*C2:C))
▼
=INDEX(IF(A2:A="",,B2:B*C2:C))
このように完全にARRAYFORMULAの代わりとして使うことができます。しかも関数名が短い！というメリットもｗ
ただ、ARRAYFORMULAを使った方が、スピらせたい・配列計算をさせたいという意図が伝わりやすいという利点があります。
スピル効果、配列計算効果を得る為だけに ARRAYFORMULAの代わりにINDEX関数を使う方法は、式を短くできるメリットはありますが、ややトリッキーな手法かなと思います。
mirも普段は ARRAYFORMULAを使います。
ARRAYFORMULAとINDEX関数
INDEX関数は ARRAYFORMULAと同じ 配列計算と配列出力の効果があるというメリットがありますが、一方で失った機能もあります。
それが 第2引数（行）と第3引数（列）に配列を取れないというデメリットです。
Googleスプレッドシートの INDEX関数は 第2引数、第3引数に配列を取れない
たとえば、A:Dの範囲から 3,4,5行目の1列目を取得したいといった場合
=INDEX(A:D,{3;4;5},1)
このような式を入れても結果は 第2引数の先頭 3行目の1列目である「ばなな」を返すだけです。複数行を返すことは出来ません。
同じく、A:Dの範囲から 2,3列目を取得したいといった場合も
=INDEX(A:D,1,{2,3})
この式の結果は、1行目の 第3引数の先頭 2列目 「単価」だけしか返せません。
スピらんな～。と言いたくなりますねｗ
Excel のINDEXは 第2引数、第3引数が配列指定可能
一方 Exelの INDEX関数は、第2引数、第3引数に配列をとることが可能です。
=INDEX(A:D,{3;4;5},{2,3})
このような式で A:D範囲の 3，4，5行目の 2,3列目のデータを取得することが出来ます。
ただし、Excelの場合
第2引数(行)、第3引数(列) の片方が単体の数値だった場合は、もう片方を省略して、行丸ごと、列丸ごと を取得することが出来ますが、
=INDEX(A:D,{3;7},)
このような記述で 3行目、7行目を 丸ごと取得はできません。
つまり 第2引数、第3引数の片方が配列だった場合は、もう片方を省略して 行丸ごと、列丸ごと取得はできず、丸ごと取得したい場合はもう片方も配列指定する必要があるってことです。
=INDEX(A:D,{3;7},{1,2,3,4})
または
=INDEX(A:D,{3;7},SEQUENCE(1,COLUMNS(A:D)))
このように記述することで、3行目と7行目を まるっと取得ができます。
INDEX関数は、Googleスプレッドシート と Excelでこのような違いがあります。
ARRAYFORMULAでは INDEX + MATCH がスピらない
このExcelとGoogleスプレッドシートのINDEXの挙動の違いで、大きく影響を受けるのが INDEX＋MATCH を使った検索です。
=INDEX(A1:D12,MATCH(F2:F4,A1:A12,0),{1,2,3,4})
Excelの場合、上の画像のように A1:D12の表のA列から F2:F5の ぶどう、みかん、りんご を上から検索して、1つ目に見つかった行をまるごと（1列目から4列目まで）出力、これを1つの式でやりたい。といった場合、INDEX＋MATCHを組み合わせた式を使います。
この縦・横スピらせる処理は、Excelの XLOOKUPやVLOOKUPでは出来ません。
しかし、Googleスプレッドシートでは INDEX関数が 第2引数、第3引数に配列指定が出来ない為、これがうまくいきません。
=ARRAYFORMULA(MATCH(F2:F4,A:A,0))
画像の上のように MATCH関数は ARRAYFORMULA関数と組み合わせて使えます。
しかし、これをINDEX関数と組み合わせて
=ARRAYFORMULA(INDEX(A:D,MATCH(F2:F4,A:A,0),{1,2,3,4}))
このように ARRAYFORMULA + INDEX + MATCH の式を作ると、結果は配列とならず 思うように機能しません。INDEXが原因ってことですね。
ARRAYFORMULAの解説サイトでよく見かける 「INDEX+MATCH はARRAYFORMULA と組み合わせて動かない。残念！」は、
・Excelだと INDEX + MATCH はスピルする
・ARRAYFORMULA は MATCHには効果があるが、INDEXには効果がない
こういった背景があるってことです。
Q5. Googleスプレッドシートで INDEX + MATCH のような検索して縦・横にスピルする式をつくりたい！
では、最後のお題いってみましょう。
Googleスプレッドシートで Excelの INDEX＋MATCHと同じように A:Dの表のA列から F2:F5の ぶどう、みかん、りんご を上から検索して、1つ目に見つかった行をまるごと（1列目から4列目まで）出力、これを1つの式でやりたい。
これを実現する為には、G2セルにどんな式を入れればよいでしょうか？
お題用データ（A1セルにコピペして利用）
商品 単価 数量 金額
りんご 100 2 200 ぶどう
ばなな 150 3 450 みかん
めろん 900 5 4,500 りんご
りんご 150 4 600
ぶどう 200 5 1,000
みかん 130 10 1,300
りんご 300 2 600
りんご 150 3 450
ぶどう 200 10 2,000
みかん 130 4 520
りんご 300 6 1,800 INDEXではない関数を使うことになります。シンプルな方法で大きく3つの式が思いつきます。考えてみましょう！
↓↓
ここから回答です。
↓↓
A5. Googleスプレッドシートで INDEX + MATCH のような検索して縦・横にスピルするARRAYFORMULA式
3パターンの回答を見ていきましょう。
A5-1. ARRAYFORMULA + VLOOKUP を使う方法
=ARRAYFORMULA(VLOOKUP(F2:F4,A:D,{1,2,3,4},false))
Googleスプレッドシートでは ARRAYFORMULAと組み合わせたVLOOKUP関数が 縦横スピル出来る関数となります。（Excelだと VLOOKUPは縦横スピルしません）
第3引数の 範囲内の行番号の部分を配列指定 {1,2,3,4} することで、横方向へもスピルし、複数行・複数列の結果を返すことができます。
これは以前、XLOOKUPの回のnoteで紹介しました。第3引数の配列をSEQUENCE関数で生成する方法にも触れています。
VLOOKUPは ARAYFORMULA関数との相性が非常に良い関数です。
A5-2. ARRAYFORMULA + CHOOSEROWS を使う方法
=ARRAYFORMULA(CHOOSEROWS(A:D,MATCH(F2:F4,A:A,0)))
INDEXは 範囲から行、列を指定して取り出す関数ですが、これに近い動きが出来る関数が
CHOOSEROWS関数 ・・・ 行番号を指定
CHOOSECOLS関数 ・・・ 列番号を指定
Excelから輸入された これらの新関数です。今回はMATCHで行番号を配列で取得したものを CHOOSEROWSに渡すことで、縦横スピルを実現しています。
ただし、CHOOSEROWSは スピルはしますが、内部で配列計算は出来ない為、第2引数に入れた MATCH関数で配列を扱えるように一番外側に ARRAYFOMRULAが必要となります。
INDEX関数との違いや CHOOSEROWS関数の活用方法など、過去noteも参照ください。
A5-3. MAP+ INDEX + MATCH を使う方法
=MAP(F2:F4,LAMBDA(v,INDEX(A:D,MATCH(v,A:A,0))))
最後は ARRAYFOMRULAを使わない回答なんでオマケですｗ
どうしても ARRAYFORMULAが機能しない場合は、LAMBDAヘルパー関数を使う方法があります。
特に Googleスプレッドシートの場合は、MAPやBYROW、BYCOL の各要素の結果で一次元配列を出力できる（最終的に配列のネストが可能となる）ので、非常に強力です。
※ Excelの LAMBDAヘルパー関数は、配列のネストが出来ません。
次回 ARRAYFORMULAが効かない 関数を検証
かなり長くなってしまったので、今回はここまでとします。
■ARRAYFORMULAを使いこなすためのコツ
1. ARRAYFORMULAを使った際は 配列や範囲のサイズを意識する
2. ARRAYFORMULAは 式内で一つ一番外側にそえるだけ
3. ARRYFORMULA内では AND関数やOR関数が使えない
4. ARRAYFORMULAが不要になる関数を理解する
5. ARRAYFORMULAが使えない関数、使いにく関数を理解する
今回は、コツの1～4が登場しましたね。そして 5は、最後に登場したINDEX関数です。
ARRAYFORMULAが効かない（または思うように動かない）関数が他にもあります。
もちろん、これら ARRAYFORMULA関数が効かない関数を、LAMBDAヘルパー関数 MAP、BYROW、BYCOLで解決する方法もありますが、実はLAMBDAがGoogleスプレッドシートに輸入される以前に対応していた 古のテクニックが幾つもありました。
この辺りをお題形式で次回触れていきたいと思います。 ARRAYFORMULA もう1週引っ張りますｗ
いいなと思ったら応援しよう！
