勤怠表を「関数で集計」と、「ピボットテーブルで集計」を比較する 改訂版
その１で紹介したように「タイムカードの集計」は大変な作業です、業務効率化のためにExcelを使う場合「関数」を使うことが多いと思いますが、「ピボットテーブル」を使えば効率よく集計することができます。
今回は、「関数」を使う場合と「ピボットテーブル」を使う場合を比較しながら説明します。
遅刻・早退・欠勤などの状況を管理する「勤怠表」を作成します
勤怠表を集計する方法にはいろいろあると思います。
- 職員別に集計
- 部署別に集計
- 月別に集計
- 勤怠情報別に集計
通常、集計を簡単にするために「何らかのルールで別けて集計」することが多いと思いますが、それらを「１つの表にまとめる」ことで、後で簡単に統計資料を作成することができます。
「関数」を使って集計表を作成する
職員の勤怠状況を関数を使って集計する場合、同一の職員が複数の勤怠情報を持っている場合があります。
関数を使う場合はそのままでは集計することが難しいため、何らかの方法で情報を加工する必要があります。
下の例のように「斎藤裕二」の勤怠情報が複数ある（同一条件が複数ある）場合にVLOOKUP関数を使う場合は工夫が必要です。
- 「VLOOKUP関数」を使って「勤怠状態」を抜き出そうとする場合、「斎藤裕二」の情報が複数ある場合「VLOOKUP関数」では区別することができない
- そこで「COUNTIF関数」を使って、同じ「社員番号」のデータに「同じ社員番号の出現順」の「値」を加えた「ID」のフィールドを新たにを作成する必要がある
COUNTIF関数を使って、「同一のID」に連番を付加する
COUNTIF関数を使って、条件が合致する複数のデータを区別する
「社員番号で検索」すると、複数のデータが同条件となってしまうので、出現する順番で連番を付けることで区別する。
社員番号「A159」の頭に、出現順に「1 , 2 , 3･･･」と番号を付けて、
「1A159 , 2A159 , 3A159･･･」に変換します。
VLOOKUP関数を使って、特定の社員の勤怠情報のある「日付」を抜き出す
COUNTIF関数を使って区別したデータを検索値にして、VLOOKUP関数を使って勤怠情報を抜き出します。
検索値を「No.」と「社員番号」を「＆で結合」したものにする。
VLOOKUP関数を使って、特定の社員の「勤怠状況」を抜き出す
このように「関数を使って集計」するには、目的に応じてその都度「数式を作成」する必要があります。
さらに、数式を入力するための「フィールド(列)」を新たに作る必要があるので「集計表」がどんどん大きくなってしまい扱いずらいものになってしまいます。
そして最大の問題は、数式を作成する際に発生する「矛盾（エラー）を回避」するために作成した数式が「他人には分かりにくい」ことです、場合によっては「作成した本人も時間がたつと忘れてしまう」ことです。
「ピボットテーブル」を使って集計表を作る
「ピボットテーブル」の特徴は「元のデータ（表）に手を加えない」ことです。
必要なデータ（項目）を網羅した単純なリスト（表）を作成しておけば、後は簡単な操作で様々な方法・形態の集計表を作ることができます。
さらに連動して「グラフ」を作成して、視覚的に情報を伝える資料を作ることもできます。
このように「ピボットテーブル」を使えば、単なる集計表ではなく、簡単な操作で、表の内容を視覚化したグラフを作成することができます。
勤怠状況を集計した「表」
簡単な操作で「集計表」を作成することができます。
「ドリルダウン」で詳細を確認
ドリルダウンの機能を使えば、集計前の情報を確認することができます。
勤怠状況を「視覚化したグラフ」
簡単な操作でいろいろな集計方法のグラフを作成することができます。
「職員名」と「勤怠状況」でクロス集計した縦棒グラフ
「職員名」と「勤怠状況」でクロス集計した積み上げ縦棒グラフ
「職員名」と「勤怠状況」でクロス集計した円グラフ
「タイムライン」の機能を使って、対象期間を容易に変更できる
ピボットテーブルの機能の「タイムライン」を使えば、「期間を区切ることなく」作成された「データ（表）」を特定の期間に絞り込んで集計し直すことができます。
ドラッグドロップの簡単な操作で、「X、Y軸の変更」ができる
「行」と「列」に「項目」をドラッグするだけで、集計方法を変えることができます。
「リレーション」の機能で、元データの「表」を最小限にできる
給与計算のために作成する勤怠表に必要なフィールドは「社員番号」「氏名」「日付」「勤務状態」等です。
しかし、勤怠表を使って「分析」をするためには、これに加えて「部署」「雇用状態」「勤務時間」などが必要になります。
必要に応じてフィールド（項目）を次々と追加していくと、「表が肥大化」してしまい見づらく使いにくい「表」になってしまいます。
「リレーション」の機能を使って「内容」別に「表」を分けることにより、見やすく使いやすくなる
「給与計算に必要な項目」のピボットテーブルと、「分析をする際に必要な項目」のピボットテーブルに分けることにより運用・管理が簡単になります。
以上のように勤怠表の集計に「ピボットテーブル」を使うことによって、単なる「集計表に過ぎなかった」、Excelの表が、「統計や分析に使うことができるツール」になります。
ピボットテーブルを使って集計をする具体的な操作方法は「その３」に続く
- 勤怠表を「関数で集計」と、「ピボットテーブルで集計」を比較する 改訂版
