Googleスプレッドシート初心者脱出！配列数式でスマートに計算しよう

最近業務では Excel はあまり使わず、Google スプレッドシートをメインで使っています。
クラウドに保存され、OS に依存せず、ブラウザが動けばどこでも使えて便利です。
まだ Excel に劣る部分もありますが、基本的なことは問題なくできます。
また、Google スプレッドシートにしかできないことも多々あり、もう手放せないサービスになっています。

今回は、『配列数式』をテーマに、スマートな計算・集計をケーススタディで説明します。
以前、SUMPRODUCT関数 についての記事も書きましたので、そちらもよろしければご覧ください。
今回のサンプルは全て以下のシートから見ることができます。
配列数式を使ったサンプル

Excel にもあるのですが、『配列数式』と呼ばれる、少し変わった計算方法があります。
配列（複数のセル）を対象に、１つの数式を使って、処理するといった意味と考えてください。
Google スプレッドシートで配列数式を使用する場合は、ARRAYFORMULA関数 を使用します。

ARRAYFORMULA (配列数式)

Google公式のARRAYFORMULA関数 ヘルプ
構文はこんな感じです。

ここまでの説明だと難しそうで、敬遠してしまいそうですが、例を見て理解していきましょう！

このサンプルでは、ARRAYFORMULA関数 を使って、配列同士の和を求めています。

=ARRAYFORMULA(A2:A4+B2:B4)

D2 に上記の式が入っています。
特徴的なのは、１つのセルにしか式を入れていないのに、別のセルに結果が出力されます。
「 A2 + B2 」「 A3 + B3 」「 A4 + B4 」の計算がそれぞれ実行されて、各セルに表示されるわけです。

=ARRAYFORMULA(A2:A4*B2:B4)

D2 の上記の式で、配列同士の積を求めています。
足し算とあまり変わらないですね。

このように、範囲を指定すると、順番に数式を計算して、結果を出力してくれます。

=ARRAYFORMULA(SUM(C2:C8*D2:D8))

G2 の上記の式で、「合計売り上げ」を求めています。
配列数式での積の求め方の応用で、合計を求めるSUM関数を使っているのがポイントです。

=ARRAYFORMULA(SUM((B2:B8="バナナ")*C2:C8*D2:D8))

G3 の上記の式で、「バナナの売り上げ」を求めています。
ここでは「B2:B8=”バナナ”」という条件式を追加して、バナナだけを抽出するようにしています。

=ARRAYFORMULA(SUM((A2:A8=DATE(2017,2,10))*C2:C8*D2:D8))

G4 の上記の式で、「2017/02/10の売り上げ」を求めています。
条件が日付でもできます。

=ARRAYFORMULA(SUM((A2:A8=DATE(2017,2,10))*(B2:B8="バナナ")*C2:C8*D2:D8))

G5 の上記の式で、「2017/02/10のバナナの売り上げ」を求めています。
複数条件の合計でも問題なくできます。

上から見ていけば、どのように計算式を組み立てているかわかりやすいと思います。

次は別のケースで、複数セルに対してAND条件・OR条件を判定するケースです。

=ARRAYFORMULA(AND(A2:A6=B2:B6))

E2 の上記の式で、「項目1と項目2が全て同じか」を判定しています。
ARRAYFORMULA関数 を使わない場合は、１つずつ判定していかなければなりませんが、ARRAYFORMULA関数 を使えば一度に全項目の判定が可能です。

=ARRAYFORMULA(OR(A2:A6=B2:B6))

E3の上記の式で、「項目1と項目2で１つでも同じ項目が存在するか」を判定しています。

=ARRAYFORMULA(MAX(C2:C8*D2:D8))

G2 の上記の式で、「１日の最高売り上げ」を求めています。
この MAX関数 では、配列内の一番大きな数の項目を抽出してくれます。

=ARRAYFORMULA(MAX(A2:A8*(MAX(C2:C8*D2:D8)=C2:C8*D2:D8)))

G3の上記の式で、「１日の最高売り上げの日」を求めています。
ここでは「MAX(C2:C8D2:D8)=C2:C8D2:D8」で、最大値かどうかを判定する配列を作っています。
この配列を日付列「 A2:A8 」の配列を掛け算します。
日付はスプレッドシートの内部的にはシリアル値という単なる数値で扱われています。
そのため、最大値の日付以外は、シリアル値が 0 となる配列が作られます。
その配列に MAX関数 を使うことで、有効な日付のみを抽出することができます。

参考に最低売り上げのサンプルもあるので見てみてください。

=ARRAYFORMULA(COUNT(IF(C2:C8*D2:D8>C5*D5,1)))+1

G6 の上記の式で、「2017/02/10のりんごの売り上げ順位」を求めています。
「IF(C2:C8D2:D8>C5D5,1)」の部分で、2017/02/10のりんごの売り上げを超えるかどうかの判定の配列を作ります。
COUNT関数 では判定結果でデータが出力された件数を求めています。
対象の項目の売り上げを上回る項目の件数を求め、それに1を足すことで、順位を出してます。

=ARRAYFORMULA(COUNT(IF(1000<=C2:C8*D2:D8,1)))

G7の上記の式で、「1000円以上売り上げたデータ件数」を求めています。

=ARRAYFORMULA(COUNT(UNIQUE(IF(1000<=C2:C8*D2:D8,A2:A8))))

G8の上記の式で、「1000円以上売り上げた日数」を求めています。
これは、「1000円以上売り上げたデータ件数」の応用ですが、UNIQUE関数 という重複を省ける関数を使用して、日付の重複を除いてから件数をカウントしています。

最後に文字列の連結するサンプルです。

=CONCATENATE(ARRAYFORMULA(B2:B8))

B10 の上記の式で、「都道府県を連結」を求めています。
CONCATENATE関数 とは、文字列を連結できる関数です。

=CONCATENATE(ARRAYFORMULA(IF(A2:A8="府",B2:B8,"")))

B11 の上記の式で、「府だけを連結」を求めています。
条件付きでの文字列連結もできます。

=LEFT(CONCATENATE(ARRAYFORMULA(B2:B8 & ",")),LEN(CONCATENATE(ARRAYFORMULA(B2:B8 & ",")))-1)

B12 の上記の式で、「都道府県をカンマ区切りで連結」を求めています。
「 & 」という記号では、文字列同士を繋げることができます。
「CONCATENATE(ARRAYFORMULA(B2:B8 & ","))」で、カンマを後ろにつけて、文字列を連結しています。
このままだと、最後にカンマがついた状態になるので、消す必要があります。
そこで、LEFT関数 と LEN関数 を使います。
LEFT関数 は文字数を指定して、左から文字列を切り出すことができます。
LEN関数 は文字列の長さを求めることができます。
これらを組み合わせて、最後のカンマだけ入らないように、左から切り出すことで、最後のカンマを消しています。

=LEFT(CONCATENATE(ARRAYFORMULA(B2:B8 & CHAR(10))),LEN(CONCATENATE(ARRAYFORMULA(B2:B8 & CHAR(10))))-1)

B13 の上記の式で、「都道府県を改行区切りで連結」を求めています。
カンマの例と式はほぼ同じですが、カンマの代わりに「 CHAR(10) 」を使っています。
CHAR(10) は改行を表しますので、改行区切りにすることができます。

色んなケースで、配列数式を使った計算方法を見てみました。
理解しやすいように、上から下に行くにつれて難易度が徐々に上がるように構成してみました。
他の関数と合わせて使うことで、もっと応用できるので、よければ使ってみてください。