EXCELピボットテーブルでの区分を可変させたい

質問

A列　B列
4 7
2 5
4　 0
8 -8
5 3
1 2
8 　4
2　-3
6　 0

上記のようなデータでＡ列を1～3、4～6のようにグループ分けして、ピボットテーブルで下記のような和や平均を求めたりします。

1～3　　5+2+（-3）＝4
4～6　　7+0+3+0＝7
7～10　　（-8）+4＝-4

そしてグループ分けをいろいろ変えたいのです。

1～5　　7+5+0+3+2+（-3）＝14
6～10　　（-8）+4+0＝-4

区分を変える手軽な方法はないですか？

回答No.2

imogasi · ベストアンサー

ピボットテーブルを作る。その後
シートにコマンドボタンを１つ貼りつけます。ダブルクリックすると、ＶＢＥ画面に飛び
Private Sub CommandButton1_Click()

End Sub
が出ます。
Private Sub CommandButton1_Click()
Range("A4").Select
Selection.Group Start:=Range("j1"), End:=Range("j2"), By:=Range("j3")
End Sub
のようにします。
どこでも良いがデータの邪魔にならない列、例えばＪ列に
Ｊ１始めの値
Ｊ２終りの値
Ｊ３飛び飛びにする値。ステップ値。
の数値を入れます。
（例えば初め１、終り１０、ステップ３とすると１、１－３、４－６、７－１０の区分けになります。）
これらはその都度いれる。
そしてデザインモードを脱して、ボタンをクリックすれば
即座に色々な区分けに出来る。

お礼

回答、ありがとうございます。
シートにコマンドボタンを貼りつける手順は過去の質問の検索で、
「表示－ツールバー－フォームのボタンと、表示－ツールバー－コントロールツールボックスのボタンがあります。
前者は右クリックにマクロ登録があり、後者はデザインモード時にダブルクリックでVBE画面が開きます。」
とあり、解決しました。

ですが、コードをコピペしてボタンをクリックしても、
Selection.Group Start:=Range("j1"), End:=Range("j2"), By:=Range("j3")
でエラーになります。どこに問題があるのでしょうか？
他の方でも、よかったらお教え下さい。

補足

回答、ありがとうございます。便利そうですね。

シートにコマンドボタンを貼りつける手順が分かりません。下記のように二通りやってみましたが、コードをコピペして実行しても最終行でエラーになります。正しい方法を教えてください。

1）ユーザーフォームの挿入→ツールボックスからコマンドボタンをUserForm1にドラッグ→ダブルクリック→ＶＢＥ画面

2）「表示]→[ツールバー]→[フォーム]→フォームツールバー → ボタンをクリック→シート上で＋記号をドラッグ→ボタン1とマクロの登録ダイアログボックスが出る（ボタン1_Click)→新規作成→ＶＢＥ画面に飛び、Module1に
Sub ボタン1_Click()

End Sub
が出ます。

回答No.1

mshr1962

グループ分け自体は、項目を選択して右クリックで
「グループとアウトラインの設定」で「グループ化」できますが

区分を手軽に変えたいなら、元の表にグループ化用の列を作って
設定を切り替えるのが楽だと思います。

数式でなら上記でグループ化するのが数値の範囲なら
1～3 =SUMIF(A:A,"<=3",B:B)
4～6 =SUMIF(A:A,"<=6",B:B)-SUMIF(A:A,"<=3",B:B)
7～10 =SUMIF(A:A,"<=10",B:B)-SUMIF(A:A,"<=6",B:B)

"<=3"は　C1に3があれば　"<="&C1 でも可

こんな関数の使い方もあります。
=SUMPRODUCT(($A$1:$A$100>=1)*($A$1:$A$100<=3)*(
($B$1:$B$100))
A1～A100が1～3の間の数値の行のB列の値を集計します。

お礼

解決したので、この欄を借りて２人にお礼します。

＃２の方のマクロのエラーの件ですが、
質問では、Ａ列、Ｂ列はデータでピボットテーブルは別の位置のつもりでした。私の説明が不十分でした。
セルＡ１にピボットテーブルという回答だったんですね。
ヘルプで「Range オブジェクトがピボットテーブル フィールドのデータ範囲内の単一セルを表す場合、Group メソッドはそのフィールドで数値またはデータのグループ化を実行します。」とありました。
それで、Range("A4").Select　な訳ですか。
実際のデータはかなりの量なので、自在に範囲分けができて大変助かります。

補足

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

＞区分を手軽に変えたいなら、元の表にグループ化用の列を作って
　設定を切り替えるのが楽だと思います。

ピボットを使うのに、この方法を知りたいのです。詳しく教えて頂きたいです。

ピボットを使わない場合のSUMIF、SUMPRODUCTは便利ですね。参考になりました。
因みに、同様に平均を出せる関数はありますか？