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

今回はそんな特別な関数の中でも、もっとも利用価値の高い「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関数などの論理関数は使えません。他の関数は使えます。

複数の条件で抽出

AND条件

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

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

OR条件

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

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

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

指定範囲内で抽出

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

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

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

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

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

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

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

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

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

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

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

関連項目