Cafe HOUKOKU-DOH

~ホッとひと息的な読み物でブレイクするサイト~

【いまさら訊けない】Office基礎講座19 ピボットテーブル

人事に必須の分析ツール

人事の分野で社員に対するさまざまな分析をするのに必須のツールといえば、データベースの表からドラッグだけでクロス集計をしてくれる「ピボットテーブル」である。この機能があるだけで、人事メンバーはExcelをインストールする価値がある。今週はこのピボットテーブル機能を基本からおさらいしていきたい。

 

まず、ピボットテーブルに使用するデータベースを用意する。すでにまとまっているシートでデータベース形式(1行目がヘッダーレコード、2行目以降がデータレコードの形式のもの。Office基礎講座11 データベース機能の基礎参照)でもいいし、新たに人事情報管理システムなどからCSV形式でダウンロードしてもいい。

 

blog.houkoku-doh.com

 

ここで気をつけたいのは、ピボットテーブルにそのシートを使ってしまうと、他の用途に使いまわすことがむずかしくなるので、このピボットテーブル専用に準備する。すでにあるシートであればシートをコピーして新たにピボットテーブル処理をする専用のブックを作成する。

 

ちなみにデータベースの回でも説明してが、データレコードに入ってる値は列ごとにデータ形式がそろっている必要がある。同じ列に数値と文字列が混在していると、Excelがデータベースを処理するときに正しく認識しないことがあるからだ。

 

具体的な手順だが、まずデータベースとなるシートのレコードのどこかをアクティブセルにする。「挿入」タブからリボンのいちばん左端にある「ピボットテーブル」をクリック。すると自動的にデータベース全体が選択され、「ピボットテーブルの作成」ダイアログボックスが表示される。そのまま「OK」を選択するとあたらしいシートが挿入され、そのシートの左側に「ピボットテーブル1」があらわれる。この時点では値はなにも入っていない。

 

f:id:HOUKOKU-DOH:20210727164457j:plain

 

f:id:HOUKOKU-DOH:20210727164452j:plain

 

シートの右側には「ピボットテーブルのフィールド」があらわれ、「レポートに追加するフィールドを選択してください」というメッセージの下にデータベースのヘッダーレコードに入っている列見出しが左から右の順に上から下に並んでいる。

 

f:id:HOUKOKU-DOH:20210727164447j:plain

 

シート右の下段には「次のボックス間でフィールドをドラッグしてください」とあり、その下には箱が4つ並んでいる。4つの箱は右上がクロス集計したい表の「列見出し」、左下が「行見出し」右下が「クロス集計値」、そして左上が「クロス集計表のフィルター」になる。

 

それではクロス集計をしてみよう。縦軸に会社年令、横軸に勤務地、クロス集計値に氏名(文字列)、フィルターに性別をそれぞれドラックする。そうすると勤務別の年令分布の表があらわれる。これだけの操作である。

 

f:id:HOUKOKU-DOH:20210727164442j:plain

 

ちなみにこの作業をピボットテーブル機能を使わずに行おうとすると、データベースのシートにフィルターをかけて、勤務地と年令でひとつずつしぼり込んで人数をひろっていかなければならない。

 

ちなみにこのクロス集計値は、「値」の箱に入れる列のデータ形式応じて自動的に設定される。今回は文字列である氏名を集計したので、データの個数(=人数)になったが、仮に本給などの数値をドラッグすると初期値は合計になる。ドラッグした後に箱の中の見出しをクリックするとメニューがあらわれるので、「値フィールドの設定」を選択する。そうすると集計に使用する計算の種類が選べる。

 

f:id:HOUKOKU-DOH:20210727164437j:plain

 

計算の種類は、合計、個数、平均、最大、最小、積、数値の個数、標本標準偏差標準偏差、標本分散、分散が選択できる。

 

つぎにフィルターであるが、性別を設定したので、シートの左上にフィルターが表示されている。このフィルターを選択すると、下にある集計表がフィルターでしぼられたレコードに限って集計される。

 

なお、この4つの箱とも、設定するフィールド(列見出し)を複数設定でき、ならんでいる下から順番に集計がかかるようになる。値の箱に複数入れると上下もしくは左右にならんで集計値を表示してくれる。

 

f:id:HOUKOKU-DOH:20210727164426j:plain

 

f:id:HOUKOKU-DOH:20210727164432j:plain

 

このように人事屋さんのデータ集計、分析にはなくてはならないツールであるピボットテーブル機能、ぜひ身につけて使いこなせるようになりたい。