Как подсчитать уникальные значения на основе нескольких критериев в Excel?
Во многих практических сценариях часто требуется не просто подсчитывать значения, но и определить, сколько уникальных элементов соответствует определенным условиям в ваших данных. Например, вы можете захотеть узнать количество различных продуктов, проданных конкретным продавцом, или сколько уникальных заказов было размещено в определенный период времени. Эффективное выполнение таких задач в Excel требует знакомства с подходящими формулами, продвинутыми функциями, такими как сводные таблицы, или даже пользовательскими решениями на VBA. В этой статье мы рассмотрим несколько практических методов для подсчета уникальных значений на основе одного или нескольких критериев, с пошаговыми инструкциями и советами.
Подсчет уникальных значений на основе одного критерия
Подсчет уникальных значений на основе двух заданных дат
Подсчет уникальных значений на основе двух критериев
Подсчет уникальных значений на основе трех критериев
Подсчет уникальных значений с помощью сводной таблицы (Distinct Count, Excel 2013+)
Подсчет уникальных значений с помощью кода VBA (для сложных/автоматизированных случаев)
Подсчет уникальных значений на основе одного критерия
Рассмотрим распространенный случай: вы хотите подсчитать, сколько разных продуктов было продано Томом. Этот метод подходит, если у вас есть простой набор данных, и вы стремитесь оценить уникальность на основе одного условия, такого как записи продаж одного человека. Это довольно просто, но требует тщательного использования формул массивов.
Для этого сценария введите следующую формулу в пустую ячейку (например, ячейку G2):
=СУММ(ЕСЛИ("Том"=$C$2:$C$20;1/(СЧЁТЕСЛИМН($C$2:$C$20; "Том"; $A$2:$A$20; $A$2:$A$20));0))
После ввода формулы нажмите Ctrl + Shift + Enter (не только Enter), чтобы подтвердить её как формулу массива. Фигурные скобки появятся вокруг формулы в строке формул, и вы сразу увидите результат, как показано ниже:
Примечание:
- “Том” — это условие, которое вы хотите использовать для фильтрации результатов. Вы можете заменить "Том" ссылкой на другую ячейку (например, $F$2), если вам нужна большая гибкость.
- $C$2:$C$20 содержит имена продавцов, которые нужно оценить.
- $A$2:$A$20 — это столбец продуктов, для которых вы хотите подсчитать уникальные значения.
- Если ваш диапазон данных изменяется, не забудьте соответственно скорректировать ссылки.
Совет: Если вы используете Excel 365 или Excel 2019 и выше, вы можете попробовать использовать функции UNIQUE и FILTER для более простых формул.
Если вы столкнетесь с ошибками #ДЕЛ/0!, дважды проверьте критерии и убедитесь, что ваши диапазоны равны по длине.
Подсчет уникальных значений на основе двух заданных дат
Когда вам нужно найти количество уникальных элементов в определенном диапазоне дат, например, все уникальные продукты, проданные между 2016/9/1 и 2016/9/30, вы можете применить этот подход. Это особенно полезно при анализе тенденций данных между определенными периодами, таких как месяцы, кварталы или пользовательские диапазоны дат. Однако будьте осторожны с форматированием дат; оно должно соответствовать значениям дат в вашей рабочей книге.
Поместите следующую формулу в пустую ячейку, где вы хотите отобразить результат:
=СУММ(ЕСЛИ($D$2:$D$20<=ДАТА(2016,9,30)*($D$2:$D$20>=ДАТА(2016,9,1));1/СЧЁТЕСЛИМН($A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&ДАТА(2016,9,30);$D$2:$D$20; ">="&ДАТА(2016,9,1)));0)
Нажмите Ctrl + Shift + Enter после ввода формулы, чтобы выполнить её как формулу массива. На снимке экрана ниже показан результат:
Примечание:
- 2016,9,1 и 2016,9,30 — это начальная и конечная даты критериев. Вы можете изменить их по мере необходимости или даже использовать ссылки на ячейки для динамических фильтров дат.
- $D$2:$D$20 содержит записи дат, которые необходимо проверить.
- $A$2:$A$20 снова является столбцом элементов или продуктов, которые вы хотите подсчитать уникально.
- Убедитесь, что ваши даты хранятся как действительные даты Excel, а не текстовые строки. Если ваш результат не отображается так, как ожидалось, проверьте форматирование дат и диапазоны.
Совет: Используйте ДАТА(год, месяц, день), чтобы избежать проблем с региональным форматированием дат. При использовании динамических диапазонов рекомендуется использовать именованные диапазоны для ясности.
Подсчет уникальных значений на основе двух критериев
Предположим, вы хотите проанализировать только те продукты, которые Том продал в сентябре, комбинируя имя и диапазон дат в вашем уникальном подсчете. Этот сценарий распространен при анализе производительности за период или сегментированных аналитических обзорах. По мере расширения критериев формула становится более сложной, и внимание к точности данных становится еще важнее.
Введите приведенную ниже формулу в любую пустую ячейку, например H2:
=СУММ(ЕСЛИ(("Том"=$C$2:$C$20)*($D$2:$D$20<=ДАТА(2016,9,30))*($D$2:$D$20>=ДАТА(2016,9,1));1/СЧЁТЕСЛИМН($C$2:$C$20; "Том"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&ДАТА(2016,9,30);$D$2:$D$20; ">="&ДАТА(2016,9,1)));0)
После ввода формулы подтвердите её с помощью Ctrl + Shift + Enter. Вы должны увидеть уникальный подсчет немедленно; проверьте следующую иллюстрацию:
Примечания:
- “Том” — это критерий имени, а “2016,9,1” и “2016,9,30” — это границы диапазона дат. Измените их по мере необходимости или сделайте их динамическими с помощью ссылок на ячейки.
- $C$2:$C$20 — это столбец сотрудников (или другой первый критерий); $D$2:$D$20 — это столбец дат; $A$2:$A$20 содержит уникальные элементы для подсчета.
- Диапазоны должны быть одинаковой длины во избежание ошибок.
Если вы хотите использовать условия «или», такие как подсчет уникальных продуктов, проданных Томом или в южном регионе, вы можете использовать следующую формулу. Это позволяет задавать более широкие условия поиска, хотя результаты могут пересекаться, если данные соответствуют обоим критериям:
=СУММ(--(ЧАСТОТА(ЕСЛИ(("Том"=$C$2:$C$20)+("Юг"=$B$2:$B$20); СЧЁТЕСЛИ($A$2:$A$20; "<"&$A$2:$A$20); ""); СЧЁТЕСЛИ($A$2:$A$20; "<"&$A$2:$A$20))>0))
Не забудьте нажать Ctrl + Shift + Enter. Вы увидите результаты, как показано ниже:
Совет: При применении условий «ИЛИ» учтите возможность двойного подсчета, если одна и та же запись удовлетворяет обоим условиям. Для больших наборов данных производительность может пострадать.
Подсчет уникальных значений на основе трех критериев
Иногда ваш анализ может потребовать трех или более условий, таких как определение уникальных продуктов, проданных Томом в сентябре только в северном регионе. Это часто встречается в многомерном анализе данных для создания отчетов или целевых бизнес-выводов. Тщательное управление ссылками важно при работе с таким составным логическим выражением.
Поместите эту формулу массива в пустую ячейку (например, I2):
=СУММ(ЕСЛИ(("Том"=$C$2:$C$20)*($D$2:$D$20<=ДАТА(2016,9,30))*($D$2:$D$20>=ДАТА(2016,9,1))*("Север"=$B$2:$B$20);1/СЧЁТЕСЛИМН($C$2:$C$20; "Том"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&ДАТА(2016,9,30); $D$2:$D$20; ">="&ДАТА(2016,9,1); $B$2:$B$20; "Север"));0)
Нажмите Ctrl + Shift + Enter, чтобы завершить. Вот пример результата для справки:
Для продвинутых условий дважды проверьте, что все диапазоны согласованы и типы данных (например, дата и текст) правильны. Несоответствия могут вызвать ошибки или ввести в заблуждение результаты.
Советы:
- Если вы столкнетесь с проблемами производительности на больших наборах данных, рассмотрите возможность разбиения формулы или использования сводной таблицы Excel.
- Именованные диапазоны или ссылки на ячейки для всех критериев улучшают читаемость и снижают вероятность ошибок в формулах.
- Для частого использования рассмотрите возможность записи этих формул в именованные ссылки на ячейки или пользовательские функции.
Подсчет уникальных значений с помощью сводной таблицы (Distinct Count, Excel 2013+)
Для пользователей Excel 2013 и выше сводные таблицы предлагают более интерактивную альтернативу без формул для подсчета уникальных значений по одному или нескольким критериям. Функция Distinct Count помогает быстро суммировать и фильтровать большие наборы данных, что делает этот метод особенно подходящим для динамичных, основанных на отчетах сред. Однако обратите внимание, что ранние версии Excel не поддерживают функцию Distinct Count в сводных таблицах.
Как использовать этот метод:
- Выберите свой набор данных и перейдите в меню Вставка > Сводная таблица.
- В диалоговом окне Создание сводной таблицы выберите место для размещения сводной таблицы, отметьте флажком «Добавить эти данные в модель данных» и нажмите ОК.
- Перетащите поле, которое вы хотите подсчитать уникально (например, Продукт), в область Значений. По умолчанию оно будет отображаться как «Количество...».
- Нажмите на поле в области Значений и выберите Настройки поля значений.
- В открывшемся диалоговом окне прокрутите вниз и выберите Distinct Count (Эта опция доступна только в Excel 2013 и выше и появляется, когда сводная таблица создана с включенной опцией «Добавить эти данные в модель данных»).
- Добавьте свои критериальные поля (например, Продавец, Регион, Дата) в область Фильтры или Строки/Столбцы, чтобы применить одно или несколько условий.
- Теперь ваша сводная таблица будет показывать уникальный подсчет значений, отфильтрованных выбранными критериями.
Преимущества: Очень наглядно, легко настроить фильтры без редактирования формул, идеально подходит для интерактивных отчетов.
Ограничения: Не доступно в Excel 2010 и ранее; добавление новых данных требует ручного обновления сводной таблицы.
Практический совет: Всегда убедитесь, что исходные данные не содержат дубликатов внутри одной записи, если они не предполагаются. Если вы не видите опцию Distinct Count, создайте сводную таблицу заново и убедитесь, что отмечен пункт «Добавить эти данные в модель данных».
Подсчет уникальных значений с помощью кода 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 предоставляют надежные альтернативы формулам массива. Выберите решение, наиболее подходящее для вашего уровня комфорта и сложности вашего набора данных. Помните, что Kutools for 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек