Arrayformula関数で配列数式の便利さを知ろう

Excel 2019が登場してから、Office Insider界隈がちょっとだけざわついてる話題がいくつかあります。それが新機能スピルと新しくサポートされた新関数達。しかし、このスピルと新関数達（Filter関数等）なのですが、Googleスプレッドシートではとっくの昔にサポートしてるものだったりするのと、スピルに至っては以前のofficeでも配列数式と呼ばれてた類のものを使いやすくしたものでしかありません。

今回はそんなGoogleでサポートされてる配列数式を実現するarrayformula関数を見てゆきたいと思います。この関数もFilter関数やQuery関数同様、Googleスプレッドシートでだけ利用可能な特別な関数です。

Arrayformula関数について

今回使用するスプレッドシート

• arrayformulaサンプルスプレッドシート

概要

Arrayformula関数は、それそのもの自体は配列で返してくれるというだけの関数なので、一見するとややこしくて難しそうに見える関数ですが、実際に使ってみるとそれが酷く単純で且つとっても便利な関数であることに気がつくと思います。ただ、Excel文化で慣れきった脳みそだと、特殊な動作（配列で返すこと）に見えるだけです。

すでにGoogle Spreadsheetの場合似たような返し方をする、Filter関数やQuery関数、Sort関数などが日常で使われている為、配列で返すことがどれだけ便利なのかというのは周知の事実ですね。

ただ、Excelにも数式の繰り返しについては、テーブル機能で実現してる側面があるのですが、このテーブル機能活用していない人が多いのに実に驚きます。古いExcel文化やその資料が未だに多くを占めてる弊害とも言えます。

※但し、数式の繰り返しと言っても、sum関数のようにもともと範囲で指定してる数式をarrayformulaで括っても、配列で返しません。例えば、=arrayformula(sum(A1:D1))としてもA1:D1の答えが返ってきて、以下のセルに補完されたりしません。ここがややこしい点といえます。あくまでも縦方向である点が理解する重要なポイントです。

効用

Arrayformula関数はその特性上、かならず他の数式や関数と組み合わせて利用する関数です。またその関数も単一セルを参照させるのではなく、必ず範囲で指定する事になります（IF文ならば、=arrayformula(if(A2:A10 > 10, "○","☓"))といった具合に）。数式が補完されるようになるのではなく、一度に計算して一発で返すのが特徴です。

1. 通常単一セルしか指定できないvlookup関数が、指定する値に範囲が使えるようになる。
2. 配列で一発で計算して返してくれるので、スプレッドシート上での表示が高速化します（数式をコピーしてるわけではないので）。
3. 参照範囲をA2:Eといった形にしておけば、データが追加されても数式の修正をする必要はありません。（Excelではテーブル機能で同様のことが実現できますね）
4. 計算結果が補完されるので、１つずつ計算式を入れてミスをするといったことを減らすことが出来ます。
5. 関数の組み合わせによっては、単独では実現できない機能を実現できるようになる（Query関数で外部結合的な動きをさせることができるようになるなど）。

ですので、一個ずつ数式をドラッグして作るといった作業を無くすことができます。全ての関数に於いてしている単一セルは範囲で指定が出来るようになるので、実に有用な関数です

Excelのそれとの違い

Excelでもついに配列で返す関数が使えるようになりました！といってもまだ、一般的ではなく（Insider参加していないと使えません）、そもそも今頃になって搭載している機能なので、G SuiteやGoogleスプレッドシートを使ってきた人間からしたら、全く目新しくもない機能です。また、過去にExcelで配列数式が使えるという話がありますが、あくまで計算で配列が使えるだけで、配列でデータを返してくれるわけではありません。

※現在はMicrosoft365でもスピルという機能として実装されました。arrayformulaと互換です。

ここが大きな違いで、2019以前のExcelの配列数式とGoogleスプレッドシートでArrayformulaを使った配列数式は意味が全く異なります。以前のExcelの配列数式はいわば、Arrayformulaとsum関数を組み合わせて合計できるといったものであって、結局は単一のセルでしか答えが返せません。また、Ctrl + Shift + Enterで確定しないと使えないという使い勝手の非常に悪いものでした（おまけにちょっとでも編集すると、元に戻って計算結果がおかしくなる）。

Arrayformulaは配列で返す事が可能でこれが非常に便利で、これこそがArrayformulaという関数の魅力です。Excelのスピルは「=A2:A10*B1:J1」で九九を実現できますが、Arrayformulaは「=arrayformula(A2:A10*B1:J1)」で実現出来ていたものです。ExcelがようやくGoogleスプレッドシートに追いついたと言えます。

※ちなみに、Googleスプレッドシート上で配列数式を入れて、Ctrl + Shift + Enterを実行するとArrayformulaにて補完されるようになっています。関数で補完されるので、修正しようとして計算結果がおかしくなるのを防げます。

Microsoft365のExcelで使える関数は知らないと損をする

使い方

シンプルな使い方

九九を実現

縦に1〜9、横にも1〜9。このマス目を使って、arrayformula数式一発で九九を実現します。スピルでもよく例題にだされる事例ですね。数式にすると以下のような感じになります。

縦と横を掛ける作業を１個ずつ構築するのではなく、まとめて数式の範囲で掛けて返してるわけです。非常に単純なのですが、これを手作業でやるとなると、実は$使って参照先固定であったり、ドラッグ作業などが発生し、非常に手間が掛かります。

図：数式一発で九九が作成できます。

売上合計を取る

通常であればSUM関数を使ってそれぞれの商品の期日毎の合計を作って、ドラッグして作るところですね。arrayformulaを使った数式の場合、sum関数自体を使いません。それぞれ縦の範囲を加算する数式で作れます。

あえて、sum関数を使わない方法なので、通常はsum関数で横の合計を取ってドラッグするほうが楽ですね。このような例題の場合、sumproductなどの数式を使わないでarrayformulaを使ったほうが数式としてシンプルという言い方はできるかもしれません。sumproduct知らない人が結構いるので微妙ですが。

図：縦に範囲を取り、それぞれ合計する理屈

他の関数との組み合わせ

arrayformulaは他の関数と組み合わせてこそその真価を発揮します。特に範囲で指定できない関数にて、範囲で値を指定できるようになるので、繰り返し何度も数式を作ったりドラッグしてオカシクなったりを防げるのは、この関数の最大の魅力です。

単純な連番を作る

連番を作るのに毎回、1,2,と入れてドラッグとか1000行までずーっと眺めながらやるというのは非生産的です。arrayformulaであれば、row関数と組み合わせて一瞬で作れます。

row関数は行目を返す関数なので、範囲指定し帰ってきた答えから-1すれば、1から始まる連番を指定の範囲で一瞬で埋めることが可能です。また、文字列と組み合わせるテクニックもありますね。

図：もう連番を作るのに時間は必要ありません

vlookupでまとめてルックアップ

これはもっともよく使う事例ですね。通常vlookupはルックアップするキーワードは単一しか指定できません。arrayformulaを使うとそれが範囲で指定が可能です。以下のような数式になります。

A2:A20で指定されていますね。まとめてキーワードを選択し、それを範囲（data!$A$2:$D$20）の中にある4列目の値を絶対値で引っ張ってます。こうする事でまとめて一発で計算ができるので、結果的にvlookup高速化が実現できます。

図：vlookupまとめて実行ができる

Chooserows + Matchでxlookupの代わりに

xlookupは横もしくは縦のどちらかの1行・１列だけを配列として返してくれます。しかし、arrayformulaではxlookupが使えない為、1つの数式でまとめてルックアップさせたいといった場合これでは困ります。

そこで使うのがChooserows関数 + Matchで表1,2それぞれの指定の列で合致するものがあったら、表1の残りの列をまとめてルックアップが可能です。

図：A列で合致するものをまとめてルックアップ

sumifまとめて条件判定で合計

sumifは１つの条件を元に指定範囲内のレコードの特定列の合計値を出す関数です。そのキーワードはしかし範囲で指定ができません。これもarrayformulaにてまとめて範囲で指定し、まとめて条件判定の合計を出す事が可能です。

一回つくってしまえば、ドラッグが不要。これこそarrayformulaの美味しいポイントですね。

図：M列には配列で答えが返ってきてます。

曜日を判定

日付から曜日を判定する関数としてTEXT関数があります。こちらも単体で判定する関数なので、範囲指定が使えるとまとめて曜日判定が可能になります。また範囲の指定にINDIRECTを使って行数を判定させたものを組み合わせると、行が追加されても自動でarrayformulaの数式が拡張されるので、メンテフリーになります。

図：この手の曜日リスト意外と作る機会多いです

文字列同一判定とまとめて判定

２つの列をならべてAとB両方が同じ値かどうか？を判定するのにはExact関数を使います。これもarrayformulaでまとめて出来ますが、今回のケースはそのデータの束に於いて、すべての行が同じ判定をしてるか？そうではないかをarrayformulaで行わせる方法です。ですので、配列で値を返すのではなく、返ってきた値を別の関数で判定させる特殊な方法です。

判定にはAND関数（全て同一かどうか判定）、OR関数（一部が同一かどうか判定）を用います。前月と今月のデータの差分判定など、事務作業では意外と使うシーンがあるかと思います。

図：給与計算などでは活躍する方法かもしれません

AND・OR関数と併用できないケース

上記の事例では範囲が固定化されている為、ANDやORを使っていますが、きちんと想定した結果が返ってきます。しかし、これをA2:AといったようなA2以下全部みたいな、範囲が固定化されていない指定方法と条件判定を組み合わせるとオカシナ挙動になります（固定化されていれば、以下の数式でも問題なく計算されます）。

例えば以下のような場合、想定した計算がされない上に、0が下の方にずーっと並びます

図：計算は間違ってるし、0が続くオカシナ結果

このケースの場合、AND関数やOR関数を使うのではなく、+記号や*記号を使うことで、ANDとORの代わりを務める事ができ、正しい想定した結果が返ってくるようになります。ちなみに、数式上では+はOR、*はANDという意味になり、SUMPRODUCT関数でも見かける方式です。

図：正しく計算され、0が続くこともない

また、ANDとORを組み合わせて使うことも出来ますが、この場合、値の無いレコードにも判定が入るので、ISBLANK関数なども組み合わせて使って判定すると尚良いでしょう。

図：ANDとORを組み合わせたパターン

sumifs・sumproductを使わないで実現

arrayformula関数を使うと上位関数を使わなくても、下位関数だけで計算が実現できるようになります。sum関数で言えば上位関数はsumifsやsumproductがそれに該当します。arrayformula内のsum関数で条件を加えたもの同士を、sumproduct的な形で掛け合わせて、sumifsを実現するみたいな感じです。

図：9/29の缶詰売上合計を出しています

マトリクス変換

頂いたデータが既にクロス集計されてしまってるデータの場合、二次利用するには非常に使いにくいです。そこでこのデータを縦横変換して、集計前のデータに戻したいシーンがあります。このアクションを実現するのにもarrayformula関数は役に立ちます。この場合組み合わせて使う関数は、split関数とflatten関数という聞き慣れない関数を利用します。

数式としては以下のようなスタイルになります。

FLATTEN関数は指定した範囲内のデータを1次元配列に変換するための関数で、指定した順番に列挙します。指定した記号で分割するSplit関数を組み合わせ、結果を配列で返すArrayformulaでくくると、見事に縦横変換して返してくれます。手動で縦横変換するのではなくシートにデータを貼り付けたら、関数で自動的に縦横変換した状態にできるので、Google Apps Scriptでの活用にも繋がります。

※GASのコードで変換する手段はこちらに掲載

図：いわゆる縦横変換

自作のカスタム関数をArrayformulaで使う

そのままでは自作のユーザ定義関数ではうまく動作しません。この関数は本来基本組み込み関数を対象に使います。もともと、GASの場合、自作の関数では配列で返せるので、そちらを使う方がスマートですが、自作の関数でArrayformulaを使うにはちょっとした工夫が必要です。

その場合自作のユーザ定義関数は配列で返すように構築しなければなりません。また、範囲ではない単体利用の場合も考慮するには以下のように構築します。

この自作したtomato関数をarrayformulaで使うには

これで、値の入ってるA2:A6のそれぞれの値に🍅をつけて返す関数が作れました。

図：自作関数でもArrayformulaで展開できました。

関連リンク

• Excel 2019の新機能「スピル」とは？ 掛け算九九の表も1行で完成！
• 【新機能】エクセルのスピルでマトリックス表を作る
• Excel2010：配列数式とは
• Google SpreadSheetで表データ（Matrix）を分解して、リストデータ（row）にする変換する関数を追加する
• Googleスプレッドシートの配列数式でAND,OR関数がバグる件の対処