Как посчитать уникальные значения в excel

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

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

Задача

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

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

Решение

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

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

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

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

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

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

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

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

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

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

Вы можете использовать следующую формулу для подсчета количества уникальных значений по группам в Excel:

=SUMPRODUCT(( $A$2:$A$13 = A2 )/COUNTIFS( $B$2:$B$13 , $B$2:$B$13 , $A$2:$A$13 , $A$2:$A$13 ))

В этой формуле предполагается, что имена групп находятся в диапазоне A2:A13 , а значения — в диапазоне B2:B13 .

В следующем примере показано, как использовать эту формулу на практике.

Пример: подсчет уникальных значений по группам в Excel

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

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

Для этого мы можем использовать функцию =UNIQUE() , чтобы сначала создать список уникальных команд. Мы введем следующую формулу в ячейку D2:

= UNIQUE ( A2:A13 )

Как только мы нажмем Enter, отобразится список уникальных названий команд:

Теперь мы можем ввести следующую формулу в ячейку E2, чтобы подсчитать количество уникальных значений очков для «Лейкерс»:

=SUMPRODUCT(( $A$2:$A$13 = D2 )/COUNTIFS( $B$2:$B$13 , $B$2:$B$13 , $A$2:$A$13 , $A$2:$A$13 ))

Затем мы перетащим эту формулу в оставшиеся ячейки в столбце E:

Столбец D отображает каждую из уникальных команд, а столбец E отображает количество уникальных значений очков для каждой команды.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Вычисление количества уникальных значений

Иногда в работе нам нужно посчитать уникальные значения в определенной колонке, однако Excel имеет функции, которые суммируют только количество записей в заданном поле, например функция COUNT(). Проблема в том, что один и тот же код товара или клиента может повторяться несколько раз. Но есть выход, для решения нашей задачи мы можем совместить стандартные функции Excel. Давайте посмотрим как это сделать.

Итак, давайте соединим функции SUM() — суммирует, IF() — проверка условия, FREQUENCY() — подсчитывает кол-во значений, попадающих в определенный интервал, LEN() — считает кол-во символов, MATCH() — ищет позицию элемента в массиве:

1. Вычисление количества уникальных числовых значений

=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10;A2:A10)>0;1))

2. Вычисление количества уникальных числовых и текстовых значений (не работает, если есть пустые ячейки)

=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10;B2:B10;0);ПОИСКПОЗ(B2:B10;B2:B10;0))>0;1))

3. Вычисление количества уникальных значений (универсальная формула)

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»);ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»))>0;1))

Последнюю формулу нужно вводить как формулу массива, т.е. нажать не просто Enter, а Ctrl + Shift + Enter. После этого в строке формул мы увидим, что формула взята в фигурные скобки (<>), это признак того, что введенная формула массива.

Изменение регистра букв в тексте
Сумма прописью на украинском языке
Поиск латиницы в кириллице и наоборот
Транслитерация с украинского на английский

ИНТЕРЕСНЫЕ СТАТЬИ

VLOOKUP
VLOOKUP2
VLOOKUP3
VLOOKUP2D
SUMIFS
CONCATIF
Сумма прописью в Excel
TRANSLIT
MAXIF
SPLITUP
WORKHOURS
SQL запрос в Excel
Функция импорта курсов валют с сайта НБУ
Импорт даних с Access в Excel

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Читать:

Как в sql написать не равно

Предположим, что требуется определить количество уникальных значений в диапазоне, содержащем повторяющиеся значения. Например, если столбец содержит:

числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
строки «Руслан», «Сергей», «Сергей», «Сергей», будут найдены два уникальных значения — «Руслан» и «Сергей».

Существует несколько способов подсчета количества уникальных значений среди повторяющихся.

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

С помощью диалогового окна Расширенный фильтр можно извлечь уникальные значения из столбца данных и вставить их в новое местоположение. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
Установите переключатель скопировать результат в другое место.
В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите Развернуть диалоговое окно .
Установите флажок Только уникальные записи и нажмите ОК. Уникальные значения из выделенного диапазона будут скопированы в новое место, начиная с ячейки, указанной в поле Копировать.
В пустой ячейке под последней ячейкой диапазона введите функцию ЧСТРОК. Используйте диапазон скопированных уникальных значений в качестве аргумента, исключив заголовок столбца. Например, если уникальные значения содержатся в диапазоне B2:B45, введите =ЧСТРОК(B2:B45).

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

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
Вычислите сумму, используя функцию СУММ.
Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
Узнайте позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

Примеры вложенных функций для подсчета количества уникальных значений среди дубликатов

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите Вычислить формулу.

Описание функций

Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, так как элемент 25 является вторым в диапазоне.
Функция ДЛСТР возвращает число символов в текстовой строке.
Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.