ピボットテーブルの集計方法の変更をマクロで

こんにちは。長文ですみません。
ピボットテーブルを使って、データ集計をしています。
記録でできるかと思ってマクロ登録してみたのですが、エラーになります。

元のエクセルデータは、
A (資格)　 B(データ(1))　C(データ(2))
　　医師　　　　4.3　　　　　6.2　
　　栄養士　　　3.5　　　　　7.8
　　看護師　　　2.9　　　　　5.7
　　医師　　　　5.0　　　　　3.5
　　　・　　　　　・　　　　　・
　　　・　　　　　・　　　　　・
　　　・　　　　　・　　　　　・
という感じで700件程度入っていて、各資格のそれぞれのデータについての平均点を出していっています。

　　　　　データ(1)の平均　　データ(2)の平均
医師　　　　　4.8　　　　　　　4.9
栄養士　　　　3.5　　　　　　　7.8

といった感じです。


ピボットの行フィールドに「資格」、列フィールドには何も入れず、
データアイテムにそれぞれのデータを入れて、別シートにコピペしていっているのですが、
毎回データを入れ直すたびに集計方法がデータの個数になって、平均に変更するのが面倒なので、マクロでパッと変換する方法はありますか？

実際は行フィールドに入れる項目が10個くらいあるので、全て1つずつしなくてはならず、本当に時間がかかってしまって…。

マクロはまだまだ初心者で分からないので、よろしくお願いします。

No.1 回答者： Dxak 回答日時： 2005/12/14 13:42

えっと、すみません。

先に確認ですが、Excelのバージョンいくつですか？
あと、サービスパックは当てています？
当方Excel2002SP03でサンプルを上げて頂いたデータ
> A (資格)　 B(データ(1))　C(データ(2))
> 　　医師　　　　4.3　　　　　6.2　
> 　　栄養士　　　3.5　　　　　7.8
> 　　看護師　　　2.9　　　　　5.7
> 　　医師　　　　5.0　　　　　3.5
> 　　　・　　　　　・　　　　　・
> 　　　・　　　　　・　　　　　・
> 　　　・　　　　　・　　　　　・
（資格名に０１、０２・・・と追加して）
１０００件ほどデータを増やして平均値で計算させ、データの更新、参照先の変更を実施してみたのですが・・・。
フィールド名が欠如しなければ、平均が個数に変わることがありませんでした。

再現が難しいのですが、「フィールド設定」で、平均、書式など設定されていますか？

この回答への補足

早速ありがとうございます。

Excelのバーションは2000です。SPは4まであてています。

自分の方もピボット自体があまりきちんと理解できていないので、もしかしたら単純操作ミスかもしれませんが…。

今やっているやり方を説明すると、
行エリアに資格を固定で置いておき、データエリア部分にデータ(1)をドラッグして、列エリアには何も入れないと、各資格のデータ個数の合計のみが出てきますよね。
この例だと、医師は２、栄養士は１、看護師は１という感じで。
そして、テーブル左上(又はデータエリア)から「フィールド設定」を開いて、集計方法を「平均」にするんです。

そして、次にデータ(1)を外して、データ(2)を入れるんですが、このデータを入れ替えると、計算方法が「データの個数」になってしまうので、これをマクロでパッとできればいいなぁと思っています。

そこで、単純に記録させてみたんですが、エラーが出るので、何か方法があれば…と思ったのですが、どうでしょうか？
説明がわかりにくくてすみません。

補足日時：2005/12/14 13:57

No.2 回答者： Dxak 回答日時： 2005/12/14 14:53

えっと、私の勘違いでしょうか？

> 　　　　　データ(1)の平均　　データ(2)の平均
> 医師　　　　　4.8　　　　　　　4.9
> 栄養士　　　　3.5　　　　　　　7.8

と、いう結果が出てきているものだと思ってたのですが・・・

> そして、次にデータ(1)を外して、データ(2)を入れる
> んですが、このデータを入れ替えると、計算方法が
> 「データの個数」になってしまうので、これをマクロ
> でパッとできればいいなぁと思っています。

この手順だと、
> データ(1)の平均
> 医師　　　 　　4.8
> 栄養士　　 　　3.5
と
> データ(2)の平均
> 医師　　　 　 　4.9
> 栄養士　　 　　7.8
という結果が２つになりませんか？
（取替えてすぐの時には個数のはずです。これを計１０回くらい？）

> 　　　　　データ(1)の平均　　データ(2)の平均
> 医師　　　　　4.8　　　　　　　4.9
> 栄養士　　　　3.5　　　　　　　7.8
の形で出して、データの更新だけであれば、平均が個数に変わることは無いと思いますが・・・。

どうでしょう？

単純にこういう形に変えてと言うより、
=SUMIF(Sheet!A:A,"医師",Sheet!B:B)/COUNTIF(Sheet!A:A,"医師")
と言う関数を使用しては？
こちらなら、マクロなしで動かせますが・・・。

> 　　　　　データ(1)の平均　　データ(2)の平均
> 医師　　　　　4.8　　　　　　　4.9
> 栄養士　　　　3.5　　　　　　　7.8

という形で保持し続けることも可能です。

マクロで如何してもなら調べては見ますが・・・。

この回答への補足

書き方が悪かったのかもしれません。すみません。
A (資格)　 B(データ(1))　C(データ(2))
　　医師　　　　4.3　　　　　6.2　
　　栄養士　　　3.5　　　　　7.8
というのは、別シートの元データなんです。

なので、例えば、A列に資格が10種類ほどランダムに入っていて、B列にデータ(1)が、C列にデータ(2)が色々な数値で入っています。それが全部で700行ほどあります。
そのため、ピボットの列エリアは各数字になってしまうんです。必要なのはその総計部分に出る平均の数字だけなので、１回ずつデータの入れ替えをしなくてはいけなくて…。

つまり、700のデータのうち、「医師」の資格を持っている人たちの、データ(1)の平均値、データ(2)の平均値、というふうに出したいところなんです。

うまく伝えられず、ご迷惑をおかけします。

補足日時：2005/12/14 15:21

この回答へのお礼

すみません、訂正です。
　　　　　データ(1)の平均　　データ(2)の平均
医師　　　　　4.8　　　　　　　4.9
栄養士　　　　3.5　　　　　　　7.8

というのは、作りたい表のことでした。元データではありません。

A (資格)　 B(データ(1))　C(データ(2))
　　医師　　　　4.3　　　　　6.2　
　　栄養士　　　3.5　　　　　7.8
　　看護師　　　2.9　　　　　5.7
　　医師　　　　5.0　　　　　3.5
というのが元データです。

お礼日時：2005/12/14 15:58

No.3 ベストアンサー 回答者： Dxak 回答日時： 2005/12/14 16:18

> つまり、700のデータのうち、「医師」の資格を持っ

> ている人たちの、データ(1)の平均値、データ(2)の平
> 均値、というふうに出したいところなんです。

結果、ピボットテーブルで下記のように出したいなら出ます。

> 　　　　　データ(1)の平均　　データ(2)の平均
> 医師　　　　　4.8　　　　　　　4.9
> 栄養士　　　　3.5　　　　　　　7.8

１．「A(資格)」を行エリアに、「B(データ(1))」、「C(データ(2))」を、データエリアに２つ入れます。そうすると、下記のようにピボットテーブルが作成されます。（１０個ほど同じところに入れて、繋げていっても結構です。）

A(資格) データ 合計
医師 平均 / B(データ(1)) 4.650
平均 / C(データ(2)) 4.850
栄養士 平均 / B(データ(1)) 3.500
平均 / C(データ(2)) 7.800
看護師 平均 / B(データ(1)) 2.900
平均 / C(データ(2)) 5.700

２．「A(資格)」の隣に出来た灰色の「データ」と言うセルをドラックしたまま、その隣の「合計」の上に持っていってください。
そうすると、下記のようなピボットテーブルが出来上がります。
データ
A(資格) 平均 / B(データ(1)) 平均 / C(データ(2))
医師 4.650 4.850
栄養士 3.500 7.800
看護師 2.900 5.700

で、以上で出来上がりです。

これなら、フィールドの入れ替えの必要性がありません。

追伸
ピボットテーブルのマクロの方、調べてみてますが・・・、記録だと確かにエラーがコードレベルで見直し中です。

この回答へのお礼

ありがとうございます！
最初にデータを全て入れて、列エリアに持っていかなければいけなかったんですね！
まさに作りたい表が出てきて、随分手間が省けそうです！！

でも、集計方法を一括で平均にするのは難しいようですね…。なんででしょうね。
マクロの方は、記録でなく自分で作るとうまくいったりするのかもしれません。
自分でも調べてみます。

参考になりました、ありがとうございました！

お礼日時：2005/12/14 16:54