こんにちは、blueです。
前回はピボットテーブルの概念と仕組みについて説明しました。
前回までの記事はこちら
前回の図解でピボットテーブルの基本概念はわかっていただけたかと思います。
その為今回は実際の使い方について話をします。
ピボットテーブルは４つもエリアが存在していて、
- どこに何を入れたらいいか？
- どのようにそれぞれのエリアを処理したらよいか
と悩むかもしれません。
ただピボットテーブルを作成する際の考え方は決まっていますので安心してください。
また４つのエリアといっても操作は１つ１つごとです。
今回も行、列、値、レポートフィルターエリアに分けて説明します。
「ピボットテーブルは難しい」という漠然としたイメージを持たれている方には以下がお勧めです。ピボットテーブルで行っている処理を1ステップずつ説明してくださっていて、この一冊を読むだけで必ずピボットテーブルが使えるようになります。
ピボットテーブルを作成する際の考え方
前々回もお話ししましたが元となる表形式のリストの各項目の名称は以下となります。
ピボットテーブルを作成する際は
- 「表形式のリストのどのフィールド（列）を使用するか」
- 「（これらのデータから）何を知りたいのか？」
を考えます。
なおピボットテーブルで考える際はフィールドを基準に考えてください。
部分的な抜き取りの場合ならピボットテーブルはいりません。
詳しい記事はこちら
以下の例では都道府県ごと、性別ごとの試験点数の合計を知りたいとします。
この場合、使用するのは３つの列とわかります。
今回は試験点数の合計の分布を知りたいので試験点数の列を値エリアに入れることになります。
結果的にピボットテーブルの結果は以下のようになります。
この考え方で以下のような表も簡単に作成することができます。
なお概念的には以下のようにあらわせます。
ただ基本は何を知りたいのか（＝集計したいのか）？がわかればOKです。
なおこの考えで
- 都道府県ごとの人数を知りたい
- 都道府県ごとの血液型の分布を知りたい
- 年齢ごとの平均点数を知りたい
というのもリストから簡単に作成することができます。
この方法でまずは各エリアにデータを配置することができるようになります。
各エリアでの操作
それでは４つのエリアに入れた後の操作について説明します。
それぞれのはボタン操作で可能なのですが最初は慣れないと思いますので詳しく説明します。
行ラベルエリア、列ラベルエリアの操作
フィルターによる操作
行ラベルエリア、列ラベルエリアにデータを入れた際は以下のようにラベルにフィルターが設置されます。
このフィルターはテーブルにした際につくフィルターと同じです。
ただピボットテーブルの性質上、重複は既に削除されているのでさらに踏み込んだ分け方ができます。
以下の例では「都道府県列」に対するラベルフィルターにて島を含む都道府県のみを抽出しています。
このように特定の文字列のみを含む項目の抽出も簡単にできます。
なおフィルターをクリアしたい場合は「～からフィルターをクリア」を選択すればOKです。
複数フィールドの挿入
行ラベルエリアに複数のフィールドを入れると以下のようになります。
この場合上の方が優先順位の高いリストになります。
上の図では「都道府県」が上にあるので以下のような都道府県ごとの名前リストになります。
なお複数の項目を行ラベルに入れた場合、デフォルトでは１列に集約されます。
この表示形式を「コンパクト形式」と呼びます。
この形式は合計3種類あり以下で表示形式を変更することができます。
「デザインタブ」→「レポートのレイアウト」
実際の表示は以下になります（アウトライン形式、表形式）。
またアウトライン形式と表形式のデータにある空白は以下の方法で埋めることも可能です。
[デザインタブ]→[レポートのレイアウト]→[アイテムのラベルをすべて繰り返す]
実際の表示は以下になります。
この方法で表形式での出力も考えた表示をさせることができます。
手動でのグループ化
ピボットテーブルにした後で任意にグループ分けしたい場合はどうしたらよいでしょう？
例えば「北海道地方、東北地方、関東地方」のように更なるエリア分けをする場合です。
この場合、リストに「地方」という列を追加してもよいですがピボットテーブルでのグループ化も可能です。
該当部分を選択
「ピボットテーブル分析]→「グループの選択」をクリック
選択範囲をグループ化することができます。
日付フィールドの挿入
ピボットテーブルの行、列ラベルエリアに日付フィールドを挿入する際、文字列や日付とは異なった挙動をします。
例として誕生日（日付）フィールドをあげます。
行ラベルエリアに入れた際、誕生日のフィールドは入りますが同時に年、四半期のフィールドが追加されます。
ピボットテーブルでは、日付 (シリアル値) のフィールドを行エリアや列エリアに配置したときに、自動的にグループ化されるという特徴があります。
結果的にピボットテーブルは以下になります。
３項目以上入るとまとめて最上位しか表示されなくなる性質はありますが、[＋]をクリックすると四半期と誕生日の列は存在していることがわかります。
また挿入後にグループの変更を行うことも可能です。
以下では「年」「四半期」「誕生日」のフィールドを「年」と「月」に変えています。
[ピボットテーブル分析タブ]-[フィールドのグループ化]で年と月を選択
作成されたテーブルの行ラベルも年と月になっていることがわかります。
数値フィールドの挿入
行ラベル、列ラベルエリアに数値フィールドを挿入した場合の挙動は文字列と変わりません。
以下は年齢（数値型）を行ラベルに入れた場合です。
ただ単純にそれぞれの数値が並ぶだけです。
なお数値フィールドは区間にわけてグループ化することができます。
以下は数値フィールドを10刻みでグループ化しています。
[ピボットテーブル分析タブ]-[フィールドのグループ化]-[先頭の値、末尾の値、単位を設定]
結果的に以下のようになります。
上記のように各区間ごとのデータをまとめたものを「度数分布表」といいます。
このようにピボットテーブルでは度数分布表も簡単に作成することができます。
ちなみに上記の表には30-40のフィールドが存在しません。
これを追加するには以下のようにします。
「ピボットテーブル分析タブ]-「フィールドの設定」にて「データのないアイテムを表示する」にチェックを入れる
これでデータがない部分の行ラベルも表示されるようになりました。
ただ値エリアに数値が表示はされていないので以下の追加操作をします。
「ピボットテーブル分析タブ」ー「ピボットテーブル」－「オプション」の「空白セルに表示する値」を0にする
これで以下のようになります。
値エリアの操作
値エリアにデータを入れた際の集計のデフォルトは「文字列型→データの個数」「数値型→合計」でした。
なおこの集計方法は値エリア挿入後にも変更できます。
挿入したフィールドを右クリックー「値フィールドの設定」ー「集計方法タブ」ー「選択したフィールドのデータ」から選択
変換された値フィールドは以下のように変更されます。
クロス集計
行ラベルエリア、列ラベルエリア両方に入れた際の挙動も見ておきます。
以下のようなフィールドを挿入すると・・・
テーブルに行と列ラベルが挿入され、
値エリアに試験点数の合計が表示されます。
クロス集計においても値エリアの集計方法は変更可能です。
なおクロス集計はピボットテーブルの肝ですが、発表などの表形式にはよく使われるので案外イメージがつきやすいのではないかと思います。
レポートフィルターエリアの操作
最後にレポートフィルターエリアの説明です。
このエリアに入れた際の挙動は簡単です。
先ほどのクロス集計に性別フィールドと年齢フィールドを追加してみます。
すると以下のように上部にエリアが生成されます。
「男性」「女性」で分けた結果は以下になります。
このようにレポートフィルターエリアでは行、列、値エリアにない項目についてフィルターをかけることができます。
より簡単に考えると以下のようになります。
各項目についてレポートにするというイメージを持っていただけたらとわかりやすいかと思います。
今回のまとめ
ピボットテーブルを作成する際は
- 「表形式のリストのどの列（フィールド）を使用するか」
- 「（これらのデータから）何を知りたいのか？」
を考えるのが肝でした。
またピポットテーブル作成後は各エリアごとで表示形式やグループ化の変更が行えました。
これらを理解すればピボットテーブルは簡単に扱えます。
次回は最終としてその他の機能について説明します。
コメント