Как посчитать количество уникальных значений в столбце excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Подсчет Уникальных значений с условиями в EXCEL
Про подсчет уникальных текстовых и числовых значений (без условий) можно прочитать в статье Подсчет Уникальных ТЕКСТовых значений в MS EXCEL и Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL . В этой статье рассмотрим более сложные варианты с условиями.
Задача1
Пусть имеется таблица с перечнем продаж по продавцам.
Требуется подсчитать сколько различных товаров продал конкретный продавец. Например, Вася продал 1 товар1, 2 Товара2 и 1 Товар4 (выделено зеленым). Всего 3 разных товара.
Это можно подсчитать формулой =СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21)) , которая будет работать только с версии MS EXCEL 2007 из-за функции СЧЁТЕСЛИМН() .
Изменив в ячейке А7 имя продавца (в файле примера для удобства сделан выпадающий список ), формула пересчитает количество уникальных.
Задача2
Аналогичным образом можно решить задачу с двумя условиями.
В этом случае будут подсчитаны уникальные товары только в строках, для которых Продавец и Месяц совпадают с критериями, установленными в желтых ячейках.
В файле примера на листе Два критерия приведено решение этой задачи с помощью Сводной таблицы . В этом случае выводится не количество уникальных, а список уникальных товаров.
Задача3
Теперь рассмотрим другую таблицу (столбцы А:С на рисунке ниже).
Необходимо вывести количество заключенных договоров в каждом месяце.
Т.к. в таблице ведется учет счетов сразу для всех договоров и по каждому договору может быть выставлено несколько счетов, то номера договоров могут повторяться.
Создадим дополнительный столбец для определения месяца заключения договора (см. статью Название месяца прописью в MS EXCEL ). Выведем из этого столбца только уникальные месяцы (см. статью Отбор уникальных значений (убираем повторы из списка) в MS EXCEL ) и поместим их в столбец F.
И, наконец, помощью формулы =СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26)) подсчитаем количество уникальных договоров в соответствующем месяце.
Решение также возможно с помощью Сводной таблицы.
Как подсчитать уникальные значения по группам в 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
Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений «а»,»а»,»а» >. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным . Соответственно, подсчитываются остальные значения, т.е. «а» и «b». Ответ очевиден: количество уникальных значений равно 2.
Задача
Произведем подсчет числа уникальных текстовых значений в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки.
Решение
Запишем формулу =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15))
Если в диапазоне кроме текстовых значений содержатся также и числа, то формула подсчитает и их. Чтобы игнорировать числовые значения нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))
Если в список постоянно добавляются значения, то можно создать Динамический именованный диапазон Исходный_список .
Теперь при добавлении новых значений они будут учитываться формулой.
Примечание : Уникальные значения в файле примера выделены с помощью Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL ).
Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст («») , а первая их игнорирует.
Приведем пример, когда это бывает важно.
Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;»») определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).
Формула =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в «пустых» ячейках С31:С34 на самом деле содержатся 4 значения «», которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.
СОВЕТ : Как подсчитать уникальные числовые значения показано в одноименной статье Подсчет уникальных числовых значений .
СОВЕТ : Как подсчитать уникальные числовые значения с дополнительными условиями (критериями) показано в статье Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL .