Общая формула
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Резюме
Для подсчета значений, выходящих за пределы допуска в наборе данных, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и ABS. В показанном примере формула в F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
где «данные» - именованный диапазон B5: B14, «цель» - именованный диапазон F4, а «допуск» - именованный диапазон F5.
Объяснение
Эта формула подсчитывает, сколько значений выходит за пределы фиксированного допуска. Вариация каждого значения рассчитывается следующим образом:
ABS(data-target)
Поскольку именованный диапазон «data» содержит 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
Результатом является массив или значения ИСТИНА ЛОЖЬ, а двойное отрицание меняет их на единицы и нули. Внутри СУММПРОИЗВ окончательный массив выглядит так:
(0;0;1;0;1;0;0;1;0;1)
где нули представляют значения в пределах допуска, а единицы представляют значения вне допуска. Затем СУММПРОИЗВ суммирует элементы в массиве и возвращает окончательный результат 4.
Все значения в пределах допуска
Чтобы вернуть «Да», если все значения в диапазоне данных находятся в пределах заданного допуска, и «Нет», если нет, вы можете адаптировать формулу следующим образом:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Если SUMPRODUCT возвращает любое число больше нуля, IF оценивает логический тест как TRUE. Нулевой результат будет оценен как ЛОЖЬ.
Выделить значения за пределами допуска
Вы можете выделить значения за пределами допуска с помощью правила условного форматирования, основанного на такой формуле:
=ABS(B5-target)>tolerance
На этой странице приведено больше примеров условного форматирования с формулами.
