Как подсчитать количество уникальных значений в диапазоне с учётом нескольких критериев в Excel?
На практике часто бывает недостаточно просто подсчитать значения — важно определить, сколько уникальных элементов соответствует заданным условиям в ваших данных. Например, вы можете захотеть узнать, сколько различных товаров продал конкретный менеджер или сколько уникальных заказов было оформлено за определённый период. Для эффективного решения таких задач в Excel стоит освоить подходящие формулы, расширенные инструменты, такие как сводные таблицы, а также пользовательские решения на VBA. В этой статье мы рассмотрим несколько практических методов подсчёта количества уникальных значений в диапазоне по одному или нескольким критериям — с пошаговыми инструкциями и полезными советами.
Подсчитать количество уникальных значений в диапазоне по одному критерию
Подсчитать количество уникальных значений в диапазоне по двум заданным датам
Подсчитать количество уникальных значений в диапазоне по двум критериям
Подсчитать количество уникальных значений в диапазоне по трём критериям
Подсчитать количество уникальных значений в диапазоне по одному критерию
Рассмотрим распространённый случай: вы хотите подсчитать, сколько различных товаров продал Том. Этот метод подходит, когда у вас простой набор данных и вы хотите оценить уникальность на основе одного условия, например, по продажам одного человека. Метод прост, но требует внимательного использования формул массива.
Для этого сценария введите следующую формулу в пустую ячейку (например, G2):
=SUM(IF(«Tom»=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20)),0))
После ввода формулы нажмите клавиши Ctrl + Shift + Enter (а не просто Enter), чтобы подтвердить её как формулу массива. В строке формул вокруг формулы появятся фигурные скобки, и результат отобразится немедленно, как показано ниже:
Примечание:
- «Tom» — это условие, по которому вы хотите фильтровать результаты. Для большей гибкости вы можете заменить «Tom» ссылкой на другую ячейку (например, $F$2).
- Диапазон $C$2:$C$20 содержит имена продавцов, которых необходимо оценить.
- $A$2:$A$20 — это столбец с товарами, для которых вы хотите получить уникальные подсчёты.
- Если ваш диапазон данных изменится, не забудьте своевременно обновить соответствующие ссылки.
Совет: если вы используете Excel 365 или Excel 2019 и новее, попробуйте упростить формулы с помощью функций UNIQUE и FILTER.
Если вы столкнётесь с ошибками #DIV/0!, внимательно проверьте критерии и убедитесь, что ваши диапазоны имеют одинаковую длину.
Подсчитать количество уникальных значений в диапазоне по двум заданным датам
Если вам нужно определить количество уникальных элементов за конкретный диапазон дат — например, все уникальные товары, проданные с 01,09.2016 по 30,09.2016, — воспользуйтесь этим подходом. Он особенно эффективен при анализе тенденций за заданные периоды: месячные, квартальные или любые другие пользовательские диапазоны дат. Обратите внимание: формат дат должен точно соответствовать тому, что используется на вашем листе.
Введите следующую формулу в пустую ячейку, где вы хотите отобразить результат:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&,DATE(2016,9,1))),0)
После ввода формулы нажмите клавиши Ctrl + Shift + Enter, чтобы выполнить её как формулу массива. На рисунке ниже показан результат:
Примечание:
- 2016,9,1 и 2016,9,30 — это начальная и конечная даты для фильтрации. При необходимости вы можете изменить их или даже использовать ссылки на ячейки для динамической фильтрации по датам.
- Диапазон $D$2:$D$20 содержит записи с датами, которые необходимо проверить.
- $A$2:$A$20 — это снова столбец с товарами или позициями, которые нужно подсчитать уникально.
- Убедитесь, что ваши даты сохранены в Excel как корректные даты, а не как текст. Если результат отображается не так, как ожидалось, проверьте формат дат и используемые диапазоны.
Совет: используйте функцию DATE(год; месяц; день), чтобы избежать проблем с региональным форматированием дат. При работе с динамическими диапазонами рекомендуем использовать именованные диапазоны — так ваша формула станет гораздо читабельнее.
Подсчитать количество уникальных значений в диапазоне по двум критериям
Допустим, вы хотите проанализировать только те товары, которые Том продал в сентябре, объединив имя и диапазон дат для подсчёта уникальных значений. Такой сценарий часто возникает при периодической оценке эффективности или сегментированном анализе. По мере роста числа критериев формула усложняется, и точность данных становится ещё важнее.
Введите приведённую ниже формулу в любую пустую ячейку, например H2:
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&,DATE(2016,9,1))),0)
После ввода формулы подтвердите её нажатием клавиш Ctrl + Shift + Enter. Уникальное количество отобразится немедленно — см. иллюстрацию ниже:
Примечания:
- «Tom» — это критерий по имени, а «2016,9,1» и «2016,9,30» задают границы диапазона дат. При необходимости скорректируйте их вручную или сделайте динамическими, используя ссылки на ячейки.
- $C$2:$C$20 — это столбец с сотрудниками (или другим первым критерием); $D$2:$D$20 — столбец с датами; $A$2:$A$20 содержит уникальные элементы, которые нужно подсчитать.
- Все диапазоны должны быть одинаковой длины, чтобы избежать ошибок.
Если вы хотите использовать условия «ИЛИ» — например, подсчитать уникальные товары, проданные Томом или в южном регионе, — воспользуйтесь следующей формулой. Это позволяет задавать более широкие критерии поиска, хотя результаты могут пересекаться, если данные соответствуют обоим условиям.
=SUM(--(FREQUENCY(IF((«Tom»=$C$2:$C$20)+(«South»=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Не забудьте нажать клавиши Ctrl + Shift + Enter. Результаты будут выглядеть так, как показано ниже:
Совет: При использовании критериев ИЛИ учитывайте риск двойного учёта — если одна и та же запись соответствует обоим условиям, это может сказаться на производительности, особенно при работе с большими наборами данных.
Подсчитать количество уникальных значений в диапазоне на основе трёх критериев
Иногда ваш анализ может требовать трёх или более условий — например, определение уникальных товаров, проданных Томом в сентябре исключительно в северном регионе. Это типичная задача при многомерном анализе данных для составления отчётов или получения целенаправленной бизнес-аналитики. При работе со столь сложной логикой крайне важно правильно управлять ссылками.
Поместите эту формульную таблицу в пустую ячейку (например, I2):
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(«North»=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Нажмите Ctrl + Shift + Enter, чтобы завершить. Ниже приведён пример результата для справки:
Для сложных условий дважды проверьте, что все диапазоны согласованы и типы данных (например, дата и текст) указаны верно. Несоответствия могут вызвать ошибки или ввести в заблуждение.
Советы:
- Если при работе с большими наборами данных возникают проблемы с производительностью, попробуйте упростить формулу или воспользуйтесь решением Excel Сводная таблица.
- Использование именованных диапазонов или ссылок на ячейки для всех критериев делает формулы понятнее и помогает избежать ошибок.
- Если вы планируете часто использовать эти формулы, мы рекомендуем сохранить их в виде именованных ссылок на ячейки или пользовательских функций.
Подсчитать количество уникальных значений в диапазоне с помощью Сводная таблица (Уникальный подсчёт, Excel 2013+)
Пользователям Excel 2013 и более поздних версий сводные таблицы предлагают более интерактивную альтернативу без формул для подсчёта количества уникальных значений в диапазоне по одному или нескольким критериям. Функция «Уникальный подсчёт» позволяет эффективно обобщать и фильтровать большие объёмы данных, делая этот метод особенно подходящим для динамичных сред, ориентированных на отчётность. Обратите внимание, что более ранние версии Excel не поддерживают функцию «Уникальный подсчёт» в сводных таблицах.
Как использовать этот метод:
- Выделите свой набор данных и перейдите в меню Вставка > Сводная таблица.
- В диалоговом окне создания сводной таблицы выберите место её размещения, установите флажок «Добавить эти данные в модель данных» и нажмите кнопку ОК.
- Перетащите поле, по которому требуется подсчитать уникальные значения (например, «Товар»), в область значений — по умолчанию оно отобразится как «Количество…».
- Щёлкните по полю в области значений и выберите пункт Параметры поля «Настройки полей».
- В появившемся диалоговом окне прокрутите список вниз и выберите вариант Уникальное количество (этот параметр доступен только в Excel 2013 и более поздних версиях и отображается, если сводная таблица создана с включённой опцией «Добавить эти данные в модель данных»).
- Добавьте поля критериев (например, «Продавец», «Регион», «Дата») в область фильтров или в строки/столбцы, чтобы задать одно или несколько условий.
- Теперь ваша сводная таблица будет отображать уникальное количество значений, отфильтрованных по выбранным критериям.
Преимущества: Высокая наглядность, простота настройки фильтров без редактирования формул и удобство для интерактивной отчётности.
Ограничения: Недоступно в Excel 2010 и более ранних версиях; при добавлении новых данных сводную таблицу необходимо обновлять вручную.
Практический совет: Всегда проверяйте, что в одной записи внутри «Исходные данные» нет случайных дубликатов. Если вы не видите опцию «Уникальный подсчёт», пересоздайте сводную таблицу и обязательно отметьте флажок «Добавить эти данные в модель данных».
Подсчитать количество уникальных значений в диапазоне с помощью кода VBA (для сложных/автоматизированных случаев)
Иногда вам может понадобиться автоматически подсчитывать количество уникальных значений в диапазоне на основе различных критериев — особенно при работе с очень большими наборами данных или при частом повторении анализа. В таких случаях идеально подходит макрос VBA: один раз настроив его, вы сможете быстро обрабатывать сложную логику, включая фильтрацию по нескольким условиям, без ручного вмешательства. Однако VBA сложнее стандартных возможностей Excel, поэтому его лучше использовать тем, кто уверенно работает с макросами, или пользователям с регулярными аналитическими задачами.
Этапы выполнения:
- Нажмите клавиши Alt + F11, чтобы открыть редактор VBA. В редакторе выберите пункт меню Вставка > Модуль, чтобы создать новый модуль.
- Скопируйте и вставьте следующий код VBA в модуль:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Закройте редактор VBA и вернитесь на лист. Нажмите клавиши Alt + F8, выберите макрос CountUniqueWithCriteria и запустите его.
- Следуйте инструкциям на экране, чтобы задать диапазоны и критерии в соответствии с вашими данными. Результат отобразится как в выбранной вами ячейке, так и в виде сообщения.
Пояснение параметров и примечания:
- В настоящее время этот макрос настроен на один критерий. Чтобы расширить его для нескольких критериев, измените логику оператора
If ... Thenвнутри цикла. - Всегда сохраняйте книгу перед запуском макросов — отменить внесённые изменения невозможно.
- Если возникают ошибки выполнения, включите макросы в настройках Excel.
- Этот метод идеально подходит для больших или часто обновляемых наборов данных, где применение обычных формул было бы неудобным.
Преимущества: Высокая степень настройки и возможности автоматизации, эффективная обработка больших и динамичных наборов данных. Идеально подходит для сложных или регулярно повторяющихся рабочих процессов.
Недостатки: Требуются разрешения на запуск макросов, а новичкам может понадобиться время, чтобы освоить работу с VBA.
При подсчёте уникальных значений по критериям всегда тщательно проверяйте ссылки на диапазоны и убедитесь, что все столбцы с критериями имеют одинаковый размер — несоответствие диапазонов часто приводит к ошибкам или некорректным результатам. Если формулы выдают неожиданные значения, обратите внимание на скрытые проблемы с форматированием или наличие пустых ячеек. В задачах, где важна производительность, сводные таблицы и VBA становятся надёжной альтернативой формульным решениям. Выбирайте подход, который лучше всего соответствует вашему уровню владения Excel и сложности данных. И помните: Kutools для Excel предлагает мощные инструменты и удобные ярлыки, значительно упрощающие выполнение подобных задач в сложных книгах.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек