役立つ! 総務マガジン

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

入社半年後から1年ごとに追加される有給休暇を関数で計算する

有給休暇の管理は、労務管理の中でも非常に重要で、しかも複雑な業務です。今回は新卒・中途にかかわらず、入社日に対応して半年ごとに発生する有給休暇を管理し、消化分や繰り越し分と合わせて残り日数を計算する表を作ります。

[2019年10月28日公開]

手計算では大変な手間がかかる作業を省力化 

社員の有給休暇の管理は、総務の重要な業務の一つです。2019年4月に厚生労働省が働き方改革の一環として「年5日の年次有給休暇の確実な取得」を義務付けたこともあり、あらためて注力している企業も多いでしょう。

企業により有給休暇の運用ルールは異なりますが、今回は労働基準法に則した最低限の基準に従い、社員の有給休暇日数を管理する表を作成します。今回、作成を想定する有給休暇運用のルールは、以下になります。

  1. 入社日から6カ月経過したら、週の労働日数(1~6日)に応じた日数の有給休暇が発生。
  2. 以降、1年ごとに有給休暇を追加。追加される日数は週の労働日数と勤続期間に応じて変わる。
  3. 前年度に消化していなかった有給休暇は翌年に繰り越されるが、発生から2年が経過した有給休暇は消滅する(翌翌年への繰り越しはされない)。

これらの計算を手で行うのはかなり複雑ですが、今回と次回の2回に分けて、関数を使って入社年月日から自動的に算出できる表を作ります。今回は上記ルールの1~2に相当する部分を解説します。

自社のルールに沿った有給休暇日数の表を作る

まずExcelのワークシートに、週の労働日数と勤続期間ごとの有給休暇の日数をまとめた「有休日数表」を作成します。以下の表のデータは労働基準法で定められている年次有給休暇の付与日数にのっとったもので、週5日~6日は通常労働者、週1日~4日はパートタイム労働者などに適用されます。

有休日数表の例

週の労働日数と勤続期間に応じた有給休暇の日数を、あらかじめ表にしておきます。日数の単位「日」は表示形式を変更して表示しているだけで、実際は数値データです。入社日の6カ月後、その後は1年6カ月後、2年6カ月後……といったように、表で示した日数の有給休暇が発生します。

社員ごとの有給休暇管理表を作る

ブックに新規のワークシートとして、社員名ごとの有給休暇管理表のワークシートを作ります。入社年月日を入力するセルは、計算がやりやすいように「入社年」「月」「日」をそれぞれ独立させています。

有給休暇管理表の例

表の上部に社員番号と氏名、入社年月日を入力しておきます。下部の「有休発生日」列などの計算式は、以下の手順で入力していきます。

入社年月日から有休発生日を計算する

まずは、「有休発生日」列で、有給休暇の発生日を計算します。有給休暇は入社日から6カ月後に発生し、以降は1年ごととなるため、DATE関数を使って入社年月日の年に「勤続年数」列の数値を加え、月に6(6カ月)を加えます。

これで入社(0年目)は入社日から6カ月後、1年目は1年6カ月後の日付が求められます。月に6を加えて年が繰り上がる分も、DATE関数で自動的に計算されます。

以下の手順では、セルに日付の表示形式を設定してからDATE関数を入力します。

(1)「有休発生日」列の表示形式を設定する

「有休発生日」列の1行目(セルB7)を選択し、[ホーム]タブの[数値]グループから[ダイアログボックス表示ボタン]をクリックします。

(2)日付の表示形式を選択する

[セルの書式設定]ダイアログボックスが表示されました。[表示形式]タブの[分類]から[日付]をクリックし、[種類]の[2012年3月14日]をクリックして最後に[OK]をクリックします。これで表示形式の設定は完了です。

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

続けてDATE関数を入力しますが、数式がやや複雑になるので、ダイアログボックスを使った手順で解説します。セルB7を選択した状態で[関数の挿入]ボタンをクリックして[関数の挿入]ダイアログボックスを表示しましょう。[関数の検索]に「date」と入力して[検索開始]をクリックし、[関数名]に[DATE]が表示されたらクリックして[OK]ボタンをクリックします。

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

DATE関数の[関数の引数]ダイアログボックスが表示されました。[年][月][日]に次の内容を入力し、[OK]をクリックします。

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

引数の内容

引数内容意味
$G$4+A7-1入社年(セルG4)に勤続年数(セルA7)を足し、入社年から1年ごとの年を計算
$H$4+6入社月(セルH4)に6を足し、6カ月後の月を計算
$I$4入社日(セルI4)と同日

入社年、月、日の参照は数式をコピーしても同じセルを参照できるように絶対参照(「$G$4」のように「$」を付けた表記)とします。

数式を入力するとき、セルの参照はマウスのクリックでも入力できます。例えば[年]の引数を入力中にセルG4をクリックすると「G4」と入力されます。続けて[F4]キーを押すと「$G$4」と絶対参照の表記に切り替わり、簡単に入力できます。

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

DATE関数の入力が完了し、「2015年10月1日」と、入社初年の有休発生日が表示されました。なお、入社年月日が未入力の状態でこのDATE関数を入力すると、意味のない日付(1900年5月31日)が表示されます。先に入社年月日を入力しておきましょう。

有休日数表を参照して発生する日数を求める

続けて、先に作成した「有休日数表」から勤続期間と週の労働日数に応じた有給休暇の日数を、[発生日数]列に入力します。ここではOFFSET関数を使い、「有休日数表」から[週労働日数]列の数値と、勤続期間に応じたセルの数値を参照します。まずはOFFSET関数の仕組みを見ていきましょう。

OFFSET関数の構文
OFFSET(参照,行数,列数,高さ,幅)

五つある引数のうち[高さ]と[幅]は省略し、今回は使いません。表の基準(左上)にあたるセルのアドレスを[参照]に指定し、そこから[行数]と[列数]分だけ離れたセルを参照します。

今回の例では、[行数]は「週労働日数」列に対応する1~6の値を参照します。[列数]は勤続年数0年では1、1年では2……となり、6年以降は7です。今回は数式を簡単にするため、表の右端(J列)に[列数]の値を入力しています。表のデータとしては無意味なので、使用時にはJ列を非表示にしてもいいでしょう。

(1)週労働日数とOFFSET関数が使う列数を入力する

「週労働日数」列に週の労働日数(ここでは5)を入力します。また、OFFSET関数の数式を簡単にするため[列数]で使う値をJ列に入力しておきます。

(2)セルE7にOFFSET関数を入力する

DATE関数と同じ要領で[関数の挿入]ダイアログボックスからOFFSET関数を入力し、OFFSET関数の[関数の引数]ダイアログボックスを表示します。[参照][行数][列数]にそれぞれ次の内容を入力し、[OK]をクリックします。

引数の内容

引数内容意味
参照有休日数表!$B$3有給休暇の日数を入力した表の基準となるセル
(「有休日数表」シートのセルB3)を指定します
行数C7「有休日数表」の何行目(週の労働日数)を参照するかを指定します
列数J7「有休日数表」の何列目(勤続期間の区分)を参照するかを指定します

(3)OFFSET関数が入力された

OFFSET関数が入力され、「発生日数」列のセルE7に、週の労働日数が5日で入社から6カ月目に発生する有給休暇日数「10」が表示されました。

自社のルールに合わせてカスタマイズできる

入社年月日と週の労働日数を入力するだけで、有給休暇の発生日と発生日数が自動計算されるようになりました。次回は有給休暇の繰り越しに関する数式などを入力し、有給休暇の管理表を完成させます。

年度の初日に有給休暇が発生するルールの企業もありますが、入社日に応じて社員一人一人の有給休暇の発生日が異なる場合は、全員分の計算が大変になります。今回作ったような表を使って、管理を楽にしましょう。

OFFSET関数では[行数]と[列数]に応じた有給休暇の日数を表にして参照できるので、週の労働日数と勤続期間に合わせた自社規定の有給休暇の日数を入力しておけば、自社のルールに合わせて利用できます。

協力メディア

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

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

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

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

Excel研修の詳細情報を見る

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

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

Office 365 について見る