
ピボットテーブルについて詳しくありません。。教えてください。
列項目の多いデータを参照して、様々な抽出条件で値を計算するという作業があります。
その際に、抽出されたデータの合計やデータの個数ではなく、
データの種類の数を出したいのです。
私の力量ではフィルタで確認したり関数で一つずつ計算するのが大変で、
ピボットテーブルで表にするのが一番正確な気がしています。
現在はピボットテーブルを作成する前に、
毎回条件に見合った簡単な関数の作業列を作成している状況です。
(それも間違えることがあります・・)
簡単に抽出する方法はないでしょうか?
もしくは1つの作業列を作成することですむ方法はありますか?
毎回、抽出するのは登録番号の種類の数です。
-----------------------------
登録番号 状況 割付 割付2
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 及び 割付2=有 の登録番号を 割付毎に抽出する
(答)
H = 3
C = 1
(希望の答=データの種類数)
H = 2
C = 1
登録番号や割付項目が多いデータで、抽出条件も都度変わるので困っています。
よろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
まずとりあえず、ご承知の通りエクセルのピボットテーブルレポートには、「種類の数」を数える方法はありません。
ご利用のエクセルのバージョンがご相談に書かれていませんが、添付図はエクセル2010(以降)を使い、
レポートフィルタ(ページフィールド)に
状況を配置、 AとBを選択
割付2を配置、有を選択
行ラベルに
割付を配置
登録番号を配置
しておき、さらに割付フィールドのフィールドの設定で「アイテムのラベルを繰り返す」ことで、別途並べたCとHをCOUNTIF関数で数えています。
I13:
=COUNTIF(G:G,H13)
関数を使って一発集計とか、補助列を用意してピボットテーブルに集計させることももちろんやれば出来ますが、抽出条件が変わる都度数式をまたイチから作り直さなきゃならないんで、現実問題としては役に立ちません。
一応ご参考に、添付図では
E2:
=IFERROR(1/SUMPRODUCT(COUNTIFS(A:A,A2,B:B,{"A","B"},D:D,"有")),0)
補助列をピボットテーブルレポートで「合計」で集計すると、それぞれの種類の数が集計される。

ありがとうございました。
>抽出条件が変わる都度数式をまたイチから作り直さなきゃならないんで、現実問題としては役に立ちません。
まさに関数だとその通りなのです。今回は抽出条件が都度変わるので困っておりました。
なのでピボットテーブルで処理できるのはとてもありがたいです!
「アイテムのラベルを繰り返す」というのがExcel2010ではあるんですね!
知りませんでした。これは便利そうです。
あと、補助列に入れる関数もとても参考になりました!
私はまだExcel2007ですが、この資料を持っている友人はExcel2010を使用しておりますので、
早速後日試してみたいと思います。
今後Excelのピボットテーブルで「種類の数をカウント」というのもできたらいいな・・と期待します。
No.3
- 回答日時:
>やはり関数を入力した作業列(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
- 回答日時:
>(抽出条件)
>状況=AorB 及び 割付2=有 の登録番号を 割付毎に抽出する
>(答)
>H = 3
>C = 1
この結果を求めるには次の式で良いでしょう。
試算は提示のデータ数の範囲で実行しました。
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 = 2
>C = 1
この結果を求めるにはデータの重複をチェックする必要がありますので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を使用しております。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】=EXACT(a1,b1) a1とb1...
-
【マクロ】excelファイルを開く...
-
エクセルのリストについて
-
【マクロ】【相談】Excelブック...
-
エクセルの関数について
-
【マクロ】数式を入力したい。...
-
【マクロ】元データと同じお客...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
【マクロ】左のブックと右のブ...
-
【画像あり】オートフィルター...
-
Office2021のエクセルで米国株...
-
LibreOffice Clalc(またはエク...
-
vba テキストボックスとリフト...
-
エクセルのVBAで集計をしたい
-
【マクロ】【配列】3つのシー...
-
エクセルシートの見出しの文字...
-
【マクロ】【画像あり】ファイ...
-
【マクロ】【画像あり】❶ブック...
-
【マクロ画像あり】❶1つの条件...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報