Загальна формула
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Резюме
Для підрахунку унікальних текстових значень у діапазоні з критеріями можна використовувати формулу масиву на основі функцій FREQUENCY та MATCH. У наведеному прикладі формула в G6 має вигляд:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
який повертає 3, оскільки над проектом Omega працювали троє різних людей.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
Це складна формула, яка використовує FREQUENCY для підрахунку числових значень, отриманих за допомогою функції MATCH. Працюючи зсередини, функція MATCH використовується, щоб отримати позицію кожного значення, яке відображається в даних:
MATCH(B5:B11,B5:B11,0)
Результат від MATCH - це такий масив:
(1;1;3;1;1;6;7)
Оскільки MATCH завжди повертає позицію першого збігу, значення, які з’являються в даних більше одного разу, повертають ту саму позицію. Наприклад, оскільки "Джим" відображається у списку 4 рази, він відображається в цьому масиві 4 рази як число 1.
Поза функцією MATCH, функція IF використовується для застосування критеріїв, що в цьому випадку передбачає тестування, чи проект "омега" (з комірки G5):
IF(C5:C11=G5 // filter on "omega"
Функція IF діє як фільтр, дозволяючи пропускати значення з MATCH, лише якщо вони пов'язані з "омегою". Результат - такий масив:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Відфільтрований масив доставляється безпосередньо до функції FREQUENCY як аргумент data_array . Далі функція ROW використовується для побудови послідовного списку чисел для кожного значення в даних:
ROW(B3:B12)-ROW(B3)+1
Це створює такий масив:
(1;2;3;4;5;6;7;8;9;10)
який стає аргументом bins_array у FILTER. На даний момент ми маємо:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY повертає масив чисел, що вказує підрахунок кожного значення в масиві даних, організований bin. Коли число вже підраховано, FREQUENCY поверне нуль. Результатом від FREQUENCY є такий масив:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Примітка: FREQUENCY завжди повертає масив з ще одним елементом, ніж bins_array .
На даний момент ми можемо переписати формулу так:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Ми перевіряємо значення більше нуля, що перетворює числа в TRUE або FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Потім ми використовуємо подвійний негатив для приведення логічних значень до 1s і 0s:
=SUM((1;0;0;0;0;1;1;0))
Нарешті, функція SUM повертає 3 як кінцевий результат.
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Обробка порожніх комірок у діапазоні
Якщо будь-які комірки в діапазоні порожні, вам потрібно буде відрегулювати формулу, щоб запобігти передачі порожніх комірок у функцію MATCH, що призведе до помилки. Це можна зробити, додавши ще одну вкладену функцію IF для перевірки порожніх комірок:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
З двома критеріями
Якщо у вас два критерії, ви можете розширити логіку формули, додавши ще один вкладений IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Де c1 = критерії1, c2 = критерії2 та vals = діапазон значень.
З логічною логікою
За допомогою булевої логіки ви можете зменшити вкладені IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Це полегшує додавання та управління додатковими критеріями.
