★ExcelVBA ~ 【データの絞り込みの異色的な方法・ピボット使用】あるデータにおいて、店番ごとにレコードを絞り込んで新規ファイルにそれをコピーするVBAにて、一般的にフィルタを使うけれど、それを使わずにピボットを使うやり方について。(QueryTableオブジェクトを使う、にも通ずる?)
  
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
  
  

サンプルファイルのダウンロード

  

上図のようなシートがあった場合に、
『ここから、店番ごと(何らかの条件ごと)にレコードを絞り込んで新規ファイルにそれをコピーしたい(絞り込み条件も自動生成したい)』
というようなことをVBAでやりたい場合、一般的な回答は以下の通りです。

  

このコードはシンプルで動作も速いです。Dictionaryオブジェクトを使っています。
ただ、これだと以下のことができていません。

(01)「そもそもソースデータが本当にミスなく入力されているか?」
   のチェックが丸で無い。

(02)出力結果が「テーブル形式」になっていない。
  (結果をついでにテーブルにするコードを付け足せばいいだけですけれど)

(03)ソースの条件をいろいろに変えるなどの変化に弱い。

Q&AサイトでExcelVBAをサンプルとして提示する人たちは、
短いことこそが正義、みたいなところがあって、
「システム化」「今後のシステム化に向けて」「変化への対応のしやすさ」、
という観念が、結構ズッポリ抜けてる「即物的」な回答をする人が多いです。
そして「ループ」しか使いません。
ちょっと驚きなんですけど。
僕は、「ループしか使いません。」という「ExcelVBA使い」の人たちに対して、
いつも理解に苦しみます。
なぜSQLやピボットのVBA操作を使わないのか?
不思議でなりません。
なんで、いちいち、機能の少ない配列やループしか使わないのか?
ピボットもQueryTableオブジェクトも、それら単体で見ると、
配列の動作速度とほぼ同等か、逆に速い場合もあるようなのに。
そして、複雑な条件に簡単に対応できるのに・・・。

でも、そもそも質問する側の人が、一般的な回答以上のことが理解できないので、
もちろんそれは仕方のないことで、全くもって責められることではありませんので
それで全然いいのですけど。
(でも例えば知恵袋のカテゴリーマスターのような力のある人には、ただ短いコードを自慢するだけでなく、「この自分のサンプルコードはこういう場合には向かない・向く」など、色々と説明はしてほしいですよね)

今回は、僕が書いたコードがいい、ということではまるでなくて、
(そもそも自分も初心者なのでいいコードなんて書けるわけもなく・・・)、
でも、
「せめて少しくらいは、システム化を意識して」
「今後のシステム化に向けて」
「変化への対応のしやすさ」
みたいなことも考えると、
遠回りに見えたり、ダサいと見えたりするかもしれませんが、
でも、
「こういう方法もあるよ」
「のちのちラクになるよ」、
という「事例のご提示」です。

  

で、
前述の(01)~(03)のことを少し意識した(完全解決ではないですけど)、
意外と変化に対応できる、ピボットを使ったコードが
これ以降のコードになります。

このコードが冒頭の先にご紹介した一般的なコードと違うのは、
少し「システム化」されている点です。

例えば冒頭のコードは、条件作成に「配列とループを使用してしまっている」…
というか、逆に、
「配列とループしか使用してない」ので、
「複雑な条件に弱いですし、複雑じゃなくてもちょっと弱い」です。
つまり、
動作が速いだけ。
コードが短いだけ。
変化に弱い。
という感じでもあります。
(ちょっと手の込んだ条件を作りたい時に、メッチャ複雑なアホみたいなコードを追記しないといけなくなる。)

  
以降のコードは、そういうことが無いです。
複雑なアホみたいなループコードを付け足す必要がありません。

条件作成にピボットを利用して少し柔軟な条件設定ができるようにしています。
同時に、ピボットのドリルスルーの機能を利用して、それをフィルタの代わりにしています。
ピボットのドリルスルーの機能を利用することで、絞り込み結果の表も、
1行のコードの追加も不要・かつ・自動的に「テーブル形式」になります。

正直動作速度については、
冒頭のDictionaryを使った一般的な例よりは遅いですが、
でも上記の(01)~(03)などのことを同時にカバーできます。
さらなるシステム化もしやすく、もちろん「待てないほど遅い」、
ということではまるでありません。

ピボットを作るコードはコピペして使うだけなので、考えなくてもよく、
基本的には、最初の「BookFileMakeTest001()」というプロシージャを
書き換えて使うだけです。
コメントを消すと、以下の分量なのでさほど多くないです。
また、条件に配列やループを使わず、ソースの表の列名で書けるのでラクちんです。

  

そして「ピボットを作るだけのプロシージャ=Excel2010_MakePvt02()」の中で、
『ソースの表は「Offset関数」を使って名前の定義をする』という処理をして
ありますので、列や行が増減しても変化に対応できるようにしてあります。

  

  
以上のような考え方で、少しシステムめいた感じにしてしまいたい場合、
ピボットの代わりにMicrosoft Query(QueryTabgeオブジェクト)を
使ってもいいと思います。

さらには「Microsoft Query(QueryTabgeオブジェクト)+ピボット」を利用することでもさらに複雑な条件設定ができ、色々にカバーできると思います。

では、以降、ピボット(+ドリルスルー)を使ったちょっと異色なデータの絞り込みのコードです。