ピボットテーブルについて詳しくありません。。教えてください。
列項目の多いデータを参照して、様々な抽出条件で値を計算するという作業があります。
その際に、抽出されたデータの合計やデータの個数ではなく、
データの種類の数を出したいのです。
私の力量ではフィルタで確認したり関数で一つずつ計算するのが大変で、
ピボットテーブルで表にするのが一番正確な気がしています。
現在はピボットテーブルを作成する前に、
毎回条件に見合った簡単な関数の作業列を作成している状況です。
(それも間違えることがあります・・)
簡単に抽出する方法はないでしょうか?
もしくは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.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を使用しております。
ありがとうございました。
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),"")),""))
当方では試算しましたが実際のデータで確認してみると良いでしょう。
お探しの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ランキング
-
エクセル 文字を増やしたい。
-
エクセルの計算
-
セルの内容表示が邪魔になる
-
Excel
-
Microsoft365に変えたのですが...
-
エクセル:一覧表に存在する文...
-
エクセルで日付を数字+アルフ...
-
エクセルでの作業計算方法について
-
エクセルで年休を管理する方法...
-
はがきについて。
-
【マクロ】その時、その時で変...
-
excelの不要な行の削除ができな...
-
Microsoft1Officeの互換ソフト...
-
エクセル関数を教えてください
-
Excel ピボットテーブルで日付...
-
【マクロ】読取専用のファイル...
-
【関数】適切な文字数の数字を...
-
時間によってファイル名が変わ...
-
ある列、或いは、ある行のセル...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報