Загальна формула
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Резюме
Для підрахунку унікальних значень з однією або кількома умовами можна використовувати формулу на основі UNIQUE та FILTER. У наведеному прикладі формула в H7 має вигляд:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
який повертає 3, оскільки в B6 є три унікальні імена: B15, пов’язані з проектом Omega.
Примітка: для цієї формули потрібні формули динамічного масиву, доступні лише в Excel 365. У попередній версії Excel можна використовувати більш складні альтернативні формули.
Пояснення
По суті, ця формула використовує функцію UNIQUE для вилучення унікальних значень, а функція FILTER застосовує критерії.
Працюючи зсередини, функція ФІЛЬТР застосовується для застосування критеріїв та вилучення лише імен, пов’язаних із проектом "Омега":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Результатом від FILTER є такий масив:
("Jim";"Jim";"Carl";"Sue";"Carl")
Далі, функція UNIQUE використовується для видалення дублікатів:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
в результаті чого з’являється новий масив, подібний до цього:
("Jim";"Carl";"Sue") // after UNIQUE
На даний момент у нас є унікальний список імен, пов’язаних з Омегою, і нам просто потрібно їх порахувати. З причин, пояснених нижче, ми робимо це за допомогою функції LEN та функції SUM. Щоб все стало зрозуміліше, спочатку ми перепишемо формулу, щоб включити унікальний список:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Функція LEN отримує довжину кожного елемента у списку та повертає масив довжин:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Далі перевіряємо, чи довжини більше нуля:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
І використовуйте подвійний мінус, щоб привести значення TRUE і FALSE до 1s і 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Нарешті, ми додаємо результати за допомогою функції SUM:
=SUM((1;1;1)) // returns 3
Цей масив доставляється безпосередньо до функції COUNTA, яка повертає остаточний рахунок:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Зверніть увагу, що оскільки ми перевіряємо довжину кожного елемента, що повертається UNIQUE, порожні або порожні клітинки, які відповідають критеріям, ігноруються. Ця формула є динамічною і негайно здійснить перерахунок у разі зміни вихідних даних.
Кількість унікальних з кількома критеріями
Для підрахунку унікальних значень на основі кількох критеріїв можна розширити логіку "включити" всередині ФІЛЬТРА. Наприклад, щоб порахувати унікальні імена для проекту Omega лише у червні, використовуйте:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Це приклад використання логічної логіки для застосування декількох умов. Тут більш детально пояснюється підхід.
Докладніше див. У цьому навчальному відео: Як фільтрувати за кількома критеріями.
КОНТА
Можна написати простішу формулу, яка відповідає функції COUNTA. Однак важливим застереженням є те, що COUNTA поверне 1, коли немає відповідних значень. Це пов’язано з тим, що функція FILTER повертає помилку, коли жодні дані не відповідають критеріям, і ця помилка в підсумку зараховується функцією COUNTA. Основна формула COUNTA виглядає так:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Знову ж таки, ця формула поверне 1, коли немає відповідних даних. Він також включатиме порожні комірки, які відповідають критеріям. Формула на основі LEN та SUM є кращим варіантом.
Немає динамічних масивів
Якщо ви використовуєте стару версію Excel без підтримки динамічного масиву, ви можете використовувати більш складну формулу. Для більш загального обговорення альтернатив динамічного масиву див .: Альтернативи формулам динамічного масиву.
