Genel formül
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Özet
Belirli bir kelimenin (veya herhangi bir alt dizenin) bir hücre aralığında kaç kez göründüğünü saymak için, YEDEK, UZUNLUK ve TOPLA işlevlerine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, C11'deki formül şöyledir:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Not: Bu sayfadaki formül, bir aralıktaki bir sözcüğün maddelerini sayar. Örneğin, bir hücre bir kelimenin iki örneğini içeriyorsa, toplam sayıya 2 katkıda bulunur. Yalnızca belirli bir kelimeyi içeren hücreleri saymak istiyorsanız, EĞERSAY işlevine dayalı bu basit formüle bakın.
Açıklama
Formülün genel versiyonunda rng , kontrol edilecek aralığı temsil eder ve txt , sayılacak kelime veya alt dizedir.
Gösterilen örnekte, B5: B8 kontrol edilecek aralıktır ve C2 sayılacak metni (kelime veya alt dize) içerir.
Aralıktaki her hücre için, SUBSTITUTE, alt dizeyi orijinal metinden kaldırır ve UZUNLUK, alt dize olmadan metnin uzunluğunu hesaplar. Bu sayı daha sonra orijinal metnin uzunluğundan çıkarılır. Sonuç, SUBSTITUTE tarafından kaldırılan karakterlerin sayısıdır.
Daha sonra, kaldırılan karakter sayısı alt dizenin uzunluğuna bölünür. Dolayısıyla, bir alt dize veya kelime 5 karakter uzunluğundaysa ve orijinal metinden kaldırıldıktan sonra 10 karakter eksikse, alt dizenin / kelimenin orijinal metinde iki kez göründüğünü biliyoruz.
Yukarıdaki hesaplama SUMPRODUCT işlevinde olduğundan, sonuç, aralıktaki her hücre için bir sayı içeren bir dizidir. Bu sayılar, her bir hücredeki alt dizenin oluşum sayısını temsil eder. Bu örnek için dizi şuna benzer: (1; 1; 0; 1)
Son olarak, SUMPRODUCT, hücre aralığındaki alt dizenin toplam oluşumlarını elde etmek için dizideki tüm öğeleri toplar.
Vakayı yok saymak
SUBSTITUTE büyük / küçük harfe duyarlı bir işlevdir, bu nedenle bir değişiklik çalıştırırken büyük / küçük harfe eşleşir. Bir sözcük veya alt dizenin hem büyük hem de küçük harf oluşumlarını saymanız gerekiyorsa, değiştirmeyi çalıştırmadan önce metni büyük harfe dönüştürmek için SUBSTITUTE içindeki BÜYÜK HARF işlevini kullanın:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
