ピポットテーブ 重複行を１としてカウントしたい

質問

何時も、大変お世話になっております。Excelの初心者です。
ピポットテーブルのクロス集計を利用して、A列～AS列のセルに入力された
データから月毎の集計を出したいのですが、重複するデータの場合でも１とカウントしたいのですが、
やり方がわかりません。何方かご教示頂けると大変助かります。

ピポットテーブルでデータ修正をする前に、データ上で重複の処理をすべきなのでしょうか？
ピポットテーブルの集計の時に、重複した個数を1とカウントする事は出来ないのでしょうか？

今回、やりたい事は、
月毎(今現在のデータ＝4月分、5月分、6月分)に、利用区分毎で更に所属区分毎に分けて、
下記のP列の課題IDの個数の合計を求めるのに、その月に同じ課題IDが複数ある場合は１とカウントさせて、ピポットテーブル上でその月の課題IDの集計をさせる事です。
課題IDの集計を月単位で上記の条件でピポットテーブルで出すと、重複行もそのままカウントされてしまうので、困っています。

集計に使用するデータ項目は以下の通りとなります。
・E列(項目＝利用日) 　　　　　　　　　　
　<項目の詳細>
　 各機器の貸し出し日を日単位で記載しています。(2022/7/10)　　　　

・L列(項目＝所属区分)
　<項目の詳細>
　 大学、民間、管理部署、構内、大企業、中小企業の何れかを記載しています。

・ N列(項目＝利用区分)　
　 <項目の詳細>
　 　使用不可、構内利用、外国利用、国内利用、使用不可の何れか載しています。

・P列(項目＝課題ID)　
　 <項目の詳細>　
　 空白もあるデータです。55AB1234、55RE2345、JPMXP1222NM0053、HHAPPYのように数字とアルファベットを組み合わせたIDや数字のみのIDやアルファベットのIDを記載しています。

回答No.5

kkkkkm · ベストアンサー

2016だと設定の中に重複しないという指定ができるようですが
(2016が無いので確認できません)

エクセル (vr.2016) – ピボット – 集計方法変更 (重複しない値の数)
https://otukai.jp/xlsx-pivot-countd

2016以前の場合には値フィールドの設定にその項目がないので

月、利用区分、所属区分をグループとして、そのグループでの課題IDの重複を排除する
(月、利用区分、所属区分、課題IDをセットとして重複を考える)

2022/5/12、大学、国内利用、55AB1234
2022/5/15、大学、国内利用、55AB1234
両方5月なので課題IDの重複あり

2022/5/12、大学、国内利用、55AB1234
2022/6/11、大学、国内利用、55AB1234
5月と6月なので課題IDの重複なし

と考える場合

元の表にたとえばQ列に

項目名Q1に「月」
Q2に
=MONTH(E2)
として必要なだけ下にコピー
項目名R1に「課題ID2」
R2に
=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)
として下に必要なだけコピー

利用区分、所属区分、課題IDをセットとする場合Q列は不要でR2の式は
=IF(COUNTIFS($L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)
で

ピボットの方でR2まで範囲を取り込んで
課題IDの代わりに課題ID2を利用して「値フィールドの設定」を「合計」にしてみてはいかがでしょう。

データは他の方への補足に記載されていたもの利用しましたが
データをテキストで記載していたので簡単に取り込めました。

補足

何時も、分かり易いご教示を頂き、本当に助かっております。
私もEXCEL2010に、PowerPivotやPowerQueryをインストールして使っておりましたが、EXCELは全くの初心者に近い知識しかなく、止む無く、今回のピポットテーブルでの集計の仕事の為に、EXCEL2019へ更新致しました。　仕事場ではExcel2019を使っているからです。

ピポットテーブやクエリーの経験が乏しい為、重複行をピポットテーブルで削除することばかり考えていました。
しっかりと下ごしらえしたうえでピポットテーブはつかうものなのですね。
【下ごしらえ】
　1)データ集計項目を優先順位でデータの並べ替えをする。
　2)重複が有る場合は、重複行の削除をする。
　　※下ごしらえは必ず1)、2)の順で実施する。

下記の①～④を実施し、課題IDを項目毎に集計する事が出来ました。
①項目名Q1に「月」、Q2に=MONTH(E2)として必要なだけ下にコピー。
②月、利用区分、所属区分、課題IDの順にデータの並べ替え。
③データの右側へ下記の式をデータの最終行まで入力する。
　知人から教えてもらった以下の式では、『=IF(Q2&L2&N2&P2=Q3&L3&N3&P3,0,1)』
　空白行が含まれてしまいましたが、ご教示頂いた下記の式では、
『=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)』、
　空白行は含まず、こちらの式を採用させて頂きました。
④ピポットテーブの集計で以下の通りにセットして、
　利用区分毎、且つ所属区分毎で課題IDの集計を出す事が出来ました。
　　行＝利用日
　　列＝利用区分、所属区分
　　合計＝課題ID重複削除済

【IFの違いを教えてください】
大変お手数ですが、上記③のIF文の式の違いにについて簡単で結構ですので、お分かりでしたら、解説して頂けないでしょうか？
　・=IF(Q2&L2&N2&P2=Q3&L3&N3&P3,0,1)
　・=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)

以上　引き続きよろしくお願いいたします。

回答No.8

kkkkkm

> EXCEL2019へ更新致しました。

EXCEL2019でしたら、回答No.5で紹介したこちらの方法が使えるのではないでしょうか。

エクセル (vr.2016) – ピボット – 集計方法変更 (重複しない値の数)
https://otukai.jp/xlsx-pivot-countd

お礼

早速のIFの解説、本当にありがとうございました。
下記の違い、理解しました。
・=IF(Q2&L2&N2&P2=Q3&L3&N3&P3,0,1)
・=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)

ご紹介頂いた、下記のURLで『重複しない値の数』の機能も確認しました。
データのリレーションシップのやり方も朧気ながら理解しました。
https://otukai.jp/xlsx-pivot-countd

実データで、上記URLを試して見ましたが、
月、利用区分、所属区分、課題IDでリレーションシップを組むやり方が分からず、
又、課題IDで、[集計方法]のプルダウンでは『重複しない値の数』が出て来ませんでした。
こちらは、私の勉強不足ですので、学んでいきます。

№5でご教示頂いた、以下の意味がやっとわかりました。
①月、利用区分、所属区分をグループとして、そのグループでの課題IDの重複を排除する
　(月、利用区分、所属区分、課題IDをセットとして重複を考える)

②利用区分、所属区分、課題IDがセットになってない時は、以下を実行する。
　　Q2に
　　=MONTH(E2)
　　R2に
　　=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)

※ピポットテーブルの集計で今回ご教示頂いたIF関数を使えば、
　ご教示頂いた通りに、テーブル上で月、利用区分、所属区分の順でデータの並べ替えをしなくても、
　正しく集計が出来る事を再確認しました。

回答No.7

kkkkkm

> 　・=IF(Q2&L2&N2&P2=Q3&L3&N3&P3,0,1)

現在の行と次の行のデータが一致していれば0に、一致していなければ1にする。
という式です。同じデータは一塊になっている必要があります。1を合計するといくつの塊があったかがわかります。

> 　・=IF(COUNTIFS($Q$2:Q2,MONTH(E2),$L$2:L2,L2,$M$2:M2,M2,$P$2:P2,P2)=1,1,0)

2行目から現在の行まで、現在の行と同じデータの個数をカウントし、1個なら1、1個以外(重複があれば2以上になります)なら0にします。
データはバラバラに存在しても大丈夫です。1を合計すると何種類のデータがあったかがわかります。
COUNTIFSは未入力のセルはカウント対象にならないみたいです。

回答No.6

kkkkkm

No.5の実行結果の画像です。

5月、大学、国内利用は課題IDが別なので「2」
6月、大企業、外国利用は課題IDが重複しているので「1」
6月、民間、構内利用は3回のうち課題IDの重複が2個一組あるので「2」

となっています。

回答No.4

imogasi

捕捉有難うございました。これを作成しOKWAVEに載せるは、大変でしたでしょう。
私もこのデータを自分のシートに持ってくるのに時間が掛かりました。
データの中身は、業界が未経験で、もう一つ現実味がわきません。
＝＝＝＝
こういう場合は、CSVファイルの形で、OKWAVEの質問に載せるのが良いと思いました。
CSVファイルで保存する。それをメモ帳で読み込み、これをコピーして新規シートのA列に張り付ける。
其の後、データー区切り位置ーカンマで各列に分離する。あと列幅を適当に広げる。
これで実際のシートのデータを作れます。
＝＝　　主題　＝＝＝＝＝
このデータで、ピボットテーブルなどを使い、どのような体裁の表を作りたいのか、が、質問に書かれていません。
標準でピボットでやった場合は（近い形の表は）こうなるーー＞だが、希望としてはこういう数字の表にしたい。ということを、示さないとダメでしょう。
例示はピボットで作成しなくても、手作業の表でも作って、例示してはどうか。
＞月毎の集計を出したいの・・はグループ化の機能を使うのでしょう。
＞重複するデータの場合でも１とカウントしたい
どの列の、どのデータの場合に（重複としてとらえ）、どうカウントするのか書かないと、議論が進まないでしょう。
　私はその中に加われないかもしれないが、考え方は述べられます。と思って挙げてみます。
＝＝＝
参考
私の作ったCSVデータを挙げておきます。一部訂正が必要かもしれません。
A列,B列,,,E列,F列,G列,H列,,,,L列,,N列,,P列
機器ID,装置名,,,利用日,開始時間,終了時間,利用時間,,,,所属区分,,利用区分,,課題ID
AB123,洗濯機,,,2022/4/1,9:00,17:00,8,,,,大学,,国内利用,,55AB1234
AB123,洗濯機,,,2022/5/15,0:00,24:00:00,24,,,,大学,,国内利用,,55RE2345
AB222,ドライヤー,,,2022/4/30,9:00,17:00,8,,,,中小企業,,国内利用,,55RE7800
AB345,炊飯器,,,2022/6/23,9:00,17:00,8,,,,大企業,,外国利用,,JPMXP1222NM0053
AB890,ポット,,,2022/4/1,9:00,17:00,8,,,,構内,,自主運用,,空白
AB890,ポット,,,2022/5/6,0:00,24:00:00,24,,,,管理部署,,使用不可,,空白
AB235,パソコン,,,2022/5/1,5:00,7:00,2,,,,管理部署,,使用不可,,空白
AB002,テレビ,,,2022/6/24,0:00,24:00:00,24,,,,民間,,構内利用,,JPMXP1222NM0054
AB002,テレビ,,,2022/6/24,5:00,7:00:00,2,,,,大学,,国内利用,,空白
AB005,スマホ,,,2022/4/25,5:00,7:00,2,,,,構内,,自主運用,,空白
AB777,乾燥器,,,2022/5/12,0:00,24:00:00,24,,,,大学,,国内利用,,55AB1234
AB016,掃除機,,,2022/6/1,13:00,20:00,7,,,,民間,,構内利用,,JPMXP1222NM0058
AB777,乾燥器,,,2022/6/5,13:00,20:00,7,,,,民間,,構内利用,,JPMXP1222NM0054
AB024,アイロン,,,2022/6/13,9:00,17:00,8,,,,大企業,,外国利用,,JPMXP1222NM0053
AB890,ポット,,,2022/5/9,9:00,17:00,8,,,,中小企業,,国内利用,,55RE7800
もし有効活用できれば使ってください。

お礼

今回も、お気づきのご指摘、本当にありがとうございました。

回答No.3

Chiquilin

重複を削除したクエリを接続のみで作成しておいて それを外部データ
接続で読み込んでピボットテーブルを作ればいいと思います。

もちろん重複を省いてグループ化すればいいだけなので クエリで全部
完結しても何ら問題ないです。

お礼

ご教示ありがとうございました。
重複行の削除やピポットテーブルでの集計の求め方など、
仰っている通りでした。知識が全く不足しておりまして、
誠にお恥ずかしい事です。
今後ともよろしくお願いいたします。

回答No.2

imogasi

昨日朝、この質問を読んで、内容がよく判らなかった。
こんな質問の仕方で、読者はわかるかな、と様子見することにした。
本日見ると回答が出た。ただし、結論的なものだけと思えた。
＃１で十分ですか？
ーー
質問としては、通例に比べ、説明文字数は多い。しかし
（１）職場・仕事寄りの説明で,ごちゃごちゃ。
（２）シート・データの具体例が載ってない　列の項目説明だけではだめ。
（３）もっと説明は、課題を「パターン化」してするべきだが、その記載や説明がない。
エクセルの「レパートリーを広げる」とは、このパターンの習得をコツコツ増やすことと思う。
ーー
仕事の実例を離れてパターン化したデータ実例を書いてほしい。
・Ａ－Ｇ列ぐらいまで、１０ー１５行までの模擬実例を書いてほしい
ーー
結論は＃１と変わらないだろうが。
常識的には、データのあるものをないものにするのは、ピボットではあり得ない。
・フィルタ
・グループ化
等しかなく、
この範疇に入らないなら、料理の「下ごしらえ」のような、
事前処理が、多分VＢＡなどで、行う必要があるのではと思っている。

補足

大変お世話になっております、何時もながら説明が下手で申し訳ありません。
データの項目が多すぎて、全ての項目を記載するのは、無理なので、取り合えず、必要な項目のデータ実例を下記へ記載させて頂きました。

P列の課題IDについて、N列の項目で更にL列に分けて、月単位で重複無しの課題IDの個数を集計したいです。
説明の表現が下手でもう分けありません。

　A列　　　B列　　　　 E列　　　 F列　　　　G列　　　　　H列　　　 L列　　　　 N列　　　　P列
機器ID　 装置名　　　利用日　　 開始時間　 終了時間　　利用時間　所属区分　　利用区分　　課題ID　　　　　
AB123 洗濯機 2202/04/01 09:00 17:00 8 大学 　　　国内利用　　55AB1234
AB123　　洗濯機 2022/05/15 00:00 24:00 24　　　大学　　　　国内利用　　55RE2345
AB222　　ドライヤー 2022/04/30 09:00 17:00 8　　　中小企業　　国内利用　　55RE7800
AB345　　炊飯器 2022/06/23 09:00 17:00 8　　　大企業　　　外国利用 JPMXP1222NM0053
AB890　　ポット 2022/04/01 09:00 17:00 8　　　構内　　　　自主運用 空白
AB890　　ポット 2022/05/06 00:00 24:00 24　　　管理部署　　使用不可　　空白
AB235　　パソコン 2022/05/01 05:00 07:00 2　　　管理部署　　使用不可　　空白
AB002　　テレビ 2022/06/24 00:00 24:00 24　　　民間　　　　構内利用 JPMXP1222NM0054
AB002　　テレビ 2022/06/24 05:00 07:00 2　　　大学　　　　国内利用　　55AB1234
AB005　　スマホ 2022/04/25 05:00 07:00 2　　　構内　　　　自主運用　　空白
AB777　　乾燥器 2022/05/12 00:00 24:00 24　　　大学　　　　国内利用　　55AB1234
AB016　　掃除機 2022/06/01 13:00 20:00 7　　　民間　　　　構内利用　　JPMXP1222NM0058　
AB777　　乾燥機 2022/06/05 13:00 20:00 7　　　民間　　　　構内利用　　JPMXP1222NM0054
AB024　　アイロン 2022/06/13 09:00 17:00 8　　　大企業　　　外国利用 JPMXP1222NM0053
AB890　　ポット 2022/05/09 09:00 17:00 8　　　中小企業　　国内利用 55RE7800

質問文の下記の記載は、使用不可が重複していました、申し訳ありません。
<項目の詳細>
　 　使用不可、構内利用、外国利用、国内利用、使用不可
　　　　　　　　　　　　↓

　 　使用不可、構内利用、外国利用、国内利用、使用不可へ修正させて頂きます。

回答No.1

aokii

重複した個数を1とカウントする事は出来ません。個数はA列の項目数になります。

お礼

質問内容が説明不足で大変失礼いたしました。
ご教示ありがとうございました。