スプレッドシートでは条件に一致するセルのデータのみを取り出したいこともあるかもしれません。絞り込みができると、条件に応じたもののみが出てくるので見やすくなりますよね。スプレッドシートのfilter関数は、特定の条件を含むor含まないもののみを表示するだけではなく、あいまい検索やワイルドカードを使って絞り込みの幅を広げられるのが特徴です。今回は、スプレッドシートでデータを抽出する方法を別シートが絡む場合も含めてご紹介します。
スプレッドシートのfilter関数は、条件に一致するセルのデータを絞り込み抽出する機能があります。式は、『=FILTER(絞り込みをする範囲,条件,[空の場合に表示する値])』です。該当するデータがなかったときには返す値を指定できますが、書かなくても問題ありません。言葉だけではわかりづらいかもしれませんので、事例を挙げてみましょう。ここでは、以下のようなタイムカードに出社と帰宅の時間が記録されていて、出勤日のみを取り出すとします。
filter関数によってスプレッドシートで当てはまるものを含む範囲を書き出したい、最も左上のセルをクリックして、式を入力しましょう。条件のところに入力する範囲は、特定の1行または1列です。指定された行or列を順番に調べて該当した場合に、同じ列or行の[絞り込みをする範囲]で入力された値を抽出します。今回の例では、『=FILTER(P3:AT3,P4:AT4<>””)』としました。スプレッドシートの4行目のP列からAT列をfilterが調べ、空白を含まないつまり特定の文字を含む行のセルに限って3行目の対応する列の値を抽出しています。
スプレッドシートのfilterは複数範囲を取り出すことも可能です。たとえば、『=FILTER(P3:AT13,P4:AT4<>””)』と変更すると、3行目だけではなく、同じ列の13行目までのデータがまとめて表示されます。ただ、「3行目と13行目のみを取り出したい」のように離れている複数の範囲を取り出す場合には一回では難しいかもしれません。スプレッドシートで取り出したセルとは別のセルに該当する行のみをまとめるか、複数行または複数列にそれぞれfilter関数を適用して絞り込みをする範囲を変えると良いでしょう。
スプレッドシートのfilter関数を使って別シートにある値を絞り込みをすることも可能です。この場合は、式で指定する範囲の前にシート名を入力しましょう。たとえば、「データ」という名前のシートから「結果入力」という名前のスプレッドシートにデータを抽出したい場合は、『=FILTER(‘データ’!P3:AT3,’データ’!P4:AT4)』などと記載します。別シートが絡む場合でも、以下の画像のように複数範囲やこの後ご紹介する複数条件で設定することも可能です。
スプレッドシートでfilter関数を複数条件で使う場合には、[条件]の部分を書き換えます。他の関数で「かつ」や「または」の条件を設定するときはandやorを使いますが、filter関数の場合は少し設定方法が異なるのが特徴です。たとえば、先ほどは出社時間が入力されている日＝出勤日として取り出しましたが、帰宅時間のどちらかしかない日も含める、あるいは両方入力されている日のみを抽出したい場合はどうすれば良いかをみていきましょう。
スプレッドシートでfilterを「または/or」で複数条件に適用するには、『=FILTER(絞り込む範囲,(条件1)+(条件2))』とそれぞれの条件をかっこ()で囲った上でプラス+でつなぎます。今回の例では、『=FILTER(P3:AT13,(P4:AT4<>””)+(P6:AT6<>””)』としました。こうすることで、P列～AT列で4行目または6行目のどちらかが空欄ではない何かを含むものに限定して、同じ列の3行目～13行目の値を取り出すことが可能です。
もし、複数条件のどちらも満たす「かつ/and」で絞り込みたい場合は、『=FILTER(P3:AT13,P4:AT4<>””,P6:AT6<>””)』のように条件をカンマ[,]で羅列しましょう。その場合、『=FILTER(絞り込む範囲,条件1,条件2)』とカンマで3つの項目が並びます。
filter関数を使った際のスプレッドシートに出るエラーをいくつかみていきましょう。まず、「FILTERの範囲サイズが一致しません」と表示されることがあるかもしれません。これは、[条件]で指定した要素(行or列)数と[絞り込みをする範囲]での数が異なることで生じるものです。特に、[絞り込みをする範囲]の列あるいは行の場所が[条件]で使っているところと別々の場合はその数が違っていると気づかない可能性もありますから、今一度範囲を見直してみましょう。
また、「FILTERの範囲は単一行か単一列にしてください。」と出てくることもあるかもしれません。これは、[条件]で参照している範囲が行も列も複数であることが原因です。[絞り込む範囲]は複数行複数列でも問題ありませんが、条件は横方向または縦方向のどちらかに見る関係で列と行の片方は1である必要があります。
スプレッドシートのfilter関数は、条件に当てはまる数字や文字列を抜き出せますが、何行または何列にもわたって表示されることには注意しましょう。先ほどの例のように何もなければ問題なく該当のセルが出てきますが、下の図のように既に別の値が記載されていると、スプレッドシートが上書きされてデータが消えるあるいはフィルタが思うように反映されないかもしれません。
その場合は、抽出する先のセルを別にするのも一つの方法ですが、スプレッドシート上のデータを結合する関数TEXTJOINと組み合わせるのもおすすめです。
詳しい方法に関しては以下の記事で触れていますので、あわせて参考にしていただければ幸いです。
今回は、スプレッドシートで条件に一致するセルの絞り込みとして、filter関数を使ったやり方をご紹介しました。複数条件で使う場合は、普段使っているようなかつまたはの表記とは異なる点がポイントです。様々なエラーが起こりやすいため、それぞれの原因と対処法を把握しておくと良いでしょう。最後までお読みいただきありがとうございました。