Загальна формула
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Резюме
Щоб підрахувати унікальні текстові значення в діапазоні, ви можете використовувати формулу, яка використовує кілька функцій: ЧАСТОТА, ЗБІГ, РЯД і ПІДПРИЄМСТВО. У наведеному прикладі формула в F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
який повертає 4, оскільки в B5 є 4 унікальних імена: B14.
Примітка: Іншим способом підрахунку унікальних значень є використання функції COUNTIF. Це набагато простіша формула, але вона може повільно працювати на великих наборах даних. За допомогою Excel 365 ви можете використовувати простішу та швидшу формулу на основі UNIQUE.
Пояснення
Ця формула є більш складною, ніж аналогічна формула, яка використовує FREQUENCY для підрахунку унікальних числових значень, оскільки FREQUENCY не працює з нечисловими значеннями. В результаті велика частина формули просто перетворює нечислові дані в числові дані, які FREQUENCY може обробляти.
Працюючи зсередини назовні, функція MATCH використовується, щоб отримати позицію кожного елемента, що відображається в даних:
MATCH(B5:B14,B5:B14,0)
Результат від MATCH - це такий масив:
(1;1;1;4;4;6;6;6;9;9)
Оскільки MATCH завжди повертає позицію першого збігу, значення, які з’являються в даних більше одного разу, повертають ту саму позицію. Наприклад, оскільки "Джим" відображається у списку 3 рази, він відображається в цьому масиві 3 рази як число 1.
Цей масив подається в FREQUENCY як аргумент data_array . Аргумент bins_array будується з цієї частини формули:
ROW(B5:B14)-ROW(B5)+1)
який будує послідовний список чисел для кожного значення в даних:
(1;2;3;4;5;6;7;8;9;10)
На даний момент FREQUENCY налаштовано так:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY повертає масив чисел, що вказує підрахунок кожного числа в масиві даних, організований bin. Коли число вже підраховано, FREQUENCY поверне нуль. Це ключова особливість у роботі цієї формули. Результатом від FREQUENCY є такий масив:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Примітка: FREQUENCY завжди повертає масив з ще одним елементом, ніж bins_array .
Тепер ми можемо переписати формулу так:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Далі ми перевіряємо значення більше нуля (> 0), що перетворює числа в TRUE або FALSE, а потім використовуємо подвійне від’ємне значення (-) для перетворення TRUE і FALSE значень в 1s і 0s. Тепер ми маємо:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Нарешті, SUMPRODUCT просто додає цифри і повертає загальну суму, яка в цьому випадку дорівнює 4.
Обробка порожніх клітин
Порожні комірки в діапазоні спричинять формулу, щоб повернути помилку # N / A. Для обробки порожніх комірок можна використовувати більш складну формулу масиву, яка використовує функцію IF для фільтрації порожніх значень:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Примітка: додавання IF робить це формулою масиву, яка вимагає control-shift-enter.
Для отримання додаткової інформації див. Цю сторінку.
Інші способи підрахунку унікальних значень
Якщо у вас Excel 365, ви можете використовувати функцію UNIQUE для підрахунку унікальних значень за допомогою набагато простішої формули.
Зведена таблиця - це також чудовий спосіб підрахувати унікальні значення.
