ARRAYFORMULA関数の使い方｜1つの式を全部の行に広げる
※写真はイメージです
書き方
=ARRAYFORMULA(配列数式)1つのセルに入れた式を、範囲の全部の行に広げて計算します。行が増えても式をコピーし直さなくていいので、フォームの回答のように毎日行が増える表で特に便利です。
引数の意味
| 引数 | 入れるもの | 図の例 |
|---|---|---|
| 配列数式 | 範囲どうしの計算や、範囲を入れた関数 | B2:B4*C2:C4 |
式を入れるときに Ctrl＋Shift＋Enter（Macは ⌘＋Shift＋Enter）を押すと、自動で ARRAYFORMULA( ) で囲んでくれます。
行が増えても自動で計算する（列全体を指定）
=ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))範囲を B2:B のように下まで全部にすると、あとから足した行も計算されます。そのままだと空の行に 0 が並ぶので、IFで「A列が空なら空」にするのが定番の書き方です。
VLOOKUPを全部の行にまとめてかける
=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, 商品!A:B, 2, FALSE)))検索値を範囲（A2:A）にすると、VLOOKUPも1つの式で全部の行に入ります。行ごとに式をコピーしなくていいので、だれかが式を消してしまう事故も減ります。
見出しも式の中に入れる
=ARRAYFORMULA(IF(ROW(A:A)=1, "金額", IF(A:A="", "", B:B*C:C)))1行目に見出し、2行目から計算、を1つの式で書けます。Googleフォームの回答シートのように、行が上から足されていく表で使います。
うまくいかないとき
1#REF!「配列結果は…データを上書きするため展開されませんでした」
式が広がる先のセルに、何か入っています（スペース1つでも）。
直し方広がる先のセルを空にします。
2空の行に 0 や #N/A が並ぶ
列全体を指定したので、空の行も計算しています。
直し方IF(A2:A="", "", …) で囲みます。
3SUMIFなどが全部の行に広がらない
SUM・AND・ORなど、範囲を1つの答えにまとめる関数は広がりません。
直し方SUMIFは条件を範囲にすれば広がります。ANDは (条件1)*(条件2)、ORは (条件1)+(条件2) に書きかえます。
使えるバージョン
Googleスプレッドシートだけの関数です。Excelにはありません。
Excelでは
Excel（Microsoft 365・2021以降）では、ARRAYFORMULAで囲まなくても =B2:B4*C2:C4 と書くだけで下に広がります（スピル）。
よくある質問
ARRAYFORMULAとは？
1つのセルに入れた式を、範囲の全部の行に広げて計算するGoogleスプレッドシートの関数です。
ショートカットはありますか？
式を入れるときにCtrl＋Shift＋Enterを押すと、自動でARRAYFORMULAで囲まれます。
空の行に0が出ます
IF(A2:A="","",式) のように、空の行は空にする条件で囲みます。
#REF!になります
式が広がる先のセルに何か入っています。空にすると広がります。
一緒に覚えたい関数・操作
このページについて
操作の説明は、パソコンのブラウザで開いた日本語のGoogleスプレッドシートに合わせています。関数の働きはGoogleの公式ヘルプ（スプレッドシートの関数リスト）で確かめています。図はこのサイトで作ったもので、表の会社名・商品名・人名・URLは例です。このサイトはGoogleとは関係ありません。運営者情報