有給管理表を作成したい：エクセル Q&A(Tips)

有給管理表を作成する　　　　topへ

当サイトの掲示板にて有給の残数を計算したいとの質問がありました。
計算は有給の付与される日が入社日から勤続6ヶ月、1年6ヶ月、2年6ヶ月・・・といった具合に、入社日で変化するというところ。
また、有給には時効があり2年後には消滅?するといった決まりがあるようです。
これらのことを踏まえて計算をする必要があるとのことで、ちょっと面倒なことになります。
なお、当方はこのような計算の運用には携わったことがありませんので、このページの記述には不適当なところがあるかもしれませんことお断りしておきます。
運用の際には十分な検証をされることをお願いいたします。参考となれば幸いです。(2015/1/24に書いています)
このページでの計算は↓のハンドブックの有給「付与日数」をペースに計算する例です。
- 有給休暇ハンドブック｜厚生労働省
  http://www.mhlw.go.jp/new-info/kobetu/roudou/gyousei/kinrou/040324-17.html
有給管理表の完成図
- この有給管理表はB1セルに入力する基準日を基に有給残の計算結果をA6セルに表示するというものです。
  有給残を計算するために、10行目以降で経過年ごとの有給消化日数を入力して、各年ごとの付与数、時効数、残高を計算しています。
  サンプル：　http://www.eurus.dti.ne.jp/~yoneyama/sample/yukyu2.xlsx

サンプルには右にもう一つの表があります。この表は「年休」を計算するもので、依頼主さんのところ独特のものと思われます。
このページでは何も説明をしていません事、申し訳ございませんでした。
質問を頂いたときのメール内容をここに記載しておきます。（2019/5/2　追記しました)

希望構成
・1シートに1個人の有給管理一覧
・有給の種類が4種類あり、一目で休暇残が分かり、休暇消滅を自動更新させたいです。

詳細
有給の種類4種について
法的な有給（2年抹消型）で一日有給・半日有給の2種

祝日を出勤する代わりに発生する有給（有給名を年休とします）で一日年休・半日年休の2種
当年4月1日～翌年3月31日が年休取得期限になります。翌年3月31日を過ぎると
年休がリセットされ（前回年休の残は繰越されません）、新たに同じ月の期間内に6日発生します。

上記4種類の有給が一目で分かるような表を作成したいです。

社員の基本データ部分の計算
- 基準年月日と社員番号～入社年月日は直接入力する部分になります。
  計算は勤続年数と有給付与数の部分です
- 勤続年数の計算は　=DATEDIF(C3,B1,"Y")&"年"&DATEDIF(C3,B1,"YM")&"ヶ月"　で求めています。
  計算結果は文字列です。この結果は単に表示しているだけでどこにも使用していません。
- 有給付与数は6ヶ月後に10日、1年6ヶ月後に11日、・・・　6年6ヶ月以上では20日となるので、下表からVLOOKUP関数で求めることができます。
  ただし、社員ごとにこの表を記載したり、他のシートを参照するのはちょっと面倒な感じがします。
  
  ここでは、数式内にこの経過月数と付与日数のデータを配列で書き込んで処理することにしました。
  数式(計算式)は　=VLOOKUP(DATEDIF(C3,B1,"M"),{0,0;6,10;18,11;30,12;42,14;54,16;66,18;78,20},2)　としました。
  DATEDIF(C3,B1,"M")　は経過月数を求めています。配列は　6か月未満時には　0　とするように　0,0;　から始まっています。
  配列内の「 , (コロン)」と「 ; (セミコロン)」は見にくいので、注意深くご覧ください。
経過表の部分の計算
- 経過月数は6、18、30・・・と6から始まって12増加していく数値です。
  ここでは、フィル機能で6,18を入力して、オートフィルで入力しています。
  数式を使うなら　=6+(ROW(A1)-1)*12　といった数式で可能です。
- 有給付与日は入社日からの経過月数の日にちを求めます。
  数式は　=EDATE($C$3,B11)　としました。
- 経過年数は計算には不要なものですが、経過月数ではピンと来ないので、経過年数を表示してみました。
  数式は経過月数から計算する　=INT(B11/12)&"年"&MOD(B11,12)&"ヶ月"　としました。
  - 勤続年数で使った数式のように　=DATEDIF($C$3,C11,"Y")&"年"&DATEDIF($C$3,C11,"YM")&"ヶ月"　としてもよいのですが、ちょっと煩雑なので却下しました。
- 有給付与日数は　E3セルと同じですが、B11セルを参照しています。
  数式は　=VLOOKUP(B11,{0,0;6,10;18,11;30,12;42,14;54,16;66,18;78,20},2)　としました。
- 繰越は初回時には0ですので、E11には　0　と入力します。
  E12セル以降は　K列の時効後残の前期の値を参照します(入力します)ので、　=K11 とひとつ前の行の値を入力します。
- 保有数は付与数と繰越の合計ですので、　=E11+F11　としました。　=SUM(E11:F11) の方がもっともらしく見えるかもしれません。
- 消化日数はここの例では　手入力します。
  - 元々の質問では、取得した(消化した)有給を列記してその数から計算するものでした。
    そのような使い方をするなら、期間内の有給数をカウントする必要があります。
    COUNTIFS関数で期間内の数をカウントしています。
- 期末残高では期間内の有給の残数を求めるので、その期間の有給保有数から有給の消化数を差し引いたものです。
  数式は　=G11-H11 としました。
- 時効数は繰り越された有給を消化しても残りがある場合は時効となる(繰越されない)ものになります。
  数式は　=IF(H11<=F11,F11-H11,0)　としました。
  - 数式は　=MAX(F11-H11,0)　とすることも考えられます。こっちの方が見た感じがよいですけど・・・。
- 時効後残　は時効となる有給を差し引いた後の残りの有給数になります。つまり、来期に繰り越される有給数です。
  数式は　=I11-J11　としました。
  - ここの数式は　消化日数が保有数を超えてしまうとマイナス(負の値)になります。
    このようなことは実際にはあり得ないので無視すればよいと思いますが、負の値とならないようにするには　=MAX(I11-J11,0)　とすることが考えられます。
    
    ↓
    間違って、有給を与えすぎても次期には影響しない・・・・といった感じです。でもあり得ないですよね?
有給残
- 有給残は経過表から、基準年月日の行の期末残高を読み取ります。
  数式は　=VLOOKUP(B1,C11:I26,7)　としました。
- 現在の残高を表示している行がどこなのか見にくいので、条件付き書式を使って該当行に色を付けたいと思います。
  条件付き書式で経過表の　B11:K26　に　=(ROW()-10)=MATCH($B$1,$C$11:$C$26)　といった数式のルールを設定してみました。
  ROW()-10　の部分は　経過表の最初の行が　1　となるようにするためのものです。
  - 例えば、基準年月日が「2014/1/20」となると、203/1/10　の行に色が付きます。
    そして、有給残の値はこの行の期末残高の値になります。