新しいExcelにはFILTER関数という関数があり、範囲の中から、検索条件で当てはまったものを抜き出すという関数です。
この関数が本格的に実装されれば、フィルタの作業がもしかしたらいらなくなるかもと思っています。
そのくらいインパクトのある関数なのですが、Excel2016では、記事執筆時点では使えません。
ちなみにGoogleSpreadSheetでは使えています。
このFILTER関数を、2016以前のバージョンでも使う方法があるので紹介します。
一致しているか
まず、検索条件に一致しているデータを検出するために、比較します。
一致しているかを検出するには、IF関数でどことどこが一致しているか、一致していればどうするか、という方法を思いつきますが、実はもっとシンプルな計算式で実現できます。
この表の一致欄には、どんな計算式が入るでしょう。
セルD2が比較データ、これは下にコピーしても移動してはダメなので絶対参照、比較対象がA2対象なので、
=$D$2=A2
という式ができて、それが10行目までコピーされます。
え？これだけで判定できるの？と思いますが、一致していればTRUE、一致していなければFALSEになります。
TRUEは真、FALSEは偽という日本語になりますが、まさに式が成立していれば真、合っていなければ偽になっています。
TRUEは１でFALSEが０
実は、このTRUEは、計算式の中に入れると1になり、FALSEは0になります。
ここ、間違えたすいのですが、１はTRUE、０はFALSEではなく、あくまで、TRUE、FALSEを計算式の中に入れると０か１になります。
F列にE列×１の計算域を入れてみます。
なりましたよね。
識別番号は行番号
一致した行をピックアップするのですが、行を求めるにはROW関数を使います。
では、ここでTRUE、FALSEに、行番号をかけてみましょう。
すると、一致したところは行番号、そうではないFALSEのところが０になるのです。
０は何をかけても０ですから。
一致した行だけを抜き出す
０ではない一致したものだけを抜き出します。
そのために、注目したいのは、一致したものが小さい順に並んでいるということです。
つまりこの行番号を下から順にピックアップしていけば、その行を拾うことができます。
小さい順に番号を求めるのは、SMALL関数です。
SMALL関数は、範囲の中の、何番目の数値を求めます。
=SMALL($F$2:$F$10,ROW()-1)
これを2行目のセルに入力し、コピーすれば、小さい順にデータが求まります。
でもちょっと違いますね。０が先頭に来てしまいました。
０は下にきて、昇順で並んでほしいですよね。
そうなると、F列の時点で、FALSEなら10000000とかしてしまえば、そのデータは後に来るようになりますよね。
もっといろいろな手法がありますが、今回はシンプルにIF関数を使うようにF列の式を変更します。
=IF(E2,ROW(),100000000)
では、この行番号に対するデータを出します。
そのためにはINDEX関数を使います。
=INDEX(A:A,$G2)
G2は下にコピーしたら動いてほしい、横にコピーしたら移動してほしくない、A列はコピーを横にしたら移動してほしいのでこの計算式になります。
この式をH列とI列に入れれば、このようになります。
これをIFERROR関数で処理します。
2つの条件でフィルタする
条件が２つあったら、その一致しているかの条件をかけ算すればいいのです。
例えば閾値を50に設定してみましょう。
その場合は、E列の計算式を次のように変更します。一つ一つの比較は（）で囲みます。
=($D$2=A2)*($D$4<B2)
TRUE、FALSEをかけ算したので0と１になりました。
でも、以降の計算式はきちんと計算してくれ、２つの条件に当てはまったものを抽出しました。
かけ算ではなく、AND関数を使ってもいいです。
ちなみに両方が一致しているAND条件ではなく、どちらかが一致していたらというOR条件ならかけ算ではなく、足し算でいいです。
この場合、一致したかどうかが０と１以外が出てきますが、きちんと判定されます。
ANDは論理積、と呼ばれています。
積ってかけ算ですよね。
ORは論理和、と呼ばれています。
和って足し算ですよね。
コメント