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

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

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. 計算結果が補完されるので、1つずつ計算式を入れてミスをするといったことを減らすことが出来ます。
  5. 関数の組み合わせによっては、単独では実現できない機能を実現できるようになる(Query関数で外部結合的な動きをさせることができるようになるなど)。

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

但し、自作のユーザ定義関数ではうまく動作しませんので、基本組み込み関数を対象に使います。自分で配列を返す関数を作って対応しましょうい。

Excelのそれとの違い

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

ここが大きな違いで、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にて補完されるようになっています。関数で補完されるので、修正しようとして計算結果がおかしくなるのを防げます。

使い方

シンプルな使い方

九九を実現

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

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

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

売上合計を取る

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

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

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

他の関数との組み合わせ

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

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

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

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

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

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

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

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

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

曜日を判定

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

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

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

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

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

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

sumifs・sumproductを使わないで実現

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

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

関連リンク

共有してみる: