どうも、Y-nek0(@Ynek0)です。
タイトル通り、今回はGoogleスプレッドシートのFilter関数の使い方についての話です。
これを読めば、頑張ってエクセルで資料をまとめている
総務部のあの子を助けてあげられるかもしれません。
下心満載ですね。
ただ経験上、エクセルやスプレッドシートの需要って結構思った以上にあるので
知っておくと人からできる奴とか思われる事が多いです。
今回はGoogleスプレッドシートの中でも特に需要のある関数といって良い
Filter関数の使い方をご紹介できればと思います。
FILTER関数とはなにか？
Google公式のヘルプにて、FILTER関数についての説明は以下のような記述があります。
ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。
と、神(Google)は言っているんですが
大問題だ、理解できん。
ってなりますよね。
要約すると指定した条件に合致したデータのみを表示させると言う事です。
……えっ
要約が理解できんもっと噛み砕いて説明しろっ……ですか。
FILTERはフィルターと呼びます。
フィルターと聞いて思い浮かべるのはコーヒーフィルターやエアコンフィルターだと思います。
FILTER関数がどのような物か想像ができたと思います。
不必要な物はフィルターに絡めて残しておき、必要な物（データ）だけ抽出する関数
それがFILTER関数です(ドヤァ
えぇっ！？もっと噛み砕いてだって！？
えっと～……例えばそこにディストピアがあったとしますね。
そのディストピアは優れた能力がある人間しか生き残れないのですよ。
どうやって優れた能力を測るのかというと全ての住人を管理する
とてつもなく大きな機械がある訳ですよ。
それにより生まれたての赤ちゃんの期待できる能力の予測値や
現在自分がどの程度の能力を保有しているのかを
ゲームのステータス画面みたいな画面から予測することができるわけです。
20歳を迎えた時、管理する機械に20歳になったその時点での能力値を計測され、
フィルターにかけられ能力値以下の人間は処分されてしまいます。
人々はこの機械が一定の能力値の人間を選定する機能を
20(トゥエンティー)フィルターと呼ばれました。
こ、これでなんとなくイメージついたのでは無いでしょうか。
この後、機械を破壊しようと処分を免れた人間達による革命の物語があるのですが
それは置いときましょう。
ではそんなFILTER関数の使い方を見ていきましょう。
FILTER関数の基本構文
以下がFILTER関数の記述方法になります。
=FILTER(表示範囲,条件範囲 条件１,[条件範囲2 条件２])
FILTER関数を記述する際の表示範囲とは？
それが範囲選択です。
=FILTER(A2:A11,条件範囲 条件１,[条件範囲2 条件2])FILTER関数を記述する際の条件範囲と条件とは
条件範囲とは
条件範囲は条件を適用させる範囲の事を表します。
……
何言っているか分からない？条件範囲って何？ってなってます？
表示範囲は実際表示されるデータであり、
条件範囲はその表示させるデータには裏でいくつかの条件が突きつけられており、それをクリアする事で表示されるのです。
アイドルグループで考えると候補生がいて、レギュラーメンバーになるには幾つもの条件を提示されておりそれをクリア出来てようやく表舞台に立て、名前をファンに公開されるみたいなそんな感じです。
条件範囲を記述式に組み込むと以下の様な形になります。
=FILTER(A2:A11,B2:B11 条件１,[条件範囲2 条件2])
ディストピアで例えるなら機械が持っている住人データベースから住人の名前とその他個人情報を表示範囲として、住人の年齢が条件範囲に入ります。
注意点
条件範囲もD2:D11と同じ列数にならないとエラーとなります。
ディストピアで例えるならば全住人が7000万人いるのに年齢で絞った時の条件範囲が7000万とんで1人だったらおかしいですよね？物語だったら面白いですが……
条件指定の方法
次に条件はどのように指定するかの方法になります。
ディストピアで例えるならば機械は全住人のうち、選定対象を年齢が20歳である事を指定しています。
実際のFILTER関数での条件指定方法には不等号や等号を使用して指定します。
不等号や等号とは
x >= 4
x <= 10
x = 5
x != 6
不等号の説明ではｘとあらわしましたが、このｘには何が入るかというと
先ほど説明した条件範囲が入ります。
そして条件を記述式に組み込むと以下の様になり、FILTER関数が完成するわけです。
=FILTER(A2:A11(表示範囲),B2:B11(条件範囲) = 5(条件1),[条件範囲2 条件2])
上記式の説明をすると、
A2からA11の表示範囲でB2からB11の条件範囲の中から5である項目を抽出する。
条件2が不要な場合は上記式から『,[条件範囲2 条件2]』を省いて下さい。
ちなみにディストピアの機械がFILTER関数を使うのであれば以下の様な記述式になると思います。
=FILTER(A:F(表示範囲),D:D(条件範囲) = 20(条件1))
年齢はスプレッドシートのD列に記述していると仮定しますね。
A:FはA列からF列の全てを指定しています。
FILTER関数の記述例
以上のことから、どういうFILTER関数を記述すればよいかというと下記の通りです。
FILTER関数で文字『a』を抽出する。
=FILTER(A2:A11,A2:A11 = "a")
→特定の文字をとってきたい場合
FILTER関数で数値『2以上の数値』を抽出する。
=FILTER(A2:A11,B2:B11 >= 2)
→A２:A11の範囲で数字が２以上の物のみを抽出
FILTER関数で数値『5未満の数値』を抽出する。
=FILTER(A2:A11,B2:B11 < 5)
→B2:B11の条件範囲で５未満の物のみをA2:A11のデータで抽出する。
FILTER関数で数値『2以上の数値かつ10以下の数値』を抽出する。
=FILTER（A2:A11,B2:B11 < 2,B2:B11 > 10）
→A2:A11の条件範囲で２以上かつB2:B1１の条件範囲で１０以上のA2:A11のデータを抽出する。
と言う風に書けば問題無く動作するかと思います。
注意点
FILTER関数は複数の条件をつけることができます。
優先順位は条件範囲1の条件から順に適用されていきます。
条件範囲1が適用された後また条件範囲2でさらにフィルタをかけていく形です。
コーヒーを1度濾して、もう1度濾すみたいな2度濾ししてるようなイメージです。
FILTER関数の参照セルを名前付き範囲で指定！
名前付き範囲の解説や設定方法については以下の記事をご参照下さい。
名前付き範囲を指定することでどこの範囲を指定しているのかが一目瞭然となります。
名前付き範囲を使用することでこの様にする事でどこの範囲を指定しているかを明確化できるので便利です。
FILTER関数の効果
FILTER関数は1つ設定すると複数データを表示させる。
FILTER関数は大本のデータの範囲を別のセル、またはシートに条件を合致したものだけ表示させる関数です。
ですので1つの関数で、複数のデータを表示します。
例えば以下の様な記述式の場合
=FILTER(A1:F10,B1:B10 < 5)
A列の1行からF列の10行までのデータをFILTER関数を記述したセルを含み右に表示させます。
FILTER関数を使い、値が入るべき所に既に別の値が入っていた場合、エラーが表示されるので注意して下さい
つまりは大本さえあればいくらでも複製が効くわけです。
ディストピアにある戦闘ロボットは大本の機械にすべてマスタ（大本）があるので
壊れたとしても複製すれば良いだけなので兵力が減りづらいと言う事です。
それにより革命軍が苦戦する理由の1つです。
参照先データは消えない
FILTER関数自体を消す事さえしなければ他に表示されたデータを消したとしても元のデータから
自動的にデータを引っ張ってきて誤ってデータを消したとしてもすぐに復元されます。
さらにマスタ（大本）に追加すれば追加したデータも参照先にすぐに更新されてナウでホットなデータをすぐに確認することができます。
データを編集されるのがちょっと……っていう方は
自分以外の人にデータを見せる場合はデータの大本はシートを非表示にし、
FILTERを使って必要最低限のデータのみを見せる事でデータの損失を防げたりします。
その他にもシートの保護などを使う方法もあります。
おなじみディストピアで例えるならば
革命軍との戦闘データは常にマスタ（大本）に更新され、
それに対応したプログラムを持つ戦闘ロボットが戦闘毎に量産されていくと言う事です。
そりゃ革命軍苦戦するわ……
そして話広げすぎて風呂敷ちゃんとたためるか心配になって来ました。
FILTER関数の応用
ここからは少し難しくなっていきます。
ORやAND、NOTの使い方がFILTER関数では少し特殊です。
AND関数やOR関数が別で用意されていたりするのですが、
FILTER関数内でこれらを使用するとエラーになってしいます。
AND条件
ANDとは○かつ○ つまりは提示された複数の条件を全て満たしているとクリアである事です。
婚活歴が長い人って沢山ある条件をANDで括って選んでいる様な気がします。
=FILTER(A2:A11,A2:A11 >2 , A2:A11 < 10)
OR条件
ORは○または○ 。提示された条件の片方もしくは両方の条件を満たしていること。
今日のご飯何が良いと聞くと、何でもいいと言う方がいらっしゃるのは
皆さん衆知の事実だと思います。
しかしこういった方々は自分の中にORで括った複数条件を持っており、
そのいずれにも引っかからない場合は拒否します。
=FILTER(A2:A11,(A2:A11 > 2) + (B2:B11 = "a"))
NOT条件
NOTはxは5では無い。前に不等号の項で説明した『！＝』と同じ意味を持ちます。
FILTER関数の条件で利用する場合の意味合いは『文字列a以外の文字を表示する』となります。
勇気を出して告白をしてお前以外だったら付き合えるわって返答されたS君は今も元気にしているだろうか。
=FILTER(A2:A11,NOT(A2:A11 = "a"))
あいまい検索
特定の文字が含まれている文字列を全て表示というものです。
つまりは『あ』という文字が含まれている項目を全て表示します。
非常に汎用性があって使いやすい構文です。
職場で知り合った人って休日も一緒に遊んだり酒飲んだり旅行したりして
親睦を深めて仲が良くても仕事辞めるとあいまいな関係になりそして伝説へって感じで
疎遠になる現象を何と言うんだろう。
=FILTER(A2:A11,search("a",A2:A11))
まぁ、こんな感じで関数を利用する事が出来ます。
複数のシートから1つのシートに累積させる。
FILTER関数を使用して複数のシートからデータを抽出して累積させる事も可能です。
その場合は配列を利用します。
配列は『｛』を使用します。詳しい説明は以下の記事をご覧下さい。
使い方としては以下の通りです。
#複数シートから累積でデータを取得する方法で空白行を反映させない方法
={
IFERROR(
FILTER('シート1'!A1:A,'シート1'!A1:A <>"")
);
IFERROR(
FILTER('シート2'!A1:A,'シート2'!A1:A <>"")
);
IFERROR(
FILTER('シート3'!A1:A,'シート3'!A1:A <>"")
);
IFERROR(
FILTER('シート4'!A3:A,'シート4'!A1:A <>"")
);
IFERROR(
FILTER('シート5'!A1:A,'シート5'!A1:A <>"")
)
}
※分かりやすいように改行しています。
これで1つのシートに複数のFILTER関数が累積して溜まるようになります。
FILTERの条件として『シート5'!A1:A <>""』と指定していますが、これは空白行を除外すると言う条件を指定しています。
リストのデータ間に空白が発生するとそれも除外するので空白には何か値を入れておく事をオススメします。
FILTER関数と他の関数を使った応用
FILTER関数は他にも用意されている様々な関数と連携させる事によりさらに便利に使用する事が出来ます。
IMPORTRANGE関数とFILTER関数でデータを選定して表示できる。
スプレッドシート関数に別のスプレッドシートからデータを持ってくる時に使用する関数である
IMPORTRANGE関数という関数があります。
その関数に関しましては専用の記事を用意しましたのでそちらを読んで頂けると
嬉しいです。
要約すると他のスプレッドシートから指定した範囲のデータを映し出してくれる関数です。
この関数とFILTER関数を連携させる事により別のスプレッドシートから呼び出したデータをFILTER関数を使って表示するデータを制御することができるのですよ。
例えば以下の様な記述式にすることで別スプレッドシートでそれが可能です。
=FILTER(IMPORTRANGE(省略),“テスト” = IMPORTRANGE(省略))
IMPORTRANGE関数とFILTER関数とUNIQUE関数で重複を無くし選定して表示できる。
UNIQUE関数については以下記事をご参照下さい。
要約すると重複を削除する関数です。
## 別シートから参照したデータをフィルター条件に当てはまり、重複を削除したデータを抽出
=UNIQUE(FILTER(IMPORTRANGE(省略),“テスト” = IMPORTRANGE(省略)))
INDIRECT関数とFILTER関数を使い範囲指定をセル入力にする事も可能
INDIRECT関数においては以下記事を参照下さい
要約するとセルに入力したセル参照の文字列をセル参照と認識させる事の出来る関数です。
FILTER関数とINDIRECT関数を利用する事でセルでの範囲指定を行う事ができます。
## FILTER関数の範囲のみをINDIRECT関数を利用
=FILTER(INDIRECT(C5),INDIRECT(C5) = "a")
INDIRECT関数とFILTER関数を利用した例2でもご紹介しておりますが、条件の値ももちろんセル参照可能です。
しかしこのセル参照にもINDIRECT関数を使用するとエラーになるので注意して下さい。
INDIRECT関数とFILTER関数とUNIQUE関数を使い、重複削除も可能
UNIQUE関数をFILTER関数の上に重ねて重複を削除することもできます。
FILTER関数を設定すると出てくるエラー
FILTER の範囲サイズが一致しません。
実際に出てくるエラー文言は以下の通りです。
FILTER の範囲サイズが一致しません。行数を ○、列数を ○ にしてください。実際の行数は ○、列数は ○ です。
このエラーについてはFILTER関数を利用する上で良くあるエラーとなります。
最初の指定した範囲と条件で指定した範囲の不一致によって出てきます。
つまり、A1:A10の範囲指定したのに条件指定でB:1:B11 = "Ynek0"の様に指定しているけど、B11ってなんやねん。
範囲指定にA11入ってないやん。
と言われている感じです。
解決策としては指定した範囲と条件指定で指定した範囲を同じ範囲にする様に修正して下さい。
FILTER の範囲は単一行か単一列にしてください
このエラーについては条件指定の範囲が複数列複数行で範囲指定していしている場合に発生します。
対処方法はエラーの文面の通り条件を単一行または単一列に指定する事で解消されます。
どうしても複数条件にしたい場合は、条件2で指定すれば問題ないです。
最後に
今回FILTER関数について自分が利用している形式を思い出しながら書いてみました。
以外と利用幅が多くてまだまだ思い出し切れていない所も多々あり、
思い出ししだいさらに追記していこうと思います。
ちなみにディストピアの話の結末は激戦の末多くの仲間の犠牲と引き換えに
最終的に革命軍が勝利します。
しかし管理していた機械のバックアップを逃がしてしまい
主人公がそのバックアップを探す旅に出て物語第一部が終わります。
ではまた、ご贔屓に。
