Excel使いこなしの基礎と応用

ピボットテーブルでクロス集計|フィールドの階層とグループ化

ピボットテーブルでは列・行に配置したフィールドに「地区」→「店舗」→「担当者」というような階層構造を持たせたり、「商品」フィールドの項目をグループ化して幾つかのカテゴリーに分類するといったことができます。これらはクリック一つで展開と収納が切り替わり、多角的な分析が速やかに実行できます。

目次


フィールドの階層

フィールドの階層とは、上位にあるフィールドの中に下位フィールドの内容が格納された状態です。フィールドに上下関係を持たせることで、集計の視点が柔軟に伸縮します。下位フィールドは折りたためますので、上位フィールドだけを確認したい時には非表示にしておけます。
フィールドの階層構造

階層の付け方

階層構造を形成するのは簡単です。
「ピボットテーブルのフィールド」作業ウィンドウの行エリア、または列エリアに複数のフィールドを配置し、その上下関係がそのまま階層の上下になります。フィールドはドラッグすれば入れ替わります。
「ピボットテーブルのフィールド」作業ウィンドウでの階層

筋道の通った上下関係にするのがコツ

ピボットテーブルの縦軸(行エリア)と横軸(列エリア)に配置するフィールドに制限はありません。行エリアの最上位に「金額」を置いて、下位に「商品」と「担当者」を押し込んだとしても忠実に集計表が出来上がります。ただ、やたら縦長で、繰り返し同じ担当者名が並ぶ不細工な表になるだけです。(そこから読み取れる情報が自分がにとって有益なら、もちろん、そのままでOK)
脈絡のない階層構造のピボットテーブル

とは言え、多人数で共有する資料にする場合などは「見やすさ」を第一義にして作表するべきで、フィールドの階層もなるべく筋道の通った構成にしましょう。
最も分かりやすい例として、「日付」フィールドの階層があります。
最上位のフィールドを「」とし、その配下に「四半期」「月」を置いて、最下位フィールドが「日付」になる構造です。
年→四半期→月→日付の階層構造
このように、一番大きなカテゴリーを最上位に置き、中、小と順に並べていくのがフィールドの階層の定石です。

スポンサーリンク

さて、気になることが一つ。
フィールド名を作業ウィンドウのエリアに並べていけば階層が付くのは解ったけど、上位に置くべきフィールドが元表に存在しなかったらどうする?
実際、この元表にあるのは「日付」フィールドのみで、「年」も「月」も無し。
元表には日付フィールドだけ
ここで登場するのが「グループ化」コマンドです。
「日付」フィールドの上位に置くべき「月」フィールドや、「商品」フィールドの上位に置きたい「カテゴリー」フィールドが元表に無くても、ピボットテーブル側で分類と新規フィールドの作成ができるのです。


グループ化|日付(自動グループ化と手動グループ化)

日常的に追加されるデータを元にピボットテーブルを作るなら、「日付」を月や年単位でまとめるのは必須事項と言ってもいいでしょう。

【日付の自動グループ化】

Excel2013、2016をお使いの方は、「日付」フィールドを作業ウィンドウの行エリアへドラッグしてみて下さい。「日付」の配置と同時に「年」と「四半期」の上位フィールドが自動で生成されましたね。
最も実装頻度の高いグループ化なので、Excelが手間を省いてくれたわけです。
(元表の「日付」フィールドに属すデータが年度を跨いでいない場合は、自動グループ化で生成される上位フィールドは「月」だけになります)
日付を行エリアにドラッグすれば自動でグループ化
ピボットテーブルの方はどうなっているかというと、「年」→「四半期」の順に階層構造ができ、日付は月単位でまとめられ、「第1~第4四半期」の各フィールド内に格納されています。
自動でグループ化したピボットテーブルの日付
「四半期」のくくりが要らない、個別の日付を表示したい、など、自動グループ化を手直ししたい時は、手動でグループ化をやり直しましょう。

【日付の手動グループ化】
グループ化された日付の項目セルをどれか選択して、「ピボットテーブル ツール」ー「分析」の「グループ」にある「フィールドのグループ化」をクリック。
フィールドのグループ化
グループ化」ダイアログボックスが開いたら、行ラベルに表示させたい単位をクリックして選択します。選択の解除もクリックでできます。
「四半期」を外して、個別の日付を表示させる場合は、「日」「月」「年」を選択。
グループ化ダイアログで日・月・年を選択
月単位だけを表示したいなら、「月」「年」を選択。
グループ化ダイアログで年・月を選択

「第1四半期」を4月~6月にする

「四半期」フィールドを残す場合、日本の会計年度は4月開始なので、修正が必要です。

スポンサーリンク

「第1四半期」~「第4四半期」の項目名を変更しますが、同じ名称への変更はできませんので、とりあえず「第1四半期」を「第四半期」のように1文字削除しておきます。
第1四半期を1文字削除
「第2四半期」→「第1四半期」、「第3四半期」→「第2四半期」、「第4四半期」→「第3四半期」、最初に文字削除しておいた「第四半期」を「第4四半期」に修正。
第2~第4四半期をそれぞれ修正
行ラベル▼」をクリックして、「フィールドの選択」が「四半期」になっているのを確認し、「昇順」ボタンをクリックします。
昇順をクリックしてフィールドを並べ替え
これで完了。
第1四半期が4月~6月になった

自由な日数分をグループ化する

1週間分、或いは10日分をまとめて集計したいというような場合では「グループ化」ダイアログで「」を選択し、「日数」でまとめたい数値を指定。週単位にしたいなら、「」にします。「開始日」~「最終日」でグループ化を実行する期間を指定します。
グループ化ダイアログでまとめたい日数を指定

曜日単位でグループ化する

曜日ごとにまとめて集計したい、という場合は、元表に「曜日」のフィールドを設けておく必要があります。
「曜日」フィールドを挿入したい列の右隣のセルを選択して右クリック→「挿入」→「テーブルの列」で列を増やし、フィールド名を「曜日」とします。
「曜日」の最初のセルに、シリアル値から曜日を求めるWEEKDAY関数を「=WEEKDAY([@日付])」と入れます。
ダイアログを使うなら、最初の引数「シリアル値」で「日付」フィールドの最初のセルをクリック。種類は指定せずに「OK」。
WEEKDAY関数で曜日を表示
「表示形式」を「ユーザ定義」で「aaaa」にして曜日表記に変えます。
表示形式をユーザ定義でaaaaと指定
元表側での操作はこれでOK。
元表に曜日のフィールドが作成できた
新しくピボットテーブルを作るか、既製のものを更新します。
「ピボットテーブルのフィールド」作業ウィンドウで、「曜日」を行エリアに配置。
曜日ごとに集計されました。
ピボットテーブルで曜日ごとの集計ができた

 

グループ化|その他のフィールド

「日付」以外のフィールドのグループ化についても幾つか例を挙げておきます 。
「ふじ」「紅玉」「シナノゴールド」という品名が元表に並んでいたとしても、自動で「りんご」にグループ化されることはありません。文字の項目は手動でまとめます。

商品をカテゴリー分けする

同じ属性の商品項目をグループ化し、より大きなカテゴリー別の集計を表示します。
商品名の項目をグループ化する
まとめたい項目を選択し、「ピボットテーブル ツール」ー「分析」の「グループ」にある「グループの選択」ボタンをクリック。
グループ化したい範囲を選択して「グループの選択」をクリック
「グループ1」というカテゴリーが生成されて、選択した3項目が格納されました。
選択していない項目もそれぞれ同名の上位フィールドの中に収まる形になりましたが、これは仮の宿のようなものと考えましょう。
ピボットテーブルで選択範囲がグループ化された
「グループ1」という名称は数式バーで変えておきましょう。

更に、グループ化したい項目をCtrlキーを押しながら選択します。選択するのは上位、下位どちらの項目でもOK。「グループの選択」をクリックし、グループ化。
次にグループ化したい項目を選択
残りも同様にグループ化。カテゴリー別に分類できました。
商品がカテゴリー別にグループ化できた

グループ化したフィールドを更にグループ化

グループ化したフィールドを束ねて、更に一つ上のフィールドにまとめることもできます。下図は、既に「りんご」と「いちご」というカテゴリーにグループ化してあるフィールドをまとめて選択して「グループの選択」を実行し、「果物」という大カテゴリーでグループ化したものです。
グループ化したフィールドを更にグループ化
このように、元表にないフィールドを上位に重ねて作っていくことができるわけです。

上位フィールドの展開と折りたたみ/移動

上位フィールドは、[+][-]をクリックで切り替えて、格納されいる項目群を展開させたり、折りたたんだりして、集計表を伸縮させます。
上位フィールドの+ーで展開と折りたたみ

グループ化したフィールドの並べ順を変えたい時は、移動させる項目名の上で右クリック、出てきたメニューの「移動」から、「下(上)へ移動」か「末尾(先頭)へ移動」を選びます。
グループ化したカテゴリーの並べ替え

グループ化の解除

後々、「集計アイテム」を追加する時などでグループ化の解除が必要になることがありますので、手順を書いておきます。

グループ化された項目名を選択。複数ある場合はCtrlキーを押しながらクリックして選択します。
グループ化された項目名を選択
「ピボットテーブル ツール」ー「分析」の「グループ」にある「グループ解除」ボタンをクリック。
グループ解除ボタンをクリック
グループ名が消え、項目がグループ化前の状態に戻りました。
「ピボットテーブルのフィールド」作業ウィンドウにリストアップされたフィールド名からもグループ化で追加されたフィールド名がなくなっているはずです。
項目名がグループ化前に戻った


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。