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

マルチセル配列と単一セル配列

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

• 複数セルの配列数式
  

• ここでは、セル 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 の結果を計算します。=F10*G10、F11*G11、F12*G12 などの標準数式を使用した場合は、11 の異なる数式を使用して同じ結果を計算していました。 これは大したことではありませんが、合計に数千行がある場合はどうなるでしょうか? そうすれば、大きな違いを生むことができます。
  • 効率性 配列関数は、複雑な数式を作成するための効率的な方法です。 配列数式 =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 テーブル内にある場合、データを追加または削除すると、動的配列数式のサイズが自動的に変更されます。
  • Excel での #スピル! を修正する 動的配列では、意図したスピル範囲が何らかの理由でブロックされていることを示す #SPILL! エラーが発生しました。 詰まりを解決すると、数式が自動的にスピルします。

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

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

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

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

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

• 水平定数を作成する
  前の例のブックを使用するか、または新しいブックを作成します。 空のセルを選択し、「 =SEQUENCE(1,5)」と入力します。 SEQUENCE 関数は、 ={1,2,3,4,5} と同じ 1 行 x 5 列の配列を作成します。 次の結果が表示されます。
  
• 垂直定数を作成する
  その下に余地がある空白セルを選択し、「 =SEQUENCE(5)」または「 ={1;2;3;4;5}. 次の結果が表示されます。
  
• 2 次元定数を作成する
  その右と下にスペースがある空白セルを選択し、「 =SEQUENCE(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 を返します。

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(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」と入力します。

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

結果を水平方向ではなく垂直方向にスピルしたい場合は、 =TRANSPOSE(Quarter1) を使用できます。

決算報告書を作成する場合のように、12 か月のリストを表示する場合は、SEQUENCE 関数を使用して今年を基準に 1 か月を表示できます。 この関数の優れた点は、月のみが表示されているにもかかわらず、その背後に有効な日付があり、他の計算に使用できることです。 これらの例は、サンプル ブックの 名前付き配列定数 および クイック サンプル データセット ワークシートにあります。

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

DATE 関数を使用して今年に基づく日付を作成し、SEQUENCE 関数を使用して 1 から 12 までの 1 から 12 までの配列定数を作成し、TEXT 関数を使用して表示形式を "mmm" (1 月、2 月、3 月など) に変換します。 1 月など、完全な月の名前を表示する場合は、"mmmm" を使用します。

名前付き定数を配列数式として使う場合は、Quarter1 だけでなく =Quarter1 のように、等号も忘れずに入力してください。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 最後に、関数、テキスト、数値を組み合わせて使用できることに注意してください。 それはすべて、どれだけクリエイティブになりたいかによって異なります。

配列定数の使用例

配列数式で配列定数を使用する方法を示す例をいくつか紹介します。 いくつかの例では、 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
• 配列内の 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))」、または「=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), or ={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}
  数値の配列を作成するため、10 を入力する前に { (左中かっこ) を入力し、180 を入力する後に } (右中かっこ) を入力してください。
  次に、空白セルに "=D9#" または "=D9:I11 " と入力します。 D9:D11 と同じ値を持つ 3 x 6 のセル配列が表示されます。 # 記号は スピル範囲演算子と呼ばれ、入力する代わりに配列範囲全体を参照する 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 - 参照値以下の最大値を返します
  • 0 - 参照値と正確に等しい最初の値を返します
  • -1 - 指定した参照値以上の最小値を返します
  • 照合の種類を指定しなかった場合は、1 が指定されたと見なされます。

  最後に、 INDEX 関数 は、配列と、その配列内の行と列番号という引数を受け取ります。 セル範囲 C9:C13 は配列を提供し、MATCH 関数はセル アドレスを提供し、最後の引数 (1) は、配列の最初の列から値を取得することを指定します。
  最小のテキスト文字列の内容を取得する場合は、上記の例の MAX を MIN に置き換えます。

• 範囲内で値の小さい方から n 番目までを検索する
  この例では、セル範囲内の 3 つの最小値を検索する方法を示します。この場合、セル B9:B18 にサンプル データの配列が =INT(RANDARRAY(10,1)*100) として作成されています。 RANDARRAY は揮発性の関数であるため、Excel で計算するたびに新しい乱数のセットが取得されることに注意してください。
  
  「 =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})
  この数式では、配列定数を使用して SMALL 関数 を 3 回評価し、セル B9:B18 に含まれる配列の中で最小の 3 つのメンバーを返します。3 はセル D9 の変数値です。 より多くの値を見つけるには、SEQUENCE 関数の値を増やすか、定数に引数を追加します。 SUM、AVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。
  =SUM(SMALL(B9#,SEQUENCE(D9))
  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• 範囲内で値の大きい方から n 番目までを検索する
  範囲内の最大値を見つけるには、SMALL 関数を LARGE 関数に置き換えることができます。 次の例では、ROW 関数と 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 などの他の関数と共に使用できます。

エラーの対処

• エラー値を含む範囲の合計を求める
  #VALUE などのエラー値を含む範囲を合計しようとすると、Excel の SUM 関数が機能しません。 または #N/A。 次の例では、エラーを含む "データ" という名前の範囲の値を合計する方法を示します。
  
• =SUM(IF(ISERROR(Data),"",Data))
  この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。 内側の関数から順に説明すると、ISERROR 関数は、セル範囲 ("データ") でエラーがないか検索します。 IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。 この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 ("") が返され、範囲 (データ) の残りの値については FALSE に評価される (つまり、エラー値を格納していない) ため、値自体が返されます。 次に、SUM 関数が、フィルター処理された配列の合計を計算します。
• 範囲内のエラー値の個数を数える
  この例は前の数式と似ていますが、エラー値をフィルター処理せずに Data という名前の範囲で返します。
  =SUM(IF(ISERROR(Data),1,0))
  この数式では、エラーを含むセルの場合は値 1 を、エラーを含まないセルの場合は値 0 を格納している配列を作成します。 次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。
  =SUM(IF(ISERROR(Data),1))
  この引数を指定しなかった場合、セルにエラー値が含まれていないと、IF 関数は FALSE を返します。 この数式をさらに簡略化して、次のようにすることもできます。
  =SUM(IF(ISERROR(Data)*1))
  この形式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

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

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

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

=SUM(IF(Sales>0,Sales))

IF 関数は、正と偽の値の配列を作成します。 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(Sales<>0,Sales))

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

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

この配列数式では、MyData および YourData という名前の 2 つのセル範囲の値を比較し、この 2 つの範囲間で相違する値の数を返します。 2 つの範囲の内容が一致する場合は、0 が返されます。 この数式を使用するには、セル範囲が同じサイズで同じ寸法である必要があります。 たとえば、MyData が 3 行 x 5 列の範囲である場合、YourData も 3 行 x 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(Data=MAX(Data),ROW(Data),""))

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

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

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

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

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

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

• 複数セルの配列数式

以下の表全体をコピーし、空白のワークシートのセル A1 に貼り付けます。

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} が表示されます。 

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

ブックのセル 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 をクリックし、 Delete キーを押します。 セル範囲全体 (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) と同じです。

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

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

• 数式を入力する前に、結果の格納先のセル範囲を選択します。 これは、複数セルの配列数式を作成する際に、セル 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、2、3、4 で、2 行目は 5、6、7、8 です。 4 と 5 の間の単一のセミコロンで 2 つの行が区切られています。

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

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

水平定数、垂直定数、および 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}
   次の結果が表示されます。
   

数式で定数を使用する

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

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

配列定数の使用例

配列数式で配列定数を使用する方法を示す例をいくつか紹介します。 いくつかの例では、 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
   セル C1 から E3 には、C8 から E10 と同じ値を持つ 3x3 配列のセルが表示されます。

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

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})

この数式では、配列定数を使用して 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 関数も使用しています。

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")) ) を見てみましょう。この式は、内かっこから外側に向かって計算します。 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)

"bunch of cells that" というテキストが表示されます。

この式を、内側の要素から順番に詳しく確認してみましょう。 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(Data),"",Data))

この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。 内側の関数から順に説明すると、ISERROR 関数は、セル範囲 ("データ") でエラーがないか検索します。 IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。 この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 ("") が返され、範囲 (データ) の残りの値については FALSE に評価される (つまり、エラー値を格納していない) ため、値自体が返されます。 次に、SUM 関数が、フィルター処理された配列の合計を計算します。

範囲内のエラー値の個数を数える

この例は前の数式と似ていますが、"データ" という名前の範囲のエラー値をフィルター処理する代わりに、エラー値の数を返します。

=SUM(IF(ISERROR(Data),1,0))

この数式では、エラーを含むセルの場合は値 1 を、エラーを含まないセルの場合は値 0 を格納している配列を作成します。 次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。

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

この引数を指定しなかった場合、セルにエラー値が含まれていないと、IF 関数は FALSE を返します。 この数式をさらに簡略化して、次のようにすることもできます。

=SUM(IF(ISERROR(Data)*1))

この形式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

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

条件に基づいて値を集計することが必要になる場合があります。 たとえば、次の配列数式では、"売上" という名前の範囲に含まれる正の整数だけを合計します。

=SUM(IF(Sales>0,Sales))

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

複数の条件を満たす値を合計することもできます。 たとえば、次の配列数式では、0 より大きく、かつ、5 以下の値を計算します。

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

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

OR 条件を使用する配列数式を作成することもできます。 たとえば、5 未満の値と、15 より大きい値の合計を求めることができます。

=SUM(IF((Sales<5)+(Sales>15),Sales))

IF 関数は、5 未満の値と、15 より大きい値をすべて検索し、これらの値を SUM 関数に渡します。

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

0 を除いた平均を計算する

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

=AVERAGE(IF(Sales<>0,Sales))

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(Data=MAX(Data),ROW(Data),""))

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

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

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))