Подсчет Уникальных ЧИСЛОвых значений в EXCEL

Произведем подсчет уникальных числовых значений. Диапазон может содержать пропуски.

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив чисел {11, 2, 3, 4, 11, 2, 3, 4 , 51}. При подсчете уникальных игнорируются все повторы, т.е. числа выделенные жирным . Соответственно, подсчитываются остальные числа, т.е. 11, 2, 3, 4, 51. Ответ очевиден: количество уникальных значений равно 5.

Задача

Подсчет числа уникальных числовых значений произведем в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки и текст, но будут подсчитываться только числа.

Уникальные значения в файле примера выделены с помощью Условного форматирования .

Решение

Для подсчета используем функцию ЧАСТОТА() . Функция ЧАСТОТА() игнорирует текстовые значения и пустые ячейки. Если аргументы функции ЧАСТОТА() Массив_данных и Массив_интервалов совпадают, то для первого вхождения значения из Массива_данных (т.е. из исходного списка) эта функция возвращает число, равное числу вхождений этого значения. Для каждого последующего вхождения этого значения эта функция возвращает ноль.

Запишем конечную формулу =СУММ(ЕСЛИ(ЧАСТОТА(A7:A15;A7:A15)>0;1))

В нашем случае функция ЧАСТОТА() вернет массив {4:0:1:2:0:1:0:0:0}. Этот результат легко увидеть с помощью клавиши F9 выделите в Строке формул выражение ЧАСТОТА(A7:A15;A7:A15) , нажмите клавишe F9 , вместо формулы отобразится ее результат).

Функция ЕСЛИ() вернет {1:ЛОЖЬ:1:1:ЛОЖЬ:1:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}, а функция СУММ() просуммирует 1, игнорируя значения ЛОЖЬ, и тем самым вернет количество уникальных значений в диапазоне (4).

Другой вариант сложения - формула =СУММПРОИЗВ(--(ЧАСТОТА(A7:A15;A7:A15)>0))

Альтернативные решения

Другая формула: =СУММПРОИЗВ((A7:A15<>"")/СЧЁТЕСЛИ(A7:A15;A7:A15&""))

Еще одна формула (не работает при наличии пустых ячеек в исходном диапазоне): =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A7:A15;A7:A15))

СОВЕТ: О том, как подсчитать уникальные текстовые значения, показано в одноименной статье Подсчет количества уникальных текстовых значений . Про подсчет неповторяющихся значений читайте в статье Подсчет неповторяющихся значений .