Karakter Wildcard Excel: Panduan dengan Contoh

Karakter wildcard Excel adalah simbol khusus yang membantu pengguna melakukan pencocokan sebagian dan operasi data yang fleksibel di set data berukuran besar. Meskipun Excel tidak mendukung fungsionalitas regex penuh, Anda dapat menggunakan wildcard dengan struktur rumus yang kreatif (fungsi seperti SEARCH(), TEXT(), dan ISNUMBER()).

Membangun kompetensi dengan karakter wildcard menjadi lebih mudah ketika Anda memiliki dasar Excel yang kuat. Kursus Introduction to Excel kami memandu Anda melalui keterampilan esensial termasuk navigasi antarmuka, referensi sel, dan rumus dasar—menciptakan fondasi yang Anda perlukan untuk menerapkan teknik wildcard yang dibahas dalam panduan ini secara efektif.

Apa Itu Karakter Wildcard di Excel?

Wildcard adalah simbol khusus yang menggantikan karakter dalam string teks, memungkinkan Anda mencari pola alih-alih kecocokan persis. Excel mengenali tiga karakter wildcard, masing-masing memiliki tujuan berbeda dalam pencocokan pola dan operasi teks.

Asterisk (*)

Asterisk (*) mencocokkan sejumlah karakter berapa pun, termasuk tidak ada karakter. Misalnya, App* akan cocok dengan "Apple", "Application", "App", dan "Approach".

Wildcard ini berguna ketika Anda mengetahui awal string teks tetapi ingin menangkap semua variasi setelahnya.

Tanda tanya (?)

Tanda tanya (?) mencocokkan tepat satu karakter. Menggunakan B?t akan cocok dengan "Bat," "Bet," "Bit," dan "But," tetapi tidak dengan "Boat" atau "Boot."

Presisi ini ideal untuk situasi ketika Anda mengetahui panjang dan sebagian besar karakter dari teks target Anda.

Tilde (~)

Tilde (~) berfungsi sebagai karakter escape, memungkinkan Anda mencari asterisk atau tanda tanya sebenarnya dalam data Anda. Saat Anda perlu menemukan teks yang secara literal berisi karakter * atau ?, menempatkan tilde sebelum karakter tersebut memberi tahu Excel untuk memperlakukannya sebagai karakter biasa, bukan wildcard.

Sebagai contoh, ~*Sale akan mencocokkan “*Sale” secara literal alih-alih memperlakukan asterisk sebagai wildcard.

Menggunakan Wildcard dalam Rumus Excel

Beberapa fungsi Excel mendukung karakter wildcard, sehingga bermanfaat untuk perhitungan bersyarat dan operasi pencarian. Fungsi yang paling sering digunakan dan menerima wildcard mencakup COUNTIF(), COUNTIFS(), SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), SEARCH(), VLOOKUP(), HLOOKUP(), XLOOKUP(), MATCH(), dan XMATCH().

Masing-masing fungsi ini dapat memanfaatkan wildcard untuk melakukan pencocokan pola yang fleksibel, memungkinkan Anda bekerja dengan kecocokan parsial dan menangani variasi pada set data Anda secara efektif.

COUNTIF() dengan wildcard

Fungsi COUNTIF() menjadi sangat berguna saat digabungkan dengan wildcard untuk menghitung sel yang cocok dengan pola tertentu. Berikut contoh dasarnya:

=COUNTIF(A2:A12, "*AA*")Didukung oleh

Rumus ini menghitung semua sel dalam rentang A2:A12 yang berisi "AA" di mana pun di dalam teks. Asterisk sebelum dan sesudah "AA" memberi tahu Excel untuk mencocokkan karakter apa pun sebelum atau sesudah urutan tersebut.

Untuk kriteria dinamis, Anda dapat mereferensikan sel yang berisi pola wildcard Anda:

=COUNTIF(A2:A12, E1)Didukung oleh

Dalam hal ini, sel E1 mungkin berisi "*AA*" atau pola wildcard lainnya. Pendekatan ini memungkinkan Anda mengubah kriteria pencarian tanpa memodifikasi rumus itu sendiri, sehingga spreadsheet lebih fleksibel dan mudah digunakan.

VLOOKUP() dan XLOOKUP() dengan wildcard

Fungsi lookup dapat menggunakan wildcard untuk kecocokan parsial, yang membantu ketika nilai pencarian Anda bukan kecocokan persis. Berikut contoh untuk kedua pendekatan:

=VLOOKUP("John*", A:C, 2, FALSE)Didukung oleh

Rumus VLOOKUP() ini mencari entri apa pun di kolom A yang dimulai dengan "John" dan mengembalikan nilai yang sesuai dari kolom ke-2. Parameter FALSE memastikan fungsi menggunakan mode kecocokan persis, yang diperlukan untuk fungsionalitas wildcard.

Untuk XLOOKUP(), sintaksnya sedikit berbeda:

=XLOOKUP("*Adams", A:A, C:C, "Not found", 2)Didukung oleh

Parameter mode kecocokan (2) memberi tahu XLOOKUP() untuk menggunakan pencocokan wildcard. Rumus ini menemukan entri apa pun yang berakhiran "Adams" dan mengembalikan nilai yang sesuai dari kolom C. Rumus akan mengembalikan “Not found” jika tidak ada kecocokan. 

IF() dengan solusi alternatif wildcard

Meskipun fungsi IF() tidak secara langsung mendukung wildcard, Anda dapat mencapai fungsionalitas serupa dengan menggabungkan IF() dengan COUNTIF():

=IF(COUNTIF(A1, "Amy*"), "Yes", "No")Didukung oleh

Rumus ini memeriksa apakah sel A1 berisi teks yang dimulai dengan "Amy" dan mengembalikan "Yes" jika ya, "No" jika tidak. Fungsi COUNTIF() menangani pencocokan wildcard, sementara IF() memproses hasilnya.

SUMIF() dengan wildcard

Fungsi SUMIF() dapat menjumlahkan nilai berdasarkan kriteria wildcard, sehingga berguna untuk mengagregasi data dengan pola serupa:

=SUMIF(A:A, "*a*", C:C)Didukung oleh

Rumus ini menjumlahkan semua nilai di kolom C di mana sel yang sesuai di kolom A berisi huruf "a" di mana pun dalam teks. Pola wildcard "a" menangkap teks apa pun yang mengandung "a" tanpa memedulikan posisinya.

Menggunakan Wildcard di Alat Excel

Wildcard tidak hanya untuk rumus dan dapat digunakan di beberapa alat Excel untuk mencari, mencocokkan, dan memfilter data. Fitur bawaan ini membuat wildcard dapat diakses bahkan saat Anda tidak menulis rumus yang kompleks.

Wildcard di Find and Replace

Dialog Find and Replace Excel mendukung wildcard untuk menemukan pola variabel dalam data Anda. Untuk mengaktifkan fungsionalitas wildcard, Anda perlu mencentang opsi "Use wildcards" di dialog Find and Replace.

Anda dapat menggunakan asterisk (*) untuk menemukan pola dengan panjang yang bervariasi. Misalnya, mencari "Product*X" akan menemukan "Product-A-X," "Product-Premium-X," dan "Product-X." Demikian pula, tanda tanya (?) membantu menemukan pola dengan posisi karakter tertentu, seperti "20??" untuk menemukan angka empat digit apa pun yang dimulai dengan "20."

Saat Anda perlu mencari asterisk atau tanda tanya sebenarnya dalam data Anda, gunakan tilde (~) sebagai karakter escape. Mencari "~*Sale" akan menemukan sel yang berisi teks literal "*Sale" alih-alih memperlakukan asterisk sebagai wildcard.

Wildcard dalam fitur filter dan pencarian

Kemampuan pemfilteran Excel mendukung wildcard, sangat berguna dalam filter teks dengan opsi seperti "contains," "begins with," dan "ends with." Filter ini secara otomatis menafsirkan karakter wildcard saat Anda menyertakannya dalam kriteria.

Misalnya, menerapkan filter dengan "Jo*" pada opsi "begins with" akan menampilkan semua nama yang dimulai dengan "Jo" - termasuk "John," "Joseph," "Joan," dan "Jonathan." Pendekatan ini cocok untuk eksplorasi data cepat tanpa menulis rumus.

Namun, fitur pencarian bawaan memiliki beberapa keterbatasan dibandingkan opsi filter lanjutan. Meskipun pola wildcard dasar bekerja efektif, pemfilteran multi-kriteria yang kompleks mungkin memerlukan penggabungan wildcard dengan rumus kustom atau menggunakan fitur Advanced Filter Excel untuk pencocokan pola yang lebih canggih.

Bekerja dengan angka dan wildcard

Menggunakan wildcard dengan angka memerlukan perhatian khusus karena Excel memperlakukan operasi wildcard sebagai fungsi berbasis teks. Saat Anda menerapkan pola wildcard pada data numerik, Excel secara otomatis mengonversi angka menjadi teks, yang dapat menyebabkan perilaku pencocokan yang tidak terduga.

Untuk pencocokan angka yang efektif dengan wildcard, Anda memiliki beberapa opsi solusi. Anda dapat mengonversi angka menjadi teks menggunakan fungsi TEXT(), fitur Text to Columns, atau rumus tertentu yang dirancang untuk tujuan ini.

Pendekatan praktis melibatkan penggabungan fungsi SEARCH(), ISNUMBER(), dan SUMPRODUCT():

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))Didukung oleh

Rumus ini menghitung berapa banyak sel dalam rentang B2:B12 yang berisi digit "4" di mana pun dalam angkanya. Untuk menjumlahkan nilai ketika angka mengandung pola tertentu, Anda dapat memperluas pendekatan ini:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4*", TEXT(B2:B12,"0")))), C2:C12)Didukung oleh

Rumus ini menjumlahkan nilai di kolom C di mana angka yang sesuai di kolom B mengandung “4” diikuti digit apa pun. 

Untuk pencocokan tanggal, gunakan fungsi TEXT() untuk mengonversi tanggal menjadi pola teks yang dapat dicari:

=TEXT(C2:C12, "mmddyyyy")Didukung oleh

Ini mengonversi tanggal menjadi format teks konsisten yang dapat diproses wildcard secara efektif. Anda kemudian dapat menerapkan pola wildcard untuk menemukan komponen tanggal tertentu: 

=COUNTIF(TEXT(C2:C12,"mmddyyyy"),"*0525*")Didukung oleh

Rumus ini menghitung tanggal yang mengandung “0525” (25 Mei pada tahun berapa pun). 

Skenario tertentu melibatkan angka yang disimpan sebagai teks, seperti kode ZIP, ID produk, atau nomor referensi. Dalam kasus ini, wildcard dapat mencocokkannya langsung sebagai string teks. Misalnya, memfilter kode dengan pola "123*" akan cocok dengan "12345," "123-AB," dan "123XYZ," sementara "??-45" akan cocok dengan "AB-45," "12-45," dan "XY-45."

Keterbatasan dan Hal yang Mengejutkan pada Wildcard

Tidak semua fungsi Excel mendukung fungsionalitas wildcard, yang dapat menimbulkan kebingungan saat Anda berharap rumus tertentu bekerja dengan pencocokan pola. Fungsi IF(), misalnya, tidak secara langsung menerima wildcard, sehingga memerlukan solusi seperti menggabungkannya dengan COUNTIF() seperti ditunjukkan sebelumnya.

Wildcard hanya berfungsi dengan teks dalam rumus, artinya nilai numerik harus dikonversi ke teks sebelum operasi wildcard dapat memprosesnya secara efektif. Persyaratan konversi teks ini dapat menyebabkan hasil yang tak terduga jika Anda tidak menyadari keterbatasan ini.

Berhati-hatilah dengan positif palsu dalam rumus lookup saat menggunakan pola wildcard yang luas. Misalnya, VLOOKUP("*Smith*", ...) mungkin mengembalikan kecocokan yang tidak diinginkan seperti "Smithson," "Blacksmith," atau "Goldsmith" ketika Anda hanya menginginkan "Smith." Perilaku ini memerlukan perancangan pola yang cermat agar tidak menangkap hasil yang tidak diinginkan.

Kejutan umum lainnya terjadi pada spasi di awal atau akhir dalam data Anda. Wildcard memperlakukan spasi sebagai karakter, sehingga "John*" tidak akan cocok dengan " John" (dengan spasi di depan). Pembersihan data menjadi penting saat bekerja dengan wildcard untuk memastikan hasil yang konsisten.

Selain itu, pencocokan wildcard tidak peka huruf besar/kecil secara bawaan. Artinya, pola seperti "apple*" akan cocok dengan "Apple," "APPLE," dan "ApPlE." Perilaku ini nyaman untuk pencarian umum tetapi dapat menyebabkan kecocokan yang tidak diinginkan jika Anda memerlukan pencocokan peka huruf besar/kecil. Untuk mencapai kecocokan peka huruf besar/kecil, pertimbangkan menggunakan fungsi seperti FIND() yang dikombinasikan dengan ISNUMBER(). 

Tips Saat Bekerja dengan Wildcard di Excel

Wildcard unggul dalam menggabungkan data yang dicocokkan secara samar (fuzzy), sangat berguna saat berurusan dengan ejaan yang tidak konsisten atau variasi entri data. Misalnya, Anda mungkin memiliki "Homel" dan "Gomel" dalam set data yang mewakili lokasi yang sama. Menggunakan pola wildcard seperti "*omel" dapat membantu mengidentifikasi dan mengonsolidasikan entri serupa ini.

Format nomor telepon sering bervariasi di berbagai set data, dengan prefiks atau kode negara yang berbeda. Anda dapat menggunakan wildcard untuk menstandardisasi ketidakselarasan ini. Misalnya, Anda dapat menggunakan Find and Replace untuk mengonversi nomor format internasional Inggris menjadi format domestik:

Find: 44??????????
Replace with: 0Didukung oleh

Pola ini mencocokkan nomor Inggris mana pun yang dimulai dengan "44" diikuti sepuluh digit, secara efektif mengonversi nomor seperti "447123456789" menjadi "07123456789" untuk pemformatan domestik yang konsisten.

Saat Anda perlu mengecualikan hasil tertentu dari kecocokan wildcard, gabungkan beberapa fungsi SUMIF() atau COUNTIF(). Teknik ini bekerja dengan baik untuk mengurangkan kecocokan yang tidak diinginkan:

=SUMIF(..., "00123H*") - SUMIF(..., "00123H*-SP")Didukung oleh

Rumus ini menjumlahkan semua nilai yang cocok dengan "00123H*" tetapi mengecualikan yang berakhiran "-SP," memberi Anda kontrol yang lebih presisi atas pencocokan wildcard.

Perubahan pemformatan bekerja efektif bersama wildcard menggunakan fungsi seperti REPLACE(), SUBSTITUTE(), atau TEXT(). Fungsi-fungsi ini dapat membersihkan dan menstandardisasi data Anda sebelum atau sesudah menerapkan operasi wildcard, memastikan hasil yang konsisten di seluruh analisis Anda.

Saat bekerja dengan kode produk atau nomor referensi, wildcard membantu mengidentifikasi pola dan pengecualian. Anda dapat menggunakan *-TEMP untuk menemukan semua kode sementara, atau ??-??-???? untuk menemukan entri yang mengikuti struktur format tertentu, sehingga validasi dan pembersihan data menjadi lebih efisien.

Kesimpulan

Baik Anda mengonsolidasikan entri serupa, menstandarkan format nomor telepon, atau memfilter set data besar untuk pola tertentu, wildcard menawarkan alternatif praktis untuk pencocokan persis. Wildcard menjembatani kesenjangan antara pencarian teks dasar dan fungsionalitas regex yang lebih kompleks, membuat pencocokan pola dapat diakses dalam lingkungan Excel yang familiar.

Untuk memperluas kemampuan Excel Anda melampaui operasi wildcard, jalur keterampilan Excel Fundamentals kami menawarkan jalur komprehensif yang mencakup teknik persiapan data, analisis, dan visualisasi. Bagi Anda yang siap menerapkan keterampilan ini dalam konteks analitis, kursus Data Analysis in Excel kami menyediakan pengalaman langsung dengan PivotTable dan fungsi logika tingkat lanjut yang melengkapi keterampilan pencocokan pola yang telah Anda pelajari di sini.