【Excel関数】複数条件に対し、最初に一致したセルの行番号を返す方法

質問

【Excel関数】複数条件に対し、最初に一致したセルの行番号を返す方法

　今晩は，いつも勉強させていただいております．
質問させていただきます．どうぞよろしくお願いいたします．
（VBA以外でお願いいたします　m(_ _)m　）

　たとえばA列にランダムな数字が並んでいる場合、
　｜　　A　　｜　　B　　｜
　　　１８
　　　２２
　　　１７
　　　３０
　　　　：
　　　　：

上から順に、「16.5より大きく21より小さい数」の条件に対し
一番最初にひっかかったものの行番号を知りたい場合、
どのようにするのがよいでございましょうか？？？
（今回の場合、「３」行目の１７がそれにあたります。）

　ファイル数と行数が多く、全部をVBAで計算すると時間がかかるため、
VBAで
　B1あたりにその関数を放り込む
→行番号を知る
→ファイル閉じる
とやりたいのでございますが、よい関数を思いつきません。。。

　もしお詳しい方がいらっしゃいましたら，
ぜひともアドバイスいただきたくどうぞよろしくお願いいたします。

回答No.2

MackyNo1 · ベストアンサー

以下のような数式を入力し、Ctrl+Shift+Enterで確定してください。

=MIN(IF((A1:A1000>16.5)*(A1:A1000<21),ROW(A1:A1000),""))

お礼

MackyNo1様
　どうもありがとうございます！！解決いたしました！＾＾
データが100万行以上あるんですが、
=MIN(IF((A:A>100456.954351)*(A:A<100456.954353),ROW(A:A),""))
といたしてみましたところ、見事行番号が表示されました！＾＾

　よくこんな方法がパッとでてまいりますねーー＾＾；

　RowのMinを取るのはなるほど(-o-)なんでございますが、
Ctrl+Shift+Enterって？？知りませんでした。
今から検索してまいります。
m(_ _)m

　本当に助かりました。ご閲覧いただけてよかったです！
まことにありがとうございました！！！

回答No.3

mitarashi

敢えてVBAで回答いたします（VBAの名誉のために？）
当方xl2000なので、65536行が最大なのですが、最後の行に該当する値をセットしておいて、下記のコードで200msec未満でみつかりますが、いかがでしょうか。遅くなる種をすべて潰してみたつもりです。なお、PentiumM 1.3Gです。

Public Declare Function GetTickCount Lib "KERNEL32" () As Long

Sub test()
Dim buf As Variant
Dim i As Long, startTime As Long

startTime = GetTickCount
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
buf = Columns(1).Value
For i = 1 To UBound(buf, 1)
If buf(i, 1) > 16.5 And buf(i, 1) < 20 Then
Debug.Print i
Exit For
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Debug.Print GetTickCount - startTime
End Sub

Sub testData()
Columns(1).Value = 6
Cells(ActiveSheet.Rows.Count, 1).Value = 17
End Sub

お礼

mitarashi様

　どうもありがとうございます！！m(_ _)m
↑絵と名前が一致してらっしゃいますね＾＾食いたくなってきます。

　こんな優良回答を連発でいただけて幸せモノでございますm(_ _)m
時間までチェックしてくださったとのことで、どうもありがとうございます。
１００万行強、でございますので200msec×100万÷6万≒３秒/File　でございますね。
これが４０万ファイルございましてorz（後から後から申し訳ございません）
3sec×400,000/60sec/60min/24h = １３日
　↑これに対し、関数を入れる＋少し待機＋数字取得　が
早いかどうか、でございますね＾＾
今からVBAで組んで比較してみます。前者についてはmitarashi様のコードを
そのまま使わせていただきます。
　このたびはご親切にアドバイスいただきまして、どうもありがとうございました！！
また見かけたらどうぞアドバイスしてやってください！
失礼いたします＾＾

回答No.1

Trick--o--

作業列が使えるのなら
B1（作業列）に
=AND(A1>16.5,A1<21)
で、下にコピー
C1に
=MATCH(TRUE,B:B,0)
で、求められる。

VBAでやったほうが速いと思うけどねぇ。

お礼

どうもありがとうございます！！
＞VBAでやったほうが速いと
はい、全ファイル１００万行あるんですorz
最大値を求める場合、関数だと「＝Max」とするだけで
一瞬ですがVBAだと、、、

　したがって、10個以下のセルに関数群を入れてうまくいく
方法を探している私大でございます。
（１００万行オートフィルさせるのも少し困難でございます。。。orz）

　この度はご親切にどうもありがとうございました！！

補足

（お礼後の捕捉になります）

すみません、「探している私大でございます」
　 　　　　→「探している次第でございます」＾＾；

　大変失礼いたしました。m(_ _)m