Загальна формула
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Резюме
Для підрахунку значень, які не допускаються в наборі даних, ви можете використовувати формулу, засновану на функціях SUMPRODUCT та ABS. У наведеному прикладі формула в F6 має вигляд:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
де "дані" - іменований діапазон B5: B14, "ціль" - іменований діапазон F4, а "допуск" - іменований діапазон 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
На цій сторінці перелічено більше прикладів умовного форматування з формулами.
