時間入力で時給を計算したい

質問

エクセルで簡単に自分の月の収入を把握するための表を作りたいです。
例えば９時～１７時までの勤務とします。それ以降は時間外です。
Ａ列に「日付」
B列に「時間内」
C列に「時間外」
D列に「B+C」
と表したい時に、時刻を入れればそのまま時給が計算されるように
するにはどうしたらよろしいですか？
時間外は時給が1.5倍に計算されるように関数を指定したいです。
おわかりになる方、よろしくお願いします。

回答No.2

sanori · ベストアンサー

あ゛ーーー　時間かかりました。
残業代ください。（笑）

簡単のため、昼休みをまたがない勤務（午前出勤で昼前に退勤とか、午後だけ出勤）には対応していません。
あと、ご質問文にある「B+C」は不要だと思うので書いていません。
それから、９時以前に出勤しても、９時出勤扱いになることにしました。

セルＡ１に「日付」と入力
セルＢ１に「出勤時刻」と入力
セルＣ１に「退勤時刻」と入力
セルＯ１に「時間内」と入力
セルＰ１に「時間外」と入力
セルＱ１に「時間内給与」と入力
セルＲ１に「時間外給与」と入力

セルＢ２に出勤時刻を記入（例　8:43）
セルＣ２に退勤時刻を記入（例　17:34）

セルＤ２
=HOUR(B2)　　と入力

セルＥ２
=MINUTE(B2)

セルＦ２
=HOUR(C2)

セルＧ２
=MINUTE(C2)

セルＨ２
=IF(D2<9;9;D2)

セルＩ２
=IF(D2<9;0;INT((E2+4.9)/5)*5)

セルＪ２
=F2

セルＫ２
=INT(G2/5)*5

セルＬ２
=H2+I2/60

セルＭ２
=J2+K2/60

セルＮ２
=IF(M2>17;17;M2)

セルＯ２
=N2-L2-55/60

セルＰ２
=IF(M2>17;M2-17;0)

セルＱ２
=O2*時給　　　　（※：時給は具体的な数字を入力）

セルＲ２
=P2*時給*1.5　　（※：同上）

あとは、
セルＤ２～Ｒ２をドラッグして選択し、「コピー」
セルＤ３～Ｒ３以下に「貼り付け」すればよいですね。

以下は、私の実験結果。

出勤時刻 退勤時刻 時間内 時間外 時間内給与 時間外給与
8:43 17:00 8 43 17 0 9 0 17 0 9 17 17 7.08 0 10625 0
9:00 16:01 9 0 16 1 9 0 16 0 9 16 16 6.08 0 9125 0
9:03 16:59 9 3 16 59 9 5 16 55 9.08 16.92 16.92 6.92 0 10375 0
9:04 17:01 9 4 17 1 9 5 17 0 9.08 17 17 7 0 10500 0
9:05 20:29 9 5 20 29 9 5 20 25 9.08 20.42 17 7 3.42 10500 7687.5
9:06 20:30 9 6 20 30 9 10 20 30 9.17 20.5 17 6.92 3.5 10375 7875
9:06 20:31 9 6 20 31 9 10 20 30 9.17 20.5 17 6.92 3.5 10375 7875
9:00 20:31 9 0 20 31 9 0 20 30 9 20.5 17 7.08 3.5 10625 7875

お礼

うわ～～、感激です、ほんとにすみません！！<m(__)m>
もっと簡単にできるものと安易に思っていました（汗）
早速コピペしながらどんどん入れていったのですがなぜかうまくできません。
まずH2とI2のセルに関数をコピーで入れると関数のまま表示され、
L2のセルとN以降のセルが全て#VALUE!です。
もちろん「時給」の部分は金額に変えてあります。
ひょっとしたら時間があくまで仮定だったのがいけなかったでしょうか。
実際の時間内勤務時間は8：50～17：45なんです。
H2とI2の関数を理解していないので手直しすることができません・・・
（パソコンスクールに20万も払って通ったのにIF関数すら体得できてないなんて
どうしようもないな、と反省しています）

思い切り他力本願で申し訳ないのですがお暇な時にでも教えて
いただけると有難いです。
遅い時間まで本当にありがとうございました（ＴＴ）

回答No.5

redowl

退勤時間の修正・・・見落としました。（汗）

セルＮ２、　P２の
１７　を　　16.75 に　入れ替え（４カ所）

（１７は　17:00 を表しており　　退勤　16:45　であれば　16.75）

補足

本当にすみません。。。4箇所17.75に変えましたが（17時45分なので）
やっぱりきちんと計算されません（ＴＴ）
時間内給与が-1466.666667のまま・・・・・・
もう私のレベルでは無理でしょうか？
ここまで面倒みていただいたのに出来ないなんて自分が情けないです。
申し訳ありません

回答No.4

redowl

#3です。
＃２＞＞９時以前に出勤しても、９時出勤扱いになることにしました。
の部分が、　9:00　を基準として、数式をたてた。
のを、　8:50 を基準にしたい。
ので、どう変更すればいいか？　ということですね？

セルH２の　　=IF(D2<9,9,D2)を
=IF(D2<(8+5/6),(8+5/6),D2)

セル　I2　　=IF(D2<9,0,INT((E2+4.9)/5)*5)　を
=IF(D5<(8+5/6),0,INT((E5+4.9)/5)*5)

９：００　に関連するのが　　９　と入力してある部分を
８：５０　に値する数値に置き換えるときに　１０進数にする必要が有るので
（８＋５／６）　正確には　（８＋５０／６０）
５０分は６０で割ることで　「時」に換算。（８時間３０分なら　８．５時間）

既存のデータを、数パターンいれて、ミス計算していないか
検証してください。　何か有れば、　補足で・・・

補足

再度ありがとうございます。
HとIを変えてみましたがHのセルの数値が8.833333333に変わっただけで
あとは何もかわりません。。。
何が間違っているのかもわからない状況で本当にすみません。
L列の関数が=H2+I2/60にもかかわらず、9という数値が入っているのです。
H2「8.833333333」+I2「0」÷60なので9って変ですよね。

回答No.3

redowl

20万の勉強料を無駄にしないために・・・・

教訓：コピペの際、うっかり見落とす場合もある・・・「コピペトラップ」

＞セルＩ２
=IF(D2<9;0;INT((E2+4.9)/5)*5)
の「;」　半角セミコロン　は　「,」半角カンマに置き換えましょう

修正箇所　は、　アイ２、Ｎ２，Ｐ２　です。

お礼

別の方にまでご面倒をおかけしてすみません。。。
すべて半角カンマに直したら確かに表示はきちんとなりました。
しかし、質問文で例にあげた時間で作っていだたいたので
9：00を8：50に変更する方法がわからず・・・
今のままですと時間内給与が「-1466.666667」です。

ほんとにこのままじゃ無駄な20万になってしまいますね。

回答No.1

sanori

そういうエクセル表は作ったことがあります。
しかし、ご質問文では情報が不足しています。

・残業は何分単位で計算するのですか。
　極端に言えば、１７：０１まで勤務したら、１分間分の残業手当がつくのですか。

・所定のお昼休みや休憩は、それぞれ何時何分から何時何分ですか。（残業時間帯の休憩時間も含む）

・遅刻や早引きをした場合、単に時給を引かれるだけですか。
　その場合も、１分単位で引かれるのですか。
　あるいは午前か午後丸々半日欠勤になりますか。
　それとも、フレックスタイム制で、コア時間以外は遅刻早退欠勤の対象にならないですか。

等々

補足

早速ありがとうございます。情報不足で申し訳ありませんでした。
●時間内・外ともに全て5分単位です。17：05以上働かないと残業はつきません。
●昼休みは55分（12：00～12：55）で休憩時間は差し引かれません。
●遅刻や早引きも全て5分単位の計算です。
残業時間帯の休憩は無いです。

他に、まだ必要なことはありますか？
お手数ですがよろしくお願いいたします。