ベストアンサー

エクセルでの深夜残業手当の計算方法

2017/07/25 10:58

お知恵をお貸しください。現在、A列=出社時間、B列=退社時間、Ｃ列=労働時間（A-B)、という表があります。これから、以下の４つの答えを取り出したいです。 D列＝深夜残業：労働時間が８時間以上　かつ　２２時以降の労働時間 E列＝深夜：２２時以降の労働時間　から　Ｄ列　をマイナス F列＝通常残業：労働時間が８時間以上　かつ　２２時未満の労働時間 G列＝通常：労働時間から上記D,E,Fを除いたもの時間はすべて　13:00:00 の形でデータを保有しています。どのように関数を書けばよいでしょうか。教えてください。

excel_benkyo
お礼率100% (4/4)

Excel（エクセル）
回答数4
ありがとう数4

みんなの回答 （4）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

bunjii
ベストアンサー率43% (3589/8249)

2017/07/25 13:36 回答No.3

用語の誤りを訂正させていただきます。「出社時間」→「出社時刻」「退社時間」→「退社時刻」「22時未満」→「22時以前」（22時は時刻なので時間の計算には以前と以降に同じ時刻を用いても整合性が損なわれません） >Ｃ列=労働時間（A-B)、という表があります。休憩時間は考慮しないのでしょうか？ >D列＝深夜残業：労働時間が８時間以上　かつ　２２時以降の労働時間 24時（翌日の0時）を超えて勤務する場合の退社時刻はどのような値になりますか？ >E列＝深夜：２２時以降の労働時間　から　Ｄ列　をマイナス考え方の問題ですが残業と深夜勤務を別に計算された方が良いと思います。例えば、15:00:00から24:00:00まで勤務したとき、労働時間が9時間となり、通常勤務を8時間とすれば残業は1時間であり、深夜勤務は2時間になります。手当の計算では通常の残業手当が時給の125%で深夜勤務が時給の25%として上記の勤務では時給を1000円としたとき次のようになります。通常勤務=8時間*1000円/時間=8000円残業手当=1時間*1000円/時間*1.25=1250円深夜手当=2時間*1000円/時間*0.25=500円あなたの論理では次のようになるのでしょうか？通常勤務=8時間*1000円/時間=8000円深夜残業=1時間*1000円/時間*(1.25+0.25)=1500円深夜手当=1時間*1000円/時間*0.25=250円同じ結果を得るためにあなたの論理の方が時間の計算が複雑になります。 >どのように関数を書けばよいでしょうか。フレックスタイムを利用している場合の勤務条件を整理して条件をもう少し詳しく説明する必要があるようです。深夜勤務は22時から翌日の5時までと記憶しています。日付が変わる24:00:00以降に退社する場合の退社時刻が1:30:00のようになるのか、25:30:00となるかの条件が必要になります。また、24時間以上の勤務が存在するか否かも重要になります。数式の組み立て方には幾つかの方法があります。 IF関数で条件分岐する方法 MAX関数やMIN関数を駆使する方法代表的な手法はこの2つと考えられます。実際の数式は考えられる出退勤の時刻を10組程度提示して頂ければ希望に沿うものを提示できると思います。

質問者

お礼 2017/07/25 17:33

読んでいるうちに頭が整理でき、他の方がくれたシート内の関数と、MAXとmin関数の組み合わせで完成できました。ありがとうございます。

その他の回答 (3)

asciiz
ベストアンサー率70% (6821/9700)

2017/07/25 13:40 回答No.4

＞現在、A列=出社時間、B列=退社時間、Ｃ列=労働時間（A-B)、という表があります。＞これから、以下の４つの答えを取り出したいです。その3つの数字をただこねくり回しても、答えは出ません。まず、きっちり計算ルールを確定させるところから始める必要があります。簡単に「深夜」と言いますが、0:00を超えた勤務はどうするのですか? もし、日付できっちり区切るなら、0:00以降の勤務は「翌日の早朝勤務」で計算することとなり、ご質問にある「深夜残業」には含まれないこととなります。でもその「早朝勤務」は、「深夜残業」のように割増し計算しますか、しませんか? 区切りを0:00としないなら、何時までの勤務を「前日分」としますか? (例えば05:00) 出社・退社の半端な分数はどのようにしますか? 1分単位まで計算しますか? 10分とか15分単位で切り捨てますか? 1時間未満の端数はすべて切り捨てますか? そのようなことを決めたうえで、まずは出社時刻・退社時刻より、24時間を「早朝勤務時間」「昼間勤務時間」「通常残業時間」「深夜残業時間」などと区切って、それぞれの部分で、何時間何分勤務したのかを、出します。さらにご質問にあるルールでは、「一日の勤務時間が8時間を超えるときに、深夜残業の部分を『早朝勤務』と『通常残業』と『深夜残業』に分ける」と言う計算式が必要になります。またそこで、「早朝勤務」には残業と同等の割り増しをするのか、昼間勤務と同じなのか。そのぐらいの計算列を作って、ようやく最終的な「早朝勤務時間」「昼間勤務時間」「通常残業時間」「深夜残業時間」の列が出来て、そこに時給と深夜割増等の掛け算ができるようになり、給与計算ができます。このルールは会社ごとに違うので、ルールに合わせた計算式を設定し、ミスの無いようにコピーしなければなりません。ルールが変われば、整合性が崩れないように慎重に、式を変更しなければなりません。はっきり言って、Excelで給与計算をするのは、作るのが大変で、メンテも大変で、どこか間違っていたらすべて自分の責任で、給与の過不足で訴えられる危険性まであるというとてもリスクの高いものになります。それだから、勤務・給与のパッケージソフトと言うものがあるわけです。勤務ルールに従った設定を最初に入れ込めば、あとは出社・退社時刻の記録だけで、給与計算ができます。まあ、Excelの練習に作ってみたいというならそれは勉強ですから構いませんが。実務用にこれから作ろうとしているんだったら、自分で作るよりもパッケージソフトの導入をお勧めします。まあさすがに、給与配るのが1人とか2人とかですと、給与ソフト買うのも考えてしまいますが…。 (給与ソフトも買い切りではなく、毎年保守費用がかかるものが多いです。その代わり、保守費用を取るものはインストールや給与ルールの設定方法のサポートを受けることができて、毎年ある税制改正に対するアップデートをしていってくれるので、使う側は勤務時間だけ入れれば正確な給与計算をしてくれます。)

質問者