エクセルで勤務表（残業計算）を作りたいのですが・・・。

質問

下記のような勤務表を作りたいと思っています。
8時半から17時の勤務です。
昼休みは12時から13時。
残業を表示したいのです。
残業は17時15分から発生します。
17時45分まで仕事した時点で残業がつき、『0.25』と表示したいのです。
18時→『0.50』（17:59までは『0.25』です）
18時15分→『0.75』（18:14までは『0.50』です）
18時30分→『1.00』
18時45分→『1.25』・・・・

後、出来れば深夜勤務時間も表示したいと思っています。
深夜勤務は、22時～5時までです。
「○○時間勤務」
と言う様に、働いた時間をそのまま表示したいと思っています。

よろしくお願いします。

回答No.6

takuranke · ベストアンサー

＃１です、きついこと書いてしましたので、回答しようと思ったのですがNo.5の方にスマートなものを書かれてしまいましたので一寸遅かったかなという感じです。
補足します。
　　　Ａ　　　　・・・　　 Ｑ 　　 Ｒ
１　１７：４５　　　　２１：４５ 　２２：００ ・・・
２　 ０．２５　　　　　４．２５　　　４．５０　・・・
とします、
=IF(B6<$A$1,"",HLOOKUP(B6,$A$1:$Z$2,2,TRUE))の式の$Z$2を
$Q&2に変えます。これが(1)の式
深夜勤務時間をQ2のセルでも良いので残業時間と同じように作ります。
４　　　Ａ　　　　　Ｂ　　　　　　　Ｃ　　　　　Ｄ
５出勤時間　退社時間　　　　残業時間　　深夜時間
６８：００　１８：００　　　　　（１）の式　　（２)の式

そして=IF(B6<$A$1,"",HLOOKUP(B6,$A$1:$Z$2,2,TRUE))の式のB6<$A$1を$R$2として（この場合、深夜勤務の始まりの時間より小さい時間を表示させない）,"",を0にします$A$1:$Z$2の部分を深夜勤務時間のデータ範囲にします。=IF(B6<$R$2,0,HLOOKUP(B6,深夜時間のデータ範囲,2,TRUE))これが(2)の式

E６のセルに
=C6+D6&"時間勤務"とすれば表示されます。
一寸ずれてますがわかりますでしょうか。
頑張って作ってください。

お礼

有難うございました。
参考になりました。

回答No.8

mshr1962

#5,7のmshr1962です。すみません式が少し間違えてました
B6がブランクの場合の処置と1日の追加が24時間なのに1時間だけ追加です。
下記が修正したものです。
=MAX(INT(B6*24+AND(B6>0,B6*24<=5)*24-22),0)

回答No.7

mshr1962

#5のmshr1962です。
＞関数自体がそのまま表示されます。
上記が気になったので、セルの書式設定の表示形式が文字列になってませんか？
もし関数がそこだけなら「ツール」「オプション」「表示」タブの「数式」にチェックがあるとか？
文字列なら、G/標準か数値等に変えてください。その後にそのセルを選択してEnterです。
チェックがある場合は外してください。それで結果が表示されます。

＞後、出来れば深夜勤務時間も表示したいと思っています。
深夜時間帯のみの時間ですか？
=MAX(INT(B6*24+(B6*24<=5)-22),0)
でこのセルの表示形式をユーザー定義で　0"時間勤務";;;　としてください。

回答No.5

mshr1962

B6:退社時間,C6:残業時間として
補足の表を使うなら
=IF(B6<$A$1,"",HLOOKUP(B6,$A$1:$Z$2,2,TRUE))

回答No.4

hidechan2004

VLOOKUPかHLOOKUPを使えば良いと思いますよ。
空いた部分（別シートでも可）に残業時間と表示する時間（0.25の部分）の表を2列で作ります。

あとは、＝VLOOKUP（検索値,検索範囲,列数,検索の型）です。
検索の型は近似値検索にするので省略して結構です。
詳しくは参考URLをご覧ください。

参考URL：

http://www.excel.studio-kazu.jp/lib/e1tw/e1tw.html

お礼

ありがとうございます。
参考になりました。

回答No.3

edomin

「関数自体がそのまま表示されます。」
との事ですが、そのセルの表示形式は「文字列」になっていませんか？
「標準」に戻してみましょう。

回答No.2

suiko_wkk

いま組んでみたのですが・・・
17時15分から残業発生で
17時45分で0.25時間つくということですが、これは15分間の休憩時間があるということでしょうか？

一応それを抜いた状態での式ですが
　　　　A　　　　　　B
1　勤務終了時間　残業時間
2　　18：14　　　　0.75

このような状態で
A2は入力、B2は以下の式です。
=INT(((HOUR(A2)+MINUTE(A2)/60)-17.25)/0.25)*0.25

もし、15分の休憩があるのであれば
=INT(((HOUR(A2)+MINUTE(A2)/60)-17.5)/0.25)*0.25
で良いかと思います。

いかがでしょうか？

回答No.1

takuranke

作ったものを載せてください、
どのように作ったのか、何処がうまくいかないのか書いてなければ
助言のしようがありません。
それとも作ってくれということでしょうか？
Excelの便利なサイト

参考URL：

http://www.moug.net/

補足

すみません。
別に１から作れ！といってるわけではありません。
作り方がわからなかったから、ヒントを欲しかったのです。
どの関数を使うべきなのか分からなくて。
ifを使って、
例えば：
　　　Ａ　　Ｂ　　Ｃ
１　17:45　18:00　18:15　・・・・
２　0.25　 0.50　 1.00　・・・・
と言う一覧を作っています。

別のセルに、勤務時間を記入しています

４　　　Ａ　　　Ｂ　　　　　Ｃ
５　出勤時間　退社時間　　残業時間
６　　8:00　　　18:00　　　ここに関数入力しています。

もし、残業時間がA1よりも大きくA2よりも小さかったら、A2を表示するって感じで作っているのですが、関数自体がそのまま表示されます。

作り方が間違ってるのでしょうね。
説明のしかたもよく分からないので、
ありがとうございました。