● ピボットテーブルのソースの表を、行も列も可変にして、再設定しなくても済むようにする方法 ２つ（「=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))」とテーブル機能）
★ 全バージョン共通の方法
「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音痴なので、僕が単に知らないだけできっと、何か、良い利用シーンがあるように思います。
- 投稿タグ
- ExcelVBA, Excelの独学, Excel連携VBA, MicrosoftQuery, パソコンでの自動化, ビジネスパソコンの基礎, ピボットテーブル関連, マクロ, 独学, 自動化
