みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

Excel2007での月の給料計算式について

Sheet1に、約30人分の月の勤務表があります。Sheet1の勤務表は、縦列には氏名、横軸には、日にちが並び、以下のようになっております。

2012年8月
No  氏名  種別  1日  2日  3日  4日・・・31日
              水   木  金   土     
1   A君   出勤  9:00  9:00 21:00
         退社  17:00 19:00 27:00
         休憩  1.0  1.0  0.5
         実働  7.0  9.0  5.5

このSheet1の勤務表をもとに、以下の給与単価で、給料を計算する関数を教えてください。
 基本給  836円
 手当    実働が8Hを超えた分は,時給は1,000円(但し8Hが超えた時間が
        深夜増し時間帯や早朝増し時間帯がある場合は,そこの時給単価を
        適用する
 深夜増  22:00-25:00までの勤務は,時給は1,200円
 早朝増  5:00-9:00まで時給が950円

このような複雑な計算式を教えてください

投稿日時 - 2012-08-12 14:45:51

QNo.7640052

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

回答No2,3です。
6行目の休憩時間および実働時間が十進法で表示されるようになっているのでしたら、D8セルには次の式を入力して右横方向にドラッグコピーします。なお、給料計算に当たって時間は15分単位で処理できるように式を変えています。

=IF(COUNT(D4:D5)<>2,"",IF(AND(D4>="5:00"*1,D4<="9:00"*1),FLOOR((MIN(D5,"9:00")-MAX(D4,"5:00")),"0:15")*24*950,0)+IF(D5>="22:00"*1,FLOOR((MIN(D5,"25:00")-MAX(D4,"22:00")),"0:15")*24*1200,0)+IF(D7/24>"8:00"*1,FLOOR((MIN(D5,"22:00")-MAX(D4,"9:00")-D6/24),"0:15")*24*1000,0)+IF(D7/24<="8:00"*1,FLOOR((MIN(D5,"22:00")-MAX(D4,"9:00")-D6/24),"0:15")*24*836,0)+IF(D5>"25:00"*1,FLOOR((D5-"25:00"),"0:15")*24*836,0))

投稿日時 - 2012-08-13 11:55:06

!!!!
「休憩」の時間帯が不明ですので、とりあえず無視し、休憩も実働に含めた計算式としました。そのため、実際より大きな額を算出すると思います。補足してください。


質問者さんは大丈夫そうですが、勤務時間中に日付が変わる場合は、深夜0時は24時、1時は25時というふうに入力してください。そのほうが数式がシンプルになるので。セルの書式設定はユーザー定義の「[h]:mm」ですね。(「h:mm」ではなく)

「時間帯どうしの重なりの長さ」を求める式は、max(,min-max)です。ある時給が適用される時間帯の区分と、勤務時間との重なりの長さを求めます。「0-1時、1-5時、5-9時、9-22時、22-25時、25-29時、29-33時、33-46時」の、8区分に分けて勤務時間との重なり・給料を計算し、合計します。

=24*sum(
1200*(max(,min(d5,"1:00")-max(d4,"0:00"))),
836*(max(,min(d5,"5:00",d4+"8:00")-max(d4,"1:00"))),
1000*(max(,min(d5,"5:00")-max(d4+"8:00","1:00"))),
950*(max(,min(d5,"9:00")-max(d4,"5:00"))),
836*(max(,min(d5,"22:00",d4+"8:00")-max(d4,"9:00"))),
1000*(max(,min(d5,"22:00")-max(d4+"8:00","9:00"))),
1200*(max(,min(d5,"25:00")-max(d4,"22:00"))),
836*(max(,min(d5,"29:00",d4+"8:00")-max(d4,"25:00"))),
1000*(max(,min(d5,"29:00")-max(d4+"8:00","25:00"))),
950*(max(,min(d5,"33:00")-max(d4,"29:00"))),
836*(max(,min(d5,"46:00",d4+"8:00")-max(d4,"33:00"))),
1000*(max(,min(d5,"46:00")-max(d4+"8:00","33:00")))
)

※「実働8時間以下であっても「深夜増」や「早朝増」が適用される」と仮定した式になっています。そうでない場合は、実態に合わせて適当に修正してください。
※見やすさのために式中に改行を入れてありますが、気にせずそのまま、14行を1セルの中にコピペしてください。気に食わない場合は、改行を削除しても差し支えありません。
※上の式を入力したセルの書式を、入力後に「標準」などに設定するのをお忘れなく。

投稿日時 - 2012-08-13 02:19:51

ANo.3

回答No2です。D8セルの表示形式は時刻ではなく標準にしてください。

投稿日時 - 2012-08-12 19:24:19

補足

やはり、D8セルの給与表示は-8600となってしまいます。
例えば、
D4セルには出勤時間9:00
D5セルには退社時間24:00
D6セルには休憩時間0.5(十進法表示)
D7セルには実働時間14.0(十進法表示、計算式は=FLOOR((D5-D4)*24-D6,1/4))
とした場合、
D8セルを書式を標準にしても、-8600となってしまいます
この-8600ではなく、8600と表示されれば、いいのですが、何がいけないのかを
お教えいただけないでしょうか?

投稿日時 - 2012-08-12 20:07:54

ANo.2

例えばD4セルに出勤時間、D5セルに退社時間、D6セルに休憩時間、D7セルには実働時間が入力されているとします。
お示しのデータでは休憩時間が1.0のようになっていますが1:00と、また実働時間は=D5-D4-D6として8:00のように表示されているとします。
そこで賃金の計算ですが25:00以降では基本給となってしまうのでしょうか?ここでは基本給であるとして式を立てています。
例えばD8セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COUNT(D4:D5)<>2,"",IF(AND(D4>="5:00"*1,D4<="9:00"*1),(MIN(D5,"9:00")-MAX(D4,"5:00"))*24*950,0)+IF(D5>="22:00"*1,(MIN(D5,"25:00")-MAX(D4,"22:00"))*24*1200,0)+IF(D7>"8:00"*1,(MIN(D5,"22:00")-MAX(D4,"9:00")-D6)*24*1000,0)+IF(D7<="8:00"*1,(MIN(D5,"22:00")-MAX(D4,"9:00")-D6)*24*836,0)+IF(D5>"25:00"*1,(D5-"25:00")*24*836,0))

投稿日時 - 2012-08-12 17:49:53

補足

早速のご回答ありがとうございます。ご質問の25:00以降ですが、25:00~朝5:00までは基本給となります。また休憩時間表示ですが、実働時間は十進法での表記となる関係上、15分休憩は0.25、30分休憩は0.5、45分休憩は0.75、1時間休憩は1.0で表記をするため、実働時間を算出するセルには=FLOOR((D5-D4)*24-D6,1/4)という計算式を入れております。
そのため、私のご説明不足で申し訳なかったのですが、お教えいただきました計算式をセルに入れますと、時間給がマイナス表記となってしまいますため、再度
お教えいただけないでしょうか?

投稿日時 - 2012-08-12 19:55:17

ANo.1

一つのセルでいろんな条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。

まずは、通常勤務時間、通常残業時間、深夜残業時間、合計勤務時間を出してから、給与単価で給料を計算してはいかがでしょう。

投稿日時 - 2012-08-12 15:34:25

あなたにオススメの質問