Bagaimana Anda membuat pencarian fuzzy di excel?

Posting ini mengeksplorasi fungsi pencarian Excel, pencocokan perkiraan, pencarian fuzzy, dan pencocokan tepat. Fungsi pencarian Excel bawaan, seperti VLOOKUP, luar biasa. Ketika diimplementasikan dengan cara yang benar untuk proyek-proyek khusus atau dalam buku kerja penggunaan berulang, mereka dapat menghemat banyak waktu. Fungsi VLOOKUP sendiri telah menghemat banyak waktu dalam buku kerja penggunaan berulang saya. Namun, fungsi VLOOKUP, mirip dengan fungsi pencarian Excel lainnya seperti HLOOKUP dan MATCH, dibuat untuk melakukan pencocokan tepat atau pencarian rentang. Keduanya sangat berbeda dari pencocokan perkiraan atau pencarian fuzzy. Posting ini membahas detail dari ide-ide ini, dan menunjukkan cara melakukan pencarian fuzzy di Excel 2010 dan yang lebih baru

*** POST BLOG YANG DIPERBARUI. FUZZY MATCH TERSEDIA DI POWER QUERY ***

Memahami Fungsi Pencarian Bawaan

Fungsi pencarian Excel bawaan, seperti VLOOKUP, HLOOKUP, dan MATCH, berfungsi dengan logika pencarian serupa. Untuk menyederhanakan postingan ini, kami akan menggunakan satu saja sebagai contoh. Karena fungsi VLOOKUP mungkin adalah fungsi pencarian yang paling sering digunakan dan paling familiar, kita akan menggunakannya saat kita mengeksplorasi ide-ide ini

Ide dasar dari fungsi pencarian Excel adalah untuk mencari nilai dalam daftar. Misalnya, kita dapat meminta Excel untuk menemukan "Perusahaan ABC" dalam daftar nama pelanggan. Itu adalah ide dasarnya, tetapi penerapan fungsi pencarian sangat banyak dan penerapannya bisa menjadi sangat canggih dan kuat

Untuk posting ini, saya ingin membagi tugas yang dilakukan fungsi pencarian menjadi dua langkah. Saya sebut langkah pertama cocok, dan langkah kedua kembali. Pada langkah pertama, pencocokan, Excel harus menemukan nilai pencocokan. Anda memberi tahu Excel nilai untuk ditemukan, seperti "Perusahaan ABC" dan Anda memberi tahu Excel di mana mencarinya, seperti dalam rentang sel. Anda meminta Excel untuk menemukan nilai pencarian dalam rentang pencarian

Langkah kedua, return, adalah hasil dari fungsi. Artinya, berapa nilai fungsi yang harus dikembalikan ke sel. Beberapa fungsi pencarian, seperti fungsi MATCH, memberi tahu Excel untuk mengembalikan nomor posisi. Fungsi pencarian lainnya, seperti fungsi VLOOKUP, memberi tahu Excel untuk mengembalikan nilai terkait. Jadi, berdasarkan fungsi pencarian mana yang Anda pilih, dan nilai argumen fungsi mana yang Anda masukkan, Excel tahu apa yang harus dikembalikan setelah menemukan kecocokannya. Sejauh ini bagus?

Mari kita lakukan contoh cepat pada saat ini

Saya ingin menemukan nama pelanggan tertentu "Perusahaan ABC" dalam daftar pelanggan, dan jika ditemukan, saya ingin Excel mengembalikan id pelanggan yang ditemukan di kolom berikutnya

Saya akan menggunakan fungsi VLOOKUP, dan saya akan memintanya untuk menemukan "Perusahaan ABC" di Tabel Pelanggan, dan mengembalikan ID. Dengan asumsi nama pelanggan dimasukkan di C7, dan pelanggan disimpan di Tabel bernama Tabel1, maka fungsi berikut akan melakukan triknya

=VLOOKUP(C7, Tabel1, 2, SALAH)

Di mana

• C7 adalah nilai untuk menemukan
• Tabel1 adalah rentang pencarian
• 2 adalah kolom yang memiliki nilai yang ingin kita kembalikan
• SALAH berarti kami tidak melakukan pencarian rentang

Fungsi ini dimasukkan di C8 pada tangkapan layar di bawah

Seperti yang Anda lihat, ID AC100 berhasil dikembalikan ke sel rumus C8. Dan itu sobat adalah ide dasar dari fungsi VLOOKUP. Temukan nilai (kecocokan) dan hitung hasilnya (pengembalian)

Penting untuk dicatat bahwa nilai pencarian, string teks "Perusahaan ABC" harus ditemukan dalam rentang pencarian. Kecuali untuk case (atas dan bawah), kedua nilai harus sama persis. “Perusahaan ABC” tidak akan cocok dengan “Perusahaan ABC, Inc. ”, “ABC Co”, atau “Perusahaan ABC   “. Tanpa spasi di depan, tanpa spasi di belakang, tanpa singkatan atau karakter tambahan. Mereka harus sama. Ini disebut pencocokan tepat. Jika nilainya tidak sama, fungsinya tidak akan cocok, dan Anda akan mendapatkan kesalahan, seperti yang ditunjukkan pada tangkapan layar di bawah ini

Sekarang kita telah membahas dasar-dasarnya, sekarang saatnya untuk mengeksplorasi argumen keempat VLOOKUP

Kebenaran tentang Argumen Keempat VLOOKUP

Argumen keempat dari fungsi VLOOKUP secara resmi diberi nama. range_lookup. Ini adalah argumen boolean, artinya Anda dapat memberikannya nilai BENAR atau SALAH, atau representasi lain dari BENAR atau SALAH. Hal yang cenderung menyesatkan pengguna Excel adalah deskripsi yang digunakan Microsoft untuk opsi tersebut. Excel mendeskripsikan nilai TRUE sebagai “Perkiraan Cocok” dan FALSE sebagai “Pencocokan Persis. Deskripsi yang lebih jelas akan menjadi sesuatu seperti BENAR "Anda melakukan pencarian rentang" dan SALAH "Anda tidak melakukan pencarian rentang" tetapi bagaimanapun juga, deskripsinya adalah apa adanya

Saat Anda memilih BENAR (Perkiraan Cocok), Anda tidak meminta Excel untuk mencocokkan nilai yang kira-kira sama satu sama lain. Deskripsi Perkiraan Kecocokan cenderung menyiratkan bahwa fungsi tersebut akan cocok dengan "Perusahaan ABC" dan "Perusahaan ABC, Inc. ” karena mereka kira-kira memiliki nama yang sama. Dalam beberapa kasus dan dalam beberapa kumpulan data, ide ini akan berhasil. Tapi ide ini tidak berfungsi di semua kasus, dan karenanya, tidak bisa diandalkan di buku kerja kami. Misalnya, pada tangkapan layar di bawah, fungsi tidak menemukan kecocokan antara "Perusahaan ABC" dan "Perusahaan ABC, Inc. ” sebagaimana dibuktikan dengan ID yang salah dikembalikan di C8

Namun dalam tangkapan layar berikut, fungsi tersebut menemukan kecocokan antara "Perusahaan ABC" dan "ABC Co" yang dibuktikan dengan ID yang diharapkan dikembalikan ke C8

Cara fungsi benar-benar berfungsi saat TRUE dipilih adalah ini. itu menelusuri daftar baris demi baris, dan akhirnya berhenti pada baris yang lebih kecil dari nilainya dan di mana baris berikutnya lebih besar dari nilainya. Inilah mengapa rentang pencarian harus diurutkan dalam urutan menaik agar fungsi mengembalikan hasil yang akurat saat argumen keempat BENAR

Ide ini bisa membingungkan saat memikirkan string teks, tetapi lebih masuk akal saat memikirkan angka. Misalnya, saat mencoba mencari tarif komisi yang tepat berdasarkan nilai penjualan. Dalam hal ini, Anda ingin melakukan pencarian rentang. Anda ingin mencari nilai dari dalam rentang. Ini diilustrasikan pada tangkapan layar di bawah ini

Fungsi berjalan ke bawah baris demi baris mencoba menentukan baris mana yang harus dihentikan. Ini berlanjut hingga menemukan baris yang lebih besar dari nilai pencarian, lalu berhenti di baris sebelumnya. Itu berhenti di baris yang lebih kecil dari nilai, dan di mana baris berikutnya lebih besar dari nilai pencarian. Ini cukup mudah dipahami saat memikirkan angka, tetapi bisa lebih sulit untuk divisualisasikan saat memikirkan string teks. Namun, kunci untuk memahami argumen fungsi ini adalah menyadari bahwa logikanya identik saat beroperasi pada string teks dan angka. Inilah mengapa “Perusahaan ABC” tidak cocok dengan “Perusahaan ABC, Inc. ”, karena “ABC Company Inc. ” lebih besar dari Perusahaan ABC. Inilah mengapa “ABC Company” akan cocok dengan “ABC Co”, karena “ABC Co” kurang dari “ABC Company. ”  Seperti yang Anda lihat, ini bukan yang kami pikirkan saat memikirkan perkiraan kecocokan

Apa itu Fuzzy Lookup alias Approximate Match

Perkiraan kecocokan, bagi kami, berarti dua string teks yang kira-kira sama, tetapi belum tentu identik, harus cocok. Misalnya, “Perusahaan ABC” harus cocok dengan “Perusahaan ABC, Inc. ,” “ABC Co,” dan “Perusahaan ABC. ”  Kami menganggap pencocokan perkiraan sebagai semacam fuzzy, di mana beberapa karakter cocok tetapi tidak semua

Ide dari pencarian fuzzy adalah bahwa nilainya tidak cocok, tidak identik. Tapi mereka kemungkinan cocok, ada kemungkinan mereka cocok. Mereka mungkin mewakili entitas dasar yang sama

Sekarang setelah kita menyadari bahwa fungsi VLOOKUP tidak benar-benar menjalankan logika pencocokan perkiraan, setidaknya, tidak seperti yang kita inginkan, apa yang harus kita lakukan?

Menambahkan

Saat Anda menabrak dinding, putarlah. Karena fungsi pencarian bawaan tidak menjalankan logika fuzzy saat melakukan pencocokan, kami mencapai batasan bawaan Excel. Microsoft telah menawarkan cara untuk mengatasi batasan ini dengan menawarkan add-in gratis

Microsoft menawarkan add-in gratis yang memungkinkan Excel melakukan pencarian fuzzy. Ini disebut "Add-In Pencarian Fuzzy untuk Excel" dan tersedia pada saat posting ini di tautan di bawah ini

http. // www. microsoft. com/en-us/download/details. aspx?id=15011

Setelah terinstal, add-in ini melakukan pencarian fuzzy. Itu tidak mengubah perilaku fungsi pencarian bawaan mana pun. Itu tidak mengaktifkan fungsi VLOOKUP Anda untuk melakukan pencarian fuzzy. Ini adalah add-in yang pada dasarnya memproses dua daftar dan menghitung probabilitas kecocokan

Anda menentukan dua tabel, dan di dalam setiap tabel kolom yang akan diperiksa. Pada dasarnya, Anda menentukan langkah pertama yang cocok. Anda kemudian menentukan langkah kedua dengan mengidentifikasi kolom mana dari tabel yang harus disertakan dalam hasil. Anda juga dapat menentukan ambang probabilitas. Anda menekan pergi, dan add-in melakukan tugasnya, lalu menampilkan tabel yang dihasilkan mulai dari sel aktif. Ini pada dasarnya menghasilkan laporan statis berdasarkan pengaturan yang Anda pilih

Berikut adalah tangkapan layar keluaran, yang menunjukkan bahwa hasil tersebut berhasil mencocokkan “Perusahaan ABC” dan “Perusahaan ABC, Inc. ” di kumpulan data yang sama yang menyebabkan fungsi VLOOKUP kami gagal

Untuk informasi lebih lanjut tentang add-in pencarian fuzzy, dan detail lebih lanjut tentang cara menggunakannya, silakan kunjungi tautan Microsoft di atas. Add-in dilengkapi dengan instruksi, contoh file Excel, dan file pdf dengan latar belakang dan logika yang digunakannya untuk melakukan keajaibannya. Itu juga dilengkapi dengan lisensi, jadi, Anda pasti ingin membaca ketentuan lisensi di Ketentuan Lisensi. dokumen rtf disertakan dengan unduhan

Ada beberapa ilmu komputer dan matematika yang sangat menarik yang bekerja di belakang layar, termasuk kesamaan Jaccard, tokenisasi catatan, dan transformasi. Matematika yang cukup berat di sana. Terima kasih Microsoft Research untuk add-in ini

Apakah ada kecocokan fuzzy di Excel?