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

はじめに

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

セルの範囲の計算ができるようになり、結果を隣接するセルに表示できるようになります。

条件を指定できない関数を条件に対応するなど様々なことができるようになります。配列数式に置き換わる新しい機能です。

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

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

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

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

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

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

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

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

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

スピルは「配列数式、CSE数式とは」をより使いやすくしたものです。配列数式は互換性のために残されています。

スピルにしない

結果を従来の方法で表示するには =@B2:B3+@C2:C3 や =@LEN(B3:B5) のように、関数やセルの範囲の前に @ を付けます。1 つ目の要素だけを表示できます。

セル参照

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

C2# のようにセル名の後ろに # を付けると、スピルの範囲を参照できます。

できること

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

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

詳しくは下記の「使用例」で紹介します。

スポンサーリンク

配列について

配列は値の集まりのことです。 {1,2,3} のように {} で囲むと配列を入力できます。

種類

{1,2,3} のように , で要素を区切ると、次のように横並びになっているものを表しています。

{1;2;3} のように ; で要素を区切ると、次のように縦並びになっているものを表しています。

{1,2;3,4} のように , と ; で要素を区切ると、次のように表形式になっているものを表しています。必ず , の後に ; の順番で区切ります。一般的に二次元配列と言います。

値

配列に入力できる値は文字列、数値、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) は =MAX(B3, B4, B5) のようにセルがそれぞれの引数に渡されます。結果は 3 のようになります。

「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 つの値を返す関数にセルの範囲を渡すと結果が配列になります。それを「MAX 関数」や「SUM 関数」などでに渡して複数のセルに対しての結果を求められます。

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

「LARGE 関数」の引数「順位」に配列を渡して結果を配列にしています。

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

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

「SMALL 関数」の引数「順位」に配列を渡して結果を配列にしています。

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

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

「MAX 関数」などに条件を指定できます。条件には比較演算子を使います。

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

「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}) になります。TRUE は 1、FALSE は 0 として扱われるので条件に一致する最大値を取得できます。

0 より小さい最大値を取得したいときは、FALSE のときに -99999 を返すなどして =MAX(IF(B3:B7="エクセル",C3:C7,-99999)) のようにする必要があります。

このように「IF 関数」でセルの範囲に対して条件を指定すると結果が配列になるので、それに対してさらに別の関数で処理をすると条件に対応できるようになります。

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

分類が「エクセル」かつバージョンが「2016」の中で最大の値を取得します。

「条件に一致する最大値を求める」と基本的な動作は同じです。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 条件を指定する部分だけ違うのでそこを解説します。

(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 条件全体も () で囲みます。

スポンサーリンク