スポンサーリンク
前回の記事では、ExcelのFILTER関数について基本的な使い方をメインに扱った。
今回は、オートフィルタ機能を超えられるような、FILTER関数の便利な使い方を取り上げていく。
文字列のフィルタリング
FILTER関数で、文字列に関するフィルタリングは、「*」記号や「?」記号といったワイルドカードは使えないけど、代わりに他の関数を便利に組み合わせることができる。
特定の文字列で始まる・終わる 指定
たとえば、下図でF列の電話番号が「090」で始まるという条件ならば、左側の最初の3文字ということでLEFT関数を使えば良い。
=FILTER( $A$2:$G$15 , LEFT($F$2:$F$15,3)=”090″ , “???” )
次にE列の都道府県が「県」文字で終わるという指定は、RIGHT関数を使えば良い。
=FILTER( $A$2:$G$15 , RIGHT($E$2:$E$15,1)=”県” , “???” )
文字列「を含む」指定→難しい
特定の文字列で「始まる」「終わる」といった指定なら簡単だったが、文字列「を含む」指定はかなり難しい。
それ用の気が利いた関数が普通にあってほしいのだけど、残念ながら無い。
ではどうするか。文字「田」を含むという条件を例にすると、
- 文字「田」が見付かるか(FIND関数やSEARCH関数)。
- 見付からなくてエラーになるのか正常値を返すのか(IFERROR関数)。
という強引な判定方法を使うことになる。
つまり、まず文字「田」が見付かるかを
FIND(“田”,$A$2:$A$15)
として判定する。
これは見付からなかったらエラーが返され、見付かったら「1」とか見付かった位置の数字が返されるので、IFERROR関数を使い
IFERROR( FIND(“田”,$A$2:$A$15) , 0 )>0
とする。FIND関数の結果がエラーなら0，エラーじゃないなら、0より大きい数値が返される(文字列が見付かった位置)という強引な論法だ。
まあこんな難しい組み合わせ関数、自力でイチから書ければそれに越したこともないが、
IFERROR( FIND(“探す文字”, セル範囲 ) , 0 )>0
とでも漢字変換に登録しておけば良いかと思う。
まとめると、A列の中から文字列「田」を含むという条件にするFILTER関数は次のようになる。
=FILTER( $A$2:$G$15 , IFERROR( FIND(“田”,$A$2:$A$15) , 0 ) > 0 , “???” )
(強み)全角半角を区別しない文字列フィルタ
通常のオートフィルタでは、下図の「キャリア」列で半角文字「ﾄﾞｺﾓ」の条件で指定しても、
フィルタ結果に表示されてくれない。通常のオートフィルタは、半角文字と全角文字をきっちり区別するフィルタリングしかしてくれないからだ。
しかしFILTER関数なら、他の関数と組み合わせられる強みがあるので、文字列を半角変換するASC関数と組み合わせ
=FILTER( $A$2:$G$15 , ASC($G$2:$G$15)=”ﾄﾞｺﾓ” , “???” )
とすれば良い。
これで、全角文字と半角文字が混在していようが、強制的に半角に統一させてフィルタリングすることができる。
全角文字に変換する関数としてJIS関数というのがあり、そちらを使っても良いかもしれないが、数字などは半角が良いし半角に変換しておいたほうが良いかと思う。
複数条件をAND条件指定
＜D列の値は「既婚」で且つE列の値は「神奈川県」である＞といったAND条件による指定(しかも複数列に条件指定を跨がらせられる)は、「*」記号を用いて次のように書けば良い。
=FILTER( $A$2:$G$15 , ($D$2:$D$15=”既婚”) * ($E$2:$E$15=”神奈川県”) , “???” )
SUMPRODUCT関数でもこの「*」記号を使うが、論理積というものだ。
配列どうしをAND条件で結合できるのだと、まあなんとなく覚えておけば良いかと。
もちろん、掛け合わせられる条件は2つだけじゃなくてもっと増やせるし、先述したASC関数との組み合わせなども、下図のように使える。
複数条件をOR条件指定
複数条件指定において、AND条件なら「*」記号だけど、OR条件については「+」記号を使えば良い。
これは論理和という。
テーブルと積極的に組み合わせる
先程も扱った例で、
=FILTER( $A$2:$G$15 , RIGHT($E$2:$E$15,1)=”県” , “???” )
というようになっている数式。
これは、元データであるA1:G15のセル範囲をコマンド Ctrl+T でテーブルとして設定し、「TBL」というテーブル名に設定すると、
=FILTER( TBL , RIGHT(TBL[都道府県],1)=”県” , “???” )
というように、直感的で分かりやすい数式になってくれる。
FILTER関数に限ったことではないが、テーブルとの組み合わせを有効活用していきたい。
作業用セルを上手く使う
ここまで色々と見てきたFILTER関数の使い方は、関数の引数を全部まともに書いているので、FILTER関数が便利とか何だかんだ言っても手間が掛かる。
FILTER関数の強みを本当に発揮できるのは、そうやって関数の中身の引数をいちいち書かず、作業用セルを上手く使って、検索条件を次々に変えていく使い方をする場合だろうと思う。
例えば下図では、元データであるA1:C15のセル範囲を「会社TBL」というテーブル名にして、H1セルに入力した値を元に会社名を検索する式として
=FILTER( 会社TBL , IFERROR( FIND($H$1,ASC(会社TBL[会社名]) ),0)>0 , “???” )
と入れている。
IFERROR関数・FIND関数・ASC関数と組み合わせることで、H1セルに半角文字で入れた値を「含む」検索を、半角全角の区別なく実施できるわけだ。
そしてH1セルの値を直接書き換えるだけで良いというのがメリット。
下図では、H1セルの値を「ﾘｰｽ」から「ｵｰﾄ」へ書き換えているが、それだけで瞬時にフィルタ結果が変化してくれる。
オートフィルタ機能では、文字列「を含む」検索は、ショートカットキーを使っても
Altキー → 下キー → Fキー → Aキー
という面倒な操作が必要だ。
それに比べて、このFILTER関数と作業セルを用いた方法なら、作業セルを書き換えるだけで良くなり楽になる。
ぜひ試してみると良いだろう。
スポンサーリンク
