はじめに

Excel10年選手の元事務職が、ちょっとした分析やデータの確認に便利なピボットテーブルの基本機能についてまとめます。

やること

データの時系列推移と構成をピボットテーブルで確認してみます。 環境はWindows 10、Excel 2016です。

ピボットテーブルで利用できるデータ形式

いわゆるテーブル、データフレーム形式に整えられたものを使用します。 Excelファイルでよく見かける以下のようなおもてなしはピボットテーブルのデータソースには過剰接待となるため、速やかに滅します。

• セル結合
• 集計グループ
• 行もしくは列の非表示

また、CSVやSQL Server等の外部データを用いたり、複数テーブルのリレーションを設定してピボットテーブルを作成することも可能です。

ピボットテーブルの作成

今回は架空の売上データを用いて単一のテーブルから作成します。 データソースとしたいセル範囲の任意のセルを選択した状態でリボンメニューから挿入＞ピボットテーブルを選択します。 意図した範囲が指定されていることを確認し、OKをクリックすると新規シートにピボットテーブルが作成されます。

ピボットテーブルの基本操作

画面右手に現れる「ピボットテーブルのフィールドリスト」から集計に利用したいフィールドのチェックボックスをオン、もしくはフィールドセクションの任意のエリアにドラッグします。 チェックボックスを利用した場合は、対象フィールドのデータ型によって行ラベルもしくは値エリアに自動で設定されます。

ピボットテーブルでデータの推移を見てみる

売上の推移を見てみます。 行もしくは列ラベルに日付形式のフィールドを追加すると、自動的に年・1月はじまりの四半期・月でグループ化されます。 このグループ化は、該当セルの右クリックメニュー＞グループ化から編集することができます。 ここでは週次（7日）を選択しました。 日付形式に限らず、行または列ラベルに設定されたフィールドは、データをグループ化して集計することができます。

ピボットテーブルで色々な集計をしてみる

列ラベルにフィールドを追加して、クロス集計を行います。 値エリア以外の各ラベルは、規定値を自由に変更することが可能で、ピボットテーブル上でラベルを変更しても、ソースデータには影響しません。データを見慣れない職種の方向けにアウトプットを作成するときに少し便利です。 月・性別のクロス集計を行い、ラベルを見やすく変更しました。 次は集計を比率で見てみます。 値エリアの任意のセルを選択し、右クリックメニューの「値フィールドの設定」から計算の種類を変更します。 集計方法や計算の種類については、下記リンク先で確認できます。 ピボットテーブル レポートのフィールドでの集計方法またはユーザー設定の計算を変更する 「行集計に対する比率」で、月次売上の男女比率が出せました。ついでに見やすくする工夫としてホーム＞条件付き書式からカラースケールを設定しています。 独自の計算式を設定したい場合は、ピボットテーブルツール＞分析＞フィールド/アイテム/セット＞集計フィールドからフィールドを使用した計算式を設定することができます。

ピボットテーブルの集計対象データをフィルタリングしてみる

フィールドセクションのフィルターにフィールドを追加したり、ピボットテーブルツール＞分析＞{スライサー,タイムライン}の挿入を使うことで集計対象データをフィルタリングすることができます。 スライサーはExcel2010、タイムラインは2013以降の新機能で、現在設定されているフィルタ条件がひとめでわかります。

まとめ

以上のように、Excelもきちんと整形されたテーブルデータを用意することで、簡単に集計処理を行うことができます。 文中では説明を割愛しましたが、データベースと連携するなど高度な操作も簡単に行うことができますので、Excel嫌いの方もこれを機会にExcelの機能を見直してみてはいかがでしょうか。