離職率

離職率や定着率・退職率を自動で計算してくれるエクセルの作成方法

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

このページでは、自分たちの企業の離職率データの計算式とその計算式をエクセルで自動化するための手順を詳しく解説していく。

離職率や定着率・退職率の計算が面倒な理由は、新卒一括採用ならともかく中途採用やアルバイト採用は入社日がそれぞれ異なり、計算期間をある特定の日に固定して考えられないからだ。

例えば、今年5月に雇ったアルバイトが来年の3月に辞めたり、今年1月に雇ったアルバイトは3月に辞めるなど、入社日と退社日がバラバラになってしまう。すると、うちの会社のアルバイトの定着率や離職率は・・・と、計算がややこしくて困ってしまう。

本来は、「わが社はアルバイトの1年定着率は72%で3年定着率は48%となっている。」というデータを出すことが出来れば、現状の人材マネジメントの課題や、離職率改善のための目標設定など、会議でスムーズに決定されていくはずだ。

そこで、本日の記事では、以下のように入社日と退社日とアルバイトの名前を入力するだけで、簡単に定着率と離職率を自動で計算してくれるエクセルシートの作成手順をお伝えしていこう。

当ページとと併せて是非読んでほしい記事

以下の記事では離職率という数字が近年注目を集めはじめてきているのか?に関して、詳しく解説している。「離職率を計算したもののその数値をどう扱っていいのか?」、「離職率は別に低くても構わないのではないか?」、「どのくらいの離職率を目指すべきなのか?」といった内容となっている。離職率の計算に入る前の前提知識に当たるので是非軽く目を通してみて欲しい。

1. 定着率と離職率の違いとエクセルでの計算方法

離職率と定着率は表裏一体の考え方といえる。

【入社人数=離職人数+定着人数】で表せるからだ。つまり、この方程式を加工すれば、以下のように定義される。

    • 離職人数=入社人数―定着人数
    • 定着人数=入社人数―離職人数

定着率か離職率、どちらの言葉を選ぶかは完全にあなたの好みで選んでもらっていい。ただグラフ化したときに若干意味は変わる。

定着率であれば以下のようなグラフとなる。

上記のグラフは職種別に採用した社員が期間後にどれだけ残っているか?を表しており、採用投資の残存効果を視覚的に理解しやすい。

一方累積離職率であれば以下のようになる。

離職率グラフは離職者がどれだけ増加しているかを示していて、人材の流出がどれだけ発生しているのか?をビジュアル化することに向いている。

意味に違いはないので、好きなほうを選んでほしい。

では具体的にエクセルでの計算式についてみていこう。

1-1.社員定着率の基本的な計算方法をマスターしよう。

ではここから一緒に具体的な事例を見ながら定着率・離職率の計算方法をマスターしていこう。2014年度から2016年度間における社員定着率の3年以内それぞれの社員定着率の事例で考えて見よう。

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

社員定着率を出すためには、2014年4月に採用した人数を集計し、1年以内、2年以内、3年以内に離職した社員の人数をカウントする。それを集計したものが以下の表となる。

4月在籍数 該当年度の離職者数 累積離職者数
2014年度 120人 30人 30人
2015年度 90人 (120人-30人) 20人 50人 (30人+20人)
2016年度 70人 (90人-20人) 10人 60人 (50人+10人)
2017年度 60人 (70人-10人)

上記の表に記載された数値を使って【各セル数値÷最初に採用した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%

ここまでが手計算で社員定着率・離職率の計算式に対する基礎的な知識となる。

しかし、実際、いちいちエクセルで手動計算したり、毎回事務社員に作業依頼してまで離職率を把握することに時間的・人員的な手間を感じる人は多いはずだ。

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

2. 離職率計算式をエクセルに埋め込み自動化する4つの手順

2.1 離職率を調べる為に必要な入力データはたったの3つ

離職率データを自動管理する為には以下の3つのデータがそろってさえいればいい。社員名・住所・給与データなどを記録しているデータベースをお持ちになっておられる企業であれば、そこに雇用日と退職日の2列を追加して貰えればOKだ。

これからエクセルで人員管理しようと考えておられる方は、保管している履歴書を集め、氏名・雇用日・退職日の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月

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

2.2 累積勤務期間を算出する関数を作成しよう

ではここから離職率を自動で計算する為の関数作成に入ろう。

冒頭でも述べたが、ほとんどの中小企業では春採用の他に中途採用もしているし、アルバイトがメインの業種では尚更入社時期がバラバラになりやすい。

そこで以下の2つの分岐をしなければならない。

  1. 離職していない社員であれば、入社日から現在までの累積勤務日数
  2. 離職した社員であれば、入社日から退職日までの累積勤務日数

2つの日付を比べるためには【DATEDIF】関数を使う。以下の式を使えば、退社日-入社日の日数を自動で計算してくれるようになる。

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

注意※年単位で勤続年数を出したければ、DATEDIF(入社日,退社日,”m”)/12)というようにただ12カ月で割り算しよう。”m”の部分を”y”にしてしまうと少数部分が0になってしまうので上記の計算式を使ってほしい。

しかし、上記のままでは在籍社員は退職日のセルが空白になっているのでエラーが出てしまう。そこで以下の様にIF式で分岐させてあげる必要がある。today()関数は今日の日付を表記する為の関数だ。

=IF(退職日=””,DATEDIF(雇用日セル,today(),”m”)/12,DATEDIF(雇用日セル,退職日セル,”m”)/12))

上記の式を作成し、それを下までオートフィルすれば下の画像のC列勤務年数(月数)が計算されているはずだ。

2.3 エクセルに離職者をカウントさせるための作業列を作成しよう

ここまでで入社日・退職日・勤務日数という離職率の計算に必要なパーツがすべてそろった。ここからエクセルにそれぞれの期間の離職率を出すための手順に移っていこう。

例えば、1年以内離職率を計算したいとき、1年以内離職者に該当するのか?しないのか?の判定をさせる必要がある。そこで以下の様な式を作成しよう。

1年以内有無 2年以内有無 3年以内有無
作成する関数 if(勤務年数>1,0,1) if(勤務年数>2,0,1) if(勤務年数>3,0,1)

上記の式は、それぞれの年度で該当者であれば1をそうでなければ0をカウントしろ。というものだ。それは上記の表のD列とE列に該当する。下までオートフィルした時に1年以内に離職していれば1、離職していなければ0といったようになっていればOKだ。

2.4 0と1で表記されたセルを集計して期間別離職人数グラフを作成しよう。

ここまで作成できた皆様のエクセルは、1年以内に離職社員は1、そうでない社員は0とそれぞれラベルが割り振られているはずだ。あとはsum関数を使って合計を出すだけだ。そうすればそれぞれの期間の離職人数を算出できる。

離職率で綺麗なグラフ・表を作るためには、細かな期間別の離職率を計算していく必要がある。

以下は一般的な範囲で離職期間を設定した例だ。

階級値 データラベル
最低値 最高値
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か月=0.08】で、年単位で計算している。データのサイズが異なると上手く計算できなくなるので、数値の単位換算が苦手な人は、上記の表と同じ形式で作成することをおススメする。
あとは、上記でやった内容を少し加工して作業列を作成しよう。

=if(and(勤続年数のセル,>=最低値セル,勤続年数の列,<=最高値),1,0)

上記の関数は、それぞれの期間に該当すれば1、違うければ0を算出するためのものだ。あとはそれぞれの行をSUMで合計すれば以下の表が完成する。

階級値 データラベル 合計
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人

黄色のセルがグラフで表示する部分となり、以下のような期間別に離職発生の動向をビジュアル化できる。

働き始めてから、どの期間の離職人数が多いのか?ボリュームを確認できるグラフ

3. 離職率を計算する上で作成したいおすすめ4ビジュアル

さてここからは離職率を出したが、どの様にデータ加工すれば離職率を綺麗にビジュアライズしたり、有用な情報が得られるのか?に関するおすすめの表やグラフを紹介しよう。

離職率データを活用した自社分析や人材マネジメントへの活用に関する全体像は以下の記事で紹介している。

是非、参考にして頂き、数値を計算するだけで満足せず、自社の利益改善にまでつなげてほしい。

3.1 人材のインとアウトの流動性を整理した”人材フロー”デザイン

入社日と退社日が入力されていれば各年度ごとに何人が入社し、何人が退社した結果、人材は増えているのか減っているのか?と言う年度ごとの人材の増減を計算することもできる。また人数の増減と採用コストの増加がつながっていないか?採用費全体が高騰していないか?ということへの理解に役立つ。

3.2 離職率と採用効率をかけあわせた”採用投資視覚化”デザイン

離職と採用費は切っても切れない関係だ。せっかく採用しても離職されればそのコストは無駄になるし、次年度以降も離職を見越して必要以上の採用人数を予定しなければいけなくなり、そのことはコスト増加に直結する。以下のグラフは、投資した採用費の内、離職した社員にかけた採用ロス金額を算出している。

3.3 複数の年度を追った”経年変化比較”デザイン

以下の表は離職率でスタンダードと言えるもので、定着率や離職率を年度別に比較し、改善されているのか?悪化しているのか?という成果を比較することに役立つ。

3.4 特定の人物の離職だけに絞った”離職者の品質チェック”デザイン

他の記事でも伝えてはいるが、私たちは離職自体は悪い事だとは考えていない。会社に貢献してくれない人材は辞めてもらって構わないが、優秀な人材は辞めてもらっては困る。という良質な離職の内訳を得ることが大切だ。以下のグラフは、離職者の内訳別に、辞めて欲しくない人の離職率・辞めても構わない人の離職に整理したものとなる。

上記で紹介した表だけでなく、採用費や人材育成、人材の質と連動させた全15個のグラフを自動で排出するリテンションマネジメントテンプレートエクセルを現在無料で公開中です。何百社の方々に利用して貰っている自信作なので是非ご活用してみてください。ダウンロードの手間は少々ありますが、こちらのページで無料購入ボタンを押すとダウンロードが可能になっております。

まとめ:離職率自動化エクセルは人材管理ツール作成の第一歩

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

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

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

そこから、社員ごとの売上データや給与など記録するデータを増やしていけば、あなたの会社だけの人材管理ツールが少しずつできあがっていく。離職率管理はその第一歩だ。

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

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

是非、すぐにでも取り組んでみてはいかがだろうか?

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

ES調査機能付き!離職率計算EXCEL

RABLE経営研究所では、離職率の計算をしてくれる人材管理EXCELシートをプレゼントしています。

サンプルEXCELブック30ページの解説PDF無料ダウンロードしていただけます。

この資料を見れば、

  1. なぜ、早期離職率を改善しなければいけないのか?

  2. なぜ、売上目標ではモチベーションが上がらないのか?

  3. なぜ、人材育成に取り組んでも業績が向上しないのか?


これらの課題を解決するための、全ての手順を掲載しています。

現在29800円で販売しているEXCELテンプレートを無料で手に入れることが出来ます。

今すぐダウンロード

コメント

  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をダウンロードしてください。

コメントを残す

*