ある数値以上の値があるセルを取得したい

質問

エクセルの関数で、ある数値以上の値があるセルの位置を取得したいのです。
範囲B2:B11に任意の数値が入っているものとします。
その中で、仮に3以上の値が出現するセルの位置を取得する場合、どのような関数を用いればよろしいでしょうか？
数値をぴったり3に限定なら、=MATCH(3,B2:B11,0)　で、範囲内で何番目にあるかわかりますが、3以上となると
補助列を用意し、=B2>3のような式を各セルに入れて、TRUEの位置を=MATCH(TRUE,A2:A11,0)　で取得するくらいしか思いつきません。
ご教示ください。

回答No.3

bunjii · ベストアンサー

>なぜINDEX関数が補助列の代わりになるのか理解できませんが
B2～B11の値が{1;1;5;4;2;3;1;1;1;1}とすればINDEX関数で1列の配列値の行番号を0または無指定にすると
INDEX(B2:B11>=3,0)
　　　↓
{1;1;5;4;2;3;1;1;1;1}>=3
　　　↓
{FALSE;FALSE;TRUE;TRUE;FALSE;TULUE;FALSE;FALSE;FALSE;FALSE}
のように配列値として外側のMATCH関数へ渡されます。
従って、補助列のA2:A11の前処理の代わりになります。
しかし、配列値の要素数が多くなると計算負荷が増加して動作が緩慢になります。

お礼

bunjiiさん、詳しい解説をありがとうございます。
とても勉強になりました。

回答No.4

msMike

「ある数値以上の値があるセルの位置」の「位置」とは、Ｂ列の“行番号”のことと解釈して、添付図では如何でせうか？
E3: =IFERROR(SMALL(IF($B$3:$B$12>=E$2,ROW(C$3:C$12)),ROW(B1)),"")
【お断り】上式は必ず配列数式として入力のこと

お礼

ありがとうございます。
行番号が取得できました。

回答No.2

mdmp2

関数では条件を満足する最初に出現するセルの位置しか取得できないのでVBA でやるしかないと思います。
「分かればいい」だけなら、条件付き書式で、条件に合うセルを塗りつぶすとかフォントの色を変えたら良いと思います。

お礼

ありがとうございます。
今回は関数で「最初に出現するセルの位置」を取得したかったのです。

回答No.1

bunjii

>仮に3以上の値が出現するセルの位置を取得する場合
補助列の数式「=B2>3」は誤りで「=B2>=3」にしてください。
「3以上」は3も含みますので間違わないようにしてください。

>ご教示ください。
補助列を使わない方法を知りたいのですか？
=MATCH(TRUE,INDEX(B2:B11>=3,0),0)
INDEX関数内で前処理をすれば補助列の代わりになります。
また、INDEX関数を使わないときは次のようになりますが数式を確定するときにCTRLとShiftを押しながらEnterの打鍵で配列数式になりますので同等の結果を得られます。
数式バーには次のように表示されます。
{=MATCH(TRUE,B2:B11>=3,0)}

お礼

bunjiiさん、お世話になります。
＞補助列の数式「=B2>3」は誤りで「=B2>=3」に
ご指摘の通りですね、ありがとうございます。
=MATCH(TRUE,INDEX(B2:B11>=3,0),0)ですか！
なぜINDEX関数が補助列の代わりになるのか理解できませんが、これで「最初に出現するセルの位置」を取得することができました！！
ありがとうございます。