Arkadaşlarımdan biri bir sağlık analiz şirketinde çalışıyor. Excel'de verilerle çalışırken karşılaştığı bazı gerçek yaşam sorunları hakkında benimle sık sık bağlantı kuruyor.
Çoğu zaman, diğer okuyucularım için de yararlı olabileceğinden, sorgularını bu sitede Excel eğitimlerine dönüştürüyorum.
Bu da böyle bir öğreticidir.
Arkadaşım geçen hafta beni aşağıdaki sorunla aradı:
Excel'de bir sütunda adres verileri var ve adresin içinde yinelenen metin dizeleri (kelimeler) olduğu hücreleri belirlemek/filtrelemek istiyorum.
İçinde yinelenen bir metin dizesi olan hücreleri filtrelemek istediği benzer veri kümesi (kırmızı oklu olanlar):
Şimdi bunu zorlaştıran şey, bu verilerde tutarlılık olmaması. Bu, satış temsilcileri tarafından manuel olarak oluşturulmuş bir veri kümesi derlemesi olduğundan, veri kümesinde farklılıklar olabilir.
Bunu düşün:
- Herhangi bir metin dizesi bu veri kümesinde tekrarlanabilir. Örneğin, bölgenin adı veya şehrin adı veya her ikisi olabilir.
- Sözcükler bir boşluk karakteri ile ayrılmaktadır ve şehir adının altı kelimeden sonra mı yoksa sekiz kelimeden sonra mı geleceği konusunda bir tutarlılık yoktur.
- Bunun gibi binlerce kayıt vardır ve ihtiyaç, yinelenen metin dizelerinin olduğu kayıtları filtrelemektir.
Birçok seçeneği değerlendirdikten sonra (metinden sütunlara ve formüller gibi), sonunda bunu 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 bir sözcük varsa bana DOĞRU, tekrar olmaması durumunda 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; yoksa YANLIŞ döndürür.
Ayrıca, sadece üç karakterden fazla kelimeleri saymak için yaratılmıştır.
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 İş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 bunun için 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 O zaman j = 0 İçin SkipA'ya Git - i'ye - 1 StringtoAnalyze(j) = StringtoAnalyze(i) ise IdDuplicates = "TRUE" SkipB'ye Git Sonraki j SkipA: Sonraki i IdDuplicates = "YANLIŞ" SkipB: Fonksiyonu Bitir
Bu koda daha iyi bir yaklaşım önerdiği için Walter'a teşekkürler!
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ışabilmesi için onu Excel'in arka ucuna yerleştirmeniz gerekir.
VBA kodunu arka uca yerleştirme adımları aşağıdadır:
- Geliştirici sekmesine gidin.
- Visual Basic'e tıklayın (ALT + F11 klavye kısayolunu da kullanabilirsiniz)
- Açılan VB Düzenleyici arka ucunda, çalışma kitabı nesnelerinden herhangi birine sağ tıklayın.
- "Ekle"ye gidin ve "Modül"e tıklayın. Bu, çalışma kitabı için 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 başvurusu olan tek bir argüman alır.
İş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 yinelenen metin dizeleri olan tüm hücreleri elde etmek için DOĞRU ile olanları filtreleyebilirsiniz.
Not: Kodu yalnızca üç karakterden uzun kelimeleri dikkate almak için oluşturdum. Bu, metin dizesinde 1, 2 veya 3 karakter uzunluğundaki sözcükler (12 A, K G M veya L D A gibi) varsa, kopyaları sayarken bunların yok sayılmasını sağlar. İsterseniz, bunu kodda kolayca değiştirebilirsiniz.
Bu işlev yalnızca, kodu modüle kopyaladığınız çalışma kitabında kullanılabilir. Bunun diğer çalışma kitaplarında da olmasını istiyorsanız, bu kodu bu çalışma kitaplarına kopyalayıp yapıştırmanız gerekir. Alternatif olarak, bir eklenti de oluşturabilirsiniz (bu işlevi sisteminizdeki tüm çalışma kitaplarında kullanılabilir hale getiren etkinleştirme).
Ayrıca, bu çalışma kitabını .xlsm uzantısına kaydetmeyi unutmayın (çünkü içinde bir makro kodu vardır).
