[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『有給休暇の残日数の計算』（ちぃ）
すみません。過去ログを色々見たのですが、わからなく同じ様な質問をして申し訳ありませんが宜しくお願いします。
うちの会社では下記のようなフォーマットで有休を管理しています。
氏 名 入社日 有給発生日前年繰越 月 1 2 3 4 5 6 7 8 9 10 11 12 残日数 ○○ H15.3.17 H15.9.17 12 発生 20 27 消化 1 1 2 1 △△ H16.8.6 H17.2.6 8 発生 16 19 消化 3 2 ×× H16.10.19 H17.4.19 12 発生 16 25 消化 1 1 1 ◎◎ H17.2.9 H17.8.9 12 発生 16 26 消化 1 1 ●● H17.9.20 H18.3.20 13 発生 14 22 消化 1 1 2 1 ▲▲ H17.12.24 H18.6.24 14 発生 14 20 消化 2 3 3 ▽▽ H18.5.9 H18.11.9 15 発生 14 23 消化 1 2 2 1 ◆◆ H18.10.28 H19.4.28 11 発生 13 23 消化 1 □□ H19.7.11 H20.1.11 17 発生 12 21 消化 1 2 5 ▼▼ H19.12.12 H20.6.12 2 発生 11 11 消化 1 1 ■■ H20.11.22 H21.5.22 0 発生 10 8 消化 2
今は残日数のところは=IF(（D3+SUM(F3:Q3)-SUM(F4:Q4）>20,20,(D3+SUM(F3:Q3)-SUM(F4:Q4））で20日以上にならないようにしたのですが まず発生を自動ででるようにはならないでしょうか？ 有休発生日の月になった場合、有休発生日の年なら、10日、有休発生日の年から二年目は11日、三年目12日、四年目14日、五年目16日、六年目以降20日です。 発生日の月に自動で付与日数が出るようにしたいのですが、このとき前年度繰越を足しても20日以上にならないようにしたいのです。 発生日までに使った有休は前月度の残数から引き、前年度残数-発生月までに使った有休+本年度付与日数が20日を越えないようにするにはどうしたらいいでしょうか？
説明が下手で申し訳ありませんが宜しくお願いします。 何か不明点、不足点などありましたら教えてください。
自動発生のみで。
[A] [B] [C] [D] [E] [F] [G] ・・・ [V] [W]
[1] 氏名 入社日 有給発生日 前年繰越 月 1 2 ・・・ 0 10
[2] ○○ H15.3.17 H15.9.17 12 発生 1 11
[3] 消化 1 2 12
[4] 3 14
[5] 4 16
[6] 5 20
[7] 6 20
以下続く
として
休暇取得表を例えばV,W列に作成したとして
F1 =IF(MONTH($C2)=F$1,VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1),"")
もっとスマートになるかと思いますが、参考として･･･ （ぽ）
すみません。 $V$1:$W$21のところをどういれたらいいかわかりません。
[A] [B] [C] [D] [E] [F] [G] [H] [I]
[1] 氏名 入社日 有給発生日 前年繰越 月 1 2 3 4
[2] ○○ H15.3.17 H15.9.17 12 発生 10
[3] 消化 1 2
[4]
この場合、発生に入力がなければ前年度から消化・・ということはできないでしょうか？ わかりずらい説明ですみません。
（ちぃ）
V,Wは作業列なんでそのまま丸写ししてもらえれば いいかと。 セルF1に上記の式をコピーして右へコピーすれば希望通りに なると思うんですが･･･（数値はうちこまないでね） 但し、表の構成が（ぽ）の書いた通りであることが条件 ですけどね ^^； （ぽ）
追記 間違えてました！ F1セルではありませんね！ F2セルです。
説明足らずでしたね。 V列 ･･･ 有給発生日からの経過年数 W列 ･･･ 有給取得日数 のつもりです。 最初に書いたのは6年経過までなんで 「以下続く」は7年以降、20日 という意味だったんです。 理解してもらえたでしょうか？ （ぽ）
ありがとうございます。 F2 =IF(MONTH($C2)=F$1,VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1),"")出来ました。
これでばっちりなのですが残日数のところはどうしたらいいでしょうか？ 前年度の繰越とあわせ20日を越えないように、今年度の有休取得を決めたいのですが
例えば
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J]・・・
[1] 氏名 入社日 有給発生日 前年繰越 月 1 2 3 4 5 ・・・残日数
[2] ○○ H16.10.17 H17.4.17 12 発生 16 ・・・ 25
[3] 消化 1 1 1
[4]
と普通にD3+SUM(F3:Q3)-SUM(F4:Q4)とやると25となってしまうのですが これを20日以下にしたいのです。 ただ
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J]・・・
[1] 氏名 入社日 有給発生日 前年繰越 月 1 2 3 4 5 ・・・残日数
[2] ○○ H16.10.17 H17.4.17 12 発生 10 ・・・ 19
[3] 消化 1 1 1
[4]
上記のように、有休発生月になるまでの消化については前年繰越から引いて、 有休発生は前年度繰越+今年度有休取得-有休取得月までの有休消化の数字にしたいのですが。
うまく説明ができなくてすみません。 （ちぃ）
>これを20日以下にしたいのです。 については（ちぃ）さんが最初に書かれたIF関数で問題ないんでは？
>有休発生は前年度繰越+今年度有休取得-有休取得月までの有休消化の数字にしたい よく分かりません。 有休発生日の年なら、10日、有休発生日の年から二年目は11日、三年目12日、･･･ では無かったんですか？ 残日数の間違い？ （ぽ）
残日数の件、ようやく意味が分かりました！ような気がします。 例えば、今が5月として7月に有休が発生する場合で 5月の時点での残日数ってことですか？ （ぽ）
=IF(D3+SUM(F3:Q3)-SUM(F4:Q4)>20,20,D3+SUM(F3:Q3)-SUM(F4:Q4)) を残日数に入力すると20日と出てしまうのです。 私は有休取得日までの有休消化は前年度繰越から引き、 その上で本年度の有休取得を決定したいのです。 もちろん基本は有休発生日の年から、最初は10日、次は11日、12日・・・となっていくのですが 上記の○○さんのように前年度の繰越が、12日あり、有休取得月までに2日しか消化していません。 この場合、○○さんの本年度の有休取得は本来は16日なのですが 有休取得月までに2日消化しています。この分は前年度の12日から消化し有休取得月までに繰越は 10日残っています。有休は20日以上は付与できないのでこの時点で20日の有休になります。 なので、○○さんの有休取得は10日となっているんです。 わかりずらくて大変申し訳ありません（＞＜） この10日という数字を自動的にだしたいのですが・・・
（ちぃ）
すみません。書き込んでる間に新たな書き込みが！！ 残日数は例えば、今が5月として7月に有休が発生する場合で 5月の時点での残日数ってことでも、今年中に発生する残数でもどちらでも大丈夫です。
「10日」が2回出てきてややこしいので確認しますが、
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J]・・・
[1] 氏名 入社日 有給発生日 前年繰越 月 1 2 3 4 5 ・・・残日数
[2] ○○ H16.10.17 H17.4.17 12 発生 11 ・・・ 19
[3] 消化 1 1 1 1
[4]
となればいいのですか？ （ぽ）
そうです！！ （ちぃ）
F2 =IF(MONTH($C2)=F$1,IF($D$2+VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1)>=20,20-$D$2,$D$2+VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1)),"") G2 =IF(MONTH($C2)=G$1,IF((VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1)+$D$2-SUMPRODUCT(($F1:F1<=F1)*$F3:F3))>=20,20-($D$2-SUMPRODUCT(($F1:F1<=F1)*$F3:F3)),VLOOKUP(YEAR(TODAY())-YEAR($C2),$V$1:$W$21,2,1)+$D$2-SUMPRODUCT(($F1:F1<=F1)*$F3:F3)),"") でG2を右へフィルコピー。
もうギブアップします･･･ （ぽ）
お邪魔します。 お二人のやり取りを読んでましたが、よく分らなくなってきたので、一から考えてみました。 勘違い等も多々あるかと思いますが。。。
まず、最初の例は2009年（平成21年）が対象という事でよろしいのですよね？ なぜか数字が合わないので，？？？が付いてました。※それでも合わないとこがありますが・・・
前提として、対象となる年をS1セルに2009という風に入力します。 何年のデータかをEXCEL君に指示する為です。
発生の部分：F2セルに↓を入れてQ2セルまでコピー。その後F2:Q2をコピーし、該当行へ貼り付け。 =IF(MONTH($C2)=F$1,CHOOSE(MIN(6,DATEDIF($C2,DATE($S$1,F$1,DAY($C2)),"y")+1),10,11,12,14,16,20),"")
残日数の部分：R2セルに↓を入れて下方コピー（該当行だけでも勿論OK） =IF($E2="発生", MIN(SUM($D2-SUM(IF(COUNT($F2),0, OFFSET($F3,0,0,1,MATCH(MAX($F2:$Q2),$F2:$Q2,0)-1))),MAX($F2:$Q2)) ,20)- SUM(IF(COUNT($Q2),$Q3, OFFSET($F3,0,MATCH(MAX($F2:$Q2),$F2:$Q2,0)-1,1, 12-(MATCH(MAX($F2:$Q2),$F2:$Q2,0)-1)))),"") ※改行してます。 (sin) 以降、すぐにご返事できないかも？ です。
氏 名 入社日 有給発生日 前年繰越 月 1 2 3 4 5 ・ ・ ・ ○○ H15.3.17 H15.9.17 12 発生 消化 1 1 2 △△ H16.8.6 H17.2.6 8 発生 14 消化 3 2 ×× H16.10.19 H17.4.19 12 発生 9 消化 1 1 1 ◎◎ H17.2.9 H17.8.9 12 発生 消化 1 1 ●● H17.9.20 H18.3.20 13 発生 9 消化 1 1 2 1 ▲▲ H17.12.24 H18.6.24 14 発生 消化 2 3 3
R列に残日数があるとして、まずS1に何年度の有休かを入れる。今年だったら「2008」 S2に有休発生月までの消化日数を求める=SUM(OFFSET(E3,,,,MONTH(C2))) S3に今年発生の有休数=CHOOSE(IF($S$1-(YEAR(C2))>6,7,$S$1-(YEAR(C2))+1),10,11,12,14,16,18,20) F2の発生には=IF($A2="","",IF(MONTH($C2)=F$1,IF(($D2-$T2+$T3)>20,20-($D2-$T2),$T3),""))で12月までドラッグ F3には普通にその月に消化した日数を入れる。 R2の残日数には=D2+SUM(F2:Q2)-SUM(F3:Q3)
これで発生月までに消化した日数は前年度から、その残日数＋取得有休が20日を越える場合は 最大20日の付与を発生月になるはずです。 これではダメでしょうか？
ありがとうございます！！ もう返事がないものと思っていたので確認が遅くなりました。
sinさん、と↑上記の方どちらもできました！！ sinさんの方は発生月の記載がわかりやすかったです。
それで、また追加でお願いしたいのですが・・・ これを今度は、ぽｻﾝが言っていたように、例えば今が5月として7月に有休が発生する場合で 5月の時点での残日数を表示することはできますか？ 6月になったら6月の残日数・・みたいに・・。
すみません。
（ちぃ）
有休付与ルールについて、一つお尋ねします。 m(__)m
○○氏の有休発生日は「H15.9.17」ですが、2年目に11日の有休が付与されるのはいつですか？
「H16.9.1」ですか、それとも「H16.9.17」ですか。後者ですと、厄介な事になりそうな予感がしますが。
(半平太)
後者です。 そうなんですよね。自動で今のままのフォーマットでやろうとすると例えば、9/12に有休をとってると本来なら前年繰越から消化なんですが 9月の有休を1にすると本年度の発生から消化されてしまいます。 なので、その場合は8月に消化を加えてしまってます。
（ちぃ）
＞自動で今のままのフォーマットでやろうとすると ＞例えば、9/12に有休をとってると本来なら前年繰越から消化なんですが ＞9月の有休を1にすると本年度の発生から消化されてしまいます。 ＞なので、その場合は8月に消化を加えてしまってます。 この方針を採用すると、毎年1月が有休発生日の人は、 新規付与前に取得した1月の有休は、前年繰越から追加で差し引くことになりますね？
そう云うやり方を採用するものとして、、
(1) S1セル =TODAY()
(2) F2セル =IF(($C2<>"")*($C2<=$S$1)*(TEXT($S$1,"mdd")-TEXT($C2,"mdd")>=0)*(MONTH($C2)=F$1),
MIN(20,INDEX({10,11,12,14,16,20},DATEDIF($C2,$S$1,"Y")+1)+$D2-SUM($E3:E3)),"")
これをQ2までフィルコピー
(3) R2セル =IF(C2="","",IF(COUNT(F2:Q2),SUM(F2:Q2)-SUM(INDEX(F3:Q3,MATCH(99,F2:Q2)):Q3),D2+SUM(F2:Q2)-SUM(F3:Q3)))
(4) 「F2:R3」2行を一括選択して、必要行フィルダウン
<結果図> 行 ____A____ ____B____ ____C_____ ___D____ _E _ F _G_ _H_ _I_ _J_ _K_ _L_ _M_ _N_ _O_ _P_ _Q_ __R___ ___S___ 1 氏 名 入社日 有給発生日 前年繰越 1 2 3 4 5 6 7 8 9 10 11 12 残日数 2008/6/2 2 ○○ H15.3.17 H15.9.17 12 発生 8 3 消化 1 1 2 4 △△ H16.8.6 H17.2.6 8 発生 19 17 5 消化 3 2 6 ×× H16.10.19 H17.4.19 12 発生 20 20 7 消化 1 1
(半平太)
ご無沙汰です。先週まで忙しくてのぞけませんでした。 すでに半平太さんから回答が付いてますが、ふと思いついたので書き込みます。 作業列を使い、しかも、【コピー→値で貼り付け】の手作業付きですが... S2セルで、有休発生日までの日数を求めます。 =DATE(YEAR(TODAY()),MONTH(C2),DAY(C2))-TODAY() 【条件付き書式】を設定すれば、目視も楽になるかも？以下は例 条件1：セルの値が 次の値に等しい 0 で書式設定（パターン：赤、文字：太字の白） 条件2：セルの値が 次の値以下 3 で書式設定（パターン：黄、文字：太字の赤） 条件3：セルの値が 次の値以下 14 で書式設定（パターン：水色、文字：太字の赤） →14日前で条件3が適用され、3日前で条件2に変わり、当日は条件1。翌日以降は、条件2。 S3セルで、繰越分から引かれる消化日数を出します。 =SUM(F3:Q3) ※消化日数の入力は、基本的に当日に入力するものとして考えていますので、こんな式にしました。 当日もしくはそれ以前にでもS3セルをコピーし、値で貼付ければ、繰越分から引かれる消化日数が確定します。 （S2セルの条件付き書式の色を判断材料にし、S3セルの計算式を値にする事が、目的です。） また、手作業で適当な数字を入力して調整してもよいです。 →S2:S3セルをコピーし、該当範囲に貼り付け。 問題の残日数は、 =IF(S2>0,D2-S3,MIN(SUM(F2:Q2,D2-S3),20)-SUM(F3:Q3,-S3)) (sin) 相変わらずの勘違いもあるかも知れませんが、その際はご指摘下さい。
はじめまして。 半平太さんのものをコピーしてみましたが、発生がうまく入りません。 なぜでしょうか？ (Mint)
まだ見て居られましたら、新規で質問されたほうが良いと思います。 その際、この記事へのリンクと 具体的に【どうなるのか】を 書いておかれるのが良いと思います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.