Excelのピボットテーブルでグループ内の順位を項目にしたクロス表を作る
Yahoo!知恵袋にExcelの試験問題で問題番号別の出題傾向を求める質問がありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14311558284
そこで、問題番別(グループ別)の分類別出題回数の順位の表を作成する方法をメモしました。
1. 元データ
| 問題番号 | 年度 | 分類 |
|---|---|---|
| 1 | 2020 | Pivot Table |
| 2 | 2020 | Power Query |
| 3 | 2020 | VBA |
| 1 | 2021 | Pivot Table |
| 2 | 2021 | Power Pivot |
| 3 | 2021 | DAX |
| 1 | 2022 | Pivot Table |
| 2 | 2022 | Power Query |
| 3 | 2022 | Power Query |
| 1 | 2023 | Power Query |
| 2 | 2023 | Power Pivot |
| 3 | 2023 | Power Query |
| 1 | 2024 | Power Query |
| 2 | 2024 | DAX |
| 3 | 2024 | Power Query |
Excelでテーブル名を"問題一覧"にし、次の手順でPower Queryに取込みます。
(1) "問題一覧"の1つのセルを選択し、[データ]-[テーブルまたは範囲から]をクリックします。
(2) [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"を選択し[OK]。
2. ピボットテーブルによる解
上記表をそのままピボットテーブルにすると以下のような集計をすることができます。
| 問題番号 | DAX | Pivot Table | Power Pivot | Power Query | VBA | 総計 |
|---|---|---|---|---|---|---|
| 1 | 3 | 2 | 5 | |||
| 2 | 1 | 2 | 2 | 5 | ||
| 3 | 1 | 3 | 1 | 5 | ||
| 総計 | 2 | 3 | 2 | 7 | 1 | 15 |
作成方法
(1) [データ]-[クエリと接続]で"問題一覧"を右クリックし、[読込先]で"ピボットテーブル"、"新規ワークシート"を選択し[OK]。
(2) ピボットテーブルのフィールドを次のように設定。
列:分類、行:問題番号、Σ値:分類(個数)
(3) 年度を右クリックし、[スライサーとして追加]
これで傾向を知ることはできますが、出題回数の順位別に分類を表したい場合は、次のような表が考えられます。
3. 本記事で集計する表
| 問題番号 | 1位 | 2位 | 3位 |
|---|---|---|---|
| 1 | Pivot Table | Power Query | |
| 2 | Power Query, Power Pivot | DAX | |
| 3 | Power Query | VBA, DAX |
特徴
1) 順位(値)がピボットテーブルの列項目である。
2) 分類(文字列)がピボットテーブルのΣ値である。
この表を"問題一覧"があるExcelに作成します。
(余談 : この表は定性的な表なので、分析をするためには2.のピボットテーブルの方が良いと思います。二極化志向の人向けには、定性的な表が良いかも知れません。)
4. 本記事の順位を求める方法
次の2つの方法をメモしました。
1) Power Query M : Table.AddRankColumn()を使用する。https://learn.microsoft.com/ja-jp/powerquery-m/table-addrankcolumn
2) DAX : RANKX()を使用する。メジャーはピボットテーブルの項目に使えないので、計算列で使用する。https://learn.microsoft.com/ja-jp/dax/rankx-function-dax
5. Power Queryで順位を求める
1-1) 回数順位 Power Query
'問題一覧'から[回数]と[順位]の列を持った表を作成するクエリです。
(1) [データ]-[クエリと接続]で適当なクエリを右クリックし[編集]でPower Queryエディタを開く。
(2) '問題一覧'クエリを右クリックし[参照]、プロパティの名前を'回数順位'に変更する。
(3) '問題番号'と"分類"列をCtrlキーを押しながら選択し、[ホーム]-[グループ化]で新しい列名に"回数"、操作に[行数のカウント]を選択し[OK]
(4) '問題番号'を選択し、[ホーム]-[グループ化]で新しい列名に"サブテーブル"、操作に[すべての行]を選択し[OK]
(5) [ホーム]-[詳細エディタ]で次のようにコードを修正して、[OK]
let
ソース = 問題一覧,
グループ化された行 = Table.Group(ソース, {"問題番号", "分類"}, {{"回数", each Table.RowCount(_), Int64.Type}}),
グループ化された行1 = Table.Group(グループ化された行, {"問題番号"}, {{"サブテーブル", each _, type table [問題番号=nullable number, 分類=nullable text, 回数=number]}}),
// 各サブテーブル内でランク列を追加
ランク追加 = Table.TransformColumns(
グループ化された行1,
{
{"サブテーブル", each Table.AddRankColumn(_, "順位", {"回数", Order.Descending})}
}
)
in
ランク追加
(6) サブテーブルの右の展開アイコンをクリックし、[さらに読み込む]をクリックし、"問題番号"と"元の列名をプレフィックスとして使用します"のチェックを外し、[OK]
(7) "分類","回数","順位"をCtrlを押しながら選択し、[変換]-[データ型の検出]
(8) [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"ピボットテーブル"と"新規テーブル"と"データモデルに追加"を選択し[OK]。
参考 : '回数順位'の完成コード
let
ソース = 問題一覧,
// 問題番号と分類でグループ化し、行数から回数を得る。
グループ化された行 = Table.Group(ソース, {"問題番号", "分類"}, {{"回数", each Table.RowCount(_), Int64.Type}}),
// 問題番号毎に順位を求めるため、問題番号毎のサブテーブルを作る。
グループ化された行1 = Table.Group(グループ化された行, {"問題番号"}, {{"サブテーブル", each _, type table [問題番号=nullable number, 分類=nullable text, 回数=number]}}),
// 各サブテーブル内でランク列を追加する。
ランク追加 = Table.TransformColumns(
グループ化された行1,
{
{"サブテーブル", each Table.AddRankColumn(_, "順位", {"回数", Order.Descending})}
}
),
// ランク列が追加されたサブテーブルを元に戻す。
#"展開された サブテーブル" = Table.ExpandTableColumn(ランク追加, "サブテーブル", {"分類", "回数", "順位"}, {"分類", "回数", "順位"}),
変更された型 = Table.TransformColumnTypes(#"展開された サブテーブル",{{"分類", type text}, {"回数", Int64.Type}, {"順位", Int64.Type}})
in
変更された型
参考情報
[Power BI] Table.AddRankColumnで月別ランク表を作る
https://qiita.com/spumoni/items/43e2a530f2c3b0386073
1-2) 順位 Power Query
同位順位があると回数順位表に順位の抜けが生じます。集計表で抜けがある順位を空白値で表示するために、抜けのない順位表を作成します。
(1) Power Queryエディタの[ホーム]-[新しいソース]-[その他のソース]-[空のクエリ]で、プロパティの名前を'順位'に変更する。
(2) [ホーム]-[詳細エディタ]でコードを次のように全て書き換える。
let
// データの参照
ソース = 回数順位,
// "順位" 列のユニーク値を取得
順位リスト = List.Distinct(ソース[順位]),
// 順位の最小値と最大値を取得
最小順位 = List.Min(順位リスト),
最大順位 = List.Max(順位リスト),
// 1 から 最大順位 までのリストを作成
完全順位リスト = List.Numbers(最小順位, 最大順位 - 最小順位 + 1),
// テーブル化
順位テーブル = Table.FromList(完全順位リスト, Splitter.SplitByNothing(), {"順位"}, null, ExtraValues.Error),
変更された型 = Table.TransformColumnTypes(順位テーブル,{{"順位", Int64.Type}})
in
変更された型
(3) [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"接続の作成のみ"と"データモデルに追加"を選択し[OK]。
2) リレーション Power Pivot
(1) Excelの[Power Pivot]-[管理]-[ダイアグラムビュー]、または[データ]-[データモデル]-[リレーションシップ]で次のようにリレーションを取ります。
'回数順位’[順位] *:1 '順位'[順位]
3) メジャー
ピボットテーブルのΣ値には項目('回数順位'[分類])を表示できないことと、同一順位の分類が複数ある場合があるので、以下のメジャーを作成します。
(1) '回数順位'から作成したピボットテーブルを選択し、ピボットテーブルのフィールドを表示し、'回数順位'を右クリックし、[メジャーの追加]で次のように入力する。
メジャーの名前 : M分類q
数式 :
=IF(
ISFILTERED('回数順位'[問題番号]) && ISFILTERED('順位'[順位]),
CONCATENATEX('回数順位', '回数順位'[分類], ",")
)
4) ピボットテーブル
ピボットテーブルのフィールドの[すべて]をクリックし、以下のように項目を展開します。
列 : '順位'[順位]
行 : 問題番号
Σ値 : M分類q
6. DAXで順位を求める
1) 回数 Power Query
'問題一覧'から[回数]の列を持つ表を作成するクエリです。
(1) [データ]-[クエリと接続]で適当なクエリを右クリックし[編集]でPower Queryエディタを開く。
(2) "問題一覧"クエリを右クリックし[参照]、プロパティの名前を"回数"に変更する。
(3) "問題番号"と"分類"列をCtrlキーを押しながら選択し、[ホーム]-[グループ化]で新しい列名に"回数"、操作に[行数のカウント]を選択し[OK]
(4) [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"ピボットテーブル"と"新規テーブル"と"データモデルに追加"を選択し[OK]。
2) 順位 Power Pivot 計算列
計算列で'回数'表に順位の列を追加します。
(1) Excelの[Power Pivot]でPower Pivot for Excelを起動し、[管理]-[データビュー]-[回数]タブ-[列の追加]をダブルクリックし名前を"C順位"に変更。
(2) [C順位]の下のセルを選択し、数式バーに次の式を入力。
=RANKX( // ランクを求める
FILTER( // ランクの対象範囲を'回数'の行コンテキストの[問題番号]の行に絞り込む
'回数', // フィルタ対象
'回数'[問題番号]=EARLIER('回数'[問題番号]) // フィルタ条件、EARLIER(...)は行コンテキストの問題番号
),
'回数'[回数], // ランクを求める対象列
,
DESC, // 降順
Skip // 同一順位の順位をスキップ
)
関連記事
Excel メジャーのRANKXで順位を求める方法(イテレータ関数の中のコンテキスト)の11. 式の中に適用されるコンテキスト
https://bigarrow.tea-nifty.com/blog/2025/02/post-1ee8c7.html
3) メジャー
ピボットテーブルのΣ値には項目('回数順位'[分類])を表示できないことと、同一順位の分類が複数ある場合があるので、以下のメジャーを作成します。
(1) '回数'から作成したピボットテーブルを選択し、ピボットテーブルのフィールドを表示し、'回数'を右クリックし、[メジャーの追加]で次のように入力する。
メジャーの名前 : M分類c
数式 :
=IF(
ISFILTERED('回数'[問題番号]) && ISFILTERED('回数'[C順位]),
CONCATENATEX('回数', '回数'[分類], ",")
)
4) ピボットテーブル
ピボットテーブルのフィールドの[すべて]をクリックし、以下のように項目を展開します。
列 : '回数'[C順位]
行 : 問題番号
Σ値 : M分類c
7. サンプルファイル
8. 余談
冒頭のYahoo!知恵袋の問題一覧はMOS(Microsoft Office Specialist)かも知れませんね。MOSの出題範囲にVBAはありますが、Power QueryやPower Pivotは入っていません。
Excelで集計の仕組みを作ることを主業務にしている人はVBA ADO(SQL)とか、他の言語の方が良いのでしょう。
でも一般的には集計の仕組みを利用している人の方が多いと思います。そして、セルアドレスの間違いで苦労している人も多い気がします。テーブルやピボットテーブルはその解決策として良い選択肢だと思います。それに加えて、モダンExcelの仕組みも良いと思っています。小さな表であれば、セルアドレスの方が直感的で速いですが、セルアドレスに苦しみを感じたらモダンExcelが良い気がします。
8. テキストマイニングの参考情報(外部リンク)
計量テキスト分析ツール KH Coder
https://khcoder.net/
30分で分かる!Pythonテキストマイニング入門 〜環境構築から基本分析まで〜
https://qiita.com/Tadataka_Takahashi/items/c67f51ed424c9367e30d
【日本語WordCloud】ExcelデータからPython/Pandasで頻出キーワードを可視化する
https://pala-ghe.com/japanese_wordcloud_excel_python_pandas_keywords/?utm_source=chatgpt.com
テキストマイニングとは?Power BIでワードクラウドを表現する方法!
https://frogwell.co.jp/blogs/pbi-textmining/
Power BIのText Analyticsは、無料では使えません。Power BI Premium Per User 2,998\/月が必要だと思われます?
https://frogwell.co.jp/blogs/powerbi-textmining/
Power BI Desktop
https://www.microsoft.com/ja-jp/power-platform/products/power-bi/desktop
Datasets
https://www.kaggle.com/datasets
https://www.kaggle.com/datasets/shivamchaudharys/tmdb-top-rated-movi-10k?resource=download
「無人航空機の飛行に関する許可・承認の審査要領(カテゴリー2飛行)」等の一部改正案に関する意見募集の結果について
https://public-comment.e-gov.go.jp/pcm/1040?CLASSNAME=PCM1040&id=155241249&Mode=1
| 固定リンク
« Excel Power Queryで1レコードが複数行、複数列に分かれている表の整形方法 | トップページ | FanCtrlがWindows Defenderでブロックされた(2025/03/12) »





コメント