Excel

Подсчет уникальных значений в диапазоне с помощью COUNTIF

Count Unique Values Range With Countif

Формула Excel: подсчет уникальных значений в диапазоне с помощью СЧЁТЕСЛИУниверсальная формула
= SUMPRODUCT (1/ COUNTIF (data,data))
Резюме

Чтобы подсчитать количество уникальных значений в диапазоне ячеек, вы можете использовать формулу на основе СЧЁТЕСЛИ а также СУММПРОИЗВ функции. В показанном примере формула в F6:





 
= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))
С участием Excel 365 , вы можете использовать более простая и быстрая формула на основе УНИКАЛЬНЫЙ . Объяснение

Работая изнутри, СЧЁТЕСЛИ настроен на значения в диапазоне B5: B14, используя все эти тем же значения как критерии:

 
 COUNTIF (B5:B14,B5:B14)

Поскольку мы предоставляем 10 значений критериев, мы возвращаем множество с 10 такими результатами:





 
{3332233322}

Каждое число представляет собой счетчик - «Джим» появляется 3 раза, «Сью» появляется 2 раза и так далее.

Этот массив настроен как делитель с 1 в качестве числителя. После деления получаем еще один массив:



 
{0.3333333333333330.3333333333333330.3333333333333330.50.50.3333333333333330.3333333333333330.3333333333333330.50.5}

Любые значения, которые встречаются только один раз в диапазоне, будут отображаться как единицы, но значения, которые встречаются несколько раз, будут отображаться как дробные значения, соответствующие кратному. (т.е. значение, которое появляется в данных 4 раза, генерирует 4 значения = 0,25).

Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и возвращает результат.

как создать новое правило условного форматирования в Excel

Обработка пустых ячеек

Один из способов обработки пустых или пустых ячеек - настроить формулу следующим образом:

 
= SUMPRODUCT (1/ COUNTIF (data,data&''))

К сцепление ан пустой строки ('') к данным, мы предотвращаем попадание нулей в массив, созданный функцией COUNTIF, когда в данных есть пустые ячейки. Это важно, потому что ноль в делителе приведет к тому, что формула выдаст ошибку # DIV / 0. Это работает, потому что при использовании пустой строки ('') для критериев будут подсчитываться пустые ячейки.

Однако, хотя эта версия формулы не будет выдавать ошибку # DIV / 0 при пустых ячейках, она буду включить в подсчет пустые ячейки. Если вы хотите исключить пустые ячейки из подсчета, используйте:

 
= SUMPRODUCT ((data'')/ COUNTIF (data,data&''))

Это приводит к отмене подсчета пустых ячеек путем обнуления числителя для связанных подсчетов.

Медленная производительность?

Это крутая и элегантная формула, но она вычисляется намного медленнее, чем формулы, в которых используется ЧАСТОТА для подсчета уникальных значений. Для больших наборов данных вы можете переключиться на формулу, основанную на функции ЧАСТОТА. Вот формула для числовые значения и один для текстовые значения .

Автор Дэйв Брунс


^