INDEX、SMALL、ROW関数と配列

INDEX、SMALL、ROW関数で配列を利用した方法がうまく理解できません。
以下のURLで書かれている方法を理解したいのですが、いくつかわからない部分があります。

http://q.hatena.ne.jp/1291020662

(1)『それ以外は101を返す』とありますが、なぜ101なのですか？

(2)SMALL関数は範囲（又は配列）と順位が引数ですが、
配列を表している部分『IF(B$1:B$100="男",ROW(B$1:B$100),101)』は、条件が男であるものを対象範囲とするという意味なのはわかるのですが、『ROW(B$1:B$100)』はなぜ必要なのでしょうか？

宜しくお願いします。

No.5 回答者： bunjii 回答日時： 2014/07/22 07:33

>(1)『それ以外は101を返す』とありますが、なぜ101なのですか？

提示されているサイトでは対象の範囲が100人分としてありますので、IF関数の論理式がFALSEのときは範囲の最大行より大きい値を返す必要があるためです。

>(2)SMALL関数は範囲（又は配列）と順位が引数ですが、配列を表している部分『IF(B$1:B$100="男",ROW(B$1:B$100),101)』は、条件が男であるものを対象範囲とするという意味なのはわかるのですが、『ROW(B$1:B$100)』はなぜ必要なのでしょうか？
SMALL関数で目的の順位の行番号をINDEX関数に引き渡すためです。

数式の全体を論理的に考えれば理解できると思います。
目的の値=INDEX(抽出範囲,行番号,列番号）
行番号=SMALL(配列範囲,順位)
配列範囲=IF(条件式,TRUEのときの値,FALSEのときの値）
FALSEのときの値=扱うデータの最大行番号より大きな値

No.4 回答者： MarcoRossiItaly 回答日時： 2014/07/22 02:41

No.3 です。

一部、不適切な説明があったので、訂正させてください。

×
……100 より大きな整数（*）であれば幾つであっても、全体の数式はエラーにならないので、101 にしてみたというだけです。「999」とかであっても問題ありません。

↓

○
……100 より大きな整数（*）であれば幾つであっても、全体の数式は同じ結果になるので、101 にしてみたというだけです。「999」とかであっても問題ありません。

IF が 101 とか 999 を返す場合、INDEX の第 1 引数「A$1:A$100」に含まれる行数を超えるので、どちらが書いてあっても C 列の下のほうの行にはエラーが表示されます。全ての行について IF が 100 以下を返す場合は、エラーは C1:C100 のどこにも表示されません。

No.3 回答者： MarcoRossiItaly 回答日時： 2014/07/22 02:25

最初に、この数式は、C1:C100 のセル範囲に記入してください。

101 行目以下だと「ROW()」が 101 以上となり、INDEX 関数の第 1 引数「A$1:A$100」に含まれる行数を超えるので、エラーとなります。


＞(1)『それ以外は101を返す』とありますが、なぜ101なのですか？


IF 関数はその第 1 引数が、TRUE であれば第 2 引数を、FALSE であれば第 3 引数をそれぞれ返します。したがって「それ以外」（「男」以外）の行については、第 3 引数の「101」が選択されます。

「なぜ第 3 引数を 101 にしたのか」という趣旨のご質問でしたら、100 より大きな整数（*）であれば幾つであっても、全体の数式はエラーにならないので、101 にしてみたというだけです。「999」とかであっても問題ありません。

* 実は INDEX 関数の第 2 引数として小数を指定してもエラーにはならなかったりしますが、小数点以下が勝手に切り捨てられてしまうので、通常は始めから整数を指定します。


＞(2)……『IF(B$1:B$100="男",ROW(B$1:B$100),101)』は、条件が男であるものを対象範囲とするという意味なのはわかるのですが、……


計算の過程について言うと、男であるもの「のみ」ではなくて、勿論 100 個のデータから成る配列の全てを対象として計算しています。IF 関数は上でも述べたとおり、選択するだけの関数です。つまりこの IF 関数は、「男」である行については第 2 引数を、それ以外の行については第 3 引数を選んでいくことにより、100 個のデータから成る配列を返しています。IF 関数によって、個数は減っていません。


＞……『ROW(B$1:B$100)』はなぜ必要なのでしょうか？


配列の、何番目の要素において第 3 引数が選ばれる場合でも、第 3 引数には「101」とだけ書いてあるため、常に 101 が返ります。101 で固定されています。

一方、第 2 引数としては 100 個のデータから成る配列を書いているので、第 2 引数は可変です。IF 関数が第 1 引数において n 番目の要素について計算しているとき、第 3 引数ではなく第 2 引数を返すならば、その 100 個のデータのうち n 番目のものを返すことになります。

具体的な数字で例を書けば、「B$1="男"」が TRUE である場合、IF 関数は「ROW(B$1)」（B1 セルの行番号）を返すし、「B$50="男"」が TRUE である場合、IF 関数は「ROW(B$50)」を返すということです。つまり第 2 引数は可変であるし、100 本の IF 関数を 1 本にまとめて書いてあると言ってもいいです。ROW は行番号を返す関数なので、「ROW(B$1:B$100)」は「1, 2, ...,99, 100」という 100 個の連続する自然数から成る配列であるわけです。

このように第 2 引数を可変にしながら、第 3 引数が選ばれた場合と合わせて 100 個の整数を IF 関数が返しておけば、その 100 個の中から SMALL 関数がただ 1 つの整数を選ぶときに、「男」に該当していた配列の要素については、一つひとつバラバラの整数になっていることになります。

SMALL 関数の第 3 引数は順位を指定しますが、それが「ROW()」と書かれているため、この数式が記入されているセルの位置に応じて、異なる順位が指定されていることとなっています。つまり IF 関数が返した配列のうち B 列において「男」に該当していた各要素がバラバラの整数になっていれば、SMALL 関数は A 列から、「一意に」1 以上 100 以下の行番号のセルを選ぶことができることになります。

具体例としては、第 2 引数と第 3 引数の場合を合わせて、IF 関数により「1, 2, 101, 4, 101, 6, 7, 101, 9, 101, ..., 98, 101, 100」といった感じの数列が得られることになりますね？

以上を総合すると、もしも B 列に「男」でない値の記入されているセルが 5 つだけあったとすれば、この数式が記入されているのは C 列なので、C1:C95 には A1:A100 の値のうち B 列が「男」である 95 行のみが行番号の順に表示され、C96:C100 にはラー値「#REF!」が表示される結果となります。


配列は、慣れないと難しいと思います。100 行だと理解しづらいので、5 行とか 10 行とかの規模に修正して試してみるとよいかもしれません。また、数式タブの「数式の検証」機能も利用してください。途中計算の様子が分かります。

No.2 回答者： MackyNo1 回答日時： 2014/07/21 17:12

>(1)『それ以外は101を返す』とありますが、なぜ101なのですか？

通常の『IF(B$1="男",ROW(B$1),101)』という数式ならＢ１セルが男ならその行番号、それ以外は101という意味になりますが、今回の配列を使用した数式ではＢ１だけでなくＢ100セルまでのセル全体をそれぞれ男が入力されているか判定し、その配列(100までの行番号と101を返す）を取得しています。
この101という数字はB100セルの行番号「100」よりも大きい数字ですので、SMALL関数で小さい順に行番号を取得し、最終的にINDEX関数でその行番号をA列から参照するとき、この101行目のセルはないのでREFエラーになります。

したがってこの引数の数字は男のセルの行番号範囲の1～100より大きい数値や文字列が入力されていれば何でもよいことになります（たとえば「""」や引数を省略してもよい）

(2)SMALL関数は範囲（又は配列）と順位が引数ですが、
配列を表している部分『IF(B$1:B$100="男",ROW(B$1:B$100),101)』は、条件が男であるものを対象範囲とするという意味なのはわかるのですが、『ROW(B$1:B$100)』はなぜ必要なのでしょうか？

上にも少し説明しましたが、上記の数式はB1が男ならその行番号１をそれ以外なら101を、同様にB2が男なら2をそれ以外なら101を返す100個のセルの配列（セル範囲と同じようなもの）を取得しています。
この配列の中で小さい順に行番号を取得し、対応するA列のデータを取得する式になっています。

なお、このSMALL関数についても、入力セルによって値の変わる「ROW()」とするよりも「ROW(A1)」のように明示的に行番号（すなわち１）を指定したほうが良いと思います

配列数式の詳細については以下のページがわかりよいと思います。
http://pc.nikkeibp.co.jp/pc21/special/hr/

No.1 回答者： tom04 回答日時： 2014/07/21 16:46

こんにちは！

（1）について
紹介されているサイトを覗いてみました。
＞=IF(B$1:B$100="男",ROW(B$1:B$100),101)
の数式は男女別に並び替えするための数式で
B列が「男」の場合は行番号を表示させ、それ以外（「女」おそらく「中性」はいないと思います）は
101を表示させて、その列の昇順で並び替えを行うと
「男」の行が上に来て、その下に「女」の行が来る！といった並び替えになるものと思われます。
ただ、上記数式では「男」の場合すべて「1」が表示されますので、
＞=IF(B$1:B$100="男",ROW(),101)
とすれば「男」の行番号そのものが表示されます。
（結果としては同じになりますが・・・）

（2）について
配列数式で「男」だけを空白セルなしに表示させるための数式だと思われますが、
他の方の数式に本来は手を付けたくないのですが、
当方であれば
=IF(COUNTIF(B:B,"男")<ROW(),"",INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(B$1:B$100)),ROW())))
という配列数式（Ctrl＋Shift＋Enter）とします。

※　上記数式は1行目に数式を入れる場合になりますので、
1行目は何らかの項目があるのが普通だと思います。
すなわち2行目以降にデータを表示させる場合がほとんどだと思いますので、
＞IF(COUNTIF(B:B,"男")<ROW(A1),"",INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(A$1:A$100)),ROW(A1))))

数式内の
＞ROW(A1)やSMALL関数内の
＞ROW(A$1:A$100))
はA列でなくても構いません。

といった感じにやっても同じだと思います。m(_ _)m