みなさん、こんにちはケンケンです。
今回は、エクセルに備わっているフィルター機能について深堀していきます。
みなさんもフィルター機能はよく使うと思います。
フィルター機能の基本からVBAを使った応用までまとめましたので参考にしてください。
フィルターの基本
まずフィルターの基本からおさらいしましょう。
「データ」タブを選択し1行目を範囲に指定し「フィルター」をクリックします。
セル右に▼マークが出ればOKです。
文字列で絞り込む
それでは、この状態でフィルター機能を使ってみましょう。
- B列の▼をクリック
- （すべて選択）をクリックしてチェックをはずす
- A株式会社のみチェックを入れてOKボタンをクリック
数値で絞り込む
次は数値で絞り込んでみましょう。
先ほどかけたフィルターは以下のように、「すべて選択」から「OK」で戻しておきます。
戻せたら今度は、C列売上額で「100,000」以上でフィルターをかけてみます。
- C列売上額▼をクリック
- 数値フィルターを選択
- 指定の値以上を選択
- 出現したオートフィルタオプションで「100000」以上の設定をする
フィルターをかけることができたらさらに、C列を昇順で並び替えます。
C列▼をクリックし、昇順を選択します。
日付でフィルターをかける
今度はD列取引日でフィルターをかけてみましょう。
手順は、
- 2/25～3/9までをフィルターにかけ
- 昇順で並べ替える
をやってみます。
D列取引日の▼をクリックし、日付フィルターから指定の範囲内を選択します。
オートフィルターオプションに以下のように日付範囲を指定してOKボタンをクリックします。
上図の状態で昇順に並べ替えます。
上図のように並び替えられたら成功です。
複数列で絞り込む
フィルターは複数の列に渡って選択することが可能です。
例えば、
- B列でA株式会社とB株式会社を抽出
- C列で売上額100,000以上を抽出
- D列で3/1以降を抽出
というなことを同時にできます。
下図のとおりに手順を踏んでみましょう。
続いて売上額でフィルターをかけます。
最後に取引日です。
会社名で昇順して見栄えを整えておきましょう。
これが複数列に渡ったフィルターのかけかたです。
フィルターで絞り込んだデータを関数を使って加工する
フィルターをかけるだけではもったいないです。
せっかくフィルターをかけたならそこから発展させて加工してみましょう。
SUBTOTAL関数を使って絞り込んだデータの集計をしよう
絞り込んだデータを関数を使って加工することによって、データをより良いものに進化させることが出来ます。
そのために、SUBTOTAL関数を使っていきます。
SUBTOTAL関数は指定の集計方法で集計値を求める方法です。
SUBTOTAL（集計方法, 参照1, 参照2, …, 参照254）
複数ある集計方法から選択し集計対象のセル範囲を指定すると集計が実行されます。
それでは、具体的に関数を使ってみます。
フィルターをかけた状態で合計値等を計算したい時に便利な方法です。
今回は、C18セルに抽出後の合計額を集計します。
関数は次のように入力します。
「=SUBTOTAL(9,C2:C16)」
関数の入力の仕方は大丈夫ですか？以下のような手順で入力するとストレスが少なくて楽です。
ここまで出来たらB列でフィルターをかけてみましょう。
会社名ごとに集計できることを確認してください。
フィルターと関数を組み合わせる基本です。覚えておきましょう。
フィルターをかけた時に非表示になった行を無視して連番を振りなおす方法
フィルターをかけるとせっかく作った連番が崩れてしまいます。
先ほどの例で言うと以下のようになっていますね。
この状態からSUBTOTAL関数を使って連番が崩れないように修正します。
A2セルに「=SUBTOTAL(3,$B$2:B2)」と入力し、A16セルまでコピーしましょう。
第一引数に「３」を指定するとデータの個数を集計してくれます。つまり、B列のデータの個数を集計して、A列に転記することでIDを振っているということです。
「$B$2」は絶対参照といいます。よく分からない方は、こちらを参考にしてください。
便利なので「F4」キーで参照を変更できることは覚えておきましょう。
絶対参照にすることで起点となる部分を固定できます。
それでは、フィルターをかけて連番が降りなおせるか確認してみましょう。
いかがですか。資料作りの際に参考にしてみてください。
SUBTOTAL関数の集計方法まとめ
簡単に集計方法（第一引数）をまとめておきます。
良く使うものだけまとめます。もし、それ以外で必要になったらその都度調べるくらいでよいでしょう。
- １、平均（AVERAGE）
- ２、数値の個数（COUNT）
- ３、データの個数（COUNTA）
- ４、最大値（MAX）
- ５、最小値（MIN）
- ９、合計（SUM)
スライサーの存在
みなさんはスライサーをご存知ですか。
スライサーとはテーブル機能の一部で直感的にフィルターをかけ抽出することができるExcel 2013から導入された機能です。
簡単なフィルターはスライサーを使った方が便利な時があるので紹介しておきます。
スライサーはテーブル上でしか使えないので、表をテーブル化します。
表の一部をクリックした状態で「挿入」から「テーブル」を選択し、ボックス内で範囲を指定します。
テーブルが作成出来たら、以下の手順でスライサーを出現させます。
このスライサーを使って抽出してみましょう。会社名スライサーには会社名（B列）の一覧が表示されます。
A株式会社で抽出できました。
複数選択したい場合は、Ctrlを押しながら選択するとできます。
また、複数列を選択することもできるので試してみてください。
フィルターオプションを使ってみよう
複雑な条件で抽出したい時にフィルターオプションという機能を使うと便利です。
この機能の優れた点は、
- 複雑な条件を指定できる
- 抽出したデータを任意の場所に表示できる
などがあります。
フィルターオプションとは
まず、フィルターオプションがどういうものか確認しましょう。
「リスト範囲」に対して「検索条件範囲」の条件で抽出し、「抽出範囲」に書き出すものです。
以下の図のようなイメージです。
フィルターオプションは上記の３つの要素で構成されます。
使うときは３つの要素を用意しましょう。
ちなみに、検索条件範囲や抽出範囲に使用するフィールド名（会社名等）は必ず「リスト範囲」のフィールド名にしてください。
少しでも相違があるとエラーになってしまいます。
フィルターオプションで抽出してみよう
それでは、実際にフィルターオプションを使ってみます。
「データ」タブから「詳細設定」を選択するとフィルターオプションの設定ボックスが表示されます。
ボックスの中で抽出先を指定します。
以下の図を参考にボックス内を埋めてください。
ボックスには絶対参照で記載しますが、範囲を指定するとデフォルトが絶対参照になるので問題ないかと思います。
各条件を入力してOKをクリックすると、条件に合ったデータが抽出範囲に転記されます。
検索条件についてですが、同一項目を同一行に加えるとAND（かつ）検索になります。
今回の条件で言うと、取引日がAND検索になります。2019/3/1以降で2019/3/9までの２つ条件をANDで結んでいることになります。
当然、売上額をAND条件で検索することもできますので、ご自分で試してみてください。
ちなみに行を変えるとOR（または）検索になりますが、ここでは割愛します。
VBAでフィルターをかけてみよう
もちろんVBAを使ってフィルター機能を使うこともできます。
VBAを使える方はチャレンジしてみましょう。
VBAを使えない方でチャレンジしてみたい方は、過去に基本をまとめた記事もあるので参考にしてみてください。
VBAでフィルターをかける
それでは、VBAでフィルターをかけてみましょう。
VBAでフィルターを使う場合は、Rangeオブジェクトに対してAutoFilterメソッドを使います。
まず、会社名（B列）でフィルターをかけてみます。
Sub filter_rensyu()
Range("A1").AutoFilter field:=2, Criteria1:="A株式会社"
End Sub
上記コードを標準モジュールに記載してください。
実行するとA株式会社でフィルターがかかるはずです。
Fieldで列番号を指定します。「２」は２列目なので「B列」を指します。
Criteriaで抽出条件を指定します。今回は文字列「A株式会社」をダブルコーテーションで囲んでいます。
複数条件で抽出しよう
複数条件でも抽出できます。コードを書いてみましょう。
Sub filter_rensyu2()
Range("A1").AutoFilter field:=3, Criteria1:=">=100000", Operator:=xlAnd, Criteria2:="<=300000"
End Sub
３列目金額欄を100,000以上300,000以下の複数条件でフィルターをかけるコードです。
Criteriaは複数指定でき、その条件をANDにするかORにするかをOperatorで指定できます。今回は、xlAndでAND条件を選んでいます。
これを実行すると、以下のようになります。
コードをうまく組み合わせればもっと複雑な抽出もできるので挑戦してみましょう。
自動記録でフィルターをかけてみよう
自動記録でもフィルターをかけることはできます。
こちらの記事で紹介していますのでよかったら参考にしてください。
VBAでフィルターオプションを設定しよう
フィルターオプション機能もVBAで表現することができます。
まず、コードを書いてみましょう。
Sub filteroption()
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("G1:J2"), copytorange:=Range("G10:J10")
End Sub
フィルターオプション機能は、VBAではAdvancedFilterメソッドを使います。
コードは少し複雑ですが、先ほど紹介した３つの要素と突き合せると分かり易いかと思います。
Action:=xlFilterCopyはリスト範囲とは他の場所に抽出データをコピーする、という意味です。今回は、G11セル以降に抽出ですね。
成功したらボタンを作成し、条件を変えて試してみましょう。
この方法だと条件を変えてボタンを押せばすぐに検索結果が表示されるので、頻繁に検索をかける方におススメの方法です。
フォームと連携して使い勝手を改良
フィルターをかけて抽出・検索する機能を持つフォームを作っていきます。
これが出来るとユーザーにとって使い勝手が良く、とても便利なものとなります。
今回は、フォーム上で期間指定ができるようにします。
フォーム作成の他の記事は以下からどうぞ
【ExcelVBA】ユーザーフォーム活用テクニック編【チェックボックス】
【ExcelVBA】ユーザーフォーム活用テクニック編【リストボックス】
【ExcelVBA】ユーザーフォーム活用テクニック編【スクロールバーについて】
【ExcelVBA】ユーザーフォーム活用【リストボックスから選択・メッセージボックス活用】
【ExcelVBA】ユーザーフォームを作りこむ【コンボボックスの使い方】
フォームを作ろう
まずフォームから作ります。
今回作るフォームには、期間指定できる機能を実装させます。
VBEを起動して「挿入」から「ユーザーフォーム」を選択します。
フォームができたら、以下のように部品を配置してオブジェクト名を変更してください。オブジェクト名はすぐに変更するクセをつけてください。
フォーム起動ボタンを作ろう
標準モジュールに以下のようにコードを記述します。
Sub Form_start()
frmKensaku.Show
End Sub
コードが書けたらフォーム起動ボタンを作っておきましょう。
フォームに機能を実装しよう
ボタンが作れたら、フォームに機能を実装していきましょう。
閉じるボタンに機能を実装する
VBE上のフォームの閉じるボタンをダブルクリックしてプロシージャを生成します。
btnClose_Clickプロシージャができているはずなので、そこに以下のようにコードを書きます。
Private Sub btnClose_Click()
Unload Me
End Sub
おなじみのフォームを閉じるコードです。
ちゃんと閉じることを確認してください。
検索ボタンに機能を実装する
続いてフォームの検索ボタンをダブルクリックしてプロシージャを生成し、btnKensaku_Clickプロシージャにコードを記述します。
Private Sub btnKensaku_Click()
Range("G2").Value = ">=" & txtDate1.Text'①
Range("H2").Value = "<=" & txtDate2.Text'①
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("G1:H2"), copytorange:=Range("G10:J10")'②
Unload frmKensaku'③
End Sub
前提として、期間指定のみに機能を限定しているので、検索範囲は以下のように取引日の２列しか用意していません。
それでは、コードの解説をします。
①txtDate1(2)テキストボックスの値をそれぞれG2とH2に代入します。
その時、「”>=” & txtDate1.Text」のように文字列を連結し、以上（>=）、以下（<=）をうまくセルに反映させられるようにします。
②先ほど紹介したコードと構成は一緒です。
検索範囲の「criteriarange」のみ変更しているので注意してください。
③検索が完了したらフォームを閉じるコードです。
ここまで出来たらフォームを起動して確認しましょう。
今回は、期間のみの検索に限定しましたが、当然、あらゆる検索条件を指定することが可能です。
いろいろと試して使い勝手の良いフォームを作れるようになっていただければと思います。
まとめ
今回は、フィルター機能を広くご紹介しました。
実務上よく使う機能なので、幅広く知っておくことに損はないかと思いますので、参考にしていただければと思います。
それでは、また次回お会いしましょう。
