エクセルで給料計算（残業）

質問

お世話になります。

表計算については殆んど無知なのですが、何とかパートさんの給料計算を楽にそして確実にできないものかと、こちらのサイトや本などを調べて自分なりに作ってみたものがあります。

１つのシートに出勤簿と給料明細を作り、出勤簿に出社時間と退社時間を入力すれば、総出勤日数や時間、支給額などが計算され、自動で給料明細に入力されるというものです。

なんとかそれで今までやってこれたのですが、今月から残業時間も発生することになりました。

ややこしいことに時給が、
●平日１０００円・土日祝１３００円
●平日１７：００を超えるものは１３００円・土日祝は残業しても１３００円
となっています。

1｜　Ａ　｜　Ｂ　｜Ｃ ｜　　Ｄ　　　｜　　Ｅ　　　｜Ｆ ｜ Ｇ｜　　Ｈ　　　｜
2｜日付｜曜日｜☆｜出社時間｜退社時間｜★｜×｜勤務時間｜
3｜　　　｜　　　｜　 ｜　　　　　　 ｜　　　　　 　｜　 ｜　 ｜　　　　　 　｜
4｜　　　｜　　　｜　 ｜　　　　 　　｜　　 　　　　｜　 ｜ 　｜　　　　　 　｜

※Ｃはその月の全ての土日祝に☆をつけます。
※Ｆはパートさんが出勤した土日祝だけに★をつけます。

私なりに残業代も出せる計算式を考えた結果、
=IF(C3="☆",H3*1300,IF(H3="","",IF(H3<7,H3*1000,IF(H3>=7,(H3-7)*(1000*1.3)+7*1000))))
という恐ろしく長いものになってしまいました・・。もうどういう思考回路でこんなものができあがったのか、自分でも思い出せません。

この式には問題が２つあります。
(1)土日祝に出勤している場合はきちんと計算されるが、出勤していない（空白）と＃VALUE！と出てしまう。
(2)この計算式では「７時間を超えるものは」として作っていますが、必ずしもそうではない状況も起こりうるということ。（9：00～17：00の勤務ならこの式でいけるのですが、10：00～17：00勤務のパートさんが残業する場合、この式に当てはまらなくなってしまいます。）

随分と長くややこしい説明になってしまいましたが、私がお聞きしたいのは、

■出勤していない土日祝に＃VALUE！がでないようにしたい。
■「７時間を越えるものは・・」という条件でなく、「１７：００を超えるものは・・」という条件にしたい。

以上を満たした計算式はできないものか？ということです。

よろしければ、力をお貸し下さい。何卒、よろしくお願いします。

回答No.5

aiai_013 · ベストアンサー

Ｈ３がそうなら、こうですね、
前回のでは、出勤してなければエラーになると思います。

=IF(H3="",""
,IF(F3="★",H3*1300
,IF(E3>17,(E3-17)*1300+(H3-(E3-17))*1000
,H3*1000
)))

勤務時間が""（空白）なら""（空白）
土日祝出勤なら、勤務時間*1300
退社が17を越えれば
　（退社時間－17時）*1300+（勤務時間-（退社時間－17時））*1000
その他は、勤務時間*1000

なおＨ３の前提も良くなかったので訂正させていただきます
=IF(ISBLANK(D3),"",IF(G3="×", E3-D3,E3-D3-1) )
＝もし（Ｄ３が空白なら，空白，そうで無い場合もし（Ｇ３が×なら，Ｅ３－Ｄ３，Ｅ３－Ｄ３－１））

でもＨ３は今のままでも大丈夫です。

なお参考に
Ａ３は、セルの書式設定で表示形式の分類を日付にする（種類はなんでも結構です）
Ｂ３は、長いですが
=IF(WEEKDAY(A3,1)=1,"日",IF(WEEKDAY(A3,1)=2,"月",IF(WEEKDAY(A3,1)=3,"火",IF(WEEKDAY(A3,1)=4,"水",IF(WEEKDAY(A3,1)=5,"木",IF(WEEKDAY(A3,1)=6,"金",IF(WEEKDAY(A3,1)=7,"土")))))))
Ｃ３は、=IF(OR(B3="日",B3="土"),"☆","")
Ｆ３は、=IF(AND(C3="☆",D3>0),"★","")

お礼

こちらのご回答にお礼を忘れていました…。

ありがとうございました！

回答No.4

aiai_013

前提が分からないところがあるので勝手に補足します

H3*1300の算式より
出勤時間・退社時間は時間を使わず、十進数での数値入力している
H3には、=IF(G3="×",E3-D3,E3-D3-1)　といった算式が入っている
として

下手な算式ですが、これでどうでしょうか
=IF(H3=0,""
,IF(F3="★",H3*1300
,IF(E3>17,(E3-17)*1300+(H3-(E3-17))*1000
,H3*1000
)))

勤務時間が0なら空白
土日祝出勤なら、勤務時間*1300
退社が17を越えれば
　（退社時間－17時）*1300+（勤務時間-（退社時間－17時））*1000
その他は、勤務時間*1000

あとエクセルのことなら、
教えて！goo > デジタルライフ > ソフトウェア > Office系ソフト
のカテも良いかも知れません。

お礼

お礼が遅くなってしまい、すみません。

Ｈ３には、
=IF(D3>0,(E3-D3)-IF(ISBLANK(G3),1,0),"")
という式が入っていますが、なんだかaiai_013さんのと比べて私のは長ったらしいですね・・(-_-;
今のままでも大丈夫なんでしょうか？？

算式に、説明までつけて下さってありがとうございます！！
私は関数初心者なので、すごく分かりやすいです。

早速試してみますね。ありがとうございました！

回答No.3

DIooggooID

Ｈ列の数式が不明です。

上記の計算式を基に、１７時以降の残業時間を考慮してみると次のような式になるでしょう。

=IF(C3="☆",H3*1300,IF(H3="","",IF(E3<=17,(1000*H3),(E3-17)*300+(1000*H3))))

また、Ｇ列の "×" を勤務時間にどのように反映しているのかも不明です。

お礼

返事が遅くなってしまってすみません。
また、質問文が説明不足で大変申し訳なく思います(>_<)

Ｈ列には
=IF(D3>0,(E3-D3)-IF(ISBLANK(G3),1,0),"")
という数式が入っています。

Ｇ列の『×』というのは、休憩時間をとらなかった場合につけるもので、『×』がつくことによって勤務時間に１時間加算して算出するようになっています。

教えていただいた式、試してみますね！
ありがとうございました。

回答No.2

poi-poi

時間がないので、途中だけですが・・・

Ｈ列に勤務時間の計算は
=MIN(E2,"17:00")-D2-"01:00"

I列に残業時間の計算を
=MAX(E2-"17:00",0)

と入れるとします。Ｈ列とＩ列はユーザー定義をh:mm;@にします
これで１７：００を超える時間給も足せます。

Ｊ１に普通金額を1000と入れて、Ｋ２に残業金額の1300と入れます。

そして、Ｊ２に
=IF(D2="","",HOUR(H2)*J1+HOUR(I2)*K1)
と入れます。これでその日のお給料が出ます。

あとは、休日の勘定ですが、NETWORKDAYS関数を使ったりすれば問題ないと思うのですが、どうでしょうか？Ｃ列にはどういう関数が入っていますか？

お礼

一身上の理由により、返事が遅れてしまって申し訳ないです。
また、お忙しい中お時間を割いていただいて、本当にありがとうございます。

Ｃ列には何も数式は入っていません。
土日祝の場合に、自分で『☆』マークをつける欄なのです。

私は関数に関しては殆んど無知なので、ＩＦ関数とかＣＯＵＮＴ関数くらいしか使えない（しかも本を見ながら・・）のですが、ＮＥＴＷＯＲＫＤＡＹＳ関数という便利そうなものがあるんですね！
それも本で調べてみたいと思います(^o^)ノ

回答No.1

poi-poi

Ｆ列に普通労働時間、Ｇ列に残業時間という感じで元の表の形を変更することは可能ですか？

お礼

お世話になります。

Ｆはなんとかなるかもしれません。
Ｇは、1時間の休憩をとらなかった場合に「×」をつける欄で、ｘをつけることによって１時間加算されることになっています。なので、ちょっと難しいかもしれません；；

さらに、出勤表の下に給料明細を作ってしまっているので、元の形を変更すると給料明細の形がおかしくなってしまいます。

ホントに、なんでこんなややこしいものを作り上げてしまったのか・・。

ＦとＧの場所でなくても、２列分の空きを作ればなんとかなりますか？