役立つ! 総務マガジン

Excelで毎年の有休日数と繰り越し分を計算

勤続年数に応じた日数をもとに、繰り越しや消滅分も計算

前回に引き続き、Excelで有給休暇の管理表を作成します。前回は入社年月日に応じた有給休暇の発生日や日数の計算を行いました。今回は、IF関数を使って残った有給休暇の翌年への繰り越しや、発生から2年が経過した分の消滅に関する処理を行います。

[2019年11月11日公開]

繰り越しの複雑なロジックをIF関数で処理

有給休暇の管理表を、前回(Excelで複雑な有給休暇日数の計算を自動化)に引き続いて作成します。前回は入社日から6カ月が経過したら週の労働日数に応じた有給休暇を発生させ、勤続年数に応じた有給休暇日数を計算する部分を作成しました。今回は年ごとの消化日数や残日数と、前年からの繰り越し分の計算を処理する部分を作成します。

前年度に未消化だった有給休暇は、翌年に繰り越されます。しかし、発生から2年が経過した有給休暇は時効によって消滅するため、翌々年への繰り越しは行われません。今回は、IF関数を使ってこの繰り越しと消滅の処理を行います。

Excelで複雑な有給休暇日数の計算を自動化

期間ごとの合計日数と期限、残日数を計算する

前回は以下のような「有給休暇管理表」の、「有休発生日」列と「発生日数」列を作成しました。今回は、既にデータが入っている「勤続年数」列と「週労働日数」列を除いた残りの列を作成していきます。

まずは、「前年繰越」列と「発生日数」列を合計した有給休暇の日数が「合計」列に入るように計算式を作成しましょう。SUM関数を利用します。

(1)SUM関数を入力する

「合計」列にあたるセルF7に「=SUM(D7:E7)」と入力します。

SUM関数の構文
SUM(セル範囲)

SUM関数で、同じ行の「前年繰越」列と「発生日数」列にあたるセルD7とセルE7の合計を計算します。初年度は繰り越し分がないため、発生日数がそのまま入ります。

続けて、DATE関数で「対象期限」列の日付を求めます。これは、発生および繰り越しした有給休暇の期限となる日付で、翌年の有給発生日の前日となります。2015年10月1日に発生した有給休暇は、2016年9月30日が対象期限です。

(2)DATE関数の入力を開始する

前回と同様、ダイアログボックスを使ってDATE関数を入力します。セルG7を選択した状態で[関数の挿入]ボタンをクリックして[関数の挿入]ダイアログボックスを表示しましょう。[関数の検索]に「date」と入力して[検索開始]をクリックし、[関数名]に[DATE]が表示されたらクリックして[OK]ボタンをクリックします。

(3)DATE関数の引数を入力する

DATE関数の[関数の引数]ダイアログボックスが表示されました。[年][月][日]を、それぞれ有給休暇の発生日であるセルB7を基準に計算するため次の内容を入力し、[OK]をクリックします。

DATE関数の構文
DATE(年,月,日)

引数の内容

引数内容意味
YEAR(B7)+1有休発生日の翌年
MONTH(B7)有休発生日と同じ月
DAY(B7)-1有休発生日の1日前
(有休発生日が1日だった場合は自動的に前月の最終日になる)

(4)DATE関数が入力された

DATE関数が入力され、「対象期限」列の日付が表示されました。

(5)日付の表示形式をコピーする

「有休発生日」列の日付と同じ表示形式を「対象期限」列にも適用します。書式のコピー元であるセルB7を選択し、リボンの[書式のコピー]ボタンをクリックします。

(6)書式を貼り付ける

マウスポインターがハケの形に変わりました。この状態でセルG7をクリックします。

(7)書式が貼り付けられた

コピーした書式が貼り付けられ、「対象期限」列の表示形式が「有休発生日」列と同じになりました。

期間中の有給休暇の残りを計算する「残日数」列の数式も入力しておきます。これは「合計」列から「消化日数」列を引いて計算します。関数は必要なく、減算(引き算)の数式を入力すればOKです。

(8)残日数の計算式を入れる

セルI7に「=F7-H7」と入力します。

(9)残日数が表示された

「残日数」列に数値が表示されました。今後「消化日数」列を入力すれば、その値に応じて「残日数」列の日数は減っていくようになります。

IF関数で条件分岐して繰り越し日数を計算する

次に、前年からの繰り越し日数である「前年繰越」列を計算していきます。冒頭で述べたように、前年に未消化の有給休暇があれば翌年に繰り越されますが、発生から2年が経過した分は消滅します。

今回はIF関数を利用して繰り越しと消滅の処理を行います。繰り越しおよび消滅を処理するには、前年の「消化日数」列および「前年繰越」列を比較し、次のようにロジックを整理できます。

「消化日数」が「前年繰越」以上
繰り越し分以上の日数を消化しているため消滅分なし。前年の「残日数」がそのまま繰り越される
「消化日数」が「前年繰越」未満
繰り越し分が未消化となるが、発生から2年が経過するため消滅する。よって、前年の「発生日数」がそのまま繰り越される

つまり、前年の「消化日数」列の値が「前年繰越」列の値以上なら「残日数」列の値、そうでなければ「発生日数」列の値を繰り越せばよい、ということになります。これをIF関数で実現しましょう。

(1)IF関数を挿入する

これまでに入力したデータを数行分コピーしておき、「前年繰越」列の勤続1年目にあたるセルD8を選択します。この状態でDATE関数の入力と同じ要領で[関数の挿入]ボタンをクリックします。

(2)IF関数の引数を入力する

[関数の挿入]ダイアログボックスで「if」を検索し、IF関数の[関数の引数]ダイアログボックスを表示したら、それぞれの引数に次の内容を入力して[OK]をクリックします。

IF関数の構文
IF(論理式,値が真の場合,値が偽の場合)

引数の内容

引数内容意味
論理式H7>=D7前年の「消化日数」は「前年繰越」以上か?
値が真の場合I7前年の「消化日数」が「前年繰越」以上の場合、「残日数」を参照する
値が偽の場合E7前年の「消化日数」が「前年繰越」未満の場合、「発生日数」を参照する

(3)IF関数をコピーする

セルD8にIF関数が入力されました。0年目で「消化日数」が0(空白)の状態では「発生日数」も「残日数」も同じ値になっています。続けてセル右下のフィルハンドルをドラッグし、以降のセルにもコピーします。

(4)IF関数がコピーされた

IF関数をコピーしたら、「消化日数」列に数字を入力してみましょう。上の表の8行目(1年目)では「消化日数」が「前年繰越」よりも少ないため、「発生日数」が翌年に繰り越されています。9行目(2年目)では「消化日数」のほうが多いため、「残日数」が繰り越されます。

複雑な処理もロジックを整理してシンプルに作成できる

有給休暇日数の繰り越し、および消滅の処理は頭の中だけで考えると複雑ですが、どのようなロジックになるかを整理してIF関数を利用することで、条件に合った処理を実現しています。

どのような式にすればいいのか悩んだときは、条件や計算に使う値をいったん書き出してみるなどして整理しましょう。複雑な計算も、頭の中で考えていたときほど回りくどい計算は必要ないと気付ける場合もあります。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

「大塚ID ビジネスeラーニング」Excelを学ぶ新コースをリリースしました

大塚IDご登録者の方へ、限定e-ラーニングが受講いただけます

ご要望が高かった、Excelのコース。
基本操作から関数の使い方、グラフの作成方法まで幅広く学べます。

ログインして利用する