Genel formül
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Özet
Bir veri kümesindeki tolerans dışı değerleri saymak için SUMPRODUCT ve ABS işlevlerine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, F6'daki formül şöyledir:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
burada "veri", adlandırılmış aralık B5: B14, "hedef" adlandırılmış aralık F4 ve "tolerans", adlandırılmış aralık F5'tir.
Açıklama
Bu formül, sabit bir tolerans aralığında kaç değer olmadığını sayar. Her bir değerin değişimi şu şekilde hesaplanır:
ABS(data-target)
"Data" adlı aralık 10 değer içerdiğinden, F4'te hedef değerin çıkarılması 10 sonuç içeren bir dizi oluşturacaktır:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
ABS işlevi, tüm negatif değerleri pozitif olarak değiştirir:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Bu dizi, F5'teki sabit toleransla karşılaştırılır:
ABS(data-target)>tolerance
Sonuç bir dizi veya DOĞRU YANLIŞ değerleridir ve çift negatif, bunları birler ve sıfırlar olarak değiştirir. SUMPRODUCT içinde son dizi şöyle görünür:
(0;0;1;0;1;0;0;1;0;1)
burada sıfırlar tolerans dahilindeki değerleri ve 1'ler tolerans dışı değerleri temsil eder. SUMPRODUCT daha sonra dizideki öğeleri toplar ve nihai sonuç olan 4'ü döndürür.
Tolerans dahilindeki tüm değerler
Bir veri aralığındaki tüm değerler belirli bir tolerans dahilindeyse "Evet", değilse "Hayır" döndürmek için formülü şu şekilde uyarlayabilirsiniz:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
SUMPRODUCT, sıfırdan büyük bir sayı döndürürse, IF mantıksal testi DOĞRU olarak değerlendirir. Sıfır sonuç YANLIŞ olarak değerlendirilecektir.
Tolerans dışındaki değerleri vurgulayın
Tolerans dışı değerleri, aşağıdaki gibi bir formüle dayalı bir koşullu biçimlendirme kuralıyla vurgulayabilirsiniz:
=ABS(B5-target)>tolerance
Bu sayfada, formüllerle daha fazla koşullu biçimlendirme örneği listelenmektedir.
