GoogleスプレッドシートARRAYFORMULA関数の使い方を完全解説!
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関数は式が1つだけだ。下の値は何?手入力?
ARRAYFORMULA関数の下にある○と×は自動的に入力されます。
つまりは1つの式で繰り返し処理を自動的に行ってくれるのがARRAY FORMULAになります。

 

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

 

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

 

ARRAY FORMULA関数は単体では特定の何かの結果を出すと言う事はせず、
指定された事を指定された範囲で行い、結果を出力するのです。

 

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

今回例であげたARRAY FORMULA関数のIF文の記述は下記の通りです。
ARRAYFORMULA(IF(C2:C10 >=20,"○","×"))

 

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

 

 

良い目の付け所です。
ARRAY FORMULA関数は関数を単一では無く範囲で指定する事が出来ます。

 

 

今まで値1つだけ入れてね!って言われていた関数が数多くあると思います。
それらのほとんどはこのARRAY FORMULA関数で範囲指定する事が可能です。
へぇー、便利なんだね。だんだん凄さが分かってきた。
それはとても嬉しいです。
それ以外にもメリットはあるんですよ。

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

 

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

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

 

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

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

 

メンテナンス作業1つにおいても全然作業量、時間共に
断然に違いますので非常に有用です。
確かに数万行のネストされたIF文を全部ちまちまと変えないといけない作業をするより
はるかに効率的だし精神的にも負担が無いよねw
そうそう、そう言った作業を一瞬で終わらせる事が出来て次のタスクに早く取り組める
そして、常時定時ダッシュが出来るというわけなのです。

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をご参照ください。

解答と解説

 

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

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

 

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

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) 
)

 

 

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

 

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

  1. ARRAY FORMULA関数を適用したMATCH関数を使用して営業の照合を行う。
  2. MATCH関数にてCSV1とCSV2に存在する営業を抽出し、FILTER関数で条件に合った人の絞込を行う。
  3. UNIQUE関数でFILTER関数で出た結果からの重複列を削除する。
  4. 出力

 

と言った感じです。

 

UNIQUE関数とFILTER関数に関しましては

別記事にてご案内しております。

 

応用の例文も載せていますので良ければこちらも併せて読んでもらえると

嬉しいです。

 

 

この例だとMATCH関数いらなくね?

ってなったりなんでUNIQUE関数使う意味は?

ってなるんですが、

CSV1とCSV2のデータ量が多ければ多いほどに

効果が増していきます。

 

あとFILTER関数にCSV2にある売上月を6月に設定したら

実際はいらないかもしれないですね。

 

分解していったらしっかりと分かってくるから諦めない事!

 

ARRAY FORMULA関数のショートカットキー

 

冒頭でお伝えしましたGoogle様が独自のショートカットキーを

設定しているとお話しましたね。

 

一応WindowsとMacでちがっているので両方掲載しておきます。

 

セルに=を入力した状態で

OS別ショートカットキー

:Ctrl + Shift + Enter

:Command + Shift + Enter

 

ぜひ使ってみて下さいね。

スプレッドシートでARRAY FORMULA関数と似た配列機能

 

実はGoogleスプレッドシートにはARRAY FORMULA関数に似てはいるんですが、関数と違う固有機能が存在します。

 

それが配列です。

 

配列の使用法

 

配列を使うには中括弧({ })を利用し、囲みます。

 

配列には以下の様な区切りを使用するができます。

, (カンマ)』  :カンマで区切った値を右のセルに表示します。

 

# 例

={10,20,30,40,50}

 

セルで数式を書くと以下の様に記述します。

接頭辞に『 = (イコール)』を忘れないようにしましょう。

 

 

結果は以下の画像の通り、横のセルに表示されます。

 

 

 

; (セミコロン)』:セミコロンで区切った値を下のセルに表示します。

 

# 例

={10;20;30;40;50}

 

セルで数式を書くと以下の様に記述します。

 

 

結果は以下の画像の通り、縦のセルに表示されます。

 

 

また、値に関しては今回は定数を入力していますが、他に四速演算をりようしたり、

SUM関数やRAND関数を使用する事も可能ですので利用の幅はかなり高くなると思われます。

 

 

1次配列

 

配列には種類があります。

横並びに数字が並んでいる状態の配列を1次配列と言います。

 

先ほど説明させて頂きました、カンマで区切った横一列の数字が1次配列とよばれるものになります。

 

 

 

 

2次配列

 

次に2次配列について説明します。

 

2次元というのは縦と横の2つが存在する事で2次元です。

ですので表であらわすと縦と横が入った配列のことを2次元配列といいます。

 

2次元配列はカンマとセミコロンの両方を使用した形となります。

2次元配列の記述方法は以下の様になります。

 

# 例

{{10,20};{30,40};{50,60}}

 

 

この様にして2次元配列を記述すると以下の様な結果になります。

 

 

縦と横、両方に値が出ているのが分かります。

 

しかしここで注意が必要です。

 

2次元配列にする場合、横軸は必ず同じ個数で無いといけません。

そうしない場合はエラーが表示されるようになっています。

 

ですので、どうしても1つ2つ足りなくなる場合はダブルクォーテーションで

値があるかの様にして表示すれば回避できます。

 

 

 

最後に

 

ARRAY FORMULA関数については以上となります。

 

応用はいくつかあるのでまた随時追加していこうと思います。

 

たしか結構面白い使い方があったはずなので探してみます。

 

やっぱりこの関数は説明が長くなりますね。

 

しかし覚えたら確かに素晴らしい関数である事は間違いないので

是非是非チャレンジしてみて下さい。

 

では今日はこの辺で、またご贔屓に。

スポンサーリンク

Twitterでフォローしよう

おすすめの記事