どの時間にあてはまるのかを調べたい

質問

以下のような表を作成中です。

　　　 A B
1 時間A 6：30
2 時間B 7：00
3 時間C 12：00
4 時間D 19：00
5 結果 時間Ａの前

B1からB4に時間を入力したときにB1の時間は
時間A～Cのどの時間の前なのか後なのか、
更に、B5の結果は(この場合は時間Aが6：30、時間Bが7：00なので30分以内）
その時間の30分以内だったか否かを求めたいと思っています。

先日からIFの組み合わせでやったり、
近似値を出して比較して抽出したりとやってみているのですが、
どうしてもできません…
このようなものを求めたいときは、どのようにしたらいいのでしょうか？

Windows 7、エクセル2010です。

ご教示よろしくお願いいたします。

回答No.3

MarcoRossiItaly · ベストアンサー

＞ＢとＣの間に1行挿入して、……

時間B～Dは、昇順に並べるということでいいですか？また、BとCだけでなく、CとDの間にも1行を挿入するということで、いいですね？

●行を挿入する場合（B7・B8セルに結果を表示）

B7　=offset(a2,if(b1<b2,0,2*int((match(b1,b2:b6,1))/2)),0)&"の"&if(b1<b2,"前",if(mod(match(b1,b2:b6,1),2),"後","前"))
B8　="30分以内"&if(min(abs(b1-b2),abs(b1-b4),abs(b1-b6))<="0:30:01"+0,"です","ではありません")

●行を挿入しない場合（B5・B6セルに結果を表示）

B5　=if(b1<b2,a2&"の前",if(b4<=b1,a4&"の後",if(b1<(offset(b2,match(b1,b2:b4,1)-1,0)+offset(b2,match(b1,b2:b4,1),0))/2,offset(a2,match(b1,b2:b4,1)-1,0)&"の後",offset(a2,match(b1,b2:b4,1),0)&"の前")))
B6　="30分以内"&if(min(index(abs(b1-b2:b4),))<="0:30:01"+0,"です","ではありません")

※「"0:30:01"」（0時間30分1秒）と記述しているのは、引き算による計算誤差への対策のためです。

お礼

ありがとうございます。
無事できました。
本当に助かりました。

回答No.6

MarcoRossiItaly

No.3・5です。度々で申し訳ないのですが、もう一つ、若干の修正をお願いします。

No.3の行を挿入しない場合で、B5セルの数式中、「(OFFSET+OFFSET)/2」という部分は、OFFSET関数を第5引数まで指定することで、「SUM(OFFSET)/2」と、より短く書けるのでした。

B5　=if(b1<b2,a2&"の前",if(b4<=b1,a4&"の後",if(b1<sum(offset(b2,match(b1,b2:b4,1)-1,0,2,1))/2,offset(a2,match(b1,b2:b4,1)-1,0)&"の後",offset(a2,match(b1,b2:b4,1),0)&"の前")))

回答No.5

MarcoRossiItaly

No.3 です。たびたびすみません。No.3 に書いた B7 セルの数式の中に、余計な「( )」が付いていました。あってもなくても計算の結果は同じなのですが、可読性が下がるというか、意味のないものなので、次のとおり削除でお願いします。

=offset(a2,if(b1<b2,0,2*int(match(b1,b2:b6,1)/2)),0)&"の"&if(b1<b2,"前",if(mod(match(b1,b2:b6,1),2),"後","前"))

※INT関数の中身から「( )」を1組、削除しました。

回答No.4

kagakusuki

B5セルに入力する関数は次の様なものでは如何でしょうか？

=IF(AND(ISNUMBER(B$1),COUNT(B$2:B$4)),IF(COUNTIF(B$2:B$4,">="&B$1),IF(COUNTIF(B$2:B$4,"<="&B$1),"","全ての時間の前"&CHAR(10))&INDEX($A$2:$A$4,MATCH(LARGE(B$2:B$4,COUNTIF(B$2:B$4,">="&B$1)),B$2:B$4,0))&IF(COUNTIF(B$2:B$4,B$1),"と同じ","の"&IF(B$1+"0:30"=LARGE(B$2:B$4,COUNTIF(B$2:B$4,">="&B$1)),CHAR(10)&"30分前","前"&CHAR(10)&"30分以内"&IF(B$1+"0:30"<LARGE(B$2:B$4,COUNTIF(B$2:B$4,">="&B$1)),CHAR(10)&"ではありません",""))),"")&IF(AND(COUNTIF(B$2:B$4,"<"&B$1),COUNTIF(B$2:B$4,B$1)=0),IF(COUNTIF(B$2:B$4,">="&B$1),"","全ての時間の後"&CHAR(10))&IF(COUNTIF(B$2:B$4,">"&B$1),CHAR(10),"")&INDEX($A$2:$A$4,MATCH(SMALL(B$2:B$4,COUNTIF(B$2:B$4,"<"&B$1)),B$2:B$4,0))&"の"&IF(B$1-"0:30"=SMALL(B$2:B$4,COUNTIF(B$2:B$4,"<"&B$1)),CHAR(10)&"30分後","後"&CHAR(10)&"30分以内"&IF(B$1-"0:30">SMALL(B$2:B$4,COUNTIF(B$2:B$4,"<"&B$1)),CHAR(10)&"ではありません","")),""),"")

回答No.2

KURUMITO

判断の基準が３０分以内かどうかの判定と思いますがより詳細にはどちらは近い方の時間を判定してその時間に比べて何時間何分大きいとか小さいを表示するようにしてはいかがでしょう。
B５セルには次の式を入力します。

=IF(B1<=B2,A2&"よりも"&TEXT(B2-B1,"h:m")&"小さい",IF(B1>=B4,A4&"よりも"&TEXT(B1-B4,"h:m")&"大きい",IF(SMALL(B1:B5,RANK(B1,B1:B4,1)+1)-B1>=B1-SMALL(B1:B5,RANK(B1,B1:B4,1)-1),INDEX(A1:A4,MATCH(SMALL(B1:B5,RANK(B1,B1:B4,1)-1),B1:B4,0))&"よりも"&TEXT(B1-SMALL(B1:B5,RANK(B1,B1:B4,1)-1),"h:m")&"大きい",INDEX(A1:A4,MATCH(SMALL(B1:B5,RANK(B1,B1:B4,1)+1),B1:B4,0))&"よりも"&TEXT(SMALL(B1:B5,RANK(B1,B1:B4,1)+1)-B1,"h:m"&"小さい"))))

３０分以内のみを表示させるのでしたらもっと複雑な式になりますね。

補足

ありがとうございます。
B5に式を入れてみましたが、
循環参照による警告が出て、0が表示されてしまいました。

回答No.1

aokii

B1の時間はA2セルがその時間の前ですので、
=A2
その時間の30分以内だったか否かは、
=IF((B2-B1)<=(0.5/24),"30分以内","30分超え")

補足

説明の一部に誤りがありました。
申し訳ありません。

A B
1 時間A 6：30
2 時間B 7：00
3 時間C 12：00
4 時間D 19：00
5 結果 時間Bの前
　　　　　　30分以内です

B1からB4に時間を入力したときにB1の時間は
時間B～Dのどの時間の前なのか後なのか、
更に、B5の結果は(この場合は時間Aが6：30、時間Bが7：00なので30分以内）
その時間の30分以内だったか否かを求めたいと思っています。

A B
1 時間A 7：15
2 時間B 7：00
3 時間C 12：00
4 時間D 19：00
5 結果 時間Bの後
　　　　　　30分以内です

A B
1 時間A 11：10
2 時間B 7：00
3 時間C 12：00
4 時間D 19：00
5 結果 時間Cの前
　　　　　　30分以内ではありません

このような形で表示をしたいと思っています。
Bの後なのかＣの前なのかを判断させる為に、
間の時間を入れなければいけなかったことに、
今、気づきました。

ＢとＣの間に1行挿入して、
9：30と表示される式を以下のように入れたいと思っています。
時間Ａ－Ｂの行
=(B4-B2)/2+B2で9：30と表示
時間Ｂより後で9：30より前の時間だった場合は、時間Ｂの後、
時間Ｂより後で9：30以降の時間だった場合は、時間Ｃの前

そのうえでもう一度ご教示いただけないでしょうか？
よろしくお願いいたします。