Wildcard Excel: *, ? dan ~ di COUNTIF dan VLOOKUP

Excel punya tiga karakter wildcard untuk kriteria dan pencarian: * cocok dengan berapa pun jumlah karakter (termasuk tidak ada sama sekali), ? cocok dengan tepat satu karakter, dan ~ mengubah * atau ? berikutnya kembali menjadi karakter biasa. =COUNTIF(A2:A7,"*apple*") menghitung sel yang mengandung apple di posisi mana saja.

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

• *apple* mengandung apple: 4 kecocokan, karena Pineapple juga dihitung.
• apple* diawali apple: hanya Apple juice dan Apples. COUNTIF tidak membedakan huruf besar dan kecil.
• *juice diakhiri juice.
• ????? panjangnya tepat lima karakter. Tidak ada produk di sini yang panjangnya lima karakter, jadi hasilnya 0; ketik Peach di A6 dan hitungannya menjadi 1.
• *e diakhiri e.

Ketik polamu sendiri di kolom C, misalnya *an* atau P*, dan hitungannya langsung diperbarui.

Pencocokan sebagian di VLOOKUP dan XLOOKUP

VLOOKUP menerima wildcard dalam mode pencocokan persis (FALSE sebagai argumen terakhir). Gabungkan wildcard dengan nilainya di dalam rumus, jadi D2 cukup diisi huruf-huruf awalnya saja:

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Kedua rumus menemukan Pineapple. Ubah D2 menjadi juice: pola VLOOKUP juice* butuh teks yang diawali juice dan menghasilkan #N/A, sedangkan pola XLOOKUP *juice* menemukan produk pertama yang mengandungnya, Apple juice. Seperti semua pencocokan persis, pencarian dengan wildcard mengembalikan baris pertama yang cocok, jadi buat polanya cukup spesifik.

XLOOKUP hanya memperlakukan * dan ? sebagai wildcard jika argumen kelimanya, match_mode, bernilai 2. Tanpa itu, XLOOKUP mencari tanda bintangnya apa adanya. MATCH menerima wildcard dengan match_type 0, dan XMATCH dengan match_mode 2, sama seperti XLOOKUP. Lihat VLOOKUP untuk argumen-argumen lainnya.

RumusReset

D2fx

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Di D2, hitung produk yang namanya mengandung phone di posisi mana saja.

PeriksaTampilkan jawaban

Menjumlahkan dan merata-ratakan dengan wildcard

Setiap fungsi yang menerima kriteria membacanya dengan cara yang sama, jadi pola yang sama bekerja di SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS dan MINIFS:

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

North* menjumlahkan setiap wilayah yang diawali North, termasuk North itu sendiri, karena * juga cocok dengan tidak ada karakter sama sekali. ????? menjumlahkan wilayah yang panjangnya tepat lima karakter: South dan North.

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Di E2, jumlahkan penjualan semua wilayah yang namanya diakhiri West.

PeriksaTampilkan jawaban

Mencari tanda bintang atau tanda tanya yang sebenarnya dengan ~

Untuk menghitung teks yang benar-benar berisi * atau ?, taruh tilde di depannya. Tilde-nya sendiri ditulis ~~.

RumusReset

C2fx

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

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

C2 menghitung sel yang berisi tanda bintang yang sebenarnya (hanya A2), C3 menghitung sel yang berisi tanda tanya. C4 menunjukkan sisi lainnya: "*" saja cocok dengan teks apa pun, jadi rumus itu menghitung setiap sel berisi teks, di sini 4. Angka dan sel kosong dilewati, itulah sebabnya COUNTIF(range,"*") adalah cara yang biasa dipakai untuk menghitung sel berisi teks.

Fungsi mana saja yang menerima wildcard

SEARCH menerima wildcard di dalam rumus: =SEARCH("b?d","a bad day") mengembalikan 3 di Excel (FIND dan SEARCH punya detailnya). Untuk FILTER, pakai ISNUMBER(SEARCH(...)) sebagai kondisinya, bukan sebuah pola.

Kesalahan umum: wildcard setelah =

Operator = tidak pernah membaca wildcard: =A2="*apple*" menanyakan apakah A2 berisi tujuh karakter *apple*. Di Excel, dengan Green apple di A2:

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

Taruh ujiannya di COUNTIF saja, yang mengembalikan 1 atau 0 untuk satu sel, atau pakai SEARCH:

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

IF memperlakukan angka 1 dari COUNTIF sebagai TRUE dan 0 sebagai FALSE. Versi SEARCH sama sekali tidak butuh wildcard, karena SEARCH sudah mencari teksnya di posisi mana pun di dalam sel.