奮闘記（3）範囲設定
ピボットテーブル・元データの範囲設定について
今回は、ピボットテーブルのデータソース、元データの範囲設定について紹介します。
ピボットテーブルを作るには、分析する範囲を指定します。
テーブルもしくは範囲を指定すれば簡単にピボットテーブルが出来ます。
ピボットテーブルのソースデータを変更する
ここまでは、どの本にも書いてあります。元データを追加して、集計をし直す。
この時の作業、面倒ですね。ピボットテーブルを実務に使おうとするほど、
この煩わしさが生じます。
解説本には、きちんと説明されていませんので、ピボットおじさんの秘訣を
ご紹介します。
ただし、「ピボットテーブルの更新」は、忘れずに実行してください。
①範囲を直接直す・・行を増やす、調整が必要です。
解説書では、ショートカットキーで↓↓というものもあります。
②データテーブルを使う・・そのままで、自動的に調整されます
③数式/名前を使う・・範囲を登録するれば、自動的に調整されます
データテーブルを使う
Excel / データのインポートと分析/ 表／表示 Excel のテーブルの概要
表を作成し、書式設定する
②のデータテーブルの利用は、ユーザーにとって使いやすい方法で、
先ずは、この方法です。
しかし、業務用にデータ件数が多くなったりすると、Ｅｘｃｅｌの自動計算に時間がかかります。再計算で、データテーブル以外無効という使い分けは難しいと思います。
Excel のパフォーマンス: パフォーマンスの向上と制限の改善
おじさんの秘訣・・「名前」を使う
Excel の名前マネージャーを使用する
③ピボットおじさんの業務用では、データテーブル機能を使わない方法をとっています。
次回以降のお話になりますが、ピボットテーブルで集計した数値を使って、新たなピボットテーブルを作成しています。「マルチピボット」と名付けていますが、予算実績管理などシステムに匹敵する分析が可能になります。
残念ながら、データテーブルではピボットテーブル自体を設定できないので「名前」として登録しています。
さて、「名前」で、範囲を指定する方法は、
〇INDEX関数を使う・・この方法をお勧めします。
＝$A$1:INDEX($(右端列):$(右端列),COUNTA($A:$A))
右端列は、ピボットテーブルを作るときに入力します。COUNT($A:$A)は、Ａ列の件数で関数で自動計算です。
名前を登録するときは、ワークシート名は入力しなくても、自動的に付与されます。
×OFFSET関数を使う・・2003まではこの方法でした。しかし、Ｅｘｃｅｌの仕様が変更になって「揮発性関数」として扱われるます。データ件数が多いと再計算に時間を要しますので、おすすめしません。
=OFFSET(A1,0,0,COUNTA($A:$A),COUNTA(1:1))
ＶＬＯＯＫＵＰ関数でも、参照範囲の設定で、同様な対応が必要です。
※OFFSET関数を解説書が紹介している解説書がありますが、揮発性関数の使用は避けて、INDEX関数を活用しています。
