エクセルの関数で教えてください。

やりたい事は画像の様な表で
ＢとＣを比較して
・①のようにＣの方が遅ければ、Ｄに「１」が入るようにする。
・②のようにＢとＣが３０分以上乖離していれば、Ｅに「１」が入るようにする。
・ただし、どちらも午前５時を基準に比較したいので
　③のような場合、通常なら「１」がつくが、Ｄの方が早く３０分以内の乖離なので「１」はつかない。
上の条件を満たす関数をＤ，Ｅに入れたい

さらにＨとＩを比較して
・④のようにＩの方が早ければ、Ｊに「１」が入るようにする。
・⑤のようにＨとＩが３０分以上乖離していれば、Ｋに「１」が入るようにする。
・ただし、どちらも午前５時を基準に比較したいので
　⑥のような場合、通常なら「１」がつくが、Ｉの方が遅く３０分以内の乖離なので「１」はつかない。
上の条件を満たす関数をＪ，Ｋに入れたい

少しわかりにくいかもしれませんが、作りたい数式は以上です。
皆さんのお力を貸してください。

質問者からの補足コメント

• ①完成例として手入力しました。
  ②例として挙げたのでシリアル値かどうかが良く分かりません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/10/16 22:10

No.12 ベストアンサー 回答者： GooUserラック 回答日時： 2019/10/18 12:38

計算式を考えました。

B,C,H,I 列を基準値を考慮した場合の分数は以下のようになります、これを基に後は比較すれば良いだけです。
=IF(HOUR(B1)<5,HOUR(B1)*60+MINUTE(B1)+1440,HOUR(B1)*60+MINUTE(B1))
=IF(HOUR(C1)<5,HOUR(C1)*60+MINUTE(C1)+1440,HOUR(C1)*60+MINUTE(C1))
=IF(HOUR(H1)<5,HOUR(H1)*60+MINUTE(H1)+1440,HOUR(H1)*60+MINUTE(H1))
=IF(HOUR(I1)<5,HOUR(I1)*60+MINUTE(I1)+1440,HOUR(I1)*60+MINUTE(I1))
これらを別の列に置いておけるならば変更などもとても楽です。

D1 セルには「=IF(E1="",IF(Ⓑ<Ⓒ,1,""),"")」
E1 セルには「=IF(ABS(Ⓑ-Ⓒ)<30,"",1)」
J1 セルには「=IF(K1="",IF(Ⓗ>Ⓘ,1,""),"")」
K1 セルには「=IF(ABS(Ⓗ-Ⓘ)<30,"",1)」
Ⓑ は「(IF(HOUR(B1)<5,HOUR(B1)*60+MINUTE(B1)+1440,HOUR(B1)*60+MINUTE(B1)))」と置き換えて下さい。
Ⓒ は「(IF(HOUR(C1)<5,HOUR(C1)*60+MINUTE(C1)+1440,HOUR(C1)*60+MINUTE(C1)))」と置き換えて下さい。
Ⓗ は「(IF(HOUR(H1)<5,HOUR(H1)*60+MINUTE(H1)+1440,HOUR(H1)*60+MINUTE(H1)))」と置き換えて下さい。
Ⓘ は「(IF(HOUR(I1)<5,HOUR(I1)*60+MINUTE(I1)+1440,HOUR(I1)*60+MINUTE(I1)))」と置き換えて下さい。

例えば D1 セルは以下のようになります。
=IF(E1="",IF((IF(HOUR(B1)<5,HOUR(B1)*60+MINUTE(B1)+1440,HOUR(B1)*60+MINUTE(B1)))<(IF(HOUR(C1)<5,HOUR(C1)*60+MINUTE(C1)+1440,HOUR(C1)*60+MINUTE(C1))),1,""),"")

No.13 回答者： GooUserラック 回答日時： 2019/10/18 23:47

No.12 の補足

・「分数」は「ぶんすう」ではなく「ふんすう」です。
・「1440」は１日の分数（ふんすう）です。（24×60）←計算出来るものは先に計算しておいた方がスピードを稼ぐ事が出来ます。
・分数（ふんすう）にすることで見た目との誤差が生じなくなります。（割り算など誤差が生じるものが無い事が重要です）
・「これらを別の列に置いておけるならば変更などもとても楽です。」はセルを変数の入れ物として使えるので仕組みを理解するのも楽だと思います。

No.11 回答者： GooUserラック 回答日時： 2019/10/18 03:13

rukaandkaito 様へ

失礼かもしれませんが私の結果と比べさせていただきました。

☆「５時以前」の処理に問題が有るパターン
B「3:59」 C「3:34」の場合
これは「パターン②」ではなく「パターン③」が正解だと思います。
B「2:51」 C「3:19」の場合
これは「パターン②」ではなく「パターン①」が正解だと思います。

☆「誤差処理」に問題がありそうなパターン
H「19:43:19」 K「20:13:06」の場合
見た目はそれぞれ「19:43」「20:13」と表示されるので、
これは「パターン⑤」になった方が良いのでは？
もちろん「秒」まで考慮するなら「パターン⑥」が正解です。

計算式だと条件が少し複雑になるだけで間違い個所を直すのが大変になるのでマクロなどのプログラムを覚えた方が宜しいかも？

ちなみにNo.4 のコードですがエラーチェックなどしなければ以下のようにかなり単純なものになります。（重かったりしたらこっちを使った方が良いかも）

Function MinDiff(開始時刻 As Date, 終了時刻 As Date, Optional 基準時刻 As String = "00:00") As Variant

Dim 開始分 As Long
Dim 終了分 As Long
Dim 基準分 As Long

　基準分 = CLng(Mid(基準時刻, 1, 2)) * 60 + CLng(Mid(基準時刻, 4, 2))
　If 基準分 < 1440 Then
　　開始分 = Hour(開始時刻) * 60 + Minute(開始時刻)
　　終了分 = Hour(終了時刻) * 60 + Minute(終了時刻)
　　If 開始分 < 基準分 Then 開始分 = 開始分 + 1440
　　If 終了分 < 基準分 Then 終了分 = 終了分 + 1440
　　MinDiff = 終了分 - 開始分
　Else
　　開始分 = Int(開始時刻 * 1440)
　　終了分 = Int(終了時刻 * 1440)
　　MinDiff = 終了分 - 開始分
　End If

End Function

しかも汎用性を削ればさらに単純です。

Function MinDiff(開始時刻 As Date, 終了時刻 As Date, Optional 基準時刻 As String = "00:00") As Variant

Dim 開始分 As Long
Dim 終了分 As Long
Dim 基準分 As Long

　基準分 = CLng(Mid(基準時刻, 1, 2)) * 60 + CLng(Mid(基準時刻, 4, 2))

　　開始分 = Hour(開始時刻) * 60 + Minute(開始時刻)
　　終了分 = Hour(終了時刻) * 60 + Minute(終了時刻)
　　If 開始分 < 基準分 Then 開始分 = 開始分 + 1440
　　If 終了分 < 基準分 Then 終了分 = 終了分 + 1440
　　MinDiff = 終了分 - 開始分

End Function

No.10 回答者： rukaandkaito 回答日時： 2019/10/17 13:41

まず基準時間を５時にしたいという事なので、判定式の際にその部分を含めれば良いと考えます。

B列の判定は「IF(B1<TIMEVALUE("05:00"),1,0)」といった具合で５時以前の場合に１日加算して計算します。
次に乖離が３０分という事なので、差分が「ー（マイナス）」の場合を考慮して、２乗した値の平方根を使用します。
C列およびJ列の判定は
ということで関数を作ってみました
--------------------------------------------------------------------------------
D1：[=IF(E1="",IF(B1+IF(B1<TIMEVALUE("05:00"),1,0)<C1,"1",""),"")]
E1：[=IF(SQRT((C1-(B1+IF(B1<TIMEVALUE("05:00"),1,0)))^2)>TIMEVALUE("00:30"),"1","")]
J1：[=IF(K1="",IF(H1+IF(H1<TIMEVALUE("05:00"),1,0)>(I1+IF(I1<TIMEVALUE("05:00"),1,0)),"1",""),"")]
K1：[=IF(SQRT(((I1+IF(I1<TIMEVALUE("05:00"),1,0))-(H1+IF(H1<TIMEVALUE("05:00"),1,0)))^2)>TIMEVALUE("00:30"),"1","")]
--------------------------------------------------------------------------------
お試しください

この回答へのお礼

ありがとうございます。
早速試してみます！

お礼日時：2019/10/17 20:22

No.9 回答者： GooUserラック 回答日時： 2019/10/17 09:02

d-q-t-p 様へ

折角なのでもう少しチェックしてみました。２４時間未満なので RAND関数で作った数が書式設定さえ変えればそのまま使えるので、私の作ったものと比べてみました。
①Ｃ列が５時より前の物は誤動作するようです。
例① 2:56 2:39 本当はパターン③になる筈
例② 5:10 4:54 本当はパターン②になる筈
②秒のデータが隠れていた場合（手入力なら問題ないけどコピペや計算によるものだと可能性が有ります）
例③ 13:14:29 12:44:49 ⇒ 13:14 12:44 と表示されるので パターン②として処理した方が良いと思います。（秒まで考えるなら d-q-t-p 様が正解です）

No.8 回答者： GooUserラック 回答日時： 2019/10/17 08:32

d-q-t-p 様へ

大変申し訳ございません。コピペされたデータで書式設定が「h:mm」の場合（見た目は問題なく見えてしまっている）対応用にテストデータにわざと日付データを入れたものでテストしたので誤作動したようです。時間だけのデータの場合は問題ありませんでした。本当に申し訳ございませんでした。

No.7 回答者： GooUserラック 回答日時： 2019/10/17 08:05

d-q-t-p 様へ

本当は以下のようになるのでは？
Ｂ　 Ｃ
0:27 0:26 パターン③
0:35 1:02 パターン①
9:26 9:48 パターン①

No.6 回答者： GooUserラック 回答日時： 2019/10/17 07:45

多分 〇数字はパターンを表しているのだと思います

No.5 回答者： d-q-t-p 回答日時： 2019/10/17 07:04

②と⑤が２個ずつある意味が分かりません。

D1：=IF(AND(E1="",C1>B1+(B1<5/24)),1,"")
E1：=IF(ABS(C1-B1-(B1<5/24))+10^-5>1/48,1,"")

逆も考え方は一緒なので省略。
「10^-5」は演算誤差対策です。

それにしても最終的にやりたいことを書いた方がいいと思いますけど。
結果に数値と文字列（「1」と「""」）をまぜこぜにすること自体が
微妙に思います。

No.4 回答者： GooUserラック 回答日時： 2019/10/17 06:35

ちなみに例は間違っていますよね？

図の赤で囲ったようになるのが正解ですよね？

ワークシート関数でも出来ないわけではないのですが、時間誤差による誤動作などを除くとえらく計算式が長くなるので途中であきらめました（変数が使えないのは辛い！）

マクロ（ＶＢＡ）を使っても良いのなら、汎用性の有るユーザー定義関数を作ってみたので使ってみて下さい。詳しい使い方は数日中に関数とともに私のＨＰ（ https://luckshp.web.fc2.com/ ）に「分差判定」として載せる予定です。興味がある人は覗いて下さい。

このユーザー定義関数（MinDiff）を使った場合は次の計算式で出来ます。
Ｄ１セルに「=IF(E1=1,"",IF(MinDiff(B1,C1,"05:00")>0,1,""))」
Ｅ１セルに「=IF(ABS(MinDiff(B1,C1,"05:00"))>=30,1,"")」
Ｊ１セルに「=IF(K1=1,"",IF(MinDiff(H1,I1,"05:00")<0,1,""))」
Ｋ１セルに「=IF(ABS(MinDiff(H1,I1,"05:00"))>=30,1,"")」

以下がユーザー定義関数のコードです。標準モジュールにコピペして下さい。
（基準時刻を考慮た上での終了時刻から開始時刻を引いた分数が返ります）

Function MinDiff(開始時刻 As Date, 終了時刻 As Date, Optional 基準時刻 As String = "00:00") As Variant

Dim 開始分 As Long
Dim 終了分 As Long
Dim 基準分 As Long

　If Len(基準時刻) = 5 Then
　　If IsNumeric(Mid(基準時刻, 1, 1)) Then
　　　If IsNumeric(Mid(基準時刻, 2, 1)) Then
　　　　If Mid(基準時刻, 3, 1) = ":" Then
　　　　　If IsNumeric(Mid(基準時刻, 4, 1)) Then
　　　　　　If IsNumeric(Mid(基準時刻, 4, 1)) Then
　　　　　　　If CLng(Mid(基準時刻, 4, 2)) < 60 Then
　　　　　　　　基準分 = CLng(Mid(基準時刻, 1, 2)) * 60 + CLng(Mid(基準時刻, 4, 2))
　　　　　　　　If 基準分 < 1440 Then
　　　　　　　　　開始分 = Hour(開始時刻) * 60 + Minute(開始時刻)
　　　　　　　　　If 開始分 < 1440 Then
　　　　　　　　　　終了分 = Hour(終了時刻) * 60 + Minute(終了時刻)
　　　　　　　　　　If 終了分 < 1440 Then
　　　　　　　　　　　If 開始分 < 基準分 Then 開始分 = 開始分 + 1440
　　　　　　　　　　　If 終了分 < 基準分 Then 終了分 = 終了分 + 1440
　　　　　　　　　　　MinDiff = 終了分 - 開始分
　　　　　　　　　　Else
　　　　　　　　　　　MinDiff = CVErr(xlErrNum)
　　　　　　　　　　End If
　　　　　　　　　Else
　　　　　　　　　　MinDiff = CVErr(xlErrNum)
　　　　　　　　　End If
　　　　　　　　Else
　　　　　　　　　開始分 = 開始時刻 * 1440
　　　　　　　　　If 開始分 <= 基準分 Then
　　　　　　　　　　If 終了分 <= 基準分 Then
　　　　　　　　　　　MinDiff = 終了分 - 開始分
　　　　　　　　　　Else
　　　　　　　　　　　MinDiff = CVErr(xlErrNum)
　　　　　　　　　　End If
　　　　　　　　　Else
　　　　　　　　　　MinDiff = CVErr(xlErrNum)
　　　　　　　　　End If
　　　　　　　　End If
　　　　　　　Else
　　　　　　　　MinDiff = CVErr(xlErrNA)
　　　　　　　End If
　　　　　　Else
　　　　　　　MinDiff = CVErr(xlErrNA)
　　　　　　End If
　　　　　Else
　　　　　　MinDiff = CVErr(xlErrNA)
　　　　　End If
　　　　Else
　　　　　MinDiff = CVErr(xlErrNA)
　　　　End If
　　　Else
　　　　MinDiff = CVErr(xlErrNA)
　　　End If
　　Else
　　　MinDiff = CVErr(xlErrNA)
　　End If
　Else
　　MinDiff = CVErr(xlErrNA)
　End If

End Function