配列数式のガイドラインと例

この演習では、複数セルの配列数式および単一セルの配列数式を使用して、売上金額を計算します。 まず、複数セルの数式を使用して、小計を求めます。 次に、単一セルの数式を使用して、総計を求めます。

• 複数セルの配列数式

下の表全体をコピーし、空白のワークシートのセル A1 に貼り付けます。

営業担当者	車 の種類	販売数	単価	合計 売上
川井	セダン	5	33000
	クーペ	4	37000
阿藤	セダン	6	24000
	クーペ	8	21000
阿部	セダン	3	29000
	クーペ	1	31000
山水	セダン	9	24000
	クーペ	5	37000
安藤	セダン	6	33000
	クーペ	8	31000
数式 (総計)			総計
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. 各営業担当者のクーペとセダンの合計売上を表示するには、セル E2:E11 を選択し、数式 =C2:C11*D2:D11 を入力し、 Ctrl + Shift + Enter キーを押します。

2. すべての売上の総計を表示するには、セル F11 を選択し、数式 =SUM(C2:C11*D2:D11)を入力し、 Ctrl + Shift + Enter キーを押します。

Ctrl + Shift + Enter キーを押すと、数式が中かっこ ({ }) で囲まれ、選択した範囲の各セルに数式のインスタンスが挿入されます。 この処理は瞬時に行われ、各販売員について車種ごとの売上合計が E 列に表示されます。 E2、E3、E4 などを選択すると、同じ数式 {=C2:C11*D2:D11} が表示されます。 

• 単一セルの配列数式を作成する

ブックのセル D13 に次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

=SUM(C2:C11*D2:D11)

この場合、Excel は配列内の値 (セル範囲 C2 から D11) を乗算し、SUM 関数を使用して合計をまとめて追加します。 この結果、売上の総計である 1,590,000 円が求められます。 この例から、配列数式がいかに便利であるかがわかります。 たとえば、1,000 行のデータがあるとします。 単一のセルに配列数式を作成すると、数式を 1,000 行分下にドラッグしなくても、そのデータの一部またはすべてを集計できます。

また、セル D13 の単一セル式は、複数セル式 (セル E2 から E11 の数式) とは完全に独立していることに注意してください。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 列 E の数式を変更したり、D13 の数式に影響を与えずにその列を完全に削除したりできます。

配列数式には、次のような利点もあります。

• 一貫性    セル E2 以降、この列のどのセルをクリックしても、同じ数式が表示されます。 この一貫性が、正確さの向上に役立ちます。

• 安全性:    複数セルの配列数式のコンポーネントを上書きすることはできません。 たとえば、セル E3 をクリックし、 Delete キーを押します。 セル範囲全体 (E2 ～ E11) を選択し、配列全体に対する数式を変更するか、または配列をそのままにしておく必要があります。 追加の安全策として、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押して、数式の変更を確認する必要があります。

• ファイル サイズを小さくする    多くの場合、複数の中間数式の代わりに 1 つの配列式を使用できます。 たとえば、ブックでは 1 つの配列数式を使用して、列 E の結果を計算します。標準の数式 (=C2*D2、C3*D3、C4*D4....など) を使用していた場合は、11 個の異なる数式を使用して同じ結果を計算していました。

多くの場合、配列数式では標準の数式の構文を使用します。 配列数式は常に等号 (=) で始まり、ほとんどの組み込みの Excel 関数を使用できます。 主な違いは、配列数式を使用する場合、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押して数式を入力することです。 これで、配列数式が中かっこで囲まれます (中かっこを手動で入力した場合、数式が文字列に変換され、機能しません)。

配列関数は、複雑な数式を作成する効率的な方法です。 配列数式 =SUM(C2:C11*D2:D11) は、=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11) と同じです。

複数セルの数式を操作するときは、次の点にも注意する必要があります。

• 数式を入力する前に、結果の格納先のセル範囲を選択します。 これは、複数セルの配列数式を作成する際に、セル E2 ～ E11 を選択したときに行いました。

• 配列数式の個々のセルの内容を変更することはできません。 実際に試すには、ブックのセル E3 を選択し、Del キーを押します。 配列の一部を変更することができないことを知らせるメッセージが表示されます。

• 配列数式全体を移動または削除できますが、配列数式の一部を移動または削除することはできません。 つまり、配列数式を縮小するには、既存の数式を削除してから、数式を入力し直す必要があります。

• 配列数式を削除するには、数式範囲全体 ( E2:E11 など) を選択し、Delete キーを押 します。

• 複数セル配列数式に空白のセルを挿入したり、セルを削除したりすることはできません。

場合によっては、配列数式の拡張が必要になる場合もあります。 既存の配列範囲の最初のセルを選択し、数式を拡張する範囲全体を選択するまで続行します。 F2 キーを押して数式を編集し、Ctrl キーを押しながら Shift キーを押しながら Enter キーを押して、数式の範囲を調整したら数式を確定します。 キーは、配列の左上のセルから始まる範囲全体を選択することです。 左上のセルは、編集されるセルです。

配列数式は優れていますが、次のような短所があります。

• Ctrl キーを押しながら Shift キーを押しながら Enter キーを押すのを忘れる場合があります。 経験豊富な Excel ユーザーでも忘れる可能性があります。 配列数式を入力または編集するときは、このキーの組み合わせを必ず押してください。

• ブックの他のユーザーが数式を理解していない可能性があります。 実際には、配列数式は一般にワークシートでは説明されません。 そのため、他のユーザーがブックを変更する必要がある場合は、配列の数式を避けるか、配列数式について理解し、必要に応じてそれらを変更する方法を理解する必要があります。

• コンピューターの処理速度とメモリによっては、大きな配列数式を使用すると、計算速度が低下することがあります。

配列定数は、配列数式の構成要素です。 配列定数を入力するには、項目のリストを入力し、次のように、手動でリストを中かっこ ({ }) で囲みます。

={1,2,3,4,5}

これで、配列数式を作成するときに Ctrl キーを押しながら Shift キーを押しながら Enter キーを押す必要があることがわかります。 配列定数は配列数式の構成要素であるため、配列定数の前後に手動で中かっこを入力して定数を囲みます。 次に、 Ctrl + Shift + Enter キーを使用して数式全体を入力します。

カンマを使用して項目を区切った場合は、水平配列 (行) が作成されます。 セミコロンを使用して項目を区切った場合は、垂直配列 (列) が作成されます。 2 次元配列を作成するには、カンマを使用して各行の項目を区切り、さらに、セミコロンを使用して各行を区切ります。

{1,2,3,4} という 1 行の配列を次に示します。 {1;2;3;4} は 1 つの列の配列です。 {1,2,3,4;5,6,7,8} は 2 行 4 列の配列です。 2 行配列では、最初の行は 1、2、3、4、2 行目は 5、6、7、8 です。 4 と 5 の間の単一のセミコロンで 2 つの行が区切られています。

配列数式と同様、Excel に用意されているほとんどの組み込み関数で配列定数を使用できます。 後のセクションでは、各種の定数を作成する方法、およびこれらの定数を Excel の関数と組み合わせて使用する方法について説明します。

水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。

水平定数を作成する

1. 空白のワークシートで、セル A1 から E1 を選択します。

2. 数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   ={1,2,3,4,5}

   この場合は、開始中かっこと右中かっこ ({ }) を入力する必要があり、Excel によって 2 番目のセットが追加されます。

   次の結果が表示されます。

   

垂直定数を作成する

1. ブックで、5 行 1 列のセルを選択します。

2. 数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   ={1;2;3;4;5}

   次の結果が表示されます。

   

2 次元定数を作成する

1. ブックで、4 列 3 行のセルのブロックを選択します。

2. 数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   ={1,2,3,4;5,6,7,8;9,10,11,12}

   次の結果が表示されます。

   

数式で定数を使用する

定数を使用する簡単な例を見てみましょう。

1. サンプル ブックで、新しいワークシートを作成します。

2. セル A1 に 3 と入力し、B1 に 4、C1 に 5、D1 に 6、E1 に 7 と入力します。

3. セル A3 に次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

   =SUM(A1:E1*{1,2,3,4,5})

   配列数式として入力したため、Excel によって定数全体が別の中かっこで囲まれます。

   

   セル A3 に値 85 が表示されます。

次のセクションでは、この数式がどのように機能するかを説明します。

上で使用した数式は、いくつかの部分で構成されます。

1. 関数

2. 格納された配列

3. 演算子

4. 配列定数

かっこで囲まれた最後の要素は配列定数 {1,2,3,4,5} です。 配列定数は Excel によって自動的に中かっこで囲まれないため、実際に入力する必要があります。 また、配列数式に定数を追加した後、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押して数式を入力します。

Excel では、かっこで囲まれた式の演算が最初に実行されるため、次に関与する要素は、ブックに格納した値 (A1:E1) と演算子の 2 つです。 この時点で、格納された値に対応する定数の値を掛ける数式が実行されます。 これは、次の式に相当します。

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最後に、SUM 関数によって値が加算され、セル A3 に合計 85 が表示されます。

格納された配列を使用せずに、演算全体をメモリ内で実行するには、格納された配列を次の配列定数で置き換えます。

=SUM({3,4,5,6,7}*{1,2,3,4,5})

これを試すには、関数をコピーし、ブック内の空白のセルを選択し、数式を数式バーに貼り付けて、 Ctrl + Shift + Enter キーを押します。 前の手順で配列数式

=SUM(A1:E1*{1,2,3,4,5}) を使用した場合と同じ結果が表示されます。

配列定数には、数値、文字列、論理値 (TRUE、FALSE など)、およびエラー値 (#N/A など) を格納できます。 数値には、整数、小数、および指数表現を使用できます。 文字列を使用する場合は、文字列を引用符 (") で囲む必要があります。

配列定数には、別の配列、数式、または関数を含めることができません。 つまり、カンマまたはセミコロンで区切られた文字列または数値のみを含めることができます。 {1,2,A1:D4}、{1,2,SUM(Q2:Z8)} などの数式を入力すると、警告メッセージが表示されます。 また、数値には、パーセント記号、ドル記号、カンマ、かっこを含めることができません。

配列定数を使用する最良の方法の 1 つは、名前を付ける方法です。 名前付き定数は使いやすく、使用すると他の人の目には配列数式の複雑さの一部が見えなくなります。 配列定数に名前を付け、数式で使用するには、次の操作を行います。

1. [数式] タブの [定義された名前] で [名前の定義] をクリックします。
   [ 名前の定義 ] ダイアログ ボックスが表示されます。

2. [名前] ボックスに、「第 1 四半期」と入力します。

3. [参照範囲] ボックスに次の定数を入力します (中かっこを手動で入力してください)。

   ={"1 月","2 月","3 月"}

   ダイアログ ボックスの定数は、次のようになります。

   

4. [OK] をクリックし、3 個の空白セルがある行を選択します。

5. 次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   =第 1 四半期

   次の結果が表示されます。

   

名前付き定数を配列数式として使用する場合は、等号を入力することを忘れないでください。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 また、文字列と数値の組み合わせを使用できることも覚えておいてください。

配列定数が機能しない場合は、次の問題がないか確認してください。

• 一部の要素が適切な文字で区切られていない可能性があります。 コンマまたはセミコロンを省略した場合、または正しくない場所に配置した場合、配列定数が正しく作成されない場合や、警告メッセージが表示される場合があります。

• 選択したセルの範囲と、定数の要素の数が一致していない可能性があります。 たとえば、6 行 1 列のセルを選択して 5 つのセル用の定数を使用しようとすると、空白セルに #N/A エラー値が表示されます。 逆に、選択したセルの数が少なすぎる場合は、対応するセルのない値が省かれます。

配列数式で配列定数を使用する方法を示す例をいくつか紹介します。 一部の例では 、TRANSPOSE 関数 を使用して行を列に変換し、その逆の変換を行います。

配列の各項目を乗算する

1. 新しいワークシートを作成し、4 列 3 行の空のセルのブロックを選択します。

2. 次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

   ={1,2,3,4;5,6,7,8;9,10,11,12}*2

配列の各項目を 2 乗する

1. 4 列 3 行の空のセルのブロックを選択します。

2. 次の配列数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

   または、キャレット演算子 (^) を使用する場合は、次の配列数式を入力します。

   ={1,2,3,4;5,6,7,8;9,10,11,12}^2

1 次元の行を転置する

1. 1 列 5 行の空白セルを選択します。

2. 次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

   =TRANSPOSE({1,2,3,4,5})

   入力したのは水平配列定数ですが、TRANSPOSE 関数によって、配列定数が列に変換されます。

1 次元の列を転置する

1. 1 行 5 列の空白セルを選択します。

2. 次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

   =TRANSPOSE({1;2;3;4;5})

入力したのは垂直配列定数ですが、TRANSPOSE 関数によって、配列定数が行に変換されます。

2 次元定数を転置する

1. 3 列 4 行のセルのブロックを選択します。

2. 次の定数を入力し、 Ctrl + Shift + Enter キーを押します。

   =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

   TRANSPOSE 関数によって、行が列に変換されます。

このセクションでは、基本的な配列数式の例を示します。

既存の値から配列および配列定数を作成する

次の例では、配列数式を使用して、異なるワークシートのセル範囲間のリンクを作成する方法を示します。 同じ値セットから配列定数を作成する方法も示します。

既存の値から配列を作成する

1. Excel のワークシートで、セル C8:E10 を選び、次の数式を入力します。

   ={10,20,30;40,50,60;70,80,90}

   「10」を入力する前に「{」 (かっこ開き) を入力し、「90」を入力した後に「}」 (かっこ閉じ) を入力します。数値の配列を作成しているからです。

2. Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。この配列は、配列数式を使用してセル範囲 C8:E10 に数値の配列を入力します。 ワークシートでは、C8 から E10 は次のようになります。

   10	20	30
   40	50	60
   70	80	90

3. C1 ～ E3 のセル範囲を選択します。

4. 数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   =C8:E10

   セルの 3x3 配列は、C8 から E10 と同じ値を持つセル C1 から E3 に表示されます。

既存の値から配列定数を作成する

1. セル C1:C3 が選択されている状態で 、F2 キーを押して編集モードに切り替えます。 

2. F9 キーを押して、セル参照を値に変換します。 Excel により、値が配列定数に変換されます。 数式は ={10,20,30 になります。40,50,60;70,80,90}。

3. Ctrl キーを押しながら Shift キーを押しながら Enter キーを押して、配列の数式として配列定数を入力します。

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

セル範囲の文字数 (スペースを含む) を数える方法の例を次に示します。

1. このテーブル全体をコピーし、ワークシートのセル A1 に貼り付けます。

   データ
   この文章は、
   それぞれのセルに別れて入力されていますが、
   すべてのセルに入力されている
   文章を合わせて、
   一つの文章にすることができます。
   A2:A6 の合計文字数
   =SUM(LEN(A2:A6))
   最も長いセル (A3) の内容
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. セル A8 を選択し、 Ctrl + Shift + Enter キー を押して、セル A2:A6 (66) の文字数の合計を表示します。

3. セル A10 を選択し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押 して、最も長いセル A2:A6 (セル A3) の内容を確認します。

セル A8 で使用される次の数式は、セル A2 から A6 の文字数 (66) の合計数をカウントします。

=SUM(LEN(A2:A6))

この例の場合は、LEN 関数がセル範囲の各セルに含まれる文字列の長さを返します。 次に、SUM 関数によってこれらの値が一緒に追加され、結果が表示されます (66)。

範囲内で値の小さい方から n 番目までを検索する

次の例では、セル範囲内で値の小さい方から 3 番目までを検索します。

1. セル A1:A11 に乱数を入力します。

2. セル C1 から C3 を選択します。 この一連のセルに、配列数式から返された結果を格納します。

3. 次の数式を入力し、 Ctrl + Shift + Enter キーを押します。

   =SMALL(A1:A11,{1;2;3})

この数式では、配列定数を使用して SMALL 関数を 3 回評価し、セル A1:A10 に含まれる配列内の最小 (1)、2 番目に小さい (2)、および 3 番目に小さい (3) メンバーを返します。 SUM、AVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。

=SUM(SMALL(A1:A10,{1,2,3})

=AVERAGE(SMALL(A1:A10,{1,2,3})

範囲内で値の大きい方から n 番目までを検索する

範囲内に含まれる値のうち、大きい方の値を検索するには、SMALL 関数の代わりに LARGE 関数を使用します。 次の例では、ROW 関数と INDIRECT 関数も使用しています。

1. セル D1 から D3 を選択します。

2. 数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

ここで、ROW 関数と INDIRECT 関数について簡単に説明しておきます。 ROW 関数を使用すると、連続する整数の配列を作成できます。 たとえば、練習用ブックで 10 個のセルの空の列を選択し、次の配列数式を入力し、 Ctrl + Shift + Enter キーを押します。

=ROW(1:10)

これで、10 個の連続する整数の列が作成されます。 潜在的な問題を確認するために、配列数式が格納されている範囲の上 (つまり、行 1) に行を挿入します。 Excel によって行参照が調整され、2 ～ 11 の整数が生成されます。 この問題を修正するには、次のように、INDIRECT 関数を数式に追加します。

=ROW(INDIRECT("1:10"))

INDIRECT 関数は、引数として文字列を使用します (範囲 1:10 を二重引用符で囲むのはそのためです)。 行の挿入、または配列数式の移動を行っても、Excel によって文字列値が調整されることはありません。 この結果、ROW 関数は、常に目的の整数の配列を生成するようになります。

前に使用した数式 =LARGE(A5:A14,ROW(INDIRECT("1:3"))) を見てみましょう。内かっこから始まり、外側に向かって動作します。 INDIRECT 関数はテキスト値のセットを返します。この場合、値は 1 から 3 です。 ROW 関数は、3 セルの列配列を生成します。 LARGE 関数は、セル範囲 A5:A14 の値を使用し、ROW 関数によって返される参照ごとに 1 回、3 回評価されます。 値 3200、2700、および 2000 は、3 セルの列配列に返されます。 さらに多くの値を検索する場合は、 INDIRECT 関数にセル範囲を広く追加します。

前の例と同様に、 SUM や AVERAGE などの他の関数でこの数式を使用できます。

セル範囲内で最も長い文字列を検索する

前のテキスト文字列の例に戻る、空のセルに次の数式を入力し、Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します。

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

テキスト "セルの束" が表示されます。

この式を、内側の要素から順番に詳しく確認してみましょう。 LEN 関数は、セル範囲 A2:A6 の各項目の長さを返します。 MAX 関数は、これらの項目の中で最も大きい値を計算します。これは、セル A3 にある最長のテキスト文字列に対応します。

ここからは、少し複雑になります。 MATCH 関数は、最も長い文字列を格納しているセルのオフセット (相対位置) を計算します。 この計算を行うには、検査値、検査範囲、および照合の種類という 3 つの引数が必要です。 MATCH 関数は、指定された検査値を検査範囲で検索します。 この例の場合、検査値は、最も長い文字列です。

(MAX(LEN(A2:A6))

この文字列は、次の配列に含まれます。

LEN(A2:A6)

"照合の種類" 引数は 0 です。 照合の種類は、1、0、または -1 の値で構成されます。 1 を指定した場合、MATCH は、検査値以下で、最も大きな値を返します。 0 を指定した場合、MATCH は、検査値と等しい最初の値を返します。 -1 を指定した場合、MATCH は、指定した値以上で、最も小さい値を検索します。 照合の種類を指定しなかった場合は、1 が指定されたと見なされます。

最後に、INDEX 関数は、配列、配列内の行番号および列番号を引数として使用します。 セル範囲 A2:A6 は配列を提供し、 MATCH 関数はセル アドレスを提供し、最後の引数 (1) は値が配列の最初の列から取得されることを指定します。

このセクションでは、高度な配列数式の例を示します。

エラー値を含む範囲の合計を求める

Excel の SUM 関数は、範囲内に #N/A などのエラー値が含まれている場合は機能しません。 この例では、エラーを含む、"データ" という名前の範囲の値を集計する方法を示します。

=SUM(IF(ISERROR(データ),"",データ))

この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。 内側の関数から順に説明すると、ISERROR 関数は、セル範囲 ("データ") でエラーがないか検索します。 IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。 この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 ("") が返され、範囲 (データ) の残りの値については FALSE に評価される (つまり、エラー値を格納していない) ため、値自体が返されます。 次に、SUM 関数が、フィルター処理された配列の合計を計算します。

範囲内のエラー値の個数を数える

この例は前の数式と似ていますが、"データ" という名前の範囲のエラー値をフィルター処理する代わりに、エラー値の数を返します。

=SUM(IF(ISERROR(データ),1,0))

この数式では、エラーを含むセルの場合は値 1 を、エラーを含まないセルの場合は値 0 を格納している配列を作成します。 次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。

=SUM(IF(ISERROR(データ),1))

この引数を指定しなかった場合、セルにエラー値が含まれていないと、IF 関数は FALSE を返します。 この数式をさらに簡略化して、次のようにすることもできます。

=SUM(IF(ISERROR(データ)*1))

この形式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

条件に基づいて値を合計する

条件に基づいて値を集計することが必要になる場合があります。 たとえば、次の配列数式では、"売上" という名前の範囲に含まれる正の整数だけを合計します。

=SUM(IF(売上>0,売上))

IF 関数は、正の値と false 値の配列を作成します。 0+0=0 であるため、SUM 関数は基本的に false 値を無視します。 この数式で使用するセル範囲には、指定の数の行と列を含めることができます。

複数の条件を満たす値を合計することもできます。 たとえば、次の配列数式では、0 より大きく、かつ、5 以下の値を計算します。

=SUM((売上>0)*(売上<=5)*(売上))

範囲に数値以外のセルが含まれている場合、この数式はエラーを返します。

OR 条件を使用する配列数式を作成することもできます。 たとえば、5 未満の値と、15 より大きい値の合計を求めることができます。

=SUM(IF((売上<5)+(売上>15),売上))

IF 関数は、5 未満の値と、15 より大きい値をすべて検索し、これらの値を SUM 関数に渡します。

AND 関数と OR 関数は単一の結果 (TRUE または FALSE) を返しますが、配列数式では結果の配列が必要であるため、配列関数で AND 関数と OR 関数を直接使用することはできません。 この問題に対処するには、前の数式で示したロジックを使用します。 つまり、OR 条件または AND 条件を満たす値に対して、加算や乗算などの数学演算を実行します。

0 を除いた平均を計算する

範囲内の値から 0 を除いて平均を求める方法の例を次に示します。 この数式では、"売上" という名前のデータ範囲を使用しています。

=AVERAGE(IF(売上<>0,売上))

IF 関数が、0 と等しくない値の配列を作成し、これらの値を AVERAGE 関数に渡します。

2 つのセル範囲間で相違する値の個数を数える

この配列数式では、MyData および YourData という名前の 2 つのセル範囲の値を比較し、この 2 つの範囲間で相違する値の数を返します。 2 つの範囲の内容が一致する場合は、0 が返されます。 この数式を使用するには、2 つのセル範囲が同じサイズで、同じ次元である必要があります (たとえば、"データ1" が 3 行 5 列の範囲であれば、"データ2" も 3 行 5 列である必要があります)。

=SUM(IF(MyData=YourData,0,1))

この数式は、比較対象範囲と同じサイズの新しい配列を作成します。 IF 関数が、配列に値 0 と値 1 を設定します (不一致の場合は 0 で、同一セルの場合は 1)。 次に、SUM 関数が、配列内の値の合計を返します。

この数式は、次のように簡略化できます。

=SUM(1*(MyData<>YourData))

範囲内のエラー値の個数を数える数式と同様、この数式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

範囲内の最大値の場所を検索する

この配列数式は、"データ" という名前の単一列の範囲に含まれる最大値の行番号を返します。

=MIN(IF(データ=MAX(データ),ROW(データ),""))

IF 関数が、"データ" という範囲に対応する新しい配列を作成します。 対応するセルに範囲内の最大値が含まれている場合、配列に行番号が格納されます。 それ以外の場合は、配列に空の文字列 ("") が格納されます。 MIN 関数は、この新しい配列を 2 番目の引数として使用して、最小値 ("データ" の最大値の行番号に対応) を返します。 "データ" という範囲に同じ最大値が複数含まれている場合は、最初の値の行が返されます。

最大値の実際のセル住所を返すには、次の数式を使用します。

=ADDRESS(MIN(IF(データ=MAX(データ),ROW(データ),"")),COLUMN(データ))