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

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

Excel power ユーザーになると、非配列数式を使用して行うことはできませんが、計算を実行できる配列数式を使用する方法を理解する必要があります。次の記事 Colin Wilcox によって執筆し、チャプター 14 と数式 2002年、John Walkenbach、Excel MVP が作成したブックの 15 から Excel パワー ユーザーの列に基づいて計算されます。

配列数式について

配列数式は、CSE (Ctrl+Shift+Enter) 数式と呼ばれることもあります。これは、Enter キーを押す代わりに、Ctrl キーと Shift キーを押しながら Enter キーを押して数式を完成させるためです。

配列数式を使用する理由

Excel の数式を使用したことがあれば、数式を使ってかなり高度な演算を実行できることがわかります。たとえば、ある期間におけるローンのコスト合計を計算できます。配列数式を使用すると、次のような複雑なタスクを実行できます。

  • セル範囲に含まれる文字数を計算する。

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

  • ある範囲の値のうち、n 個ごとに出現する値の合計を計算する。

配列と配列数式の基礎

配列数式では、配列内の 1 つ以上の項目に対して複数の計算を実行できます。配列は、値の行、値の列、または値の行と列の組み合わせと考えることができます。配列数式は、複数または単一の結果を返します。たとえば、あるセル範囲に配列数式を作成し、この配列数式を使用して、行または列として小計を計算できます。単一のセルに配列数式を配置し、単一の数量を計算することもできます。複数のセルを含む配列数式は複数セルの数式と呼ばれ、単一のセル内の配列数式は単一セルの数式と呼ばれます。

次のセクションでは、複数セルおよび単一セルの配列数式の作成方法の例を示します。

演習

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

複数セルの配列数式

ブラウザーに埋め込まれているブックを次に示します。サンプル データが含まれていますが、埋め込まれたブックでは配列数式を作成または変更できないことを知っておく必要があります (Excel プログラムが必要です)。埋め込まれたブックでは答えを表示でき、配列数式の動作を説明するテキストを参照できますが、配列数式の本当の価値を理解するには、Excel でブックを表示する必要があります。

複数セルの配列数式を作成する
  1. 下のテーブル全体をコピーし、Excel の空のワークシートのセル 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)

  2. クーペとセダンの売上合計を販売員ごとに表示するには、E2:E11 を選び、数式「=C2:C11*D2:D11」を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

  3. すべての売上の総計を表示するには、セル F11 を選び、数式「=SUM(C2:C11*D2:D11)」を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

このブックをダウンロードするには、ブックの一番下にある黒いバーの緑の Excel をクリックします。その後 Excel でファイルを開き、配列数式を含むセルを選択し、Ctrl キーと Shift キーを押しながら Enter キーを押し、数式を実行できます。

Excel で作業している場合、Sheet1 がアクティブであることを確認し、セル E2:E11 を選択します。F2 キーを押して、現在のセル E2 に数式 =C2:C11*D2:D11 を入力します。Enter キーを押すと、数式がセル E2 のみに入力され、165000 と表示されます。Enter キーのみを押す代わりに、数式を入力した後で、Ctrl キーと Shift キーを押しながら Enter キーを押します。今度は、セル E2:E11 に結果が表示されます。数式バーで、数式が {=C2:C11*D2:D11} と表示されることに注意してください。次の表で説明しているように、この表示は配列数式であることを示しています。

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

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

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

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

=SUM(C2:C11*D2:D11)

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

セル G11 内の単一セルの数式は、複数セルの数式 (セル E2 ~ E11 の数式) から完全に独立しています。これは、配列数式 を使用することのもう 1 つの利点である柔軟性です。E 列の数式の変更や、この列全体の削除などを実行しても、セル G11 の数式には影響がありません。

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

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

  • 安全性:    複数セルの配列数式を構成する個々のセルを上書きすることはできません。たとえば、セル E3 をクリックし、Del キーを押すことで、このセルの内容を削除することはできません。セル範囲全体 (E2 ~ E11) を選択し、配列全体に対する数式を変更するか、または配列をそのままにしておく必要があります。安全性を高めるための方法として、Ctrl キーと Shift キーを押しながら Enter キーを押して、数式に対する変更を確認する必要があります。

  • ファイル サイズの小型化:    多くの場合、複数の中間式の代わりに単一の配列数式を使用できます。たとえば、このブックでは、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 キーを押します。配列の一部を変更することができないことを知らせるメッセージが表示されます。

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

  • 配列数式を削除するには、数式全体 (=C2:C11*D2:D11 など) を選択し、Del キーを押します。次に、Ctrl キーと Shift キーを押しながら Enter キーを押します。

  • 複数セルの配列数式に対して空白セルの挿入またはセルの削除を行うことはできません。

配列数式を拡張する

場合によっては、配列数式の拡張が必要になることもあります。この手順は複雑ではありませんが、上のガイダンスに必ず従ってください。

このワークシートで、行 12 ~ 17 に売上の行をいくつか追加してあります。ここでは、これら追加の行が含まれるように配列数式を更新します。

これは必ず Excel デスクトップ プログラムで実行してください (ブックをコンピューターにダウンロード後)。

配列数式を拡張する
  1. このテーブル全体を Excel ワークシートのセル A1 にコピーします。

    販売


    販売 台数


    売上 合計

    川井

    セダン

    5

    33000

    165000

    クーペ

    4

    37000

    148000

    阿藤

    セダン

    6

    24000

    144000

    クーペ

    8

    21000

    168000

    阿部

    セダン

    3

    29000

    87000

    クーペ

    1

    31000

    31000

    山水

    セダン

    9

    24000

    216000

    クーペ

    5

    37000

    185000

    安藤

    セダン

    6

    33000

    198000

    クーペ

    8

    31000

    248000

    池林

    セダン

    2

    27000

    クーペ

    3

    30000

    池山

    セダン

    4

    22000

    クーペ

    1

    41000

    伊藤

    セダン

    5

    32000

    クーペ

    3

    36000

    総計

  2. セル E18 を選び、セル A20 に総計数式の「=SUM(C2:C17*D2:D17)」を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。
    答えは 2,131,000 になります。

  3. 現在の配列数式 (E2:E11) を格納しているセルの範囲と、新しいデータの横の空白セル (E12:E17) を選択します。つまり、セル E2:E17 を選択します。

  4. F2 キーを押して編集モードに切り替えます。

  5. 数式バーで、C11C17 に変更し、D11D17 に変更して、Ctrl キーと Shift キーを押しながら Enter キーを押します。
    セル E2 ~ E11 の数式が更新され、新しいセル (E12 ~ E17) に同じ数式が配置されます。

  6. 配列数式が行 2 ~ 行 17 を参照するように、配列数式 = SUM(C2:C17*D2*D17) をセル F17 に入力し、Ctrl キーと Shift キーを押しながら Enter キーを押して、配列数式を入力します。
    新しい総計が 2,131,000 になります。

配列数式を使用する場合の短所

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

  • 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 行目が 1 、2、3、4 であり、2 行目が 5、6、7、8 です。4 と 5 の間の単一のセミコロンで 2 つの行が区切られています。

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

ページの先頭へ

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

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

水平定数を作成する

  1. 前の例のブックを使用するか、または新しいブックを作成します。

  2. セル A1 ~ E1 を選択します。

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

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

    この場合は、左中かっこと右中かっこ ({ }) を入力する必要があります

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

    数式の水平配列定数

垂直定数を作成する

  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. 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 と同じ値で、3x3 のセル配列がセル C1 ~ E3 に表示されます。

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

  1. セル C1:C3 を選択した状態で、F2 キーを押し、編集モードに切り替えます。
    配列数式は依然として = C8:E10 のままです。

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

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

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

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

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

  2. セル A9 を選んで Ctrl キーと Shift キーを押しながら Enter キーを押し、セル A2:A6 の文字の合計数 (66) を表示します。

  3. セル A12 を選択し、Ctrl キーと Shift キーを押しながら Enter キーを押し、セル A2:A6 の中で内容が最も長いセル (セル A3) を表示します。

データ

この文章は、

それぞれのセルに別れて入力されていますが、

すべてのセルに入力されている

文章を合わせて、

一つの文章にすることができます。

A2:A6 の合計文字数

=SUM(LEN(A2:A6))

最も長いセル (A3) の内容

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

セル A9 で使用される次の数式は、セル A2 ~ A6 内の総文字数 (66) をカウントします。

=SUM(LEN(A2:A6))

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

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

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

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

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

    =SMALL(A5:A14,{1;2;3})

セル A16 ~ A18 に、値 400475、および 500 がそれぞれ表示されます。

この式では、配列定数を使用して SMALL 関数を 3 回評価し、セル A1:A10 に格納されている配列のメンバーのうち、最も小さいメンバー (1)、2 番目に小さいメンバー (2)、および 3 番目に小さいメンバー (3) を返しています。検索する値を増やすには、定数に引数を追加し、対応する数の結果セルを範囲 A12:A14 に追加します。SUMAVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。次に例を示します。

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{1;2;3}))

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

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

  1. セル A1 ~ A3 を選択します。

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

セル A1 ~ A3 に、値 32002700、および 2000 がそれぞれ表示されます。

ここで、ROW 関数と INDIRECT 関数について簡単に説明しておきます。ROW 関数を使用すると、連続する整数の配列を作成できます。たとえば、演習用のブックで、1 列 10 行の空のセル (A5:A14) を選択し、次の配列数式を入力し、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 番内側のかっこから順番に確認してみましょう。INDIRECT 関数が、一連の文字列値 (この例の場合は値 1 ~ 3) を返します。次に、ROW 関数が、3 つのセルから成る列方向の配列を生成します。LARGE 関数は、セル範囲 A5:A14 を使用しますが、ROW 関数から返された参照ごとに、合計 3 回評価されます。値 3200、2700、および 2000 が、列方向の 3 つのセル配列に返されます。取得する値の数を増やす場合は、INDIRECT 関数に渡すセル範囲を大きくします。

また、SUMAVERAGE などの他の関数を、この数式と組み合わせて使用することもできます。

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

この数式が機能するのは、データ範囲のすべてのセルが単一の列に含まれる場合だけです。Sheet3 で、セル A16 に次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

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

文字列 "bunch of cells that" がセル A16 に表示されます。

この式を、内側の要素から順番に詳しく確認してみましょう。LEN 関数が、セル範囲 A6:A9 の各項目の長さを返します。MAX 関数は、これらの項目のうちの最も大きな値を計算しますが、これは最も長い文字列に相当するセル A7 の値です。

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

(MAX(LEN( A6 : A9 ))

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

LEN( A6:A9 )

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

最後に、INDEX 関数は、配列、配列内の行番号および列番号を引数として使用します。セル範囲 A6:A9 が配列で、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 つのセル範囲間で相違する値の個数を数える

この配列数式では、"データ1" および "データ2" という名前の 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* (データ1<>データ2) )

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

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

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

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

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

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

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

ページの先頭へ

補足説明

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

注記: 

  • 機械翻訳についての免責事項: この記事の翻訳はコンピューター システムによって行われており、人間の手は加えられていません。マイクロソフトでは、英語を話さないユーザーがマイクロソフトの製品、サービス、テクノロジに関するコンテンツを理解するのに役立てるため、こうした機械翻訳を提供しています。記事は機械翻訳されているため、用語、構文、文法などに誤りがある場合があります。

  • この記事の英語版を参照するには、ここ をクリックしてください。Microsoft コミュニティの他のメンバーと一緒に、Community Translation Framework (CTF) を使ったこの記事の改善にご協力ください。記事内の文章にマウス ポインターを合わせて、CTF ウィジェットの [翻訳を改善します] をクリックしてください。CTF の詳細については、ここ をクリックしてください。CTF を使用すると弊社の サービス利用規約 に同意したことになります。

関連項目

数式の概要

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

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

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

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

×