Общая формула
=SUMPRODUCT(1/COUNTIF(data,data))
Резюме
Чтобы подсчитать количество уникальных значений в диапазоне ячеек, вы можете использовать формулу, основанную на функциях СЧЁТЕСЛИ и СУММПРОИЗВ. В показанном примере формула в F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Объяснение
Работая изнутри, СЧЁТЕСЛИ настроен на значения в диапазоне B5: B14, используя все эти же значения в качестве критериев:
COUNTIF(B5:B14,B5:B14)
Поскольку мы предоставляем 10 значений критериев, мы возвращаем массив с 10 такими результатами:
(3;3;3;2;2;3;3;3;2;2)
Каждое число представляет собой счетчик - «Джим» появляется 3 раза, «Сью» появляется 2 раза и так далее.
Этот массив настроен как делитель с 1 в качестве числителя. После деления получаем еще один массив:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Любые значения, которые встречаются только один раз в диапазоне, будут отображаться как единицы, но значения, которые встречаются несколько раз, будут отображаться как дробные значения, соответствующие кратному. (т.е. значение, которое появляется в данных 4 раза, генерирует 4 значения = 0,25).
Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и возвращает результат.
Обработка пустых ячеек
Один из способов обработки пустых или пустых ячеек - настроить формулу следующим образом:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Соединяя пустую строку ("") с данными, мы предотвращаем попадание нулей в массив, созданный функцией COUNTIF, когда в данных есть пустые ячейки. Это важно, потому что ноль в делителе приведет к тому, что формула выдаст ошибку # DIV / 0. Это работает, потому что при использовании пустой строки («») в качестве критерия будут подсчитываться пустые ячейки.
Однако, хотя эта версия формулы не будет выдавать ошибку # DIV / 0 при использовании пустых ячеек, она будет включать в счет пустые ячейки. Если вы хотите исключить пустые ячейки из подсчета, используйте:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Это приводит к отмене подсчета пустых ячеек путем обнуления числителя для связанных подсчетов.
Медленная производительность?
Это классная и элегантная формула, но она вычисляется намного медленнее, чем формулы, в которых используется ЧАСТОТА для подсчета уникальных значений. Для больших наборов данных вы можете переключиться на формулу, основанную на функции ЧАСТОТА. Вот формула для числовых значений и одна для текстовых значений.
