ピボットテーブルの値について質問です。

質問

ピボットテーブルについて詳しくありません。。教えてください。

列項目の多いデータを参照して、様々な抽出条件で値を計算するという作業があります。

その際に、抽出されたデータの合計やデータの個数ではなく、
データの種類の数を出したいのです。

私の力量ではフィルタで確認したり関数で一つずつ計算するのが大変で、
ピボットテーブルで表にするのが一番正確な気がしています。

現在はピボットテーブルを作成する前に、
毎回条件に見合った簡単な関数の作業列を作成している状況です。
（それも間違えることがあります・・）

簡単に抽出する方法はないでしょうか？
もしくは1つの作業列を作成することですむ方法はありますか？

毎回、抽出するのは登録番号の種類の数です。

-----------------------------

登録番号　状況　割付　割付２　

101-001　　A　　H　　有
101-001　　B　　H　　有
204-001　　　　　C　
204-002　　A　　C　　有
204-002　　　　　C
205-002　　C　　C
206-005　　A　　H　　有

（抽出条件）
状況＝AorB　及び　割付２＝有　の登録番号を　割付毎に抽出する

（答）
H　＝　３
C　＝　１

（希望の答＝データの種類数)
H　＝　２
C　＝　１

登録番号や割付項目が多いデータで、抽出条件も都度変わるので困っています。
よろしくお願いいたします。

回答No.2

keithin · ベストアンサー

まずとりあえず、ご承知の通りエクセルのピボットテーブルレポートには、「種類の数」を数える方法はありません。

ご利用のエクセルのバージョンがご相談に書かれていませんが、添付図はエクセル２０１０（以降）を使い、

レポートフィルタ（ページフィールド）に
　状況を配置、　ＡとＢを選択
　割付２を配置、有を選択
行ラベルに
　割付を配置
　登録番号を配置

しておき、さらに割付フィールドのフィールドの設定で「アイテムのラベルを繰り返す」ことで、別途並べたＣとＨをCOUNTIF関数で数えています。
Ｉ１３：
=COUNTIF(G:G,H13)

関数を使って一発集計とか、補助列を用意してピボットテーブルに集計させることももちろんやれば出来ますが、抽出条件が変わる都度数式をまたイチから作り直さなきゃならないんで、現実問題としては役に立ちません。

一応ご参考に、添付図では
Ｅ２：
=IFERROR(1/SUMPRODUCT(COUNTIFS(A:A,A2,B:B,{"Ａ","Ｂ"},D:D,"有")),0)

補助列をピボットテーブルレポートで「合計」で集計すると、それぞれの種類の数が集計される。

お礼

ありがとうございました。

>抽出条件が変わる都度数式をまたイチから作り直さなきゃならないんで、現実問題としては役に立ちません。

まさに関数だとその通りなのです。今回は抽出条件が都度変わるので困っておりました。
なのでピボットテーブルで処理できるのはとてもありがたいです！
「アイテムのラベルを繰り返す」というのがExcel2010ではあるんですね！
知りませんでした。これは便利そうです。

あと、補助列に入れる関数もとても参考になりました！

私はまだExcel2007ですが、この資料を持っている友人はExcel2010を使用しておりますので、
早速後日試してみたいと思います。

今後Excelのピボットテーブルで「種類の数をカウント」というのもできたらいいな・・と期待します。

回答No.3

bunjii

>やはり関数を入力した作業列（E列）を抽出条件ごとに作成する必要がありそうですね。
絶対ではありませんが演算式が複雑になってデバックが難しくなります。

割り付けがHのとき
=SUM(IF((B$2:B$8="A")*(C$2:C$8="H")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7)/SUM(IF((B$2:B$8="A")*(C$2:C$8="H")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7),"")),""))+SUM(IF((B$2:B$8="B")*(C$2:C$8="H")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7)/SUM(IF((B$2:B$8="B")*(C$2:C$8="H")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7),"")),""))

割り付けがCのとき
=SUM(IF((B$2:B$8="A")*(C$2:C$8="C")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7)/SUM(IF((B$2:B$8="A")*(C$2:C$8="C")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7),"")),""))+SUM(IF((B$2:B$8="B")*(C$2:C$8="C")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7)/SUM(IF((B$2:B$8="B")*(C$2:C$8="C")*(D$2:D$8="有"),ROW(A$1:A$7)/ROW(A$1:A$7),"")),""))

当方では試算しましたが実際のデータで確認してみると良いでしょう。

お礼

ありがとうございました。
色々な関数が参考になりました。

回答No.1

bunjii

>（抽出条件）
>状況＝AorB　及び　割付２＝有　の登録番号を　割付毎に抽出する
>（答）
>H　＝　３
>C　＝　１
この結果を求めるには次の式で良いでしょう。
試算は提示のデータ数の範囲で実行しました。

Hの個数 =COUNT(IF((OR(B$2:B$8="A",B$2:B$8="B"))*(C$2:C$8="H")*(D$2:D$8="有"),ROW($A$2:$A$8),""))
Cの個数 =COUNT(IF((OR(B$2:B$8="A",B$2:B$8="B"))*(C$2:C$8="C")*(D$2:D$8="有"),ROW($A$2:$A$8),""))
この式はIF関数の返り値を配列としなければならないので入力後、Ctrl+Shift+Enterで確定します。

>（希望の答＝データの種類数)
>H　＝　２
>C　＝　１
この結果を求めるにはデータの重複をチェックする必要がありますのでE列に次の式をセットします。

E2=COUNTIFS(B$2:B2,B2,C$2:C2,C2,D$2:D2,D2)
E2セルを下へ必要数コピーします。

Hの個数 =COUNT(IF((OR(B$2:B$8="A",B$2:B$8="B"))*(C$2:C$8="H")*(D$2:D$8="有")*(E$2:E$8=1),ROW($A$2:$A$8),""))
Cの個数 =COUNT(IF((OR(B$2:B$8="A",B$2:B$8="B"))*(C$2:C$8="C")*(D$2:D$8="有")*(E$2:E$8=1),ROW($A$2:$A$8),""))

Excel 2013を使用しましたがExcel 2007以降のバージョンで処理可能です。
Excel 2003以前のバージョンではCOUNTIFS関数がありませんので代替の関数式で対応させます。

お礼

やはり関数を入力した作業列（E列）を抽出条件ごとに作成する必要がありそうですね。

バージョンは2007を使用しております。

ありがとうございました。