[[20220616122630]]　『ピボットテーブルの集計値同士の計算』（ばんしゅう）　ページの最後に飛ぶ

『ピボットテーブルの集計値同士の計算』（ばんしゅう）

ピボットテーブルで、集計値同士で計算ができないでしょうか？
作りたいデータは次のようなかちです。

◆集計前のデータ

 ID	名前	チーム　　回答
 001	田中	　　A	　　有
 002	増本	　　B	　　有
 003	佐藤	　　C	　　無
 004	近藤	　　C	　　有
 005	菱田	　　E	　　無

◆ピボットテーブルで集計したデータ

 カウント / ID数	列ラベル：回答		 
 　チーム　　無	有	総計
 　　A	　　1	0	1
 　　B	　　22	58	80
 　　C	　　12	33	45
 　　D	　　1	2	3
 　　E	　　7	46	53
 　　F	　　7	17	24
 　　G	　　47	14	61
 　総計	　　97	170	267

 ◆作りたい集計データ
 カウント / IＤ数	列ラベル：回答			 
 チーム	無	有	総計	回答有無の割合
 A	1		1	100%
 B	22	58	80	28%
 C	12	33	45	27%
 D	1	2	3	33%
 E	7	46	53	13%
 F	7	17	24	29%
 G	47	14	61	77% 
 総計	97	170	267	36%

各チームでの人数（ID数合計）のうち、回答が「有」の
人数の割合を計算したいです。

現状は、データをピボットテーブルで集計したものを
別にコピペして、回答有無の割合を毎回計算しています。

ピボットテーブルのメジャーを使ってできないかなと思い、
色々調べたのですが、設定方法がわからず断念しました。

ご教授いただけますと幸いです。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 惜しい所までは持って行けると思います。

 たとえば、
 値フィールドに「ID」を2個追加し、集計方法は2つとも「データの個数」のままで、
 2つ目の[計算の種類]を「行集計に対する比率」にした図が↓これです。

 __|____A_____|____B_____|_____C_____|____D_____|_____E_____|________F________|________G_________
  1|          |          |           |          |           |                 |                  
  2|          |          |           |          |           |                 |                  
  3|          |列ラベル  |           |          |           |                 |                  
  4|          |無        |           |有        |           |全体の 合計 / ID |全体の 合計 / ID2 
  5|行ラベル  |合計 / ID |合計 / ID2 |合計 / ID |合計 / ID2 |                 |                  
  6|A         |          |      0.00%|         1|    100.00%|                1|           100.00%
  7|B         |          |      0.00%|         2|    100.00%|                2|           100.00%
  8|C         |         3|     42.86%|         4|     57.14%|                7|           100.00%
  9|E         |         5|    100.00%|          |      0.00%|                5|           100.00%
 10|総計      |         8|     53.33%|         7|     46.67%|               15|           100.00%
 11|          |          |           |          |           |                 |                  
                              ↑                     ↑
                            無の割合               有の割合

（白茶） 2022/06/16(木) 13:38

 あ、すみません。↑のデータ自体はテキトーです
（白茶） 2022/06/16(木) 13:40

 ピボットテーブルの使用列が固定されているのなら
 質問の回答にはなっていませんが
 別にコピペせずに 作りたい集計データ と同じ様に５列目に(ピボットの表示横列）
 あらかじめ関数を入れておけばどうでしょうか
 ピボットテーブルがA1から表示しているとして
 E2=IFERROR(C3/D3*100,"")またはIFERROR(C3/D3,"")で表示形式を％にしておくなど
 いちいちゲットピボットでなくても計算できると思いますので

（なるへそ） 2022/06/16(木) 16:31

メジャーが簡単なのでしょうが、わたしのPCでは使えないので
ピボットテーブルでなく、Power Queryを使った回答でよければ、
こんな手順

１）列のピボット
２）グループ化：有無のカウント
３）加算：総計の計算
４）除算：割合の計算

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"ID", type text}, {"名前", type text}, {"チーム", type text}, {"回答", type text}}),
    ピボットされた列 = Table.Pivot(変更された型, List.Distinct(変更された型[回答]), "回答", "ID", List.Count),
    グループ化された行 = Table.Group(ピボットされた列, {"チーム"}, {{"無", each List.Sum([無]), type number}, {"有", each List.Sum([有]), type number}}),
    挿入された加算 = Table.AddColumn(グループ化された行, "総計", each [無] + [有], type number),
    挿入された除算 = Table.AddColumn(挿入された加算, "回答無の割合", each [無] / [総計], type number)
 in
    挿入された除算

（マナ） 2022/06/16(木) 20:37

皆様、ありがとうございます。
勉強のために、アドバイスいただいた方法、全て試してみます。改めてエクセルの奥の深さを思い知りました。

（ばんしゅう） 2022/06/20(月) 17:53

Power Query（案）で追記

 １）総計の行は、Power Queryでは作成していません。
 　　シートに読み込んだ後で、「テーブルデザイン」タブから、「集計行」を表示させてください。
 　　集計行の数式は、割合列も合計になるので、手作業で修正してください。

 ２）Power Queryでは、列のピボット（「有」「無」フィールドの追加）だけにして、
 　　それを、ピボットテーブルで読み込むという方法もあります。
 　　その場合、総計、割合は、集計フィールドで実現できます。
 　　ピボット操作になれているなら、１）より簡単に感じるかもしれません。

 ３）総計も含めて、すべてをPower Queryだけでも完結できますが
 　　Mコードの編集が必要になるので、おすすめしません。

（マナ） 2022/06/20(月) 19:14

コメント返信：

[ 一覧(最新更新順) ]

[[20220616122630]] 『ピボットテーブルの集計値同士の計算』（ばんしゅう） ページの最後に飛ぶ

[[20220616122630]]　『ピボットテーブルの集計値同士の計算』（ばんしゅう）　ページの最後に飛ぶ