出産前後の痔にはご注意!

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

例)
1月1日  43点
1月2日  83点
1月3日  81点
1月4日  26点
・・・
この例であれば、80点を初めて越えるのは「1月2日」、
初めて40点を下回るのは「1月4日」という形で答えを返したいのですが、どなたかお知恵を貸してください。よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (6件)

◆こんな方法はいかがでしょう


>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,))
「エクセルで、指定の値よりも大きい数字を最」の回答画像2
    • good
    • 2
この回答へのお礼

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

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

お礼日時:2009/08/23 10:11

>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日」になります
「エクセルで、指定の値よりも大きい数字を最」の回答画像6
    • good
    • 1
この回答へのお礼

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

お礼日時:2009/08/23 17:26

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に変えることで出来ると思います。
    • good
    • 2
この回答へのお礼

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

お礼日時:2009/08/23 10:21

作業列を使った方法を紹介します。


データが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関数を検討したことでしょうが何点以上とか、何点以下とかの形で式をたて使うことができません。点数を昇順や降順で並べ替えをしたうえで使うことができなくもないですが、元のデータを並び替えるなど操作が面倒になりますね。
    • good
    • 0
この回答へのお礼

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

お礼日時:2009/08/23 10:17

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


◆なお、回答式は「80越え」になっています
    • good
    • 0

こんばんは!


色々方法はあると思いますが・・・

一例です。

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

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
「エクセルで、指定の値よりも大きい数字を最」の回答画像1
    • good
    • 0
この回答へのお礼

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

お礼日時:2009/08/23 10:05

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q初めて0以外の数値が出てきたときの値を表示(EXCEL)

化学系の仕事をしていて実験の値をエクセルでまとめています。
こんなことは出来るのでしょうか?

1分ごとに測定を行いますが、はじめの数分は0の値が続きます。
簡単に例を挙げるとこんな感じです。
【例】
分→測定値
00→0
01→0
02→0
03→0
04→2.36
05→4.83
06→3.52

このとき初めて数値が0以外の値が出たときの2.36の値を適当なセルに表示させるにはどのような方法がありますか?測定値の数値には規則性がないのでMAX関数やMIN関数が使えません。何かいい方法教えてください。

Aベストアンサー

A列にデータがあるとしたら、
=INDEX(A1:A99,MATCH(TRUE,A1:A99<>0,0))
を配列数式として入力(Ctrl+Shift+Enter)
でいかが?

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

Q条件に合った最初の行番号を表示したい。

excelで、Sheet1に、Aに丁目、Bに番地、Cに号を割り振りました。
  A  B  C
1  1  1  15
2  1  1  18
3  1  1  21
4  1  2  7
5  1  2  14
6  2  1  33
7  2  1  36
8  2  1  37
9  3  1   15
そこで、条件に合った最初の行番号を表示したいのです。
例えば、2丁目1番地の最初は、6行目なので、F1などに6と表示する方法を知りたいのです。
1丁目2番地だったら、F1は4になるようにしたいのです。
よろしくお願いします。

Aベストアンサー

こんばんは!

>例えば、2丁目1番地

D1sるに○丁目 の○の数値、E1セルに△番地 の△数値を入力するとします。

F1セルに
=MIN(IF((A1:A1000=D1)*(B1:B1000=E1),ROW(A1:A1000)))

これは排列数式になりますので、Shift+Ctrl+Enterで確定してください。

この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → F1セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。m(_ _)m

Q指定範囲から任意の近似値を探したい

画像のようなものを作ろうと思っています。
ですが、vlookupやindex・match関数では私の力ではうまい具合にはいきませんでした。

どのように数式を入れるとよろしいのでしょうか?

Aベストアンサー

こんにちは!

G12セルに
=MIN(IF(B3:E15>G8,B3:E15))
これは配列数式になりますので、Ctrl+Shift+Enterで確定!

G14セルに
=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:B15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E3)),4)

という数式を入れてみてください。m(_ _)m

Qエクセルで、ある範囲のセルのうち、5以上の値で最小の値を求めたい

エクセルで、ある範囲のセルのうち、5以上の値で最小の値を求めたいです。


1 3 2 6 9 0 4

ならば、 6が答え。

関数が無ければ、VBAで作った関数でも構いません。

また、VBAで範囲指定と条件指定が引数となる方法も知りたいです。

Aベストアンサー

=MIN(IF(セル範囲>=5,セル範囲))
(Shift + Ctrl + Enter)

DMIN関数もあるし。(ヘルプ見てね)

ユーザー定義関数も簡単ですよ、ぜひ作ってみてください。(こんなこともできないようでは、コードをもらってもメンテも作り込みもできません。)

QExcelで、条件と一致する最後のセルを検索したい

Excelで、条件と一致する最後のセルを検索したいのですが、どの関数をどのように使えばいいかわかりません。
どなたかお力をお貸し下さい。

問)
日付  社名  品名  個数
1/1   A    あ    1
1/1   C    い    2
1/2   B    う     3
1/2   A    え    4
1/3   C    お    5
1/3   A    あ    3

答)
A社の最終購入日は  1/3 あ 3
B社の最終購入日は  1/2 う  3
C社の最終購入日は  1/3 お 5

となるようお願いします。

Aベストアンサー

エクセルには「最後の該当」を検索する関数はありません。
無駄に複雑な数式を駆使すればもちろん「やればできます」が、添付図のようにちょっと工夫するだけで、普段使いの数式だけでやっつけるのがお勧めです。

A2:
=IF(OR(C2="",COUNTIF($C$2:C2,C2)<COUNTIF(C:C,C2)),"",C2)
以下コピー

答えは簡単なVLOOKUP関数を使うだけです。
H2:
=IF(COUNTIF($A:$A,$G2),VLOOKUP($G2,$A:$E,2,FALSE),"")




まぁ一応ご参考に作業列を使わない方法:
H2:
=IF(COUNTIF($C:$C,$G2),INDEX(B:B,MAX(IF($C$2:$C$999=$G2,ROW($C$2:$C$999)))))
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する

説明は省略しますので、ブラックボックスで使ってください。

Qエクセルの数値がある一定の数値を越えたらO.K.と表示したい。

エクセルで表を作っていたのですが、その最後にOKかNOか判断しなければいけない欄があるのです。
たとえば、1以上になったときにその横の欄にOKと表示させ、1以下の時にはNOと表示させるような関数とか命令はあるのでしょうか?。
マクロを使えば出来るのでしょうが当方なにぶんあんまりエクセルには詳しくないので出来るだけ簡単に出来る方法があれば教えてください。

Aベストアンサー

IF関数を使って簡単に実現できます。

例えばA5のセルの中身が100以上であればOKと表示しそれ以下であればNOと表示すると仮定しますと、

=IF(A5>=100,"OK","NO")

となります。 ダブルクオーテーションの中は表示したい文字列であれば何でも結構です。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング