エクセル スピル、動的配列数式の使い方

はじめに

エクセルのスピル、動的配列数式の使い方を紹介します。

セルの範囲の計算ができ、関数をセルの範囲に対して実行できます。

=A1:A3+10 のようにして、各セルに 10 を足し算できます。結果は隣接するセルに表示されます。

=LEN(A1:A3) のようにして、各セルの文字数を隣接するセルに表示できます。

条件を指定できない関数に AND や OR 条件を付けられます。

数式を Ctrl + Shift + Enter で確定すると「配列数式」になり、すべてのバージョンで使用できます。スピルは配列数式を簡単に入力できるようにした上位互換の機能です。

スピル、動的配列数式とは

セル「C2」に =B2:B4 と入力します。

結果がセル「C2」～「C4」に表示されます。これがスピルです。そのセルを選択するとスピルの範囲が強調表示されます。

=B2:B4 と入力した後に F9 キーを入力すると、セルの範囲が配列になります。これは従来からできます。そのまま確定すると、結果が同じようにセル「C2」～「C4」に表示されます。

つまり、スピルは次のことをしています。

• セルの範囲を配列化：セルの範囲やそれを計算した結果を配列にします。
• 配列を隣接するセルに表示： 数式の結果が配列のとき、そのすべての値を隣接するセルに表示します。

これらの動作を理解するために、「配列」と「セルの範囲の計算」について下記で解説します。

スピル表示された範囲に何か値が入力されていると、エラー #SPILL! になります。

スピルを解除するには、それを入力したセル「C2」を削除します。

スピルは従来の「配列数式」を使いやすくした上位互換の機能です。配列数式は互換性のために残されています。

スピルにしない

結果を従来の方法で表示するには =@B2:B3+@C2:C3 や =@LEN(B3:B5) のように、関数やセルの範囲の前に @ を付けます。結果が数式を入力したセルだけに表示されます。

スピルの範囲をセル参照

スピルで表示された結果のセルも、通常と同じようにセル参照できます。

スピルの範囲を参照するには C2# のようにスピルが入力されているセル名の後ろに # を付けます。これを「スピル範囲演算子」と言い、スピルの範囲を参照できます。

スピルでできること

スピルを使うと次のことができるようになります。

• セルの範囲を計算できる。
• 単一の結果を返す関数をセルの範囲に対して使用できる。
• 関数に条件を付けられる。
• 全てのセルで必ず同じ式になる。

配列について

配列は複数の値をまとめたものです。{1,2,3} のように {} で囲むと配列を入力できます。

セルの範囲はセル参照しますが、値を直接入力するときにセルの範囲のようにまとめて入力するのに配列を使用します。

要素の並び

{1,2,3} のように , で要素を区切ると、次のように要素が横並びになります。

{1;2;3} のように ; で要素を区切ると、次のように要素が縦並びになります。

{1,2;3,4} のように , と ; で要素を区切ると、次のように表形式になります。Z 方向に入力するように , の後に ; の順番で区切ります。一般的に二次元配列と言います。

要素の値

配列に入力できる値は文字列、数値、TRUE、FALSE、エラー値などです。セル参照や関数、日付は入力できません。 {"2000/1/1"} のように文字列としてなら日付を入力できます。

配列の表示

配列をセルに入力するとスピルの「配列を隣接するセルに表示」機能により、すべての値が表示されます。

スピルに対応していないバージョンでは、1 つ目の要素だけが表示されます。

配列の計算

{1;2;3}+10 のように配列に対して計算すると、{1+10; 2+10; 3+10} のようにそれぞれの要素に対して計算します。結果は配列で {11;12;13} になります。

{1;2;3}+{10;20;30} のように配列同士で計算すると、{1+10; 2+20; 3+30} のようにそれぞれの要素同士で計算します。結果は配列で {11;22;33} になります。

配列同士の計算は、要素の並びと要素数を同じにする必要があります。違っているときはその要素の結果がエラー #N/A になります。

配列を関数の引数に入力

「LEN 関数」のように単一の値を受け取る引数に配列を入力すると、結果が配列になります。

=LEN({"A";"AB";"ABC"}) は ={LEN("A"); LEN("AB"); LEN("ABC")} のようにそれぞれの要素に対して関数を実行します。結果は配列で {1;2;3} のようになります。

「MAX 関数」のようにセルの範囲や複数の値を受け取る引数に配列を入力できます。

=MAX({1;2;3}) は =MAX(1,2,3) のように要素がそれぞれの引数に分割されます。結果は 3 になります。

「MATCH 関数」のようにセルの範囲を受け取る引数に配列を入力できます。

=MATCH("A",{"A";"B";"C"},0) は配列がセルの範囲と同様に扱われます。結果は 1 になります。

「RANK 関数」など、セルの範囲を受け取る引数の一部の関数には配列を入力できません。その時はエラーになります。

セルの範囲を計算する

セルの範囲やそれを計算した結果はスピルの「セルの範囲を配列化」機能により配列化されます。そのため配列と同じ動作になります。

セルの範囲の計算

B3:B5+10 のようにセルの範囲に対して計算すると、B3+10, B4+10, B5+10 のようにそれぞれのセルに対して計算します。結果は配列で {11;12;13} のようになります。

B3:B5+C3:C5 のようにセルの範囲同士で計算すると、B3+C3, B4+C4, B5+C5 のようにそれぞれのセル同士で計算します。結果は配列で {11;22;33} のようになります。

セルの範囲同士の計算は、お互いの行数や列数を合わせて計算する必要があります。B3:B5+C4:C6 の計算は B3+C4, B4+C5, B5+C6 のようになります。

どちらか一方のセルの数が多い時はその部分の結果がエラー #N/A になります。B3:B4+C4:C6 の計算は B3+C4, B4+C5, (なし)+C6 のようになり、結果は {21;32;エラー} のようになります。

セルの範囲を関数の引数に入力

セルの範囲やそれを計算した結果は配列になるため、引数に配列を入力したときと同じ結果になります。

「LEN 関数」のように単一の値を受け取る引数にセルの範囲を入力すると、結果が配列になります。

=LEN(B3:B5) は ={LEN(B3); LEN(B4); LEN(B5)} のようにそれぞれの要素に対して関数を実行します。結果は配列で {1;2;3} のようになります。

「MAX 関数」のように複数の値を受け取る引数にセルの範囲の計算結果を入力できます。

=MAX(B3:B5+10) は =MAX(B3+10, B4+10, B5+10) のようにセルがそれぞれの引数に分割されます。結果は 13 のようになります。

「SUM 関数」のようにセルの範囲を受け取る引数にセルの範囲の計算結果を入力できます。

=SUM(B3:B5+10) は =SUM({B3+10; B4+10; B5+10}) のようになります。結果は 11+12+13 の合計で 36 になります。

「RANK 関数」などセルの範囲を受け取る引数の一部の関数は配列を入力できないため、セルの範囲の計算結果を入力するとエラーになります。

使用例

関数の数だけ使用方法があります。その中からよく使いそうな例を紹介します。

• セルの範囲の計算結果を使用できる。
• セルの範囲の文字数を数える
• 上位 3 位までの合計を求める
• 下位 3 位までの合計を求める
• 条件に一致する最大値を求める
• AND 条件に一致する最大値を求める
• OR 条件に一致する最大値を求める
• AND と OR 条件を組み合わせる

セルの範囲を計算する

=B3:B5*C3:C5 のように 1 つのセルに数式を入力するだけで、各セル同士の計算結果を表示できます。数式をコピーする手間が省け、それぞれのセルの数式を同じにできます。

上記で紹介したように、関数の引数に計算結果を入力できるので使用する機会は多いです。

セルの範囲の文字数を数える

「LEN 関数」は 1 つのセルの文字数しか数えられませんが、スピルを使うとセルの範囲の文字数を数えられます。

このように 1 つの値を返す関数にセルの範囲を入力すると結果が配列になります。それを「SUM 関数」や「MAX 関数」などに入力してセルの範囲に対しての結果を求められます。

上位 3 位までの合計を求める

「LARGE 関数」の引数「順位」に配列を入力して結果が配列になります。それを「SUM 関数」で合計します。

引数「順位」をセルの範囲で =SUM(LARGE(B2:B6,C2:C4)) のようにもできます。

下位 3 位までの合計を求める

「SMALL 関数」の引数「順位」に配列を入力して結果が配列になります。それを「SUM 関数」で合計します。

引数「順位」をセルの範囲で =SUM(SMALL(B2:B6,C2:C4)) のようにもできます。

条件に一致する最大値を求める

「MAX 関数」などに条件を指定できます。条件の入力に「IF 関数」を使用します。

分類が エクセル の最大値を取得します。

「IF 関数」にセルの範囲を入力しているので {IF(B3="エクセル",C3); IF(B4="エクセル",C4); …} のように、それぞれのセルに対して条件を判定する配列になります。

その結果は配列で {TRUE;TRUE;TRUE;FALSE;FALSE} のようになります。TRUE のときは C3:C7 を取得するので {1;2;3;FALSE;FALSE} になります。

それが =MAX({1;2;3;FALSE;FALSE}) のようになり、FALSE は 0 として扱われるので条件に一致する最大値を取得できます。

0 より小さい最大値を取得するには、条件が FALSE のときに最小値 -99999 などを返すように入力します。

このように「IF 関数」からセルの範囲に対して条件を入力すると結果が配列になります。それを別の関数の引数に入力して関数に条件を入力できるようになります。

AND 条件に一致する最大値を求める

分類が エクセル かつバージョンが 2016 の中で最大の値を取得します。「AND 関数」はスピルしないので使用できません。

上記の「条件に一致する最大値を求める」と基本的な動作は同じです。AND 条件を入力する部分だけ違うのでそこを解説します。

(B3:B7="エクセル") の部分で結果が {TRUE;TRUE;TRUE;FALSE;FALSE} になります。

(C3:C7=2016) の部分で結果が {TRUE;TRUE;FALSE;TRUE;FALSE} になります。

この結果を掛け算しています。TRUE は 1、FALSE は 0 として扱われるので 1 * 1 は 1 (TRUE) になります。それ以外の組み合わせはすべて 0 (FALSE) になります。

すべての条件に一致しているときだけ TRUE になり、TRUE のときは D3:D7 の値を取得するので、AND 条件に一致する最大値を取得できます。

入力時の注意として条件を () で囲む必要があります。 (条件1)*(条件2)

OR 条件に一致する最大値を求める

分類が エクセル またはバージョンが 2016 の中で最大の値を取得します。「OR 関数」はスピルしないので使用できません。

上記の「条件に一致する最大値を求める」と基本的な動作は同じです。OR 条件を入力する部分だけ違うのでそこを解説します。

(B3:B7="エクセル") の部分で結果が {TRUE;TRUE;TRUE;FALSE;FALSE} になります。

(C3:C7=2016) の部分で結果が {TRUE;TRUE;FALSE;TRUE;FALSE} になります。

この結果を足し算しています。TRUE は 1、FALSE は 0 として扱われるので 0 + 0 は 0 (FALSE) になります。それ以外の組み合わせはすべて 1 以上 (TRUE) になります。

1 つでも条件に一致していると TRUE になり、TRUE のときは D3:D7 の値を取得するので、OR 条件に一致する最大値を取得できます。

入力時の注意として条件を () で囲む必要があります。 (条件1)+(条件2)

AND と OR 条件を組み合わせる

AND 条件と OR 条件を組み合わせるには次のようにします。

条件1 かつ 条件2 または 条件3

条件1 または 条件2 かつ 条件3 または 条件4

それぞれの条件を () で囲み OR 条件全体も () で囲みます。