1. Tipsfound
  2.  › エクセル
  3.  › スピル、動的配列数式の使い方

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

はじめに

対応：3652021

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

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

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

この関数は 365 または 2021 から使用できます。
  • 目次
    • スピル、動的配列数式とは
    • 配列について
    • セルの範囲を計算する
    • 使用例

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

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

1

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

2

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

3 2

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

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

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

4

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

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

スピルにしない

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

セル参照

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

5

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

6

できること

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

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

配列について

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

種類

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

7

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

8

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

9

値

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

24

表示

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

={1,2,3}
={"あ","い","う"}
={TRUE,FALSE}
10

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

計算

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

11

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

12

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

関数の引数に渡す

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

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

13

「MAX 関数」のように複数の値を受け取る引数に配列を渡せます。

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

15

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

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

16

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

セルの範囲を計算する

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

計算

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

17

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

18

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

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

19

関数の引数に渡す

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

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

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

20

「MAX 関数」のように複数の値を受け取る引数にセルの範囲を渡せます。

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

21

「SUM 関数」のようにセルの範囲を受け取る引数にセルの範囲の計算結果を渡せます。このような関数は元々配列を受け取れるので、そのまま計算した結果になります。

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

22

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

使用例

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

セルの範囲を計算する

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

23

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

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

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

=SUM(LEN(B2:B4))
25

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

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

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

=SUM(LARGE(B2:B6,{1,2,3}))
26

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

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

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

=SUM(SMALL(B2:B6,{1,2,3}))
27

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

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

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

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

=MAX(IF(B3:B7="エクセル",C3:C7))
28

「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」の中で最大の値を取得します。

=MAX(IF((B3:B7="エクセル")*(C3:C7=2016),D3:D7))
29

「条件に一致する最大値を求める」と基本的な動作は同じです。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) になります。

(B3:B7="エクセル")*(C3:C7=2016)
{TRUE;TRUE;TRUE;FALSE;FALSE}*{TRUE;TRUE;FALSE;TRUE;FALSE}
{1;1;1;0;0}*{1;1;0;1;0}
{1*1; 1*1; 1*0; 0*1; 0*0}
{1;1;0;0;0}
{TRUE;TRUE;FALSE;FALSE;FALSE}

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

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

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

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

=MAX(IF((B3:B7="エクセル")+(C3:C7=2016),D3:D7))
30

「条件に一致する最大値を求める」と基本的な動作は同じです。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) になります。

(B3:B7="エクセル")+(C3:C7=2016)
{TRUE;TRUE;TRUE;FALSE;FALSE}+{TRUE;TRUE;FALSE;TRUE;FALSE}
{1;1;1;0;0}+{1;1;0;1;0}
{1+1; 1+1; 1+0; 0+1; 0+0}
{2;2;1;1;0}
{TRUE;TRUE;TRUE;TRUE;FALSE}

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

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

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

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

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

(条件1)*((条件2)+(条件3))

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

((条件1)+(条件2))*((条件3)+(条件4))

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

  • ∨目次
    • スピル、動的配列数式とは
    • 配列について
    • セルの範囲を計算する
    • 使用例