スポンサーリンク
Excel2021とかMicrosoft365とか、最近のExcelでは様々な関数が追加されたが、今回はその中でもかなり利便性が高いと考えるFILTER関数を取り上げる。
これは文字通り、オートフィルタ機能のように、データのフィルタリングをする関数だ。
FILTER関数の強み
FILTER関数は、オートフィルタと違うメリットがあるのかよく分からなかったので、私も食わず嫌いで使わずにいた。
でも実際のところ、下記のようにオートフィルタを超えられる便利な点が幾つもある。
- 元のデータ範囲自体はイジらず、別のセル範囲にフィルタ結果を表示できる。
- 他の関数と組み合わせて、通常のオートフィルタではできないフィルタリングができる。
- オートフィルタでは、絞り込み用の画面を開いたりする手間が必要だが、関数の仕込み次第でこれを無くせる。
- 元データが横に30列あるとして、その中から5列だけ表示させるといった指定も可能。
弱みはというと、ちょっと下準備が必要だということがあるので、その仕込みを済ませておいて繰り返し使うような場合に力を発揮すると思う。
逆に、その場1回限りの作業で使うのには、仕込みの手間に見合わないので適さないと思う。
FILTER関数の構成
FILTER関数の引数の構成は、公式なんかでは
=FILTER(配列,含む,[空の場合])
とか紹介されるけど、もう少し分かりやすく書くと
というようになる。
では実際にFILTER関数を使ってみる例。
下図ではA1:G15セルの範囲にあるデータから、D列が「既婚」であるデータを抽出するということで、J3セルに
=FILTER( $A$2:$G$15 , $D$2:$D$15=”既婚” , “???” )
と式を入れている。
FILTER関数は「スピル」機能を前提とした関数なので、J3セル1つに式を入れるだけで結果が縦横に広がってくれる。
くれぐれも、その結果表示用のセル範囲には、余計なデータを入れず空けておくように。スピルの特性として、余計なデータがあったらエラーになるので。
上記の式では
$D$2:$D$15=”既婚”
の部分がスピル対象になっている。
本来は$D$2=”既婚”、$D$3=”既婚”、・・・と1個1個のセルを対象に条件判定していくところを、一気に複数セルを対象にしている。
FILTER関数の注意点
この時点で既に分かるFILTER関数の注意点として、次のようなことがある。
タイトル行を関数で別認識させられない
J3セルのFILTER式
=FILTER( $A$2:$G$15 , $D$2:$D$15=”既婚” , “???” )
は、元データのタイトル行である1行目を含めず、2行目から対象にしている。
FILTER関数は、対象データ範囲に指定したセル範囲が、最初の1行目はタイトル行だとかいう指定をすることができない。
タイトル行とデータ行とを区別せず、必ず全てがフィルタ対象となるデータ行だということになってしまう。
ここはなんとか、関数の引数で指定できるようにしてほしかったのだが。
書式設定に注意
次にL列は、元のデータ範囲でいうC列「誕生日」の列だが、日付型の表示をしてほしいところが、シリアル値ということで単なる数字の羅列になっている。
シリアル値というのは、日付でいう1900/01/01を数値の1という起点にして、そこから日付値に対して内部的に付与される数値といったところ。
それが下図では、たとえば日付1951/04/16 に対応するシリアル値は18734 ということで表示されていて、意味不明になっている。
こういったことを防ぐためには、予め、FILTER関数が作動するセル範囲には適切な書式設定をしておかないといけない。
これも、元のデータ範囲における書式設定に自動で従うようにしてくれたら良かったなあと。
それを考慮して、J2:P2セルはタイトル行として設定し、L列には日付の書式設定をしたものが、下図のようになる。
FILTER関数を使うには、残念ながらこういう下準備は必要になる。
たとえばB列「年齢」が45歳以上であるという条件指定をするなら、不等号「>=」を使って、下図のようになる。
以下、他の関数なども組み合わせた、ケースごとの具体的な使用例を見ていく。
日付のフィルタリング
日付に関するフィルタの指定として
>=1980/01/01
といった書き方は残念ながら駄目なのだけど、代わりにDATE関数を組み合わせて
=FILTER( $A$2:$G$15 , $C$2:$C$15>=DATE(1980,1,1) , “???” )
という書き方をすれば良い。
また、C列の日付の年が1960年に
=FILTER( $A$2:$G$15 , YEAR($C$2:$C$15)=1960 , “???” )
というようにすれば良いし、4月という条件ならば
=FILTER( $A$2:$G$15 , MONTH($C$2:$C$15)=4 , “???” )
というように、各種関数を組み合わせれば良い。
一部の列(連続)だけを残す
下図で元のデータ範囲はA列～G列だが、その一部のB列～E列だけを結果に残したい場合は、
=FILTER( $B$2:$E$15 ,
と、元のデータ範囲をそのように指定すれば良い。そしてフィルタリング条件の範囲を
($G$2:$G$15=”ドコモ”)
などと、元のデータ範囲の外に指定しても、正常にフィルタは作動してくれる。
まとめると、次のように関数を書けば良い。
=FILTER( $B$2:$E$15 , ($G$2:$G$15=”ドコモ”) , “???” )
一部の列(非連続)だけを残す
元のデータ範囲がA列～G列の7列で、そのうちB列・D列・E列・G列という非連続の列だけを結果に残したいという場合
=FILTER ( FILTER( $A$2:$G$15 , ($G$2:$G$15=”ドコモ”) , “???”), {0,1,0,1,1,0,1} )
というようにする。FILTER関数を2重に貼るのだが、末尾の
{0,1,0,1,1,0,1}
というのは、残したい列には1を、残したくない列には0を指定するという指定をしている。
{ }記号の中には、元のデータ範囲が7列だから7個の0・1指定を入れていて、7個より多くても少なくてもいけない。
この数が間違っていると「#VALUE!」エラーになる。
また、予め抽出後データのタイトル行を正確に設定しておき(下図のJ2:M2セル)、それをCOUNTIFS式で元データのタイトル行と突き合わせ
=FILTER ( FILTER( $A$2:$G$15 , ($G$2:$G$15=”ドコモ”) , “???”), COUNTIFS($J$2:$M$2,$A$1:$G$1) )
とするなんていうやり方もある。
今回書いたのは、オートフィルタ機能と比べたメリットなどは目立たない基本機能がメインだが、次回はオートフィルタより便利に使い得る利用方法に重点を置いた項目を取り上げていく。
スポンサーリンク
