Как подсчитать уникальные значения по группам в 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?
Иногда в работе нам нужно посчитать уникальные значения в определенной колонке, однако 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. После этого в строке формул мы увидим, что формула взята в фигурные скобки (<>), это признак того, что введенная формула массива.
покупка
Как подсчитать количество уникальных и различных значений из списка столбцов?
Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, теперь вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или отдельных значений (все разные значения в списке, это означает уникальные значения + 1-е повторяющиеся значения) в столбце, как показано на скриншоте слева. В этой статье я расскажу о том, как справиться с этой задачей в Excel.
Подсчитайте количество уникальных или различных значений из списка столбцов с формулами массива
Подсчитайте количество уникальных значений из списка столбцов
Чтобы подсчитать только уникальные значения в столбце, примените следующую формулу:
1. Введите или скопируйте приведенную ниже формулу массива в пустую ячейку:
=SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))
Внимание: В приведенной выше формуле A2: A12 это список данных, для которых вы хотите подсчитать уникальные значения.
2, Затем нажмите Shift + Ctrl + Enter вместе, все уникальные значения, которые появляются в списке только один раз, учитываются, как показано на следующем снимке экрана:
Подсчитайте количество различных значений из списка столбцов
Если вы хотите подсчитать количество всех уникальных значений + 1-е повторяющиеся значения в столбце, вам может помочь приведенная ниже формула.
1. Введите или скопируйте приведенную ниже формулу массива в пустую ячейку:
=SUM(IF(A2:A12<>«»,1/COUNTIF(A2:A12, A2:A12), 0))
Внимание: В приведенной выше формуле A2: A12 это список данных, по которым вы хотите подсчитать различные значения.
2, Затем нажмите Shift + Ctrl + Enter вместе, вы получите следующий результат по мере необходимости:
Подсчитайте количество уникальных или уникальных значений из списка столбцов с полезными функциями
Если вам трудно запоминать формулы, я представлю вам мощный инструмент — Формула Помощник of Kuttools для Excel, Формула Помощник собраны десятки общих формул, которые могут сэкономить вам много времени, и вам больше не нужно запоминать формулы. С этими Cout уникальные ценности и Подсчет ячеек с уникальными значениями (включая первое повторяющееся значение) функции, вы можете быстро получить количество уникальных и различных значений из списка.
Примечание: Чтобы применить Cout уникальные ценности и Подсчет ячеек с уникальными значениями (включая первое повторяющееся значение) функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Щелкните ячейку, в которой вы хотите вывести результат. Смотрите скриншот:
2. Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне выполните следующие действия:
- Выберите Статистический из файла ФормулаТип раскрывающийся список;
- Затем выберите Подсчет уникальных значений or Подсчет ячеек с уникальными значениями (включая повторяющееся значение) из Выберите изула список, как вам нужно;
- В правой Ввод аргументов В разделе выберите список ячеек, в которых вы хотите подсчитать уникальные значения.
4. Затем нажмите Ok Кнопка, уникальные или уникальные значения будут рассчитаны, как показано ниже:
Больше относительных статей:
- Подсчет уникальных значений на основе другого столбца в Excel
- У нас может быть обычным считать уникальные значения только в одном столбце, но в этой статье я расскажу о том, как подсчитывать уникальные значения на основе другого столбца. Например, у меня есть данные из следующих двух столбцов, теперь мне нужно подсчитать уникальные имена в столбце B на основе содержимого столбца A, чтобы получить следующий результат.
- Подсчет уникальных значений на основе нескольких критериев в Excel
- В этой статье я приведу несколько примеров для подсчета уникальных значений на основе одного или нескольких критериев на листе. Следующие подробные шаги могут вам помочь.
- Подсчитайте уникальные значения между двумя датами в Excel
- Вы когда-нибудь путались с подсчетом уникальных значений между диапазоном дат в Excel? Например, вот два столбца, идентификатор столбца включает несколько повторяющихся чисел, а столбец Дата включает ряд дат, и теперь я хочу подсчитать уникальные значения между 8/2/2016 и 8/5/2016, как вы можете быстро решить эту проблему в Excel?
- Подсчет уникальных значений в сводной таблице
- По умолчанию, когда мы создаем сводную таблицу на основе диапазона данных, который содержит несколько повторяющихся значений, все записи также будут подсчитаны, но иногда мы просто хотим подсчитать уникальные значения на основе одного столбца, чтобы получить правильные результат скриншота. В этой статье я расскажу о том, как подсчитать уникальные значения в сводной таблице.
- Подсчет уникальных значений с пробелами в столбце Excel
- Например, в Excel есть список чисел с дубликатами и пустыми ячейками, как быстро подсчитать уникальные значения в этом списке, как показано на скриншоте ниже?
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .
Вкладка Office — предоставляет интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Подсчет количества уникальных значений
Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза: Задача — подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта. Рассмотрим несколько способов ее решения.
Способ 1. Формула массива
Если вы уверены, что в исходном диапазоне данных нет пустых ячеек, то можно использовать короткую и элегантную формулу массива: 
Не забудьте ввести её как формулу массива, т.е. нажать после ввода формулы не Enter , а сочетание Ctrl + Shift + Enter . Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF) . Если представить это в виде дополнительного столбца, то выглядело бы оно так: 
Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов: 
Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби): 
В английской версии это будет: =SUM(IF(ISEMPTY(A2:A10);0;1/COUNTIF(A2:A10;A2:A10))) Ну, и наконец, если не охота заморачиваться с сочетанием клавиш Ctrl + Shift + Enter , то можно заменить функцию СУММ на аналогичную по смыслу (в данном случае) функцию СУММПРОИЗВ (SUMPRODUCT) , которая понимает формулы массива «из коробки», т.е. при вводе обычным образом с помощью только Enter : 
Вот и все дела. Но на достаточно большой таблице (хотя бы несколько тысяч строк) такая формула будет тормозить нещадно, так что потребуются другие подходы.
Способ 2. Удаление дубликатов
Это хоть и рукопашный, но вполне рабочий вариант, если нужно быстро и единоразово оценить количество уникальных значений. Выделив исходные данные, идём на вкладку Данные — Удалить дубликаты (Data — Remove Duplicates) . В открывшемся окне помечаем флажком столбцы, по которым нужно удалить повторы и после нажатия на ОК получаем сообщение с количеством оставшихся уникальных значений:
Просто, но минус в том, что при изменении исходных данных придётся повторять весь процесс.
Способ 3. Сводная таблица
Если построить по исходной таблице сводную, то можно поместить поле, по которому мы хотим подсчитать количество дубликатов, в область строк или столбцов и затем простой функцией СЧЁТЗ (COUNTA) посчитать количество значений в полученном списке (вычтя 2 лишних ячейки на заголовок и итоги): 
Ещё красивее будет, если построить не простую сводную, а сводную по Модели Данных, включив в окне построения сводной соответствующий флажок через Вставка — Сводная таблица — Добавить эти данные в модель данных (Insert — Pivot Table — Add this data to Data Model) . В этом случае можно закинуть искомый столбец уже в область значений и переключить функцию расчёта поля в подсчёт количества уникальных, щёлкнув по полю правой кнопкой мыши и выбрав команду Итоги по — Количество различных значений (Summarize Values By — Count Distinct) : 
Большим плюсом этого варианта будет возможность использовать другие области сводной таблицы для дополнительной фильтрации и детализации, например, по годам, категориям товаров и т.п.
Способ 4. Новая функция УНИК
В последних версиях, а именно начиная с Excel 2021 и в Excel 365, появилась поддержка динамических массивов и несколько новых мощных функций для работы с ними. Одна из этих функций УНИК (UNIQUE) — выводит массив уникальных значений из исходного списка. Если завернуть её снаружи в функцию подсчёта количества заполненных ячеек СЧЁТЗ (COUNTA) , то получим простой и быстрый способ расчёта количества уникальных для таблицы любого размера:
Способ 5. Макрофункция на VBA
Если последней версии Excel у вас пока нет, а функцию УНИК иметь очень хочется, то вполне можно написать её аналог на VBA. Для этого выберите на вкладке Разработчик (Developer) команду Visual Basic или нажмите сочетание клавиш Alt + F11 , чтобы открыть окно редактора макросов, вставьте туда новый модуль через меню Insert — Module и введите в него код следующей функции:
Function DistinctCount(dataRange As Range) Dim coll As New Collection Dim cell As Range 'если возникла ошибка при добавлении в коллекцию (т.е. добавляем дубль), то идём дальше On Error Resume Next 'перебираем ячейки в исходном диапазоне For Each cell In dataRange 'если ячейка не пустая, то пытаемся добавить её в коллекцию If Not IsEmpty(cell) Then coll.Add cell.Value, CStr(cell.Value) Next cell DistinctCount = coll.Count End Function
Эта функция принимает в качестве единственного аргумента диапазон, перебирает его ячейки и пытается добавить их в коллекцию. Если в коллекции уже есть такой элемент, то он игнорируется и, в итоге, мы получаем набор уникальных значений. Количество элементов в собранной коллекции — это и есть результат, который функция возвращает нам в итоге. Использовать созданную функцию очень легко — она ничем не отличается от любой другой встроенной функции Excel:
Только не забудьте сохранить файл в формате с поддержкой макросов (xlsm или xlsb), чтобы не потерять созданный код.
Ссылки по теме
- Как извлечь из диапазона уникальные элементы и удалить дубликаты
- Как подсветить дубликаты в списке цветом
- Как сравнить два диапазона на наличие в них дубликатов
- Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки PLEX