Galapagos Tech Blog

株式会社ガラパゴスのメンバーによる技術ブログです。

GoogleスプレッドシートのArrayFormula関数を使って、1つの式で複数のセルに値を反映させる

こんにちは、最近Googleスプレッドシートの関数の勉強をしているテストチームのあべです。
今回は、GoogleスプレッドシートのArrayFormula関数をご紹介したいと思います。

ArrayFormulaとは

Googleのドキュメントエディタヘルプでは、以下のように書かれています。

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。

(配列数式:複数のセルを対象に、1つの数式を作成する式)
簡単に言えば、1つの式で複数のセルに値を反映させることができるということです。
繰り返しの式を、この関数で置き換えることができます。

(ちなみにArrayFormulaはExcelにはありません。Googleスプレッドシートのみの関数です。)

ArrayFormulaは以下のメリットがあります。

  • 1つの式を書き換えれば対象範囲全てに反映される
    →1つの式を書き換えてから対象範囲にオートフィルで反映。。。という手間が省けます。また、セルを追加しても、対象範囲に追加したセルが指定されていれば自動的に反映されます。
  • 動作が早くなる
    →ArrayFormulaは指定範囲のセル1つ1つで式を実行するのではなく、値のみを表示します。そのため、1つ1つのセルに式を入力するよりも、動作が早くなります。

手順

今回は、私が勉強として作成したガントチャートで説明します。
ArrayFormula以外の関数の詳しい説明は省きます。ここで使っている関数のほとんどは、過去記事*1に書いてありますので、ご興味のある方は読んでみてください。

作成したガントチャートは以下です。
f:id:glpgsinc:20180703120823p:plain
開始日と終了日に日付を入力すると、期間内の稼働日のセル色が緑になるよう、関数を入れています。
このシートで使っている繰り返しの式を、ArrayFormulaに置き換えていきます。

1. 指定範囲が単一行/列のとき

今回は、ガントチャートの上部、曜日を表示している列をArrayFormulaに置き換えます。

変更前の曜日行(G5以降5行目)には、セル1つ1つに以下の式が入っていました。

=TEXT(G1,"ddd")

同列1行目の日付から曜日のみを表示しています。

f:id:glpgsinc:20180703162839p:plain

このままでも大きな問題はありません。ですが、列を増やすたびに増やした列に同じ式を入れていくのは手間がかかります。その手間を解消するために、曜日行の1列目のセル(G5)を以下に書き換えます。

=ArrayFormula(TEXT(G1:1,"ddd"))

まず、もとの式をArrayFormulaで囲みます。次に、単一セルだった指定範囲を、対応させたい範囲セルに変更します。
今回は、曜日行のセル全体に曜日が表示されて欲しいので、G列以降の1行目を指定しています。

f:id:glpgsinc:20180703162704p:plain

G5 に式を入力するだけで、5行目のG列以降に曜日を表示させることができました!

ちなみに、となりのセル H6 には式が入力されておらず、値のみが表示されています。 f:id:glpgsinc:20180703174640p:plain

2. 指定範囲が複数行/列のとき

ガントチャートのカレンダー部分(下図参照)にも、セル1つ1つに以下の式をいれていました。

=IF(($D6<=G$1)*(G$1<=$E6)*(WEEKDAY(G$1,2)<6)*(G$2=""),1,"")

同一行の開始日と終了日の期間内で、かつ、稼働日のみに「1」が表示されます。
その後、セルが「1」のときはセル色が緑になるよう条件付き書式で設定しています。

f:id:glpgsinc:20180703163159p:plain

1.の曜日のときは簡単な式だったので、動作に問題はありませんでした。ですが、今回のように多少複雑な式の場合、セル1つ1つにこの式が入っていると、動作が遅くなることがあります。 またこちらも、列や行を追加したとき、増やしたセルに同じ式を入力しなければなりません。曜日のような単一行/列のときはそんなに手間ではないですが、今回のように広範囲の場合は、かなり手間がかかります。

上記の問題を解消するため、カレンダー部分の起点のセル(G6)に以下の式を入力します。

=ArrayFormula(IF(($D6:$D<=G$1:$1)*(G$1:$1<=$E6:E)*(WEEKDAY(G$1:$1,2)<6)*(G$2:$2=""),1,""))

基本的には1.と同様に、もとの式をArrayFormulaで囲み、単一セルの指定範囲を複数セルに変更します。
このとき、指定範囲に誤りがないよう、注意して変更してください。

例えば、今回の式の場合、開始日と終了日を示す $D6 と $E6 は、$D6:$D / $E6:$E と列を指定しています。
また、日付を示す G$1 は G$1:$1 と行を指定しています。
同じ処理をした値が返されるよう、指定範囲を設定する必要があります。

f:id:glpgsinc:20180703170649p:plain

G6 に式を入力するだけで、開始日〜終了日の期間内のセル色が変更されるようになりました!

ショートカットキー

上のように便利なArrayFormulaですが、ショートカットキーで簡単に入力することができます。

mac : ⌘ + Shift + Enter  
Windows : Ctrl + Shift + Enter  

名前自体が長いので、ショートカットキーで一発入力できるのはとてもありがたいです!

ビフォーアフター

変更前、シートの状態は下図のような状態でした。
G列以降のすべてのセルに式が入っていることがわかります。
(一部式が表示されていない箇所がありますが、実際はすべてのセルに式が入っています。)

f:id:glpgsinc:20180704102206p:plain

色枠で囲ってある部分を、手順1.2.の要領でArrayFormulaに置き換えると...

f:id:glpgsinc:20180704103046p:plain

それぞれの起点のセルに式を入れるだけで、変更前と同じ処理が実行されました!

さいごに

今回は、ArrayFormula関数で、1つの式で複数のセルに値を反映させる方法をご紹介しました。
関数を学ぶと、複雑な式を複数のセルに大量に入力してしまいがちです。そうなると動作が遅くなったり、編集に手間がかかったり等の問題が発生します。(私が実際にそうなりました)
そんな問題も、ArrayFormulaを使えば解決できますので、ぜひ使ってみてください。

ここまで読んでいただき、ありがとうございました!

ところで

弊社ではエンジニアを募集しています。ご興味をお持ちのかたはぜひ弊社採用ページをご覧ください。

www.glpgs.com