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

配列数式は、配列内の 1 つ以上の項目に対して複数の計算を実行できる数式です。 配列は、値の行または列、または値の行と列の組み合わせと見なされます。 配列数式は、複数の結果または 1 つの結果を返します。

Microsoft 365の 2018 年 9 月の更新プログラムから、複数の結果を返す数式は、自動的にダウンまたは隣接するセルにスピルします。 この動作の変更には、いくつかの新しい動的 配列関数も伴います。 動的配列数式は、既存の関数を使用している場合でも動的配列関数を使用している場合でも、1 つのセルに入力する必要があるだけで 、Enterキーを押して確認できます。 以前は、従来の配列数式では、最初に出力範囲全体を選択してから、Ctrl + Shift + Enter キーを押して数式を 確認する必要があります。 これらは一般的に CSE 数式 と呼 ばれます。

配列数式を使用すると、次のような複雑なタスクを実行できます。

  • サンプル データセットをすばやく作成します。

  • セル範囲に含まれる文字数をカウントします。

  • 特定の条件を満たす数値 (範囲内の最も小さい値など) または上下の境界の間にある数値のみを合計します。

  • 値の範囲内のすべての N 番目の値を合計します。

次の例では、複数セルと単一セルの配列数式を作成する方法を示します。 可能であれば、一部の動的配列関数と、動的配列とレガシ配列の両方として入力された既存の配列数式の例を取り上えました。

サンプルをダウンロードする

この記事のすべての配列数式の例を含むサンプル ブックをダウンロードします

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

  • 複数セルの配列数式

    セル H10 =F10:F19*G10:G19 の複数セル配列関数を使用して、単価で販売された自動車の数を計算します。

  • ここでは、セル H10 に「=F10:F19*G10:G19」 と入力して、各販売員のクーペとアクセサリーの売上合計を計算します。

    Enter キーを すと、結果がセル H10:H19 にあふれて表示されます。 スピル範囲内のセルを選択すると、スピル範囲が境界線で強調表示されます。 また、セル H10:H19 の数式が灰色表示されている場合があります。 参照用のセルだけなので、数式を調整する場合は、マスター数式が含まれるセル H10 を選択する必要があります。

  • 単一セル配列数式

    =SUM(F10:F19*G10:G19) で総計を計算する単一セル配列数式

    サンプル ブックのセル H20 に 、=SUM(F10:F19*G10:G19)を入力するか、コピーして貼り付け、Enter キーを 押します

    この場合、Excelの値 (セル範囲 F10 ~ G19) を乗算し、SUM 関数を使用して合計を合計します。 この結果、売上の総計である 1,590,000 円が求められます。

    この例から、配列数式がいかに便利であるかがわかります。 たとえば、1,000 行のデータがあるとします。 単一のセルに配列数式を作成すると、数式を 1,000 行分下にドラッグしなくても、そのデータの一部またはすべてを集計できます。 また、セル H20 の単一セル数式は、複数セルの数式 (セル H10 ~ H19 の数式) とは完全に独立しています。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 H20 の数式に影響を与えることなく、列 H の他の数式を変更できます。 また、結果の精度を検証するのに役立つ、このような独立した合計を用意するのも良い方法です。

  • 動的配列数式には、次の利点があります。

    • 一貫性    H10 のセルを下方向にクリックすると、同じ数式が表示されます。 この一貫性が、正確さの向上に役立ちます。

    • 安全性:    複数セルの配列数式のコンポーネントを上書きできない。 たとえば、セル H11 をクリックし、Delete キーを押します。 Excelの出力は変更されません。 変更するには、配列の左上のセル、またはセル H10 を選択する必要があります。

    • ファイル サイズを小さくする    多くの場合、複数の中間数式ではなく、1 つの配列数式を使用できます。 たとえば、自動車販売の例では、1 つの配列数式を使用して列 E で結果を計算します。=F10*G10、F11*G11、F12*G12 などの標準数式を使用した場合は、11 種類の数式を使用して同じ結果を計算したとします。 これは大したことではありませんが、合計で何千行もの行があった場合は、どうでしょうか。 そうすると、大きな違いを生み出す可能性があります。

    • 効率性   データを 1 か所にまとめて保存すれば、ディスク領域を節約できます。 配列関数は、複雑な数式を効率的に作成する方法です。 配列数式 =SUM(F10:F19*G10:G19) は、=SUM(F10*G10,F11*G11,F12*G12,F) と同じです。 13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19)。

    • スピル    動的配列数式は、出力範囲に自動的にスピルします。 ソース データがテーブル内にあるExcel、データを追加または削除すると、動的配列数式のサイズが自動的に変更されます。

    • #SPILL!error    動的な配列では 、#SPILL! エラーが発生しましたは、意図したスピル範囲が何らかの理由でブロックされた状態を示します。 ブロックを解決すると、数式が自動的にスピルします。

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

={1,2,3,4,5} または ={"January","February","March"}

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

水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。 SEQUENCE関数を使用して配列定数を自動的に生成する例と、手動で入力した配列定数を示します。

  • 水平定数を作成する

    前の例のブックを使用するか、または新しいブックを作成します。 空のセルを選択し 、「=SEQUENCE(1,5)」と入力します。 SEQUENCE 関数は 、={1,2,3,4,5}と同じ 1 行 5 列の配列を構築します。 次の結果が表示されます。

    =SEQUENCE(1,5) または ={1,2,3,4,5} を使用して水平配列定数を作成する

  • 垂直定数を作成する

    空白セルの下に空白のセルを選択し 、=SEQUENCE(5)、または ={1;2;3;4;5}と入力します。 次の結果が表示されます。

    =SEQUENCE(5)、または ={1;2;3;4;5} で垂直配列定数を作成します。

  • 2 次元定数を作成する

    右側と右側に空白のセルを選択し 、「=SEQUENCE(3,4)」と入力します。 次の結果が表示されます。

    =SEQUENCE(3,4) を使用して 3 行 4 列の配列定数を作成する

    または ={1,2,3,4;5,6,7,8;9,10,11,12} と入力できますが、セミコロンとコンマを入れる場所に注意する必要があります。

    ご覧のように、SEQUENCE オプションは、配列定数値を手動で入力する場合と大きな利点があります。 主に、時間を節約できますが、手動入力によるエラーを減らすのにも役立ちます。 また、特にセミコロンをコンマ区切り文字と区別するのは難しい場合があるので、読みやすくなります。

より大きな数式の一部として配列定数を使用する例を次に示します。 サンプル ブックで、数式ワークシートの 定数に 移動するか、新しいワークシートを作成します。

セル D9 では 、=SEQUENCE(1,5,3,1)と入力しましたが、セル A9:H9 に 3、4、5、6、7 を入力できます。 特定の数値の選択について特別な情報は何もありません。区別のために 1 ~ 5 以外の値を選択しました。

セル E11 に 、=SUM(D9:H9*SEQUENCE(1,5))を入力するか 、=SUM(D9:H9*{1,2,3,4,5})と入力します。 数式は 85 を返します。

数式で配列定数を使用します。 この例では、=SUM(D9:H(*SEQUENCE(1,5)) を使用しました。

SEQUENCE 関数は、配列定数 {1,2,3,4,5} に相当する関数を構築します。 Excelはかっこで囲まれた式に対して操作を実行します。次の 2 つの要素は D9:H9 のセル値と乗算演算子 (*) です。 この時点で、格納された値に対応する定数の値を掛ける数式が実行されます。 これは、次の式に相当します。

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5), または =SUM(3*1,4*2,5*3,6*4,7*5)

最後に、SUM 関数は値を加算し、85 を返します。

格納された配列の使用を回避し、操作全体をメモリ内に保持するには、それを別の配列定数に置き換える必要があります。

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

配列定数で使用できる要素

  • 配列定数には、数値、テキスト、論理値 (TRUE、FALSE など)、エラー値 (例: #N/A) を含めることができます。 数値は、整数、10 進数、および科学形式で使用できます。 テキストを含める場合は、引用符 ("text") で囲む必要があります。

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

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

[名前の定義] >定義>数式]に移動します。 [名前] ボックスに 「Quarter1」と入力します。 [参照範囲] ボックスに次の定数を入力します (中かっこを手動で入力してください)。

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

ダイアログ ボックスは次のように表示されます。

[数式] から名前付き配列定数を追加>定義された名前>名前マネージャー> New

[OK]をクリックし、3 つの空白セルを含む任意の行を選択し、「=Quarter1」と入力します

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

数式で名前付き配列定数 (=Quarter1 など) を使用します。ここで、Quarter1 は ={"January","February","March"} と定義されています。

結果を水平方向ではなく垂直方向にスピルする場合は、 =TRANSPOSE(Quarter1) を使用できます

12 か月の一覧を表示する場合は、計算書を作成するときに使用する場合と同様に、SEQUENCE 関数を使用して今年の基準を設定できます。 この関数の概要は、月だけが表示されているにもかかわらず、他の計算で使用できる有効な日付が背後にあることです。 これらの例は、サンプル ブックの 名前付き配列定数 ワークシート クイック サンプル データセット ワークシートに表示されます。

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

TEXT、DATE、YEAR、TODAY、SEQUENCE 関数の組み合わせを使用して、12 か月の動的なリストを作成する

この関数は 、DATE 関数を使用して現在の年に基づいて日付を作成し、SEQUENCE は 1 ~ 12 の 1 ~ 12 の配列定数を作成し 、TEXT 関数は表示形式を "mmm" (Jan、Feb、Mar など) に変換します。 1 月などの月全体の名前を表示する場合は、"mmmm" を使用します。

名前付き定数を配列数式として使用する場合は、Quarter1 ではなく =Quarter1 のように、必ずしも等号を入力してください。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 最後に、関数、テキスト、数値の組み合わせを使用できます。 すべては、取得するクリエイティブな方法によって異なります。

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

  • 配列内の各項目を複数指定する

    「=SEQUENCE(1,12)*2」、または「={1,2,3,4;5,6,7,8;9,10,11,12}*2」と入力します。

    ( / ) で除算し( +)で加算し、 ( - を使用して減算できます

  • 配列の各項目を 2 乗する

    「=SEQUENCE(1,12)^2」、または「={1,2,3,4;5,6,7,8;9,10,11,12}^2」と入力します。

  • 配列内の 2 乗項目の平方根を見つける

    Enter =SQRT(SEQUENCE(1,12)^2)、または =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • 1 次元の行を転置する

    「=TRANSPOSE(SEQUENCE(1,5))」、または「=TRANSPOSE({1,2,3,4,5}」と入力します。

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

  • 1 次元の列を転置する

    「=TRANSPOSE(SEQUENCE(5,1))」、または「=TRANSPOSE({1;2;3;4;5}」と入力します。

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

  • 2 次元定数を転置する

    「=TRANSPOSE(SEQUENCE(3,4))、または=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})と入力します。

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

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

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

    次の例では、配列数式を使用して既存の配列から新しい配列を作成する方法について説明します。

    「=SEQUENCE(3,6,10,10)、または={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}と入力します。

    数値の配列を作成する場合は、「10」と入力する前に「 { (開始中かっこ)、180 を入力した後に } (閉じ中かっこ)」と入力してください。

    次に、空白 セルに「=D9#」、または 「=D9:I11」と入力します。 3 x 6 のセル配列が、D9:D11 に表示されるのと同じ値で表示されます。 # 記号は、スピル 範囲演算子 と呼ばれるので、配列範囲全体を入力する代わりに Excel の方法で参照できます。

    スピル範囲演算子 (#) を使用して既存の配列を参照する

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

    スピルした配列数式の結果を取得し、その結果をコンポーネントパーツに変換できます。 セル D9 を選択し 、F2 キーを押 して編集モードに切り替えます。 次に、F9 キーを押してセル参照を値に変換し、Excel定数に変換します。 Enter キーを すと、数式 =D9#が ={10,20,30;40,50,60;70,80,90} になる必要があります。

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

    次の例は、セル範囲の文字数をカウントする方法を示しています。 これにはスペースが含まれます。

    範囲内の文字の総数と、テキスト文字列を操作する他の配列をカウントする

    =SUM(LEN(C9:C13))

    この場合 、LEN 関数は 、範囲内の各セルの各テキスト文字列の長さを返します。 その後、SUM 関数によってこれらの値が加算され、結果が表示されます (66)。 平均文字数を取得する場合は、次のコマンドを使用できます。

    =AVERAGE(LEN(C9:C13))

  • 範囲 C9:C13 の最も長いセルの内容

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    この数式が機能するのは、データ範囲のすべてのセルが単一の列に含まれる場合だけです。

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

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

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

    この場合の match type 引数は 0 です。 一致の種類には、1、0、または -1 の値を指定できます。

    • 1 - ルックアップの値以下の最大の値を返します。

    • 0 - 参照値と完全に等しい最初の値を返します。

    • -1 - 指定した参照値以上の最小の値を返します。

    • 照合の種類を指定しなかった場合は、1 が指定されたと見なされます。

    最後に 、INDEX 関数は 、配列と、その配列内の行番号と列番号という引数を受け取ります。 セル範囲 C9:C13 は配列を提供し、MATCH 関数はセルアドレスを提供し、最後の引数 (1) は、値が配列の最初の列から取得される値を指定します。

    最小のテキスト文字列の内容を取得する場合は、上記の例の MAX を MIN に置き 換えてください

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

    この例では、セル範囲の 3 つの最小の値を検索する方法を示します。セル B9:B18has 内のサンプル データの配列は =INT(RANDARRAY(10,1)*100)で作成されています。 RANDARRAY は揮発性の関数なので、計算を実行するごとに新しい乱数のセットExcel注意してください。

    Excel値 =SMALL(B9#,SEQUENCE(D9)) を検索する配列数式を作成します。

    「=SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})」と入力します。

    この数式では、配列定数を使用して SMALL 関数を 3 回評価し、セル B9:B18 に含まれる配列内の最小 3 つのメンバーを返します。3 はセル D9 の変数値です。 他の値を検索するには、SEQUENCE 関数の値を増やしたり、定数に引数を追加したりします。 SUMAVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

    範囲内の最大の値を検索するには、SMALL 関数を LARGE 関数 に 置き換える必要があります。 次の例では、ROW 関数と INDIRECT 関数も使用しています。

    「=LARGE(B9#,ROW(INDIRECT("1:3"))))、または=LARGE(B9:B18,ROW(INDIRECT("1:3"))))と入力します。

    ここで、ROW 関数と INDIRECT 関数について簡単に説明しておきます。 ROW 関数を使用すると、連続する整数の配列を作成できます。 たとえば、空を選択し、次を入力します。

    =ROW(1:10)

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

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

    INDIRECT 関数は、引数としてテキスト文字列を使用します (範囲 1:10 が引用符で囲まれている理由です)。 行の挿入、または配列数式の移動を行っても、Excel によって文字列値が調整されることはありません。 この結果、ROW 関数は、常に目的の整数の配列を生成するようになります。 SEQUENCE も同様に簡単に使用できます。

    =SEQUENCE(10)

    前に使用した数式 (=LARGE(B9#,ROW(INDIRECT("1:3")) ) を調べ、内側のかっこから始め、外側に向かって作業しましょう。INDIRECT 関数は、値 1 ~ 3 のテキスト値のセットを返します。 ROW 関数は、3 つのセル列配列を生成します。 LARGE 関数はセル範囲 B9:B18 の値を使用し、ROW 関数によって返される参照ごとに 1 回、3 回評価されます。 さらに値を検索する場合は、INDIRECT 関数により大きなセル範囲を追加します。 最後に、SMALL の例と同様に、SUM や AVERAGE などの他の関数でこの数式を使用できます。

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

    エラー値をExcel値を含む範囲を合計しようとしても、この関数の SUM 関数は#VALUE。 または #N/A。 この例では、エラーを含む Data という名前の範囲の値を合計する方法を示します。

    配列を使用してエラーに対処します。 たとえば、=SUM(IF(ISERROR(Data),"",Data) は、エラーが含まれる場合でも、Data という名前の範囲を合計#VALUE! または #NA!

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

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

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

    この例は前の数式と同様ですが、Data という名前の範囲のエラー値をフィルター処理するのではなく、返します。

    =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,売上)) は、Sales という範囲の 0 より大きいすべての値を合計します。

たとえば、次の配列数式は、Sales という名前の範囲の正の整数を合計します。これは、上の例のセル E9:E24 を表します。

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

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

複数の条件を満たす値を合計することもできます。 たとえば、次の配列数式は、0 より大きい 値と 2500 より小さい値を計算します。

=SUM((Sales>0)*(Sales<2500)*(Sales))

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

OR 条件を使用する配列数式を作成することもできます。 たとえば、0 または 2500より小さい値を合計できます。

=SUM(IF((Sales>0)+(Sales<2500),Sales))

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

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

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

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

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

サンプル ブックの 「データセット間の違い」ワークシートにも同様の 例があります

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

  • 複数セルの配列数式

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

営業 担当者

Car Type

販売数

単価

売上合計

川井

セダン

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

E 列の合計を配列数式により計算

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

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

=SUM(C2:C11*D2:D11)

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

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

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

  • 一貫性    セル 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) と同じです。

重要: 配列 数式を入力する必要がある場合は、Ctrl キーと Shift キーを押しながら Enter キーを押します。 これは、単一セル数式と複数セル数式の両方に適用されます。

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

  • 数式を入力するに、結果の格納先のセル範囲を選択します。 これは、複数セルの配列数式を作成する際に、セル 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セットを追加します。

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

    数式の水平配列定数

垂直定数を作成する

  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}

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

    配列数式の 2 次元配列定数

数式で定数を使用する

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

  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

    セル C1 ~ E3 に 3x3 のセル配列が表示され、C8 ~ E10 と同じ値が表示されます。

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

  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) メンバーを返します。さらに値を検索するには、定数に引数を追加します。 SUMAVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。

=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(データ))

受信確認

この記事の一部は、Colin Wilcox によって書かれた一連の Excel Power User 列に基づいており、Excel 2002 年の数式の第 14 章と 15 章 (Excel MVP の John Walken Excel によって書かれた本) から修正されました。

補足説明

Excel 技術コミュニテでは、いつでも専門家に質問できます。また、Answers コミュニティでは、サポートを受けられます。

関連項目

動的配列とスピル配列の動作

動的配列数式と従来の CSE 配列数式

FILTER 関数

RANDARRAY 関数

SEQUENCE 関数

SORT 関数

SORTBY 関数

UNIQUE 関数

Excel での #SPILL! エラー

暗黙的な交差演算子:@

数式の概要

ヘルプを表示

スキルを磨く
トレーニングの探索
新機能を最初に入手
Microsoft Insider に参加する

この情報は役に立ちましたか?

翻訳品質にどの程度満足していますか?

どのような要因がお客様の操作性に影響しましたか?

その他にご意見はありますか?(省略可能)

フィードバックをお送りいただきありがとうございます!

×