ピボットテーブルで中央値を表示させるためのマクロ修正法

エクセルのピボットテーブルでは中央値が出ないので、マクロ入門書を片手に奮闘しています。
しかし、全くうまくいきません。

.Function = xlAverage　となっていたのを
.Function = xlMedian　としてみたのですが、中央値とは全く異なる値（たぶん総計）が返されてしまいます。

正しい方法をご存知の方、どうか教えて下さい。宜しくお願い致します。

No.3 ベストアンサー 回答者： imogasi 回答日時： 2005/09/20 19:24

＃１です。

>データ数が膨大なので、手作業でプログラムを作るのは難しそうです
「手作業で」とはどういう意味なんでしょうか。私が前半で例示した
ようにプログラムせよ、なんて言ってません。そうとったのかもしれない。あくまでも判りやすいかなと例示したまでです。
私が、最後の部分に書いた「実際ではCells(1, 5) = WorksheetFunction.Median(Range("b2:b5"))
のRange("b2:b5")などの部分を（商品の変わり目を）プログラムで割り出してこの部分にRange（Cells（）、Cells（））の（）内にセットすればよい」
の意味を理解してもらえなかったようです。
この線でプログラムを組めば、全体で１０数行程度でできます。
それを想起してもらえないのなら、コードを上げても、自分のケースに合わせて、修正できないでしょうから無駄かなとも思います。

この回答へのお礼

補足ありがとうございます！
恥ずかしながら、だいぶ誤解していたようです。
がんばって挑戦してみます。。

お礼日時：2005/09/21 00:23

No.2 回答者： Wendy02 回答日時： 2005/09/19 13:19

こんにちは。

＞.Function = xlMedian　
というプロパティの設定を確認しましたが、2002までにはありません。それ以上のバージョンでは分りませんが、xlAverageなどというのは、組み込み定数であって、それ自体が関数や命令ではありません。違うものが入ると、Default になる設定がされているのかもしれません。

ただ、それがない場合は、ピボットテーブル自体が、一種の既成マクロなのですから、ある程度、ご自分で同じように再現して表現するしかないと思います。ピボットテーブルの計算欄に埋め込み型で作るという方法もないわけではありませんが、マクロ初心者のレベルの方には、ちょっと無理かもしれませんね。－私のイメージでは、そんなに簡単ではありません。

レイアウトがあちこち変わらなければ、やはり一般の関数で行うのが良いのではないか、と思います。

例えば、

A　　　　B
名前　　数字1
a　　　　1
b　　　 15
b　　　 20
a　　　　1
・　　　・
・　　　・
・　　　・

このようになっている時に、
=MEDIAN(IF(A2:A17="a",B2:B17,""))

※　配列式ですから、一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定してください。

とすれば、取れます。
もちろん、マクロにこの式を取り込めることが出来れば、TableRangeの　Value値がジャグ配列になっていますから、そこで、検索値を求め、そのまま戻り値を書き込めばよいです。その時は、ワークシート配列式は関係がなくなるので、値そのまま代入できます。でも、ジャグ配列を取り扱えるようになるには、上級文法を終えないとちょっと難しいかもしれません。

この回答へのお礼

どうもありがとうございました。

マクロ初心者の私にはやはり難しそうです。
統計解析ソフトを使う等、別の策を講じてみます。。

お礼日時：2005/09/20 15:01

No.1 回答者： imogasi 回答日時： 2005/09/19 07:15

＞Function = xlMedian　としてみたのですが、中央値とは全く異なる値（たぶん総計）が返されてしまいます

合計を返すようですね。
その前に「xlMedian」の記述が許される（そういうメヂアンのプログラムが
エクセルに組まれていてxlMedianで呼び出せる）かどうかが問題です。ダイアログに出てくる種類と、オプションで出てくる計算方法以外にユーザー定義関数的な指定はできないのではないでしょうか。エラーは出ないのが不思議だけれど。（断定は自信なし）
もしそうであれば
VBAでプログラムを組む必要があります。
たとえば下記例では、配列にデータをいれ、商品でソートし
商品の切れ目までを、指定する。
手操作かVBAで、シート状でソートするとやりやすい。
Sub test01()
Cells(1, 4) = Range("a2")
Cells(1, 5) = WorksheetFunction.Median(Range("b2:b5"))
Cells(2, 4) = Range("a6")
Cells(2, 5) = WorksheetFunction.Median(Range("b6:b7"))
Cells(3, 4) = Range("a8")
Cells(3, 5) = WorksheetFunction.Median(Range("b8:b10"))
Cells(4, 4) = Range("a11")
Cells(4, 5) = WorksheetFunction.Median(Range("b11:b13"))
End Sub
データ例A1:B13
商品価格
a1
a5
a7
a2
b3
b1
c4
c3
c4
d6
d5
d6
結果D1:E4に
a3.5
b2
c4
d6
実際では
Cells(1, 5) = WorksheetFunction.Median(Range("b2:b5"))
のRange("b2:b5")などの部分を（商品の変わり目を）プログラムで割り出して
この部分にRange（Cells（）、Cells（））の（）内にセットすればよい。

この回答へのお礼

アドバイスどうもありがとうございました。

データ数が膨大なので、手作業でプログラムを作るのは難しそうです。そもそもピボットテーブルにはなぜ中央値の設定がないのでしょうか？？困ったものです・・

お礼日時：2005/09/20 15:04