Загальна формула
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Резюме
Для підрахунку клітинок, не рівних будь-якій з багатьох речей, ви можете використовувати формулу, засновану на функціях MATCH, ISNA та SUMPRODUCT. У наведеному прикладі формула в комірці F5 має вигляд:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
де "дані" - іменований діапазон B5: B16, а "виключити" - іменований діапазон D5: D7.
Пояснення
Спочатку трохи контексту. Зазвичай, якщо у вас є лише кілька речей, які ви не хочете рахувати, ви можете використовувати COUNTIFS таким чином:
=COUNTIFS(range,"apple",range,"orange")
Але це не дуже добре масштабується, якщо у вас є список багатьох речей, тому що вам доведеться додати додаткову пару діапазону / критеріїв до кожної речі, яку ви не хочете рахувати. Було б набагато простіше скласти список і передати посилання на цей список як частину критеріїв. Це саме те, що робить формула на цій сторінці.
По суті, ця формула використовує функцію MATCH для пошуку комірок, не рівних "a", "b" або "c" з таким виразом:
MATCH(data,exclude,0)
Зверніть увагу, що значення підстановки та масив підстановки "відміняються" від звичайної конфігурації - ми надаємо всі значення з іменованого діапазону "дані" як значення підстановки та надаємо всі значення, які ми хочемо виключити, у вказаному діапазоні "виключити". Оскільки ми даємо MATCH більше одного значення підстановки, ми отримуємо більше одного результату в масиві, подібному до цього:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
По суті, MATCH надає нам позицію відповідності значень як число і повертає # N / A для всіх інших значень.
Результати # N / A - це ті, які нас цікавлять, оскільки вони представляють значення, не рівні "a", "b" або "c". Відповідно, ми використовуємо ISNA, щоб примусити ці значення до TRUE, а числа до FALSE:
ISNA(MATCH(data,exclude,0)
Тоді ми використовуємо подвійний негатив, щоб примусити TRUE до 1 і FALSE до нуля. Отриманий масив усередині SUMPRODUCT виглядає так:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Маючи лише один масив для обробки, SUMPRODUCT підсумовує та повертає кінцевий результат, 4.
Примітка: Використання SUMPRODUCT замість SUM дозволяє уникнути необхідності використовувати control + shift + enter.
Граф мінус збіг
Інший спосіб підрахувати клітини, які не дорівнюють будь-якій із кількох речей, - це порахувати всі значення та відняти збіги. Ви можете зробити це за такою формулою:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Тут COUNTA повертає кількість усіх непорожніх комірок. Функція COUNTIF, з урахуванням названого діапазону "виключити", поверне три відліки, по одному для кожного елемента у списку. SUMPRODUCT додає загальну суму, і це число віднімається від підрахунку всіх непустих комірок. Кінцевим результатом є кількість клітинок, які не дорівнюють значенням у "виключити".
Літерал містить логіку типу
Формула на цій сторінці вважається логікою "дорівнює". Якщо вам потрібно підрахувати комірки, які не містять багато рядків, де містить означає, що рядок може з’являтися де завгодно в комірці, вам знадобиться більш складна формула.
