残業計算（エクセル）

質問

残業の計算を関数を使って計算したいのですがうまくいきません。
残業開始は17:20～と決まっており、2時間毎に15分の休憩が必要で
時間単位での管理で最小は0.25時間となり、満たない場合は切り上げとなります。
また、22:00以降は深夜勤務となります。
例えば17:20～23:00の場合は、残業5.25時間、深夜勤務1時間となります。
（列としては、開始時間17:20、終了時間、残業、深夜勤務の4列です）
また、有給休暇とかも記載するのでIF式は極力少なく（5個位が限度）したい
です。どなたかすっきりした形のものを提案してくださるとありがたいです。

回答No.8

omusupa

遅くなりました。やっぱりダメだったんですね。今度はこれで試してみてください。そして、また、ちゃんと出なかったところを教えてください。
=(CEILING(B2-A2,"0:15")-IF(CEILING(B2-A2,"0:15")/"0:15"<=8,0,IF(CEILING(B2-A2,"0:15")/"0:15"<=17,"0:15","0:30")))*24

回答No.7

omusupa

困りましたね・・・
私がやるとちゃんと答えは出るのですが・・
17:20 18:50 1.5
17:20 19:05 1.75
になります。

A2に　17:20　　B2に　18:50　と入力し　下の式を範囲指定してください。

=CEILING(B2-A2-INT((B2-A2)/"2:00")*"0:15","0:15")*24

そして、範囲指定をしている状態で、Ctrl+ｃ　を押してください。（コピーです）
そして、EXCELに戻って、　C2　をクリックし、
「編集」→「形式を選択して貼り付け」→テキスト（一覧からテキストを選択してください）→ＯＫ　をクリック。
これで試してください。

補足

やっぱりできないです。
やはりエクセルが壊れているのかな？会社にある色々なパソコンで試したけど
ダメでした。（式としては起こりえないと思うんですけど）
それと、もうひとつ気づいたんですが、この式だと
17:20～21:35の勤務をした場合4時間を超えているので
30分差し引かれることになりませんか？
17:20～19:20
19:35～21:35の4時間残業にならなくては・・・

回答No.6

omusupa

補足見ました。
>実際は15分の休憩を必ず差っぴかなくてはいけないので2.50時間のはずなんですが
の件ですが、確認しましたがきちんと2.5と言う結果が出てきます。
もう一度、括弧の数とか確認していただけませんか？
>また、深夜のほうは空白になるだけで計算してくれません。
の件ですが、これは、私の解答が間違っていました。訂正してください。
=IF(B2>=TIME(22,0,0),(B2-TIME(22,0,0))*24,"")
これも入力されるときに、括弧の数を気をつけて入力してください。
またダメでしたら、補足してくださいね。

補足

おかしな結果だけ抜き出すと
17:20 18:50 1.75
17:20 19:05 2.00
17:20 19:35 2.25
17:20 19:50 2.50
17:20 20:20 3.00
17:20 20:35 3.25
17:20 21:25 3.75
17:20 21:30 3.75
17:20 21:35 3.75
です。深夜のほうは計算してくれてます。
計算式の括弧の数は確認しました。何ででしょう？

回答No.5

imogasi

直ぐ思いつく案の、ＩＦ，ＩＮＴ，ＭＯＤなどの関数をネストする方法は式が複雑になったり、余分な列を使います。そこで提案します。
（１）表検索関数ＶＬＯＯＫＵＰを使うように考えられてはいかがでしょうか。
（２）そのためには、その使う表はすべてのケースに付いて、正しい結果を出さないと行けないですが。
（３）提案として表を２段構えにしては。
　　Ａ．「時間単位での管理で最小は0.25時間となり、満たない場合は切り上げとなります」を反映した、端数の切り上げを吸収する表。修正後時刻が出る。
　　Ｂ．その修正後時刻から正しい時間外時間数を算出す　　　る表。休憩時間も吸収・反映します。
１７：２０から２４：００まで分単位で４００エントリ、
５分単位で８０エントリ、１５分で２７エントリですから
１０分か１５分単位に出きれば、表も小さくなり、判りやすいでしょう。
具体的には、２４：００時までの（例えば）５分刻みの表を作り、その右に時間外、深夜勤務時間数を書き込んで見る。２－３体系あるのなら、Ｖｌｏｏｋｕｐの参照表範囲を変えることにより対応可能でしょう。
会社・事業所の時間外規定によって、ＡとＢの表は必ず出きるとは限らないが、ぜひ一度検討してください。
（５）ＶＬＯＯＫＵＰの「検索の型」はＴＲＵＥを使い、
値の範囲に落ちるものを、１つの値に決める働きがある
が、これを活用するもの。

回答No.4

moon00

すいません、「満たない場合に切り上げ」というのを見落としてましたね。
C2を=ROUNDUP(((B2-A2-INT((B2-A2)/"2:00")*"00:15")/"00:15"),0)*"00:15"
に訂正します。
0.25時間＝15分なので、さっき出した値を15分で割り、切り上げ処理して
再度15分を掛けています。
式はかなり長くなってしまいましたが。
これを5.25時間と表示したい場合は、最後に「*24」を加えて
表示形式を小数点以下２桁まで表示できるようにしてください。
（日付形式ではなくて）

回答No.3

omusupa

こんな感じでどうですか？
残業時間のところは
=CEILING(B2-A2-INT((B2-A2)/"2:00")*"0:15","0:15")*24
深夜
=IF(B2>=TIME(22,0,0),B2-TIME(22,0,0),"")*24
（B2には終了時間、A2には開始時間）

お礼

早速考えていただきありがとうございます。
今、試しているんですけれど17:20～20:05の場合2.75時間になってしまいます。
実際は15分の休憩を必ず差っぴかなくてはいけないので2.50時間のはずなんですが・・・また、深夜のほうは空白になるだけで計算してくれません。
エクセル壊れたのかな？

回答No.2

moon00

17:20～23:00の場合は、残業5.25時間、深夜勤務1時間ということですが、
一応例を作ってみました。
　　　Ａ　　　　Ｂ　　　Ｃ　　　Ｄ
1 　開始時間　終了時間　残業　深夜勤務
2　

A2に開始時間、B2に終了時間を入れるとして
C2には「=B3-A3-INT((B3-A3)/"2:00")*"00:15"」
D2には「=IF(B2>"22:00","",B2-"22:00")」
でどうでしょう？
C2では実働時間から実働時間を２で割った整数に15分を掛けたものを引いています。
D2では、終了時間が22:00を越えているかをIFで判定しています。
外してたらすいません。

回答No.1

nabi-hayato

具体的に、どの様なワークシートで、行列構成されているのか、どの様な計算を必要とされているのか、ちょっと判らない部分もあるのですが、Excel には、時間／日付関数が有り、色々と使えるのではと思います。又、切上げ・切捨て・四捨五入等には、Roundup／Rounddown／Round　という関数も有りますので、この辺をお使いになれば如何でしょうか？
又、開いているセルやワークシートなどを使って、補助計算をする事もできると思います。それらを他の人が、誤ってその補助計算部分を消したり、触らないように、非表示にする事もできますので、その様な機能も使われると、良い物が出来ると思います。
ご参考頂くには、ほど遠い回答かもしれませんが、一度、Excelの関数に関するHELPをご参照してみては如何でしょうか。