ピボット作成時に (空白) カテゴリーを作らない方法

次の記事の補足情報です。

ピボット処理の自動実行
http://xoblos.hatenablog.jp/entry/2017/08/19/233737

上記の記事の方法でピボットテーブル、ピボットグラフを作成すると、ひな形データシートの3行目までを範囲とするため、余分な (空白) カテゴリーができてしまいます。ピボット集計上は何ら問題ないため、そのままにするか、気になる場合は軸フィールドのフィルタリングでオフにする方法で問題ないと思います。

どうしても (空白) カテゴリーを作りたくない場合は、下記の2つの方法があります。1. の方法は、処理速度が遅いため、データ量が多いときはお勧めではありません。

  1. データシートの最終行を #S 記号で削除する方法
  2. 外部スクリプトで、自動的にピボットテーブルを更新する方法

1. データシートの最終行を #S 記号で削除する方法

ひな形シートの3行目までをピボットテーブルからデータソース参照しているため、余分な空白行(最終行)をカテゴリーにしてしまうので、その行を削除する手法です。

原本ひな形データシートの3行目、再右列の隣に「#S」記号を入れておきます。

f:id:xoblos:20170820161558p:plain

#S は、Spacerの意味合いで、処理後に削除する行(分類項目の列位置)に指定します。

データをインポートする制御シートに、#行番号 を設定しておきます。

f:id:xoblos:20170820161612p:plain

「#行番号」は、[固定値] を「%ROW」にして行番号を生成し、[区分] を分類項目(この例では「詳細行」)にしておきます。[整形対象部分の右下] を、ひな形データシートの3行目、#S 記号を記入した列のセルにしておきます。

この方法で (空白) カテゴリーはできなくなりますが、各行が分類カテゴリー(#行番号)となるため、データ量が多いときはかなり処理時間がかかります。

2. 外部スクリプトで、自動的にピボットテーブルを更新する方法

この手法を使う場合、原本ピボットテーブルの [データソースの変更] で3行分の範囲指定を前もってしておく必要はありません。 外部スクリプトがデータシートのレコード数を判断して、自動的に [データソースの変更] を行うからです。また同様に、[ファイルを開くときにデータを更新する] をチェックオンにしておく必要もありません。

また、処理速度も 1. の手法よりもかなり速く実行できます。

データインポート手順の下に、外部アプリ実行手順を加え、外部スクリプトVBScript)を実行してピボットテーブルの更新を行います。

f:id:xoblos:20170820213203p:plain

実行するファイル SCRIPT\PivotUpdate.vbs (この例では [SCRIPT] フォルダに配置)
         スクリプトのソースコード
コマンドライン引数
  第1引数 出力ブックのパス名
  第2引数 データシート名(この例では「データ」)
  第3引数 ピボットシート名(この例では「ピボット」)
  第4引数 ピボットテーブル名(この例では「"ピボットテーブル1"」)
作業フォルダ . (カレントフォルダを指定)