EXCEL VBA でテーブルのオートフィルター結果を取得する

Pocket

 EXCEL のワークシートに挿入されたテーブルにはデフォルトでオートフィルターが設定されています.このテーブルに対してオートフィルターをかけた結果を VBA で取得する方法は難解で,従来の考え方とは少し異なります.

 より抽象度の高い考え方をする必要があります.リレーショナルデータベースの概念である集合論を理解する必要があります.

テーブル=ListObjectオブジェクト

 テーブルのオートフィルターの結果を取得するには ListObject オブジェクトを理解する必要があります.セル範囲をテーブルにする(OfficeTanaka)に詳細がありますが,ListObject オブジェクトはテーブルを表しており,親オブジェクトはワークシートです.また作成したテーブルには名前が自動的につきます.

テーブルの挿入

 ワークシートにテーブルを挿入するには「挿入」タブの「テーブル」をクリックします.

ワークシートのセル範囲にテーブルを挿入する
ワークシートのセル範囲にテーブルを挿入する

ワークシートをコピーしてマスターテーブルを作成する

 EXCEL で作業していると気がつかないこともあるのですが,データベースを使っているとマスターテーブルというものが存在することに気がつきます.

 今回は実作業に入る前に,作業用データの入ったワークシートをコピーしてマスターテーブルを作成しましょう.

 ワークシートをコピーします.「テーブルデザイン」タブの「ツール」の「重複の削除」をクリックします.

「テーブルデザイン」の「ツール」にある「重複の削除」
「テーブルデザイン」の「ツール」にある「重複の削除」

 ダイアログの初期状態です.全てのタイトルにチェックが入っています.

「重複の削除」の初期状態
「重複の削除」の初期状態

 主キーである City_Code のみチェックしてOKします.

「すべて選択解除」してキーである「City_Code」をチェック
「すべて選択解除」してキーである「City_Code」をチェック

 不要な列を削除し,City_Code, City_Type, Prefecture_Code, Prefectures, City の5列を残します.

不要な列を削除しマスターテーブルとする
不要な列を削除しマスターテーブルとする

 マスターテーブルの入っているワークシート名を CITY_CODE とします.また,元のデータの入っているワークシート名を DATA とします.

散布図のデータ系列には「系列名」「系列Xの値」「系列Yの値」が必要

 図を見ると分かりますが,散布図のデータソースの選択においてはデータ系列を指定する必要があります.

 データ系列に必要なのは「系列名」「系列Xの値」「系列Yの値」です.

 「系列名」はデータ系列が一個の場合はグラフのタイトルになり,複数の場合には凡例になります.

 「系列Xの値」と「系列Yの値」はそれぞれ散布図上の座標の (X, Y) の値に相当し,通常ペアをなしています.通常は対応する同じ大きさのセル範囲,つまり配列を指定します.

 また,データ系列は複数指定することができます.

「データソースの選択」の「系列の編集」
「データソースの選択」の「系列の編集」

 ここから,「系列Xの値」と「系列Yの値」には VBA 上で配列を直接指定することが可能なのではないか?という推測が成り立ちます.今回はこの仮設を検証しましょう.

処理の流れ

マスターテーブルのキーをループする

 20 行目から 21 行目でマスターテーブルのセル範囲を取得しています.この範囲を一個ずつループ(24 行目と 47 行目)してフィルターのキーとします.

データテーブルをキーでオートフィルターする

 ワークシート DATA にあるテーブル(25 行目と 43 行目)に対してオートフィルターをかけます(26 行目).41 行目でいったんオートフィルターを解除します.

オートフィルターの結果を取得する

 オートフィルターの結果を取得する方法は従来とは異なり,ListObject オブジェクトから取得する必要があります.

 トリッキーな方法ですが, .SpecialCells (xlCellTypeVisible) プロパティを用いるのが確実です.タイトル行も取得されるため,.DataBodyRange との論理積を取ってデータだけを取得しています(27 行目).

取得した値を配列に格納する

 動的配列をループの中で初期化し,配列 myXValue(), myValue() に格納します(30 行目から 36 行目).この配列は使い回すため(29 行目,39 行目),値の保持は行いません.

Debug.Print でオブジェクト取得や配列への格納を確認する

 コメントアウトした Debug.Print は Range オブジェクトが取得できているか,配列に値が格納されているかの確認用です(28 行目,37 行目).

配列をデータ系列に渡す処理

 動的配列に格納された値を散布図のデータ系列に渡す処理は 40 行目に書きます.今回はそこまで行いません.後日確認してから記事を書きます.

まとめ

 EXCEL VBA でテーブルのオートフィルター結果を取得する方法について書きました.

 従来の手続き型ベースの考え方ではなく,集合ベースの考え方が要求されています.特に ListObject オブジェクトのメンバは興味深い内容です.

 最近の EXCEL は PowerQuery なるデータベースソフトなどとの連携を重視しているようで,SQL Server チームとの共同作業をしているようです.

 テーブルを扱うオブジェクトに関してもその影響が出ているのでしょう.

Pocket

投稿者: admin

趣味:写真撮影とデータベース. カメラ:TOYO FIELD, Hasselblad 500C/M, Leica M6. SQL Server 2008 R2, MySQL, Microsoft Access.

コメントを残す

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