seorang kawan sayaSaya sering berhubung dengannya tentang beberapa masalah dunia sebenar yang dia hadapi semasa bekerja dengan data dalam Excel.
Banyak kali saya menterjemah pertanyaannya ke dalam tutorial Excel di laman web ini kerana ia mungkin berguna kepada pembaca saya yang lain juga.
Ini adalah salah satu tutorial sedemikian juga.
Rakan saya menelefon saya minggu lepas dengan soalan berikut:
Saya mempunyai data alamat dalam lajur dalam Excel dan saya ingin mengenal pasti/menapis sel yang mengandungi rentetan teks berulang (perkataan) dalam alamat.
Berikut ialah set data yang serupa di mana dia ingin menapis sel (sel dengan anak panah merah) yang mempunyai rentetan teks berulang di dalamnya:
Apa yang menjadikan ini sukar sekarang ialah data tidak konsisten.Memandangkan ini ialah kompilasi set data yang dibuat secara manual oleh wakil jualan, mungkin terdapat percanggahan dalam set data.
cuba pertimbangkan:
- Sebarang rentetan teks boleh diulang dalam set data ini.Contohnya, ia boleh menjadi nama wilayah atau nama bandar, atau kedua-duanya.
- Perkataan-perkataan itu dipisahkan oleh aksara ruang, dan tidak ada konsistensi sama ada nama bandar selepas enam atau lapan aksara.
- Terdapat beribu-ribu rekod sedemikian dan perlu menapis rekod yang mempunyai sebarang rentetan teks berulang.
Selepas mempertimbangkan banyak pilihan seperti teks ke lajur dan formula, saya akhirnya memutuskan untuk menggunakan VBA untuk melakukan kerja itu.
Jadi saya mencipta fungsi VBA tersuai ('IdDuplicate') untuk menghuraikan sel-sel ini dan memberi saya BENAR jika terdapat perkataan pendua dalam rentetan teks dan FALSE jika tiada pendua (seperti ditunjukkan di bawah):
Fungsi tersuai ini menganalisis setiap perkataan dalam rentetan teks dan menyemak berapa kali ia berlaku dalam teks.Mengembalikan TRUE jika kiraan lebih daripada 1;Jika tidak, kembalikan FALSE.
Selain itu, ia dicipta untuk hanya mengira perkataan dengan lebih daripada tiga aksara.
Setelah saya mempunyai data BENAR/PALSU, saya boleh menapis semua rekod yang BENAR dengan mudah.
Sekarang izinkan saya menunjukkan kepada anda cara melakukan ini dalam Excel.
Kod VBA untuk fungsi tersuai
Ini dilakukan dengan mencipta fungsi tersuai dalam VBA.Fungsi ini kemudiannya boleh digunakan sebagai mana-mana fungsi lembaran kerja lain dalam Excel.
Inilah kod VBAnya:
Fungsi IdDuplicates(rng Sebagai Julat) Sebagai String Dim StringtoAnalyze Sebagai Varian Dim i Sebagai Integer Dim j Sebagai Integer Const minWordLen Sebagai Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") Untuk i = UBound(StringtoAnalyze) Langkah -0 Jika Len(StringtoAnalyze(i)) < minWordLen Kemudian PergiTo SkipA Untuk j = 1 Hingga i - 0 Jika StringtoAnalyze(j) = StringtoAnalyze(i) Kemudian IdDuplicates = "TRUE" PergiTo SkipB Tamat Jika Seterusnya j SkipA: Seterusnya i IdDuplicates = "FALSE" SkipB: End Function
Cara menggunakan kod VBA ini
Memandangkan anda mempunyai kod VBA, anda perlu meletakkannya di bahagian belakang Excel supaya ia boleh berfungsi sebagai fungsi lembaran kerja biasa.
Berikut ialah langkah-langkah untuk meletakkan kod VBA di bahagian belakang:
- Pergi ke tab pembangun.
- Klik pada Visual Basic (anda juga boleh menggunakan pintasan papan kekunci ALT+F11)
- Dalam bahagian belakang editor VB terbuka, klik kanan pada mana-mana objek buku kerja.
- Pergi ke "Sisipkan" dan klik "Modul".Ini akan memasukkan objek modul buku kerja.
- Dalam tetingkap kod modul, salin dan tampal kod VBA yang dinyatakan di atas.
Sebaik sahaja anda mempunyai kod VBA di bahagian belakang, anda boleh menggunakan fungsi - 'IdDuplicates' sebagai mana-mana fungsi lembaran kerja biasa yang lain.
Fungsi ini mengambil satu parameter, rujukan sel sel tempat anda mempunyai teks.
Hasil daripada fungsi ini adalah BENAR (jika terdapat perkataan pendua di dalamnya) atau SALAH (jika tiada pendua).Sebaik sahaja anda mempunyai senarai TRUE/FALSE ini, anda boleh menapis yang mempunyai TRUE untuk mendapatkan semua sel yang mempunyai rentetan teks berulang di dalamnya.
Nota: Kod yang saya buat hanya untuk mengambil kira perkataan yang lebih panjang daripada tiga aksara.Ini memastikan bahawa jika terdapat perkataan dalam rentetan teks yang panjangnya 1, 2 atau 3 aksara (cth 12 A, KGM atau LDA), perkataan tersebut diabaikan semasa mengira pendua.Anda boleh menukar ini dalam kod dengan mudah jika perlu.
Ciri ini hanya tersedia dalam buku kerja tempat anda menyalin kod dalam modul.Jika anda mahu kod ini tersedia dalam buku kerja lain juga, anda perlu menyalin dan menampal kod ini ke dalam buku kerja tersebut.Sebagai alternatif, anda boleh membuat tambahan (mendayakan ini akan menjadikan ciri tersedia dalam semua buku kerja pada sistem anda).
Selain itu, ingat untuk menyimpan buku kerja ini dengan sambungan .xlsm (kerana ia mengandungi kod makro).
