エクセル有給管理表を作りたい

質問

エクセル2003です。エクセル初心者です。

現在有給管理表を作っています。
6ヶ月で10日、1年6ヵ月後で11日、2年6ヵ月後で12日有給がつきます。

基準年月日　2012/6/1

氏名　入社日　前年度から引き継いだ有給　次回有給付与日　次回有給付与数　　使用数　残日数
鈴木　　　　　　　　　　15日　　　　　　　　　　　　　　2012/6/11　　　　　　10日　　　　　　　2日　　　　11日

こんな感じの表を作りたいのです。

(1)基準日を入れると自動的に現在の有給残日数が表示されるようにしたい。
基準年月日の部分に日付をいれるとその時点での有給残日数が表示されるようにしたいんです。

残日数は、前年度引き継いだ有給の数－使用数＋次回有給付与数になると思うのですが、
この表の鈴木さんの場合、次回有給付与日は、2012/6/11になります。
基準日に2012/6/11以降の日付を入れると、残日数にその数も追加されるようにしたいんです。

(2)6ヶ月で10日、1年6ヵ月後で11日、2年6ヵ月後で12日有給つくのですが、3年6ヶ月で13日つくのですが、それも自動的に入力されるようにしたいんです。

わかりにくい文章ですいません。。
何かアイディアいただければと思います。

よろしくお願い致します。

回答No.1

KURUMITO · ベストアンサー

次のようにしてはどうでしょう。
Ａ１セルには基準年月日と文字を入力しB1セルに2012/6/1というように日付を入力します。
Ａ2セルには氏名、Ｂ2セルには入社日、・・・・・Ｇ2セルには残日数とお示しの項目名を並べます。
そこでＡ3に鈴木などと入力しＢ3セルには入社日、Ｃ3セルには別のシートからＶＬＯＯＫＵＰ関数などで前年度における残り日数を表示させます。Ｄ3セルには次の式を入力し下方にドラッグコピーします。

=IF(B3="","",DATE(YEAR(B$1)-IF(AND(MONTH(B$1)>=1,MONTH(B$1)<=3),1,0),MONTH(B3)+6,DAY(B3)))

Ｅ3セルには付与日が来た時の付与日数ですが次の式を入力して下方にドラッグコピーします。

=IF(B3="","",IF(D3>=DATE(YEAR(B3)+2,MONTH(B3)+6,DAY(B3)),12,IF(D3>=DATE(YEAR(B3)+1,MONTH(B3)+6,DAY(B3)),11,IF(D3>=DATE(YEAR(B3),MONTH(B3)+6,DAY(B3)),10,""))))

Ｆ3セルはその年度のおける使用数を入力します。
Ｇ3セルには基準年月日での残りに数を表示させるために次の式を入力して下方にドラッグコピーします。

=IF(A3="","",IF(D3>B$1,C3-F3,IF(D3<=B$1,C3+E3-F3,"")))

なお、ここでは年度は4月1日から翌年の3月31日までが当該年度としています。

お礼

お礼が遅くなり、申し訳ありません。。
丁寧に説明していただき、本当にありがとうございます＾＾
助かりました＾＾