Excel2019より前でもFILTER関数が使える方法☆

2024年8月11日

FILTER関数って便利なんですけど、Excel2021以降かMicrosoft365じゃないと使えないので、ちょっとやだなぁって思うんですけど、Excel2019でもやれないことはないので、方法を紹介したいと思います！
使う関数はCOUNTIFS関数とVLOOKUP関数です！

COUNTIFS関数で条件に合ったものを数える

まず、今回の表はこんな感じで、全部が70点以上のものをフィルターするとします。

COUNTIFS関数で条件に会うたびに1ずつ増やすってことをしたいんですけど、そのためにA列に「検索番号」って列を作ります。

そしたらセルA2に計算式を入れます。

=COUNTIFS($C$2:C2,">=70″,$D$2:D2,">=70″,$E$2:E2,">=70″)

ちょっとこの計算式、よくみると、変なところがあるんですよね。セル範囲が$C$2:C2ってなってて、どっちも同じC2だし、片方だけ絶対参照だし。
これ、下にオートフィルしたときのことを考えると、面白いことが起きるんです。
$C$2は絶対変わらないんですけど、C2はどんどん下に移動していくんです。C10までオートフィルしたらC2からC10まで範囲が広がるんですね。
COUNTIFS関数の動作で考えると、はじめはセルC2だけで条件が当てはまるか数えてるけど、下にいけばC2から下に動いただけの大きさのセル範囲でみていくんです。
そうすると、だんだん見つかる数が増えるってことなんです。

下にオートフィルしてみましょう。

初めは0個ですけど、見つかるたびにだんだん数が増えていきます。すごいでしょ。
この数が増えた行がその条件に当てはまったところなんですね。

VLOOKUP関数で見つかった数が変わったところを抜き出す

あとはこの検索番号が変わったところだけで抜き出せばいいんですね。

そのために使えるのが、以外にもVLOOKUP関数なんです。

VLOOKUP関数の完全一致は、検索値が同じものがあった場合、最初に現れる一番上のものを探します。だから、例えば2という数字が4個あっても、最初の2を探すので、検索番号が変わったところを見つけてくれるのです。

まずはそのために1から始まる番号をどこかに作ります。

この番号を検索値にして、左の表からVLOOKUP関数で値を探します。
セルH2に生徒番号を求めるには、次の計算式を入力します。

=VLOOKUP(G2,$A$2:$E$21,2,FALSE)

同じように、I列からK列まで列番号を変えて入力していったら、全部に入ってくれるんです。

この3つのセルを下にオートフィルしたらフィルターの結果が表示されます。

検索されたものが4つしかないので今回は、4より大きい場合は見つからない#N/Aエラーになります。でもこれはIFERROR関数かなんかでうまく処理できますね。
見つかった個数が作った計算式より多くなっちゃうってことには対応できないので、少し多めに作る必要があります。あまりにも縦に多い表のフィルターには向いてなさそうですね。それだけいっぱいVLOOKUP関数を用意しなきゃなんで、あまり多いとExcelが負担になっちゃうからね。