Excel tablo metinlerinde tekrarlanan kelimeleri arama

Excel'de tekrarlanan kelimeleri bulmak için tek bir hücreyi analiz etmek amacıyla =LET işlevini TEXTSPLIT ve FILTER ile birlikte kullanın (Microsoft 365'te mevcuttur) veya tüm sütunları işlemek için Power Query aracını kullanın. Bu yöntemler, manuel inceleme veya makro yazma gerektirmeden metni otomatik olarak normalleştirmenize, ayrı kelimelere bölmenize ve birden fazla kez geçenlerin listesini oluşturmanıza olanak tanır.

Excel'de metin analizi ne zaman gereklidir?

Aynı kelimeleri aramak yalnızca veri temizliği için değil, aynı zamanda belirli iş görevlerini çözmek için de gereklidir:

• SEO denetimi: Meta etiketlerde veya ürün açıklamalarında anahtar kelimelerin aşırı kullanımının tespit edilmesi.
• Veritabanı temizliği: CRM'e aktarmadan önce yorumlardaki veya adreslerdeki rastgele tekrarların kaldırılması.
• Dilbilimsel analiz: Büyük yorum veya belge yığınlarında terimlerin sıklığının hesaplanması.
• Kalite kontrolü: Teknik şartnamelerin tautolojilere karşı kontrol edilmesi.

Tek bir hücre içindeki tekrarlanan kelimelerin analizi

Eğer görev belirli bir metin içinde (örneğin A2 hücresinde) tekrarlanan kelimeleri bulmaksa, en etkili yol modern Excel sürümlerinde dinamik dizilerin kullanılmasıdır.

Eylem algoritması

1. Normalleştirme: "Kelime" ve "kelime," gibi ifadelerin aynı kabul edilmesi için metni küçük harfe çevirin ve noktalama işaretlerini kaldırın.
2. Bölme: Dizgiyi ayrı kelimelerden oluşan bir diziye bölün.
3. Filtreleme: Sıklığı 1'den fazla olan dizi öğelerini sadece bırakın.

Excel 365 için hazır formül

Aşağıdaki formülü, A2 yerine metin içeren hücrenizin adresini yazarak herhangi bir boş hücreye yapıştırın:

=LET(
    raw_text, LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"."," "),","," "),";"," ")),
    words, TEXTSPLIT(raw_text, " "),
    unique_words, UNIQUE(words),
    counts, COUNTIF(words, unique_words),
    FILTER(unique_words, counts > 1, "Tekrar yok")
)

Farklı hücreler veya sütunlar arasındaki ortak kelimelerin aranması

Bir sütunun farklı satırlarında tekrarlanan veya iki sütun arasında kesişen kelimeleri bulmanız gerektiğinde, formüller hantal hale gelir. Burada Excel'e yerleşik Power Query eklentisi daha uygundur. Binlerce satırı daha hızlı işler ve dosyayı hesaplamalarla yormaz.

Power Query'de adım adım talimatlar

1. Veri aralığını seçin ve Veri sekmesine gidin → Tablo/Aralıktan.
2. Power Query düzenleyicisinde metin içeren sütunu seçin.
3. Metni temizleme:
   • Sütun başlığına sağ tıklayın → Dönüştür → Küçük Harf.
   • Noktaları, virgülleri ve diğer noktalama işaretlerini boşluklarla değiştirmek için Değerleri Değiştir özelliğini kullanın (birden fazla değiştirme adımını art arda yapabilirsiniz).
4. Kelimelere bölme:
   • Dönüştür sekmesine gidin → Sütunu Böl → Ayırıcıya Göre.
   • "Boşluk" ayırıcısını seçin ve kelime listesi elde etmek için kritik öneme sahip olan Satırlara Böl kutusunu mutlaka işaretleyin.
5. Gruplama ve sayma:
   • Giriş sekmesine gidin → Gruplandır.
   • Yeni sütun adı: "Sayı".
   • İşlem: Satır Sayısı.
   • Gruplandırma ölçütü: Kelimelerinizi içeren sütun.
6. Sonucu filtreleme:
   • "Sayı" sütunundaki filtreye tıklayın ve yalnızca 1'den büyük değerleri bırakın.

Sonuç, sol tarafta kelimenin ve sağ tarafta tüm kaynak dizide kaç kez geçtiğinin belirtildiği bir tablo olacaktır.

Metin işleme yöntemlerinin karşılaştırılması

Araç seçimi Excel sürümüne ve veri hacmine bağlıdır. Hızlı karar vermek için aşağıdaki yaklaşım karşılaştırması verilmiştir.

Analiz sırasında yapılan yaygın hatalar

Excel'de metin verileriyle çalışırken kullanıcılar genellikle aşağıdaki sorunlarla karşılaşır:

• Büyük/küçük harf duyarlılığının göz ardı edilmesi: "Moskova" ve "moskova" kelimeleri farklı kabul edilir. Her zaman LOWER işlevini veya Power Query'de küçük harfe dönüştürmeyi kullanın.
• Gereksiz boşluklar: Kelimeler arasındaki çift boşluklar boş değerler olarak algılanabilir veya bölmeyi bozabilir. Power Query'de TRIM işlevini veya "Dönüştür" → "Temizle" adımını kullanın.
• Noktalama işaretleri: Noktaları ve virgülleri önceden boşluklarla değiştirmezseniz, kelimeler işaretlere "yapışık" kalır (örneğin, "ev." ve "ev" farklı olacaktır).
• Formüllerle taşma: 50.000 satırlık bir sütuna karmaşık dizi formülleri uygulamaya çalışmak programın donmasına neden olur. Büyük hacimler için yalnızca Power Query kullanın.

SSS

Tekrarlanan kelimeleri metin içinde doğrudan renkle vurgulayabilir miyim? Excel'in standart araçlarıyla bir hücre içindeki metnin bir kısmını farklı renkle vurgulamak mümkün değildir. Koşullu biçimlendirme yalnızca tüm hücre ile çalışır. Görselleştirme için yinelenenlerin listesini bitişik bir sütunda görüntülemeniz gerekecektir.

Excel'imde TEXTSPLIT işlevi yoksa ne yapmalıyım? Power Query kullanın (yöntem yukarıda açıklanmıştır). Bu, karmaşık formüller girmeyi gerektirmediği ve kolayca güncellendiği için düzenli çalışma için daha güvenilir bir yoldur.

Farklı son ekleri olan kelimeleri (örneğin "ev" ve "evler") nasıl dikkate alabilirim? Excel, kelimeleri otomatik olarak kök haline getiremez (lemmatizasyon). Bunun için üçüncü taraf bir eklentiye veya Excel'e yüklemeden önce metnin özel dilbilimsel hizmetlerde ön işlenmesine ihtiyaç duyulacaktır.