奮闘記(3)範囲設定

ピボットテーブル・元データの範囲設定について

今回は、ピボットテーブルのデータソース、元データの範囲設定について紹介します。

ピボットテーブルを作るには、分析する範囲を指定します。
テーブルもしくは範囲を指定すれば簡単にピボットテーブルが出来ます。

分析データの選択

ピボットテーブルのソースデータを変更する

ここまでは、どの本にも書いてあります。元データを追加して、集計をし直す。
この時の作業、面倒ですね。ピボットテーブルを実務に使おうとするほど、
この煩わしさが生じます。
解説本には、きちんと説明されていませんので、ピボットおじさんの秘訣を
ご紹介します。
ただし、「ピボットテーブルの更新」は、忘れずに実行してください。

①範囲を直接直す・・行を増やす、調整が必要です。
 解説書では、ショートカットキーで↓↓というものもあります。
②データテーブルを使う・・そのままで、自動的に調整されます
③数式/名前を使う・・範囲を登録するれば、自動的に調整されます

データテーブルを使う

Excel / データのインポートと分析/  表/表示 Excel のテーブルの概要
表を作成し、書式設定する


②のデータテーブルの利用は、ユーザーにとって使いやすい方法で、
先ずは、この方法です。

データテーブルを使う


しかし、業務用にデータ件数が多くなったりすると、Excelの自動計算に時間がかかります。再計算で、データテーブル以外無効という使い分けは難しいと思います。
Excel のパフォーマンス: パフォーマンスの向上と制限の改善

自動計算-手動計算

おじさんの秘訣・・「名前」を使う

Excel の名前マネージャーを使用する

③ピボットおじさんの業務用では、データテーブル機能を使わない方法をとっています。
次回以降のお話になりますが、ピボットテーブルで集計した数値を使って、新たなピボットテーブルを作成しています。「マルチピボット」と名付けていますが、予算実績管理などシステムに匹敵する分析が可能になります。
残念ながら、データテーブルではピボットテーブル自体を設定できないので「名前」として登録しています。

名前の登録


さて、「名前」で、範囲を指定する方法は、
〇INDEX関数を使う・・この方法をお勧めします。
=$A$1:INDEX($(右端列):$(右端列),COUNTA($A:$A))
右端列は、ピボットテーブルを作るときに入力します。COUNT($A:$A)は、A列の件数で関数で自動計算です。

INDEX関数を使用

名前を登録するときは、ワークシート名は入力しなくても、自動的に付与されます。


×OFFSET関数を使う・・2003まではこの方法でした。しかし、Excelの仕様が変更になって「揮発性関数」として扱われるます。データ件数が多いと再計算に時間を要しますので、おすすめしません。
=OFFSET(A1,0,0,COUNTA($A:$A),COUNTA(1:1))

VLOOKUP関数でも、参照範囲の設定で、同様な対応が必要です。
※OFFSET関数を解説書が紹介している解説書がありますが、揮発性関数の使用は避けて、INDEX関数を活用しています。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です