可変な範囲に名前を定義する

毎月、ピボットテーブルを使った集計をしています。

集計対象のデータ数が毎回変わるので、
ピボットテーブルのデータソース範囲を
毎回更新するのが面倒でした。

f:id:piyoco-garden:20160705230551p:plain

色々と調べていくうちに、
「可変な範囲に名前を定義できる」ということが分かりました。

やり方としては、名前を定義する際、
OFFSET関数とCOUNTA関数を組み合わせて、
参照範囲を指定します。

OFFSETの引数は、以下の通りです。

基準となるA1セル（範囲の左上端）から、
「A列に記載されているデータ数 - 1」だけ下へ
「1行めに記載されているデータ数 - 1」だけ右へ
進んだ箇所が、範囲の右下端となります。

「名前の管理」から、「新規作成」を選択し、
「参照範囲」に以下の数式を指定してみます。

=Sheet1!$A$1:OFFSET(Sheet1!A$1,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!1:1)-1)

左上端から右下端を、数式で表現することができました。

作成した名前を、ピボットテーブルのデータソース範囲に指定します。
これで、データが増減しても、データソース範囲は最適化されます。

Excel方眼紙からの脱却