Excel’de bir aralıkta belirli kelimeleri sayma

Bu makale, Excel’de bir hücre aralığında belirli bir kelimenin kaç kez geçtiğini saymak için kullanılan formülü açıklar.

Excel’de bir hücre aralığında belirli kelimeleri nasıl sayabilirsiniz?

Genel formül

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

Bağımsız değişkenler

Bu formülü nasıl kullanırsınız?

Diyelim ki “tool” kelimesinin B5:B8 aralığında kaç kez tekrarlandığını saymak istiyorsunuz; bunu aşağıdaki gibi yapabilirsiniz.

Boş bir hücre seçin, aşağıdaki formülü içine girin ve ardından Enter tuşuna basın.

İpuçları:Formülü doğrudan seçili hücreye kopyalayıp yapıştırabilirsiniz.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

Notlar:

• 1. Lütfen B5:B8 aralığını ve “tool” kelimesini kendi ihtiyaçlarınıza uygun şekilde güncelleyin;
• 2. Kelime bulunamazsa 0 değerini döndürür;
• 3. Formül, büyük/küçük harf duyarlılığına sahiptir;
• 4. Bir hücre aralığında belirli bir kelimeyi sayarken büyük/küçük harf farkını göz ardı etmek istiyorsanız, aşağıdaki formülde gösterildiği gibi SUBSTITUTE fonksiyonuna UPPER fonksiyonunu ekleyin:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

Bu formül nasıl işler?

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): LEN fonksiyonu, B5:B8 aralığındaki her hücre için toplam Dizi Uzunluğuna Göre Filtrele değerini hesaplar ve {47;23;23;15} şeklinde bir dizi sayı olarak döndürür;
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): Burada SUBSTITUTE fonksiyonu, B5:B8 aralığındaki her hücreden “tool” kelimesini kaldırır ve ardından LEN fonksiyonu, “tool” kelimesi çıkarıldıktan sonra kalan metnin uzunluğunu hesaplar. Sonuç olarak {35;19;19;15} şeklinde bir dizi döndürür;
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): Formülün bu kısmı artık {47;23;23;15}–{35;19;19;15} haline gelir. Çıkarma işlemi sonucunda ise {12;4;4;0} elde edilir.
• 4. LEN("tool"): LEN fonksiyonu burada “tool” kelimesinin uzunluğunu hesaplar ve 4 sonucunu döndürür;
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): Yukarıda belirtildiği gibi, bu kısım {12;4;4;0}/4 şeklinde görüntülenir. Dizideki her sayıdan 4 değeri çıkarıldığında sonuç {3;1;1;0} olur;
• 6. SUMPRODUCT({3;1;1;0}): SUMPRODUCT fonksiyonu, dizideki sayıları çarparak toplar ve B5:B8 aralığında “tool” kelimesinin toplam sayısını verir.

İlgili fonksiyonlar

İlgili formüller