スプレッドシートで特定の条件に当てはまるものだけを抽出して、表示するときに使うのがFILTER関数です。
「AIが当たり前である今、関数の使い方を覚える必要があるのか？」
と疑問に感じるかも知れませんが、ぼく個人の考えとしては「ある」と思っています。
理由はAIへ指示する時により具体的に指示が出せたり、AIが出力したものの間違いに気づけるようになるからです。
FILTER関数とは？
FILTER関数は、データの一覧から指定した条件に合うデータだけを表示する関数です。
例として、売上一覧の中から「東京支店だけ」「売上100万円以上だけ」「未対応の案件だけ」のような条件に一致するデータを表示させられます。
ツールバーから設定できるフィルター機能の場合は元の表を絞り込んで表示しますが、FILTER関数は別の場所に表示できるので、特定の条件にあったデータだけのシートの作成が可能となります。
ただし、この記事でh総会するように、４～８行目を指定した式では、９行目以降に追加したデータは対象になりません。
追加した分も抽出したい場合は、数式の範囲を変更してください。
元データが更新されるとFILTER関数で抽出される結果も自動で更新されるため、毎回手作業で絞り込む必要がないのもメリットの一つです。
FILTER関数の基本構文
=FILTER(範囲,条件1,[条件2, …])
「指定した範囲から、条件に合うものを出して」という意味になります。
範囲：どのデータを表示したいか
条件：どんなデータだけを表示したいか
このように考えると少しイメージしやすくなるかも知れません。
例えば、B列に”拠点”、C列に”担当者”が入っているとします。
その時、拠点が東京の人だけを表示したい場合は、以下のように記述します。
=FILTER(C4:C8,B4:B8="東京")こうすることでB列に東京の値を持つC列の値だけが表示されます。
とはいえ、まだイメージが難しいかも知れないので、実際の画面を見ながら使い方を確認していきましょう。
表から「東京の場合だけ」を抽出する方法
先ほど、記述した関数をE列に挿入してあります。
今回は表示する範囲をC4:C8に設定し、B4:B8に東京を持つものという条件にしているため、該当する担当者だけが表示されます。
田中
鈴木
伊藤
それぞれの値が結果として表示されます。
田中、鈴木、伊藤がC4:C8、B4:B8が東京。つまり大阪や福岡は条件に当てはまらないため佐藤や高橋が表示されません。
複数条件（AND）で抽出する方法
「条件を複数設定したい場合はどうしたらいいの？」
という方もいるかと思います。
FILTER関数は複数条件も設定可能。
記述方法もシンプルです。
そもそものFILTER関数の書き方は
=FILTER(範囲,条件1,[条件2, …])
となっています。
つまり、条件を追加する時は条件1を書いたら『,』を記述してから2つ目の条件を書けばよいです。
今回はA列に売上の項目を追加しました。
その上で今回、記述した式は以下。
=FILTER(C4:C8,B4:B8=”東京”,A4:A8>=100000)
『,A4:A8>=100000』この部分を追加しました。
これはA4からA8の数字が100,000以上という条件になっています。
「B列が東京」かつ「A列の売上が10万円以上」の人について、C列のデータを表示する
という意味です。
このように、複数の条件をすべて満たすデータだけを抽出する方法をAND条件と呼びます。
鈴木さんは売上が50,000のため、今回の条件からは外れているため、表示されなくなります。
>=になっているため100,000を”含む”になっています。
＝が抜けて、>だけの場合は100,000より上の数字という意味になります。
OR条件で抽出する方法
FILTER関数では、「どちらか一方の条件に当てはまれば表示する」という使い方もできます。
例えば、拠点が「東京」、または「大阪」のどちらかに当てはまる人だけを表示したい場合は、以下のように記述します。
=FILTER(C4:C8,(B4:B8="東京")+(B4:B8="大阪"))
この数式は、次の条件を表しています。
B列が東京または大阪の値を持つC列の値を表示。
FILTER関数でOR条件を設定するときは、それぞれの条件を丸括弧で囲み、条件同士を「＋」で繋ぐ必要があります。
(条件1)+(条件2)
これが一つポイント。
上記の例では、東京または大阪の担当者が表示されることになります。
さらに、売上条件まで追加する場合は以下のように記述をします。
=FILTER(C4:C8,(B4:B8="東京")+(B4:B8="大阪"),A4:A8>=100000)最初の表示したい範囲をC4:C8とし、それ以降には条件が続いています。
そのため、C4:C8を、A4:C8にすると、条件に該当するA~C列の値が表示されます。
別シートに抽出結果を表示する方法
別のシートに抽出結果を表示することもできます。
これはスプシを使う上では必須のテクニックとなります。
ただし、同じスプレッドシート内からデータを抽出する場合と、別のスプレッドシートからデータを抽出する場合では関数が異なる点に注意が必要となります。
同じスプレッドシート内
シート名が売上一覧というのがあり、その中から東京支店のデータだけを別シートに表示したい場合は、以下のように記述します。
=FILTER(売上一覧!A4:C8,売上一覧!B4:B8="東京")上記の数式では、「売上一覧」シートのA４からC8までを表示範囲として指定し、B列が東京の行だけを抽出します。
違いとしては、セル範囲の指定の前に抽出元のシート名を入れることです。
=シート名!範囲となり、シート名と範囲の間には「!」が入っているのを見落とさないようにご注意ください。
シート名に空白（スペース）が入っている場合はシート名を「’」のシングルクォーテーションで囲う必要があります。
=FILTER('売上 一覧'!A4:C8,'売上 一覧'!B4:B8="東京") 元データを更新すると、別シートに表示している結果も自動的に更新されます。
違うスプレッドシートからデータを持ってくる場合
スプレッドシートではよく使う関数の一つであるimportrangeを使います。
=IMPORTRANGE("スプレッドシートのURL","範囲の文字列")という型になります。
これにFILTER関数を組み合わせると以下のようになります。
=FILTER(
IMPORTRANGE("スプレッドシートURL","売上一覧!A4:C8"),
IMPORTRANGE("スプレッドシートURL","売上一覧!B4:B8")="東京"
)最初のIMPORTRANGEで表示するA~C列を読込み、2つ目で条件を判定するB列を読込みます。
スプレッドシートURLを実際のものに置き換えてください。
初回に限りアクセス許可が必要となるので、表示されたら許可するをクリックしましょう。
よくあるエラーと対処法
#N/Aがでる
条件に一致する値がない場合は、#N/Aでエラーを返します。
先ほどの表で言うと、拠点の条件をB4:B8=”名古屋”のようにした場合、表に名古屋を持つ値が無いため、エラーが返ってきます。
抽出範囲と条件範囲の行数が違う
関数で指定している条件の範囲がズレている場合はエラーが返っています。
=FILTER(C4:C8,B4:B7="東京")表示範囲が4～8、条件範囲が4～7とズレが有るためエラーとなってしまいます。
違うものが表示される場合は条件している列が違う、もしくは表示範囲がズレている可能性があります。
関数の中身を確認してみましょう。
FILTER関数を仕事で使う具体例
FILTER関数は「毎回同じ条件で見る一覧」を作るとき等に活用できます。
例えば、売上の一覧から東京拠点だけを別シートに出す、ステータスが未完了の案件だけを抽出するなどです。
ただし、FILTER関数で作られたものはあくまで「見るためのもの」です。
元データから抽出しているだけなので、内容を変更したい場合は、抽出結果ではなく元データ側を編集してください。
FILTER関数で表示しているデータはあくまで、見るためのものである。
FILTER・QUERY・XLOOKUPの使い分け
| やりたいこと | 第一候補 | この表なら |
|---|---|---|
| 条件に合う複数の行を一覧にしたい | FILTER | 東京の担当者を全員表示する |
| 抽出に加えて、列の選択や並べ替え、集計もまとめて行いたい | QUERY | 拠点別の売上をまとめる |
| キーを使って対応する値や行を探したい | XLOOKUP | 担当者名から対応する売上を探す |
元データから抽出する関数は複数ありますが、それぞれ、上記を基準に使い分けができます。
AIに式を作らせる指示の出し方と理解しておくポイントとは？
AIで指示を出すためには以下のポイントを理解しておくと良いと考えています。
- 最終的にどんなデータが欲しいのか
- どこからデータを抽出したいのか（Excel、スプレッドシート等）
- どの条件でデータを抽出したいのか
- どこにデータの抽出結果を出力したいのか
まずは、最終的にどんなデータが欲しいのかを考える必要があります。
ゴールさえ分かればAIへ指示を出すことで少しずつ軌道修正ができるからです。
逆にココが分からないままAIへ指示を出してしまうと、会話のたびに内容がズレてしまい、結果としてAIも混乱し、思うようなデータが手に入らなくなります。
先ほどのFILTER関数の例で言うと
- どんなデータが欲しいか→東京で売上100,000以上の人だけが表示されているデータ
- どこからデータを抽出したいのか→スプレッドシートのシート名◯◯、データ範囲はA~C列
- どの条件でデータを抽出したいのか→売上100,000以上で、地域が東京
- どこにデータの抽出結果を出力したいのか→シート名◯◯のF列
となるため、AIへの指示文例は以下となります。
Googleスプレッドシートで使う数式を作ってください。
元データのシート名は「売上一覧」です。
3行目が見出しで、データは4〜8行目にあります。
A列：売上（数値）
B列：地域
C列：担当者名
地域が「東京」で、売上が100000以上の行から、
担当者名だけを抽出したいです。
結果は「売上一覧」シートのF4から下に表示します。
F4に入力するFILTER関数と、
数式の各部分の意味を説明してください。のようなプロンプトになります。
今回は一例として同じシートのものを同じシートの別の範囲へ抽出する動作となりますが、実務では別シート名で指定することが多いと思うので、正しいシート名を指示しましょう。
もし、上記を投げても出てこない場合はさらにセルの情報やデータ範囲を教えてあげると作成してくれるはずです。
AIが作った式で抽出したデータは、元の表と比較して結果が合っているかを確認しましょう。
確認をせず、次々に作成をしていくと気がついたら大きく間違ったデータが完成しているかも知れません。
FILTER関数のまとめ
僕個人としては、AIの登場により今まで自分が作りたいけど技術的にできなかったものが実現できるようになってきているので、個人の可能性が広がると感じています。
「パソコンを使いたいけど、関数とか苦手・・・。」といった場合でも、AIへ指示することさえできればある程度形にできます。
ただ、実務で使う上ではAIが出したものが100％正しいとは限らないため、どこが間違えているかに気付けるためにも、関数の基本的な事は知っておいたほうが良いと感じています。
その一つとして、今回のFILTER関数をご紹介しました。
条件に一致するデータが表示できる関数なので、実務では使い道が多いと思いますので、ぜひ使ってみてください。
コメント