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

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

注: 最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。 このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。 簡単に参照できるように、こちらに英語の記事があります。

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

Office 365の 2018 の年 9 月の更新プログラム以降では、複数の結果を返すことができるすべての数式が自動的に当てはまりません、縦または横に隣接するセルします。 この動作の変更は、いくつかの新しい動的な配列関数も伴います。 動的な配列数式では、既存の関数や、動的配列関数を使用しているかどうかが必要に限ら、1 つのセルに入力し、 Enterキーを押して [承諾] します。 以前の場合、従来の配列数式は、まず、全体の出力範囲を選択し、 Ctrl + Shift + Enterに数式を確認する必要があります。 これらはいるCSE数式と呼ばれます。

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

  • サンプルのデータセットを簡単に作成します。

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

  • 範囲内の下位の値など、特定の条件を満たす数値のみを合計またはに上限と下限の間に位置する数値。

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

次の例では、複数セルおよび単一セルの配列数式を作成する方法を示します。 可能であれば、動的配列関数と動的と従来の両方の配列として入力されている既存の配列数式の例が掲載されています。

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

すべての配列数式の例では、この記事でのブックの例をダウンロードしてください。

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

  • 複数セルの配列数式

    セル h10 の内容では複数セルの配列関数 = F10:F19 * G10:G19 の車の値を計算する販売単価

  • クーペとセダン販売員ごとの売上合計を計算しているを入力して次のとおり= F10:F19 * G10:G19セル 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 の 1 つのセルの数式が完全には、複数のセルの数式 (セル h10 の内容を H19 ~ 数式) の独立したことを確認します。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 H20 の数式に影響を与えずに、列 H の他の数式を変更する可能性があります。 できますが、次のように関係なくの合計をお勧めと、結果の精度の範囲を検証するのに役立ちます。

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

    • 一貫性    下のセル h10 の内容のいずれかをクリックすると、同じ式が表示されます。 この一貫性が、正確さの向上に役立ちます。

    • 安全性:    複数セルの配列数式のコンポーネントを上書きすることはできません。 たとえば、H11 のセルをクリックし、Del キーを押します。 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,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19) します。

    • このように    動的な配列数式は当てはまりません出力範囲を自動的にします。 Excel のテーブルのソース データの場合は、[、動的な配列数式は自動的に変更を追加するか、データを削除するとします。

    • #SPILL です。エラー    動的な配列が導入された、 #SPILL! エラー、のいくつかの理由で、目的の算術範囲がブロックされていることを示します。 進行を妨げているを解決すると、数式が自動的にこぼしたします。

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

{1,2,3,4,5} =または= {「月」,「月」,「3 月」}

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

水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。 配列定数を自動的に生成するシーケンス関数を使用する例を紹介ほかに配列定数を手動で入力します。

  • 水平定数を作成する

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

    =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) に配列定数を 4 列 3 行を作成する.

    入力することもできます: {1,2,3,4; 5,6,7,8; 9,10,11,12} = またはカンマとセミコロンを配置するには注意が必要なします。

    わかるように、[シーケンス] オプションでは、利点、配列定数の値を手動で入力をします。 主に、保存、時間が、手動で入力するからエラーを軽減することができます。 特にセミコロンとしてハード コンマ区切り文字を区別することも、読みやすくられます。

使用のポイントが大きく数式の一部として定数を配列例を示します。 サンプルのブックで、数式に定数のワークシートを移動または新しいワークシートを作成します。

セル 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))

シーケンス関数は、配列定数 1,2,3,4,5 {0} の同等の設定を作成します。 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 を返します。

格納された配列を使用しないようにして、完全にメモリ内の操作を保持] には、もう 1 つの配列定数を使用した置き換えることができます。

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5))、または=SUM({3,4,5,6,7}*{1,2,3,4,5})

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

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

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

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

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

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

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

新しい数式 _gt 定義された名前 _gt 名前の管理 _gt から名前付き配列定数を追加します。

[OK] をクリックし、次の 3 つの空白セルを含むいずれかの行を選択= 1 四半期に入力します。

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

数式で名前付き配列定数を使用して、このような {「月」、「月」、「3 月」} = 1 四半期が定義されている、1 四半期を =

水平方向にではなく、垂直方向に移す必要結果にした場合は、 = (1 四半期) を入れ替える使用できます。

財務ステートメントを作成するときに使用するように、12 か月の一覧を表示する場合は、シーケンス関数では、現在の年をオフのいずれかを作成できます。 便利なことについては、この関数は、月だけを表示している場合でもが他の計算で使用できるその後ろに無効な日付です。 ブックの例でクイック サンプル データセット配列定数の名前ワークシートに次の例が表示されます。

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

テキスト、日付、現在、年シーケンス関数の組み合わせを使用して、12 か月間の動的なリストを作成するには

現在の年に基づいて日付を作成するのには、 DATE 関数を使用してこのし、[シーケンスを作成配列定数を 1 から 12 月 12 月のTEXT 関数は、"mmm"を表示形式を変換します。 (1 月、年 2 月、3 月など)。 年 1 月] など、完全な月名を表示したい場合は、"mmmm"を使用します。

名前付き定数を配列数式として使用するときに、等号 (=) を入力して、= 1 四半期だけでなく、1 四半期します。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 最後に、関数、テキストと数字の組み合わせを使用してできることに注意してください。 全体を表示する方法クリエイティブなかによって決まります。

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

  • 配列の各複数の項目

    入力= シーケンス (1,12) * 2、または= {1,2,3,4; 5,6,7,8 9,10,11,12} * 2

    (/) と分割、(+) を追加、および (-) を減算するもことができます。

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

    入力シーケンス (1,12) = ^2、または= {1,2,3,4; 5,6,7,8 9,10,11,12} ^2

  • 配列の平方和のアイテムの平方根を検索します。

    入力=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))、または= 行列を入れ替える ({1、2、3; 4; 5})を入力します。

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

  • 2 次元定数を転置する

    =TRANSPOSE(SEQUENCE(3,4))、または= 行列を入れ替える ({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)

    一致の種類] 引数は、この場合は 0 です。 一致の種類を 1、0、または-1 の値を使用できます。

    • 1 - 参照以下の最大の値を返します val。

    • 0 ~ が参照値に等しい最初の値を返します

    • -1 - が参照を指定した値以上にある最小値を返します。

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

    INDEX 関数は、次の引数は、最後に、: 配列と配列内の行と列番号。 C9:C13 のセル範囲には、配列、MATCH 関数には、セルのアドレスが用意されています (1) の最後の引数が配列の左端の列からは、値であることを指定します。

    文字列が含まれる最小の内容を取得する場合は、 MINで、上の例 MAX を置換するとします。

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

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

    Excel の n 番目に含まれる最小値を検索する配列数式: =SMALL(B9#,SEQUENCE(D9))

    入力=SMALL(B9#,SEQUENCE(D9)小規模の = (B9:B18、{1、2、3})

    この数式は、3 回SMALL 関数を評価し、3 はどこにセル D9 の変数値、セル B9:B18 に含まれる配列に含まれる最小の 3 つのメンバーを返す配列定数を使用します。 その他の値を見つけるには、シーケンス関数の値を大きくまたは定数に複数の引数を追加できます。 SUMAVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。

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

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

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

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

    大 (B9 の場合、行 (INDIRECT (「1:3」))) =を入力または大の = (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(10)

    以前のバージョンを使用している数式を調べてみましょう-大 (B9 の場合、行 (INDIRECT (「1:3」))) =-内部かっこから開始して外側:、INDIRECT 関数値を返します一連のテキスト値をここでは、1 ~ 3 します。 ROW 関数には、3 つのセルの列の配列が生成されます。 LARGE 関数では、B9:B18、セル範囲の値を使用して、ROW 関数で返される参照ごとに 1 回、3 回評価されます。 その他の値を検索する場合は、INDIRECT 関数に大きいセル範囲を追加します。 最後に、小さい例と同様、この数式で SUM、AVERAGE などの他の関数を使用できます。

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

    #Value! など、エラー値を含む範囲を合計するしようとすると、Excel では、SUM 関数は機能しません。 または、#n/a します。 この例では、エラーが含まれるデータを名前付き範囲の値を合計する方法を示します。

    エラーを処理するには、配列を使用します。 たとえば、=SUM(IF(ISERROR(Data),"",Data) #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(Sales>0,Sales)) は、Sales という範囲内で 0 よりも大きいすべての値が合計されます。

たとえば、この配列数式は、上記の例では、セル E9:E24 を表す"売上"という名前の範囲内の正の整数だけを合計します。

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

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

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

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

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

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

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

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

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

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

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

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

データセットの相違点のワークシートにブックの例のような例をご覧ください。

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

  • 複数セルの配列数式

下のテーブルの中から全体をコピーして、空のワークシートのセル 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キーを押して、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 の 1 つのセルの数式が完全には、複数のセルの数式 (セル E2 E11 ~ 数式) の独立したことを確認します。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 E 列の数式を変更または D13 の数式に影響を与えずに、その列を削除できます。

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

  • 一貫性    セル 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キーを押します。 これは、1 つのセルと、複数のセルの数式に適用されます。

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

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

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

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

  • 配列数式を削除するには、数式範囲全体 (たとえば、 e2: e11) を選択し、 Delキーを押します。

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

場合によっては、配列数式の拡張が必要になる場合もあります。 既存の配列の範囲の最初のセルを選択して、会話を続ける範囲全体を選択するまでに、数式を拡張すること。 数式を編集し、 ctrl キーを押しながら shift キーを押しながら ENTERキーを押して、数式の範囲を調整した後に数式を確認、 F2キーを押します。 キーは、配列内の左上のセル範囲全体を選択します。 左上のセルは、編集するものです。

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

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

  • ブックの他のユーザーは、数式を理解できない可能性があります。 実際には、配列数式は通常いないで説明されているワークシートです。 このため、他のユーザーにブックを変更する場合は、する必要があります配列数式を回避するかユーザーは、配列数式に関する補足し、する必要がある場合、それらを変更する方法を理解するかどうかを確認します。

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

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

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

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

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

1 行に配列を次のとおりです: {0} 1,2,3,4 します。 {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}

    この例では、中かっこと右中かっこ ({}) を入力する必要がありますがし、の 2 番目のセットが 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. [数式] タブの [定義された名前] で [名前の定義] をクリックします。
    [名前の定義] ダイアログ ボックスが表示されます。

  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. Enter キーを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

    C8 ~ E10 に同じ値を持つセル C1 ~ E3 のセルの 3 x 3 の配列が表示されます。

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

  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 を選択し、セル a2: a6 (66) の文字の合計数を表示、 ctrl キーを押しながら shift キーを押しながら Enterキーを押します。

  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 回SMALL関数を評価し、最小 (1)、2 番目に小さな (2)、a1: a10 に複数の値を検索するセルに含まれる配列の 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 関数は、常に目的の整数の配列を生成するようになります。

以前のバージョンを使用している数式を見てみましょう-大 = (A5:A14,ROW(INDIRECT("1:3"))) -内部かっこから開始して外側: INDIRECT関数値を返します一連のテキスト値をここでは、1 ~ 3 します。 ROW関数には、3 つのセルの列の配列が生成されます。 関数では、A5:A14、セル範囲の値を使用し、 ROW関数で返される参照ごとに 1 回、3 回評価されます。 単票形式の 3 つのセルの配列には、値 3200、2700、および 2000 が返されます。 その他の値を検索する場合は、 INDIRECT関数に大きいセル範囲を追加します。

前の例として、合計平均など、他の機能には、この数式を使用することができます。

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

以前のテキスト文字列の例に戻るには、空のセルで次の数式を入力し、 ctrl キーを押しながら shift キーを押しながら Enterキーを押して移動します。

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

文字列「bunch of セル」が表示されます。

この式を、内側の要素から順番に詳しく確認してみましょう。 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、によって執筆し、チャプター 14 と Excel 2002 数式 John Walkenbach、元の Excel MVP が作成したブックの 15 から Excel Power [User] 列に基づいています。

補足説明

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

関連項目

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

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

FILTER 関数

RANDARRAY 関数

SEQUENCE 関数

SINGLE 関数

SORT 関数

SORTBY 関数

UNIQUE 関数

Excel での #SPILL! エラー

数式の概要

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

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

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

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

×