関数をいろいろと勉強していくうち「戻り値のスタイルが従来の関数と異なった関数」にたどり着きました。
配列数式実行に際してのご忠告
操作を間違えて、終了しようとしても「配列の１部を変更できません。」
この警告が出た場合、何をどうしようとしても、操作が不能になります。
(１)マウスでセルの選択ができなくなります。
(２)「言語バー」がおかしくなります。対処法のHPを開くにも開けません。
(３)エクスプローラの表示も常態でなく、下手にフォルダを開くと、中のフアイルが何十個も開いたりして散々です。
(４)これを解決するには、数式バーの[×]ボタンを押して解除します。
その方法です。
数式バーをクリックします。
この時括弧の[｛ ｝]は消えて[×]が表示されます。
[×]をクリックします。
未だ[｛ ｝]は消えていません。
改めて、数式バーをクリックすると、[｛ ｝]は消え、配列数式が解除されます。
戻り値が配列となるため、配列形式として入力しなければならない関数が、沢山あります。 そのうちの一部ですが。
範囲内でのデータの頻度分布を、縦方向の数値の配列として返します。この関数では、
値は配列として返され、配列数式として入力されます。
こちらをご覧ください。
INDEX 関数については、前々ページをご覧ください。
範囲 内にある、指定したセルまたはセル範囲の参照が返されます。
指定された行と列が交差する位置にあるセルの参照を返します。セル参照が複数選択となる場合は、その中から任意の領域を選択できます。
（INDEX 関数には、セル範囲形式と配列形式の 2 つがあります。セル範囲形式の INDEX 関数は指定されたセルの参照を返し、配列形式の INDEX 関数は値または値の配列を返します。）
行番号 と 列番号 で指定されるテーブルまたは配列の要素の値を返します。
[配列数式]についてはこちらをご覧ください。
エクセル フアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン すみ)
「SUMIF 関数使用の配列数式」例１
エクセル フアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン すみ)
右の端、金額欄をクリックすると、数式バーに「数式」が表示されます。また、数式バーの適当な箇所をクリックすると、「配列箇所」が枠線で囲まれて表示されます。
「SUMIF 関数使用の配列数式」例２
「ピボットテーブル」でも上の例のように、「各種目別」、「品種別」の合計が出せますが、どちらが使いが手が良いか。
ただ、「ピボットテーブル」の場合は、ほんの４〜５列か５〜６列区分けのべた書き日記でも、答えを出して呉れます。
原文はこちらから
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[列番号]で指定した「座標」の[値]を「ずばり」求めます。
その範囲(第１の)は、[C2]〜[C5]です。
[MATCH] 関数では、指定したセルに含まれている値を求めるのではなく、一寸遠回りして、[検査範囲]内にある[検査値]に
一致する[値]の「位置番号」を求めます。このようにして、選択肢を増やすことができるのです。というより、元々「条件を複数指定して値を検索する」解法でした。
[INDEX] 関数は行番号と列番号が必要ですが、この行と列の番号を[MATCH] 関数で下記のように取り出すのです。
検査値として、[D2]を設定します。
検査範囲は、[B2]〜[B5]が[E2]と等しい場合、[A2]〜[A5]になります。
領域は、第１の範囲になります。
検査値に一致する値のみが検索の対象となります。
宜しかったら「常総線の運賃表」をダウンロ−ドしてご覧ください。
「配列数式」を使うと一辺にできます。(女子の場合、男子の数式をコピー貼り付けし[男]を[女]に替えてもできます。)
上の数式で[AVERAGE]を[SUM]に、範囲を[F2:F8]から[C2:E8]の空色部分に替えるだけで、求まります。
