Excel tablolarında tekrarlanan kelimelerin analizi
Excel'de tekrarlanan kelimeleri bulmak ve saymak için öncelikle metni normalleştirmek (tek bir büyük/küçük harf durumuna getirmek ve noktalama işaretlerini kaldırmak), ardından onu ayrı kelimelere bölmek ve benzersiz değerleri sayma işlevlerini uygulamak gerekir. Modern sürümlerdeki (Excel 365/2021) en hızlı yöntem, TEXTSPLIT, UNIQUE ve COUNTIF işlevlerinin kombinasyonunu kullanmaktır. Büyük veri kümeleri için Power Query eklentisini kullanmak daha etkilidir.
Metin verilerinin hazırlanması
Tekrarları aramadan önce metnin tek bir standart haline getirilmesi gerekir. Aksi takdirde "Kelime", "kelime" ve "kelime." gibi ifadeler farklı değerler olarak kabul edilir.
Hazırlık süreci üç aşamadan oluşur:
- Küçük harfe dönüştürme. "Kelime" ve "kelime" ifadelerinin aynı hale gelmesi için
=LOWER(hücre)(veya İngilizce sürümde=LOWER) işlevini kullanın. - Noktalama işaretlerinin kaldırılması. Nokta, virgül ve diğer sembollerin kelimelere "yapışmaması" için bunları boşluklarla veya boş değerlerle değiştirin.
- Fazla boşlukların giderilmesi.
=TRIM()(veya İngilizce sürümde=TRIM) işlevi çift boşlukları ve gereksiz girintileri kaldırır.
Karmaşık noktalama işareti temizliği için birkaç SUBSTITUTE (YERİNE KOY) işlevini iç içe yerleştirebilir veya temiz parçaları ayırmak için TEXTAFTER/TEXTBEFORE işlevlerini kullanabilirsiniz.
Yöntem 1: Dinamik dizi formülleri (Excel 365/2021)
Bu yöntem, karmaşık raporlar oluşturmadan tek bir hücredeki veya küçük bir sütundaki metni hızlıca analiz etmek için idealdir.
Adım 1: Metnin kelimelere bölünmesi
=TEXTSPLIT(metin; " ") (=TEXTSPLIT) işlevini kullanın. Bu işlev, bir metin dizgisini dikey bir ayrı kelimeler dizisine dönüştürür.
Örnek: A2 hücresinde temizlenmiş metin varsa, =TEXTSPLIT(A2; " ") formülü kelimelerin listesini bitişik hücrelere yazdırır.
Adım 2: Tekrarların sayılması
Kelimelerin listesini aldıktan sonra (diyelim ki E2:E20 aralığını kapladı):
- Benzersiz kelimelerin listesini alın:
=UNIQUE(kelime_aralığı). - Benzersiz listeyle yan yana her kelimenin sıklığını sayın:
=COUNTIF(kelime_aralığı; benzersiz_kelime_hücresi).
Adım 3: Tekrarların filtrelenmesi
Yalnızca birden fazla kez geçen kelimeleri görmek için sonucu FILTER işlevine sarın:
=FILTER(benzersiz_liste; sıklık_listesi > 1)
Dinamik dizi işlevleri, formülün altında ve sağında yeterli boş hücre olmasını gerektirir. Aksi takdirde #SPILL! (#YAYILMA!) hatası oluşur.
Yöntem 2: Büyük veri hacimleri için Power Query
Binlerce satır metni analiz etmek gerekiyorsa, formüller dosyanın çalışma hızını yavaşlatabilir. Power Query eklentisi bunu daha hızlı halleder ve süreci otomatikleştirir.
- Metin içeren sütunu seçin ve Veri → Tablo/Aralıktan sekmesine gidin.
- Power Query düzenleyicisinde metin içeren sütunu seçin.
- Giriş sekmesinde Sütunu Böl → Ayırıcıya Göre seçeneğine tıklayın. "Boşluk" seçeneğini belirleyin ve "Satırlara Böl" seçeneğini işaretleyin (bu, sonraki gruplama işlemi için kritik öneme sahiptir).
- Dönüştür → Biçim → Küçük Harf menüsü aracılığıyla verileri küçük harfe dönüştürün.
- Fazla boşlukları ve boş satırları kaldırın (değere göre filtreleme).
- Dönüştür → Gruplandır sekmesine gidin.
- Yeni sütun adı: "Sayı".
- İşlem: Satır Sayısı.
- Gruplandırma ölçütü: Kelimeleri içeren sütun.
- "Sayı" sütununu filtreleyerek yalnızca 1'den büyük değerleri bırakın.
- Hazır raporu yeni bir sayfaya yüklemek için Kapat ve Yükle düğmesine tıklayın.
İşleme yöntemlerinin karşılaştırılması
| Kriter | Formüller (TEXTSPLIT/UNIQUE) | Power Query |
|---|---|---|
| Kurulum karmaşıklığı | Düşük (tek bir formül) | Orta (adım yapılandırması gerekir) |
| Performans | Binlerce satırda düşer | Yüksek, büyük veriler için optimize edilmiştir |
| Veri güncelleme | Hücre değiştiğinde otomatik | "Yenile" düğmesine basılmasını gerektirir |
| Temizleme esnekliği | Formül uzunluğuyla sınırlı | Yüksek (sembol kaldırma işlemi kolaydır) |
| Excel Sürümü | Yalnızca 365 ve 2021+ | 2010 ve üzeri sürümlerde mevcut |
Analiz sırasında yapılan yaygın hatalar
- Büyük/küçük harf duyarlılığı: "Moskova" ve "moskova" kelimeleri farklı kabul edilir. Analizden önce mutlaka küçük harfe dönüştürme işlemi uygulayın.
- Noktalama işaretleri: Nokta ve virgüllerin boşlukla değiştirilmemesi durumunda "ev" ve "ev," kelimeleri farklı varlıklar olarak kabul edilir.
- Fazla boşluklar: Çift boşluklar, kelime dizisinde boş öğeler oluşturarak istatistikleri bozar. Mutlaka boşluk sıkıştırma işlevini uygulayın.
- Durdu kelimeleri: Sıklık analizinde genellikle edat ve bağlaçlar ("ve", "içinde", "üzerinde") hariç tutulur; bunlar teknik olarak en sık tekrarlanan kelimelerdir ancak anlamsal yük taşımazlar.
SSS
Tüm bir sütundaki tekrarlanan kelimeleri aynı anda sayabilir miyim?
Evet. Formüllerde, =CONCAT() veya =TEXTJOIN() kullanarak sütundaki tüm hücreleri tek bir uzun dizgede birleştirin ve ardından bölme işlemini uygulayın. Power Query'de bu işlem aralık içe aktarılırken otomatik olarak yapılır.
Kelimeler sadece boşluklarla değil, satır sonlarıyla da ayrılıyorsa ne yapmalıyım? Formüllerde, bölme işleminden önce satır sonu karakterini (CHAR(10)) boşlukla değiştirin. Power Query'de ayırıcıya göre bölme işlemi sırasında "Satır Sonu" seçeneğini belirleyebilir veya gelişmiş düzenleyicide düzenli ifadeler kullanabilirsiniz.
Kısa kelimeleri (örneğin 1-2 harfli) nasıl yok sayabilirim?
Sıklık tablosunu elde ettikten sonra kelime uzunluğuna göre bir filtre ekleyin. Formüllerde, FILTER işlevi içinde =LEN(kelime) > 2 koşulunu kullanın. Power Query'de sütun uzunluğuna göre bir filtreleme adımı ekleyin.