Excelで24時間対応の給与計算方法を教えてください

質問

Excelを使って給与計算をしたいと思っているのですが、うまくいきません。
人数も多く勤務パターンも多いので計算式を作れないでいます。
日付をまたぐ勤務パターンもあるので
24時間で対応できる計算式ができればありがたいです。
ぜひ皆さんのお力をお貸し頂きたくお願いいたします。

セルには
A：日付
B：曜日
C：出勤時間（夜勤例18.0で表示）
D：退勤時間（夜勤例30.5で表示）
E：実働時間（D-Cで求めてます）
F：所定労働時間（22時～5時以外で9時間までの実働時間）
G：時間外労働時間（22時～5時以外で実働9時間を超えた時間）
H：深夜労働時間（22時～5時の実働時間）
I：深夜残業時間（22時～5時で実働9時間を超えた時間）
※出勤時間・退勤時間は数値に変換し、退勤時間で24時を越えた時間に関しては＋24して基本の勤怠データを作っています。

関数計算式を利用して算出したいのは
F：所定労働時間
G：時間外労働時間
H：深夜労働時間
I：深夜時間外労働時間
です。

例えば
日勤：出勤9.0　退勤24.0　実働15時間の場合は
F:所定：9h
G:残業：4h
H:深夜：0h
I:深残：2h
夜勤：出勤18.0　退勤31.5（朝の7時半）実働13.5時間の場合は
F:所定：4h
G:残業：2.5h
H:深夜：5h
I:深残：2h
と計算結果を出したいのです。

給与の支払いに直接関わるのでとても困っています。
よろしくお願いいたします。

回答No.4

ayaka-papa

時間計算の為に列の追加が許されるのであれば、使ってみて下さい。

C1:出勤時間
D1:退勤時間
E1:実働時間
F1:所定全労働時間
G1:深夜全労働時間
H1:所定労働時間
I1:時間外労働時間
J1:深夜労働時間
K1:深夜残業時間
とします。

所定全労働時間とは、22時～5時以外の全部の実働時間(所定全労働時間=所定労働時間+時間外労働時間)
深夜全労働時間とは22時～5時の全部の実働時間(深夜全労働時間=深夜労働時間+深夜残業時間)
この2列は「表示しない(H)」で見えなくしても良いです。

E2のセルに=D2-C2

F2のセルに=IF(AND(C2<5,D2<=5),0,IF(AND(C2<5,D2>22),22-5,IF(AND(C2<5,D2>5),D2-5,IF(AND(D2<22,C2>5),D2-C2,IF(AND(C2<=22,D2<30),22-C2,IF(C2<22,22-C2+D2-29,D2-29))))))

G2のセルに=E2-F2

H2のセルに=IF(F2=0,0,IF(AND(C2>=21,G2>0,F2>9-G6),9-G2,IF(E2<9,F2,IF(OR(C2<5,C2>21.5),9-(5-C2),IF(C2<13,IF(F2>9,9,F2),22-C2)))))

I2のセルに=F2-H2

J2のセルに=IF(OR(C2<5,C2>22),IF(D2=5,5-C2,IF(D2<5,D2-C2,IF(OR(E2<9,H2<2),G2,9-H2))),IF(H2<9,IF(9-H2>G2,G2,9-H2),0))

K2のセルに=G2-J2
と各セルに入力して下さい。これで計算出来ると思います。
実働時間が24時間を超えると間違った時間が表示される時間帯が有ります。
但し、間違いが有ってはいけませんので2～3ヶ月は以前の計算方法と併用して下さい。

回答No.3

noname#95859

少し、考えてみました。

タイムゾーン－－
所定：朝5:00-22:00
深夜：22:00-朝5:00
連続して9時間以上働いた場合は、残業として、カウント
それぞれのタイムゾーンにて、所定残、深夜残

考え方：
for　nextを使って、(出勤+0.5)から退勤時間までを 0.5stepでまわします。
(出勤+0.5)とするのは、例：（出勤　10:00　－－－10:30になったら、0.5時間働いた）
for nextでまわす時に、タイムゾーンで、足しこむメモリを選択する。
メモリ配列は４つ用意する。
' memo(1) -------H:深夜
' memo(2) -------F:所定
' memo(3) -------I:深残
' memo(4) -------G:残業

memo(1)+memo(2)---(H:深夜+F:所定)を毎回監視していて、9時間に達したら
オフセット２を与えて、それ以降は、memo(3)memo(4)が選択されるようにする。
---------------------------------------------------------------------
残業の計算は以上でよいのですが、これは、ワークシート関数だけでは不可能です。
そこで、イベントプロシージャを使います。
出勤、退勤時間を入れたら、E：実働時間（D-C）の該当セルをダブルクリックします。
これをトリガとして、当該セルの左に記入されている、出勤、退勤時間を上記の考えで
計算し、当該セル、および、右方に計算結果を代入します。
---------------------------------------------------------------------
A：日付
B：曜日
C：出勤時間（夜勤例18.0で表示）
D：退勤時間（夜勤例30.5で表示）
E：実働時間（D-Cで求めてます）
F：所定労働時間（22時～5時以外で9時間までの実働時間）
G：時間外労働時間（22時～5時以外で実働9時間を超えた時間）
H：深夜労働時間（22時～5時の実働時間）
I：深夜残業時間（22時～5時で実働9時間を超えた時間）
-------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim StartTime As Double
Dim EndTime As Double
Dim myOffset As Double, i As Double, a As Double
Dim rowpos As Integer, Colpos As Integer
Dim memo(4) As Double

' memo(1) -------H:深夜
' memo(2) -------F:所定
' memo(3) -------I:深残
' memo(4) -------G:残業

rowpos = Target.Row
Colpos = Target.Column
If Colpos <> 5 Or rowpos = 1 Then Exit Sub
StartTime = Target.Offset(0, -2).Value
EndTime = Target.Offset(0, -1).Value
If EndTime - StartTime = 0 Then Exit Sub
myOffset = 0
For i = StartTime + 0.5 To EndTime Step 0.5
If memo(1) + memo(2) >= 9 Then myOffset = 2
If i > 0 And i <= 5 Then memo(myOffset + 1) = memo(myOffset + 1) + 0.5
If i > 5 And i <= 22 Then memo(myOffset + 2) = memo(myOffset + 2) + 0.5
If i > 22 And i <= 24 Then memo(myOffset + 1) = memo(myOffset + 1) + 0.5
If i > 24 And i <= 29 Then memo(myOffset + 1) = memo(myOffset + 1) + 0.5
If i > 29 And i <= 46 Then memo(myOffset + 2) = memo(myOffset + 2) + 0.5
If i > 46 And i <= 48 Then memo(myOffset + 1) = memo(myOffset + 1) + 0.5
Next
Target.Offset(0, 0).Value = EndTime - StartTime
Target.Offset(0, 1).Value = memo(2)
Target.Offset(0, 2).Value = memo(4)
Target.Offset(0, 3).Value = memo(1)
Target.Offset(0, 4).Value = memo(3)
End Sub
計算結果
----------------------------------------------------
C：出勤 D：退勤 E：実働 F：所定労働 G：時間外労 H：深夜労働 I：深夜残業
9 24 15 9 4 0 2
18 31.5 13.5 4 2.5 5 2
2 4 2 0 0 2 0
2 12 10 6 1 3 0
9 22 13 9 4 0 0
10 35 25 9 9 0 7
14 33 19 8 4 1 6
23 46 23 3 14 6 0

----------------------------------------------------
これで、すべてのケースで、正しく計算されているか、大丈夫だとは思いますが、
異常なケースがあれば、お知らせください。また、考えます。

回答No.2

Cupper

すみません。
他の人に作成を依頼しているように感じるんです。
不明な点が全く書かれておらず、仕様を並べているに過ぎない質問に答えるべきか悩んでしまいます。

並べた仕様のなかで何が不明なのかを明らかにしてください。

■とりあえず回答
まずは考え方を整理します。
紙の上で日勤/夜勤において全ての出勤ケースにおける「計算過程」を一つずつ書き取ってください。
　※これが計算式の基本になります。
次に条件で計算が変わる場合の条件を書いてください。

かなり面倒だとは思いますが、これができていないと機械で計算する事はできません。
また、思うような結果が得られない場合の修正もできません。

このように作成していく過程で具体的な疑問が出てくると思います。
そういった疑問であれば、ここで質問することで回答を得ることができます。

使えそうな関数
　IF　（云わずと知れた分岐条件関数）
　MAX（ =MAX(5,9) で大きい方が選択される）
　MIN（ =MIN(22,24) で、小さい方が選択される）
など

回答No.1

noname#192382

説明と後の計算例とがくいちがっいます。
説明のとおり計算すると初めの例ではＨが2後の例ではＨは7になるようですが。

お礼

回答ありがとうございます。
説明不足ですみません。
一日の労働（所定+深夜）が9時間を超えると時間外労働（残業）になるのです。
深夜時間帯（22時～5時）の労働時間だけで見るとご指摘のとおり
日勤の例ではH：2h、夜勤の例ではH:7hとなるのですが
日勤の例では所定ですでに9時間を超えているので、
深夜時間帯の2hは深夜残業になり
夜勤の例では所定の4時間と深夜時間帯7時間のうち5時間で
9時間を超えるので、深夜の残りの2hが深夜残業となるのです。