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

質問

A/B/C/D/E/F/G/H/I/J/K/L/M
基本/17時～/深夜/出勤/休憩(入)/休憩(出)/退勤/基本/夜/割増/勤務時間/見込み
１０(土)/950/1000/1250/11:30/14:00/15:00/23:00/4:30/5:00/1:00/10時間30分/10,525円
１１(日)/950/1000/1250/18:00/-/-/22:30/-1:00/5:00/0:30/04時間30分/4,675円
１２(月)/900/950/1187.5/-/-/-/-/17:00/5:00/-22:00/00時間00分/-6,075円

パートで働いているのですが、給与の計算をエクセル２００７でしようとして行き詰っています。
まず平日は１７：００までが９００円、１７：００以降２２：００までが５０円増し。２２：００以降は２５％増しです。
休日は１７：００までが９５０円、１７：００以降２２：００までが５０円増し。２２：００以降は２５％増しです。
常に昼の時間帯から出勤すれば下記の計算式でなんとかできるのですが、１８：００から出勤の場合、最初の式をオートでコピーしてきた際に休憩が存在しないので上記のようにどうしても計算がおかしくなります。

１．基本の求め方=("17:00"-E10)-(G10-F10)
２．夜の求め方=H10-"17:00"-(H10-"22:00")
３．深夜（割増）の求め方=H10-"22:00"
４．勤務時間の求め方=H10-E10-(G10-F10)

後、場合によっては休憩が１７：００をまたぐ場合もありますし、ラストまでおらず早い時間帯で出勤、退勤もあります。
どうかよろしくお願いします。

回答No.4

pc_knight

No.2です。
No.2、No.3以外の方法を思いつきました。
三種類の勤務時間（基本、夜、割増）を出勤／休憩(入)／休憩(出)／退勤の時刻から直接計算式を立てるのは難しいので次のような方法を思いつきました。
＜方法＞
（１）拘束時間（基本、夜、割増）の欄をＮ～Ｐ列に設け、拘束時間を求める計算式（下記）を入れる。
（２）休憩時間（基本、夜、割増）の欄をＱ～Ｓ列に設け、休憩時間を求める計算式（下記）を入れる。
（３）拘束時間と休憩時間から勤務時間（基本、夜、割増）＝拘束時間（基本、夜、割増）－休憩時間（基本、夜、割増）を求める計算式を入れる。(例. I2=N2-Q2)

＜拘束時間、休憩時間の計算式＞
・基本拘束時間(N2セル)　=IF(E2<=17/24,IF(H2>17/24,"17:00"-E2,H2-E2),0)
・夜拘束時間(O2セル)　　=IF(E2<=17/24,IF(H2>=17/24,IF(H2<=22/24,H2-"17:00",5/24),0),IF(E2<=22/24,IF(H2<=22/24,H2-E2,"22:00"-E2),0))
・割増拘束時間(P2セル)　 =IF(E2<=22/24,IF(H2>=22/24,H2-"22:00",0),IF(H2>=22/24,H2-E2,0))

・基本休憩時間(Q2セル)　 =IF(F2<=17/24,IF(G2<=17/24,G2-F2,"17:00"-F2),0)
・夜休憩時間(R2セル)　　=IF(F2<=17/24,IF(G2<=17/24,0,IF(G2<=22/24,G2-"17:00",5/24)),IF(F2<=22/24,IF(G2<=22/24,G2-F2,"22:00"-F2),0))
・割増休憩時間(S2セル)　 =IF(G2>=22/24,IF(F2<=22/24,G2-"22:00",G2-F2),0)

お礼

pc_knight様、有り難うございます。

確かにフリーソフトも考えたのですが、家計簿（エクセル）との連携を考えているので
将来の事も踏まえ、このまま頑張ってエクセルで完結させたいと思います。
ちなみに使用しているエクセルのVerは2007なので、関数は最大で６４までネスト出来る（？）みたいです。
（問題があった時の修正が大変そうですが、、、（汗））

回答の様に、まず「もし、この場合‥‥じゃぁ、この場合‥‥では、この場合‥‥でっ、この場合、、、」
と、箇条書きで設計図を作ってから考えていけば時間はかかるでしょうが出来そうな気が？、、、してきました。
でっ、やってみたのですが、、、。

=IF(E10<=17/24,IF(F10<=17/24,IF(G10<=17/24,IF(H10>=17/24,IF(H10>=22/24,IF(G10>=17/24,IF(F10>=17/24,IF(G10>=22/24,IF(F10>=22/24),H10-"17:00"),H10-G10),H10-"17:00"-(G10-F10)),H10-E10-(G10-F10)),5),5-(G10-"17:00")),5-(G10-F10)),5-("22:00"-F10)),5),"22:00"-E10-(G10-F10)),F10-E10),"22:00"-E10)

なんか、ダメなようです。。

別な角度から、、、と思っていたら！？おぉぉぉぉぉぉーっ、すばらしいです！！
一旦、計算用に別々にした方が関数がシンプルでいいですね。
本当に有り難うございました。。

d(゜Д゜)☆スペシャルサンクス☆( ゜Д゜)b
゜☆,。・:*:・゜★o(´▽｀*)/♪Thanks♪＼(*´▽｀)o゜★,。・:*:・☆゜

回答No.3

pc_knight

No.2です。IF関数を提案しましたが、基本時間は良いとして、夜時間の計算式は12ケースもあることが分りIF関数では無理のような・・・。フリーソフトを試されては・・・。あとはVBAにより論理的にデータ処理を行わせるかです
出勤、休憩(入) 、休憩(出)、退勤の各時刻をS、Ki、Ko、T、各勤務時間帯を「基本」、「夜」、「深夜」とすると
基本時間は
ケース1：すべての時刻が「基本」の場合=T-S-(Ko-Ki)
ケース2：(S、Ki、Ko)が「基本」で、(T)が「夜・深夜」の場合=17:00-S-(Ko-Ki)
ケース3：(S、Ki)が「基本」で、(Ko、T)が「夜・深夜」の場合=Ki-S
ケース4：(S)が「基本」で、(Ki、Ko、T)が「夜・深夜」の場合=17:00-S
ケース5：すべての時刻が「夜・深夜」の場合=ゼロ
となりIF関数は、
=IF(E10<=17/24,IF(F10<=17/24,IF(G10<=17/24,IF(H10<=17/24,H10-E10-(G10-F10),”17:00”-E10-(G10-f10)),F10-E10),”17:00”-E10),0)となります。
他に「IF(AND」を用いる方法もあります。

夜時間は、
ケース1：(S、Ki、Ko)が「基本」で、(T)が「夜」の場合= T-17:00
ケース2：(S、Ki)が「基本」で、(Ko、T)が「夜」の場合= T-Ko
ケース3：(S)が「基本」で、(Ki、Ko、T)が「夜」の場合= T-17:00-(Ko-Ki)
ケース4：(S、Ki、Ko、T)が「夜」の場合= T-S-(Ko-Ki)
ケース5：(S、Ki、Ko)が「基本」で、(T)が「深夜」の場合=5
ケース6：(S、Ki)が「基本」で、(Ko)が「夜」、(T)が「深夜」の場合=5-(Ko-17:00)
ケース7：(S)が「基本」で、(Ki、Ko)が「夜」、(T)が「深夜」の場合=5-(Ko-Ki)
ケース8：(S)が「基本」で、(Ki)が「夜」、（Ko、T）が「深夜」の場合=5-(22:00-Ki)
ケース9：(S)が「基本」で、(Ki、Ko、T)が「深夜」の場合=5
ケース10：(S、Ki、Ko)が「夜」以前で、(T)が「深夜」の場合=22:00-S-(Ko-Ki)
ケース11：(S、Ki)が「夜」以前で、(Ko、T)が「深夜」の場合= Ki-S
ケース12：(S)が「夜」以前で、(Ki、Ko、T)が「深夜」の場合=22:00-S

回答No.2

pc_knight

Ｎｏ1さんが紹介されているように、フリーソフトの活用が最善かと思います。
もし、ご自身で勉強を兼ね解決されるのでしたら、計算式に「If関数」を使います。
例えば、
（１）Ｉ１０セルの基本時間は、Ｅ１０出勤時刻帯により計算式が異なります。
１７：００以前の場合は、基本時間=("17:00"-E10)-(G10-F10)で合っています。
１７：００を過ぎた場合は、基本時間＝０（時間）ですから
基本時間の求め方=IF(E10<=17/24,"17:00"-E10-(G10-F10),0)となります。

（２）Ｈ１０セルの夜の時間も、（ａ）１７：００以前から出勤していたかどうか
（ｂ）２２：００まで勤務していたかどうかの二つの条件の複合で計算式が異なります。
夜の求め方=IF(E10<=17/24,IF(H10>=22/24,"22:00"-"17:00",H10-"17:00"),IF(H10>=22/24,"22:00"-E10,H10-E10))
（３）深夜（割増）もＨ１０セルの退勤時刻により計算式が異なります。
退勤時刻が２２時以前の場合は　深夜（割増）＝０
退勤時刻が２２時を超える場合は　深夜（割増）＝H10-"22:00"であっています。
計算式は、基本時間の例を参考にご自身で・・・。（Ｅｘｃｅｌのヘルプ画面にて「Ｉｆ」で検索すると用例が出てきます。）

以上の計算式は、休憩時間が基本時間帯である１７：００までの時間帯内にあるとして示しました。
「場合によっては休憩が１７：００をまたぐ場合もありますし」となると（１）、（２）の計算式に更にその条件を考慮した「If関数」のネストを行う必要があります。

お礼

有り難うございます。
IF関数はさっぱりだったのですが、じぃぃぃぃーっと見つめていたらなんとなくですが光が見えてきたような、、（汗。
基本=IF(E10<=17/24,"17:00"-E10-(G10-F10),0)
出勤時間が17:00より前であれば"17:00"-E10-(G10-F10)の値を、じゃなければ,0を、と言う意味ですよね？
でっ

割増=IF(H10>=22/24,H10-"22:00",0)

がなんとかできました。

後、休憩が17:00をはさんだ場合ですが、一応、自分なりに考えてみました。

基本=IF(E10<=17/24,IF(G10>=17/24,G10-E10-(G10-F10),"17:00"-E10-(G10-F10)),0)

これでなんとかなってるみたいですが、例えば11:00に出勤して15:00に退勤の場合
おかしくなります。。（涙
こんな調子ですので割増分の関数でIFをネストさせる場所（タイミング）がいまいち判りません。
お時間がありましたら、今一度、ご教授をおねがいいたします。

回答No.1

d2hc

参考にしてください
http://www.vector.co.jp/soft/win95/business/se382566.html

お礼

有り難うございます。