● ピボットテーブルのソースの表を、行も列も可変にして、再設定しなくても済むようにする方法 2つ(「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」とテーブル機能)

※関連記事
Excel2010・2000でのピボットテーブルのワンクリック自動作成 ~ 現在の表に自動的に名前の定義をして、「ソースの表として行と列が増減しても範囲の再設定が不要にした状態」での、ピボットのワンクリック自動作成

★ 全バージョン共通の方法
「OFFSET関数を使った定型の式をコピペして名前定義」をする方法

一応、列も行も増減する(どちらかというと増える)ことを想定しますので、表の入力は「1シートに1つのリスト表」という形を前提とします。(将来的にDAOやADOなどで見に行くかもしれないし。)

リスト表は、1行1レコード形式の表です。
リスト表は、1レコード が カード形式のデータベースの 1 画面分に相当します。
よって、リスト表は、「カード形式のデータが行数分、集まった表。」ということになります。

このようなリスト表の 列名を1行目に作ります。空白行、空白列は絶対に作りません。
時系列順に、下方向にデータを蓄積していきます。

なお、「列名だけ」でデータが一件も無い場合でも名前の定義はできます。

表に対して、名前の定義をするときは以降のことに気を付けます。

範囲には必ず「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」という式のみをコピペします。(下図参照)

表の名前は自由に決めていいです。

なお、「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」の設定を見直すようなときは、その時点ではすでに、Excelがシート名を自動的に付加してくれています。
ただ、式にシート名が加わってしまっていますものの、直す必要はありません。
また、再度、「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」とコピペしたとしても再度、シート名が勝手に付加されるだけで問題ありません。特にエラーにもなりません。

この式は、どのシートでも、どんな名前でも、列名を1行目にしたときは必ずこのまんまの式をコピペします。(列名の行を変えたいときは後述を参照)

以上です。

これでピボットテーブルのデータ範囲の指定の時に、この定義された名前を使えば、行が増えても列が増えても、逆に両方ともが減っても、範囲の指定をし直す必要はありません。

データを順次、入力していきます。
1行データを入力すれば、列名とそのデータで2行分が自動的に「表」とみなされます。
2行データを入力すれば、列名とそのデータで3行分が自動的に「表」とみなされます。

なお、この定義された名前は、VBAプログラムからも「表」として認識できるのでプログラミングが少しラクになります。VBAからも可変の表として見えているので、行や列の増減の処理は必要ありません。

DAOなどのミドルウェアで表を見に行くときは、シート名($の付いたシステムテーブル名)で見に行きます。そのため、1シートにつき1つの表としています。より面倒の無い表操作がしたい場合は、DAOやADOなどを使うほうがやりやすい場合もあるかもしれません。

※参考記事
Excel2010で、開かれていない閉じたままのブック・Excel(xls)データをDAOにて読み込む方法 https://euc-access-excel-db.com/tips/ct07_se/ct075010_ac2ktips/readbydao

なお、Microsoft Query で抽出した結果を別シートに表示した場合も、このやり方が使えます。
そのシート上でも、その結果の表に名前の定義をして、それをピボットのソースにできます。(もちろん、行も列も可変です。増減自由です。)

「Microsoft Query で都度違う各種条件で絞り込みをやりなおし→ピボットの更新」
を繰り返すことでいろんな集計ができます。
場合によっては、ソース側の列構成が変化することもあると思いますので、その場合は、ピボット側の行や列の構成をやりなおすだけです。

フィルタをピボットのソースにしてしまうと、(見た目は絞り込まれていても)絞り込み前の全レコードが集計対象になってしまい、ソース側の見た目結果は反映されません。

※列名を11行目からにしたいとき、つまり、表の上に10行の空白を設けたいときは、式の「1」を「11」に変更します。 「=OFFSET($A$11,0,0,COUNTA($A:$A),COUNTA($11:$11))」というように。
もちろん、この場合でも行や列が増えても減っても問題ありません。
ただし、列名の上に空白行を入れてしまうと、Microsoft Query や DAO、ADO、などから正常に表として認識されないかもしれません。(されるかもしれませんのでご自分でテストしてみてください。)
  
  
  
★ バージョン2007以降
「テーブル機能」を使う方法

ピボットのソースにしたい表を、「テーブル機能」でテーブル設定します。
テーブルとしての名前は好きな名前でOKです。

以上で、ピボットテーブルのデータ範囲の指定の時に、この定義された名前を使えば、行が増えても列が増えても、範囲の指定をし直す必要はありません。

この定義された名前は、たしかVBAプログラムからも「表」として認識できたと思います。
が、VBAからも可変の表として扱えるかや、それに伴う行や列の増減の処理は必要か不要かは未確認です。
  
  
※「テーブル機能」を使うとそこそこ便利なのですが、フィルタをかけた「結果」そのものの表をピボットのソースにできません。フィルタをかけてもかけてない状態の全データ(全レコード=全行)がピボットの集計の対象になってしまいます。
また、単一の表しか使えず、リレーションが組めなかったり、フィルタをかけながらできるとこだけでも事前集計をしてしまう、といったこともできません。

逆にそのようなことがしたい時は、Microsoft Queryにてリレーションや部分集計をしながら同時に条件絞り込みもしてから、その結果表に対して、前述の「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」を使ってピボットソースにすると集計の手間がかなり省けて便利です。条件変更や更新がラクになります。

なので僕は「テーブル機能」は、ほとんど使ったことがありません。
でも、僕はExcel音痴なので、僕が単に知らないだけできっと、何か、良い利用シーンがあるように思います。