
テストの点数で、初めて40点を下回ったときの日付と、初めて80点を越えた時の日付を返すような関数を組みたいのですが、どうやればいいか分かりません。MATCH関数を使うような気がするのですが、そこから先が分かりません。
例)
1月1日 43点
1月2日 83点
1月3日 81点
1月4日 26点
・・・
この例であれば、80点を初めて越えるのは「1月2日」、
初めて40点を下回るのは「1月4日」という形で答えを返したいのですが、どなたかお知恵を貸してください。よろしくお願いします。
No.2ベストアンサー
- 回答日時:
◆こんな方法はいかがでしょう
>80点を初めて越えるのは「1月2日」
D2=MIN(INDEX((B2:B10<=80)*10^5+A2:A10,))
>初めて40点を下回るのは「1月4日」
D5=MIN(INDEX(((B2:B10>=40)+(A2:A10=""))*10^5+A2:A10,))

ありがとうございます。
私が理想にしていた形そのものです。
質問なのですが、INDEX関数内で範囲を指定して掛け算、足し算まで入っていて、この関数の意味するところが良く理解できません。
この部分についても教えていただけないでしょうか?
よろしくお願いいたします。
No.6
- 回答日時:
>INDEX関数内で範囲を指定して掛け算、足し算まで入っていて、この関数の意味するところが良く理解できません。
1)((B2:B10>=40)+(A2:A10=""))*10^5 は40未満のデータを排除しています
2)「B2:B10の40以上」または、「A2:A10が空白」に10^5(100000)を掛けます
3)数式バーのうち、((B2:B10>=40)+(A2:A10=""))*10^5 の部分を指定して、「F9」をクリックします
4){100000;100000;100000;0;0;100000;100000;100000;100000} になり、B列40以上、A列空白が「100000」になっています
5)それに、A2:A10 を足しますと
6)数式バーのうち、((B2:B10>=40)+(A2:A10=""))*10^5+A2:A10 の部分を指定して、「F9」をクリックします
7){139814;139815;139816;39817;39818;100000;100000;100000;100000} になり、これのMINは「39817」になります
8)「39817」はシリアル値なので、表示形式を「日付」にすると、「1月4日」になります

ありがとうございました。大変ためになりました。
F9キーの使い方まで教えていただき、大変感謝です。
本当にありがとうございました。
No.5
- 回答日時:
80点以上の行で、行番号の最小の行の日付、を求める。
配列数式ですが
例データ
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を同時押し。
99はこのデータではありえない行数を入れる。
結果
2009/2/25 (このセル書式は日付に設定)
ーー
作業列を使うなら
A2:B7 にデータ
A列 B列 C列
2月3日23
2月14日45
2月25日821
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
- 回答日時:
作業列を使った方法を紹介します。
データがAおよびB列の2行目から下方にあるとします。
C2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(COUNTIF(B$2:B2,"<="&40)=1,"A",IF(COUNTIF(B$2:B2,">="&80)=1,"B","")))
最初に40点以下となった行にAが、80点以上になった行にBが表示されます。
たとえばD2セルに40点以下となった日、D3セルに80点以上になった日と入力し、E2セルには次の式を
=INDEX(A:A,MATCH("A",C:C,0))
E3セルには次の式を入力します。
=INDEX(A:A,MATCH("B",C:C,0))
なお、E2およびE3のセルの表示形式は日付にします。
いずれの式も列を対象にしていますのでデータが次々に入力されても即座に対応できます。
なお、MATCH関数を検討したことでしょうが何点以上とか、何点以下とかの形で式をたて使うことができません。点数を昇順や降順で並べ替えをしたうえで使うことができなくもないですが、元のデータを並び替えるなど操作が面倒になりますね。
ありがとうございます。やはり作業列を使うのがスマートのようですね。
MATCH関数を使おうかと思ったんですが、KURUMITOさんのおっしゃるとおり、昇順並び替えの必要などがあり、あまり実用的なものにならず困っていました。。。
ありがとうございました。
No.1
- 回答日時:
こんばんは!
色々方法はあると思いますが・・・
一例です。
↓の画像のような感じで作業列を使っています。
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

ありがとうございます。作業列を使えばいいんですね。
なんとか関数だけで完結させようとしてたので、
今後はこういうやり方も考えるようにしたいと思います。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
エクセルで左から、最初に0より大きい値を抽出したい
Excel(エクセル)
-
EXCELで、指定の値を最初に越えたときの値と、その隣のセルの値を参照したい
Excel(エクセル)
-
初めて0以外の数値が出てきたときの値を表示(EXCEL)
Excel(エクセル)
-
-
4
エクセルシート内のある数値以上の最初のセルを検索したい
Excel(エクセル)
-
5
エクセルで、条件に合うセルのうち最も上の行の行数を返す方法
Excel(エクセル)
-
6
条件に合った最初の行番号を表示したい。
Excel(エクセル)
-
7
エクセルの表で一定の数量を超える前までの合計を算出することを繰り返す方法
Excel(エクセル)
-
8
列内の最初に現れる数字の入った行番号を知りたい
Excel(エクセル)
-
9
エクセルの散布図のX軸に文字を表示したいのですが、どうしたらよいのでしょうか?
Excel(エクセル)
-
10
EXCELで2つの数値のうち大きい方を採択する数式
Excel(エクセル)
-
11
EXCELにてローパスフィルタを作成する
その他(教育・科学・学問)
-
12
Excelでエラー(#N/Aなど)値を含む範囲で最大値や最小値をもとめ
その他(Microsoft Office)
-
13
エクセル数式: 基準値以下の最初の値の入ったセルの列数を返すには
Excel(エクセル)
-
14
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
15
たくさん作った同じ設定のグラフを一括して変更したい
Excel(エクセル)
-
16
最大値のあるセルの行番号のみを求めたいです。
Excel(エクセル)
-
17
値が入っている一番右のセル位置を返す方法
Excel(エクセル)
-
18
エクセルで別シートの同じ位置にオブジェクトをコピーしたい
その他(Microsoft Office)
-
19
ExcelのLINEST関数で空白セルを無視するようにしたい
その他(Microsoft Office)
-
20
一列に並んでいる大量のデータを10個ずつ平均(A1~A2000まである
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
文字列から英数字のみを抽出す...
-
SUMIF関数で、「ブランク以外を...
-
自分の左隣のセル
-
エクセルで、指定の値よりも大...
-
エクセル1行おきのセルを隣の...
-
文字数のカウントと平均数の算出
-
エクセルで特定のセル内にだけ...
-
EXCELでマイナス値の入ったセル...
-
excelで、空白を除いてデータを...
-
セルを結合した時のエクセル集...
-
エクセルで、A2のセルにA3...
-
エラー「#REF」の箇所を置き換...
-
一時間当たりの製造数を調べた...
-
エクセル、○が連続する回数を数...
-
EXCELのcountif関数での大文字...
-
EXECL バーコード生成でBarCode...
-
同一セル内の重複文字を削除し...
-
セルの内容表示が邪魔になる
-
エクセル シート全体の項目をカ...
-
数値を一つずつ別々のセルに分...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SUMIF関数で、「ブランク以外を...
-
文字列から英数字のみを抽出す...
-
自分の左隣のセル
-
EXCELのcountif関数での大文字...
-
excelで、空白を除いてデータを...
-
セルを結合した時のエクセル集...
-
エクセル1行おきのセルを隣の...
-
同一セル内の重複文字を削除し...
-
エクセルで、指定の値よりも大...
-
文字数のカウントと平均数の算出
-
エクセルで特定のセル内にだけ...
-
週の労働時間を計算するエクセル
-
EXCELでマイナス値の入ったセル...
-
エクセルに入力後、別シートの...
-
エクセルで、A2のセルにA3...
-
【Excel】4つとばしで合計する方法
-
一時間当たりの製造数を調べた...
-
=SUBTOTAL に =COUNTIF の機能...
-
エクセルで年月日から月日のみへ
-
Excelで大量のセルに一気に関数...
おすすめ情報