ピボットテーブルで、個数の集計がうまくできません

質問

職場でExcel2003を使っています。
商品ごとに、取引先の何拠点に納入しているか、調べる…という仕事が発生しました。
元データが、こんな感じ↓
2011年度7月販売実績
担当部　担当課　取引先名　取引先詳細　商品Ａ　商品Ｂ　商品Ｃ
○○部　○○課　Ａ式会社　　a支店　　　　１００円　100円　200円
○○部　○○課　Ａ式会社　　b支店　　　　１０円　　0円　　　200円
○○部　○○課　Ｂ式会社　　a支店　　　　１００円　100円　200円
○○部　○○課　Ｃ式会社　　a支店　　　　0円　　　　0円　　200円
…

販売実績（金額)表です。
ピボットの「データの個数」の方を表示する風にすれば、各商品ごと・取引先ごとに、何店舗に買って頂いたのか！ていう、データが出せるのじゃないか、と。

担当部　担当課　取引先名　商品Ａ　商品Ｂ　商品Ｃ
○○部　○○課　Ａ式会社　　2　　　　　1　　　　2
○○部　○○課　Ｂ式会社　　1　　　　　1　　　　1

↑という形に、データを持っていきたい。
今日、元データの下に
担当部　担当課　取引先名　商品Ａ　商品Ｂ　商品Ｃ
○○部　○○課　Ａ式会社　　　●　　　　●　　　●
○○部　○○課　Ｂ式会社　　　●　　　　●　　　●
という表を作り、●部分のセルにＣＯＵＮＴＡ関数を入れ、上の表の範囲を一つ一つ指定して…ということをしたのですが（　COUNTA（C4:C124)　とかそういう具合）、手間がかかるし、月ごとに取り扱う会社・店舗数が変動する（予定）なので、来月また同じ設定をしなおさなきゃならないですよね。
ピボットでやっちゃえば一発じゃない？
と思っても、
「行データ（←列だったかも）が多すぎる、データ数を減らすか、非表示に設定しろ」
みたいなエラーメッセージが出て、うまく行きません…。
ちなみに、元データが1万行と少し。かなり大きなデータです。
が、誰が作ったとも知れぬ昔のファイルを見たら、4万行を超すデータもピボットで組んでいて。
ということは、ピボットで集計できるだろう、とは思うのですが…その表のレイアウトを覗いて、全く同じように（ウィザード画面で）並べても、完了ボタンを押すと、データが多い、とはねられるのです。

うまいやり方をご存知の方、どうかお教えください。

回答No.1

MackyNo1 · ベストアンサー

>今日、元データの下に
担当部　担当課　取引先名　商品Ａ　商品Ｂ　商品Ｃ
○○部　○○課　Ａ式会社　　　●　　　　●　　　●
○○部　○○課　Ｂ式会社　　　●　　　　●　　　●
という表を作り、●部分のセルにＣＯＵＮＴＡ関数を入れ、上の表の範囲を一つ一つ指定して…ということをしたのですが

この部分はCOUNTA関数ではなくSUMPRODUCT関数を利用します。

表示したい担当部、担当課、取引先名がK2, L2,　M2,セル以下に入力されているなら、N2セルに以下の式を入力して右方向に３つ下方向にデータ数分だけオートフィルしてください。

=SUMPRODUCT(($A$2:$A$10000=$K2)*($B$2:$B$10000=$L2)*($C$2:$C$10000=$M2)*(D$2:D$10000<>0))

ただし元データに本当に０円と入力されているなら、数式の最後の部分を「D$2:D$10000<>"0円"」にする必要があるし、実際は空白なら「D$2:D$10000<>""」に、この部分に関係なくA,B,C列の組み合わせが一致するデータで良いなら、この部分を削除することになります。

＞その表のレイアウトを覗いて、全く同じように（ウィザード画面で）並べても、完了ボタンを押すと、データが多い、とはねられるのです。

そもそも元のデータはレイアウトがピボットテーブルの集計に向いていないリストになっています。
例示のリストで作成した場合は、おそらく列フィールドにD列のA商品などの項目をドラッグしたため、表示するデータが多く（この場合は100円や200円などのデータが数多く表示された）のではないかと思われます。

もしピボットテーブルで対応するなら、金額を集計するのではなく、商品の種類を集計できる以下のようなリストにする必要があります。

担当部　担当課　取引先名　取引先詳細　商品　　金額　
○○部　○○課　Ａ式会社　　a支店　　　　商品Ａ　 100　
○○部　○○課　Ａ式会社　　b支店　　　　商品Ｂ　 200

お礼

回答、ありがとうございました！

うわ～、やっぱり元データがピボット向きじゃないですかっ。
昨日、帰宅途中に（結局就業時間中にうまくデータ作り直せなかった）本屋に飛び込み、エクセルの本を立ち読みしたら、例として載っていたデータが、やっぱりお示しの表みたくなってて、
「…もしや、元データがピボットに変換できる形になっていない　…？」
とは思ったのですが…！

元データのレイアウト、変えられるかなあ…
データ取得時に、レイアウトを変えられるみたいなんですが、まだあまり慣れてなくて、できるかどうかわからないんですよね…明日出社したら、元データのレイアウト変更を出来るかどうか、試します！

回答No.4

noname#204879

「ピボットテーブル」に固執して「↑という形に、データを持っていきたい」のなら、「元データが、こんな感じ」でなく、添付図上段のような「感じ」にする必要があります。
そうすると添付図下段の結果が得られます。

お礼

詳細な図解、ありがとうございました～！
わかりやすかったです！

元データのレイアウト変更が可能か、試します！

回答No.3

KURUMITO

回答2ですが元の表では商品の価格で円が付いていますがこれはセルの表示形式で表示されているもので実際のデータは円が付いていない形になっているものとしています。

お礼

あっ、はい。
・売上実績がない＝データが空白　の欄は０値
・円表示は、実際には数字
です。

…となると、ピボットの「個数をカウントする」では、ムリかな…？
だって、ゼロ値も数えちゃいますよね？？
かといって、数字を消しちゃうと、ピボットって動かせないんですよね？確か。

回答No.2

KURUMITO

ビボットテーブルを利用する方法は取引先、支店名の増加などでは再びビボットテーブルを操作する必要があるなど不便なことがあります。
関数を使って別のシートに表示させるようにすればさまざまな変化に瞬時に対応させることができます。
シート1にお示しのデータがあるとしてA2セルからG2セルにかけて項目名が入力されておりそれぞれのデータが稼業に入力されているとします。作業列としてH3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C3="","",IF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,""))

シート2にお求めの表を表示させるとしてA2セルからF2セルにかけて担当部　担当課　取引先名　商品Ａ　商品Ｂ　商品Ｃと項目名をそれぞれ入力します。
A3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$H:$H),"",INDEX(Sheet1!$A:$C,MATCH(ROW(A1),Sheet1!$H:$H,0),COLUMN(A1)))

D3セルには次の式を入力したのちにF3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($C3="","",COUNTIF(INDEX(Sheet1!E:E,MATCH($C3,Sheet1!$C:$C,0)):INDEX(Sheet1!E:E,MATCH($C3,Sheet1!$C:$C,0)+COUNTIF(Sheet1!$C:$C,$C3)-1),">0"))

お礼

回答、ありがとうございます！

ま、待って、まず
「もしC3セルが空白なら、空白を表示せよ。空白じゃないならIF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,"")を表示せよ」
と入れるんですね？
IF(COUNTIF(C$3:C3,C3)=1,MAX(H$2:H2)+1,""))
は
「COUNTIF(C$3:C3,C3)が1ならMAX(H$2:H2)+1,を表示せよ。１以外のときは空白を表示せよ。」
の意味、更に
COUNTIF(C$3:C3,C3)
は
「C列３行目（固定）から、C3までの範囲で、C3セルに入ってる値と同じ値の個数を求めなさい」。

…メモります。メモって明日会社で試します…！

…お盆休みで上司達がお休み（下っ端は出勤…）なので、会社から直接このページにアクセスしちゃあだめだろうか！と思いつめる昼下がり。