[[20251205205146]]　『関数で抽出し、比較することは可能でしょうか』（れお）　ページの最後に飛ぶ

『関数で抽出し、比較することは可能でしょうか』（れお）

お世話になります。

勤怠データを作成しており、下記結果を関数で得ることは可能でしょうか。

＜求めたい結果＞
・出勤時間(B列)と、備考欄(D列)に記載された「早出(●:●●～)」の時間が1時間以上乖離している場合、E列に「乖離あり」と表示
・退勤時間(C列)と、備考欄(D列)に記載された「残業(～●:●●)」の時間が1時間以上乖離している場合、F列に「乖離あり」と表示

日付出勤退勤備考
12/1 9:30 22:30 出社修正-退社修正-早出(9:30～)-残業(～21:00)
12/2 9:30 19:45 出社修正-早出(9:30～)-残業(～19:45)
12/3 11:30 19:45 出社修正-早出(9:30～)-残業(～19:45)
12/4
12/5 10:00 19:30 残業(～19:30)
12/6
12/7
12/8 9:30 19:00 早出(9:30～)
12/9 9:30 19:00 早出(9:30～)
12/10 10:00 20:30 残業(～20:30)
12/11 10:00 21:00 残業(～22:30)
12/12 10:15 19:15 出社修正
12/13
12/14
12/15 10:00 19:00

上記の場合は、

12/1 F列(退勤)に「乖離あり」と表示
12/3 E列(出勤)に「乖離あり」と表示
12/11 F列(退勤)に「乖離あり」と表示

備考欄に修正など操作により様々な文言が入るためうまく当該条件で抽出し、時間として比較することができずにいます。

よろしくお願いします

＜使用 Excel：unknown、使用 OS：unknown ＞

 E2セル =REPT("乖離あり",ABS(LOOKUP(9,LEFT(MID(TRIM(MID(SUBSTITUTE(D2,"早出",REPT(" ",50)),50,50)),2,5)&TEXT(B2,"h:m"),{1,2,3,4,5})*1)-B2)>"0:59:59"*1)
 F2セル =REPT("乖離あり",ABS(LOOKUP(9,LEFT(MID(TRIM(MID(SUBSTITUTE(D2,"残業",REPT(" ",50)),50,50)),3,5)&TEXT(C2,"h:m"),{1,2,3,4,5})*1)-C2)>"0:59:59"*1)

 それぞれ下へコピー、でいけるかも

（半平太） 2025/12/05(金) 22:44:51

 もしもExcel365なら、以下のようにも書けます。

 E2セル
 =LET(
     出勤2,IFERROR(REGEXEXTRACT(D2,"早出\((\d{1,2}:\d{2})～\)",2,1)*1,0),
     IF(出勤2>0,IF(ABS(B2-出勤2) > TIME(0,59,59),"乖離あり",""),"")
 )

 F2セル
 =LET(
     退勤2,IFERROR(REGEXEXTRACT(D2,"残業\(～(\d{1,2}:\d{2})\)",2,1)*1,0),
     IF(退勤2>0,IF(ABS(C2-退勤2) > TIME(0,59,59),"乖離あり",""),"")
 )
 それぞれ下にコピーします。（なお式の最初に、半角スペースを入れないで下さい。）
（xyz） 2025/12/06(土) 08:37:58

コメント返信：

[ 一覧(最新更新順) ]

[[20251205205146]] 『関数で抽出し、比較することは可能でしょうか』（れお） ページの最後に飛ぶ

[[20251205205146]]　『関数で抽出し、比較することは可能でしょうか』（れお）　ページの最後に飛ぶ