Filter関数を使ってデータを抽出しよう

Googleスプレッドシートには、Excelにはない特別な関数がいくつかあります。それらはスクリプト無しでまた、特別な機能を使わずにデータの塊の中から、色々な条件でデータを塊で取り出すことが出来ます（Excelは基本的に関数は塊ではなく、単一の値でしか答えを返せません）。この特別な関数を使えば、スクリプトを組む時に余計なコードを記述しなくても済むので、是非使えるようになっておくとお得です。

今回はそんな特別な関数の中でも、もっとも利用価値の高い「Filter関数」を使ってみたいと思います。

今回利用するサンプルファイル

• Filter関数を使用したサンプルスプレッドシート

Filter関数の使い方

最もシンプルな使い方

最もシンプルな事例からまず書いてみましょう。これが全ての基本になります。条件は1個だけで、特定の列の値が10以上の値のデータを全ての列で取ってくる事例です。

Filter関数の書式は以下の通り。

取ってくるデータの範囲ではA2:Eと指定しています。E列の最後を指定していないので「E列の一番下まで」という指定方法になります。データが増えてもこの場合、数式を修正する必要がないので便利です（この指定方法はExcelでは出来ませんね）。また、条件指定する列でもE列全部を指定し、条件式として>=10で10以上という指定になります（<>10の場合は、10ではない値という表現になります）。

注意点があります。

1. 関数を入れたセルの下方向、右方向のセルに何か値が入ってる場合、#REFエラーになります（値があると値を上書きできないのです）
2. Filter関数の値が返ってきた後、関数の入ってる式以外はDeleteしても常に関数の結果で即時上書きされ消せません。
3. 条件式の>=10の部分は、data!E2:E = G5といった具合にセルも指定出来ます。
4. Filter関数の中では、AND関数やOR関数などの論理関数は使えません。他の関数は使えます。

選択結果から特定列だけ表示

Filter関数での結果表示から必要な列だけを表示するといった事が可能になっています。この手の表示ではQuery関数を使っていましたが、Filter関数の入れ子で第二引数にオプション指定をすると、フィルタ結果且つ特定列だけ表示が可能になります。

この場合の数式のスタイルは以下の通り

• まず、Filter関数でA2:Eの範囲をE列が10以上でフィルタして取り出す
• その計算結果に対して、更にFilter関数の入れ子で指定する
• 第二引数で{ }を使い、表示したい列は1、非表示にしたい列は0を指定する（4列目だけ非表示にするので、4個目を0としています）

Google Apps Scriptなどで必要な列だけ取り出して操作する上での前処理として関数のみで整備できるので、余計な列表示を消し扱いやすくなりました。第二引数で指定が必要なので、抽出条件を付けながらの指定が出来ない為、Filter関数での入れ子で実現しています。

図：4列目だけを非表示にしてみた

複数の条件で抽出

AND条件

条件式を複数入れたい場合、カンマ区切りでいくらでも追加が可能です。例えば、以下のような感じになります。

1個目の条件式で、C列はG2の値とイコールのもの且つ2つ目の条件式でE列の値がG5の値以上のもの　という具合に指定しています。２つの条件に合致するものが抽出されます。複数の条件といっても例えば、「都道府県が東京都と神奈川県のレコード」といった場合、この条件式ではAND条件なので「東京都且つ神奈川県」となってしまうので、データがゼロ件となってしまいます。

OR条件

複数の条件式を入れたいけれど、ANDではなくOR（もしくは・・・）で指定したい場合があります。但し、OR関数が使えないので、自分でORになる論理式を条件式に加えてあげる必要があります。以下のような感じになります。

特徴は、1つ目の条件式内で２つの条件をそれぞれ括弧でくくり、それを+記号で繋いでる点です。この+が「OR」条件として働きます。上記の例では、「D列の社名がマイクロシャフトもしくは東横ハンズのもの」という条件式として働きます。

図：OR条件の指定方法はちょっと癖がある

指定範囲内で抽出

大量のデータの中で「ここからここまで」という条件式もよく利用されます。主に日付の範囲や、数値で言えば上下の閾値の範囲の指定例などなど。この場合にはAND条件を持って２つ条件式を利用すればOKです。それぞれは>=と<=で作る事になります。日付データであってもそのままダブルコーテーションでくくれば問題なく利用できます。

上記の例では、B列の日付がG2で指定された日付〜G5で指定した日付のデータを抽出してくれます。日付の開始と終了が逆にならないように注意してください。もちろん比較演算子として>や<を利用できます。

Filter関数の結果を別の関数に食わせる

Filter結果の合計

Filter関数はデータの塊を「配列」で返してくれます。例えばFilter関数で「数値の列」をFilterで引っ張ってきた場合、そのデータは全てが「数値」な訳です。これを他の関数で食わせると、合計や件数を出す事が可能になります。試しにFilter関数の結果をSUM関数で括ってみましょう。

こんな具合です。dataシートのE列が10以上の値についてフィルタし、データはdataシートのE列だけを取って来ています。これをSum関数に渡してるので、E列の合計値が出て来るという具合です。同じ事例でCount関数に渡せばデータの件数が取得出来ます。

図：セルの値で範囲変更はよく使うテクニックですね

Filter結果をソート

Filter結果の配列を更に別の配列関数である「SORT関数」に食わせる事で、結果に対してソートしたものを表示といった事が可能になります。いちいち人間が手動でソートする手間をとことん削れますね。

数式のスタイルは以下の通り。

SORT関数の引数で、5列目を「降順」にするために、5と0を指定しています。第三引数を-1で指定もしくは省略すれば昇順になります。オートフィルタなど使わず、関数で予めソートしておく事で、GASで使う場合も無駄なロジックを組む必要がなくなり、処理しやすくなります。

図：配列関数の合わせ技

指定範囲内レコードで重複してるレコードのみを抽出する

例えばPC管理台帳みたいなシートの場合、本来１人１台のケースに於いて、PCを返却してくれておらず台帳に同じ人が重複して登録されたままといったようなケースがあります。この表から重複してる人のユニークなレコードを取り出したい場合、以下のような設定で関数を構築し、最終的にUnique関数に食わせる事で目的の抽出をする事が可能です。

但し、このパターンを使う場合、抽出されるレコードの全列が同じレコードである必要があるので、人の名前だけ同じでPC名が異なるみたいな場合、抽出対象の範囲に加えてはいけません。上記の数式でもA2:Cが範囲ですが、D列にPC名があるので、そこを避けています。

Filter関数だけでは重複したレコードだけが抽出されてるだけで、同じ名前の人が複数列挙されてしまうので、そこをUnique関数で食わせることで結果的に、重複したレコードのユニークなデータだけが返ってきます。

※注意点は上記の例で言えばC列が空白の行に関してはCountが働かないので抽出対象とならないので注意。

図：無事に重複してる人のレコードを取り出せた

Filter関数の条件式内で関数を使う

AND関数やOR関数は使えないのですが、ソレ以外の関数でTRUEやFALSEを返してくれる関数であれば利用する事が出来ます。例えば「奇数の場合TRUEを返す」ISODD関数などが利用できます。

上記の場合、IDが奇数のものを抽出する事が出来ます。もちろん、比較演算子を使って「平均値以上の値」なんて取り出し方もありますね。その場合は例えば

average関数で平均値を出し、その値をもって同じ列の数値が平均値以上のものだけを取り出す事例です。また、Filter関数はいわゆる「○○を含む」的なものをワイルドカードを使って抽出が出来ません。そこで、search関数を併用してそれを実現する方法があります。

上記の事例だと、「社」という文字が仕入元名前に入ってるものを抽出してくれます。search関数を使って擬似的にワイルドカードを実現しています。

countif関数を組み合わせる事で、ある列の範囲と別の列の範囲とを比較し、どちらにも重複している値のレコードだけを抽出する事が可能です。不一致のものを抽出する場合は、>0ではなく=0とすることで可能になります。（重複してる場合には1が返ってきて、そうでない場合には、0が返ってくる為）。比較は単一列でしかできませんが、抽出は複数列出せます。

図：組み合わせの妙は応用範囲を広げる

関連項目