Общая формула
=COUNTA(UNIQUE(data))
Резюме
Чтобы подсчитать уникальные значения в наборе данных, вы можете использовать функцию UNIQUE вместе с функцией COUNTA. В показанном примере формула в F5:
=COUNTA(UNIQUE(B5:B16))
который возвращает 7, поскольку в B5 есть семь уникальных цветов: B16.
Объяснение
В этом примере функция UNIQUE используется для извлечения уникальных значений. Когда UNIQUE предоставляется с диапазоном B5: B16, который содержит 12 значений, он возвращает 7 уникальных значений, замеченных в D5: D11. Они возвращаются непосредственно в функцию COUNTA в виде такого массива:
=COUNTA(("red";"amber";"green";"blue";"purple";"pink";"gray"))
В отличие от функции COUNT, которая считает только числа, COUNTA считает и текст, и числа. Поскольку в массиве семь элементов, COUNTA возвращает 7. Эта формула является динамической и будет немедленно пересчитываться при изменении исходных данных.
Со ссылкой на ячейку
Вы также можете обратиться к списку уникальных значений, уже извлеченных на рабочий лист, с помощью функции UNIQUE, используя специальный вид ссылки на ячейку. Формула в D5:
=UNIQUE(B5:B16)
который возвращает семь значений из D5: D11. Чтобы подсчитать эти значения с помощью динамической ссылки, вы можете использовать следующую формулу:
=COUNTA(D5#)
Символ решетки (#) сообщает Excel, что нужно ссылаться на диапазон разлива, созданный UNIQUE. Как и приведенная выше универсальная формула, эта формула является динамической и будет адаптироваться при добавлении или удалении данных из исходного диапазона.
Подсчет уникальных пропусков игнорирования
Чтобы подсчитать уникальные значения, игнорируя пустые ячейки, вы можете добавить функцию ФИЛЬТР следующим образом:
=COUNTA(UNIQUE(FILTER(data,data"")))
Более подробно этот подход объясняется здесь. Вы также можете фильтровать уникальные значения с помощью критериев.
Нет данных
Одним из ограничений этой формулы является то, что она будет неправильно возвращать 1, если в диапазоне данных нет никаких значений. Эта альтернатива будет подсчитывать все значения, возвращаемые UNIQUE, которые имеют длину больше нуля. Другими словами, он будет считать все значения хотя бы с одним символом:
=SUM(--(LEN(UNIQUE(B5:B16))>0))
Здесь функция LEN используется для проверки длины результатов UNIQUE. Затем длины проверяются, чтобы убедиться, что они больше нуля, и результаты подсчитываются с помощью функции СУММ. Это пример логической логики. Эта формула также исключает из результатов пустые ячейки.
Диапазон динамического источника
UNIQUE не изменит автоматически исходный диапазон при добавлении или удалении данных. Чтобы предоставить UNIQUE динамический диапазон, размер которого будет автоматически изменяться по мере необходимости, вы можете использовать таблицу Excel или создать динамический именованный диапазон с помощью формулы.
Нет динамических массивов
Если вы используете старую версию Excel без поддержки динамических массивов, вот несколько альтернатив.
