Общая формула
=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.
Примечание. Еще один способ подсчета уникальных значений - использовать функцию СЧЁТЕСЛИ. Это гораздо более простая формула, но она может медленно работать с большими наборами данных. В Excel 365 вы можете использовать более простую и быструю формулу, основанную на UNIQUE.
Объяснение
Эта формула более сложная, чем аналогичная формула, в которой ЧАСТОТА используется для подсчета уникальных числовых значений, поскольку ЧАСТОТА не работает с нечисловыми значениями. В результате большая часть формулы просто преобразует нечисловые данные в числовые данные, которые может обрабатывать FREQUENCY.
Работая наизнанку, функция ПОИСКПОЗ используется для получения позиции каждого элемента, который появляется в данных:
MATCH(B5:B14,B5:B14,0)
Результатом MATCH является такой массив:
(1;1;1;4;4;6;6;6;9;9)
Поскольку ПОИСКПОЗ всегда возвращает позицию первого совпадения, значения, которые встречаются в данных более одного раза, возвращают ту же позицию. Например, поскольку «Джим» появляется в списке 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 возвращает массив чисел, который указывает количество для каждого числа в массиве данных, организованный по ячейкам. Когда число уже подсчитано, 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), в результате чего числа преобразуются в ИСТИНА или ЛОЖЬ, а затем используем двойное отрицательное число (-) для преобразования значений ИСТИНА и ЛОЖЬ в 1 и 0. Теперь у нас есть:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Наконец, СУММПРОИЗВ просто складывает числа и возвращает итог, который в данном случае равен 4.
Обработка пустых ячеек
Пустые ячейки в диапазоне приведут к тому, что формула вернет ошибку # Н / Д. Для обработки пустых ячеек вы можете использовать более сложную формулу массива, которая использует функцию ЕСЛИ для фильтрации пустых значений:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Примечание: добавление IF превращает это в формулу массива, требующую control-shift-enter.
Для получения дополнительной информации см. Эту страницу.
Другие способы подсчета уникальных значений
Если у вас есть Excel 365, вы можете использовать функцию UNIQUE для подсчета уникальных значений с помощью гораздо более простой формулы.
Сводная таблица также является отличным способом подсчета уникальных значений.
