こんにちは！
おでぃーです。
初回の投稿ということで、私がスプレッドシートを使い始めて大きな衝撃を受けたArrayformula関数を紹介します。
スプレッドシートの行すべてに対して同じ処理をしたいとき、オートフィルなどで関数を下の行までコピペすることで対応している方も多いのではないでしょうか？
しかしそうすると、、
- 関数を変更したときに再度コピペ作業が発生する
- 関数が入力されているセルが多くなるためブック全体の処理が重くなる
などといった面倒な対応が生じてしまいますよね。
そんなときにこのArrayformula関数の出番です！
前半では、使い方や使用例を解決していきます。
後半では、Arrayformulaを擦りまくった経験から得た応用テクニックを紹介していきます！
Arrayformula関数とは
Arrayformula関数は、Googleスプレッドシートの関数の1つで、配列を操作する関数です。
この関数を使うと、複数のセルに対して一度に操作を行うことができ、手軽に大量のデータを処理することができます。
同じ表計算ツールであるMicrosoft Excelには存在しません。
Arrayformula関数の使い方
Arrayformula関数の基本的な使い方は、以下のようになります。
=Arrayformula(関数(範囲))
このように記述することで、範囲内のすべてのセルに対して、指定した関数を適用することができます。
ちなみに、ショートカットが存在します。
数式を記載している途中で「ctrl」+「shift」+「Enter」を押すと、数式の先頭にArrayformulaを付与することができます。
以上が基本的な記述方法になりますが、これだけではあまりピンとこない方もいると思うので、具体的な使用例を見ていきましょう！
使用例
例として、上記のような生徒のテストのサンプルデータを使っていきます。
A~C列にはそれぞれ学年・クラス・番号が、D~F列にはそれぞれ国語・数学・英語の点数が記載されています。
各生徒の3科目の合計点数を算出したいとします。
通常であれば、G列の最初のセルに「=D2+E2+F2」 と入力した後に、下の行にオートフィルをすることで列全体に数式を適用すると思います。
しかし、 Arrayformula関数を使うことで、G2セルに一度だけ数式を入力すれば、列全体に自動的に数式が適用されます。
この例では下記のような数式を記述しています。
=ArrayFormula((IF(A2:A=””,””,D2:D+E2:E+F2:F)))
セル参照を範囲で指定する！
注意点
Arrayformula関数では一部の関数を併用することができません。
しかし、他の関数を組み合わせることで同じ機能を実現することは可能だったりするので、そのテクニックについては後半のほうで解説していきます。
集計関数は使用できない
- SUM関数
- AVERAGE関数
- MIN関数
- MAX関数
- COUNT関数
といった集計関数は上手く機能しません。
なぜなら、Arrayformula関数と集計関数どちらも範囲を指定することになりますが、それぞれの範囲の用途が意味が異なっているからです。
IFS系関数は使用できない
- IFS関数
- SUMIFS関数
- AVERAGEIFS関数
- MINIFS関数
- MAXIFS関数
といったIFS系関数は上手く機能しません。
単一の条件を指定するIF系関数であれば問題ないのですが、複数条件になるとダメなようです。
応用テクニック
ここからはArrayformula関数を使用する際のテクニックや、他の関数との組み合わせ使用例を紹介していきます！
範囲の最終行は指定しない
先ほどの数式、「=ArrayFormula((IF(A2:A=””,””,D2:D+E2:E+F2:F)))」について、
サンプルデータでは28行目までしか使っていないため、「D2:D28」としたほうが無駄がなくてよさそうに思えます。
しかし実際の利用シーンではどうでしょうか。
もちろん行数が固定されている場合もあるかと思いますが、列全体に数式を適用するようなシーンではデータが増減する、すなわち行数が変動するケースがあります。
そのようなケースのときに範囲をきっちり指定していては、行数が変動するたびに数式を更新しなければなりません。
一方で、最終行を指定せずに「D2:D」とすれば、これ以降は更新の必要はありません。
「もし空欄ならば空欄にする」という条件をつける
Arrayformula関数は指定した行すべてに適用されるため、参照セルが空白だったとしても適用されてしまいます。
そのため、「=ArrayFormula(D2:D+E2:E+F2:F)」と記述すると、空白行であるG29以降のセルすべてに計算結果である0が出力されてしまいます。
1列だけならまだよいですが、何列にもArrayformula関数を使えばスプレッドシートが重くなってしまいます。
そこで、「IF(A2:A=””,””,」と記述して、「A列が空白であるならば空白を出力する」処理をすることによって無駄な負担を削減させることができます。
先ほどの数式、「=ArrayFormula((IF(A2:A=””,””,D2:D+E2:E+F2:F)))」においても、同じように記述しています。
ちなみに「IF(ISBLANK(A2:A),””,」でも同じようなことができますが、この場合はA2:Aが空白に見えたとしても関数が入っていたら出力されてしまいます。
そのため、前者にしておいたほうが想定外の出力は防げると考えています。
文字列を列全体に適用する
同じ文字列を列全体に適用させたいときにも有効です。
例えば、I列に同じ学校名：「スプシ高校」を入力したいときは下記のように記述します。
=ARRAYFORMULA(IF(A2:A=””,””,”スプシ高校”))
文字列だけなら、「=ARRAYFORMULA(“スプシ高校”)」でいいんじゃないの？
と思った方もいるかもしれません。
Arrayformula関数はあくまで数式に対して適応させる関数なので、文字列単体には機能しません。
そこで、「もし空欄ならば空欄にする」という条件を組み込み数式化させることで、文字列を列全体に適用させることを実現させることができます。
IF関数の条件を複数指定する
IF関数で複数の条件を指定したいとき、通常であればAND関数やOR関数を使うと思います。
しかし、Arrayformula関数内ではそれらの関数が機能しません。
そんなとき、AND関数の代わりに「*」、OR関数の代わりに「+」を使用します。
例えば、文字列を列全体に適用する例で、「A列が空欄じゃないかつB列が空欄じゃないとき、”スプシ高校”と表示させたい」ときには下記のように記述します。
=ARRAYFORMULA(IF((A2:A=””)*(B2:B=””),””,”スプシ高校”))
一方で、「A列が空欄じゃないまたはB列が空欄じゃないとき、”スプシ高校”と表示させたい」ときには下記のように記述します。
=ARRAYFORMULA(IF((A2:A=””)+(B2:B=””),””,”スプシ高校”))
条件に一致するデータをフィルタリングする
データの中から、条件に一致するデータだけを抽出して出力させることができます。
Query関数やFilter関数のような使い方ですね。
例えば、先ほどのデータから学年が1の行のみ抽出したいとします。
新たにシート2を作成し、A2セルに下記のような数式を記述すると、学年が1の行のみ出力させることができます。
=ARRAYFORMULA(IF(‘シート1′!A2:A=1,’シート1’!A2:F,””))
IF関数を使い、「シート1の学年列が1であれば、シート1の学年~英語列を出力する」という指示を数式で記述しています。
前項のテクニックを使えば、１つの条件だけでなく、複数の条件を指定することもできます。
例えば、学年が1かつクラスが1の行を抽出したいときは、
「=ARRAYFORMULA(IF((‘シート1’!A2:A=1)*(‘シート1′!B2:B=1),’シート1’!A2:F,””))」
と記述すればOKです。
ちなみに、「=IF(‘シート1′!A2=1,’シート1’!A2:F2,””)」と1行分だけを記述しても、B列以降の内容は出力されません。
つまり、Arrayformula関数には複数列を制御できる機能も備わっているわけですね。
SUM関数を疑似的に実現する
Arrayformula関数内では、集計関数が上手く機能しません。
しかし、だからといってオートフィルする必要はありません！
他の関数を駆使することで回り道して実現させることができます。
最初の使用例では、「=ArrayFormula((IF(A2:A=””,””,D2:D+E2:E+F2:F)))」という数式がありました。
これをSUM関数を用いて「=ArrayFormula((IF(A2:A=””,””,SUM(D2:D:F2:F)))」とすると機能しません。
そんなときは下記のように記述します。
=ArrayFormula((IF(A2:A=””,””,MMULT(N(D2:F),N(TRANSPOSE(COLUMN(D2:F)^0))))))
関数がやや複雑なのでここでの解説は割愛しますが、D2:Fのように合計したい範囲をまるごと指定することで、SUM関数の機能を作り出すことができます。
SUMIFS関数を疑似的に実現する
Arrayformula関数内では、SUMIFS関数が上手く機能しません。
具体的には、複数の条件を指定する場合です。
１つの条件を指定するSUMIF関数は基本的に問題なく機能します。
例えば、”国語が50点以上の生徒の3教科合計点数”を合計したものを”学年ごと”に算出したい場合を考えます。
通常どおり下記のように記述すると、B列すべてに対してB2セルの出力結果が反映されてしまいます。
そこで、Vlookup関数とQuery関数を組み合わせて下記のように記述すると、SUMIFS関数と同じことを実現させることができます。
=ArrayFormula(IF(A2:A=””,””,Vlookup(A2:A,query(‘シート1’!A2:G,”select A,sum(G) where D >= 50 group by A”),2,0)))
この数式では、学年を1列目、”国語が50点以上の生徒の3教科合計点数”の合計したものを2列目にもつようなデータをQuery関数で作り出し、それに対してVookup関数で学年を検索キーとして合計を取り出す処理をしています。
複雑な数式にはなってしまいますが、慣れれば意外と直観的に組めるようになるはずです！
まとめ
今回は、Arrayformulaの使い方と応用テクニックについて解説しました。
使いこなせれば、スプレッドシートでの作業効率が格段に上がること間違いなしです！
何か新しいテクニックが見つかれば随時更新していきます。
ぜひマスターして役立ててみてくださいね！
コメント