最新のExcel関数の一つ、FILTER関数のお話です。
その名の通りフィルタする関数で、関数でできているので、条件を入れたり、値を追加したとたんに答えが出てきたりします。
フィルタ機能だと、少なくともどんな条件か選ぶアクションが必要ですよね。
もし、そのアクションを忘れたり間違えたりしたらきちんとフィルタされないのです。
その心配がないのがとても大きなメリットです。
でも、Excel2016以前では動作しない関数なのがとても残念です。
今回はCOUNTIF関数の応用でFILTER関数と同じことを実装してみたいと思います。
この方法ならExcel2016でもFILTER関数と同じことができるんですよ、ほんとに！
関数でフィルタする
COUNTIF関数は、条件に当てはまったものの個数を数える関数ですよね。なぜそんな関数でFILTER関数と同じことができるのでしょう。
次の表をご覧ください。
ここで、COUNTIF関数で女性の人数を数えれば、5人と出てきます。
それを、それぞれの行までの人数で数えます。
ちょっとわかりにくいですね。では具体的に。
1行と2行目での女性の数は、1人です。
1行目から3行目の女性の数は、2人です。4行目までで3人。
5行目は男性なので、5行目までで3人。6行目も男性なので3人です。
このようにすべての行に対して求めてみましょう。
計算式は次のようになります。
=COUNTIF($B$1:B2,"女")
B1は絶対参照、B2は相対参照なのでコピーで動きます。
この技は累計をSUM関数で求めるときにも使う技ですね。
この計算式をセルD2に入力して下にコピーするとこのようになります。
見てみると、1から始まって、検索条件にヒットした行で１上がっています。
その数字が変わった行のでデータだけ抜き出せばいいのですね。
そして、このそれぞれの数字の一番初めの行は、VLOOKUP関数やMATCH関数でヒットできるのです！
VLOOKUP関数やMATCH関数は上から見ていって、該当の検索値と一致した最初のものを検出するからです。
つまり、この番号を１から順に検索していけばどの行を出すのかがわかるということです。
下の表は、F列は単純に1からの連番です。
G列は、MATCH関数で連番に対するD列の行数を出しています。
H列は、INDEX関数でA列の中からG列の行数に対する名前を出しています。
フィルタ、できてますよね。
あとは計算式にIFERROR関数などでエラー回避すればいいのではないでしょうか。
複数条件のフィルタ
複数条件のフィルタも簡単です。
Excel2016であればCOUNTIFS関数で複数条件のカウントができますので、それを使えばできます。
それ以前のExcelでは複数条件のカウントができないので、AND関数などの論理関数を使います。
AND関数はなおかつ条件、OR関数はまたは条件ですね。COUNTIFS関数もAND条件になっているので、Excel2016でもまたはの条件を使いたいときはこれから説明する使い方をします。
下の表をご覧ください。
セルD2には、女性でなおかつ150万以上という条件が入っています。計算式は次の通りです。
=AND(B2="女",C2>=1500000)
そしてセルE2では、D列がTRUEの数を先ほど同じ方法で求めています。計算式は次の通りです。
=COUNTIF($D$1:D2,TRUE)
そしてG列以降にE列の値を使ってフィルタの結果を求めています。
まとめ
Excelはどんどん進化していますが、昔のExcelを使っていると、新しい機能からはどんどん遅れてしまいます。
なので、最新版を使うといいのですが、例えばそのソフトを買った1か月後に新しいバージョンが出る、なんてこともあります。
これはタイミング次第なのですが、それによって現状使えている人と使えていない人が出てしまうのですね。
教える方は最新版で動作するようにしています、というものの、1つ前のバージョンはまだまだ現役で使っているのでそれで動作しないという人も出てきます。
教える方も1つ前か2つ前のバージョンくらいまでは動作するように教えるのですが、さすがにMicrosoftでサポート切れたらからもう使わないでと言っているバージョンについては把握しきれません。
Officeの最新版を使うには買い切り版ではなく、Office365という月か年で契約する形のものを使うと良いのです。
ということで、昔のバージョンでもなんとかすればその関数と似たようなことができる、そういう技を今回は紹介しました。
応用すればUNIQUE関数やSORT関数もできます。その記事はまた後日。
コメント