フィルター機能とは？
特定の条件に合致するレコードを表示
FILTER関数の説明に入る前に、簡単にフィルター機能の解説をしていきます。
フィルター機能、またの名を「オートフィルター」とは、特定の条件に合致するレコードを表示したり、特定の項目をベースにレコードの並べ替えができる機能です。
例えば次のようなデータがあるとして、フィルター機能を使えば、「交通費」のみを抽出する、といった対応が可能です。
使い方は非常に直感的で、「データ」の「フィルター」のボタンを押下して、フィルターの設定をしたところで、「科目」のところの「▼」を押して、「交通費」にチェックを入れます。
SUBTOTAL関数はセットで覚える！
このようなフィルター機能とセットで使われるのが、SUBTOTAL関数です。
フィルターを掛けた対象に関して、「表示されているレコード」のみに対して、合計の算出などができます。
試しに、D16セルに「=SUBTOTAL(9,D1:D15)」と入れると、交通費のみの総和が表示されます。
この時注意すべきは、「=SUM(D1:D15)」という入力では、すべての総和が表示され、交通費のみに絞った総和が表示されないということです。
試しに、さらに部門を「営業部門」だけに絞ると、式の中身は変えていなくても、表示が変わっていることが確認できます。
SUBTOTAL関数は、第一引数に何を指定するかによって、総和だけではなく、平均や最大値、最小値などを返却できるようになっています。
FILTER関数とは？
フィルター機能でできることはほぼできる
このように、便利なフィルター機能ですが、FILTER関数は、このようなフィルター機能でできることは、ほぼできます。
使い方も、慣れてしまえば簡単で、フィルターを掛けたい範囲を第一引数に取り、第二引数に表示したいレコードの条件を入力するだけです。
上記のように、A:Dの範囲に対して、「C列が”交通費”のもの」という指定をすると、対象のレコード全体が表示されます。
ちなみに、表示をD列だけにしたければ、「=FILTER(D:D,C:C=”交通費”)」と入力すればその通りに表示されます。
複数の条件を指定してあげたい場合は、「=FILTER(A:D,(C:C=”交通費”)*(A:A=”営業部門”))」といった具合に、「*」で結んであげれば対応可能です。
フィルター機能ではできないOR条件
さらに便利なことに、FILTER関数では、フィルター機能ではできないことができます。
その例の一つが、「OR条件での表記」です。
上の例で例えば、「科目が交通費、または部門が営業部門」といった条件を出そうとすると、フィルター機能では1段階ではできません。
そういった場合、「=FILTER(A:D,(C:C=”交通費”)+(A:A=”営業部門”))」と、第二引数の条件を「+」で結んであげると、OR条件での表記が可能です。
さらに、条件にワイルドカードを入れてあげることで、さらに柔軟な検索も可能です。
ワイルドカードについては、次の記事でまとめています。
SUBTOTAL要らず！
FILTER関数は、さらに工夫して使えば、SUBTOTAL関数も不要になります。
というのも、FILTER関数は上記の通り、配列を返却するものであり、且つ検索条件の範囲はフィルターを掛ける範囲の外にあっても問題ありません。
なので、例えば、「=SUM(FILTER(D:D,C:C=”交通費”))」として挙げると、一撃で交通費のみの総和を求めることができます。
もちろんこの場合、SUMIFS関数などを使った方が早い場合が多いですが、複雑な条件で検索を掛けたい場合などは、FILTER関数の方が使い勝手がいいケースもあるので、覚えておくと便利です！
なお、SUMIFSなどの○○IF系関数については、こちらの記事にまとめています。
コメント