MOS365 Excel Expert出題範囲「FILTER関数」解説

MOS365の出題範囲が発表になり、Excelの科目では新たにFILTER関数が出題範囲に入ることになりました。FILTER関数は最近追加された関数で、今まで機能としかできなかった、フィルターを、機能ではなく、関数で行うことができるものです。

FILTER関数は新しい関数ですのでまだ情報が少なく、存在自体も広まっているとは言えません。当然MOS受験者にもそんなことができる関数があるとは知らない人も多いでしょう。

そこで、今回、MOSにFILTER関数がどんな感じで出題されるかを考えながら、あくまでMOSレベルで深堀しない範囲で解説していきます。

FILTER関数とは
FILTER関数の予想問題文
まとめ
関連リンク

FILTER関数とは

FILTER関数は、上記の通りフィルターする関数です。機能であるフィルターが計算式でわざわざできるのでしょうか。それはデータを更新したり追加したりしたときに、機能でのフィルターはもう一度フィルターのかけなおしの手作業が必要になりますが、FILTER関数だと、計算式なので、そのまま再計算が行われ、何もせずにフィルターの結果が変わります。これはとても素晴らしいことで、フィルターのかけ忘れがなくなるということになります。

もちろん機能のフィルターに比べて、FILTER関数のデメリットもあります。元データを追加したり削除したりしたときは範囲を指定しなおす必要があります。しかしこのデメリットはフィルター元のデータをテーブルにすれば、構造化参照が使えるようになり、データの範囲をテーブル名で指定できるので、書き換える必要がなくなります。このことからFILTER関数の使い方としては、元のデータはテーブルデータがいいということになります。もう一つのデメリットは、計算式なので、元データのほかにフィルター後の脚気を出力するセル範囲が必要になるということになります。

FILTER関数の書式を解説します。

これはよく勘違いするポイントになるのですが、フィルターを考えてみると必要なものは「元データ」「検索条件の範囲」「検索条件」が必要で、COUNTIFS関数を考えてみると、検索条件が複数ある場合は、「検索条件の範囲」「検索条件」が何個も連なる形で指定するのですが、実際のフィルター関数の書式は次の通りです。

=FILTER(元データの範囲,検索条件,検索結果がないときの値)

元データの範囲は、元のデータすべてです。項目行は入れないです。

検索条件は、「検索条件範囲」「演算子」「検索条件」の3つからなります。「検索条件範囲」は検索条件の列で絶対に元データ範囲と同じ行数にします。「演算子」は「=」や「>=」のようなものですね。「検索条件」はどういったものを抽出するかを表すセルか値です。

検索結果がないときの値は、1つも検索されなかったとき、FILTER関数は「#CALC!」というエラーを出してしまうのですが、その代わりに出したい値があれば指定します。

ということで、FILTER関数では、条件は1つしか指定できません。この検索条件はCOUNTIF関数で使うような関数ではなく、どちらかというとIF関数に近いと思います。IF関数も1つの引数の中には1つの条件しか入らないという共通点があります。そこで、IF関数では複数条件をAND関数、OR関数で指定できますが、FILTER関数ではその方法は使えません。その対処は「FILTER関数の予想問題文」で解説します。

FILTER関数の実例ですが、次のようなものになります。

=FILTER(販売,販売[取引先]="泉商店")

テーブル「販売」の中で「販売」テーブルの「取引先」が「泉商店」であるものをセルE2に出力しています。これが基本形です。

=FILTER(販売,販売[販売額]>=20000,"データなし")

テーブル「販売」の中で「販売」テーブルの「販売額」が「20000以上」であるものをセルE2に出力しています。一つも該当がないので「データなし」が出力されています。

FILTER関数の予想問題文

MOSでは、FILTER関数が深堀すると一気に難しくなるので、出題パターンはそれほど多くはならないと思います。基本形と、もしかしたら複数条件が入るかもしれません。

セルE5に販売内容テーブルの販売商品がセルE2のものだけを表示するための関数を作成してください。

=FILTER(販売内容,販売内容[販売商品]=E2)

基本形でセル参照する、というケースです。

セルE5に、販売内容テーブルの販売日がセルE5以降のレコードだけを抜きだす計算式を関数を使って求めてください。もし抜き出した結果が1件もなければ「期間内なし」と表示してください。

=FILTER(販売内容,販売内容[販売日]>=E2,"期間内なし")

不等号を使った「以上」で解答するのと、1件も見つからなかったときの指定があります。

セルE5に、販売内容テーブルの販売期間がセルE2からE3のものだけを表示するための関数を作成してください。検索結果がなければ「期間内なし」と出力します。

=FILTER(販売内容,((販売内容[販売日]>=E2)*(販売内容[販売日]<=F2)),”期間内なし”)

この問題が出題される可能性は薄いと思います。しかしどんな試験でも鬼門の問題があり、出題範囲で最大の難易度で出題する問題が１、２問はあります。もしこの問題が出題されればその鬼門問題と言えます。そのような問題はわからないものはいくら考えてもわからないので飛ばすという対応をして試験後にどう解答するのが正解なのか調べるということをするのですが、きちんとした知識があればその場で対応できますので解説します。

まず、期間を「の間」と指定されるということは「◯以降」と「◯以下」の「なおかつ」で結ばれる2つの条件の組み合わせの条件ということを思いつけるようにしましょう。「なおかつ」は「AND」条件ですね。前述のようにFILTER関数は条件を1つしか設定できません。また「AND関数」を組み合わせるということもできません。そこでこういうことができると知っておいてほしいのが、コンピュータの世界では「*」で「なおかつ」を「+」で「または」を表現できるということです。FILTER関数の条件に複数条件を指定するには。この方法で行います。条件の一つ目「販売内容[販売日]>=E2」を括弧で囲んで何よりも優先して条件を判定してしまいます。2つ目の条件「販売内容[販売日]<=F2」についても同様です。そしてそれぞれ括弧で囲んだ2つの条件を「*」で結合し、最後に条件全体を1つの条件だとFILTER関数に思わせるために括弧で囲みます。これがFILTER関数の複数条件の指定方法です。この複数条件でFILTER関数を使う方法は実務的にも使える方法ですので、試験に出ないとしても覚えておきましょう。

まとめ

FILTER関数についてMOSに出題されるレベルを予想しながら解説しました。基本形では表全体と条件の2つの引数を指定すればよかったですね。条件は「範囲」と1つのセルや値を指定します。ポイントは条件の引数は「条件範囲」と「条件の値」の2つに分かれているのではなく、1つの引数の中に「条件範囲」と「条件の値」とその2つを「演算子」で結合した条件式が入るということです。だから複数条件はできないことはありませんが複雑になってしまうのです。そして3番目の引数として「見つからなかったときに表示する値」があるので、3番目以降に複数条件を指定できなくなってしまい、第2引数に条件の範囲も演算子も条件も、2つ目以降の条件式も全部入れなければいけなくなってしまっています。これは少しFILTER関数の使いにくい部分かもしれません。

しかし、FILTER関数では難しいとはいえ複数条件を指定することができるということは大きなメリットで、考えてみれば機能のフィルター（下向き三角をクリックするもの）ではＯＲ条件を指定できないのですが、FILTER関数では可能になっています。

今後、Excelの関数の中ではVLOOKUP関数以上、XLOOKUP関数以上に強力な関数と言われるかもしれない関数ですので、MOSの勉強をしながらしっかり覚えておきましょう。