FILTER 関数は、データの指定したセル範囲または配列から、条件に一致するデータだけを取り出すことができます。
今回はFILTER 関数です。この関数もサブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021です。残念ながらExcel 2016やExcel 2019では使用できませんが、Excelの使い方が大きく変わる画期的な関数のひとつですので知っておいていただきたいと思います。
この関数も「スピル」機能が使われているんですよね。「スピル」のこともだんだんわかるようになってきました。今回もよろしくお願いします(^^)/
【この記事でわかることは】
・FILTER 関数とはどういうものか使用例を使って解説
・「部分一致」検索にFIND関数、SEARCH関数を使う方法
・COUNTIFS 関数でワイルドカードを使えるようにする方法
FILTER 関数の書式
FILTER 関数の「数式オートコンプリート」では次の画像のとおり「範囲または配列をフィルターします」と表示されます。
さらに、表示される引数の表示画像がこちらです。
FILTER(配列,含む,[空の場合]) って、これじゃぁわかりづらいですよね(–;
FILTER 関数の構文は次のとおりです。
=FILTER(データのセル範囲(配列), 抽出条件, [条件が該当なしの場合])
※[ ]の引数はオプションです。それ以外の引数は必須です。
引数を確認します
| 引数 | 内容 |
|---|---|
| 範囲または配列 | 【必須】フィルターをかける対象範囲（配列）を指定します。 |
| 条件 | 【必須】範囲の中から検索するための条件を指定します。 |
| [条件が該当なしの場合] | 条件に該当するデータが無かった場合に表示する値を指定できます。 省略している場合「#CALC!」エラーが返されます。 |
引数「条件」には、どの列（範囲）に、どんな条件化を示す式を記述します。たとえば「範囲列=”文字列”」や「範囲列<>文字列や値」「範囲列>値」などのよう比較演算子を使って指定します。
引数「条件が該当なしの場合」は省略可能ですが、該当が1つも無かった場合「#CALC!」エラーが表示されます。ここに「””」や「”該当なし”」を指定すれば空欄や指定文字列が表示されます。
FILTER 関数の使用例
FILTER 関数はスピル機能を使っているので、一つのセルに式を入力するだけで複数のセルに結果が表示されます。それでは、XLOOKUP関数でも使用した「日本百名山」のデータを使って使用例を見ていきましょう。
指定文字列で絞り込む使用例
「日本百名山」リストを使用します。データ量は百名山なので当然100行あります。
文字列をひとつだけ指定して絞り込む
まずは一番単純な文字列をひとつだけ指定して絞り込む例です。
数式バーでG4セルに入っている数式が確認できます。「条件値」は、K1セルの “岩手” を参照しています。[条件に該当なしの場合]は “該当なし” を表示するようにしています。
結果、「条件範囲」E2:E101 から「条件値」”岩手” を検索し、該当した行(TRUE)のスピル配列データをG4セルに表示しています。
仕組みは「スピル」機能の動作で上の行から順に調べ、該当があった行は「TRUE」無かった行は「FALSE」が返され、「TRUE」の行だけを動的配列「スピル」に格納し表示させているというものです。
複数の文字列を指定する場合
複数の条件を設定する場合の構文は次のように設定します。
・複数の条件をすべて満たす場合と設定するには、条件を (*) でつなぎます。(ANDに該当)
・複数の条件のいずれかを満たす場合と設定するには、条件を (+) でつなぎます。(ORに該当)
※設定時の注意として、(条件1)*(条件2) のように条件を () で囲む必要があります。
K1セルとK2セルそれぞれに条件を入れています。K1セルは “岩手”、K2セルは “山形” です。
そして、条件の結合には (+) を使っています。”岩手” または “山形” という条件設定です。
結果、”岩手”が２件 + “山形”も２件ヒット、該当行のデータがG4セルからスピル表示されました。
念のため、元表のデータを確認してみたところ、下図のように二つ以上の県にまたがっている山が４件あり、抽出できていないことがわかりました。
K1セルに “*岩手*”、K2セルには “*山形*” のように、ワイルドカード(*)を文字列の前後につけて実行してみましたが、結果は下図のとおり「該当なし」となりました。
このように、FILTER関数の「条件」指定は「完全一致」であることが確認できました。
では、「条件」を「部分一致」で抽出することができるのでしょうか。
文字列を部分一致で絞り込むには
「部分一致」で該当があった行は「TRUE」を返すというような条件式を設定すれば絞り込みできるはずです。次のような関数を使って文字列を調べる方法が使えそうです。
| 関数名 | ここでの書式 | ワイルドカード | 特徴 |
|---|---|---|---|
| FIND | (検索文字列,範囲) | ×使えません | 大文字と小文字を区別します |
| SEARCH | (検索文字列,範囲) | △ | 大文字と小文字は区別しません 〇〇で始まる、終わるなどが指定できない |
| COUNTIFS | (範囲,範囲,範囲,検索文字列) ↑条件1 ↑条件2 | 〇 | 大文字と小文字は区別しません 複数の条件に一致するデータの個数を求める |
「SEARCH 関数」と「FIND 関数」を使用する場合、検索値が見つからない場合エラーになるので、「IFERROR 関数」を併用してエラーだった場合 FALSE を返すようにする必要があります。
SEARCH 関数または FIND 関数を使う方法
・FIND 関数の例（SEARCH 関数も構文は同じなのでFINDをSEARCHに変更するだけです）
・一つの条件だけにした数式は次のとおりです。
=FILTER(A2:E101,IFERROR(FIND(K1,E2:E101),FALSE),”該当なし”)
「岩手」と「山形」を含む行に絞り込めました。
範囲を上から順にセルの文字列を調べて、見つかった場合位置の数値が返ります(=TRUE)。見つからなかった場合 #VALUE! エラーとなるので、IFERROR 関数を使ってFALSEを返すようにしています。
FIND と SEARCH の違いは、ワイルドカード文字(*)(?)が使えるかどうかです。FIND はワイルドカードではなく単に文字として認識してしまうのでワイルドカードは使えません。
COUNTIFS 関数でワイルドカードを使って絞り込む
まずは COUNTIFS 関数を使ったつぎの画像をご覧ください。
「岩手*」「山形*」とワイルドカードをつけて、県名が先頭にあるデータだけで絞り込んでいます。
SEARCH 関数でこれと同じことはではできません。ただし、SEARCH 関数の返り値が「=1」というように指定すれば可能です。「1」は最初に見つかった=先頭ということです。
=FILTER(A2:E101,IFERROR(SEARCH(K1,E2:E101)=1,FALSE),”該当なし”)
でも、「条件値」にワールドカードを使って指定する方が簡単ですし、いろいろ設定ができるので汎用性が高いですよね。
COUNTIFS 関数で絞り込む動作
COUNTIFS は複数の条件に一致するデータの個数を求める関数です。
構文は：COUNTIFS（範囲1, 検索条件1, 範囲2, 検索条件2, …）
図の数式を条件１だけにした数式は次のとおりです。
=FILTER(A2:E101,COUNTIFS(E2:E101,E2:E101,E2:E101,K1),”該当なし”)
（範囲1, 検索条件1, 範囲2, 検索条件2）
・範囲1, 検索条件1 の E2:E101,E2:E101 では、各行のセル毎に同一範囲（配列）内をカウントしています。自セルをカウントするので必ず「1」以上が返ります(=TRUE)ので全部TRUEになります。
・範囲2, 検索条件2 の E2:E101,K1 では、範囲内のセル毎にK1セルの「検索値」をカウントしています。「検索値」があった場合は「1」以上(=TRUE)、無かった場合は「0」が返ります(=FALSE)。
・結果として、各行のセル毎にカウントした結果が{1;2;2;0;0;…}のように配列で返ります。
「0」=FALSE、「1以上」=TRUE なので、TRUEの行だけで絞り込むことができるということです。
数値で絞り込む例
次は、標高(m)で絞り込んでみます。その前に使用できる比較演算子を確認しておきましょう。
比較演算子は、値を比較し、True、False、または Null の結果を返します。
| 演算子 | 用途 | 使用例 | 結果 |
|---|---|---|---|
| < | 左側の値が 右側の値よりも小さい場合に True を返す | 1 < 2 | True |
| <= | 左側の値が 右側の値以下の場合に True を返す | 2 <= 1 | False |
| > | 左側の値が 右側の値よりも大きい場合に True を返す | 1 > 1 | False |
| >= | 左側の値が 右側の値以上の場合に True を返す | 1 >= 1 | True |
| = | 左側の値が 右側の値と等しい場合に True を返す | 1 = 1 | True |
| <> | 左側の値が 右側の値と等しくない場合に True を返す | 1 <> 1 | False |
注: 値のいずれかが Null の場合、結果も Null (不明の値)になります。
標高1000ｍ以上～1500ｍ未満を抽出
・条件1は1500未満→ (範囲<1500)*(範囲>=1000) ←条件2は1000以上
・(*)二つの条件を満たす結果は、画像のとおり３件の山が抽出されました。
標高1000ｍ未満と3190ｍ以上を抽出
・1000ｍ未満の山と3190ｍ以上の山を絞り込んでいます。
・二つの条件のどちらかを満たす結果を求めるので条件の接続は(+)を使います。
・条件1は1000未満→ (範囲<1000)+(範囲>=3190) ←条件2は3190以上
まとめ（おわりに）
以上、FILTER 関数について使用例を使って解説してみました。
すでに使用できるバージョンのExcelを使っているユーザーのみなさんは、いろいろ試して使い方に慣れておくことをおすすめします。職場のExcelがまだ対応していないバージョンの方も、近い将来には対応するExcelバージョンにアップグレードされるはずです。
FILTER 関数は、オートフィルタ機能でできることがFILTER関数ではできないことがあります。でも、オートフィルタではできないことがFILTER関数で可能になります。その一つが強力な「スピル」機能によって数式をコピーすることなく複数のセルに計算結果を表示できるというものです。是非ともこの機能は使いこなせるようにしておきましょう(^^)/
オートフィルタでデータを絞り込んで、それをコピーして別シートに貼り付けるという手順で使うのが今までのやり方ですよね。でも、FILTER 関数なら別シートに元データから直接絞り込んだデータを表示できちゃうんですね(^^♪
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
