[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件がいっぱい』（Myu）
いつも楽しく勉強させて頂いておりますが、今日は投稿させて頂きます。
パートの給料計算で、月曜日から日曜日という単位で普通残業(A1)、深夜残業(H1)などに違うセルに答えを出したいと思います。
例えば
条件１）先月29日(月)から31(水)までに40時間以上働いている場合には無条件で今月の第一週1(木)から4日(日)の5:00から22:00まで働いた合計の5時間は普通残業の合計セル(A1)に5時間を返すように。(条件が当てはまらない場合は0を)
条件２）先月の29日から31日までは38時間(40時間以下)で、今月の1日から4日までに12時間働いており、この合計が40時間以上になる場合は 40=38=2時間は普通残業(A1)に返すように。(条件が当てはまらない場合は0を)
条件３）先月は3時間(40時間以下)で、今月は55時間の場合は55-40=15時間を普通残業に返すように。(条件が当てはまらない場合には0を)
=if(and(or( など色々とやってみたのですがどうもうまくいきません。
ご指導を宜しくお願いいたします。
※他の方のレスがついたら、私のこの照会レスは無視してください。
ちょっとご説明が飲み込みにくいです。 各条件に当てはまるケースと当てはまらないケースについて、どうなれば良いのか図表で示して頂けませんか？
(半平太)
半平太様
有難うございます。
条件１） 5:00〜22:00勤務 22:00〜5:00勤務
先月最終週 55時間 今月最初週 12時間 2時間 = 14時間
の場合は週に14時間しかなくても先月最終週の時間と合計すると69時間となり規定の40時間以上有るため、 12時間は普通残業、2時間は深夜手当ではなく深夜残業になる。
条件２） 5:00〜22:00勤務 22:00〜5:00勤務
先月最終週 38時間 今月最初週 12時間 3時間 = 15時間
の場合先月最終週の合計が規定の40時間以下、でも今月最初週と合計すると53時間で規定の40時間を超えるため、 40-38=2で 2時間は普通残業で残りの10時間は通常時間、3時間は深夜手当。
条件３） 5:00〜22:00勤務 22:00〜5:00勤務
先週最終週 5時間 今週最初週 7時間 10時間 = 17時間
の場合、先月最終週と今月最初週の合計は19時間で規定の40時間以下のため、7時間は普通時間、10時間は深夜手当。
と言うように条件が色々とあるタイムカードから普通残業(A1) 深夜手当(B1) 深夜残業(C1)、 それぞれのセルに時間が返ってくるようにしたいのです。
通常時間は別に合計するセルがあるのでこの場合考えなくて良くなってます。
A1にもB1にもC1にも全ての条件が当てはまるようにしたいのですが、 A1のみは時間帯が5:00から22:00の合計時間が対象で、B1とC1は22:00〜5:00の時間帯が対象です。
この説明で、大丈夫でしょうか？
どうか宜しくお願いいたします。
１、いくら読んでも二つの違いが分かりません(トホホ）
両方とも先月が40時間以下で、合計は40時間以上ですよね。どこに注目して、差を付けるのですか？
>条件２）先月の29日から31日までは38時間(40時間以下)で、今月の1日から4日までに12時間働いており、 > この合計が40時間以上になる場合は 40=38=2時間は普通残業(A1)に返すように。 >条件３）先月は3時間(40時間以下)で、今月は55時間の場合は55-40=15時間を普通残業に返すように。
２．サンプルでは、先月最終週の22:00〜5:00帯の時間がないのですが、考慮しないでいいと云う事ですか？
３．表のレイアウトはどうなっていますか？ どのセルに時刻データがあるか分からないのですが。
(半平太)
有難うございます。
表は
5:00〜22:00(B) 22:00〜5:00(C) 週単位の合計時間(D) F(普通残業) G(深夜手当) H(深夜残業)
月 in/out 時間 in/out 時間
火 in/out 時間 in/out 時間
水 in/out 時間 in/out 時間
木 in/out 時間 in/out 時間
金 in/out 時間 in/out 時間
土 in/out 時間 in/out 時間
日 in/out 時間 in/out 時間
小計時間
を縦に6週分繰り返してます。最初の繰り返しは先月最終週の合計時間、2番目の繰り返しに今月の最初の週の時間が計算されるようになっており、そしてその小計行のそれぞれセルF14には普通残業の対象時間, G14には深夜手当の対象時間, H14には深夜残業の対象時間を計算したいのです。最初の週はだけは先月の時間を考慮に入れて普通残業、深夜残業などの金額が変わってくるからです。もちろん、先月発生した残業手当は先月支払い済みなので計算する必要はありません。
条件１） 先月最終週に40時間超えていることがまず必要です。超えていれば無条件で今月最初の週の時間は全て残業扱いで、通常時間(5:00〜22:00)は普通残業(F)、深夜の作業時間は深夜残業(H)になります。今月最初の週が40時間未満でも残業扱い(F)になるわけです。そして深夜作業時間は深夜手当(G)ではなく深夜残業(F)扱いになります。
条件２） 先月最終週が超えてない場合には次月の最初の週の時間を合計して40時間超えているかの確認が必要です、合計が40時間を超えていれば、先月の40時間に満たない分(例えば先月最終週38時間として)、(40-38=2)2時間が普通残業(F) (残りは通常作業時間なのでこの計算には当てはまらない、別途計算しているのでこの質問では考える必要がありません)、深夜の時間が発生していれば深夜手当(G)に発生した時間を返す。そして尚かつ、今月最初週が40時間を超えている場合(例えば48時間)には先に引いた2時間を差し引いた残りが40時間超えている(48-2=46)場合にはその超えている分(6時間)も普通残業に加算されます。結果8時間を普通残業として(F)に返す。
条件３） 先月最終週と今月最初週の合計が40時間以下の場合は、普通残業は発生しない。でも、22:00〜5:00に作業していればその時間は(G)の深夜手当が付くようになります。
説明が下手で申しわけありません。
一緒に考えて頂ける方がいると思うだけで心強いです。
宜しくお願いいたします。
明確なレイアウトが分かりませんが、下図の様だとして
(1) E14セル =IF(TEXT(B2,"[h]:mm")*1>="40:00"*1,B14,MAX((B14+C14-"40:00")*(TEXT(B14+C14,"[h]:mm")*1>="40:00"*1),
("40:00"-B2)*(TEXT(B2+B14+C14,"[h]:mm")*1>="40:00"*1)))
(2) F14セル =IF(TEXT(B2,"[h]:mm")*1>="40:00"*1,0,C14)
(3) G14セル =IF(TEXT(B2,"[h]:mm")*1>="40:00"*1,C14,0)
行 _A__ ______B_______ ______C_______ _________D_________ ____ E ____ ____ F ____ _____G_____ 1 5:00〜22:00(B) 22:00〜5:00(C) 週単位の合計時間(D) F(普通残業) G(深夜手当) H(深夜残業) 2 先週 5:00:00 3 4 5 6 7 月 in/out in/out 8 火 in/out in/out 9 水 in/out in/out 10 木 in/out in/out 11 金 in/out in/out 12 土 in/out in/out 13 日 in/out in/out 14 小計 7:00 10:00 0:00 10:00 0:00
(半平太)
本当に有難うございます。
実を言うと遅まきながら今朝会社に来る道のりで、EにもFにもGにも同じ関数を入れようとするから分けらなくなってしまうんじゃないかしら？って自問自答してたんです。
コピペして運用させて頂きます。
本当に有難うございます。
♡♡♡♡♡
もう一度ご指導をお願いいたします。
頂きました関数を参考に深夜手当は=IF(OR(K11>40,K11+K19>40,K19>40)*1,0,J19)、深夜残業は=IF(OR(K11>40,K11+K19>40,K19>40)*1,J19,0)で、何とか正しく返って来るようになりましたが合計数によっては普通残業にマイナス数字がでてしまう場合があります。
実は5:00〜22:00のin/outは略してしまったのですが E, Fに、22:00〜5:00のin/outはH, Iに入力するようになっていてそれぞれ*24で整数に自動計算されています。
5:00〜22:00 22:00〜5:00
行 _E_____F_____G_____H_____I_____ J_____K_____L________M________N________ in out 計 in out 計 時間計 普通残業 深夜手当 深夜残業
１
２
３
４
５
６
７ 先週計 38時間
８月
９火
10水
11木
12金
13土
14日
15小計
という表の方が正しいです。
先に述べました条件１）は月末週と最初の週との合計が40時間を超えている場合で考える方が良いようでした。
本当に説明が下手で済みません。
先週が38時間、最初の週合計が16時間（今週の5:00〜22:00が1時間、22:00〜5:00が15時間の場合）は、(通常時間が1時間※)、深夜手当が1時間、深夜残業が14時間になります。
同じく合計が16時間でも内訳が5:00〜22:00が4時間、22:00〜5:00が12時間の場合は、(通常時間が2時間※)、普通残業が2時間、深夜残業が12時間となります。
又同じく合計が16時間でも内訳が普通時間が15時間、深夜時間が1時間の場合は、(通常時間が2時間※)、普通残業が13時間、深夜残業が1時間となります。
40時間から先月の38時間を引いてから残りが通常時間で(深夜なら深夜手当)、その他は残業だけど普通残業か深夜残業になります。
※通常時間は繰り返している6週分の小計を普通残業、深夜残業から引くようになっているため今回の計算には含まなくて大丈夫です。
お忙しい所大変ご迷惑をおかけしますが、普通残業の計算をもう一度ご指導頂くお願いいたします。
いろいろご説明いただいているのに、情けなくも計算ルールが理解できません。
上述のご説明を簡略にすると以下のようになりますが、追加で5〜8行目も時間を入れて頂けませんか？
行 1 先週計 普通時間 深夜時間 （通常時間） 普通残業 深夜手当 深夜残業 2 38 1 15 1 0 1 14 3 38 4 12 2 2 0 12 4 38 15 1 2 13 0 1 5 40 39 0 6 39 40 0 7 40 40 0 8 41 41 0
（半平太）
ご返信有難うございます。
行 1 先週計 普通時間 深夜時間 （通常時間） 普通残業 深夜手当 深夜残業 2 38 1 15 1 0 1 14 3 38 4 12 2 2 0 12 4 38 15 1 2 13 0 1 5 40 39 0 39 0 0 6 39 40 0 1 39 7 40 40 0 40 8 41 41 0 41
となります。
先月が40時間を超えていれば今月の時間帯1も時間帯2も全て残業扱いです。(時間帯1は普通残業、時間帯2は深夜残業)
先月が<40時間の場合は 40時間-先月分＝通常時間になりその残りが残業扱いです。(普通残業(時間帯1)か深夜残業(時間帯2))。この場合は時間帯1と時間帯2の時間も関係します。時間帯1で先月の40時間を満たす為に引く時間が足りない場合は、時間帯2からも40時間を満たすために時間を引きます。これが深夜手当。そして残りが深夜残業になります。(週40時間以上働いた時間が残業)
先月と今月に跨った1週間が40時間ない場合(先月＋今月<40時間)の場合は残業は発生しないので、今月の時間帯1は通常時間、時間帯2は深夜手当。
となります。
何度も申しわけありません。
宜しくお願いいたします。
そうすると、こんなことですかね？
(1) L15セル =IF(K7+G15+J15<=40,0,G15+J15-MAX(0,(40-K7)))-N15 (2) M15セル =IF(K7+G15+J15<=40,J15,MAX(0,J15-N15)) (3) N15セル =IF(K7+G15+J15<=40,0,MIN(J15,G15+J15-MAX(0,(40-K7))))
行 _D__ _____ E ______ F _G_ ______H_______ __I___ _J_ _______K________ ____L_____ ____M_____ ____N_____ 1 5:00〜22:00(B) . . 22:00〜5:00(C) . . 週単位の合計時間 (普通残業) (深夜手当) (深夜残業) 2 先週 3 4 5 6 7 38 8 月 in/out in/out 9 火 in/out in/out 10 水 in/out in/out 11 木 in/out in/out 12 金 in/out in/out 13 土 in/out in/out 14 日 in/out in/out 15 小計 4 12 2 0 12
何度も有難うございます。
いろんな例を入力して確認しました。見落としている例がない限り、今度こそ、ＯＫです！！
TEXTやMIN, MAXだけではなく、説明の仕方も一緒に勉強させて頂きました。
忍耐強くお付き合い下さいまして、本当に有難うございます。
素敵なX'masとすばらしい来年が来ますように・・・お祈り申し上げます。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.