• ベストアンサー

エクセルで給料計算(残業)

お世話になります。 表計算については殆んど無知なのですが、何とかパートさんの給料計算を楽にそして確実にできないものかと、こちらのサイトや本などを調べて自分なりに作ってみたものがあります。 1つのシートに出勤簿と給料明細を作り、出勤簿に出社時間と退社時間を入力すれば、総出勤日数や時間、支給額などが計算され、自動で給料明細に入力されるというものです。 なんとかそれで今までやってこれたのですが、今月から残業時間も発生することになりました。 ややこしいことに時給が、 ●平日1000円・土日祝1300円 ●平日17:00を超えるものは1300円・土日祝は残業しても1300円 となっています。 1| A | B |C |  D   |  E   |F | G|  H   | 2|日付|曜日|☆|出社時間|退社時間|★|×|勤務時間| 3|   |   |  |       |       |  |  |       | 4|   |   |  |       |       |  |  |       | ※Cはその月の全ての土日祝に☆をつけます。 ※Fはパートさんが出勤した土日祝だけに★をつけます。 私なりに残業代も出せる計算式を考えた結果、 =IF(C3="☆",H3*1300,IF(H3="","",IF(H3<7,H3*1000,IF(H3>=7,(H3-7)*(1000*1.3)+7*1000)))) という恐ろしく長いものになってしまいました・・。もうどういう思考回路でこんなものができあがったのか、自分でも思い出せません。 この式には問題が2つあります。 (1)土日祝に出勤している場合はきちんと計算されるが、出勤していない(空白)と#VALUE!と出てしまう。 (2)この計算式では「7時間を超えるものは」として作っていますが、必ずしもそうではない状況も起こりうるということ。(9:00~17:00の勤務ならこの式でいけるのですが、10:00~17:00勤務のパートさんが残業する場合、この式に当てはまらなくなってしまいます。) 随分と長くややこしい説明になってしまいましたが、私がお聞きしたいのは、 ■出勤していない土日祝に#VALUE!がでないようにしたい。 ■「7時間を越えるものは・・」という条件でなく、「17:00を超えるものは・・」という条件にしたい。 以上を満たした計算式はできないものか?ということです。 よろしければ、力をお貸し下さい。何卒、よろしくお願いします。

質問者が選んだベストアンサー

  • ベストアンサー
  • aiai_013
  • ベストアンサー率60% (230/382)
回答No.5

H3がそうなら、こうですね、 前回のでは、出勤してなければエラーになると思います。 =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 なおH3の前提も良くなかったので訂正させていただきます =IF(ISBLANK(D3),"",IF(G3="×", E3-D3,E3-D3-1) ) =もし(D3が空白なら,空白,そうで無い場合もし(G3が×なら,E3-D3,E3-D3-1)) でもH3は今のままでも大丈夫です。 なお参考に A3は、セルの書式設定で表示形式の分類を日付にする(種類はなんでも結構です) B3は、長いですが =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,"土"))))))) C3は、=IF(OR(B3="日",B3="土"),"☆","") F3は、=IF(AND(C3="☆",D3>0),"★","")

CoCo-31
質問者

お礼

こちらのご回答にお礼を忘れていました…。 ありがとうございました!

その他の回答 (4)

  • aiai_013
  • ベストアンサー率60% (230/382)
回答No.4

前提が分からないところがあるので勝手に補足します 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系ソフト のカテも良いかも知れません。

CoCo-31
質問者

お礼

お礼が遅くなってしまい、すみません。 H3には、 =IF(D3>0,(E3-D3)-IF(ISBLANK(G3),1,0),"") という式が入っていますが、なんだかaiai_013さんのと比べて私のは長ったらしいですね・・(-_-; 今のままでも大丈夫なんでしょうか?? 算式に、説明までつけて下さってありがとうございます!! 私は関数初心者なので、すごく分かりやすいです。 早速試してみますね。ありがとうございました!

  • DIooggooID
  • ベストアンサー率27% (1730/6405)
回答No.3

H列の数式が不明です。 上記の計算式を基に、17時以降の残業時間を考慮してみると次のような式になるでしょう。 =IF(C3="☆",H3*1300,IF(H3="","",IF(E3<=17,(1000*H3),(E3-17)*300+(1000*H3)))) また、G列の "×" を勤務時間にどのように反映しているのかも不明です。

CoCo-31
質問者

お礼

返事が遅くなってしまってすみません。 また、質問文が説明不足で大変申し訳なく思います(>_<) H列には =IF(D3>0,(E3-D3)-IF(ISBLANK(G3),1,0),"") という数式が入っています。 G列の『×』というのは、休憩時間をとらなかった場合につけるもので、『×』がつくことによって勤務時間に1時間加算して算出するようになっています。 教えていただいた式、試してみますね! ありがとうございました。

  • poi-poi
  • ベストアンサー率31% (115/361)
回答No.2

時間がないので、途中だけですが・・・ H列に勤務時間の計算は =MIN(E2,"17:00")-D2-"01:00" I列に残業時間の計算を =MAX(E2-"17:00",0) と入れるとします。H列とI列はユーザー定義をh:mm;@にします これで17:00を超える時間給も足せます。 J1に普通金額を1000と入れて、K2に残業金額の1300と入れます。 そして、J2に =IF(D2="","",HOUR(H2)*J1+HOUR(I2)*K1) と入れます。これでその日のお給料が出ます。 あとは、休日の勘定ですが、NETWORKDAYS関数を使ったりすれば問題ないと思うのですが、どうでしょうか?C列にはどういう関数が入っていますか?

CoCo-31
質問者

お礼

一身上の理由により、返事が遅れてしまって申し訳ないです。 また、お忙しい中お時間を割いていただいて、本当にありがとうございます。 C列には何も数式は入っていません。 土日祝の場合に、自分で『☆』マークをつける欄なのです。 私は関数に関しては殆んど無知なので、IF関数とかCOUNT関数くらいしか使えない(しかも本を見ながら・・)のですが、NETWORKDAYS関数という便利そうなものがあるんですね! それも本で調べてみたいと思います(^o^)ノ

  • poi-poi
  • ベストアンサー率31% (115/361)
回答No.1

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

CoCo-31
質問者

お礼

お世話になります。 Fはなんとかなるかもしれません。 Gは、1時間の休憩をとらなかった場合に「×」をつける欄で、xをつけることによって1時間加算されることになっています。なので、ちょっと難しいかもしれません;; さらに、出勤表の下に給料明細を作ってしまっているので、元の形を変更すると給料明細の形がおかしくなってしまいます。 ホントに、なんでこんなややこしいものを作り上げてしまったのか・・。 FとGの場所でなくても、2列分の空きを作ればなんとかなりますか?

関連するQ&A

専門家に質問してみよう