エクセル2010 深夜時間外・普通時間外の関数設定

従業員の勤務表を管理することになりました。
関数があまり得意ではなくどなたか教えていただける方がいたら
大変助かります。

時間が不規則な従業員が１人だけいます。（表の１番下緑の行のＪ）
この日は16時～翌朝６時（30時）まで勤務です。

①２２時までは通常の時給
②２２時～翌朝５時まで深夜時間外料金
③翌朝5時～普通時間外料金

この従業員の合計料金はどのような関数を入れたらよいでしょうか？
２行または３行に分けないといけないでしょうか？
宜しくお願い致します。

質問者からの補足コメント

• 

  こういうところで質問をするのは初めてですが、こんなに早くにお返事をいただけて
  とても嬉しいのとビックリしています。本当に皆様ありがとうございます！感謝しております。
  
  この表は前任者が作成したのですがよく分からないことが多くて苦労しています・・
  こういう時間が不規則な人も今までいなかったようで。
  今、一番下の緑のＪさんに入っている数値です。

  
    補足日時：2016/04/10 19:27

• 

  今緑の行に入っている数値です。
  
  B:従業員指名　　　　　　Ｊさん
  C:基本料金（１日）　　￥15,500
  D:基本料金（１時間）　￥1,938
  E:通常残業料金　　　　￥2,423
  F:深夜作業料金　　　　￥2,907
  G:出勤時間　　　　　　16.00
  H:退勤時間　　　　　　30.00
  I:休憩時間　　　　　　　1.00
  M:深夜休憩時間　　　　 0.00
  P:勤務時間　　　　　　 13.00
  R:残業時間　　　　　　　5.00
  T:通常残業料金　　　　￥12,115
  X:基本料金　　　　　　￥15,500
  AE:深夜勤務時間　　　 　7.00
  AG:深夜料金　　　　　 ￥20,349
  AH:金額（合計） 　　 ￥35,849

    補足日時：2016/04/10 19:29

No.6 回答者： tonton7373 回答日時： 2016/04/11 10:27

Wxcelは一過性の表計算に過ぎません

是非Accessをご使用下さいませ

Accessですと、共有化し入力、閲覧が可能に成ります

No.5 回答者： ママチャリ 回答日時： 2016/04/10 23:44

kao2016さんは、関数は得意でなくても、給与に関してはプロですよね。

勤務時間のうち、8時間までは通常勤務。それを超えた部分が時間外で基本料金（１時間）の25％増しです。
また、深夜時間帯（22：00から翌5：00まで）の勤務については、基本料金（１時間）の25％分が加算されます。
よって、JさんのAH(金額合計）は下記①~③の合計とするのが一般的です。
①通常勤務8時間に対する基本料金（１日）で、￥15,500
②通常残業料金￥2,423×時間外5時間で、￥12,115
③深夜料金（1時間分の基本料金￥1,938×0.25)×7時間で、\3,392･･･端数処理は御社のルールで！！

上記①②は、正しく求められているようですが、③の考え方がおかしいです。
深夜料金を基本料金（１時間）￥1,938の150％で求めているようですが、これは時間外（25％増し）かつ深夜勤務（25％増し）の場合です。通常時間の深夜勤務は、深夜勤務分の25％増しだけです。

No.4 回答者： mike_g 回答日時： 2016/04/10 22:05

私が判るところだけネ。

T12: =E12*R12/"1:0"
AE12: =MIN("29:0",H12)-MAX(G12,"22:0")
AG12: =F12*AE12/"1:0"

R12 は "22:00"-G12="6:00" ではないかと思うのだけど、なぜ "5;00" になっているのか理解できません。ひょっとして I12 の "1:00" を差し引いたのかな？
そうなると、列Ｉ の休憩時間を与える時間帯条件はどうなっているのかしら？
AH12 は X12(基本料金)+AG2(信哉料金)になっているようだけど、なぜ T12(通常残業料金)が参入されていないのか？とか、「通常の時給」と「普通時間外料金」の定義もサッパリポンです。

》 前任者が作成したのですがよく分からない
そんなことを仰るなら、社外の私は尚更のことでござりまするゥ～

No.3 回答者： mike_g 回答日時： 2016/04/10 16:25

添付図のセル B3～AH3 の各セルに入力されている文字列を全て書き出してください。

この回答へのお礼

お返事してくださってありがとうございます<(_ _)>

B:従業員指名
C:基本料金
D:基本料金（１時間）
E:通常残業料金
F:深夜作業料金
G:出勤時間
H:退勤時間
I:休憩時間
M:深夜休憩時間
P:勤務時間
R:残業時間
T:通常残業料金
X:基本料金
AE:深夜勤務時間
AG:深夜料金
AH:金額（合計）

です。　この表は前任者が作ったものですが、Ｘ（基本料金）は同じものがあるので不要っぽいですが・・
すみません、こういうところで質問をするのが初めてで、図はクリックすれば大きく見られるものと思っていました・・

お礼日時：2016/04/10 18:57

No.2 回答者： zircon3 回答日時： 2016/04/10 16:12

関数と言うか。

。。

　添付されている図面は小さすぎてどんな項目があるのかわかりませんが、各人の1ヶ月分の勤務表の1日の就業情報には「出勤時間」、「退勤時間」、「在場時間」（退勤時間ー出勤時間）、「就業時間」（休憩時間を除いた時間）、「残業時間」、「深夜労働時間」などがあるかと思います。
　ご質問にある(1)～(3)（質問は丸数字ですが）は「深夜労働」と「通常時間外」の時間をどう求めるかです。

　深夜時間は以下でしょう。

(1) 出勤時間が22時以前の場合
　1)退勤時間が翌朝5時（29時）を超えていたら7時間
　2)退勤時間が翌朝5時（29時）以前だったら「22時 ー 退勤時間」
(2) 出勤時間が22時を過ぎている場合
　1)退勤時間が翌朝5時（29時）を超えていたら「29時 ー 出勤時間」
　2)退勤時間が翌朝5時（29時）以前だったら「出勤時間 ー 退勤時間」

　実際には22時から翌朝5時までの間に勤務規則上休憩時間があると思うのでそれ入れた場合分けが必要です。

　で。通常時間外は「退勤時間 ー 出勤時間 ー 休憩時間」が8時間を超える場合でしょう。
　休息時間は出勤時間と退勤時間によりその間に何時間休憩時間があるかが就業規則で決まります。
　また、出勤時間は何時以降になることはありえないといった就業規則上の制約がある場合はそれを加味した条件分けを行えばよいと考えます。

　いずれにしろ1日の勤務についてあり得るパターンを考慮した条件分岐をまずは日本語で箇条書きに書き出すと仕様が明確になります。
　それをExcelの式に落とし込めばよいわけで。。。

参考まで。

この回答へのお礼

お返事してくださって、ありがとうございます<(_ _)>
添付図はクリックすれば大きく見られるものと思っていました・・ごめんなさい。。
ご丁寧に教えてくださいまして本当にありがとうございます！

お礼日時：2016/04/10 19:00

No.1 回答者： angkor_h 回答日時： 2016/04/10 16:00

先ずは、時間に対する給与や残業割り増し等のテーブルを作ります。

休憩時間は0円で入れればよいでしょう。

対象者の勤務時間を時間ごとに整理して、
その整理された時間を元に、先の時間給テーブルを参照すれば良いです。

時間給テーブルの参照は、後者の勤務時間をキーに、vlookup関数が使えるでしょう。

この回答へのお礼

お返事をしてくださってありがとうございます<(_ _)>
少し時間がかかりそうですが勉強しながらやってみます。
早くにお返事をくださいまして本当にありがとうございます！

お礼日時：2016/04/10 19:02