Обща формула
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Обобщение
За да преброите стойности, които са извън допустимото отклонение в набор от данни, можете да използвате формула, базирана на функциите SUMPRODUCT и ABS. В показания пример формулата във F6 е:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
където "data" е наименованият диапазон B5: B14, "target" е наименуваният диапазон F4, а "tolerance" е наименуваният диапазон F5.
Обяснение
Тази формула отчита колко стойности не са в обхвата на фиксиран толеранс. Вариацията на всяка стойност се изчислява с това:
ABS(data-target)
Тъй като посоченият диапазон "данни" съдържа 10 стойности, изваждането на целевата стойност във F4 ще създаде масив с 10 резултата:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Функцията ABS променя отрицателните стойности на положителни:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Този масив се сравнява с фиксирания толеранс във F5:
ABS(data-target)>tolerance
Резултатът е масив или TRUE FALSE стойности и двойното отрицателно ги променя на единици и нули. Вътре в SUMPRODUCT крайният масив изглежда така:
(0;0;1;0;1;0;0;1;0;1)
където нулите представляват стойности в рамките на допустимото отклонение, а 1s представляват стойности извън допустимото отклонение. След това SUMPRODUCT сумира елементите в масива и връща окончателен резултат, 4.
Всички стойности в рамките на толерантност
За да върнете "Да", ако всички стойности в диапазон от данни са в рамките на даден толеранс, и "Не", ако не, можете да адаптирате формулата по следния начин:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Ако SUMPRODUCT върне число, по-голямо от нула, IF ще оцени логическия тест като TRUE. Нулев резултат ще бъде оценен като FALSE.
Подчертайте стойностите извън толерантността
Можете да подчертаете стойности извън допустимото отклонение с правило за условно форматиране въз основа на формула като тази:
=ABS(B5-target)>tolerance
Тази страница изброява още примери за условно форматиране с формули.
