いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Excel)FILTER関数でワイルドカードを使って抽出する

 ExcelのFILTER関数では基本的に抽出条件としてワイルドカードを使うことはできませんが、適当な関数と組み合わせてなんとか実現する方法を紹介します。

  • FILTER関数とCOUNTIFS関数を組み合わせて、ワイルドカードを使った抽出をすることができます。

手順

f:id:accs2014:20201129015244p:plain:right:w600

 画像ではB,C列にメニューと価格が並んでいます。
 E3セルには次のような式を入力して、「*丼」というワイルドカードに一致する、つまり「丼」の字で終わるメニューとその価格を抽出しています。

 E3セル

=FILTER(B3:C9,COUNTIFS(B3:B9,B3:B9,B3:B9,"*丼"))

 FILTER関数の第2引数は直観的に「COUNTIF(B3:B9,"*丼")」としたくなるところですが、COUNTIF関数が返すのは1つの数値だけです。つまりB3:B9の範囲に「丼」で終わる文字列がいくつあるか(2)だけを返しますので、結果的にエラーになってしまいます。
 しかしCOUNTIFS関数を使って「COUNTIFS(B3:B9,B3:B9,B3:B9,"*丼")」とすれば、(最初の条件が一見冗長ですが)各行ごとにカウントした結果({0;1;0;0;1;0;0})が返りますので、行単位の抽出が成立するというわけです。
 なお、COUNTIFS関数の後に「>0」などと付けた方が親切ですが、結果に影響しないのでここでは省略しています。
 

f:id:accs2014:20201129015248p:plain:right:w600

 こちらでは「カ????」というワイルドカードを使って、「カ」の字で始まる5文字のメニューとその価格を抽出しています。

 E3セル

=FILTER(B3:C9,COUNTIFS(B3:B9,B3:B9,B3:B9,"カ????"))