= SUMPRODUCT (1/ COUNTIF (data,data))Резюме
Чтобы подсчитать количество уникальных значений в диапазоне ячеек, вы можете использовать формулу на основе СЧЁТЕСЛИ а также СУММПРОИЗВ функции. В показанном примере формула в F6:
С участием Excel 365 , вы можете использовать более простая и быстрая формула на основе УНИКАЛЬНЫЙ . Объяснение= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))
Работая изнутри, СЧЁТЕСЛИ настроен на значения в диапазоне 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&''))
Это приводит к отмене подсчета пустых ячеек путем обнуления числителя для связанных подсчетов.
Медленная производительность?
Это крутая и элегантная формула, но она вычисляется намного медленнее, чем формулы, в которых используется ЧАСТОТА для подсчета уникальных значений. Для больших наборов данных вы можете переключиться на формулу, основанную на функции ЧАСТОТА. Вот формула для числовые значения и один для текстовые значения .
Автор Дэйв Брунс