Googleスプレッドシートで表などを作成していると、同じ内容の数式を連続コピーしたくなる時が有ります。
Googleスプレッドシートには連続コピーをサポートする機能も有りますが、後日、数式を微調整するとまた数式をコピーし直すのが結構な手間だったりします。
そんな時に役立つのがGoogleスプレッドシート固有関数で有るArrayFormula関数です！
これを使えば、同じ数式をコピーする必要がなく一括で修正も可能となります！
の使い方
単一セル指定の数式を入力したらショートカットキーで、ArrayFormula関数が挿入されるので、単一セル指定を範囲指定に変更！
ショートカットキー
Mac：Command+Shift+Enter
Windows：Ctrl+Shift+Enter
基本的にはこの使い方だけで一気に楽になります！
Googleスプレッドシートとエクセルの関数の違いについて⇗
Googleスプレッドシート専用関数を使った例
ArrayFormula関数⇗GoogleTranslate関数⇗ImportXml関数⇗
エクセルでも可能
Google Apps Script（GAS）との組み合わせ
目次
Excelの上位互換なGoogleスプレッドシート
この見出しの「上位互換」は私個人のイメージです笑
ですが、インストール不要のWeb上で、しかも無料。マクロ作成なども含め、Excelとほぼ同様の機能を持つGoogleスプレッドシートは上位互換と言っても過言ではないと感じています。
そして、GoogleスプレッドシートにはExcelには無い便利関数が存在します！
今回はその中でも最も便利なArrayFormula関数についてです！
ArrayFormula関数とは？
まずはGoogleのヘルプに記載されている内容を引用紹介します。
ARRAYFORMULA
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
使用例
ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
ARRAYFORMULA(A1:C1+A2:C2)構文
ARRAYFORMULA(配列数式) 配列数式 – 1 つの範囲か、1 つのセル範囲または同じサイズの複数範囲を使用する数式か、1 つのセルより大きい結果を返す関数を指定します。
Google
ヘルプの内容はパッと見、理解が難しいですが・・・ここにある使用例を使ったスプレッドシートを作成してみました。
使用例解説
上記のGoogleヘルプに記載されている使用例・・・
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))) =ARRAYFORMULA(A1:C1+A2:C2) を使って解説をします。
２種類の数式を使った使用例が有りますが、個人的にはSUM関数を使った前者の例よりも後者の例の方がArrayFormula関数っぽさを使った使用例だと思います。
サンプルとしてのデータがこちらです。
そして、使用例の数式をI13セルに入れた結果がこちらです。(H13セルはI13セルの数式です。)
サンプルデータだと数式の結果が「40」となります。
IF関数の結果をSUM関数で集計するといった数式になります。
IF関数をわかりやすく各行毎に記載してみました。
このG1〜G10の合計を求めたい数式になりますが、ArrayFormula関数を使用しないと・・・
このようにエラーとなります。
IF関数の結果は単一の値となりますので、配列の引数を必要とするSUM関数ではエラーになるのです。
ですが、ArrayFormula関数を使用するとIF関数の結果も配列化されるのでSUM関数で集計出来るようになります。
サンプルとしてのデータがこちらです。
そして、使用例の数式をF7セルに入れた結果がこちらです。(E7セルはF7セルの数式です。)
サンプルデータだと数式の結果がF7セルに「3」、G7セルに「9」、H7セルに「15」となります。
A列、B列、C列の1行目と2行目の値を足した結果をF列〜H列に算出する数式になります。
同じ結果になるようにArrayFormula関数を使わずに1番シンプルに数式を書くと・・・
こうなります。当然ながらG列、H列それぞれに数式の入力が必要となります。
ですが、F7セルに入力した数式だけでG7セルとH7セルには何も入力しなくてもG7セルとH7セルに結果を出力するのがArrayFormula関数の凄いところです！
数字が展開された後のG7セルを見てみると・・・
「9」とだけ表示されています。
ArrayFormula関数だけではないですが、範囲指定する際に、終わり側（数式の右側）の行や列を省略する事で、その行の最後まで、その列の最後までという指定が出来ます。
例：SUM(A1:A) A列を全て合計する。
開始位置を変更する事で途中から最後まで。という指定も可能です。
例：SUM(A100:A) A列の100から最後までを合計する。
ArrayFormula関数の範囲指定とも相性が良いので、オススメです！
Arrayformula関数のメリット
個人的に最大のメリットとも思えるのは、メンテナンス性が高まる事です。
特にどんどんデータを追加していくデータベースとして使っている場合に計算式の変更が生じた場合、ArrayFormula関数を使っていれば見出しの下の行の数式を変更してあげれば、全ての行に反映させるように出来ます！
数式を入力していくのはコピペやGoogleスプレッドシートの補完機能でそこまで手間ではないですが、変更時の反映の事を考えると出来る限り数式は集約した方が良いですよ！
また、ArrayFormula関数は数式が一括化される事でスプレッドシートの高速化につながると言われています。
Arrayformula関数の注意点
使いこなすとめちゃくちゃ便利なArrayFormula関数ですが、注意が必要な点も有ります。
使用出来ない、上手く動かない関数がある。
数式の文法的にはエラーにはならないのですが、期待した動きをしない関数も多いです。
大抵の場合は引数を指定する関数です。
ArrayFormula関数を使い始めた頃の私の失敗をお伝えします。
サンプルとしてA〜E列にランダムな2桁の数字が入っているデータが有ります。10行目まで各行毎に合計を出したい場合・・・
F2セルのようなSUM関数を入れてF3セル以降にコピペすればOKですがArrayFormula関数を知ったので、早速ArrayFormula関数を使ってみようと・・・
の引数をA2からE10として各行に展開されるかと思ったら・・・
あれ？思ってたのと違う・・・？
よくよく考えてみれば・・・この記述の仕方って・・・
ただのSUM関数の範囲を広げただけでした笑
SUM関数は諦めて・・・
セルの足し算をする数式を作った上で・・・ArrayFormula関数
これで、自動で展開されます！
使えない関数は有りますが、既存関数と上手く組み合わせる事で期待した動きを実現する事は可能です！
エラー：配列結果はxxのデータを上書きするため、展開されませんでした。
ほぼメッセージ通りですが、ArrayFormula関数で自動的に算出されるセルに入力が有ると上記エラーとなります。
このスクリーンショットだと、G6セルに「300」と手入力をした事によりエラーになっています。G6セルの内容を消す事でG2セルのArrayFormula関数の内容が展開出力されて
と表示されます。
展開先に入力が有るとエラーとなる為、ArrayFormula関数が展開される先の値を変更する事ができないのですが、数式を保護するという視点ではメリットとも言えます。
共有管理しているエクセル、スプレッドシートにせっかく入れた数式をいつの間にか他の人が手入力で上書きしてしまい、気づけば数式が壊れている・・・なんて事はよく有りますが、それが完全に防ぐ事が出来ます！
ArrayFormula関数のまとめ
ArrayFormula関数は四則計算やIF関数などの単純な関数やVLOOKUP関数やIMPORTRANGE関数などの参照系の関数と非常に相性が良い関数です。
また、数式が一括化され記述する箇所が減る事にスプレッドシートの高速化と後のメンテナンス性が高まります！
上手く使えばめちゃくちゃ便利です！応用次第で使い方はかなり広がります！
これが有るからこそ、Googleスプレッドシートはエクセルの上位互換とも言えますので、是非使いこなしていきましょう！
1 件のコメント