EXCELのピボットテーブルで最頻値を集計できないか

ピボットテーブルフィールドの集計の方法は、ウィザードによれば、

　　合計
　　データの個数
　　平均
　　最大値
　　最小値
　　積
　　数値の個数
　　標本標準偏差
　　標準偏差
　　標本分散
　　分散

の中から選ぶことになっているのですが、ここに、

　　最頻値　・・・関数で言えばmode関数

を持ってきたいのですが、何とかそのようにするやり方はないものでしょうか。

No.1 ベストアンサー 回答者： imogasi 回答日時： 2008/03/29 13:44

例データA1:B12　　X,Yは項目見出し

A列　　　B列
XY
a3
b2
a1
d2
a3
d2
f2
a3
c4
d1
d2
ーーー
A列でソート
結果
A列　　B列　　　C列
xyz
a33
a13
a33
a33
b22
c44
d22
d22
d12
d22
f22
ーーー
C2に
=IF(COUNTIF($A$2:$A$12,A2)=1,B2,MODE(IF($A$2:$A$12=A2,$B$2:$B$12)))
と入れて、カーソルを数式バー部に置いて
Shitt、Cｔｒｌ、Enterの３つのキーを同時押しする。
配列数式。
結果
上記のC列の通り
ーーーーーーー
A1:C12を範囲指定
データーピボットテーブル・・
次へを２回
レイアウトボタンを押して
行にX
データに平均を指定する（ここがミソ）
完了
ーーー

平均 / z
x合計
a3
b2
c4
d2
f2
総計2.545454545
苦肉の要領による方法なので、「合計」や「平均」行などが使えないが、行の非表示で対処するとか。
＝＝＝
ピボットテーブルでやっている、クロス表、頻度表を作る機能は、VBAでも比較的簡単なロジックで作れるので、そちらでやることも出来る。
ソート後の表で、上例でのaの終わり行さえ捕まえれば、VBAでもMODE関数は使える。
Sub test01()
x = Application.WorksheetFunction.Mode(Range("B2:B5"))
MsgBox x
End Sub
結果は３
http://oshiete1.goo.ne.jp/qa3406240.html（過去質問）

この回答への補足

結局、=mode(if( 　))の配列数式でを使って、最頻値を計算するための行を一行作って、そこで作った最頻値行を別の表にコピー＆ペーストする、という形で、ピボットは使わない手作業ですが、何とか実用になる手間の中に押さえることができました。ありがとうございました。
3/31 AM 0:20

補足日時：2008/03/31 00:24

この回答へのお礼

なるほど、ピボットにする前に予め、値を最頻値に置き換えた列を作ってしまえばよいわけですね。そのためには、配列数式を使うのですね。
そして、mode関数は、引数が一つではエラーになるので、引数が一つになる場合と複数になる場合とを分けるために、配列数式の中身がIF文付になるわけですね。

配列数式文も含めてデータ量がかなり大きくなりそうなので、また、いろいろな確度からクロス表を作るために、カウンタ列を増殖させていったらさらに膨大になりそうなので（汗）、どれくらいデータ量が増えそうか、やってみます。

どうもありがとうございました。見通しがつき、助かりました。

ところで、予めA列でソートしておかなくても、配列数式は機能しますよね？

お礼日時：2008/03/29 23:30