Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
打刻データの出勤時間の計算方法について
投稿日時: 20/08/07 12:33:17
投稿者: たんしお

打刻データのExcelでの算出方法について
定時時間より前に出社した場合でも定時時間を表示して集計する方法を教えていただけますでしょうか。
定時時間 9:00 打刻時間 8:25 の場合、9:00と表示して、労働時間を集計したいです。
数式をいれてやってみたのですが、うまくできませんでした。
 
現在の集計方法
打刻データをCSVで抽出して、別シートに張り付けて労働時間を集計しております。
 
Aセルの出勤時間 SUMIFS関数にてCSVデータの日付と名前を条件指定して、抽出しております。
Bセルの退勤時間 SUMIFS関数にてCSVデータの日付と名前を条件指定して、抽出しております。
Cセルの労働時間 IF(A1>0,B1-A1-"1:00","")にて集計
 
定時時間より早く来た場合の打刻時間を補正して計算したいので、セルを追加してみました。
 
Dセル Aセルの出勤時間を =IF(A1=0,"",MAX(A1,TIME(9,0,0))) ここは反映されました。
しかし、Cセルの数式のA1をD1に変更しただけだと打刻データの入ってない箇所がすべて
#VALUE!と表示されてしまいます。
どこが間違っているのかわからず困っております。
 

回答
投稿日時: 20/08/07 13:00:52
投稿者: んなっと

よく読んでいませんが、これだとどうですか?
 
C1
=IF(D1<>"",B1-D1-"1:00","")

回答
投稿日時: 20/08/07 13:03:47
投稿者: WinArrow
投稿者のウェブサイトに移動

>Cセルの労働時間 IF(A1>0,B1-A1-"1:00","")

Cセルの労働時間 IF(D1>0,B1-D1-"1:00","")
ってことですよね?
 
まず
>Dセル Aセルの出勤時間を =IF(A1=0,"",MAX(A1,TIME(9,0,0)))
A列セルが「0」・・・・疑問が残るが・・・・打刻データがない場合、「""」(空白文字列という)をセットしています。これは、文字列ですから、数値と比較すると、大きいと判断されます。
従って、C列の数式では、「B1-D1-"1:00"」が働きますが、D1は文字列だから計算できません。
 
そもそも、
セルが空白なのかチェックすうのに
なぜ、「A1=0」のように「0」を使って数値判断するのでしょうか?
単純に
=IF(A1=""
のように素直にできませんか?

投稿日時: 20/08/07 15:06:05
投稿者: たんしお

ご返信ありがとうございます。
 
数式をしっかり理解しておりませんでした。
質問内容が変わってきてしまいますが、
 
Dセル =IF(A1="","",MAX(A1,TIME(9,0,0)))とした場合なんですが、
A1セルには数式が入っているため、空欄と認識されず、
9:00が返ってきてしまいます。これを回避する方法はあるのでしょうか

回答
投稿日時: 20/08/07 15:38:42
投稿者: radames1000

A列にSUMIFSが入っているのであれば打刻データがなければ0になるので
A1=0でいいと思います。
 
修正には2パターンあると思います。
■パターン1
C列
=IF(D1>0,B1-D1-"1:00","")
D列
=IF(A1=0,0,MAX(A1,TIME(9,0,0)))
 
■パターン2※んなっとさん提案の方法です。
C列
=IF(D1<>"",B1-D1-"1:00","")
※んなっとさんの式
D列
=IF(A1=0,"",MAX(A1,TIME(9,0,0)))
↑これはたんしおさんが書かれた式のままです

投稿日時: 20/08/09 07:36:53
投稿者: たんしお

回答いただきました皆様ありがとうございました。
radames1000さん パターン2(んなっとさん提案)でうまく表示できました。
んなっとさん 早々に回答いただいたのにすみません。
ありがとうございました。