スキップしてメイン コンテンツへ
配列数式のガイドラインと例

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

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

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

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

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

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

  • 範囲内の最小値や、上限と下限の間にある数値など、特定の条件を満たす数値だけを合計します。

  • 指定した範囲の値のすべての n 番目の値を合計します。

次の例では、複数セルおよび単一セルの配列数式を作成する方法について説明します。 可能であれば、いくつかの動的配列関数、および dynamic と legacy の両方の配列として入力された既存の配列数式について例を示しています。

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

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

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

  • 複数セルの配列数式

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

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

    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つの配列数式を使うことができます。 たとえば、car の売上例では、1つの配列数式を使用して、列 E の結果が計算されます。= F10 * G10、F11 * G11、F12 * G12 などの標準の数式を使用していた場合は、同じ結果を計算するために11個の異なる数式を使用している可能性があります。 これは大きな問題ではありませんが、合計する行数が多すぎるとどうなりますか? そのため、大きな違いがあります。

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

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

    • #SPILL!誤差    動的配列によって#SPILL! エラーが発生しましたこの値は、目的のスピル範囲が何らかの理由でブロックされていることを示します。 ボトルネックを解決すると、数式が自動的にスピルされます。

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

= {1, 2, 3, 4, 5} or = {"January ", "3 月", "3 月"}

カンマを使用して項目を区切った場合は、水平配列 (行) が作成されます。 セミコロンを使用して項目を区切った場合は、垂直配列 (列) が作成されます。 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列の列配列定数を作成する

    また、「or = {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 (1, 5, 3, 1) * sequence (1, 5))、または= sum ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

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

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

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

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

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

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

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

名前付き配列定数を数式から追加する > 定義された名前 > Name Manager > New

[ OK] をクリックし、3つの空白セルがある行を選択して、「 = 第1四半期」と入力します。

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

第1四半期 = {"January", "2 月", "3 月" のように定義された数式で、名前付き配列定数を使用します。

結果を水平方向ではなく垂直方向にスピルする場合は、 =転置(第1四半期)を使用できます。

財務諸表の作成に使用する場合と同じように、12か月のリストを表示する場合は、SEQUENCE 関数を使用して現在の年を1つにすることができます。 この関数の詳細については、表示されているのは月だけですが、他の計算で使用できる有効な日付があることを意味します。 これらの例については、「ブックの例」の「名前付き配列定数」および「クイックサンプルデータセット」ワークシートをご覧ください。

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

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

Date 関数を使用して、現在の年に基づいて日付を作成します。この場合、配列定数は1月から12月の配列定数を作成し、"mmm" (1 月、2月、3月など)に変換します。 月全体の名前を表示する場合は、"mmmm" を使用します。

配列数式として名前付き定数を使う場合は、第1四半期だけでなく、= 第1四半期のように等号を入力してください。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 最後に、関数、テキスト、数値の組み合わせを使用できることを覚えておいてください。 これは、どのような創造力が必要かによって異なります。

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

  • 配列内の複数の項目

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

    (/) を使って除算したり、(+) を追加したり、(-) で減算したりすることもできます。

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

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

  • 配列内の2乗の要素の平方根を求めます。

    Enter =sqrt(シーケンス (1, 12) ^ 2)、または= sqrt ({1, 2, 3, 4; 5, 6, 7, 8; 5, 6, 7, 8

  • 1 次元の行を転置する

    Enter = 転置 (SEQUENCE (1, 5))、または= 転置 ({1, 2, 3, 4, 5})

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

  • 1 次元の列を転置する

    Enter = 転置 (SEQUENCE (5, 1))、または= 転置 ({1; 2; 3; 4; 5})

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

  • 2 次元定数を転置する

    Enter = 転置 (シーケンス (3, 4)), または= 転置 ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12})

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

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

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

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

    Enter = SEQUENCE (3、6、10、10)、または= {10、20、30、40、50、60、70、80、90100110120、130140150160170180}

    「10」を入力する前に「{」 (左中かっこ) を入力してください。数値の配列を作成しているため、「180」と入力します。

    次に、空のセルに「 = D9 #」または「 = D9: I11 」と入力します。 D9: D11 で示されているのと同じ値で、3 x 6 のセルの配列が表示されます。 # 記号は、"こぼれていない範囲" 演算子と呼ばれ、入力しなくても、配列範囲全体を参照することができます。

    空の配列演算子 (#) を使って既存の配列を参照する

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

    こぼれた配列数式の結果を取得して、それをその構成要素に変換できます。 セル D9 を選択し、 F2キーを押して編集モードに切り替えます。 次に、 F9キーを押してセル参照を値に変換します。これで、Excel は配列定数に変換されます。 Enterキーを押すと、数式 "= D9 #" が = {10, 20, 30; 40, 50, 60; 70, 80, 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 関数はセルアドレスを指定し、final 引数 (1) は、値が配列の最初の列から取得されることを指定します。

    最小のテキスト文字列の内容を取得するには、上の例の MAX をMINに置き換えます。

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

    次の例は、セル範囲内で3つの最小値を検索する方法を示しています。たとえば、セル B9: B18has で作成されたデータの配列は= INT (RANDARRAY(10, 1) * 100)です。 RANDARRAY は volatile 関数であるため、Excel で計算されるたびに新しいランダムな数値のセットが取得されます。

    N 番目の最小値を見つけるための Excel 配列数式: = SMALL (B9 #, SEQUENCE (D9))

    Enter = small (b9 #, SEQUENCE (D9), = small (b9: B18, {1; 2; 3})

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

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

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

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

    範囲内の最大値を求めるには、SMALL 関数を大きい関数に置き換えます。 次の例では、ROW 関数と INDIRECT 関数も使用しています。

    Enter = large (b9 #, ROW (indirect ("1: 3"))))、または= large (b9: B18, row (INDIRECT ("1: 3 "))))

    ここで、ROW 関数と INDIRECT 関数について簡単に説明しておきます。 ROW 関数を使用すると、連続する整数の配列を作成できます。 たとえば、空白を選択して enter キーを押すと、次のようになります。

    =ROW(1:10)

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

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

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

    = SEQUENCE (10)

    前に使用した数式を調べていきます。 = LARGE (B9 #, ROW (INDIRECT ("1: 3")))))。間接関数は、一連のテキスト値を返します。この例では、1 ~ 3 の値を返します。 ROW 関数によって、3つのセルの列配列が生成されます。 LARGE 関数は、セル範囲 B9: B18 の値を使用し、ROW 関数によって返された各参照については3回評価されます。 より多くの値を検索するには、INDIRECT 関数により大きなセル範囲を追加します。 最後に、小さな例と同様に、この数式を SUM や AVERAGE などの他の関数と組み合わせて使用できます。

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

    Excel の SUM 関数は、#VALUE などのエラー値を含む範囲を合計しようとしても機能しません。 または #N/A 次の例は、エラーを含むデータという名前の範囲の値を合計する方法を示しています。

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

  • =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, 売上)) を指定すると、売上と呼ばれる範囲内のすべての値が0より大きくなります。

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

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

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

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

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

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

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

= SUM (IF ((売上>0) + (売上<2500), 売上))

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

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

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

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

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

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

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

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

= SUM (1 * (データ<>の MyData))

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

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

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

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

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

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

この例のブックでは、データセットのワークシートの違いについて同様の例を示しています。

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

  • 複数セルの配列数式

下のテーブル全体をコピーし、空のワークシートのセル 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} が表示されます。 

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 をクリックし、 delキーを押します。 セル範囲全体 (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つの行の配列の例: {1, 2, 3, 4}。 {1;2;3;4} は 1 つの列の配列です。 {1,2,3,4;5,6,7,8} は 2 行 4 列の配列です。 2行の配列では、1行目は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}

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

    配列数式の 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 エラー値が表示されます。 逆に、選択したセルの数が少なすぎる場合は、対応するセルのない値が省かれます。

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

配列の各項目を乗算する

  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

    求める

    40

    50

    60

    70

    80

    90

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

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

    =C8:E10

    C8 ~ E10 で示されているのと同じ値で、3 x 3 のセルの配列がセル 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})

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

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

= AVERAGE (小 (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"))))を見てみましょう。間接関数は、テキスト値のセットを返します (この場合は、1 ~ 3 の値)。 ROW 関数によって、3つのセルからなるの配列が生成されます。 LARGE関数はセル範囲 A5: A14 の値を使用し、 ROW関数によって返された各参照については、3回評価されます。 3200、2700、および2000の値は、3つのセルの単票配列に返されます。 より多くの値を検索するには、 INDIRECT関数により大きなセル範囲を追加します。

この数式は、前の例と同様に、 SUMAVERAGEなどの他の関数と共に使用できます。

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

前のテキスト文字列の例に戻るには、空のセルに次の数式を入力し、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関数はセルアドレスを指定し、final 引数 (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))

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

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

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

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

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

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

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

正常

この記事の一部は、Wilcox によって記述された一連の Excel Power User 列に基づいており、Excel 2002 の数式の第14および15によって作成されました。ジョン Walkenbach は、以前の Excel MVP で書かれています。

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

関連項目

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

動的配列数式とレガシ CSE の配列数式

FILTER 関数

RANDARRAY 関数

SEQUENCE 関数

SORT 関数

SORTBY 関数

UNIQUE 関数

Excel での #SPILL! エラー

暗黙的なインターセクション演算子:@

数式の概要

注:  このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 情報が役に立ったかどうか、ご意見をお寄せください。 参考までに、こちらから英語の記事をお読みいただけます。

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

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

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×