関数のコピペを劇的に減らす関数、arrayformula

Spreadsheet を使っていて、同じ関数をコピペして使うことがよくある。

例えば、 A 列に年組番を入力し、 B 列にその生徒の姓を表示したいときがある。

セル A2 に「1LIA1」と入力したときに、セル B2 に

=vlookup(A2,importrange("元データの ID","データの範囲"),"データ範囲の中での「姓」の列番号",false)

とすることで、元データから 1LIA1 の生徒の姓を拾ってくることができる。この「姓」の列番号を探すのが面倒なときは、

=vlookup(A2,importrange("元データの ID","データの範囲"),match(B1,importrange("元データの ID","\$A\$1:\$1",0),false)

とすることで対応できる。

C 列にその生徒の名を表示したい場合、このままコピペをすると、恐らくエラーが発生する。参照の方法が、相対参照になっている為、そのままコピペをしたときは最初の参照するデータがセル B2 になってしまうのである。

これを防ぐ為に、相対参照にする必要がある。

例えば、前述の例で言うと、右にコピペをするときに、相対参照だとその参照元も右に移動し、絶対参照だとその参照元は動かない、という違いになります。

なので、「年組番」が入力されている A2 の部分は右には動かないで欲しい、ということになるので、右方向はアルファベットで表記しているので A を固定する為に、「\$A2」と変更します。この「\$」が、絶対参照の意味になります。

逆に、「match」の中の B1 は右にズレたときはズレてほしいので「\$」は付けない。

更に下にコピペをするときを考えると、 \$A2 のところは縦には変化して欲しいので \$A\$2 としてはいけない。逆に B1 のところは下に移動してはいけないので、 B\$1 とする必要がある。

よって、後々に右方向、下方向とコピペをすることを考えると、 B2 に入力する式は

=vlookup(\$A2,importrange("元データの ID","データの範囲"),match(B\$1,importrange("元データの ID","\$A\$1:\$1",0),false) 

とするのが良いだろう。

で、ここからが本題。タイトルにもある「arrayformula」の使い方について。

「array」は「配列」、「formula」は「公式」などと訳される。つまり、公式を定義して、それを入れず全体で対応させる、というようなイメージであろうか。

例えば、20 以下の正の整数同士の掛け算の表（小学校で学ぶ九九の拡張）を作る。

この Spreadsheet を見ながら読むと分かりやすいかと。

（シート１）まずは「掛けられる数」と「掛ける数」を並べる。

セル B2 には「=A2*B1」と入力されている。

この数式から、「arrayformula」を使って、一度に表を完成させる。

（シート2）先程の数式を、「arrayformula」で囲う。

セル B2 「=arrayformula(A2*B1)」

これだけでは何も変化がない。

（シート3）この掛け算の、A2 の部分を A2 から A21 までの範囲で行いたいので、

セル B2 「=arrayformula(A2:A21*B1)」

とすると、掛け算の結果が縦に並ぶ。

（シート4）同様に、横方向にも広げたいので、

セル B2「=arrayformula(A2:A21*B1:U1)」

とすると、縦にも横にも広がり、掛け算の表が完成する。

なので、恒例の総理大臣の一覧から、新しい名簿を作成する。

入力してある数式はセル B2 のみで、

=arrayformula(vlookup(A2:A20,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","$A:$D"),match(B1:D1,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","\$A1:\$D1"),0),false))

のみである。

ただ、「代」のところに「37」と入力しようとしたが、間違えて「378」と入力されている。すると、「vlookup」は元データの中の A 列には 378 が見つからない為、エラーが返ってきている。

この場合は直せばいいのだが、例えば複数のクラスで使うデータの場合、人数の多いクラスに合わせてシートを作成し、クラス名だけを変更して使う、なんてことがよくある。このときに、人数の少ないクラスの余ったところは「#N/A」という謎のエラーではなく空白になって欲しい。

そこで使うのが、「iferror」関数である。

「=iferror(数式等,"エラー時の返り値")」で数式を確認し、エラーになる場合は"エラー時の返り値"を返し、エラーでない場合は数式の結果を返すものである。なので、

=arrayformula(iferror(vlookup(A2:A20,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","$A:$D"),match(B1:D1,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","\$A1:\$D1"),0),false),""))

とすると、人がいないところは空白にすることができる。

「arrayformula」を使えるようになると、コピペをしなくて済むし、数式を変更する場合も１箇所直すだけで済むだけでなく、数式処理も軽くなるようである。