エクセル 配列数式、CSE数式とは、仕組みと使い方

はじめに

エクセルの配列数式、CSE数式とは何かを紹介します。

セルの範囲とセルの範囲を計算した結果を求められます。

条件を指定できない関数に条件を指定できるようになります。

配列数式とは

数式の入力確定に Ctrl + Shift + Enter キーを入力することから、その頭文字をとって「CSE数式」とも呼ばれます。

配列数式には「単一セルの配列数式」と「複数セルの配列数式」の 2 種類があります。どちらもセルの範囲に対して計算できるようになります。

単一セルの配列数式：計算結果を表示するセルを用意することなく、関数に直接セルの範囲を計算した結果を使用できます。配列数式を使用したいときは主にこちらの使い方だと思います。

複数セルの配列数式：1 つの配列数式で、セルの範囲を計算した結果がそれぞれ行ごとの計算した結果になります。

これらの動作を解説するために、「配列」と「セルの範囲の計算」を先に説明します。それを理解すると配列数式が理解できるようになります。

配列について

配列は値の集まりのことです。 {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} になります。配列同士の計算は、配列の種類と要素数を同じにする必要があります。

関数の引数に渡す

「LEN 関数」のように単一の値を引数で受け取る関数に配列を渡せます。

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

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

=SUM({1,2,3}) は =SUM(1,2,3) と同じ結果になります。結果は 6 になります。

「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+(なし); B4+C4; B5+C5; (なし)+C6 のようになり、行や列がずれている部分では結果がエラーになります。結果は {エラー;22;33;エラー} のようになります。

表示

計算結果が配列のようになると上記で説明しましたが、表示については配列と違います。

この動作がとても重要なので「セルの範囲の制約」という名前を説明しやすくするために付けます。

関数の引数に渡す

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

=LEN(B3:B5) は ={LEN(B3); LEN(B4); LEN(B5)} のようにそれぞれの要素に対して関数を実行します。結果は配列で {1;2;3} のようになります。これは「セルの範囲の制約」を受けます。

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

=SUM(B3:B5+10) は ={SUM(B3+10); SUM(B4+10); SUM(B5+10)}) のようにそれぞれの要素に対して関数を実行します。結果は配列で {11;12;13} のようになります。これは「セルの範囲の制約」を受けます。

まとめ

このようにセルの範囲を計算するとその結果の表示には「セルの範囲の制約」を受けることになります。配列数式を使用すると「セルの範囲の制約」を解除できます。

単一セルの配列数式を入力する

単一セルの配列数式を使用すると「セルの範囲の制約」を受けることなく、セルの範囲を計算した結果を単一のセルに表示できます。

入力するには =SUM(B3:B5*C3:C5) と入力した後に Ctrl + Shift + Enter キーで確定します。編集する度に毎回入力する必要があります。成功すると数式バーが {} で囲まれます。

なぜ結果が 2000 になるのかというと、配列数式はセルの範囲を配列に変換しています。セルの範囲の計算から配列の計算になるので「セルの範囲の制約」が無くなります。

例えば次のように =B2:B4 と入力した後に F9 キーを入力すると、セルの範囲が配列になります。

この動作を配列数式は内部で行っています。 {=SUM(B3:B5*C3:C5)} を展開していくと次のようになります。

配列数式を使わないで =SUM({100;200;300}*{2;3;4}) と入力しても、結果が同じになることがわかると思います。

単一セルの配列数式を使うと次のことができるようになります。

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

詳しくは下記の「使用例」をご覧ください。

複数セルの配列数式を入力する

複数セルの配列数式は単一セルの配列数式と意味が異なります。セルの範囲を配列に変換しないため「セルの範囲の制約」を受けたままです。

入力するには結果を表示するセルを範囲選択します。

次に =SUM(B3:B5*C3:C5) と入力した後に Ctrl + Shift + Enter キーで確定します。編集する度に毎回入力する必要があります。

成功すると数式バーが {} で囲まれます。最初に範囲選択した全てのセルで同じ配列数式が入力されます。絶対参照は必要ありません。

これは配列数式を使わないでも同じ結果を得ることができます。配列数式にすることで次のようになります。

• 全てのセルで必ず同じ式になる。
• セルを個別に編集できなくなる。
• セルの間に行や列を追加できなくなる。

配列数式を入力しているセルは保護されます。編集するには同じ配列数式を使用している全てのセルを範囲選択して Delete キーで削除する必要があります。

このように新しいことができるようになるのではなく、セルを保護したり、式を統一する意味で使います。なので無理をして複数セルの配列数式を使用する必要はありません。

使用例

単一セルの配列数式の使用例を紹介します。

• セルの範囲の文字数を数える
• 上位 3 位までの合計を求める
• 下位 3 位までの合計を求める
• 条件に一致する最大値を求める

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

「LEN 関数」は単一のセルの文字数しか数えられませんが、配列数式を使うとセルの範囲の文字数を数えられます。

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

これは配列数式を使いません。「LARGE 関数」の引数「順位」に配列を渡して結果を配列にしています。

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

これは配列数式を使いません。「SMALL 関数」の引数「順位」に配列を渡して結果を配列にしています。

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

「MAX 関数」には条件を指定できませんが、配列数式を使えば条件を指定できます。条件には比較演算子を使います。

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

IF(B3:B7="エクセル" の部分でそれぞれのセルが「エクセル」か判定しています。エクセルなら TRUE そうでないなら FALSE になります。結果は配列で {TRUE;TRUE;TRUE;FALSE;FALSE} になります。

C3:C7 の部分で判定した結果が TRUE のときはこの範囲の値を取得します。FALSE のときは FALSE になります。結果は配列で {1;2;3;FALSE;FALSE} になります。

つまり「IF 関数」は {IF(B3="エクセル",C3); IF(B4="エクセル",C4);・・・} のような動作をしています。

最終的に =MAX({1;2;3;FALSE;FALSE}) になるので条件に一致する最大値を取得できます。ただし FALSE は数値の 0 として扱われます。負の値の最大値を取得したいときは、FALSE を返すところを -9999 などにする必要があります。

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 になり 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 が FALSEで、それ以外の数値はすべて TRUE になります。0 + 0 は 0 (FALSE) になります。それ以外の組み合わせはすべて 1 以上 (TRUE) になります。

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

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

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

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

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

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

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