ExcelのSMALL関数について

質問

ExcelのB列（B2:B50）にフラグ「０」または「１」が入力されています。
フラグ「０」が入力されている行番号を関数を使用して取得したいと思っています。
B列のフラグは順次更新されており、フラグ「０」の個数は複数あります。

下記の関数式（以下多めにオートフィル）で上記のことができるようになったのですが、

=IF(COUNTIF(B$2:B$50,0)<ROW(A1),"",SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1)))

SMALL関数式の部分↓↓の意味がよくわかりません。

SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1))

どなたかご教授いただけないでしょうか。

回答No.1

keithin · ベストアンサー

計算の背景については，例えば下記などを参考に勉強してみてください。
http://pc.nikkeibp.co.jp/pc21/special/hr/

＞部分↓↓の意味がよくわかりません。

B$2:B$50<>0
の部分は，B2からB50まで49個のセルについて一つずつ＜＞０（ゼロではないか）という論理計算を行い，結果して49個の「TRUE(ゼロではなかった）」と「FALSE（ゼロだった）」の『配列』を得ます。

(B$2:B$50<>0)*10^5
の部分は，上述で得たTRUEとFALSEの配列について一つずつ10^5とのかけ算を行い，結果して49個の10^5またはゼロの配列を得ます。
この計算が出来る背景には，エクセルの一般知識として
○TRUEの値は数字と計算されると１として計算される
○FALSEの値は数字と計算されると０として計算される
についても知っておかなければなりません。

(B$2:B$50<>0)*10^5+ROW(B$2:B$50)
の部分は，上述で得た100000とゼロの配列について一つずつROW(B2),ROW(B3)…ROW(B50)との足し算を行います。
この計算をする背景には，エクセルの一般知識として
○ROW関数は行番号の数字を計算する
についても知っておかなければなりません。

結果して，一つ前の結果がTRUEだった（ゼロではなかった）行に対応する要素では
１０００００＋その行の行番号（たとえば２）＝１００００２
FALSEだった（ゼロだった）行に対応する要素では
０＋その行の行番号（たとえば３）＝３
といった数字が並んだ配列が得られます

INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),)
の部分は，以上のような計算を配列計算としてエクセルに実施させるためのテクニックの一つです。
最後の「，）」の部分にもINDEX関数の機能に応じた働きを持たせてあることを見落とさないように注意してください。

最後に
SMALL(INDEX(…,),ROW(A1))
によって，さっき求めた「100001，10002，3，4，10005…」といった配列から，小さい順に（当然１００００もかさ上げすればずっと大きいですから，結果して最初に出てきた小さい数字を）求める行番号を得ます。

回答No.2

mu2011

SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1))
↓
SMALL(範囲，順位）
↓
B2:B50を０以外でTRUE（1)、FALSE(0)に判定、この値を１０００００倍（100程度十分）して該当行番号を加算した結果をINDEX関数で配列、
仮にB2以下が０、１、０ならば２(FALSE*100000+2)、１００００３（TRUE*100000+3）、４(FALSE*100000+4)となり、SMALL関数でこの範囲から小さい順に値を０の個数分抽出する事になります。
順位は、ROW(A1)はオートフィルする事で１からの昇順値となります。