まずは基本から：離職率の計算式と部署別分析でわかること
離職率とは、一定期間にどれくらいの社員が会社を辞めたのかを示す指標です。人事や経営層だけでなく、現場で働くビジネスパーソンにとっても、組織の状態を知るうえで重要な数字です。特に部署別に離職率を分析すると、「どの部署で人が辞めやすいのか」「特定の部署だけ問題が起きていないか」といった傾向を把握しやすくなります。
一般的な離職率の計算式は、次のとおりです。
離職率 ＝ 期間中の離職者数 ÷ 期間中の平均社員数 × 100
たとえば、ある部署で1年間に3人が退職し、その期間の平均社員数が30人だった場合、離職率は以下のように計算できます。
3 ÷ 30 × 100 ＝ 10％
つまり、この部署では1年間で社員の10％が離職したことになります。Excelで分析する場合も、基本的にはこの考え方を使います。離職者数と社員数を部署ごとに集計し、計算式に当てはめるだけで、部署別の離職率を算出できます。
ここで注意したいのが、「社員数」をどの時点で見るかです。単純に期末時点の社員数だけを使うと、途中で入社や異動が多かった部署では実態とズレることがあります。そのため、より正確に見るなら、期首社員数と期末社員数の平均を使う方法がよく使われます。
平均社員数 ＝（期首社員数 ＋ 期末社員数）÷ 2
部署別に離職率を見るメリットは、会社全体の平均だけでは見えない課題を発見できる点です。たとえば会社全体の離職率が8％だったとしても、営業部が15％、開発部が3％であれば、営業部に何らかの課題がある可能性があります。業務量、人間関係、評価制度、マネジメント、残業時間など、原因を深掘りするきっかけになります。
また、離職率は「高いから悪い」「低いから良い」と単純に判断するものではありません。新卒社員が多い部署、成果主義が強い部署、繁忙期が偏る部署など、部署ごとの特性によって数字の意味は変わります。大切なのは、部署ごとの離職率を同じ条件で比較し、前年や前月との変化もあわせて確認することです。
Excelを使えば、部署別の離職率を手作業で計算するだけでなく、関数やピボットテーブルを使って効率よく集計できます。まずは離職率の基本式を理解し、「部署ごとの違いを見ることで、組織のどんな課題が見えるのか」を押さえておきましょう。
分析に必要なデータを整理する：社員名簿・入退社日・部署情報の準備
部署別の離職率をExcelで正しく分析するには、最初のデータ整理がとても重要です。計算式やピボットテーブルを使う前に、社員ごとの情報がバラバラになっていると、集計ミスや二重カウントの原因になります。まずは、分析に使うデータを1つの表にまとめましょう。
最低限、用意しておきたい項目は以下のとおりです。
- 社員番号
- 氏名
- 部署名
- 入社日
- 退職日
- 在籍ステータス
- 雇用区分
特に重要なのは、社員番号・部署名・入社日・退職日です。氏名だけで管理すると、同姓同名の社員がいた場合に判別しづらくなります。そのため、Excel上では社員番号をキーにして管理するのがおすすめです。
表のイメージは次のようになります。
| 社員番号 | 氏名 | 部署名 | 入社日 | 退職日 | ステータス |
|---|---|---|---|---|---|
| 1001 | 山田太郎 | 営業部 | 2021/04/01 | 在籍 | |
| 1002 | 佐藤花子 | 開発部 | 2020/07/01 | 2024/03/31 | 退職 |
退職していない社員の退職日は空欄で問題ありません。ただし、「-」や「なし」などの文字を入れると、Excelで日付として扱いづらくなるため注意しましょう。日付項目はすべてyyyy/mm/dd形式にそろえておくと、後でCOUNTIFS関数を使うときにスムーズです。
また、部署名の表記ゆれにも気をつけましょう。たとえば「営業部」「営業」「営業チーム」が混在していると、Excelでは別の部署として集計されてしまいます。分析前に部署名の一覧を確認し、正式名称に統一しておくことが大切です。
分析期間も先に決めておきましょう。たとえば「2024年4月1日〜2025年3月31日」のように期間を明確にしておくと、その期間中に退職した人だけを正しく抽出できます。入社日と退職日を使えば、「期首に在籍していた社員」「期末に在籍していた社員」「期間中に退職した社員」を判定できるようになります。
さらに、部署異動がある会社では、どの部署でカウントするかのルールも決めておく必要があります。一般的には、退職時点の部署で離職者を集計する方法がわかりやすいです。一方で、期首時点の所属部署で見る方法もあるため、社内で比較する場合は基準を統一しましょう。
このように、Excel分析の精度は元データの整い方で大きく変わります。関数やグラフ作成に進む前に、社員名簿・入退社日・部署情報を見直し、誰が見ても同じ条件で集計できる状態にしておきましょう。
Excelで部署別の離職率を集計する：COUNTIFS関数とピボットテーブルの使い方
データの準備ができたら、いよいよExcelで部署別の離職率を集計していきます。方法は大きく分けて、COUNTIFS関数で集計する方法と、ピボットテーブルで集計する方法の2つです。少人数のデータならCOUNTIFS、大量データを扱うならピボットテーブルが便利です。
まずは、分析期間をExcel上に設定しておきましょう。たとえば以下のように入力します。
| 分析開始日 | 2024/04/01 |
|---|---|
| 分析終了日 | 2025/03/31 |
ここでは、社員データが以下の列で管理されているとします。
- A列：社員番号
- B列：氏名
- C列：部署名
- D列：入社日
- E列：退職日
- F列：ステータス
次に、別シートまたは同じシートの右側に部署別の集計表を作ります。
| 部署名 | 期間中の離職者数 | 期首社員数 | 期末社員数 | 平均社員数 | 離職率 |
|---|---|---|---|---|---|
| 営業部 |
期間中の離職者数は、COUNTIFS関数で集計できます。たとえば部署名がH2セル、分析開始日がK1セル、分析終了日がK2セルにある場合、次のように入力します。
=COUNTIFS($C:$C,H2,$E:$E,">="&$K$1,$E:$E,"<="&$K$2)
この式は、「部署名がH2と一致し、退職日が分析期間内にある社員」を数えています。部署ごとに式をコピーすれば、各部署の離職者数を簡単に出せます。
次に、期首社員数を求めます。期首時点で在籍している社員は、「入社日が分析開始日以前」で、かつ「退職日が空欄、または分析開始日以降」の人です。COUNTIFSでは“または”条件を1つの式で扱いにくいため、2つの式を足し合わせます。
=COUNTIFS($C:$C,H2,$D:$D,"<="&$K$1,$E:$E,"") + COUNTIFS($C:$C,H2,$D:$D,"<="&$K$1,$E:$E,">="&$K$1)
期末社員数も考え方は同じです。分析終了日時点で在籍している社員を数えます。
=COUNTIFS($C:$C,H2,$D:$D,"<="&$K$2,$E:$E,"") + COUNTIFS($C:$C,H2,$D:$D,"<="&$K$2,$E:$E,">="&$K$2)
期首社員数と期末社員数が出たら、平均社員数を計算します。
=(期首社員数+期末社員数)/2
最後に、離職率を計算します。
=期間中の離職者数/平均社員数
セルの表示形式を「パーセンテージ」にすれば、部署別の離職率が見やすくなります。平均社員数が0になる部署がある場合は、エラーを防ぐためにIFERROR関数を使うと安心です。
=IFERROR(期間中の離職者数/平均社員数,0)
一方、社員数が多い会社では、ピボットテーブルを使うと集計が楽になります。おすすめは、元データに以下のような補助列を追加する方法です。
- 期間内退職：条件に合えば1、合わなければ0
- 期首在籍：条件に合えば1、合わなければ0
- 期末在籍：条件に合えば1、合わなければ0
たとえば「期間内退職」列には、次のような式を入れます。
=IF(AND(E2>=$K$1,E2<=$K$2),1,0)
補助列を作ったら、データ範囲を選択して「挿入」→「ピボットテーブル」をクリックします。行に「部署名」、値に「期間内退職」「期首在籍」「期末在籍」を入れ、それぞれ集計方法を「合計」にします。これで部署別の離職者数、期首社員数、期末社員数を一気に集計できます。
ピボットテーブルで集計した後は、横に「平均社員数」と「離職率」の列を追加して計算すれば完成です。毎月データを更新する場合も、元データを追加してピボットテーブルを更新するだけなので、手作業のミスを減らせます。
COUNTIFS関数は計算の中身を確認しやすく、ピボットテーブルは大量データを素早く集計できるのが強みです。まずはCOUNTIFSで仕組みを理解し、運用に慣れてきたらピボットテーブルで効率化する流れがおすすめです。
グラフで見える化する：部署ごとの離職傾向をわかりやすく比較する方法
部署別の離職率を集計できたら、次はグラフで見える化しましょう。数字の表だけでも分析はできますが、部署ごとの差や離職率の高低は、グラフにしたほうが直感的に伝わります。特に上司や人事担当者に共有する場合は、ひと目で傾向がわかる形にすることが大切です。
まずおすすめなのは、部署別の離職率を棒グラフで比較する方法です。前章で作成した集計表から、「部署名」と「離職率」の列を選択し、Excelのメニューから「挿入」→「縦棒グラフ」または「横棒グラフ」を選びます。部署数が少ない場合は縦棒グラフ、部署数が多い場合は横棒グラフにすると見やすくなります。
グラフを作成したら、離職率が高い順に並べ替えるのがおすすめです。部署名がバラバラに並んでいると、どこに課題がありそうか判断しづらくなります。集計表の離職率列を降順に並べ替えるだけで、グラフも連動して「離職率が高い部署順」に表示されます。
さらに見やすくするには、以下の設定を加えると効果的です。
- グラフタイトルを「部署別 離職率比較」など具体的にする
- 縦軸または横軸をパーセント表示にする
- データラベルを表示して、各部署の離職率を数字で見せる
- 離職率が高い部署だけ色を変える
たとえば、全社平均の離職率が8％で、営業部が15％、カスタマーサポート部が12％、開発部が4％だった場合、営業部とカスタマーサポート部の棒だけ赤やオレンジにすると、注意すべき部署がすぐにわかります。単にグラフを作るだけでなく、見る人が判断しやすいデザインにすることがポイントです。
また、部署ごとの離職率だけでなく、離職者数も一緒に確認することが重要です。社員数が少ない部署では、1人退職しただけでも離職率が大きく上がることがあります。たとえば5人の部署で1人退職すると離職率は20％ですが、50人の部署で5人退職した場合は10％です。率だけを見ると前者のほうが深刻に見えますが、組織への影響人数では後者も無視できません。
そこで、より丁寧に見せたい場合は、離職率と離職者数を組み合わせた複合グラフも便利です。「部署名」「離職率」「離職者数」を選択し、Excelの「挿入」→「複合グラフ」を使います。離職率を折れ線、離職者数を棒グラフにすると、「割合」と「人数」の両方を同時に確認できます。
月別や四半期別のデータがある場合は、部署ごとの離職率推移を折れ線グラフにするのも有効です。単年度の比較では見えない「最近急に悪化している部署」や「改善傾向にある部署」を把握できます。特に、組織変更や上司の交代、繁忙期のあとに離職率が上がっていないかを見ると、原因分析につなげやすくなります。
グラフ作成で気をつけたいのは、見た目を複雑にしすぎないことです。色を使いすぎたり、3Dグラフにしたりすると、かえって重要なポイントが伝わりにくくなります。基本はシンプルに、強調したい部署だけ色を変える程度で十分です。
Excelのグラフは、分析結果を「読むもの」から「見て理解するもの」に変えてくれます。部署別の離職率を棒グラフで比較し、必要に応じて離職者数や時系列の推移も加えることで、次の原因分析に進みやすい資料になります。
分析結果を活かす：離職率が高い部署の原因を読み解くポイント
Excelで部署別の離職率を集計し、グラフで見える化できたら、最後に大切なのは「なぜその部署の離職率が高いのか」を読み解くことです。数字を出して終わりではなく、原因を仮説立てし、改善アクションにつなげてこそ分析の意味があります。
まず確認したいのは、離職率だけで判断しないことです。前章でも触れたように、社員数が少ない部署では1人の退職でも離職率が大きく跳ね上がります。そのため、離職率が高い部署を見つけたら、必ず「離職者数」「部署の人数」「前年との比較」もあわせて確認しましょう。
- 離職率は高いが、退職者は1人だけなのか
- 前年と比べて急に悪化しているのか
- 毎年同じ部署で離職率が高いのか
- 全社平均や同じ職種の部署と比べて高いのか
次に見るべきポイントは、退職者の属性です。たとえば同じ営業部でも、新卒入社の若手ばかりが辞めているのか、中堅社員が辞めているのか、管理職層が辞めているのかで、考えられる原因は変わります。Excel上で「年齢」「勤続年数」「役職」「雇用区分」などの項目も管理している場合は、あわせて集計してみましょう。
特に20代・入社3年以内の離職が多い場合は、オンボーディングや教育体制、配属後のフォローに課題があるかもしれません。一方で、中堅社員の退職が多い場合は、評価制度、キャリアパス、業務負荷、上司との関係などが原因になっている可能性があります。
また、退職のタイミングにも注目しましょう。繁忙期の直後に退職が集中しているなら、残業時間や業務量に問題があるかもしれません。上司の交代や組織変更のあとに離職率が上がっているなら、マネジメントや方針変更への不満が影響している可能性があります。
Excelで月別・四半期別に退職者数を集計しておくと、「いつ辞めているのか」が見えやすくなります。単に年間の離職率を見るだけではなく、時系列で変化を追うことで、原因の手がかりをつかみやすくなります。
ただし、数字だけで原因を決めつけるのは危険です。離職率が高い部署に対して、いきなり「上司が悪い」「働き方に問題がある」と断定してしまうと、現場の反発を招くこともあります。Excelでわかるのは、あくまで問題が起きていそうな場所を見つけることです。
原因を深掘りするには、以下のような定性情報も組み合わせましょう。
- 退職面談の記録
- 従業員アンケートの結果
- 残業時間や有給取得率
- 上司との1on1実施状況
- 異動希望や休職者の状況
たとえば、離職率が高く、残業時間も多く、有給取得率が低い部署であれば、業務量や人員配置に課題があると考えられます。反対に、残業は少ないのに若手の離職が多い場合は、成長実感やキャリア支援、評価への納得感に原因があるかもしれません。
分析結果を活かすうえで重要なのは、「数字を見る」→「仮説を立てる」→「現場に確認する」→「改善策を実行する」という流れです。離職率が高い部署を責めるためではなく、働き続けやすい環境をつくるための材料として使いましょう。
Excelで部署別の離職率を分析すれば、感覚ではなくデータにもとづいて組織の課題を把握できます。高い数値の裏側にある原因を丁寧に読み解き、採用・育成・評価・マネジメントの改善につなげていくことが、離職率分析のゴールです。
コメント