エクセル関数で給与の計算について

質問

エクセルの関数を使って、出社時刻と退社時刻、休憩時間を入力するだけで日給が求められるようにしたいのですが、どうしてもうまく計算されません。
始業時間　10:00　　時給　1,000
終業時間　17:00　　残業割増　100
休憩　　　 1:00　　休日割増　100
と決まっていて、平日に残業がある場合、休日出勤で残業がある場合、残業はない場合などがありますので、それらの条件での日給を求めたいのです。
表には、A1からK1までに下記のような項目と値になっています。　　
日付　曜日　出社時刻　退社時刻　出社　 退社　 休憩　
6/1　　土　　9:45　　18:25　 　10:00 18:15 1:00
残業　　時間　　実働時間　 日給
1:30 　8:30 8.50 9,200
Eの出社には、15分単位で切り上げた値を、Ｆの退社には、15分単位で切り捨てた値がでるようにして、Iの時間には、「F-E-G+H」、Jの実働時間には、シリアル値を時間と分に変換する計算になっています。
日給がうまく計算される方法は無いでしょうか？
教えてください。お願いします。

回答No.6

smile_Joy

残業があった場合はJ9*(K4+K5)、休日で残業があった場合はJ9*(K4+K5+K6)、休日で残業がなかった場合はJ9*(K4+K6)といった条件も含めて式を作りたいのですが、複雑すぎて関数をどのように組み合わせたらいいのかがわからないのです。

休日の定義はなんでしょうか？
単純に土日だけならいいですが、祝祭日も含めますか？
その場合、休日を示す記入欄をつくるか、別のシートで
休日一覧を作らないと分かりませんよね。

単に土日だけなら=WEEKDAYの値が1か7で判別つきますが。

あとは、=IFで組み合わせるか、新しく標準時給、残業分、休日手当分の
列を作って合計するかですよね。
ここまで関数使えるのですから、あとちょっとで完成です。がんばって。

お礼

親切に教えていただきありがとうございました。
教えていただいたやり方を参考にして作ってみます。

回答No.5

smile_Joy

>「B9」の「=WEEKDAY(A9,1)」を「=WEEKDAY(A9,2)」に
>直すのですか？そうすると曜日が変わってしまいます。
=TEXTで曜日表示すると変わりますね(^^;
先ほどの発言無視してください。スミマセン

>「K9」の式はどのようにしたら6,250と表示されるのでしょうか？
単純にＪ9*K4で出ないでしょうか？
あとはそれに割増分を組み込めばいいと思いますが。
こちらでは試しにやってみると上手くいっていますが
その式が上手くいかないのでしょうか？

補足

残業がなかった場合はJ9*K4で6,250となるのですが、
残業があった場合はJ9*(K4+K5)、休日で残業があった場合はJ9*(K4+K5+K6)、休日で残業がなかった場合はJ9*(K4+K6)といった条件も含めて式を作りたいのですが、複雑すぎて関数をどのように組み合わせたらいいのかがわからないのです。
別に簡単に求められる方法があれば、そちらのやり方でもかまわないのですが・・・。

回答No.4

smile_Joy

>実働時間が6.25、日給は6,250にならないといけないのに、
>6,000となってしまって、正しく計算されないのです。

Ｊ列の実働のセル書式が日付表示になっていませんか？
試したところ、数値形式になっていれば、
書かれている計算式で6250円と表示されましたが。。

休日割増は=WEEKDAY(A9,1)の部分を=WEEKDAY(A9,2)にし
6以上なら土日で休日とみなすか、別シートで
休日表を作り、日付であたって判定のうえ、
割増分を掛けてやればいいと思います。

補足

早々の回答ありがとうございます。
J列の実働のセル書式が日付表示になっていました。
数値形式にしたら6.25となりました。

「B9」の「=WEEKDAY(A9,1)」を「=WEEKDAY(A9,2)」に直すのですか？そうすると曜日が変わってしまいます。

「K9」の式はどのようにしたら6,250と表示されるのでしょうか？

回答No.3

smile_Joy

ちょっと整理させて下さい。
どこの列まで上手く計算できているのですか？

補足

今作っている給与計算表は、
「H4」始業時刻、「I4」10:00、「J4」時給、「K4」1,000、「H5」終業時刻、「I5」17:00、「J5」残業割増、「K5」100、「H6」休憩時間、「I6」1:00、「J6」休日割増、「K6」100
というような参照する表を作っています。
C、D、Ｇには、時間を入力します。
例えば、「B9」には、「=WEEKDAY(A9,1)
「E9」には、「IF(C9="","",IF(C9<$I$4,CEILING(C9,TIME(0,15,0))))」
「F9」には、「IF(D9="","",FLOOR(D9,TIME(0,15,0)))」
「H9」には、「IF(OR(C9="",D9=""),"",IF(F9<=$I$5,0,F9-$I$5))」
「I9」には、「IF(OR(C9="",D9=""),F9-E9-G9+H9)」
「J9」には、「IF(OR(C9="",D9=""),"",HOUR(I9)+MINUTE(I9)/60)」
という式になっています。
残りのK列の式が分からないのですが、ほかの列の式もこれで正しいかどうかもわからないので、ご存知でしたら教えてください。
とても分かり難いかと思いますけど、お願いします。

回答No.2

smile_Joy

> F、Ｇには10:00と17:15と15分単位で計算されるのですが、
ここまではうまくいってるんですよね？

Iは普通に＋、－計算で正しく表示されるはずですから、
あとはIに=HOUR()関数で時間をもとめ時給を掛け、
Iに=MINUTE()関数で分をもとめ、６０で割ったものに
時給を掛けて足せばどうでしょうか？

補足

Kの日給には、平日で残業なしや残業ありの場合、また、休日出勤で残業なしや残業ありの場合でも正しく計算させたいのですが、どのような式にすればうまく計算されるようになるのかが、どうしてもわからないのです。
IF、OR、AND、HOUR、MINUTE、INTなどの関数を組み合わせてみましたけれども、ちゃんとした答えがでてこないのです。
休日の場合の条件として、Bの曜日には、WEEKDAY関数で日曜が1、土曜が7の曜日に変換しています。

回答No.1

smile_Joy

どのようにうまくいかないのでしょうか？

他の作業用の列を複数作って計算した方が
楽じゃないでしょうか？
別のシートにしたり、非表示にすれば、見た目は今と同じですし。

補足

説明不足でした。
例えば、出社時刻が10:00、退社時刻が17:15、休憩を1:00、残業はなしといった場合に、F、Ｇには10:00と17:15と15分単位で計算されるのですが、Iの時間が6:15で、実働時間が6.25、日給は6,250にならないといけないのに、6,000となってしまって、正しく計算されないのです。
作業用の列は非表示にしてもかまわないのですが、1日の日給が出したいのです。難しいでしょうか？