戻り値が配列となるため、配列形式として入力しなければならない関数が、沢山あります。 そのうち、私のページで取り上げたものに下記があります。
範囲内でのデータの頻度分布を、縦方向の数値の配列として返します。この関数では、
値は配列として返され、配列数式として入力されます。
こちらをご覧ください。
INDEX 関数については、前々ページをご覧ください。
範囲 内にある、指定したセルまたはセル範囲の参照が返されます。
指定された行と列が交差する位置にあるセルの参照を返します。セル参照が複数選択となる場合は、その中から任意の領域を選択できます。
(INDEX 関数には、セル範囲形式と配列形式の 2 つがあります。セル範囲形式の INDEX 関数は指定されたセルの参照を返し、配列形式の INDEX 関数は値または値の配列を返します。)
行番号 と 列番号 で指定されるテーブルまたは配列の要素の値を返します。
[配列数式]についてはこちらをご覧ください。
エクセル フアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン すみ)
「SUMIF 関数使用の配列数式」例1
エクセル フアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン すみ)
右の端、金額欄をクリックすると、数式バーに「数式」が表示されます。また、数式バーの適当な箇所をクリックすると、「配列箇所」が枠線で囲まれて表示されます。
「SUMIF 関数使用の配列数式」例2
原文はこちらから
INDEX および MATCH ワークシート関数を使用し、条件を複数指定して値を検索する例
(1) =INDEX ) (2) $C$2:$C$5, (3) MATCH(D2, ) (4) IF($B$2:$B$5=E2,$A$2:$A$5), (5) 0 (6) ,1 省略 数式説明 (1)はINDEX 関数です。 (2)はINDEX 関数の引数1の「範囲」です。 (INDEX 関数の 1 番目の引数が範囲のときは、範囲形式を使います。) 範囲形式書式:「 INDEX(範囲,行番号,列番号,領域番号) 」 この関数は、指定された行と列が交差する位置にあるセルの参照を返します。 (3)はINDEX 関数の引数2の「行番号」ですが、MATCH 関数をネストして、その引数1の[検査値]が[D2]です, INDEX 関数の引数3の「列番号」ですが、 「範囲で指定した各領域が 1 行または 1 列である場合、 行番号または列番号はそれぞれ省略することができます。」 により省略 (4)はMATCH 関数の引数2の[検査範囲]は「IF」文になっています。 $B$2:$B$5=E2, までが、条件式 $A$2:$A$5 は[TRUE]の場合で、[FALSE]は省略、 (5)はMATCH 関数の引数3の照合の型で、検査方法を指定しています。[0]は、 検査値に一致する値のみが検索の対象となります。 (6)INDEX 関数の引数4の「領域番号」ですが、 最初に選択された領域の領域番号が 1 となり、以下2 番目の領域は 2、3 番目の領域は 3 と続きます。 「領域番号を省略すると、1 であると見なされます。」により省略しましたのでで[1]となります。 ですから、上の表で「 =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0)) 」とあるのを、 INDEX 関数の引数4を入力して「 =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0),1) 」 としても、同じ結果が得られます。
解説
[INDEX] 関数の引数1[範囲]で指定した配列の中から引数2[行番号]、引数3[列番号]で指定した「座標」の[値]を「ずばり」求めます。
その範囲(第1の)は、[C2]〜[C5]です。
[MATCH] 関数では、指定したセルに含まれている値を求めるのではなく、一寸遠回りして、[検査範囲]内にある[検査値]に
一致する[値]の「位置番号」を求めます。このようにして、選択肢を増やすことができるのです。というより、元々「条件を複数指定して値を検索する」解法でした。
[INDEX] 関数は行番号と列番号が必要ですが、この行と列の番号を[MATCH] 関数で下記のように取り出すのです。
検査値として、[D2]を設定します。
検査範囲は、[B2]〜[B5]が[E2]と等しい場合、[A2]〜[A5]になります。
領域は、第1の範囲になります。
検査値に一致する値のみが検索の対象となります。
前々ページ「常総線の運賃表」もご覧ください。
「配列数式」を使うと一辺にできます。(女子の場合、男子の数式をコピー貼り付けし[男]を[女]に替えてもできます。)
上の数式で[AVERAGE]を[SUM]に、範囲を[F2:F8]から[C2:E8]の空色部分に替えるだけで、求まります。