Excel フィルタ自動更新で検索とフィルタ機能を持ったリストを作る

Excel

Excelであるセルの値を変更すると、それに応じてリストがフィルタされる仕組みを作りました。

紹介と解説をしていきます！

どんなことができるのか
サンプルのダウンロード
リストの作成
リストをテーブル化
検索フォームの作成
- 検索フォーム用データ
- 検索ボックス
検出列の数式
検出のフィルタ
VBAの作成
- フィルタ関数
- セル変更イベント関数
実行してみる
余談
参考
あとがき

どんなことができるのか

「検索」の右セルから、カテゴリをリストボックスで選択します。

するとそれに応じて、フィルタが適用され、リストの表示が変化します。

サンプルのダウンロード

今回作成したファイルは、こちらからダウンロードできます。

drive.google.com

リストの作成

サンプルでこんな感じのリストを作成しました。

サンプル

今回は「検索」というところに値を入れて、対象のカテゴリのみに抽出表示する仕組みを作りました。

「Aが入っている行だけ表示したい」

ということするものを作ります。

リストをテーブル化

リストをテーブルをして扱った方が楽なので、テーブル化しましょう。

リスト内のどこでもいいので、セルを選択状態にする
Ctrl+L
「テーブルの作成」ダイアログ
1. 「先頭行をテーbルウの見出しとして使用する」にチェック☑
2. OKボタンを押す

テーブルの作成ダイアログ

検索フォームの作成

上部にある「検索」のところを作ります。

検索フォーム用データ

検索フォーム用に、カテゴリ一覧を作成します。

別シートにこんなテーブルを作成しました。

カテゴリ一覧

ここに検索対象である、カテゴリの一覧を作成します。

1番上は空白としたほうが、後々検索の解除ができるので良いです。

テーブルを選択した状態だと、リボンに「テーブルツール→デザイン」が表示されます。

ここにある「テーブル名」を付けておくと、選択範囲の情報を見たときに、なんのデータを選択している分かりやすくなります。

テーブル名

検索ボックス

検索フォームをB1セルに作ります。

B1セルを選択した状態で、リボンから「データ→データの入力規則」を選択します。

データの入力規則

データの入力規則の設定ダイアログが出てくるので、これらを設定しましょう。

入力値の種類: リスト
空白を無視する: チェックOFF
元の値: DataシートのCategoryテーブルのデータ部分

データの入力規則の設定

これでB1を選択すると、リストボックスから値を選べるようになります。

検索リストボックス

検出列の数式

「検出」という列の数式を設定します。

データ先頭セルにコレを設定します。

=IF(ISERR(FIND($B$1,List!$B4)),0,1)

やっていることは

B1(検索フォームの値)を取得
この行のカテゴリ列に、取得値が含まれているか検索
含まれているか？をエラーで判定し、値をセット
1. 含まれていない(エラー): 0
2. 含まれている: 1

これで、検出列は0/1の値になってくれます。

テーブルにしていれば、数式を入力して、Enterキーを押すと、このような表示が出ます。

この列のすべてのセルをこの数式で上書き

コレを選択すれば、数式を選択したテーブル列(この場合「検出」)は、この数式が全て適用されます。

これ便利なので覚えておくとイイ感じです。

検出のフィルタ

検出のフィルタボタンを押します。

「検索」に「1」を入力して、OKします。

フィルタ条件を設定

これで0/1のうち、「1」のデータを表示してくれます。

今は全部1なので全部表示されていますが。

VBAの作成

さてここからVBAを書いていきます。

まずはエディタを開きます。

VBAのエディタを開く

「Sheet1 (List)」にVBAを書いていきます。

フィルタ関数

まずはこれです。

Sub filtering()
    With Worksheets("List")
        .Range("A3").AutoFilter _
            field:=4, Criteria1:="=1"
    End With
End Sub

少し複雑ですが

要素	意味合い
Rangeで指定しているセル	フィルタを適用するブロックの先頭セル
field	フィルタを適用する列(Range指定セルを1とする)
Criterial	適用条件

セル変更イベント関数

セルが変更されたときのイベント関数を作成します。

ここで検索フォーム「B1」が変更されたら、フィルタ関数を実行するようにします。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then
        Exit Sub
    End If

    Call filtering
End Sub

実行してみる

さてここまで完成したら、検索フォームから検索してみましょう。

検索に沿って、ちゃんとフィルタされますね！

余談

VBAでフィルタリングの更新をする方法を調べていると

[ActiveSheet.AutoFilter.ApplyFilter:embed:cite]

というのを見かけたのですが、エラーが発生してしまい、うまく動作しませんでした。

参考

コチラを参考にしました。ありがとうございました！

www.moug.net www.eurus.dti.ne.jp

あとがき

フィルタ自動更新で検索とフィルタ機能を持ったリストの紹介と解説でした！

Excelって動的な動きは苦手なイメージがありましたが、簡単なVBAでこのくらいできるならいいですね。

VBAにあまり手間暇かけるのはどうかと思いますが、このレベルならもう少し勉強してみたい……！

lisz-works

プログラミングと興味を貴方に