![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
ピボットテーブルについて詳しくありません。。教えてください。
列項目の多いデータを参照して、様々な抽出条件で値を計算するという作業があります。
その際に、抽出されたデータの合計やデータの個数ではなく、
データの種類の数を出したいのです。
私の力量ではフィルタで確認したり関数で一つずつ計算するのが大変で、
ピボットテーブルで表にするのが一番正確な気がしています。
現在はピボットテーブルを作成する前に、
毎回条件に見合った簡単な関数の作業列を作成している状況です。
(それも間違えることがあります・・)
簡単に抽出する方法はないでしょうか?
もしくは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)
補助列をピボットテーブルレポートで「合計」で集計すると、それぞれの種類の数が集計される。
![「ピボットテーブルの値について質問です。」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/1223049_5497e68853afc/M.jpg)
ありがとうございました。
>抽出条件が変わる都度数式をまたイチから作り直さなきゃならないんで、現実問題としては役に立ちません。
まさに関数だとその通りなのです。今回は抽出条件が都度変わるので困っておりました。
なのでピボットテーブルで処理できるのはとてもありがたいです!
「アイテムのラベルを繰り返す」というのが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関数がありませんので代替の関数式で対応させます。
![「ピボットテーブルの値について質問です。」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/1229070_5497eb8d29ae6/M.jpg)
やはり関数を入力した作業列(E列)を抽出条件ごとに作成する必要がありそうですね。
バージョンは2007を使用しております。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelの数式についての質問 1 2022/10/31 15:50
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) Excelでデータを抽出するに良い方法 9 2023/02/06 12:42
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- Excel(エクセル) 【詳しい方教えて下さい】EXCEL条件に一致する値の複数抽出 9 2022/04/29 10:56
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- 統計学 確率統計の問題です。 3 2022/04/07 04:39
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
セルの数を求めたい
-
ある表にフィルターをかけて出...
-
Lookup関数
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
エクセルの数式バーのフォント...
-
EXCELの散布図で日付が1900年に...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelピボットテーブルの1行目
-
エクセル日付 文字列の関数がエ...
-
西暦や和暦の表示をyyyymmdd表...
-
エクセルで「ページレイアウト...
-
【ExcelVBA】名前を付けて保存→...
-
Excelに詳しい方! B列が「日...
-
Excelで50個のセルに同じ文字を...
-
Excelについて
-
Excel関数について教えてくださ...
-
Excelで表を作ったところに文字...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報