Benim bir arkadaşımExcel'de verilerle çalışırken karşılaştığı bazı gerçek dünya sorunları hakkında onunla sık sık iletişim halindeyim.
Çoğu zaman sorgusunu bu sitedeki bir Excel eğitimine çeviriyorum çünkü diğer okuyucularıma da yardımcı olabilir.
Bu da böyle bir öğretici.
Arkadaşım geçen hafta beni şu sorularla aradı:
Excel'de bir sütunda adres verilerim var ve adreste tekrarlanan metin dizeleri (kelimeler) içeren hücreleri belirlemek/filtrelemek istiyorum.
İşte, içinde tekrarlanan metin dizeleri (kırmızı oklu hücreler) bulunan hücreleri filtrelemek istediği benzer bir veri kümesi:
Bunu şimdi zorlaştıran şey, verilerin tutarlı olmamasıdır.Bu, satış temsilcileri tarafından manuel olarak oluşturulan veri kümelerinin bir derlemesi olduğundan, veri kümelerinde farklılıklar olabilir.
bunu düşün:
- Bu veri setinde herhangi bir metin dizisi tekrarlanabilir.Örneğin, bir bölge adı veya bir şehir adı veya her ikisi olabilir.
- Sözcükler boşluk karakterleriyle ayrılmıştır ve şehir adının altı mı yoksa sekiz karakterden sonra mı olduğu konusunda bir tutarlılık yoktur.
- Bu tür binlerce kayıt vardır ve yinelenen metin dizelerine sahip olanları filtrelemeniz gerekir.
Metinden sütunlara ve formüller gibi birçok seçeneği düşündükten sonra, sonunda işi yapmak için VBA kullanmaya karar verdim.
Bu yüzden, bu hücreleri analiz etmek için özel bir VBA işlevi ('IdDuplicate') oluşturdum ve metin dizesinde yinelenen sözcükler varsa bana DOĞRU, yinelenen sözcükler yoksa YANLIŞ (aşağıda gösterildiği gibi):
Bu özel işlev, metin dizesindeki her sözcüğü analiz eder ve metinde kaç kez geçtiğini kontrol eder.Sayı 1'den büyükse DOĞRU döndürür;Aksi takdirde YANLIŞ döndürün.
Ayrıca, yalnızca üçten fazla karakter içeren kelimeleri saymak için oluşturuldu.
DOĞRU/YANLIŞ verilerine sahip olduğumda, DOĞRU olan tüm kayıtları kolayca filtreleyebilirim.
Şimdi size bunu Excel'de nasıl yapacağınızı göstereyim.
Özel işlev için VBA kodu
Bu, VBA'da özel bir işlev yaratılarak yapılır.Bu işlev daha sonra Excel'deki diğer herhangi bir çalışma sayfası işlevi olarak kullanılabilir.
İşte VBA kodu:
İşlev IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Adım -1 Eğer Len(StringtoAnalyze(i)) < minWordLen Sonra J = 0 İçin SkipA'ya Git - i'ye - 1 Eğer StringtoAnalyze(j) = StringtoAnalyze(i) ise IdDuplicates = "TRUE" SkipB'ye Git Eğer Sonraki j SkipA: Sonraki i IdDuplicates = "YANLIŞ" SkipB: Fonksiyonu Bitir
Bu VBA kodu nasıl kullanılır?
Artık VBA kodunuz olduğuna göre, normal bir çalışma sayfası işlevi olarak çalışması için onu Excel'in arka ucuna koymanız gerekir.
VBA kodunu arka uca yerleştirme adımları şunlardır:
- Geliştirici sekmesine gidin.
- Visual Basic'e tıklayın (ALT+F11 klavye kısayolunu da kullanabilirsiniz)
- Açık VB düzenleyici arka ucunda, herhangi bir çalışma kitabı nesnesine sağ tıklayın.
- "Ekle"ye gidin ve "Modül"e tıklayın.Bu, çalışma kitabının modül nesnesini ekleyecektir.
- Modül kodu penceresinde, yukarıda belirtilen VBA kodunu kopyalayıp yapıştırın.
Arka uçta VBA koduna sahip olduğunuzda, diğer normal çalışma sayfası işlevi olarak 'IdDuplicates' işlevini kullanabilirsiniz.
Bu işlev, metnin bulunduğu hücrenin hücre referansı olan bir parametre alır.
Bu işlevin sonucu DOĞRU (içinde yinelenen sözcükler varsa) veya YANLIŞ (yinelenen sözcükler yoksa).Bu DOĞRU/YANLIŞ listesine sahip olduğunuzda, içinde tekrarlanan metin dizeleri olan tüm hücreleri elde etmek için DOĞRU olanları filtreleyebilirsiniz.
Not: Oluşturduğum kod sadece üç karakterden uzun kelimeleri hesaba katmak içindir.Bu, metin dizisinde 1, 2 veya 3 karakter uzunluğunda kelimeler varsa (örn. 12 A, KGM veya LDA), yinelemeleri sayarken bu kelimelerin yok sayılmasını sağlar.Gerekirse bunu kodda kolayca değiştirebilirsiniz.
Bu özellik yalnızca modüldeki kodu kopyaladığınız çalışma kitabında mevcuttur.Bu kodun diğer çalışma kitaplarında da bulunmasını istiyorsanız, bu kodu kopyalayıp bu çalışma kitaplarına yapıştırmanız gerekir.Alternatif olarak, bir eklenti oluşturabilirsiniz (bunu etkinleştirmek, özelliği sisteminizdeki tüm çalışma kitaplarında kullanılabilir hale getirir).
Ayrıca, bu çalışma kitabını bir .xlsm uzantısıyla kaydetmeyi unutmayın (çünkü makro kodu içerir).
