【スプレッドシートの便利な使い方】ARRAYFORMULA関数でコピペを減らす
【スプレッドシートの便利な使い方】ARRAYFORMULA関数でコピペを減らす
要約
スプレッドシートで同じ計算を繰り返し行う場合、ARRAYFORMULA関数を使うとコピペを減らすことができます。
EXCELにはないGoogleスプレッドシートの関数です。
使いこなすと、スプレッドシートの作業効率が上がります。
スプレッドシートで同じ関数を繰り返す
スプレッドシートで同じ計算を繰り返し行う場合、通常は１つのセルで計算や関数を作り、それをコピペしていくことになります。
例えば、A列とB列の値を足す場合、=A5+B5という計算式をA5からA14まで適用したい場合、
C5セルに=A5+B5と入力して、C6セルからC14セルまでコピペします。
スプレッドシートのセルをコピーする際は、セルの参照先を自動で変更してくれるため、コピペするだけで簡単に同じ計算を繰り返すことができます。
しかし、コピペを繰り返すと以下のような不便さがあります。
- 計算式を変更する場合、全てのコピペしたセルに再度コピペし直す必要がある
- どこまでが同じ計算式を使っているのかが分かりにくい
- どこかのセルだけ計算式が違っていても気づきにくい
ARRAYFORMULA関数を使う
ARRAYFORMULA関数を使うと、上記の不便さを解消することができます。
この関数は、同じ計算式を複数のセルに適用することができます。
使い方は、ARRAYFORMULAという関数の中に、適用したい計算式を入れるだけです。
その際に、適用する範囲を指定します。
例えば、先程と同様の計算でE列とF列の値を足す場合、G5セルに
=ARRAYFORMULA(E5:E14+F5:F14)という関数を入力すると、
E列とF列を足した結果がG列に一括で表示されます。
ARRAYFORMULA関数を使うポイントとして、以下の4つが挙げられます。
範囲を合わせる
ARRAYFORMULA関数では、１つのセルの中で、複数のセルを:で範囲を決めて入力します。
このとき、各範囲の数を合わせておく必要があります。
上記の例だと、E5:E14とF5:F14はともに10セルを指定して計算しているため、G5:G14に10セル分の結果が表示されます。
上書きされるセルには何も入力しない
ARRAYFORMULA関数で計算結果が表示される範囲には、何も入力しないようにします。
上記の例だと、G6:G14には何も入力しないようにします。
どこか１つのセルにでも入力してしまうと、ARRAYFORMULA関数がエラーになります。
エラーが出た場合は、マウスを合わせるとエラーの内容が表示されるので、それを参考に修正します。
ARRAYFORMULA関数の中に別の関数を入れる
ARRAYFORMULA関数の中には、他の関数を入れることができます。
例えば、ROW()という関数は、その中のセルの行番号を返す関数です。
ARRAYFORMULA(ROW(L5:L35))と入力すると、ROW関数の結果である行番号が表示されます。
範囲を指定して使う関数はARRYFORMULA関数の中でうまく動かない
ARRAYFORMULA関数の中で、範囲を指定して使う関数は、うまく動かないことがあります。
例えば、SUM()関数は、それ自体が範囲指定をする関数ですが、ARRAYFORMULA関数の中で使うと、うまく動きません。
ARRAYFORMULA(SUM(L5:L35))と入力すると、L5:L35の合計値が、L5:L35のセル数分表示されてしまいます。
そのため、範囲を指定しない関数に限定して使うようにします。
これを回避するには、BYROW関数やMAP関数を使うと便利です。
これらの関数はまた別の機会にご紹介します。
ARRAYFORMULAの使用例
下記のスプレッドシートに、ARRAYFORMULA関数を使った関数例を用意しました。
https://docs.google.com/spreadsheets/d/1gyRHe9obHOWCKaaWt8tk-34kMHWEpMODiEp4g2awyDc/edit#gid=0
A〜C列とI〜J列が通常の関数を使った場合の表、E〜G列とL〜M列がARRAYFORMULA関数を使った場合の表です。
どちらも同じ結果が表示されていますが、関数を入力している範囲が違います。
関数を入力しているセルに色をつけていますので、その差を確認してみてください。
まとめ
ARRAYFORMULA関数を使うと、同じ計算を繰り返す際にコピペを減らすことができます。
また、ARRAYFORMULA関数を使うことで、どこまでが同じ計算式を使っているのかが一目で分かるため、ミスを防ぐことができます。
スプレッドシートの作業効率を上げるために、ARRAYFORMULA関数を使いこなしてみてください。
Discussion