Power Query 、Power Pivot 入門（4）、階層の作成、階層を使用したピボットテーブルの作成

ーーーーーーーーーーーーーーーーーーーーー2026年4月14日執筆ーーーーーーーーーーーーーーーーー

Power Query 、Power Pivot 入門（4）、階層の作成、階層を使用したピボットテーブルの作成のPodcast

下記のPodcastは、Geminiで作成しました。

はじめに

Microsoft Excelにおけるデータ分析の進化は、Power QueryとPower Pivotという二つの強力なエンジンの導入によって劇的な転換点を迎えました。大量のデータを効率的に処理し、単なる表計算の域を超えた多次元分析を可能にするこれらのツールは、現代のビジネスインテリジェンスにおいて中心的な役割を担っています。本報告では、特にデータの「階層構造」に焦点を当て、その構築からピボットテーブルでの高度な活用方法までを網羅的に解説します。

階層構造がデータ分析にもたらすパラダイムシフト

データ分析における「階層（Hierarchy）」とは、データ項目間に存在する論理的な親子関係や順序関係を定義した構造のことです。例えば、時間は「年 ＞ 四半期 ＞ 月 ＞ 日」という自然な階層を持ちます。同様に、組織構造や製品カテゴリ、地理情報なども階層として定義することが可能です。階層を正しく構築することは、単にデータを整理するだけでなく、分析者が直感的にデータを掘り下げ（ドリルダウン）、あるいは俯瞰（ドリルアップ）することを可能にします。

従来のExcelピボットテーブルでは、分析の軸を切り替えるたびにフィールドリストから項目を個別にドラッグ・アンド・ドロップする必要がありました。しかし、Power Pivotで階層を定義しておくことにより、ピボットテーブル上での操作は極めてシンプルになります。プラスボタンやマイナスボタンをクリックするだけで、年間の売上推移から月単位の詳細へ、あるいは地域全体の数字から個別の店舗へと、思考のスピードを止めることなくデータの探索が行えるようになります。

また、階層化はデータモデルの設計図としての役割も果たします。大規模なデータセットでは数百もの列が存在することも珍しくありませんが、関連する項目を階層としてまとめることで、レポート作成者が迷うことなく適切な分析軸を選択できるようになります。これは、組織内でのデータガバナンスを高め、セルフサービスBIを成功させるための重要な鍵となります。

Power Queryによる階層構築のためのデータプレパレーション

階層を構築する前段階として、Power Queryを用いた緻密なデータ整形が不可欠です。階層の各レベルとなるデータが適切に分離され、正しいデータ型で定義されていなければ、後の分析段階で予期せぬエラーや誤った集計結果を招くことになります。

特に時間軸の階層を作成する場合、元データに存在する「日付列」だけでは不十分な場合が多いです。Power Queryを活用して、日付から「年」「四半期」「月」「週」「曜日」といった属性を抽出し、独立した列として作成することが推奨されます。これにより、Power Pivot側でこれらの項目を組み合わせた柔軟な階層を定義できるようになります。

Power Queryでこれらの処理を行う最大の利点は、データが更新されるたびに同じ整形プロセスが自動的に適用されることです。一度堅牢なクエリを構築してしまえば、分析者は手作業による加工から解放され、本来の目的であるインサイトの発見に集中できるようになります。

Power Pivotによるデータモデルの高度化

Power Queryでの準備が整ったら、データをPower Pivotのデータモデルに読み込みます。Power Pivotは、複数のテーブルをリレーションシップで結びつけ、巨大なデータセットをインメモリで高速処理するエンジンです。ここで階層を作成することにより、フラットな表形式のデータが「分析可能な構造体」へと進化します。

ダイアグラムビューでの直感的な設計

階層の作成において最も効率的なのは、Power Pivotウィンドウの「ダイアグラムビュー」を活用する方法です。ダイアグラムビューは、各テーブルを視覚的なボックスとして表示し、フィールド間の関係性を一目で把握できるように設計されています。

1. ビューの切り替え: Power Pivotウィンドウのホームタブにある「表示」セクションから「ダイアグラムビュー」を選択します。
2. 階層の作成ボタン: 各テーブルボックスの右上にある「階層の作成」アイコンをクリックします。これにより、テーブルの末尾に新しい階層の「入れ物」が作成されます。
3. 項目の配置: 階層に含めたい列を、親レベルから子レベルの順（例：年 ＞ 四半期 ＞ 月）でドラッグ・アンド・ドロップします。

この際、階層の名前を「カレンダー」「製品分類」「組織」といった直感的な名称に変更することが重要です。ピボットテーブルのフィールドリストには、個別の列名とともにこの階層名が表示されるため、ユーザーの利便性に直結します。

カレンダー階層における「並べ替え」の重要性

階層を作成する際、多くの初心者が直面する問題が「月」の並べ替え順です。例えば「1月、2月、3月...」というテキスト形式の月名は、デフォルトでは五十音順やアルファベット順に並んでしまいます。これを防ぐためには、「列による並べ替え（Sort by Column）」の設定が必須となります。

具体的には、テキストとしての「月名」列を選択し、リボンの「列による並べ替え」をクリックした後、基準となる数値型の「月番号」列（1〜12の値を持つ列）を指定します。この設定を行うことで、階層内でドリルダウンした際にも、データが時系列に沿って正しく表示されるようになります。

このソート順の制御は、階層構造をビジネスレポートとして実用的なものにするための「細部へのこだわり」であり、プロフェッショナルなレポート作成には欠かせないステップです。

階層を使用したピボットテーブルの構築と操作

データモデル内で階層を定義すると、Excelのピボットテーブルフィールドリストの表示が変化します。個別のフィールドとは別に、階層アイコンが表示されるようになります。

レポートへの配置とドリル操作

作成した階層をピボットテーブルの「行」エリアに配置すると、最上位レベルのデータ（例：年）が最初に表示されます。データの横にある「＋」アイコンをクリックすることで、次の階層レベル（例：四半期）へと詳細が展開されます。

この操作の利点は、単にデータが見やすくなるだけではありません。ピボットテーブルは、現在表示されているレベルに応じて自動的に集計範囲を調整します。例えば、年レベルでは年間の合計売上を表示し、四半期レベルに展開した瞬間に、その四半期ごとの内訳へと計算を切り替えます。これは、手作業でフィルタリングを繰り返す従来の分析手法と比較して、圧倒的なスピード感をもたらします。

高度な分析：DAX関数と階層の融合

さらに高度な分析を行う場合、DAX（Data Analysis Expressions）関数と階層を組み合わせることで、表示されているレベルに応じた動的な計算が可能になります。特にISINSCOPE関数は、現在どの階層レベルがフォーカスされているかを判定するために非常に有効です。

例えば、「年レベルでは前年比を表示し、月レベルでは予算達成率を表示する」といった、コンテキストに応じたメジャー（計算指標）を作成することができます。これにより、一つのピボットテーブルで多層的なKPIを同時に管理できるようになり、ダッシュボードの密度と価値が飛躍的に高まります。

コード スニペット

Dynamic_KPI := 
IF(
    ISINSCOPE('Calendar'[Month]),
   ,
   
)

このような動的な制御は、標準的なExcel機能だけでは実現が困難であり、Power Pivotと階層構造を組み合わせることで初めて可能になる領域です。

ベストプラクティスとパフォーマンスの最適化

大規模なデータモデルを扱う場合、階層の設計がパフォーマンスに影響を与えることがあります。不必要に多くのレベルを持つ階層や、非常に高いカーディナリティ（ユニークな値の数が多い）を持つ列を階層に含めると、メモリ消費が増大し、ピボットテーブルの動作が重くなる可能性があります。

分析に真に必要のないレベルは削ぎ落とし、シンプルかつ強力な構造を維持することが、エンドユーザーにとって使いやすいレポートを作成するための鉄則です。また、階層の基となる列がフィールドリストに重複して表示されないよう、元の列を「クライアントツールから非表示」に設定することも、インターフェースを整理するための有効なテクニックです。

結論と今後の展望

Power QueryとPower Pivotを駆使した階層の作成は、Excelを単なる帳票作成ツールから、強力な意思決定支援システムへと昇華させます。データを論理的な構造に整理し、インタラクティブなドリル操作を可能にすることは、分析の深さを変えるだけでなく、データから価値ある知見を引き出すための「視座」を組織に提供します。

今後、AI技術の進展により、データモデルの自動構築や最適な階層のレコメンド機能などがさらに強化されることが予想されます。しかし、ビジネスの本質的な文脈（ドメイン知識）に基づいた階層設計は、依然として分析者の腕の見せ所です。本報告で解説した基本と応用をマスターすることで、読者の皆様がデータの海から真に意味のあるインサイトを導き出せるようになることを確信しています。

参考資料

1. Power Pivot: Powerful data analysis and data modeling in Excel, https://support.microsoft.com/en-us/office/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045
2. Create and manage hierarchies in Power Pivot, https://support.microsoft.com/en-us/office/create-and-manage-hierarchies-in-power-pivot-86080351-460d-4589-9800-4740f951e737
3. Hierarchies in Power BI - Power BI, https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-hierarchies
4. Excel Power Pivot - Hierarchies, https://www.tutorialspoint.com/excel_power_pivot/excel_power_pivot_hierarchies.htm
5. Data profiling tools in Power Query, https://learn.microsoft.com/en-us/power-query/data-profiling-tools
6. Creating a Date Table in Power Query, https://www.sqlbi.com/articles/creating-a-simple-date-table-in-da-power-query/
7. Data types in Power Query, https://learn.microsoft.com/en-us/power-query/data-types
8. Date column transformation in Power Query, https://learn.microsoft.com/en-us/power-query/add-date-column
9. Relationship basics in Power Pivot, https://support.microsoft.com/en-us/office/create-a-relationship-between-tables-in-excel-fe1b6cd7-d9f2-45e0-b24d-5aa9ef9108b4
10. Performance modeling in Power BI and Power Pivot, https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
11. Automating data preparation with Power Query, https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
12. Introduction to Power Pivot engine, https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg413433(v=sql.110
13. Navigating the Power Pivot Diagram View, https://support.microsoft.com/en-us/office/navigate-the-power-pivot-window-176c1251-512c-47bc-9400-f655610ec1f2
14. Display Diagram View in Excel, https://support.microsoft.com/en-us/office/view-the-tables-and-columns-in-a-data-model-in-power-pivot-8700049e-644b-4654-94e4-7d5817c1809a
15. Creating hierarchies manually, https://www.excelcampus.com/pivot-tables/power-pivot-hierarchies/
16. Best practices for data modeling hierarchies, https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
17. Naming conventions for Data Models, https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-naming-conventions
18. Sort by Column in Power BI and Power Pivot, https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column
19. Sorting months chronologically in Power Pivot, https://www.sqlbi.com/articles/sorting-months-in-power-bi/
20. Fix month sorting in Excel Pivot Tables, https://www.contextures.com/excelpivotmonthsorting.html
21. Sorting weekdays in custom order, https://community.powerbi.com/t5/Desktop/Sort-by-Day-of-the-Week/td-p/11832
22. Advanced sorting techniques in DAX, https://www.daxpatterns.com/custom-sorting/
23. Data modeling best practices, https://learn.microsoft.com/en-us/power-bi/guidance/data-modeling-best-practices
24. Understanding the PivotTable Field List, https://support.microsoft.com/en-us/office/use-the-field-list-to-arrange-fields-in-a-pivottable-44445353-094d-444a-9391-7667232e0c0a
25. Drill down and drill up in a PivotTable, https://support.microsoft.com/en-us/office/drill-into-pivottable-data-e565922e-a0e2-4f38-895c-9c748c909673
26. Interactive data exploration with Power Pivot, https://support.microsoft.com/en-us/office/use-quick-explore-to-analyze-data-model-data-75e9668d-294c-47da-90d2-97b69234850c
27. ISINSCOPE Function (DAX) documentation, https://learn.microsoft.com/en-us/dax/isinscope-function-dax
28. Advanced DAX for Hierarchies, https://www.sqlbi.com/articles/distinguishing-filters-from-hierarchies/
29. Dynamic measures based on visual context, https://www.sqlbi.com/articles/isinscope-vs-isfiltered/
30. Tabular modeling performance, https://learn.microsoft.com/en-us/analysis-services/tabular-models/tabular-models-ssas
31. Hide columns from client tools in Power Pivot, https://learn.microsoft.com/en-us/analysis-services/tabular-models/hide-or-freeze-columns-ssas-tabular
32. Designing effective field lists, https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-field-list-organization
33. Scalability in Power Pivot models, https://support.microsoft.com/en-us/office/data-model-specification-and-limits-19da7841-2d4d-4e3a-9f5e-1466007fa021
34. VertiPaq engine compression, https://www.sqlbi.com/articles/what-is-the-vertipaq-engine/
35. The evolution of Excel as a BI tool, https://www.microsoft.com/en-us/microsoft-365/blog/2015/08/27/the-evolution-of-excel-and-power-bi/
36. Data Literacy for decision making, https://www.gartner.com/smarterwithgartner/a-data-and-analytics-leaders-guide-to-data-literacy
37. Microsoft 365 Excel Release Notes, https://learn.microsoft.com/en-us/officeupdates/current-channel
38. DAX Guide - Sorting, https://dax.guide/functions/sorting/
39. DAX Reference - Filter Functions, https://learn.microsoft.com/en-us/dax/filter-functions-dax
40. Power BI and Excel Data Modeling comparison, https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel

ーーーーーーーーーーーーーーーーーーーーー2021年8月29日執筆ーーーーーーーーーーーーーーーーー

はじめに

　Microsoftチュートリアルに沿って学習する第3回目としてのPower Query 、Power Pivot 入門（3）では、主に次のことを学びました。

　今日のPower Query 、Power Pivot 入門（4）では、次のことを学習します。

階層の作成

　Power Query 、Power Pivot 入門（3）で使用したファイル（Microsoft-pwerpviot-tutorial.xlsx）をダブルクリックして起動してください。

　カレンダーデータ（年、月、週、日、曜日、時間など）、地理データ（日本全体、都道府県、市区町村、番地など）のように階層構造を持つものがデータの中には多くあります。

　ここで扱うデータの中にも階層構造を持つものがあり、「Sports」と「Locations」の２つの階層を作成していきます。

　そうすることにより、ピボットテーブルを作成する際に大変便利になります。

Sport階層の作成

　リボンの上部メニューある「Power Pivot」⇒「管理」⇒「ダイアグラムビュー」と順にクリックして、ダイアグラムを表示させます。

　「Events」オブジェクトの右上にカーソルを持ってゆくと、表示される「階層の作成」をクリックすると、「階層１（レベルを追加するには、ここに列をドラッグします…）」が「Events」の一番下に作成されます。

　「Events」の項目を全部表示させ、「Sport」、「Discipline」、「Event」の３つを選択します。

　同時に複数選択する方法は、「Ctrl」キーを押しながら、各項目をクリックすることにより選択できます。

　３つ選択した状態でドラッグして「階層１」が薄青くなった状態で、その上でドロップします。

　階層１の下に「Sport」、「Discipline」、「Event」の項目が作成されます。

　作成される順序は、上から「Sport」、「Discipline」、「Event」の順であることを確認します。

　もし違っている場合は、項目をドラッグして上述の順番になるように入れ替えます。

　「階層１」の上でダブルクリックして、名前を「SDE」に変更します・

Locations階層の作成

　「Hosts」内の「Season」、「NOC_CountryRegion」、「City」、｛「EditionID」についても階層を作成し、名前を「Locations」とします。

階層を使用したピボットテーブルの作成

　ダイアグラムビューの上にあるメニューで「ピボットテーブル」⇒「ピボットテーブル」と選択して順にクリックすると、「ピボットテーブルの作成」ウインドウが表示されますので、「新しいシート」にチェックが入っていることを確認し、「Ok」をクリックします。

　新しいシート（Sheet2）が立ち上がり、ピボットテーブルの作成画面がでます。

　右にある「ピボットテーブルのフィールド」画面で、「列」に「Locations」を、「行」に「SDE」を、値とフィルターに「Medal」を、上のオブジェクトを展開して表示される項目から、それぞれをドラッグ＆ドロップしてやると表が作成されます。

　作成されたピボットテーブルは縦長のテーブルなので、フィルター機能により上位１０位までを表示させるようにします。

　「行ラベル　▼」の「▼」をクリックし、「値フィルター」⇒「トップテン」をクリックすると、「トップテンフィルター」が表示されますので、表示部分で「カウント/ Discipline」、「上位」、「10」、「項目」を選択記入して、「OK」をクリックします。

　作られたピボットテーブルは次のようになります。

　この行ラベルの競技種目の前にある「＋」をクリックすることにより展開し、例えば、「Aquatics」を展開すると、「Diving」、「Swimming」、「Synchronized S.」、「Water Polo」があり、さらに、「Diving」を展開すると、「plain high diving」、「plunge for distance」、「synchronized diving 10m platform」、「synchronized diving 3m springboard」があり、「Water Polo」を展開すると「Water Polo」のみが表示され、その競技種目の細かなデータまで見ることができます。

階層を使わずにピボットテーブルを作成

　先ほどと同様に、ダイアグラムビューの上にあるメニューで「ピボットテーブル」⇒「ピボットテーブル」と選択して順にクリックし、「ピボットテーブルの作成」ウインドウが表示されますので、「新しいシート」にチェックが入っていることを確認し、「Ok」をクリック、テーブル名を「Sheet3」とし、ピボットテーブル3とピボットテーブルのフィールを表示させます。

　ピボットテーブルのフィールドの「列」に 「Season」、「City」、「NOC_CountryRegion」、「EditionID」をこの順序で入れ込みます。

　また、「行」に「Sport」、「Discipline」、「Event」をこの順序で入れ込みます。

　前のピボットテーブルと同様に、「値」と「フィルター」に「Medals」の「Medal」を入れます。

　さらに、同様にフィルター機能でトップテンを抜き出し、競技種目などを折りたたんで、前のピボットテーブルと同じように表示させます。

　下の画像は、階層を使っていないピボットテーブル（左）と階層を使ったピボットテーブル（右）を対比して表示させています。

おわりに

　如何だったでしょうか？

　階層の作成、Sport階層の作成、Locations階層の作成、階層を使用したピボットテーブルの作成、階層を使わずにピボットテーブルを作成などについて解説してきました。

　この記事が少しでもあなたにとって役に立てればこれほど嬉しいことはありません。

以上です。