ピボットテーブルをマクロ化

質問

はじめまして
業務でピボットテーブルを使うことが多々あるのですが、マクロ化したいものの、なかなかできない状況です。
お助けいただけませんか？

以下のような２列のデータがあります。（行数は毎回異なります,
また１列目の数字も無数にあります）
1列目は数字と文字によるもの
２列目は正負の数値

100 t 40
200 t 60
100 t -50
300 t -20
300 t -10
200 t 10
100 t -10
200 t -90
300 t 50
100 t 20
300 t 30
200 t -10

これらのデータを２列目の正負によって場合わけして、１列目の文字列別に集計したいです。
つまり今回の場合は
100 t 60
200 t 70
300 t 80
100 t -60
200 t -100
300 t -30
のようになります。
手作業で行うと正負の場合わけをした後に、ピボットテーブルを２回使用しなければなりません。
よろしくお願いいたします。

回答No.6

MackyNo1

>ただ、ピボットテーブルまではできるのですが、
100 t 60
200 t 70
300 t 80
100 t -60
200 t -100
300 t -30
のように並び替えるのにはどうすればよろしいでしょうか？

上記の質問に回答するだけなら、ピボットテーブルでcodeの上にカーソルを置いて「A↓Z」の昇順のアイコンをクリックする操作を加えればよいと思います。

しかし、例示されたコードをみると、B列の一番下のセルの右のセルを取得するところとピボットテーブルの作成範囲が固定されていますのでデータ数が増減すると問題が発生します。

前者については、マクロの記録で対応するなら、B列の一番下のセルを選択後に相対参照に切り替え、１つ右のセルを選択するか、Selection.Offset(0,1)のように選択した範囲の１つ右のセルを指定するコードにする必要があります。

後者については、簡便に対応するならデータ範囲を大きめに指定してピボットテーブルを作成し、ピボットテーブルに表示される「（空白）」という欄を右クリックして「表示しない」を選択するのが簡単な対応です。

あるいは「挿入」「名前」「定義」で参照範囲に「=OFFSET($A$1,0,0,COUNTA($A:$A),1)」のような数式を入力しておいてピボットテーブルのデータ範囲を自動取得できるようにしておき、この範囲の名前をコード内に使用することもできます。

しかし、最も確実な対応策は、すでに回答したように、いったんピボットテーブルを作成しておき（この場合ピボットテーブルのデータ範囲に上記の名前定義を利用する）、新たに作成したいデータを元データの上にコピー貼り付けして（必要に応じて元データの削除をしてから）、ピボットテーブルの「データの更新」を行うのが簡便でよいと思うのですが・・・