Excel関数での勤務時間並びに残業、深夜残業時間の計算方法

[A1]出社時間 9:00
[B1]退社時間 23:45
の値から
かなりややこしい条件があるのですが。。。
昼休憩が1時間
勤務時間が8時間で15分の休憩(9:00出社の場合18:00～18:15)
勤務時間が10時間で15分の休憩(9:00出社の場合20:15～20:30)
休憩時間は結果から引いている
出社時間には変動があります。
出社時間にかかわらず夜間作業は22:00以降となってます(退社時間が22:15に0.25)

これらの条件で
[C1]総稼動時間 13.25
[D1]時間外 5.25
[E1]夜間作業 1.75
を出せる関数を教えていただきたいのです。出来れば出社、退社に時間が入ってない場合はそれぞれ空白になるようにしていただきたいです。
ややこしいことを質問していますが、よろしくお願いします。

No.1 ベストアンサー 回答者： MarcoRossiItaly 回答日時： 2015/05/16 13:02

●「勤務時間が8時間で」を、「昼休憩を除く勤務時間が 8 時間に達した時点で」という

　意味に捉えました。
●「総稼動時間」にも休憩は含まないと解釈しました。
●「総稼動時間」のうち 8 時間を超えた部分を「時間外」とするものと解釈しました。
●「夜間作業」も「総稼動時間」の内数（うちすう）であると解釈しました。

添付図に記入している数式は、下のとおりです。

なお ROUND 関数を適用しているのは、「オートフィルや数式で作製された時刻データを扱う場合などに小数点第 14 位、15 位あたりに発生するケースがある計算誤差」を補正するためです。小数点以下に 7 桁を残せば、0:01 単位で時刻を記録して日給 100,000 円であっても、誤差 1 円未満になると考えられます。

C3　=(b3-a3)/"1:00"
D3　12:00　など
E3　=min($B3,d3+"1:00")
F3　=($C3>1+8)*($A3+"1:00"+"8:00")
G3　=min($B3,($C3>1+8)*($A3+"1:00"+"8:15"))
H3　=($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:00")
I3　=min($B3,($C3>1+0.25+10)*($A3+"1:00"+"0:15"+"10:15"))
J3　=round(c3-sum(e3-d3,g3-f3,i3-h3)/"1:00",7)
K3　=max(0,j3-8)
L3　=round(max(0,b3-"22:00")/"1:00",7)

●「24:00 以後の時刻を表示する可能性のある列」のセルの書式は、「h:mm」ではなく、
　「[h]:mm」を設定してください。
　「セルの書式設定 > 表示形式タブ > ユーザー定義 > 種類ボックス」に書式記号を
　入力します。
●退社が翌日になったときは、午前 1 時であれば 25:00 というふうに記入してください。
●ゼロであるセル（「0.00」、「0:00」）を空白（のような見かけ）にするには、
　「ゼロ以下の値のみを非表示にするセルの書式」として、「0.00;;」、「h:mm;;」、
　などをセル範囲に設定しておきます。
　操作の方法は、「退社時間」の書式と同じです。
● J 列などに数式を記入した瞬間に、そのセルの書式が勝手に時刻になってしまう
　ことがありますが、気にせず「0.00;;」などを設定すれば、正常な値が表示されます。

この回答へのお礼

こんなややこしい質問に答えていただいてありがとうございます。

お礼日時：2015/05/20 11:06

No.3 回答者： MarcoRossiItaly 回答日時： 2015/05/18 02:58

No.1・2 です。

他にもミスを犯していました。よく確認せずに回答してしまって、まことに申し訳ありません。

No.1 の添付図を見ると、D4・E4 セルの大小関係がおかしくなっていますよね。正しくは、ちょっと難しいのですが、次式でした。

E3　=d3+max(0,min("1:00",b3-d3))

※昼休憩より前に出勤することを前提としています。
　それより遅くからの出勤もあり得る場合は、他のセルも含め、更に数式の修正が必要です。

No.2 回答者： MarcoRossiItaly 回答日時： 2015/05/16 14:06

No.1 です。

ちょっと手が滑ってミスをしたので、訂正させていただきます。

お金の計算をする場合、最終的に小数点以下を ROUND あるいは ROUNDUP 関数で丸めると思いますので、丸めるのはそのときの 1 回だけにしたほうがベターですね。何度も行うと、丸め誤差が計算結果に入ってしまう余地がありそうですので。したがって No.1 の数式から ROUND を次のとおり削除してください。

J3　=c3-sum(e3-d3,g3-f3,i3-h3)/"1:00"
L3　=max(0,b3-"22:00")/"1:00"

ただし、J・L 列と他のセルの値とを大小比較した結果を条件として、何かの処理を行う…といった場合などは、ROUND などで丸めておかないと、誤差のせいで失敗することもあります。必要が生じたときに別の列を使うなどして、適当な桁数に ROUND してください。