KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как подсчитать количество уникальных значений в диапазоне, игнорируя дубликаты, в Excel?

АвторSiluviaДата изменения

При работе со списками данных в Excel — особенно с именами, идентификаторами или другими повторяющимися записями — часто возникает необходимость определить, сколько значений встречается ровно один раз. Иными словами, требуется подсчитать действительно уникальные значения, полностью исключая любые дубликаты. Представьте, например, список имён, где некоторые из них повторяются. Если ваша цель — узнать, сколько имён встречаются в этом списке только один раз (игнорируя все, что указано дважды или чаще), Excel предлагает несколько эффективных способов решить эту задачу. В данной статье подробно описаны различные методы достижения этой цели: с использованием формул, VBA, сводных таблиц и встроенных функций. Кроме того, для каждого подхода рассматриваются его преимущества и возможные ограничения, чтобы вы могли легко выбрать наиболее подходящий вариант для своей конкретной ситуации.

Подсчитать количество уникальных значений в диапазоне без учета дубликатов с помощью формулы
Легко Подсчитать количество уникальных значений в диапазоне без учета дубликатов с помощью мощного инструмента
Подсчитать количество уникальных значений в диапазоне без учета дубликатов с помощью кода VBA
Подсчитать количество уникальных значений в диапазоне без учета дубликатов с помощью Сводная таблица

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

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

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

1. Щелкните, чтобы выбрать пустую ячейку для размещения результата. Следуя рекомендациям, выберите ячейку за пределами вашего набора данных — это поможет избежать случайной перезаписи информации.

2. Введите следующую формулу и нажмите Enter, чтобы отобразить количество уникальных значений, встречающихся только один раз (исключая все значения, которые встречаются более одного раза):

=SUM(IF(FREQUENCY(MATCH(B3:B14,B3:B14,0),ROW(B3:B14)-ROW(B3)+1)=1,1))

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

Примечания:
1) В приведённой выше формуле B3:B14 — это диапазон, содержащий анализируемые значения. Измените ссылку на диапазон в соответствии с вашими фактическими данными. Диапазон может охватывать любое количество строк.
2) Для формул массива в старых версиях Excel может потребоваться нажать Ctrl + Shift + Enter после ввода формулы вместо обычного нажатия Enter. В Excel 365 и Excel 2019 или новее достаточно просто нажать Enter.
3) Особое внимание уделите пустым ячейкам в диапазоне, так как они могут повлиять на результат. Старайтесь очищать набор данных или используйте формулу только для непустых списков, чтобы получить точные результаты.
4) Если ваш диапазон данных очень велик, вычисление по формуле может замедлиться — в этом случае рассмотрите другие решения, приведённые ниже.

Сценарий и плюсы/минусы:

  • Хорошо работает со стандартными, небольшими и средними диапазонами.
  • Не требует надстроек — это встроенная функция Excel.
  • Возможно, потребуется ввести формулу массива; при работе с очень большими наборами данных производительность может снижаться.

 

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

Kutools для Excel предлагает интуитивно понятную функцию Подсчитать количество уникальных значений в диапазоне, которая мгновенно определяет значения, встречающиеся в списке ровно один раз — без сложных формул и утомительной ручной работы. Этот метод идеально подходит пользователям, ценящим простоту и гибкость, и легко справляется даже с крупными наборами данных.

1. Выберите пустую ячейку для отображения результата, убедившись, что она не перезапишет существующие данные. Затем перейдите в меню Kutools > Помощник формул > Помощник формул.

снимок экрана включения помощника формул

2. В диалоговом окне Помощник формул выполните следующие действия:

  • Найдите и выберите Подсчитать количество уникальных значений в диапазоне из списка Выберите формулу.
    Совет: Используйте поле Фильтр, чтобы быстро найти нужное — просто введите ключевые слова, связанные со словом «уникальный».
  • Укажите диапазон, содержащий данные для анализа.
  • Нажмите ОК, чтобы вставить функцию и отобразить количество значений, встречающихся в списке только один раз.

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

Этот подход мгновенно определяет количество уникальных значений, избавляя от необходимости вручную считать или разбираться с ошибками в формулах. Результат показывает число элементов, встречающихся в выделенном диапазоне ровно один раз:

снимок экрана с результатом

Советы и примечания:

  • С Kutools вам не придётся вводить сложные формулы и переживать из-за ошибок в них.
  • Этот инструмент поддерживает как смежные, так и несмежные диапазоны.
  • Если вы часто проводите такой анализ данных, Kutools поможет вам значительно сэкономить время и сократить количество ошибок.
Сценарий и плюсы/минусы:
  • Идеальный выбор для тех, кто ищет быстрый и надёжный способ решения задачи.
  • Обрабатывает большие диапазоны эффективнее, чем формулы.
  • Kutools необходимо установить и активировать перед началом использования.

 

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

В сценариях, где требуется автоматизировать задачу или многократно подсчитывать количество уникальных значений в диапазоне, встречающихся ровно один раз — в пределах нескольких листов или книг, — целесообразно использовать VBA (Visual Basic for Applications). Это решение позволяет точно определить элементы, присутствующие в вашем диапазоне только один раз, игнорируя все, что встречается более одного раза.

Применимые сценарии:

  • Автоматизация процесса для больших или нескольких наборов данных
  • Интеграция в макросы Excel или пакетные процессы
  • Пользователи, знакомые с основами работы с VBA
Плюсы/Минусы:
  • Высокая гибкость, возможность повторного использования в будущих Анализ данных
  • Возможность настройки для улучшенной отчетности
  • Требует базового знания VBA; код необходимо добавлять вручную

 

Шаги:

1. Откройте редактор VBA: нажмите Инструменты разработчика > Visual Basic. В новом окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль.

2. Вставьте приведённый ниже код в окно модуля:

Sub CountUniqueOnlyOnce()
    Dim WorkRng As Range
    Dim cell As Range
    Dim dict As Object
    Dim singleCount As Long
    Dim Key As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select range to count unique and non-duplicate values:", xTitleId, WorkRng.Address, Type:=8)
    
    If WorkRng Is Nothing Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In WorkRng
        If Not IsEmpty(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        End If
    Next cell
    
    singleCount = 0
    
    For Each Key In dict.Keys
        If dict(Key) = 1 Then
            singleCount = singleCount + 1
        End If
    Next Key
    
    MsgBox "Count of unique values that appear only once: " & singleCount, vbInformation, "Result"
End Sub

3. Нажмите кнопку Кнопка запускаили клавишу F5, чтобы запустить код. Появится диалоговое окно с запросом на выбор диапазона данных. После выбора и подтверждения в окне сообщения отобразится количество уникальных элементов, встречающихся только один раз.

Меры предосторожности и устранение неполадок:

  • Обязательно сохраните книгу перед запуском макросов.
  • По умолчанию этот код игнорирует пустые ячейки.
  • Если параметры безопасности Excel блокируют выполнение макросов, измените их в Центре управления безопасностью через Параметры Excel.
  • Если возникает ошибка, убедитесь, что выделен корректный диапазон — только один столбец или одна строка.

 

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

Сводная таблица предоставляют гибкий и интерактивный способ сводного анализа данных в Excel, и их можно использовать для подсчёта количества различных элементов, встречающихся только один раз в вашем Диапазон данных. Это решение особенно полезно, если вы уже используете Сводная таблица для других видов анализа или предпочитаете маршрут без формул, управляемый через меню. Оно идеально подходит пользователям, работающим с большими наборами данных или желающим создавать динамические и фильтруемые отчёты.

Преимущества:

  • Легко обновляется простым обновлением Сводная таблица
  • Наглядный интерфейс со встроенными функциями фильтрации и сортировки
  • Не требует формул

 

Шаги:

  • Выделите весь диапазон ваших данных, включая столбец со значениями, которые необходимо проверить.
  • Перейдите в меню Вставка > Сводная таблица. В появившемся окне выберите место размещения сводной таблицы — новый или существующий лист.
  • В области Поля сводной таблицы перетащите заголовок столбца (например, «Имя») одновременно в область Строки и ещё раз — в область Значения. В области «Значения» убедитесь, что выбрано Количество (если нет — щёлкните и измените тип вычисления с «Сумма» или другого на «Количество»).
  • Сводная таблица отображает каждый элемент и количество его вхождений. Чтобы показать только те элементы, которые встречаются один раз, используйте раскрывающийся список фильтра в столбце с количеством: выберите Числовые фильтры > Равно > 1. Таблица автоматически отфильтруется, оставив только значения, встречающиеся ровно один раз.
  • Подсчитайте оставшиеся видимые элементы — их количество и есть число уникальных (неповторяющихся) значений.

Меры предосторожности и устранение неполадок:

  • Пустые ячейки будут отображаться отдельно в сводной таблице; возможно, их потребуется отфильтровать.
  • Обновляйте сводную таблицу после изменения или обновления исходных данных.
  • Сводные таблицы отлично справляются с большими объемами данных, но не обновляются автоматически в реальном времени без ручного вмешательства.
  • Если вы хотите автоматизировать подсчёт отфильтрованных значений, используйте функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ или СЧЁТЗ для создания сводной таблицы.
Сценарий и плюсы/минусы:
  • Отлично подходит для сводной отчетности, анализа больших объемов данных или ситуаций, когда нужно видеть актуальный список уникальных элементов.
  • Требует нескольких шагов, но обходится без формул.

 

Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.

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

 

 

 

  Kutools для Excel включает более 300 мощных функций для Microsoft Excel. Попробуйте бесплатно без ограничений в течение 30 дней! Скачать сейчас!

Связанные статьи:

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал 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.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек