GoogleスプレッドシートARRAYFORMULA関数の使い方を完全解説！

2018年8月18日 2023年1月4日 17分18秒

ARRAY FORMULA関数

この記事は2021/12/11 に更新されました。

どうもY-nek0(@Ynek0)です。

さて、Googleスプレッドシートを使って目指せ定時退社。

帰りに相席屋にいける時間を確保しよう。

では今回はARRAY FORMULAについての使い方です。

ARRAY FORMULA関数がGoogleスプレッドシートを

代表する関数といっても過言ではないです。

ARRAY FORMULA関数に関してはGoogle様より

独自にショートカットキーもご用意されているご身分です。

なんかスゴイって事だけが伝わってきましたよね。

そんなARRAY FORMULA関数のご紹介です。

では以下目次です。

聞いたこと無いARRAY FORMULA関数って何？

最初なんと読むのか迷いますよね。

読み方は『アレイフォーミュラ』と言うらしいです。

ARRAYは配列

FORMULAは式

統合すると配列式と言うこととなります。

つまりはひとつの結果しか出さなかった関数を

複数列対応の関数に変化させる関数

それがARRAY FORMULAです。

多分頭の中に『ちょっとなに言ってるかわからない。』とサンドイッチマンみたいな感じに

なってしまったかと思います。

Ynek0

で、デスヨネー……

では分かる様に基本構文から順に説明していきますね！

ARRAY FORMULA関数の基本構文

まずは基礎からやっていきましょう

ARRAYFORMULA(配列数式)

いや分かんないし、配列数式って何よ？

デスヨネ～……

ちょっと言葉で説明すると非常に長くなるので

一度スプレッドシートを見せますねっ！ドン！

なーんだ、どっちも結果同じじゃないか。

それだったらよく分からないARRAY FORMULAを使うよりIFを使った方がマシじゃないか。

そう思うじゃないですか。

では数式を見せましょう。

こちらですドン!

※数式表示させながらスプレッドシートの表示が出来なかったので画像で処理しました。

おっ？なんかARRAY FORMULA関数は式が１つだけだ。下の値は何？手入力？

ARRAYFORMULA関数の下にある○と×は自動的に入力されます。

つまりは１つの式で繰り返し処理を自動的に行ってくれるのがARRAY FORMULAになります。

そういうこと！FILTER関数とかUNIQUE関数とかと
似たようなことを自分がやりたい関数で出来るって事？

まずはそんな感じで理解して頂けたらと思います。

ARRAY FORMULA関数は単体では特定の何かの結果を出すと言う事はせず、

指定された事を指定された範囲で行い、結果を出力するのです。

ARRAY FORMULA関数を使用する事で通常の関数の記述が変わります

今回例であげたARRAY FORMULA関数のIF文の記述は下記の通りです。

ARRAYFORMULA(IF(C2:C10 >=20,"○","×"))

ん？いつものIF文と変わってない？

良い目の付け所です。

ARRAY FORMULA関数は関数を単一では無く範囲で指定する事が出来ます。

今まで値１つだけ入れてね！って言われていた関数が数多くあると思います。

それらのほとんどはこのARRAY FORMULA関数で範囲指定する事が可能です。

へぇー、便利なんだね。だんだん凄さが分かってきた。

それはとても嬉しいです。

それ以外にもメリットはあるんですよ。

メンテナンスの省略化と重さ軽減

Googleスプレッドシートとはいえ、関数を多く使っていたら

それは表示が重くなります。

それを軽減させるのがARRAY FORMULA関数を初めとする

Googleの独自関数の数々です。

メンテナンス作業１つにおいても全然作業量、時間共に

断然に違いますので非常に有用です。

確かに数万行のネストされたIF文を全部ちまちまと変えないといけない作業をするより

はるかに効率的だし精神的にも負担が無いよねｗ

そうそう、そう言った作業を一瞬で終わらせる事が出来て次のタスクに早く取り組める

そして、常時定時ダッシュが出来るというわけなのです。

ARRAY FORMULA関数を使い他の関数を使用した例

では各関数を利用したARRAY FORMULA関数の例を挙げていきます。

ROW関数

よく項番などで利用する事があるROW関数ですがARRAY FORMULA関数を利用する事により

セルを削除するときに発生する項番のズレなどを整えてくれるので便利です。

=ARRAYFORMULA(ROW(A1:A10))

VLOOKUP関数

エクセルなどでもよく使うVLOOKUP関数はARRAY FORMULA関数を利用する事で

検索値を増やす事が出来ます。

=ARRAYFORMULA(VLOOKUP(D1:D10,A1:B10,2,FALSE))

太文字である部分がARRAY FORMULA関数を使用する事により変化する部分です。

検索値をどの範囲まで指定するかの範囲を指定できます。

こちらに関しては難しいVLOOKUPの関数を設定する際に使用すると後日メンテナンスする際に

この関数を編集すればよいだけなので使い勝手がよいです。

ARRAY FORMULA関数が利用できない関数

逆にARRAY FORMULA関数が利用できない関数もあります、

セルの範囲指定から答えを導き出す関数はARRAY FORMULAは利用が難しい。

ARRAY FORMULA関数が使えない関数は以下の通りです

SUM関数
AVERAGE関数
MAX関数
MIN関数

等

なぜならば関数の特性上、範囲を指定して答えを導き出す関数であるため、

ARRAY FORMULAが出力する範囲を指定できないのです。

こういった場合でARRAY FORMULA関数を使う場合は色々とやりくりすればできるのですが、

正直素直にセル範囲を指定して足し算させたり割り算させた方が早いです。

セル単体で完結する関数はARRAY FORMULAは利用不可

例えばRAND関数です。

これはランダムに数字を表示する関数ですがARRAY FORMULA関数を使ったとして

表示されるのはランダムな関数1つです。範囲できる所が……ありません。

なのでRAND関数に関してはARRAY FORMULA関数が使えません。

ARRAY FORMULA関数と似た様な機能を使用することにより、一定区間を表示させる事は可能です。

※使える方法がありましたら教えて頂けると嬉しいです。

ARRAY FORMULA関数と様々な関数を使ったシステム例題

簡単な分別システムを作ってみましょう。

例えば、仕事で使う事を想定して

二つの異なるシステムからCSVが出力されます。

以下図にあるCSV1は営業員情報が羅列されている営業員マスター

以下図にあるCSV2は営業員の売上データが入った売上マスター

条件として売上マスターも営業員マスターもCSVの値を貼り付けただけで

売上が25万以上の営業だけを抽出したデータが分別され

別シートに自動出力するようにして欲しいと

お願いされたことにします。

その際にはどういったことをするかというと

※タブのCSV1とCSV2をご参照ください。

解答と解説

CSV１には営業員データ、人の名前が管理されているシートです。

CSV2には売上データ、売上が管理されているシートです。

応用ARRAY FORMULAには売上が２５万以上の営業が

ARRAY FORMULAの応用で出力されています。

では、どのように出力されたか式を見て見ましょう

＃重複行を削除
UNIQUE(

 #CSV1のA列とB列を指定
　　　FILTER('CSV1'!A:B,

＃マッチ関数をARRAYFORMULAで範囲指定できる様にしてCSV1のA列全てがCSV2のA列全てと比較して合致する値だけ出力。
　　　　　　ARRAYFORMULA( MATCH('CSV1'!A:A,'CSV2'!A:A,0)),

＃合致した値をFILTER関数で 25万以上の営業員のみを抽出
　　'CSV2'!B:B >=25) 
)

しっかりと説明しますね。

ネストは基本、分解してから見た方がわかりやすいです。