スプレッドシートで「1つの式を範囲全体に一気に適用したい」ときに使うのがARRAYFORMULA関数です。草野球の成績管理表で、全選手の打率を1つの式でまとめて計算する場面を例に、使い方と内部のしくみを解説します。
ARRAYFORMULA関数とは
本来は1つのセルにしか結果を返さない計算式を、指定した範囲全体にまとめて適用し、複数セル分の結果を一度に返す関数です。成績管理表では「全選手の打率」「全試合の防御率」など、同じ計算を行方向にずらっと並べたい場面で活躍します。
基本の書式はこうです。
=ARRAYFORMULA(計算式)
カッコの中に入れる「計算式」の部分には、単一セルではなく「D2:D10」のようなセル範囲を使った計算式をそのまま書きます。
成績管理表での実例:全選手の打率を1つの式で出す
大阪グーニーズの個人成績シートに、選手ごとの通算打数・通算安打が並んでいるとします。C列が打数、D列が安打、E列に打率を表示したい場合を考えてみましょう。
| 選手名 | 打数 | 安打 | 打率 |
|---|---|---|---|
| SUZUKI | 40 | 14 | 0.35 |
| NAKATA | 36 | 9 | 0.25 |
| YAMAMOTO | 30 | 12 | 0.4 |
| … | … | … | … |
この打率(D列÷C列)を全選手ぶん一度に計算したいとき、式はこうなります。
=ARRAYFORMULA(D2:D10/C2:C10)
これをE2セルにだけ入力すると、E3・E4…E10まで、行ごとの「安打÷打数」の結果が自動的に一括で表示されます。1行ずつ式を入力したり、下にコピーしたりする必要がありません。
打率の一覧を表示したい範囲の一番上のセル(例:E2)をクリックして選択します。ARRAYFORMULAは、この1セルに式を入れるだけで下の行にも結果が展開されます。
カッコの中には「D2:D10/C2:C10」のように、単一セルではなく範囲どうしの計算式をそのまま書きます。ここが通常の数式との一番の違いです。
式を確定すると、入力したセルだけでなく、範囲に含まれる行すべてに一気に結果が表示されます。
しくみを理解する:ARRAYFORMULAは何をしているのか
通常の数式は、1つのセルに対して1回だけ計算を行います。「D2:D10まで同じ計算をしたい」というときに、これまでは1行目の式を作ってから下の行へコピー(オートフィル)することで、同じ処理を1行ずつ繰り返させていました。
ARRAYFORMULAは、参照した範囲をひとまとまりの「配列」として扱い、その配列に含まれる要素(D2とC2、D3とC3…)ひとつひとつに、カッコの中の計算式を自動的に当てはめていきます。1つの式でありながら、内部では行の数だけ同じ計算が実行されている、というイメージです。
よくあるミス:結果が正しく表示されない原因
「D2:D10」と「C2:C9」のように、割り算の左右で範囲の行数がずれていると、サイズが合わないというエラーになり結果が返ってきません。ARRAYFORMULAの中で範囲どうしを組み合わせるときは、行数を必ず揃えるようにしてください。
ARRAYFORMULAで展開された結果は、E2だけでなくE3以降のセルにも自動で表示されていますが、実体の式が入っているのはE2だけです。E5などに別の値をうっかり手入力すると、そこだけ配列の展開が止まってしまい、以降の行が空白になることがあります。値を直接入力せず、必要な修正は元のD列・C列側で行うようにしましょう。
- ARRAYFORMULA関数は「1つの式を範囲全体に一括適用する」関数
- 先頭のセルに式を1つ入れるだけで、下の行にも自動で結果が展開される(コピー不要)
- 組み合わせる範囲どうしの行数を揃えることと、展開範囲を手入力で上書きしないことが使いこなしの2大ポイント
次回予告
次回のテーマは「QUERY関数」です。SQL風の構文でデータを抽出・集計する仕組みを、条件に合う成績だけを別シートに抽出表示する例で解説していきます。
▶︎ 次回：QUERY関数の使い方としくみ
コメント