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

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel for iPad Excel for iPhone その他…表示数を減らす

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

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

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

サンプルデータセットをすばやく作成します。
セル範囲に含まれる文字数をカウントします。
特定の条件を満たす数値 (範囲の最小値、上限と下限の境界の間にある数値など) のみを合計します。
値の範囲内のすべての N 番目の値を合計します。

次の例では、複数セルおよび単一セル配列の数式を作成する方法を示します。可能な場合は、動的配列関数の一部と、動的配列とレガシ配列の両方として入力された既存の配列数式の例が含まれています。

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

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

Microsoft 365 Begin >>>

複数セル配列と単一セル配列

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

複数セルの配列数式

ここでは、セル H10 に「=F10:F19*G10:G19 」と入力して、各営業担当者のクーペとセダンの合計売上を計算します。

Enter キーを押すと、結果がセル H10:H19 にスピルダウンします。スピル範囲内のセルを選択すると、スピル範囲が境界線で強調表示されていることに注意してください。セル H10:H19 の数式が淡色表示されていることにも気付く場合があります。これらは参照のために存在するため、数式を調整する場合は、マスター数式が存在するセル H10 を選択する必要があります。

単一セル配列の数式

ブック例のセル 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 の結果を計算します。=F10G10、F11G11、F12*G12 などの標準数式を使用していた場合は、11 の異なる数式を使用して同じ結果を計算していました。これは大したことではありませんが、合計で数千行あった場合はどうでしょうか。その後、大きな違いを生み出すことができます。
- 効率性 データを 1 か所にまとめて保存すれば、ディスク領域を節約できます。配列関数は、複雑な数式を作成する効率的な方法です。配列数式 =SUM(F10:F19G10:G19) は、=SUM(F10G10,F11G11,F12G12,F 13G13,F14G14,F15G15,F16G16,F17G17,F18G18,F19*G19)。
- こぼれる 動的配列の数式は自動的に出力範囲にスピルされます。ソースデータが Excel テーブル内にある場合、データを追加または削除すると、動的配列数式のサイズが自動的に変更されます。
- #SPILL!エラー 動的配列で target=”_blank” title=”#SPILL! エラーが発生” rel=”noopener”>#SPILL! エラーが発生しましたは、何らかの理由で意図されたスピル範囲がブロックされていることを示します。ブロックを解決すると、数式が自動的にスピルします。

1 次元および 2 次元配列定数を作成する

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

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

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

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

水平定数を作成する

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

垂直定数を作成する

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

2 次元定数を作成する

右側とその下にある空白のセルを選択し、「 =SEQUENCE(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 を返します。

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

=SUM(D91,E92,F93,G94,H9*5) 、または =SUM(31,42,53,64,7*5)

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

ストアド配列の使用を回避し、操作を完全にメモリ内に保持するには、別の配列定数に置き換えます。

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)) ,または =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 月"}

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

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

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

結果を水平方向ではなく垂直方向にスピルする場合は、= target=”_blank” title=”TRANSPOSE” rel=”noopener”>TRANSPOSE(Quarter1)を使用できます。

財務諸表の作成時に使用する場合と同様に、12 か月の一覧を表示する場合は、SEQUENCE 関数を使用して現在の年から 1 つを基準にすることができます。この関数に関するきちんとした点は、月のみが表示されているにもかかわらず、その背後に他の計算で使用できる有効な日付があるということです。これらの例は、サンプルブックの 名前付き配列定数 ワークシートと クイックサンプルデータセット ワークシートにあります。

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

これにより target=”_blank” title=”、DATE 関数” rel=”noopener”>、DATE 関数を使用して現在の年に基づいて日付を作成し、SEQUENCE は 1 月から 12 月の 12 までの配列定数を作成し、 target=”_blank” title=”TEXT 関数” rel=”noopener”>TEXT 関数は表示形式を "mmm" (1 月、2 月、3 月など) に変換します。 1 月などの完全な月の名前を表示する場合は、"mmmm" を使用します。

配列数式として名前付き定数を使用する場合は、必ず等号を入力してください。=Quarter1 のように、Quarter1 だけではありません。等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。最後に、関数、テキスト、数値の組み合わせを使用できることに注意してください。それはすべて、あなたが取得したいどのように創造的に依存します。

配列定数の使用例

配列数式で配列定数を使用する方法を示す例をいくつか紹介します。一部の例では target=”_blank” title=”、TRANSPOSE 関数” rel=”noopener”>、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

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

「= target=”_blank” title=”SQRT” rel=”noopener”>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 と同じ値で表示されます。 # 記号は target=”_blank” title=”スピルされた範囲演算子” rel=”noopener”>スピルされた範囲演算子と呼ばれ、入力する代わりに配列範囲全体を参照する Excel の方法です。

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

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

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

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

=SUM(LEN(C9:C13))

この場合、 target=”_blank” title=”LEN 関数” rel=”noopener”>LEN 関数は、範囲内の各セル内の各テキスト文字列の長さを返します。次に、SUM 関数によってこれらの値が一緒に追加され、結果が表示されます (66)。平均文字数を取得する場合は、次を使用できます。

=AVERAGE(LEN(C9:C13))

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

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

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

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

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

MAX(LEN(C9:C13)

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

LEN(C9:C13)

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

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

* 0 - ルックアップ値と完全に等しい最初の値を返します

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

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

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

最小のテキスト文字列の内容を取得する場合は、上記の例の MAX を target=”_blank” title=”MIN” rel=”noopener”>MIN に置き換えます。

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

この例では、セル範囲で 3 つの最小値を検索する方法を示します。セル B9:B18has のサンプルデータの配列は、 =INT( target=”_blank” title=”RANDARRAY” rel=”noopener”>RANDARRAY(10,1)*100) で作成されています。 RANDARRAY は揮発性関数であるため、Excel が計算するたびに新しい乱数セットを取得します。

「=SMALL(B9#,SEQUENCE(D9) ,=SMALL(B9:B18,{1;2;3})

この数式では、配列定数を使用して target=”_blank” title=”SMALL 関数” rel=”noopener”>SMALL 関数を 3 回評価し、セル B9:B18 に含まれる配列内の最小の 3 つのメンバーを返します。3 はセル D9 の変数値です。より多くの値を見つけるには、SEQUENCE 関数の値を増やすか、定数にさらに引数を追加します。 target=”_blank” title=”SUM” rel=”noopener”>SUM、 target=”_blank” title=”AVERAGE” rel=”noopener”>AVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。次に例を示します。

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

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

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

範囲内の最大値を見つけるには、SMALL 関数を target=”_blank” title=”LARGE 関数” rel=”noopener”>LARGE 関数に置き換えます。次の例では、 target=”_blank” title=”ROW” rel=”noopener”>ROW 関数と target=”_blank” title=”INDIRECT” rel=”noopener”>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(データ),"",データ))

この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。内側の関数から順に説明すると、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))

この形式が機能するのは、TRUE1=1 で、FALSE1=0 であるためです。

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

条件に基づいて値を集計することが必要になる場合があります。

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

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

IF 関数は、正の値と false 値の配列を作成します。 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 関数に渡します。

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

この配列数式では、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))

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

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

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

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

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

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

サンプルブックの [ データセット間の相違点 ] ワークシートには、同様の例があります。

Microsoft 365 End <<<

Windows Begin >>>

複数セルおよび単一セル配列の数式

複数セルの配列数式

下の表全体をコピーし、空白のワークシートのセル 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)

各営業担当者のクーペとセダンの合計売上を表示するには、セル E2:E11 を選択し、数式 =C2:C11*D2:D11 を入力し、 Ctrl + Shift + Enter キー を押します。
すべての売上の総計を表示するには、セル 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) を乗算し、 target=”_blank” title=”SUM 関数” rel=”noopener”>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 の結果を計算します。標準の数式 (=C2D2、C3D3、C4*D4….など) を使用していた場合は、11 個の異なる数式を使用して同じ結果を計算していました。

配列数式の構文

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

配列関数は、複雑な数式を作成する効率的な方法です。配列数式 =SUM(C2:C11*D2:D11) は、=SUM(C2D2,C3D3,C4D4,C5D5,C6D6,C7D7,C8D8,C9D9,C10D10,C11D11) と同じです。

配列数式を入力および変更する

重要: 配列数式を入力する必要がある場合は、常に 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 の関数と組み合わせて使用する方法について説明します。

1 次元定数および 2 次元定数を作成する

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

水平定数を作成する

空白のワークシートで、セル A1 から E1 を選択します。
数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します 。

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

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

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

垂直定数を作成する

ブックで、5 行 1 列のセルを選択します。
数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します 。

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

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

2 次元定数を作成する

ブックで、4 列 3 行のセルのブロックを選択します。
数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します 。

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

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

数式で定数を使用する

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

サンプルブックで、新しいワークシートを作成します。
セル A1 に 3 と入力し、B1 に 4 、C1 に 5 、D1 に 6 、E1 に 7 と入力します。
セル 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(A11,B12,C13,D14,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 四半期 」と入力します。
[参照範囲] ボックスに次の定数を入力します (中かっこを手動で入力してください)。

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

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

[OK] をクリックし、3 個の空白セルがある行を選択します。
次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します 。

****=第 1 四半期** **

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

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

トラブルシューティング: 配列定数

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

一部の要素が適切な文字で区切られていない可能性があります。コンマまたはセミコロンを省略した場合、または正しくない場所に配置した場合、配列定数が正しく作成されない場合や、警告メッセージが表示される場合があります。
選択したセルの範囲と、定数の要素の数が一致していない可能性があります。たとえば、6 行 1 列のセルを選択して 5 つのセル用の定数を使用しようとすると、空白セルに #N/A エラー値が表示されます。逆に、選択したセルの数が少なすぎる場合は、対応するセルのない値が省かれます。

配列定数の使用例

配列の各項目を乗算する

新しいワークシートを作成し、4 列 3 行の空のセルのブロックを選択します。
次の数式を入力し、 Ctrl + Shift + Enter キー を押します。

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

配列の各項目を 2 乗する

4 列 3 行の空のセルのブロックを選択します。
次の配列数式を入力し、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 列 5 行の空白セルを選択します。
次の数式を入力し、 Ctrl + Shift + Enter キー を押します。

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

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

1 次元の列を転置する

1 行 5 列の空白セルを選択します。
次の数式を入力し、 Ctrl + Shift + Enter キーを 押します。

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

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

2 次元定数を転置する

3 列 4 行のセルのブロックを選択します。
次の定数を入力し、 Ctrl + Shift + Enter キー を押します。

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

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

基本的な配列数式の使用例

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

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

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

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

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

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

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

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

10	20	30
40	50	60
70	80	90

C1 ～ E3 のセル範囲を選択します。
数式バーに次の数式を入力し、 Ctrl キーを押しながら Shift キーを押しながら Enter キーを押します 。

****=C8:E10** **

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

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

セル C1:C3 が選択されている状態で 、F2 キーを押して編集モードに切り替えます。
F9 キーを押して、セル参照を値に変換します。 Excel により、値が配列定数に変換されます。数式は ={10,20,30 になります。40,50,60;70,80,90} 。
Ctrl キーを押しながら Shift キーを押しながら Enter キーを押 して、配列の数式として配列定数を入力します。

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

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

このテーブル全体をコピーし、ワークシートのセル 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) の文字数の合計を表示します。

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

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

****=SUM(LEN(A2:A6))** **

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

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

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

セル A1:A11 に乱数を入力します。
セル C1 から C3 を選択します。この一連のセルに、配列数式から返された結果を格納します。
次の数式を入力し、 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 関数も使用しています。

セル D1 から D3 を選択します。
数式バーに次の数式を入力し、 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))

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

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

この形式が機能するのは、TRUE1=1 で、FALSE1=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))

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

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

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

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

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

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

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

Windows End <<<

確認

この記事の一部は、Colin Wilcox によって書かれた一連の Excel Power User 列に基づいており、Excel 2002 数式の第 14 章と 15 章から適応されています。これは、元 Excel MVP の John Walkenbach が執筆した書籍です。

補足説明

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

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

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

Microsoft 365 Begin >>>

Microsoft 365 End <<<

Windows Begin >>>

データ

Windows End <<<

確認

補足説明

関連項目