エクセルで、指定の値よりも大きい数字を最初に越えたときの列番号を返すには？

質問

テストの点数で、初めて４０点を下回ったときの日付と、初めて８０点を越えた時の日付を返すような関数を組みたいのですが、どうやればいいか分かりません。MATCH関数を使うような気がするのですが、そこから先が分かりません。

例）
１月１日　　４３点
１月２日　　８３点
１月３日　　８１点
１月４日　　２６点
・・・
この例であれば、８０点を初めて越えるのは「１月２日」、
初めて４０点を下回るのは「１月４日」という形で答えを返したいのですが、どなたかお知恵を貸してください。よろしくお願いします。

回答No.2

maron--5 · ベストアンサー

◆こんな方法はいかがでしょう
>８０点を初めて越えるのは「１月２日」
D2=MIN(INDEX((B2:B10<=80)*10^5+A2:A10,))

>初めて４０点を下回るのは「１月４日」
D5=MIN(INDEX(((B2:B10>=40)+(A2:A10=""))*10^5+A2:A10,))

お礼

ありがとうございます。
私が理想にしていた形そのものです。

質問なのですが、INDEX関数内で範囲を指定して掛け算、足し算まで入っていて、この関数の意味するところが良く理解できません。
この部分についても教えていただけないでしょうか？
よろしくお願いいたします。

回答No.6

maron--5

>INDEX関数内で範囲を指定して掛け算、足し算まで入っていて、この関数の意味するところが良く理解できません。

１）（(B2:B10>=40)+(A2:A10=""))*10^5　は40未満のデータを排除しています
２）「B2:B10の40以上」または、「A2:A10が空白」に10＾5（100000）を掛けます
３）数式バーのうち、（(B2:B10>=40)+(A2:A10=""))*10^5　の部分を指定して、「F9」をクリックします
４）{100000;100000;100000;0;0;100000;100000;100000;100000}　になり、B列40以上、A列空白が「100000」になっています
５）それに、A2:A10　を足しますと
６）数式バーのうち、((B2:B10>=40)+(A2:A10=""))*10^5+A2:A10　の部分を指定して、「F9」をクリックします
７）{139814;139815;139816;39817;39818;100000;100000;100000;100000}　になり、これのMINは「39817」になります
８）「39817」はシリアル値なので、表示形式を「日付」にすると、「1月4日」になります

お礼

ありがとうございました。大変ためになりました。
F9キーの使い方まで教えていただき、大変感謝です。
本当にありがとうございました。

回答No.5

imogasi

８０点以上の行で、行番号の最小の行の日付、を求める。
配列数式ですが
例データ
2月3日 23
2月14日 45
2月25日 82
3月4日 81
4月4日 34
4月7日 89
=INDEX(A1:A8,MIN(IF(B2:B8>80,ROW(B2:B8),99)))
と入れて、SHIFT+CTRL+Enterを同時押し。
９９はこのデータではありえない行数を入れる。
結果
2009/2/25　（このセル書式は日付に設定）
ーー
作業列を使うなら
A2:B7　　にデータ
A列　　　B列　　C列
2月3日 23
2月14日 45
2月25日 82 1
3月4日 81
4月4日 34
4月7日 89
C列は
=IF(COUNTIF($B$2:B2,">80")=1,1,"")
求める日付は
=INDEX(A2:A8,MATCH(1,$C$2:$C$8,0),1)
（このセル書式は日付に設定）
ーーー
下回った日も上記式を
初出だからMIN、点数判定部部分を<40に変えることで出来ると思います。

お礼

ありがとうございます。配列数式という方法があるんですね。
話には聞いていたんですが、こういうときに使うんですね。
あまり馴染みがなっかたので、今後覚えていこうと思います。
ありがとうございました。

回答No.4

KURUMITO

作業列を使った方法を紹介します。
データがＡおよびＢ列の2行目から下方にあるとします。
Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(COUNTIF(B$2:B2,"<="&40)=1,"A",IF(COUNTIF(B$2:B2,">="&80)=1,"B","")))
最初に４０点以下となった行にＡが、８０点以上になった行にＢが表示されます。
たとえばＤ２セルに４０点以下となった日、Ｄ３セルに８０点以上になった日と入力し、Ｅ２セルには次の式を
=INDEX(A:A,MATCH("A",C:C,0))
Ｅ３セルには次の式を入力します。
=INDEX(A:A,MATCH("B",C:C,0))
なお、Ｅ２およびＥ３のセルの表示形式は日付にします。
いずれの式も列を対象にしていますのでデータが次々に入力されても即座に対応できます。
なお、ＭＡＴＣＨ関数を検討したことでしょうが何点以上とか、何点以下とかの形で式をたて使うことができません。点数を昇順や降順で並べ替えをしたうえで使うことができなくもないですが、元のデータを並び替えるなど操作が面倒になりますね。

お礼

ありがとうございます。やはり作業列を使うのがスマートのようですね。
MATCH関数を使おうかと思ったんですが、KURUMITOさんのおっしゃるとおり、昇順並び替えの必要などがあり、あまり実用的なものにならず困っていました。。。
ありがとうございました。

回答No.3

maron--5

◆先ほどの回答での添付図で「83越え」になっていますが、正しくは「80越え」です
◆なお、回答式は「80越え」になっています

回答No.1

tom04

こんばんは！
色々方法はあると思いますが・・・

一例です。

↓の画像のような感じで作業列を使っています。

C2セルに
=IF(OR(B2="",B2<=80),"",ROW(A1))
D2セルに
=IF(OR(B2="",B2>=40),"",ROW(A1))

という数式をいれて、C2・D2セルを範囲指定した後に
C2セルのフィルハンドルで下へオートフィルでコピーします
（とりあえず100行まで対応できる数式にしていますので、100行までオートフィルでコピー）

そしてF2セルに
=INDEX($A$2:$A$100,SMALL(C$2:C$100,1))
と入れてG2セルまでオートフィルでコピーします
（セルの書式設定から表示形式は「日付」で・・・）

これで何とか希望に近い形にならないでしょうか？

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ありがとうございます。作業列を使えばいいんですね。
なんとか関数だけで完結させようとしてたので、
今後はこういうやり方も考えるようにしたいと思います。
ありがとうございました。