離職率計算

アルバイトや社員の定着率と離職率を自動計算するエクセルの作成方法

  • このエントリーをはてなブックマークに追加
  • LINEで送る

本日の記事は、アルバイトを雇っている企業に特に読んでいただきたい内容となっている。

なぜ、アルバイトは新卒採用と違って離職率や定着率を測定しづらいかご存知だろうか?それは、入社日が毎月のように発生するからだ。

つまり、今年20人雇って残ったのが10人だったから、離職率は50%だ!というわけにはいかない。例えば、今年5月に雇ったアルバイトが来年の3月に辞めたり、今年1月に雇ったアルバイトが3月に辞めるなど、入社日と退社日がバラバラになってしまう。すると、うちの会社のアルバイトの定着率や離職率は・・・と、計算がややこしくて困ってしまう。

本来は、「わが社はアルバイトの1年定着率は62%で3年定着率は38%となっている。」というデータを出すことが出来れば、採用ページに記載する原稿を変更する際や、離職を改善するための手当ての仕組みまで、会議でスムーズに決定されていくはずだ。

そこで、本日の記事では、以下のように入社日と退社日とアルバイトの名前を入力するだけで、簡単に定着率と離職率を出すことが出来るエクセルの作り方をお伝えしようと思う。エクセルに記述する関数も公開しているため、コピーして貼り付けすれば完成する。

当記事で作成できるエクセルは、正社員用でも良いしアルバイト用でも大丈夫だ。後で解説するが、離職率を出せば定着率も出るので、人材管理のために、ぜひ、ご利用いただきたい。

以下の記事では、当ブログで無料提供しているEXCELテンプレートで出力できるグラフを紹介している。

1. そもそもアルバイトや社員の定着率とは、なぜ必要なのか?

社員定着率とは、簡単に言えば、ある年度に採用した人材が〇年度までに何割残っているかを算出するための計算式だ。

例えば、2014年度から2016年度間における社員定着率の3年以内それぞれの社員定着率を出すには、どうやって計算すればいいのか?

以下のようなケースを例に考えてみよう。

1-1.採用人数から累積離職者数を引いて、社員定着率を割り出す方法

ここでは簡単に一括採用したケースで説明する。中途採用やアルバイト・パートメインで採用されている場合は、入社日がバラバラになるが、その計算式については、後で詳しく解説する。ここではまず、定着率を割り出す基本的な考えを見ていこう。

社員定着率を出すためには、以下の様にまず数値のメモをとることが必要だ。

2014年度4月に120人を採用したケース:2014年採用人数を記録する。

4月在籍数 その年度での離職者数 累積離職者数
2014年度 120人 30人 30人
2015年度 90人 20人 50人
2016年度 70人 10人 60人
2017年度 60人

ここから、【各セル数値÷最初に採用した100人(×100)】という計算をすれば、各数値を出すことが出来る。

1年以内定着率なら2015年、2年以内定着率は2016年、3年以内定着率なら2017年と、グレーでマークされた数値を使えば、それぞれの年度ごとの社員定着率を算出することが可能だ。

社員定着率の計算 使う数字 計算式 社員定着率
1年以内社員定着率 2015年在籍数 90÷120×100 75%
2年以内社員定着率 2016年在籍数 70÷120×100 58.3%
3年以内社員定着率 2017年在籍数 60÷120×100 50%

1-2. 社員定着率と離職率を足せば採用人数合計になる

定着人数を出せれば、離職人数も出すことが可能になる。この2つの数値自体に差は全くなく、社員定着率と離職率は対となる概念であり、どちらかさえ出せれば、引き算をするだけで、もう1つの数値を出すことが出来る。

離職人数を抑えたいと考えるのか?社員定着率を高めて人材の質を向上させようと考えるのか?という、どちらの数値の方を社員に意識させたいのか?

しかし、その言葉の響きが部下に与える影響は全く違う。

「社員の定着率があがってきているぞ!!」と前向きな指示をしたい方は定着率に注目させよう!
「離職が止まらず人材が育っていない!!」と危機感を感じさせたい場合は離職率を注目させよう!

それぞれ部下に発するメッセージとして活用したい方で計算するようにしてほしい。

・社員定着率を先に計算した時:100%-社員定着率=離職率
・離職率を先に計算した時:100%-離職率=社員定着率

上記のケースであれば、定着率を先に出しているので、離職率は以下の様に算出できる。

離職率の計算 使う数字 計算式 離職率
1年以内離職率 1年以内社員定着率 100-75 25%
2年以内離職率 2年以内社員定着率 100-58.3 41.7%
3年以内離職率 3年以内社員定着率 100-50 50%

ここまでが手計算で社員定着率・離職率を計算する方法だ。しかし、実際、いちいちエクセルで手動入力計算すると、非常に手間がかかる。

そこで、当記事では、従業員の名簿管理で入社日と退社日さえ入力していれば、エクセルに自動計算させ、会議や打ち合わせで必要な時にプリントアウトするだけで良くなる方法についてお伝えする。

これからご紹介するエクセルで社員定着率を自動で計算する方法に関しては、エクセルが苦手とする方には、少し難しい内容であるかもしれません。
しかし、Rableでは、もし要望があれば、関数入力済みのエクセルを無料で提供する予定をしていますので、離職率・社員定着率計算エクセルをご要望の方は気軽にお問い合わせをしてください。

2.社員定着率を計算するために入力する必要があるものはたったの3つ

氏名 雇用日 退職日
スタッフ529 2017年7月
スタッフ528 2017年6月
スタッフ527 2017年4月
スタッフ526 2017年4月
スタッフ525 2017年3月 2017年7月
スタッフ524 2017年3月 2017年5月
スタッフ523 2017年3月 2017年4月
スタッフ522 2017年3月 2017年6月
スタッフ521 2017年3月
スタッフ520 2017年2月
スタッフ519 2017年2月
スタッフ518 2017年2月 2017年9月
スタッフ517 2016年11月 2016年11月

ではまずこのようなシートを用意してほしい。とはいっても、それほど難しいものではない。

このように、氏名・入社日・退社日の3つを入力すればいいだけだ。

在籍中の社員は、退職日の列は空欄でOKだ。

3.社員定着率を自動で計算させるために必要な3つの準備

ではこれから自動で社員定着率を計算するエクセルの計算式を作成していこう。

3.1 現在在籍中かどうかを判断する作業列を作ろう

離職率・定着率を算出するためには、「まだ在籍中なのか?それとも辞めてしまったのか?」を判断する必要がある。

そこで、以下の画像にある黄色のセルを参考にしながら、在籍中の方は【1】辞めてしまった人は【0】と入力させる関数を作成しよう。

作成する関数:=IF(退職日=””,1,0)
式の作り方としては、従業員名簿の退職日の列を参照とし、退職日が空欄なら1、退職日が入力されていれば0を表示させる。

3.2 辞めた人の勤続・勤務年数を計算しよう

次に自社の平均勤続年数を計算するための準備に入ろう。この作業は大きく3つに分けられる。

  • すでに辞めてしまった社員の勤続年数を出すための計算
  • 現在も在籍している社員の勤続年数を出すための計算
  • 上記2つの式を分岐させる関数の作成
3.2.1 すでに辞めてしまった人の勤続年数を出す

辞めてしまった人の勤続年数を出すのは比較的簡単だ。なぜなら、入社日と退社日がすでに入力されているからだ。

2つの日付を比べるためには【DATEDIF】関数を使う。

=DATEDIF(入社日,退社日,”m”)

という式を作れば、何ヶ月勤務したか?ということを出すことが出来る。
年単位で勤続年数を出したければ、=DATEDIF(入社日,退社日,”m”)/12)というようにただ12カ月で割り算しよう。

3.2.2 現在も勤続中の社員の勤続年数を出す方法

現在も勤続中の社員を出すことは少々式が複雑となる。なぜなら、退職日の欄が空欄であるからだ。そのため、別のセルを参照させてあげるという工程を踏む必要がある。

以下の様に、月単位で良いので、現在の日付を入力するセルを作成しよう。

現在の日付を入力する関数は =TODAY() という関数がある。

後はそのセルを参照にして、以下の関数を作ろう。

=IF(退職日,”,DATEDIF(退職日,現在の日付,”m”)/12
この式は、もし退職日の列が空欄ならば、現在の日付を使って、今までの勤続年数を年単位で出してね。というものだ。

3.2.3 IF式を使って関数の分岐をさせよう

しかし、エクセルは同じセルに2つの式を入力することはできない。少し複雑になるが、以下のような関数を作成し、2種類のパターンに分岐させる必要がある。

=IF(退職日=””,DATEDIF(雇用日,現在の日付,”m”)/12,DATEDIF(雇用日,退職日,”m”)/12))

この式はまず退職の有無をまず確認し、現在も勤務している社員は、当たり前だが退職日が入力されていない。そこで退職日のセルが空白なら②(当3.2.2節)の関数を使え、退職セルが入力されているなら、①(当3.2.1節)の関数を使え。という分岐式だ。

※実際にセルに入力するのは上記の式だけなので、良くわからない人は、セルに上記の式をコピペし、セル番号だけカスタムしてもらえば上手くいくはずだ。

この作業が終われば、勤務年数の列には、全社員の勤続年数が表示されているはずだ。ここまでの作業でやっと全社員の勤続年数を出すことが出来た。

あとはいよいよ、年度ごとの定着率・離職率を計算する段階へと移る。

4.離職率を算出するための計算式を作ろう

では続けて、離職率・定着率を計算するための関数を作成していこう。

4.1 自社で集計したい定着率・離職率の期間を設定する

離職率・定着率を出すためには、その計算の元となる期間の設定が必要となる。そのため、自社の離職状況に合わせて、離職期間を設定しよう。以下は一般的な範囲で離職期間を設定した例だ。

階級値 データラベル
0.00年 0.08年 1ヶ月
0.09年 0.24年 3ヵ月
0.25年 0.50年 半年
0.51年 1.00年 1年
1.01年 1.50年 1年半
1.51年 2.00年 2年
2.01年 3.00年 3年
3.01年 5.00年 5年
5.01年 20.00年 5年以上
上記の表の階級値は、前章で作成した勤続年数の数値を基準にしているので、1ヶ月なら【1ヶ月÷12か月】で、年単位で計算している。データのサイズが異なると上手く計算できなくなるので、数値の単位換算が苦手な人は、上記の表と同じ形式で作成することをおススメする。

4.2 それぞれの離職期間を算出する

では実際にそれぞれの離職期間で辞めた社員の数をカウントしていこう。

1ヶ月内に辞めた社員の数をカウントする場合

最低値 最高値 ラベル
0.00年 0.08年 1ヶ月

上記の例であれば、以下の関数を入力すれば人数を算出できる。
=COUNTIFS(勤続年数の列,”>=”&最低値セル,勤続年数の列,”<“&最高値)

上記の関数をそれぞれの期間で行い、以下の表を完成させよう。

階級値 データラベル 合計
1. 0.00年 0.08年 1ヶ月 41人
2. 0.09年 0.24年 3ヵ月 42人
3. 0.25年 0.50年 半年 89人
4. 0.51年 1.00年 1年 63人
5. 1.01年 1.50年 1年半 63人
6. 1.51年 2.00年 2年 47人
7. 2.01年 3.00年 3年 62人
8. 3.01年 5.00年 5年 53人
9. 5.01年 20.00年 5年以上 48人

黄色のセルが計算式で表示させる部分だ。

ここまで作成できれば、【データラベル】と【合計】の列を参照にグラフ化すれば、以下のような離職率の動向をビジュアル化できる。
働き始めてから、どの期間の離職人数が多いのか?ボリュームを確認できるグラフ

上記のグラフを見れば、入社後半年から1年未満の離職者が多いため、なんらかの対策を測ることが必要だ。と誰もが判断できるはずだ。

5.直近の人材定着率を算出するための計算式

離職率が出せれば、最後はいよいよ直近の年度の人材定着率を算出するための計算式に入ろう。長かった作業もこれでやっと終わりだ。

5.1 直近3か年で対象となる社員を特定しよう

まず具体的な計算式に入る前に、どの期間に入社した社員を対象として、社員定着率を出すのか指定しなければいけないため、以下のような参照セルを作成しよう。

そしてそのセルを参照にして、以下の関数を作成しよう。

作成する関数:=IF(入社日>3年前の日付,1,0)
この式は、入社日が3年前の日付より大きければ(新しければ)、1を3年前にすでに入社している人は0、つまり計算に含まない。という計算対象を仕分けするために使う。

上記画像の黄色のセル部分を見ていただきたい。表記が0と1に分けられていることが分かるだろう。そのセルを参照にして以下の関数を作成しよう。

※7/19 以下の式が書いていませんでしたので、追加しておきます。

1行目から8行目までは、以下の式で作成してください。

=COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<“&最高値)

最後の列だけ、最高値と最低値を両方含む必要性があります。

=COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<=”&最高値)

この式の意味は、3年以内に入社した人で、現在は勤務していない人が対象、そして計算内容は先ほど行った計算と同様に、以下の各期間で辞めた人の数をカウントしてくれ。というものだ。

最低値 最高値 ラベル 直近3年
0.00年 0.08年 1ヶ月 4人

この計算を繰り返せば、以下の表が完成する。

階級値 データラベル 直近3年
1. 0.00年 0.08年 1ヶ月 4人
2. 0.09年 0.24年 3ヵ月 4人
3. 0.25年 0.50年 半年 6人
4. 0.51年 1.00年 1年 1人
5. 1.01年 1.50年 1年半 1人
6. 1.51年 2.00年 2年 2人
7. 2.01年 3.00年 3年 1人
8. 3.01年 5.00年 5年 0人
9. 5.01年 20.00年 5年以上 0人

5.2 直近3か年で辞めなかった人数を算出しよう

続けて、直近3年以内に入社し、なおかつ現在も勤続中の人を出そう。それは以下の式を作ればいい。

=COUNTIFS(3年以内有無,1,勤続有無,1)
式自体は非常に簡単だ。3年以内に入社し、現在も勤続中の人をカウントしろ。たったそれだけだ。

ここまでできれば、以下の表が完成しているはずだ。

これらの表を基にグラフ化すれば、以下のようなものが出来上がる。
離職率の推移

上記のグラフを見れば、直近では採用1ヶ月は改善が図れているが、3ヶ月・半年という期間での離職が目立つことが伺える。

直近3年以内における人材定着・離職者の内訳

上記のグラフは離職者合計の部分を円グラフで出したものだ。円グラフで作成すれば、自動的に%で出力してくれる。

このように、データを表からグラフに変えることで、誰もが自社の問題となっている部分を把握することが可能となる。

まとめ:関数さえ完成すればあとは入社日・退社日さえ入力するだけ

ここまで非常に多くの作業が必要であったが、関数さえ入力すれば、あとはもう何もする必要はない。アルバイトや事務作業員に退社日記録をさせるだけで、自動で計算してくれる。

後は、自動で作成される表を会議資料にまとめるなり、スタッフに対して、離職率の課題を意識させるなり、離職削減目標を掲げるなりして活用すればいい。

退社日さえ入力すれば、表やグラフは自動で書き変わるので、あなたが今後やる必要はなく、誰でも出来る業務となるはずだ。

社内で取り組む目標を共有するためには、このような経営数値を継続して記録していくことが重要だ。

今回の記事で紹介したエクセルならば、資金に余裕がない中小企業でも十分に実行可能だろう。コストをかけずに、効率化を図るためには、そういった賢い経営を目指していこう。

  • このエントリーをはてなブックマークに追加
  • LINEで送る

コメント

  1. yamamoto より:

    =COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<”&最高値)

    この式の意味は、3年以内に入社した人で、現在は勤務していない人が対象、そして計算内容は先ほど行った計算と同様に、以下の各期間で辞めた人の数をカウントしてくれ。というものだ。

    この部分の計算式では算出されないメンバーがいるようです。
    この式に問題がありますか?

    1. hashimoto より:

      コメントありがとうございます。

      もし差し支えなければ、どのようなメンバーさんがカウントされていないかわからないでしょうか?

      推測になりますが、以下の内、どれかに該当しているかもしれません。
      1.作業列の3年以内に入社している方に0がついていたり、
      2.退職列が空白であったりして、現在在職が1になっている。
      3.入社日と退社日に入力ミスがあり、勤続年数が正確に反映されていない。
      4.不等式(>や<の部分)にエラーがあり、漏れが発生している。

      関数と言うよりかは、計算に用いている作業列に問題があるかもしれないので、作業列で何かエラーが出ていないか見てみてはいかがでしょうか?

  2. りゅう より:

    =COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<”&最高値)

    の最低値と最高値が逆だと思いますが、どうなんでしょうか?

    1. 浅田雅也 より:

      コメントありがとうございます。
      記載頂いたことに関して、元々の原稿では最小値を含むだけで、最大値を含んでいない式でした。
      修正として、最小値の範囲を含む式と最大値の範囲含む式の2つを加筆しました。
      ご指摘ありがとうございます。

      =COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”< ”&最高値) 一番下のセルには、最低値と最高値を含む必要があるため、以下の式が追加で必要となります。 =COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<=”&最高値)

  3. 河野 より:

    =COUNTIFS(3年以内有無,1,在籍有無,0,勤務年数,”>=”&最低値,勤務年数,”<“&最高値)

    の説明で、1行目から8行目のところがどこのことを言っているのかがわかりません。
    最後の列もわかりません。

    どの部分をおしゃっているのでしょうか?

    1. 浅田雅也 より:

      コメントありがとうございます。
      少しわかりづらくなってしまったでしょうか?

      該当の計算式は、階級値として期間を区切った後、その中で在籍しているか?それとも、在籍していないか?ということを値として返す式となっています。

      そのため、該当の計算式の下に表示している表がありますが、その一番右側のセルに入力していただく式となっています。

      そうすると、雇用してから(0.00年)から0.08年の期間には41名の社員がいますが、その中で4人だけが0.08年の間に離職したことがわかる。という内容となります。

      また、当記事の下部に無料EXCELテンプレートもご用意しております。

      ご自身でEXCELを作成するのが難しいと感じられた場合は、こちらのEXCELをダウンロードしてください。

コメントを残す

*