エクセルの表から条件にあった数字が入力されているセルの行数を抽出する

質問

A1からD100まで任意の数字が入力されている表があります。
E1にある数字を入力したとき、A1からD100までの表を1行目から順に、2行目、3行目と検索していき、E1の数字よりも小さな数字がはじめて出てきた行数をE2に自動的に入力するようにしたいのですが、関数を使ってそのようなことは可能でしょうか。

回答No.2

imogasi · ベストアンサー

関数でも出来るのかもしれないが、ユーザー関数を作るのか簡単なので紹介する。順次のセル指定を行方向に進めてくれるのでFor　Eachがお誂え向き。
標準モジュールに下記をコピペ。
Function Fsmall(a, b)
Dim cl
For Each cl In a
If cl < b Then
Fsmall = cl.Row
Exit Function
End If
Next
End Function
ーー
例データ　
A1:C4
14 15 12
16 13 21
15 9 10
4 4 21
E1 に　８
ーーー
シートで空きセルに
=Fsmall(A1:C4,E1)　と入れる
結果
４（行目の４がこれを決定）
＝＝＝
●配列数式利用
=MIN(IF(A1:C4<$E$1,ROW(A1:C4),99))
と入れてSHIFT,CTRL,ENTERの３つのキーを同時押し（配列数式）
でも出来そうなのだが、少数のテストしか出来てない。

お礼

確認してみたら、お示しいただいたユーザー関数を用いる方法でうまうできました。ありがとうございました。

補足

ご回答ありがとうございます。
ユーザー関数というのははじめて知りました。
自分のデータで確認してみたところ、どうもうまくいかないようです。
配列数式のほうについても確認してみます。

回答No.3

maron--5

E2=LARGE(INDEX((A1:D100<E1)*ROW(A1:A100),),COUNTIF(A1:D100,"<"&E1))

補足

ご回答ありがとうございました。
いま少し確認させていただいたのですが、うまく抽出されないようです。もしよろしければ、式の意味など教えていただけるとありがたく思います。

回答No.1

higekuman

やりたいこととは違うのですが・・・

条件付き書式で、E1より小さいデータすべてに色をつけるのではダメですか？

お礼

実は、E２の数字はまた別のセルの関数でで利用しようと考えておりますので、色ではうまくいきません。
でも、アイデアをありがとうございました。