Excelのワイルドカード: COUNTIFやVLOOKUPでの*、?、~

Excelには、条件や検索で使えるワイルドカード文字が3つあります。*は任意の数の文字(0文字を含む)に、?はちょうど1文字に一致し、~は次の*や?を普通の文字に戻します。=COUNTIF(A2:A7,"*apple*")は、どこかにappleを含むセルを数えます。

数式リセット

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

• *apple*はappleを含むもので、Pineappleも数えるので4件一致します。
• apple*はappleで始まるもので、Apple juiceとApplesだけです。COUNTIFは大文字と小文字を区別しません。
• *juiceはjuiceで終わるものです。
• ?????はちょうど5文字のものです。5文字の商品はないので0ですが、A6にPeachと入力すると1になります。
• *eはeで終わるものです。

C列に*an*やP*のような自分のパターンを入力すると、件数が更新されます。

VLOOKUPとXLOOKUPでの部分一致

VLOOKUPは完全一致モード(最後の引数がFALSE)でワイルドカードを受け付けます。数式の中で値にワイルドカードをつなぐので、D2には最初の数文字だけを入力します：

数式リセット

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

どちらの数式もPineappleを見つけます。D2をjuiceに変えてみてください。VLOOKUPのパターンjuice*はjuiceで始まる文字列を求めるので#N/Aを返し、XLOOKUPのパターン*juice*はそれを含む最初の商品、Apple juiceを見つけます。ほかの完全一致と同じく、ワイルドカードの検索は条件に合う最初の行を返すので、パターンは十分に具体的にしてください。

XLOOKUPが*と?をワイルドカードとして扱うのは、5つ目の引数match_mode(一致モード)が2のときだけです。指定しないと、アスタリスクそのものを探します。MATCHはmatch_typeが0のとき、XMATCHはXLOOKUPと同じくmatch_modeが2のときにワイルドカードを受け付けます。そのほかの引数についてはVLOOKUPを参照してください。

数式リセット

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: D2で、名前のどこかにphoneを含む商品の数を数えてください。

答え合わせ答えを見る

ワイルドカードで合計・平均する

条件を受け取る関数はどれも同じように条件を読むので、同じパターンがSUMIF、SUMIFS、AVERAGEIF、AVERAGEIFS、MAXIFS、MINIFSでも使えます：

数式リセット

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

*は0文字にも一致するので、North*はNorth自体も含めてNorthで始まるすべての地域を合計します。?????はちょうど5文字の地域、SouthとNorthを合計します。

数式リセット

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: E2で、名前がWestで終わるすべての地域の売上を合計してください。

答え合わせ答えを見る

~で本物のアスタリスクや疑問符を探す

本当の*や?を含む文字列を数えるには、その前にチルダを付けます。チルダ自体は~~と書きます。

数式リセット

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

C2はアスタリスクそのものを含むセル(A2だけ)を、C3は疑問符を含むセルを数えます。C4は逆の例です。"*"だけだと任意の文字列に一致するので、文字列のセルをすべて数え、ここでは4になります。数値と空のセルは数えないので、COUNTIF(range,"*")は文字列のセルを数える定番の方法です。

ワイルドカードが使える関数

SEARCHは数式の中でワイルドカードを受け付けます。Excelでは=SEARCH("b?d","a bad day")は3を返します(詳しくはFINDとSEARCH)。FILTERでは、パターンの代わりにISNUMBER(SEARCH(...))を条件にします。

よくある間違い: =の後のワイルドカード

=演算子はワイルドカードを読みません。=A2="*apple*"は、A2が7文字の*apple*かどうかを尋ねています。ExcelでA2がGreen appleのとき：

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

代わりに、1つのセルに対して1か0を返すCOUNTIFで判定するか、SEARCHを使います：

数式リセット

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

IFはCOUNTIFの1をTRUE、0をFALSEとして扱います。SEARCHはもともとセルのどこにある文字列でも探すので、SEARCHの方法ではワイルドカードはまったく要りません。