Menggunakan Karakter Pengganti dalam Kriteria

Sejauh ini, kriteria Anda mencocokkan nilai secara persis. Namun, data nyata sering kali tidak rapi. Anda mungkin ingin menemukan setiap kode produk yang diawali AB atau setiap jabatan yang memuat kata Manajer.

Karakter pengganti memungkinkan Anda mencocokkan sebagian teks dalam SUMIF, COUNTIF, dan AVERAGEIF. Dua karakter khusus sudah cukup untuk melakukan semuanya: tanda bintang dan tanda tanya.

Hanya ada dua:

• * tanda bintang mencocokkan karakter dalam jumlah berapa pun, termasuk tidak ada karakter
• ? tanda tanya mencocokkan tepat satu karakter

Letakkan karakter tersebut di dalam teks kriteria yang diapit tanda kutip. Semua yang telah Anda pelajari tentang tanda kutip dan tanda ampersand tetap berlaku.

Letakkan tanda bintang di akhir untuk menyatakan akhiran apa pun. Untuk menghitung setiap kode di A2:A10 yang diawali AB, tambahkan tanda bintang setelah AB.

Ini mencocokkan AB, AB1, ABXYZ, dan apa pun yang diawali kedua huruf tersebut. Tanda bintang mewakili bagian apa pun yang mengikutinya.

Letakkan tanda bintang di awal untuk mencocokkan awalan apa pun. Untuk menjumlahkan nilai di B2:B10 saat label di kolom A diakhiri kata Utara, awali kriteria dengan tanda bintang.

Ini mencakup Utara Jauh, Utara Atas, dan Utara saja. Tanda bintang menyerap apa pun yang berada sebelum kata tersebut.

Apit teks dengan tanda bintang di kedua sisinya untuk mencocokkan teks di bagian mana pun dalam sel. Untuk menghitung judul di A2:A10 yang memuat Manajer:

Ini menemukan Manajer Penjualan, Manajer IT, dan Asisten Manajer. Kedua tanda bintang memungkinkan adanya teks apa pun sebelum dan sesudah kata yang dicari.

Tanda tanya mencocokkan tepat satu karakter, tidak lebih dan tidak kurang. Jika kode selalu berupa satu huruf yang diikuti dua digit, hitung kode yang diawali A dengan menggunakan satu tanda tanya untuk setiap karakter yang belum diketahui.

"A??" mencocokkan A12 dan A99, tetapi tidak mencocokkan A1 atau A123 karena panjangnya harus tepat tiga karakter.

Untuk membuat kondisi diawali dari sebuah sel, gabungkan sel tersebut dengan tanda bintang menggunakan tanda ampersand. Jika D1 berisi AB, cocokkan apa pun yang diawali teks tersebut.

& menempelkan tanda bintang ke nilai di D1 sehingga menghasilkan "AB*". Sekarang pencocokan sebagian Anda dikendalikan oleh sel, bukan oleh teks yang ditetapkan langsung.

Trik yang sama dapat membuat pencarian yang memuat teks tertentu. Apit sel dengan tanda bintang di kedua sisinya menggunakan dua tanda ampersand.

Di sini, nilai dalam D1 dibungkus menjadi "*value*", sehingga perubahan pada D1 langsung mengubah hal yang dicari rumus. Inilah inti dari ringkasan sederhana yang mencari saat Anda mengetik.

Bagaimana jika data Anda benar-benar berisi * atau ? dan Anda ingin mencocokkannya secara harfiah? Letakkan tanda tilde ~ di depannya.

Jadi, "~?" menemukan tanda tanya harfiah, sedangkan "~*" menemukan tanda bintang harfiah. Tanda tilde memberi tahu lembar bentang untuk memperlakukan karakter berikutnya sebagai teks biasa.

Satu batasan penting: karakter pengganti bekerja pada teks, bukan angka. Anda tidak dapat menggunakan * untuk mencocokkan sebagian angka seperti harga.

Jika kode disimpan sebagai angka sungguhan, karakter pengganti tidak akan mengenalinya. Ubah kode tersebut menjadi teks terlebih dahulu, atau gunakan operator perbandingan untuk rentang angka. Karakter pengganti dan operator angka menyelesaikan masalah yang berbeda.

Bayangkan kode seperti SALES-01, SALES-02, dan HR-01 di kolom A, serta anggaran di kolom B. Untuk menjumlahkan seluruh departemen Penjualan, cocokkan setiap kode yang diawali SALES dan diikuti apa pun.

Satu SUMIF dengan "SALES*" langsung merekap semua subkode, sehingga Anda tidak perlu mencantumkannya satu per satu. Ganti awalan tersebut untuk merekap departemen lain.

Anda sekarang dapat mencocokkan sebagian teks dalam fungsi matematika IF. Ingat:

• * mencocokkan sejumlah karakter berapa pun; ? mencocokkan tepat satu karakter.
• Gunakan "AB*" untuk teks yang diawali, "*North" untuk teks yang diakhiri, dan "*Manager*" untuk teks yang mengandungnya.
• Gabungkan sel dengan ampersan, seperti pada D1&"*".
• Gunakan tilde agar * atau ? literal diperlakukan sebagai karakter biasa, dan ingat bahwa karakter pengganti hanya berfungsi pada teks.

Dengan demikian, perhitungan bersyarat dengan SUMIF, COUNTIF, dan AVERAGEIF telah selesai.