Genel formül
SUMPRODUCT(--(A:A=A1))
Özet
Önsöz
Bu sinir bozucu derecede uzun bir giriş, ancak bağlam önemli, üzgünüm!
EĞERSAY ile bir aralıktaki çok uzun sayıları (16+ basamak) saymaya çalışırsanız, bazı işlevlerin uzun sayıları işleme biçimindeki bir hata nedeniyle, bu sayılar metin olarak saklansa bile yanlış sonuçlar görebilirsiniz. Aşağıdaki ekranı düşünün. D sütunundaki tüm sayılar yanlış - B sütunundaki her sayı benzersiz olsa da, EĞERSAY tarafından döndürülen sayı bu sayıların yinelenen sayılar olduğunu gösteriyor.
=COUNTIF(data,B5)
Bu sorun, Excel'in sayıları nasıl işlediğiyle ilgilidir. Excel yalnızca 15 önemli basamağı işleyebilir ve Excel'de 15'ten fazla basamak içeren bir sayı girerseniz, sondaki basamakların sessizce sıfıra dönüştürüldüğünü görürsünüz. Yukarıda bahsedilen sayma sorunu bu sınırdan kaynaklanmaktadır.
Normalde, uzun sayıları metin olarak girerek, numarayı tek bir tırnakla ('999999999999999999) başlatarak veya hücre (ler) i girmeden önce Metin olarak biçimlendirerek bu sınırı önleyebilirsiniz. Bir numara üzerinde matematik işlemleri yapmanız gerekmediği sürece, bu iyi bir çözümdür ve kredi kartı numaraları ve seri numaraları gibi şeyler için herhangi bir numara kaybetmeden ekstra uzun sayılar girmenize olanak tanır.
Ancak, 15'ten fazla basamaklı bir sayıyı saymak için COUNTIF kullanmayı denerseniz (metin olarak kaydedilse bile) güvenilmez sonuçlar görebilirsiniz. Bunun nedeni, EĞERSAY'ın dahili olarak uzun değeri işleme sırasında bir noktada tekrar sayıya dönüştürerek yukarıda açıklanan 15 basamak sınırını tetiklemesidir. Tüm rakamlar mevcut olmadığında, bazı numaralar EĞERSAY ile sayıldığında yinelenen sayılar gibi sayılabilir.
Çözüm
Çözümlerden biri, EĞERSAY formülünü TOPLA veya TOPLAÇARPAN kullanan bir formülle değiştirmektir. Gösterilen örnekte, E5'teki formül şuna benzer:
=SUMPRODUCT(--(data=B5))
Formül, "veri" (B5: B9) adlandırılmış aralığını kullanır ve SUMPRODUCT ile her sayı için doğru sayıyı üretir.
Açıklama
İlk olarak, SUMPRODUCT içindeki ifade, "veriler" adlı adlandırılmış aralıktaki tüm değerleri, geçerli satırdaki B sütunundaki değerle karşılaştırır. Bu, DOĞRU / YANLIŞ sonuç dizisiyle sonuçlanır.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Ardından, çift negatif DOĞRU / YANLIŞ değerlerini 1/0 değerlerine zorlar.
=SUMPRODUCT((1;0;0;0;0))
Son olarak SUMPRODUCT, dizideki öğeleri toplar ve sonucu döndürür.
Dizi formül değişkeni
SUMPRODUCT yerine TOPLA işlevini de kullanabilirsiniz, ancak bu bir dizi formülüdür ve control + shift + enter ile girilmelidir:
(=SUM(--(B:B=B5)))
Bu problemle ilgili diğer işlevler
Bunu kendim doğrulamadım, ancak SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ve AVERAGEIFS gibi birkaç işlevde aynı sorun olduğu görülüyor.
