スプレッドシートの関数を自動コピー!ARRAYFORMULA関数の使い方と活用例8選

2019-10-02

スプレッドシートの関数を自動コピー!ARRAYFORMULA活用例7選
mono
mono
こんにちは、monoです。

スプレッドシートの関数はコピーして貼り付けることで、列全体、行全体などに展開することができます。

でも、関数を逐一コピーして貼り付けるのは手間だし、セル範囲が広くなればスプレッドシートの処理が重くなってパソコンがフリーズすることもあるかもしれません。

当記事では、

スプレッドシートの関数コピーを自動化したい

関数の処理を軽くしたい

という方向けに、

  1. 関数のコピーを自動化する方法
  2. ARRAYFORMULA関数の使い方
  3. ARRAYFORMULA関数の活用例7選

について解説します。

ARRAYFORMULA関数とは?

ARRAYFORMULA関数は「配列関数」とも呼ばれ、配列(ARRAY)を数式(FORMULA)で扱うことのでできる関数です。

例えば、普通だと1セルにつき1セルの参照しかできませんが、ARRAYFORMULA関数だとセルを範囲指定して参照できます。


※A4セルにだけ関数を記述しています。

これだけだとOFFSET関数と似ているように思いますが、ARRAYFORMULA関数は1セルに関数を記述してセル範囲でデータを参照できるだけでなく、範囲内において一括で演算をしたり、範囲内において関数を自動コピーしたりすることが可能です。

つまり、ARRAYFORMULA関数を使うことで、

  • 一括演算:処理が軽くなる
  • 関数自動コピー:関数をコピペする必要がなくなる

などの恩恵を受けることができるのです。

1セルだけ関数を記述すれば1番下の行のセルまで関数が適用できたりするので、データ量が増えたら関数をコピーして足さなきゃ…なんてこともなくなります。

自動コピーが便利!ARRAYFORMULA関数の活用例8選

それでは、ARRAYFORMULA関数の恩恵を最大限享受するための活用例を8つご紹介します。

どれも今日から使える実用的なものばかりなので、早速パクって使ってみてください。

四則演算を自動コピー

普通なら、

=A2+B2
=A3+B3
 ︙
=A4+B4

と関数をコピーしなければいけない場合でも、ARRAYFORMULA関数なら一発で全範囲を計算することができます。

四則演算をARRAYFORMULAで使うときの計算式は下記です。

=ARRAYFORMULA(セル範囲A [演算子1] セル範囲B [演算子2] セル範囲C …)

定数を計算式に含めたい場合は、絶対参照でセル範囲を指定するか、数字をそのまま入力すれば計算してくれます。

こんな感じですね。

=ARRAYFORMULA(セル範囲A [演算子1] $B$1 [演算子2] 10

[演算子]の部分には、以下のように計算式に応じた演算子を入力してあげてください。

足し算の活用例

足し算:+(プラス)

引き算の活用例

引き算:-(マイナス)

掛け算の活用例

掛け算:*(アスタリスク)

割り算の活用例

割り算:/(スラッシュ)


※0で割っているためエラー(#DIV/0!)となっています。これの解決方法については、後述のIFERROR関数の節で解説します。

IF関数を自動コピー

=ARRAYFORMULA(IF(条件式,真のときの値,偽のときの値))

ARRAYFORMULA関数でIF関数を自動コピーする方法について解説します。

IF関数は「論理式がTRUEの場合はある値を返し、FALSEの場合は別の値を返す関数」です。

以下のように記述することで、条件式に基づいて値を出し分けることができます。

データがないときは空白、データによって値を変えたいときに、IF×ARRAYFORMULA関数はおすすめです。

IF関数の活用例

IFERROR関数を自動コピー

=ARRAYFORMULA(IFERROR(範囲,値))

ARRAYFORMULA関数でIFERROR関数を自動コピーする方法について解説します。

IFERROR関数は「エラー値でない場合は1番目の引数を返し、エラー値である場合は2番目の引数を返す(指定していない場合は空白)関数」です。

以下のように記述することで、IFERROR関数をコピーしなくても、範囲内で自動コピーされて使うことができます。

先ほどARRAYFORMULA関数で割り算をしたときに発生しましたが、0で除算をすると、エラーが発生します。

「#DIV/0!」や「#REF!」などのエラーが表示されていると正直かなりダサいので、除算をするときにはセットでIFERROR関数も使うことをおすすめします。

IFERROR関数の活用例

ROW関数を自動コピー

=ARRAYFORMULA(ROW(範囲))

ARRAYFORMULA関数でROW関数を自動コピーする方法について解説します。

ROW関数(COLUMN関数)は「指定したセルの行番号(列番号)を返す関数」です。

シートの左端に行番号打ちたいときありますよね。

以下のようにARRAYFORMULA関数を1セルだけ記述すれば、その願い、叶います。

ROW関数の活用例

MOD関数を自動コピー

=ARRAYFORMULA(MOD(範囲,除数))

ARRAYFORMULA関数でMOD関数を自動コピーする方法について解説します。

MOD関数は「除算の剰余(割り算の余り)を返す関数」です。

奇数行だけフラグを立てたい、任意の数ごとにフラグを立てたいというときにMOD×ARRAYFORMULA関数はおすすめです。

MOD関数の活用例

VLOOKUP関数を自動コピー

=ARRAYFORMULA(VLOOKUP(検索値, 範囲, 指数, 並べ替え済み))

ARRAYFORMULA関数でVLOOK関数を自動コピーする方法について解説します。

VLOOKUP関数は「垂直方向の検索関数」です。

別のセル範囲をマスタを作成して、VLOOKUP × ARRAYFORMULA関数を使えばかなり直感的に業務効率を上げることができます。

ちなみにVLOOKUP関数とよく同じような使い方をされる関数でINDEX×MATCH関数があります。

しかし残念ながら、ARRAYFORMULA関数でINDEX × MATCH関数は使えません(使えない理由については下記の記事で解説しています)。

ARRAYFORMULA関数とINDEX・MATCH関数を組み合わせるには?

なので、おとなしくVLOOKUP関数で代用しましょう。

ARRAYFORMULA関数とVLOOKUP関数の組み合わせ方【コピペでOK】

VLOOKUP関数の活用例

COUNTIFS関数を自動コピー(検索値変動)

=ARRAYFORMULA(COUNTIFS(範囲, 検索値))

検索値が変動する場合において、ARRAYFORMULA関数でCOUNTIFS関数を自動コピーする方法について解説します。

COUNTIFS関数は「複数条件が成り立つ範囲の個数を返す関数」です。

列全体にコピーする場面は非常に多いのではないでしょうか。そして、スプレッドシートが重くなる原因としても有名な関数です。

COUNTIFSをそのままコピーして使うと「コピペが大変」「処理が重い」などデメリットが発生します。

COUNTIFSをコピペして使うときは、COUNTIFS × ARAAYFORMULA関数でスマートに列全体をカウントしましょう。

COUNTIFS関数の活用例

FIND関数を自動コピー(検索値不変)

=ARRAYFORMULA(IF(IFERROR(FIND(検索文字,セル範囲),0)>0,1,0))

検索値が変動する場合において、ARRAYFORMULA関数でCOUNTIFS関数を使うことはできないので、代わりにFIND関数を使います。

以下に、「りんご」という文字列を含むときは1、含まないというは0を返す動作例を載せておくので、参考にしてみてください。

 command=ARRAYFORMULA(IF(IFERROR(FIND("りんご",A:A),0)>0,1,0))

検索値が不変の場合、ARRAYFORMULA関数とCOUNTIFS関数が使えない理由や、複数条件の場合にFIND関数を使う方法については下記の記事で解説しています。

ARRAYFORMULAとCOUNTIFS関数は使えない?代わりにFIND関数を使おう!

まとめ

今回はGoogleオリジナルの関数で、スプレッドシートの関数を自動コピーできる「ARRAYFORMULA関数」を紹介しました。

ARRAYFORMULA関数は少しクセのある関数ですが、使いこなせれば作業を効率化できるだけでなく、スプレッドシートの処理負荷も軽減できます。

習得する価値が大いにある関数なので、積極的に活用していきましょう。

では、また。

【保存版】スプレッドシート関数一覧!業務が爆速化する関数33選

【2019年】スプレッドシートの勉強におすすめの本7選【網羅しました】

  • この記事を書いた人

mono

26歳♂️関東在住のサイドFIREを目指すブロガー/座右の銘は「知識は誰にも奪われない」/ゆるく稼いで楽しく暮らすライフハック情報をお届けします/アイコンは黎(クロイ)さん。

スキルを磨く スプレッドシート

ARRAYFORMULA スプレッドシート関数

© 2021 monoblog