エクセル勤怠管理を自動化できる ｢ピボットテーブル ｣ とは？

エクセルで勤怠管理をしたいけれど、どこから手をつけてよいのか？　どうやったらできるのか？　がわからない方は多いと思います。また、すでにエクセルで労務・勤怠管理をしていても、もっと効率よくできないかと、お悩みの方も大勢いらっしゃると思います。

エクセルで勤怠管理するときに、最も効率よく管理できる方法は、「ピボットテーブルで勤怠管理する」です。ピボットテーブルとは、エクセルに備わった擬似的なデータベース機能」です。

データベースとは、データを効率よく扱うためのソフトや仕組み、作法のことをいいます。例えば、市販の勤怠管理ソフトであれば勤務時間や従業員情報などを、会計ソフトであれば仕訳情報など、大量のデータを扱っています。これを裏側で支えているのがデータベースです。

またAmazonや楽天でなどのショッピングサイトも、膨大な顧客情報・商品情報などをデータベースソフトで管理しています。このように、膨大なデータを扱うにはデータベースを使うのが最適な方法です。

ピボットテーブルによって得られるメリットは4つありますが、そのうちの1つに、｢ピボットテーブルは勤怠情報をほぼ自動的に集計・平均・カウントできる｣というものがあります。

例えば、毎月やらなければならない作業に、給与計算期間ごと・部署ごと・個人ごとに、所定労働時間・時間外労働・休日出勤などの合計、従業員ごとの出勤日数や有給休暇使用日数のカウントがあります。ピボットテーブルを使わずに時間の合計、日数のカウントをする場合、担当者は多くの時間を費やしますが、ピボットテーブルを使えばほぼ自動化できます。

このように、ピボットテーブルを使うことで自動化 = 効率化できます。この他にも多くのメリットがあり、エクセル勤怠管理にはピボットテーブルを使った方法がおすすめです。

そこでこのページでは、ピボットテーブルでエクセル勤怠管理するときの4つのメリットをさらに解説していきます。

 

ピボットテーブルのメリット

勤怠管理するときは、一週間ごとや一ヶ月ごと、給与計算期間ごとなどの期間で、従業員ごとに所定外労働時間・残業時間・休日出勤を集計します。また、分析のために期間ごと・部署ごとなどに労働時間の平均値を集計したり、正社員だけの集計、パートタイマーを除いた集計、派遣労働者だけの集計することも多いでしょう。

このような集計作業を行うときにピボットテーブルを使うと、効率よく集計でき、集計ミスもなくなります。

1. 期間ごと・部署ごと・個人ごとなどの軸（ピボット）で簡単に自動集計でき、集計ミスがなくなる

ピボットテーブルで勤怠管理せず、｢期間ごと・部署ごと・個人ごとの労働時間｣を集計するときは、集計表を作り「SUM関数（合計）」や「SUMIF関数（条件に合致するものだけ合計）」などを駆使して集計しなければなりません。さらに、関数を入力するのは手作業ですので、範囲指定ミスなどの関数入力ミスが起こる可能性があります。

しかし、ピボットテーブルの集計機能であれば、下のgif動画のように簡単に、部署ごと・一ヶ月ごとの集計ができます。集計は自動集計であるため、集計ミスがなくなります。労働時間計算は給与計算に直接影響しますので、集計ミスがなくなることはとても大きなメリットです。

2. 従業員や部署が増えてもその都度、表を作り直さなくてもよい

ピボットテーブルを使わずに集計表を作る場合、「集計期間ごと（毎月・毎週・四半期ごとなど）」「従業員の入退社や異動した」などのタイミングで、集計表のレイアウトや関数を編集しなければならず、非常に手間がかかります。また、手作業が多くなるほど関数の範囲設定ミスなどが発生します。

しかし、ピボットテーブルの集計機能は、集計期間が変わったり、従業員の入退社・異動があったりしても、それらを反映した集計表が自動的に作成されます。正社員だけの集計、パートタイマーを除いた集計、派遣労働者だけの労働時間も驚くほど簡単に集計できます。集計ミスもありません。

 

3. 蓄積されたデータを手軽に利用できる

ピボットテーブルを使うと、一例として下のgif動画のように手軽に部署ごと・期間ごとの集計・比較ができます。

 

一方、ピボットテーブルを使わない勤怠管理表では、下の図のように集計表が期間ごとに別シート、別ファイルに分かれて保存されていることがあります。下の集計表は私が勤務していた会社で前任者が作成した集計表です。こうした集計表はデータ分析に時間がかかります。

なぜなら、上の図のように期間ごとに別ファイル・別シートに分かれた勤怠管理表を使って、｢部署ごと・月ごとに集計・比較｣しようとした場合、下のgif動画のような手順になるからです。①必要な部分をコピーして、②別シートに貼り付けて、と手間がかかります。

ピボットテーブルであれば、このような集計の手間はありません。期間ごと・従業員ごと・労働形態ごと、といったデータ分析を手軽に行うことができます。

 

4. 簡単にグラフが作成できて、自動更新される

ピボットテーブルでは、下のgif動画のように簡単にグラフを作成することができます。これをピボットグラフといいます。

ピボットテーブルを使わずにグラフを更新する場合、労働時間データは日々増え続けるため、毎回グラフ範囲を設定し直さなければなりません。しかし、ピボットグラフは、日々データが増えてもグラフ範囲が自動的に拡張されますので、グラフ更新の手間がかかりません。

 

ピボットテーブルのメリットは以上の4つです。このように、ピボットテーブルで勤怠管理をすることによって、業務が効率化され、集計ミスがなくなります。

 

ピボットテーブルのデメリット

1. ピボットテーブルの使い方を覚えなければならない

ピボットテーブルを利用するためには、ピボットテーブルの使い方を勉強をしなければなりません。ですが、それほど多くの勉強量ではありません。勉強に必要な時間と、効率化によって生み出される時間を比較すれば、効率化で生まれる時間の方が遙かに大きくなります。

 

2. レイアウトが思い通りにならない

ピボットテーブルは、従業員や部署が増えてもその都度、自動的に集計表が作られます。しかしデメリットとして、集計表が思い通りのレイアウトにならないことがあります。例えば、下の図のようにピボットテーブルに新しい行を挿入することはできません。同様に、列を追加することもできません。

もしレイアウトに変更を加えたい場合は、表をコピーして別シートに貼り付けてから編集します。少し手間がかかりますが、ピボットテーブルの集計機能を使わず集計表を作るよりも速く作れます。

ただ、集計表のレイアウトに変更を加えたいケースは、管理目的ではなく、社内外の第三者に見せる目的がほとんどではないかと思います。そのため、普段の管理ではほとんどデメリットになりません。

 

3. 部署や名字が変わると、うまく集計できない

ピボットテーブルの集計機能は、名前や部署などの属性ごとに数値を集計します。そのため、部署や名前が変わってしまうと、属性ごとに集計してしまうため、うまく集計できません。例えば下の図のように、名字ごと、区分ごとに集計されてしまうと、その人の労働時間が一目でわかりません。

従業員ID1041の従業員は、2016年4月の途中で正社員から管理職になりました。そのため、区分ごとに集計されてしまい、ぱっと見で4月の労働時間（43:15+20:45=64:00）がわかりません。

5月は途中で名字が変わりました。そのため、名前ごとに集計されてしまい、ぱっと見で5月の労働時間（3:15+36:30=39:45）がわかりません。

管理するときには、名字や部署などが変わった月だけ注意すればよいのですが、どうしても一目で把握したい場合は下の図のように、従業員一人一人にユニーク（ユニークとは、重複がないことです）なIDを割り振って、IDごとに集計します。

※ピボットテーブルの「リレーションシップ」や「PowerQuery」を使うとうまく集計できるようになります。

 

4. 思い通りの平均値を求められない

ピボットテーブルには、自動集計機能という便利な機能があります。自動集計機能を使えば、部署ごと・個人ごと・期間ごとの労働時間の合計・平均などを求めるときに関数が不要で大変便利な機能です。しかし、勤怠管理においては、平均の計算には注意が必要です。

具体的には、｢ピボットテーブルの自動集計機能で得られる平均は労働日1日あたりの平均｣というものです。そのため、｢従業員ごとに1ヶ月間の労働時間を合計し、その平均を求める｣といった平均値は計算できません。もう少し具体的に解説していきます。

 

下の図では、「個人ごとの月ごと残業時間合計」と、集計行では「部署ごと月ごと残業時間合計」が自動集計されています。赤い吹き出しは、この合計を平均で集計しようと設定しているところです。

平均を求めるよう設定したところ、「月ごと一人あたり平均残業時間」ではなく、「部署ごと1日あたり・一人あたりの平均残業時間」になってしまいました。

なぜ1日あたりの平均になってしまうかというと、ピボットテーブルの集計機能で求めることができる｢平均｣とは、｢入力されている一つ一つのデータの平均（勤怠管理では出勤日1日ごとの平均）｣だからです。

ここで求めたい値は、下の図のような「月ごと一人あたり平均残業時間」です。しかし、ピボットテーブルの集計機能では、下の図のような集計は難しいです。

下の図のように「月ごと一人あたり平均残業時間」を求るためには、①ピボットテーブルで合計を求め、②ピボットテーブルをコピーして別シートに貼り付け、③AVERAGE関数を使って「月ごと一人あたり平均残業時間」求めます。少し手間ですが、この方法が手間が少なくおすすめです。

デメリットをまとめます。①集計表のレイアウトが思い通りにならない、②名字が変わると集計に工夫がいる、③平均値を思い通りに集計できないことです。しかし、これらのデメリットは解説したように普段はほとんどデメリットにならないようなものばかりです。そのため、メリットがデメリットを上回ることがわかります。

 

労働時間を集計するためのテーブル構成

上記労働時間集計のためのテーブル構成(ピボットテーブルの元になる表)は以下のとおりです。参考にしてみて下さい。

 

まとめ

ピボットテーブルを使った勤怠管理のメリット・デメリットをまとめると、下の表のようになります。

メリット	デメリット
自動集計でき、集計ミスがなくなり効率化できる	ピボットテーブルの勉強が必要
集計表が自動作成される	レイアウトが思い通りにならない
簡単にデータ分析できる	名字が変わるとうまく集計できない
簡単にグラフを作成でき、自動更新される	思い通りの平均値を求めるのに一手間かかる

 

メリットとデメリットを比較すると、メリットがデメリットを大きく上回ることがわかると思います。勤怠管理にはピボットテーブルを使うのが最も効率よく、ミスなく集計できる方法です。使い方になれるまでは少し大変ですが、なれてしまえばこれほど便利なものはありません。ピボットテーブルを使った勤怠管理にチャレンジしてみましょう。