いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)FILTER関数の使い方

手順

機能と構文

 FILTER関数の基本的な機能は「指定範囲(表データ)のうち、条件に当てはまる行をすべて抽出する」というものです。
 構文は次のとおりです。

FILTER(範囲, 条件1[, 条件2, ...])

 最初の2つの引数が必須です。3つ目以降は条件の追加(AND条件)の際に用いるもので、省略できます。

基本的な使用例

数値を条件とする抽出

 画像では、B3:D9の範囲の表から、人口が200(万人)以上の県にあたる行をすべて抽出しています。

 F3セル

=FILTER(B3:D9,C3:C9>=200)

 昔だとメニュー操作でフィルタを作るしかなかったところですが、簡単な式でデータの抽出を行うことができます。これだけでとても強力な関数であることがわかります。


 抽出する範囲と条件を課す範囲は別々(共通部分がない)でも構いません。ただし双方の行数は一致している必要があります。
 この辺りはSUMIF関数などと近いかもしれません。

 F3セル

=FILTER(B3:B9,C3:C9>=200)

 


 しかし注意すべきなのは条件の書き方です。
 SUMIF関数の感覚だと「=FILTER(C3:C9,">=200",B3:D9)」としてしまいそうですが、これだとうまくいきません。

 F3セル(失敗例)

=FILTER(C3:C9,">=200",B3:D9)

 


 また、次の画像は単純に数値の中から200以上のものを抽出するという例ですが、このように対象となる列が1つだけであっても「=FILTER(B3:B9,">=200")」とはできません。「=FILTER(B3:B9,>=200)」でもダメです。

 F3セル(失敗例)

=FILTER(B3:B9,">=200")

 結果は省略しますが「=FILTER(B3:B9,B3:B9>=200)」とする必要があります。
 この辺りもSUMIF関数とは異なり紛らわしいところですが、まずは独特の条件の記し方に慣れることが重要です。

文字列を条件とする抽出

 次の画像では地方名が「中部」である行を抽出しています。

 F3セル

=FILTER(B3:D11,B3:B11="中部")

 文字列を条件にするときは、その文字列を「""」(ダブルクォーテーション)で囲みます。

日付を条件とする抽出

 日付を条件にするのはちょっと難しいですが、次のようにします。

 F3セル

=FILTER(B3:D11,D3:D11=DATEVALUE("1982/7/20"))

 このように、確実なのは日付をDATEVALUE関数で囲むことです。これにより日付がシリアル値に換算される(例として1982/7/20は30152になります)ので一致や大小比較が可能になります。
 ここでも""を外せないのが注意点です。

条件値をセルから参照する場合

 数値・文字列・日付のいずれを条件とする場合でも、条件値をセルに記してそれを参照する場合は、セル番地を記号で囲む必要はありません。

 G3セル

=FILTER(C3:D11,B3:B11=F3)


横になっている表からの抽出

 FILTER関数では表が横並びの形式(データが増えると横に伸びていく)からでも抽出ができます。条件に該当する列を抽出します。

 C7セル

=FILTER(C2:I4,C3:I3>=200)

 条件(第2引数)を課している範囲「C3:I3」が横1列であることが、最初の例との違いです。UNIQUE関数とは異なり、横方向に機能させるための特別の引数は必要ありません。

AND/OR検索(複数条件による抽出)

AND検索

 第3引数(以降)を利用して2番目(以降)の条件を記すことで、AND条件による検索ができます。
 次の画像では人口が200(万人)以上かつ面積が3000(km2)以下である行(県)を抽出しています。

 F3セル

=FILTER(B3:D9,C3:C9>=200,D3:D9<=3000)

 これは次のやり方より簡単なのですが、ExcelのFILTER関数では第3引数が別の意味になってしまい仕様が共通していないのが残念なところです。


 また、条件をそれぞれカッコで囲み、さらに「*」でつなぐことでAND条件による検索ができます。


 
 F3セル

=FILTER(B3:D9,(C3:C9>=200)*(D3:D9<=3000))

OR検索

 条件をそれぞれカッコで囲み、さらに「+」でつなぐことでOR条件による検索ができます。
 次の画像では人口が200(万人)以上または面積が3000(km2)以下である行(県)を抽出しています。

 F3セル

=FILTER(B3:D9,(C3:C9>=200)+(D3:D9<=3000))

 


 ところで、AND/OR検索にはAND/OR関数を使えば良さそうにも思えますが、これはうまくいきません。実例と理由については次の記事で説明しています。
 Excelの記事ですが理屈は全く同じです。

www.officeisyours.com

あいまい検索

 文字列の部分一致(~を含む)、前方一致(~で始まる)、後方一致「~で終わる」による検索を行う方法についてです。
 これについてはGoogleスプレッドシートオリジナルのREGEXMATCH関数を使うのが便利です。正規表現を扱う関数なので難しいイメージがありますが、簡単なあいまい検索程度なら全く難しくないので活用していただきたいと思います。

部分一致(~を含む)

 次の画像では、都府県名が「山」の字を含む行を抽出しています。

 F3セル

=FILTER(B3:D9,REGEXMATCH(B3:B9,"山"))

 「REGEXMATCH(B3:B9,"山")」によりB3:B9の各文字列が「山」の字を含んでいるかどうか一括して求めています(結果はTRUEまたはFALSE)。
 このように単純な部分一致ならワイルドカードのような記号が全く要りません。

前方一致(~で始まる)

 ここでは、都府県名が「山」で始まる行を抽出しています。

 F3セル

=FILTER(B3:D9,REGEXMATCH(B3:B9,"^山"))

 「^」は正規表現で文字列の先頭を意味し、「^山」は「先頭にある『山』の字」という意味になります。「山」で始まる文字列はすべてこの条件にマッチ(該当)し、抽出されます。

後方一致(~で終わる)

 ここでは、都府県名が「県」で終わる行を抽出しています。

 F3セル

=FILTER(B3:D9,REGEXMATCH(B3:B9,"県$"))

 「$」は正規表現で文字列の末尾を意味し、「県$」は「末尾にある『県』の字」という意味になります。「県」で終わる文字列はすべてこの条件にマッチし、抽出されます。

ワイルドカードの使用について

 ワイルドカードを使用できる関数はFILTER関数との相性が良くないものが多く、しかも上記のREGEXMATCH関数を使う方が有利なのでおすすめしません。
 ただし、一例としてCOUNTIFS関数を使うことが可能です。式は複雑ですがワイルドカードによる抽出が可能です。
 詳細は次の記事で紹介しています。

www.officeisyours.com

 他の候補としてはSEARCH関数が挙げられますが、関数の特性により特に後方一致が面倒になる(例えばワイルドカードを「"*県"」としても、結果的に部分一致検索になります)など欠点があります。

XLOOKUP関数との違いと使い分け

 表からデータを抽出するのに便利な関数として、他にXLOOKUP関数があります。
 XLOOKUP関数では1つの検索値(検索キー)に対して1つの行しか取得できないので、検索値を並べておいてそれぞれに対する検索結果を取得するような検索・抽出の際に用いられます。


 一方でFILTER関数の場合は検索条件に対して複数の値を返しますので、検索キー1つに対して結果が1行だけ返ってくるような抽出にはあまりには向きません(できなくはないですが)。
 一方で条件に該当する複数の行を一括して取得するような検索・抽出ではFILTER関数を使うしかありません。