Excel のパワー ユーザーになるには、他の数式では実行できない計算を行うことができる配列数式の使用方法を学ぶ必要があります。この記事は、Colin Wilcox 氏による Excel パワー ユーザー コラムおよび Excel MVP である John Walkenbach 氏の著書『Excel 2002 Formulas: (英語)』の第 14 章と第 15 章を参考にしています。John Walkenbach 氏の他の書籍については、同氏の書籍のページ(英語)を参照してください。

---

• 配列数式について

• 配列定数について

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

• 高度な配列数式の使用例

---

配列数式について

ここでは、配列数式の概要を紹介し、配列数式の入力、編集、およびトラブルシューティングの方法について説明します。

配列数式を使用する理由

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

• セル範囲に含まれる文字数を計算できます。
• 一定の条件を満たす数値 (範囲内の下位値、上限と下限の間に位置する数値など) のみの合計を計算できます。
• ある範囲の値のうち、n 個ごとに出現する値の合計を計算できます。

 メモ   配列数式をブックに入力するときは Ctrl キーと Shift キーを押しながら Enter キーを押すため、配列数式は "CSE 数式" とも呼ばれます。

配列と配列数式の基礎

プログラミングの経験が少しでもあれば、"配列" という言葉を聞いたことがあると思います。ここで言う配列は、項目の集合です。Excel では、これらの項目を単一の行 (1 次元の水平配列)、単一の列 (1 次元の垂直配列)、または複数の行と列 (2 次元配列) に格納できます。Excel では、3 次元の配列または配列数式を作成することはできません。

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

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

演習

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

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

1. 新しい空白のブックを開きます。
2. サンプルのワークシート データをコピーし、新しいブックのセル A1 から始まるセル範囲に貼り付けます。

   サンプルのワークシート データをコピーするには

   1. 新しいブックまたはワークシートを作成します。
   2. ヘルプ トピックから使用例を選択します。

       メモ   行見出しまたは列見出しは選択しないでください。

      

      ヘルプから使用例を選択する
   3. Ctrl キーを押しながら C キーを押します。
   4. ワークシートのセル A1 を選択し、Ctrl キーを押しながら V キーを押します。

   ---

   販売員	車種	販売数	単価	売上合計
   川井	セダン	5	220000
   	クーペ	4	180000
   野内	セダン	6	230000
   	クーペ	8	170000
   河垣	セダン	3	200000
   	クーペ	1	160000
   山水	セダン	9	215000
   	クーペ	5	195000
   幡垣	セダン	6	225000
   	クーペ	8	200000

   ---

3. 横に表示される (貼り付けのオプション) を使用して、貼り付け先の書式に合わせます。
4. 配列 (C2 ～ D11 のセル範囲) の値を乗算するために、セル E2 ～ E11 を選択し、数式バーに次の数式を入力します。

   =C2:C11*D2:D11

5. Ctrl キーと Shift キーを押しながら Enter キーを押します。

Excel によって数式が中かっこ ({ }) で囲まれ、選択した範囲の各セルにこの数式が配置されます。この処理は瞬時に行われ、各販売員について車種ごとの売上合計が E 列に表示されます。

---

---

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

1. ブックのセル A13 に、「売上合計」と入力します。
2. セル B13 に次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

   =SUM(C2:C11*D2:D11)

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

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

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

• 一貫性  セル E2 以降、この列のどのセルをクリックしても、同じ数式が表示されます。この一貫性が、正確さの向上に役立ちます。
• 安全性  複数セルの配列数式を構成する個々のセルを上書きすることはできません。たとえば、セル E3 をクリックし、Del キーを押すことで、このセルの内容を削除することはできません。セル範囲全体 (E2 ～ E11) を選択し、配列全体に対する数式を変更するか、または配列をそのままにしておく必要があります。安全性を高めるための手段として、Ctrl キーと Shift キーを押しながら Enter キーを押して、数式に対する変更を確認する必要があります。
• ファイル サイズの小型化  多くの場合、複数の中間式の代わりに単一の配列数式を使用できます。たとえば、この演習で作成したブックでは、1 つの配列数式を使用して E 列の結果を計算しています。標準の数式 (=C2*D2 など) を使用して同じ結果を得るには、11 個の異なる数式を使用する必要があります。

配列数式の構文の概要

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

もう 1 つの重要な点は、配列関数は短縮形であるということです。たとえば、前に使用した複数セルの関数は、次と同じ意味になります。

=C2*D2
=C3*D3

以下同様に続きます。セル B13 の単一セルの数式は、これらの乗算を短縮し、さらに小計を集計するために必要な算術演算 =E2+E3+E4 (以下同様) を加えたものです。

配列数式の入力と変更の規則

前の説明の繰り返しになりますが、配列数式を作成するための基本的な規則は、配列数式を入力または編集するときは、Ctrl キーと Shift キーを押しながら Enter キーを押す必要があるということです。この規則は、単一セルの数式と複数セルの数式の両方に適用されます。

複数セルの数式を操作するときは、次の規則にも従う必要があります。

• 数式を入力する "前" に、結果の格納先のセル範囲を選択する必要があります。これは、複数セルの配列数式の演習の手順 3. で、セル E2 ～ E11 を選択したときに行いました。
• 配列数式の個々のセルの内容を変更することはできません。これを実際に試すには、サンプル ブックのセル E3 を選択し、Del キーを押します。
• 配列数式全体を移動または削除できますが、配列数式の一部を移動または削除することはできません。つまり、配列数式を縮小するには、既存の数式を削除してから、数式を入力し直す必要があります。

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

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

配列数式を拡張する

ときには、配列数式の拡張が必要になる場合もあります。ただし、上で説明したように、配列数式の縮小はできません。拡張する場合の手順は複雑ではありませんが、前のセクションで説明した規則を忘れないでください。

1. サンプル ブックで、メイン テーブルの下にあるテキストおよび単一セルの数式をクリアします。
2. ブックのセル A12 から始まるセル範囲に、追加のデータ行を貼り付けます。横に表示される (貼り付けのオプション) を使用して、貼り付け先の書式に合わせます。

   ---

   豊田	セダン	6	250000
   	クーペ	7	190000
   佐本	セダン	4	220000
   	クーペ	3	200000
   古井	セダン	8	230000
   	クーペ	8	210000

   ---

3. 現在の配列数式 (E2:E11) を格納しているセルの範囲と、新しいデータの横の空白セル (E12:E17) を選択します。つまり、セル E2:E17 を選択します。
4. F2 キーを押して編集モードに切り替えます。
5. 数式バーで、C11 を C17 に変更し、D11 を D17 に変更して、Ctrl キーと Shift キーを押しながら Enter キーを押します。セル E2 ～ E11 の数式が更新され、新しいセル (E12 ～ E17) に同じ数式が配置されます。

   ---

   

   ---

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

配列数式にはすばらしい機能が備わっていますが、次のような短所もあります。

• Ctrl キーと Shift キーを押しながら Enter キーを押すのを忘れる場合があります。配列数式を入力または編集するときは、このキーの組み合わせを必ず押してください。
• 数式が、他のユーザーに理解されない可能性があります。配列数式に関する文書や記事は比較的少ないため、ブックの作成者以外のユーザーがブックを変更する必要がある場合は、配列数式を使用しないようにするか、配列数式の変更方法を他のユーザーがわかるようにしておく必要があります。
• コンピュータの処理速度とメモリによっては、大きな配列数式を使用すると、計算速度が低下することがあります。

 ページの先頭へ

配列定数について

ここでは、配列定数の概要を紹介し、配列数式の入力、編集、およびトラブルシューティングの方法について説明します。

配列定数の概要

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

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

この記事の前半では、配列数式を作成するときは Ctrl キーと Shift キーを押しながら Enter キーを押す必要があることを強調しました。配列定数は配列数式の構成要素であるため、配列定数の前後に手動で中かっこを入力して定数を囲みます。その後で、Ctrl キーと Shift キーを押しながら Enter キーを押して、数式全体を入力します。

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

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

---

---

数式で定数を使用する

配列定数の入力方法がわかったところで、上で説明した定数を使用する簡単な例を見てみましょう。

1. 空白のワークシートを開きます。
2. 次のリストをセル A1 から始まるセル範囲にコピーします。横に表示される (貼り付けのオプション) を使用して、貼り付け先の書式に合わせます。

   ---

   3

   4

   5

   6

   7

   ---

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

   =SUM(A1:E1*{1,2,3,4,5})

配列数式として入力したため、Excel によって定数全体が別の中かっこで囲まれます。

---

---

セル A3 に値 [85] が表示されます。次のセクションでは、この数式がどのように機能するかを説明します。

配列定数の構文の概要

上で使用した数式は、いくつかの部分で構成されます。

---

---

かっこで囲まれた最後の要素は配列定数 {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] をクリックします。
5. ワークシートで、1 行 3 列の空白セルを選択します。
6. 次の数式を入力し、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 乗する

• 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. 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 関数によって、行が列に変換されます。

 ページの先頭へ

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

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

はじめに

このセクションのデータを使用して、2 つのサンプルのワークシートを作成します。

1. 既存のブックを開くか、または新しいブックを作成し、空白のワークシートが 2 つ含まれていることを確認します。
2. 次の表のデータをコピーし、ワークシートのセル A1 から始まるセル範囲に貼り付けます。

   400		Excel で		1	2	3	4
   1200		あなたの		5	6	7	8
   3200		コンピュータの		9	10	11	12
   475		世界が		13	14	15	16
   500		広がります
   2000
   600
   1700
   800
   2700

   貼り付け済みのワークシートは、次のようになります。

   ---

   

   ---

3. 最初のワークシートに「データ」、2 番目の空白のワークシートに「配列」という名前を付けます。

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

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

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

1. サンプル ブックで、[配列] ワークシートをクリックします。
2. C1 ～ E3 のセル範囲を選択します。
3. 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

   =データ!E1:G3

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

この数式は、[データ] ワークシートのセル E1 ～ G3 に格納されている値にリンクしています。この複数セルの配列数式を使用する代わりに、次のように、[配列] ワークシートの各セルに独立した数式を格納する方法もあります。

---

=データ!E1	=データ!F1	=データ!G1
=データ!E2	=データ!F2	=データ!G2
=データ!E3	=データ!F3	=データ!G3

---

[データ] ワークシートの一部の値を変更した場合は、変更内容が [配列] ワークシートに表示されます。[データ] ワークシートの値を変更する場合は、配列数式の編集に関する規則に従う必要があります。これらの規則の詳細については、「配列数式について」を参照してください。

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

1. [配列] ワークシートで、セル C1 ～ E3 を選択します。
2. F2 キーを押して編集モードに切り替えます。
3. F9 キーを押して、セル参照を値に変換します。Excel により、値が配列定数に変換されます。
4. Ctrl キーと Shift キーを押しながら Enter キーを押して、配列定数を配列数式として入力します。

Excel によって、配列数式 =データ!E1:G3 が、次の配列定数に置き換えられます。

={1,2,3;5,6,7;9,10,11}

[データ] ワークシートと [配列] ワークシートの間のリンクは解除され、配列数式が配列定数に置き換えられました。

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

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

• [データ] ワークシートで、セル C7 に次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

  =SUM(LEN(C1:C5))

セル C7 に値 [26] が表示されます。

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

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

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

1. [データ] ワークシートで、セル A12 ～ A14 を選択します。

   この一連のセルに、配列数式から返された結果を格納します。

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

   =SMALL(A1:A10,{1;2;3})

セル A12 ～ A14 に、値 [400]、[475]、および [500] がそれぞれ表示されます。

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

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

=AVERAGE(SMALL(A1:A10,{1;2;3}))

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

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

1. [データ] ワークシートで、セル A12 ～ A14 を選択します。
2. Del キーを押して既存の数式をクリアし、セルは選択状態のままにします。
3. 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

セル A12 ～ A14 に、値 [3200]、[2700]、および [2000] がそれぞれ表示されます。

ここで、ROW 関数と INDIRECT 関数について簡単に説明しておきます。ROW 関数を使用すると、連続する整数の配列を作成できます。たとえば、演習用のブックで、1 列 10 行の空のセル (A1:A10) を選択し、次の配列数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

=ROW(1:10)

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

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

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

前に使用した数式 =LARGE(A1:A10,ROW(INDIRECT("1:3"))) で行われる処理を、1 番内側のかっこから順番に確認してみましょう。INDIRECT 関数が、一連の文字列値 (この例の場合は値 1 ～ 3) を返します。次に、ROW 関数が、3 つのセルから成る列方向の配列を生成します。LARGE 関数は、セル範囲 A1:A10 を使用しますが、ROW 関数から返された参照ごとに、合計 3 回評価されます。値 3200、2700、および 2000 が、列方向の 3 つのセルに返されます。取得する値の数を増やす場合は、INDIRECT 関数に渡すセル範囲を大きくします。

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

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

次の例では、セル範囲内で最も長い文字列を検索します。この数式が機能するのは、データ範囲のすべてのセルが単一の列に含まれる場合だけです。

• [データ] ワークシートでセル C7 の既存の数式をクリアし、このセルに次の数式を入力し、Ctrl キーと Shift キーを押しながら Enter キーを押します。

  =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

セル C7 に値 [Excel で] が表示されます。

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

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

(MAX(LEN(C1:C5))

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

LEN(C1:C5)

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

最後に、INDEX 関数は、配列、配列内の行番号および列番号を引数として使用します。セル範囲 C1:C5 が配列で、MATCH 関数によってセル番地が渡され、最後の引数 (1) は配列内の最初の列から取得した値を表します。

ここで説明した関数の詳細については、Excel のヘルプを参照してください。

 ページの先頭へ

高度な配列数式の使用例

ここでは、高度な配列数式の例を示します。

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

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 つのセル範囲が同じサイズで、同じ次元である必要があります。

=SUM(IF(データ1=データ2,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(データ))

 ページの先頭へ