Загальна формула
=COUNTIF(range,""&high)
Резюме
Щоб підрахувати значення комірок, які не знаходяться між двома числами, ви можете використовувати функцію COUNTIF. У наведеному прикладі формула у клітинці K5, скопійована вниз, має вигляд:
=COUNTIF(C5:G5,""&J5)
У кожному новому рядку ця формула повертає підрахунок значень, що не знаходяться між низьким і високим значеннями у стовпцях I та J.
Пояснення
Метою цього прикладу є підрахунок значень числа, записаних протягом 5 днів, які не потрапляють між двома числами, низьким значенням і великим значенням. Іншими словами, для підрахунку значень, які «виходять за межі діапазону». Зверніть увагу, що кожен рядок, позначений як AG, має свій власний нижній і верхній ліміт у стовпцях I та J.
Спочатку можна подумати використовувати функцію COUNTIFS із двома критеріями. Однак оскільки COUNTIFS поєднує критерії з логікою AND, його не можна використовувати з двома критеріями в цьому сценарії. Логіка значення менше ніж нижче І більше значення вищого значення завжди буде невдалою, а результат завжди буде нульовим. Натомість нам потрібна АБО логіка.
Одним простим рішенням є використання функції COUNTIF двічі наступним чином:
=COUNTIF(C5:G5,""&J5)
Перший COUNTIF підраховує значення нижче значення в I5, а другий COUNTIF підраховує значення вище значення в J5. Якщо їх скласти разом, ці два результати правильно обробляють необхідну логіку: менше I5 АБО більше J5. Зверніть увагу, що оператори більше (">") і менше ("<") об'єднуються у посилання на комірки з оператором амперсанда (&), химерністю функцій RACON.
З SUMPRODUCT
Трохи більш елегантним рішенням є використання функції SUMPRODUCT із двома логічними виразами, як це:
=SUMPRODUCT((C5:G5J5))
Зверніть увагу, що нам не потрібно використовувати конкатенацію з посиланнями на комірки, як у випадку з COUNTIF вище; стандартні вирази чудово працюють.
Це приклад використання логічної алгебри з додаванням (+), що створює АБО логіку. Коли ці вирази обчислюються, ми маємо два масиви значень TRUE і FALSE, таких як:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Операція математики автоматично примушує значення TRUE і FALSE до 1s і 0s. Результат можна візуалізувати так:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
У результаті виходить єдиний масив, що містить два 1:
=SUMPRODUCT((0,0,1,0,1))
Маючи лише один масив для обробки, SUMPRODUCT підсумовує елементи масиву і повертає кінцевий результат 2.
