勤怠データ分析の準備：まず確認すべき項目とデータの整え方
Excelで遅刻傾向を分析する前に、まず大切なのが勤怠データを分析しやすい形に整えることです。データがバラバラのままだと、関数やピボットテーブルを使っても正確な結果が出せません。最初の準備を丁寧に行うことで、後の分析がかなりスムーズになります。
まず確認したいのは、勤怠データに必要な項目がそろっているかです。最低限、以下のような列があると遅刻分析がしやすくなります。
- 社員番号または氏名
- 部署名
- 勤務日
- 出勤時刻
- 始業時刻
- 遅刻時間または遅刻判定
特に重要なのが「出勤時刻」と「始業時刻」です。遅刻かどうかは、この2つを比較して判断します。例えば始業時刻が9:00、出勤時刻が9:08であれば、8分の遅刻と判定できます。もし始業時刻の列がない場合は、部署や勤務形態ごとに始業時間を別表で管理しておくと便利です。
次に、日付や時刻の形式を統一しましょう。Excelでは見た目が「9:00」でも、文字列として入力されていると計算できないことがあります。出勤時刻の列を選択し、表示形式を「時刻」に設定しておくのがおすすめです。勤務日も同様に、日付データとして扱える状態にしておきましょう。
また、空白や入力ミスも事前にチェックしておきます。例えば、出勤時刻が空欄のままだと遅刻なのか欠勤なのか判断できません。「9:0」「９：００」のように表記ゆれがある場合も、正しく集計できない原因になります。データ量が多い場合は、フィルター機能を使って空白セルや不自然な値を探すと効率的です。
分析用の表は、1行目に項目名を入れ、2行目以降にデータを並べるシンプルな形にします。結合セルや複数行にまたがる見出しは、関数やピボットテーブルで扱いづらくなるため避けましょう。Excelで分析するなら、「1行＝1人の1日分の勤怠」という形に整えるのが基本です。
ここまで準備できれば、遅刻回数のカウントや曜日別の傾向分析に進めます。勤怠データ分析は、いきなり複雑な関数を使うよりも、まずは正しいデータをそろえることが成功のポイントです。
遅刻回数を見える化する：Excel関数で遅刻者・遅刻日を抽出する方法
勤怠データを整えたら、次は「誰が、いつ、何回遅刻しているのか」をExcel関数で見える化していきます。遅刻傾向を分析するうえで、まず必要なのは遅刻の有無を判定する列を作ることです。
例えば、以下のような表があるとします。
- A列：社員名
- B列：部署名
- C列：勤務日
- D列：出勤時刻
- E列：始業時刻
この場合、F列に「遅刻判定」を作り、出勤時刻が始業時刻より遅い場合に「遅刻」と表示させます。F2セルには、次のような数式を入力します。
=IF(D2>E2,"遅刻","正常")この数式は、D2の出勤時刻がE2の始業時刻を超えていれば「遅刻」、そうでなければ「正常」と表示するものです。あとは下の行までコピーすれば、全員分の遅刻判定を一括で出せます。
さらに、遅刻時間も確認したい場合は、G列に「遅刻時間」を作ると便利です。G2セルに以下の数式を入れると、遅刻した時間だけを表示できます。
=IF(D2>E2,D2-E2,"")結果が「0:08」のように表示されれば、8分遅刻していることがわかります。表示が小数になる場合は、セルの表示形式を「時刻」または「[h]:mm」に変更しましょう。
次に、個人ごとの遅刻回数を数える方法です。例えば、社員名がA列、遅刻判定がF列にある場合、特定の社員の遅刻回数はCOUNTIFS関数で集計できます。
=COUNTIFS(A:A,"山田太郎",F:F,"遅刻")この数式では、A列が「山田太郎」で、なおかつF列が「遅刻」の行数をカウントします。社員名を直接入力する代わりに、別表に社員名一覧を作り、そのセルを参照すれば、複数人の遅刻回数をまとめて集計できます。
=COUNTIFS(A:A,H2,F:F,"遅刻")H2セルに社員名が入っていれば、その人の遅刻回数が自動で表示されます。これを社員一覧に沿ってコピーすれば、誰が何回遅刻しているのかが一目でわかります。
また、「遅刻した日だけを一覧で見たい」という場合は、Excelのフィルター機能を使うのが簡単です。表全体を選択してフィルターを設定し、F列の遅刻判定で「遅刻」だけに絞り込みます。すると、遅刻者と遅刻日だけを確認できる状態になります。
Microsoft 365などの新しいExcelを使っている場合は、FILTER関数も便利です。例えば、遅刻した行だけを別の場所に抽出するなら、次のように入力します。
=FILTER(A:G,F:F="遅刻")これにより、元データを残したまま、遅刻データだけの一覧を自動で作成できます。毎月の勤怠データを確認するときにも使いやすい方法です。
このように、IF関数で遅刻を判定し、COUNTIFS関数で回数を数え、必要に応じてフィルターやFILTER関数で遅刻日を抽出することで、遅刻状況をわかりやすく整理できます。まずはシンプルな関数で「遅刻の事実」を見える化することが、次の傾向分析につながります。
曜日・時間帯別に分析する：遅刻が起きやすいパターンを探る
遅刻者や遅刻回数を把握できたら、次は「どの曜日に多いのか」「何時台の遅刻が多いのか」を確認していきましょう。単に遅刻回数を見るだけではなく、発生しやすいタイミングを分析することで、原因や改善策を考えやすくなります。
まずは、勤怠データに「曜日」の列を追加します。例えば、C列に勤務日が入っている場合、H列に「曜日」を作り、H2セルに次の数式を入力します。
=TEXT(C2,"aaa")この数式を使うと、勤務日から「月」「火」「水」のように曜日を表示できます。下の行までコピーすれば、すべての勤怠データに曜日情報を付けられます。
曜日別の遅刻回数を集計したい場合は、別表に月〜日を並べて、COUNTIFS関数を使います。例えば、H列が曜日、F列が遅刻判定の場合、月曜日の遅刻回数は次のように求められます。
=COUNTIFS(H:H,"月",F:F,"遅刻")火曜日以降も同じように集計すれば、どの曜日に遅刻が多いのかが見えてきます。例えば月曜日に遅刻が多い場合は、週明けの生活リズムの乱れや、通勤時の混雑が影響しているかもしれません。金曜日に多い場合は、疲労の蓄積や前日の残業などが関係している可能性があります。
次に、時間帯別の分析です。遅刻といっても、9:01のような軽微な遅刻と、9:30以降の大きな遅刻では意味合いが変わります。そこで「遅刻時間帯」の列を作ると、より具体的な傾向をつかめます。
例えば、G列に遅刻時間が入っている場合、I列に「遅刻時間帯」を作り、次のような数式を入力します。
=IF(G2="","",IF(G2<=TIME(0,5,0),"5分以内",IF(G2<=TIME(0,15,0),"15分以内","15分超")))この数式では、遅刻していない行は空白、遅刻している場合は「5分以内」「15分以内」「15分超」に分類します。自社のルールに合わせて「10分以内」「30分以内」などに変更しても問題ありません。
時間帯別の件数も、COUNTIFS関数で集計できます。
=COUNTIFS(I:I,"5分以内",F:F,"遅刻")このように分類すると、遅刻の多くが数分程度なのか、それとも大幅な遅刻が目立つのかを判断できます。数分の遅刻が多い場合は、電車遅延や始業直前の出社習慣が原因かもしれません。一方で15分を超える遅刻が多い場合は、個人の生活リズムや業務負荷など、より踏み込んだ確認が必要になるでしょう。
曜日と時間帯を組み合わせて見るのも効果的です。例えば「月曜日の15分超の遅刻が多い」「水曜日は5分以内の遅刻が多い」といった傾向がわかれば、単なる注意喚起ではなく、曜日ごとの対策を考えられます。
遅刻分析では、回数だけで判断するのではなく、発生する曜日や遅刻時間の長さまで分解して見ることが大切です。パターンが見えてくると、次の章で紹介するピボットテーブルを使った集計も、より意味のある分析になります。
ピボットテーブルで傾向を把握：個人別・部署別の遅刻状況を集計する
曜日や時間帯ごとの傾向が見えてきたら、次はピボットテーブルを使って、個人別・部署別の遅刻状況をまとめて確認していきましょう。ピボットテーブルを使うと、関数をいくつも作らなくても、社員ごとの遅刻回数や部署ごとの件数をすばやく集計できます。
まず、勤怠データの表内をクリックし、Excelのメニューから「挿入」→「ピボットテーブル」を選択します。作成場所は、元データと分けて管理しやすいように「新規ワークシート」を選ぶのがおすすめです。
ピボットテーブルが作成されたら、右側に表示されるフィールド一覧で、以下のように項目を配置します。
- 行：社員名
- 列：遅刻判定
- 値：遅刻判定
値エリアに「遅刻判定」を入れると、通常は件数として集計されます。列に「遅刻」「正常」が並ぶため、各社員が何回遅刻しているかを一覧で確認できます。もし「遅刻」だけを見たい場合は、列ラベルのフィルターで「遅刻」のみに絞り込みましょう。
部署別に確認したい場合は、行エリアに「部署名」を入れます。さらに、部署の下に社員名を追加すれば、部署ごとの合計と、その内訳となる個人別の遅刻回数を同時に確認できます。
- 行：部署名 → 社員名
- 列：遅刻判定
- 値：遅刻判定の個数
この形にすると、「営業部全体で遅刻が多いのか」「特定の社員に偏っているのか」といった見方ができます。部署単位で多く見えても、実際には一部の人に集中しているケースもあるため、全体と個人の両方で確認することが大切です。
また、遅刻時間の合計や平均を見たい場合は、「遅刻時間」を値エリアに追加します。値フィールドの設定から「合計」や「平均」を選ぶことで、遅刻回数だけでなく、どれくらいの時間遅れているのかも把握できます。回数は少なくても1回あたりの遅刻時間が長い人は、別途確認が必要かもしれません。
さらに、「勤務月」や「曜日」をフィルターエリアに入れると、月別・曜日別に切り替えながら分析できます。例えば「先月だけ」「月曜日だけ」と条件を絞れば、より具体的な傾向を見つけやすくなります。
ピボットテーブルは、元データを更新しただけでは自動で反映されない場合があります。新しい勤怠データを追加したら、ピボットテーブル上で右クリックし、「更新」を押すようにしましょう。
個人別・部署別に集計することで、遅刻の傾向はかなり見えやすくなります。次の章では、この集計結果をグラフやレポートにまとめ、改善アクションにつなげる方法を紹介します。
分析結果を改善につなげる：グラフ化とレポート作成のポイント
ピボットテーブルで遅刻状況を集計したら、最後はその結果をグラフやレポートにまとめ、改善アクションにつなげることが大切です。Excelで数字を集計するだけでは、「結局どうすればいいのか」が伝わりにくいため、見る人がすぐに傾向を理解できる形に整えましょう。
まずおすすめなのが、遅刻回数を棒グラフで表示する方法です。個人別や部署別の遅刻回数を棒グラフにすると、どこに遅刻が集中しているのかが一目でわかります。ピボットテーブルの集計結果を選択し、Excelのメニューから「挿入」→「縦棒グラフ」を選ぶだけで簡単に作成できます。
部署別の比較なら縦棒グラフ、月ごとの推移を見るなら折れ線グラフが向いています。例えば、1月から6月までの遅刻件数を折れ線グラフにすれば、遅刻が増えているのか、改善しているのかを確認できます。曜日別の遅刻傾向を見る場合も、棒グラフにすると「月曜日が多い」「金曜日に増える」といった特徴が伝わりやすくなります。
グラフを作るときは、見た目を整えることも重要です。タイトルには「部署別 遅刻回数」や「曜日別 遅刻件数」のように、何を表しているグラフなのかを明確に書きましょう。また、色を使いすぎると見づらくなるため、強調したい項目だけ色を変えるのがおすすめです。
レポートにまとめる際は、単にグラフを貼るだけでなく、以下の3点をセットで書くと伝わりやすくなります。
- 事実：どの部署・曜日・時間帯で遅刻が多いのか
- 考察：なぜその傾向が出ていると考えられるのか
- 対策：具体的に何を改善するのか
例えば、「営業部は月曜日の遅刻が多い」という結果が出た場合、事実だけで終わらせず、「週明けの直行予定や朝礼時間の影響が考えられる」といった考察を加えます。そのうえで、「月曜朝の予定確認を前週金曜に行う」「始業前のオンライン朝礼を見直す」など、具体的な改善案につなげると実務で使えるレポートになります。
また、個人名を出す場合は注意が必要です。遅刻分析は改善が目的であり、誰かを責めるためのものではありません。社内共有用の資料では、必要に応じて個人名を伏せたり、部署単位でまとめたりする配慮も大切です。
最後に、改善策を実施した後は、翌月以降のデータと比較しましょう。グラフで推移を追えば、対策の効果が出ているかを確認できます。Excelで勤怠データを分析する目的は、遅刻を数えることではなく、働きやすく、時間を守りやすい仕組みを作ることです。集計・可視化・改善をセットで回すことで、遅刻傾向の分析はより実践的なものになります。
コメント