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

Легко подсчитывайте уникальные и различные значения – 7 примеров

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

Обычно в Excel уникальными значениями считаются те, которые встречаются в списке только один раз без дубликатов, а различные значения — это все Разное значение (уникальные значения + первые вхождения дубликатов). При работе с большим набором данных может понадобиться подсчитать количество уникальных и различных значений среди дубликатов в списке ячеек, как показано на приведённом ниже снимке экрана. В этом руководстве представлены быстрые способы подсчёта уникальных и различных значений в Excel.

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

Подсчитать количество уникальных значений в диапазоне в Excel

Подсчет уникальных значения (уникальные и первые вхождения дубликатов) в Excel

Подсчитать количество уникальных значений в диапазоне в Excel

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

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

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

образец данных

Для решения этой задачи поможет следующая формула массива:

Шаг 1: введите формулу

Введите или скопируйте приведённую ниже формулу в пустую ячейку, куда вы хотите вывести результат:

=SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))
Примечание. В приведённой выше формуле «A2:A12» — это список данных, для которого необходимо подсчитать уникальные значения.

Шаг 2: нажмите клавиши Ctrl + Shift + Enter, чтобы получить правильный результат:

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

Примечания:

  1. Если ваш список ячеек включает другие типы данных — например, числа, логические значения, ошибки и т.д., — приведённая выше формула вернёт количество уникальных значений независимо от их типа.
  2. Когда список данных содержит пустые ячейки, эта формула автоматически их исключает.
  3. Чтобы подсчитать только уникальные текстовые значения при наличии других типов данных, примените приведённую ниже формулу массива, а затем нажмите клавиши «Ctrl» + «Shift» + «Enter», чтобы получить результат:
    =SUM(IF(ISTEXT(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))
  4. Чтобы подсчитать только уникальные числовые значения из списка ячеек, содержащего любые типы данных, используйте следующую формулу массива, а затем нажмите клавиши «Ctrl» + «Shift» + «Enter», чтобы получить результат:
    =SUM(IF(ISNUMBER(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))

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

Вам может быть сложно запомнить формулы, когда придётся применять их в следующий раз. Однако с «Kutools для Excel» и её функцией «Подсчитать количество уникальных значений в диапазоне» в  «Помощнике формул» вы получите результат всего за несколько кликов. См. демонстрацию ниже:

  1. Щёлкните ячейку для вывода результата;
  2. Активируйте эту функцию, выбрав «Kutools» > «Помощник формул» > «Помощник формул» > «Статистические» > «Подсчитать количество уникальных значений в диапазоне»;
  3. Выберите список данных и нажмите «ОК».
    Подсчет уникальных значений с помощью Kutools
Советы:
  1. Чтобы воспользоваться этой функцией, необходимо загрузить и установить Kutools для Excel.
  2. Функция «Помощник формул» включает более 40 часто используемых формул, таких как «Извлечение уникальных значений», «Извлечение ячеек с уникальными значениями из диапазона (включая первые дубликаты)», «Подсчёт количества значений, разделённых запятыми, в ячейке», «Суммирование по одинаковому тексту» и многие другие.

Подсчитать количество уникальных значений в диапазоне в Excel 365/2021 с формулами

Если вы используете Excel 365 или Excel 2021, вам доступна новая функция UNIQUE, которая упрощает создание формул для подсчёта количества уникальных значений в диапазоне набора данных.

Например, чтобы подсчитать количество уникальных имён в диапазоне A2:A12, введите следующую формулу：

Шаг 1: скопируйте или введите приведённую ниже формулу

=IFERROR(ROWS(UNIQUE(A2:A12,,TRUE)), 0)
Примечание. В этой формуле «A2:A12» — это список данных, для которого необходимо Подсчитать количество уникальных значений в диапазоне.

Шаг 2: нажмите клавишу Enter, чтобы получить результат:

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

Советы:

  1. Если список данных включает различные типы значений — текст, числа, логические значения, ошибки и другие, — эта формула точно подсчитает количество уникальных элементов, независимо от их типа.
  2. Приведённая выше формула подсчитывает количество уникальных значений в диапазоне, включая пустые ячейки. Если вы хотите исключить пустые ячейки, примените приведённую ниже формулу и просто нажмите клавишу «Enter»:
    =SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))

Подсчет уникальных значения (уникальные и первые вхождения дубликатов) в Excel

Чтобы подсчитать разные значения (уникальные элементы и первые вхождения дубликатов) в списке ячеек, ниже приведены дополнительные формулы для решения этой задачи.

Подсчет уникальных значения с формулами

В Excel вы можете использовать любую из приведённых ниже формул, чтобы получить количество уникальных значений.

Шаг 1: введите любую из приведённых ниже формул

Формула 1: после ввода формулы нажмите клавишу Enter.

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

Формула 2: после ввода формулы нажмите сочетание клавиш «Ctrl» + «Shift» + «Enter».

=SUM(1/COUNTIF(A2:A12,A2:A12))
Примечание. В этих формулах  «A2:A12» — это список данных, для которого необходимо подсчитать Разное значение.

Результат:

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

Советы:

  1. Если список данных включает другие типы значений — например, числа, логические значения, ошибки и т.д., — эта формула подсчитает количество уникальных элементов независимо от их типа.
  2. При использовании приведённой выше формулы вы получите ошибку #DIV/0, если список данных содержит пустые ячейки. Чтобы исправить это и игнорировать пустые ячейки, примените одну из приведённых ниже формул:
    Формула 1: после ввода формулы нажмите клавишу «Enter».
    =SUMPRODUCT((A2:A12<,>,"")/COUNTIF(A2:A12,A2:A12&,""))
    Формула 2: после ввода формулы нажмите клавиши «Ctrl» + «Shift» + «Enter».
    =SUM(IF(A2:A12<,>,"",1/COUNTIF(A2:A12, A2:A12), 0))

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

  3. Чтобы получить количество различных текстовых значений в списке данных, примените приведённую ниже формулу массива и нажмите клавиши «Ctrl» + «Shift» + «Enter», чтобы получить результат:
    =SUM(IF(ISTEXT(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
  4. Чтобы подсчитать только различные числовые значения, используйте следующую формулу массива и нажмите клавиши «Ctrl» + «Shift» + «Enter», чтобы получить результат:
    =SUM(IF(ISNUMBER(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))

Подсчет уникальных значения всего за несколько кликов с помощью Kutools для Excel

Если вы часто используете формулы в своей книге, запомнить их для дальнейшего применения бывает непросто. Однако с надстройкой «Kutools для Excel» и её функцией «Подсчёт ячеек с уникальными значениями (включая первые дубликаты)» из раздела «Помощник формул» вы получите нужный результат всего за несколько кликов. См. демонстрацию ниже:

  1. Щёлкните ячейку для вывода результата;
  2. Активируйте эту функцию, выбрав «Kutools» > «Помощник формул» > «Статистические» > «Подсчёт уникальных ячеек с уникальными значениями (включая первые дубликаты)»;
  3. Выберите список данных и нажмите «ОК».
    Подсчет различных значений с помощью Kutools
Советы:
  1. Чтобы использовать эту функцию, необходимо загрузить и установить Kutools для Excel.
  2. Функция «Помощник формул» включает 40+ часто используемых формул, таких как «Извлечение уникальных значений», «Извлечь ячейки с уникальными значениями из диапазона (включая первые дубликаты)», «Подсчитать количество значений, разделенных запятыми, в ячейке», «Суммирование по одинаковому тексту» и другие…

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

В Excel сводная таблица также поможет быстро получить количество уникальных значений из списка данных. Выполните следующие шаги:

Шаг 1: создайте Сводная таблица

  1. Выберите список данных, затем на ленте нажмите «Вставка» > «Сводная таблица» (см. снимок экрана).
    создание сводной таблицы для выделенных данных
  2. В появившемся диалоговом окне «Создание сводной таблицы из диапазона или таблицы»:
    (1). Выберите «Новый лист» или «Существующий лист», куда вы хотите поместить сводную таблицу;
    (2). Установите флажок «Добавить эти данные в модель данных»;
    (3). Нажмите кнопку «ОК».
    настройка параметров в диалоговом окне «Сводная таблица из таблицы или диапазона»

Шаг 2: настройте поля и выберите опцию «Уникальное количество»

  1. Отобразится область «Поля сводной таблицы». Перетащите поле «Имена» в область «Значения» — см. демонстрацию ниже:
    Настройка полей для сводной таблицы
  2. Затем щелкните раскрывающийся список «Количество по Именам» и выберите «Параметры значений Настройки полей» (см. снимок экрана).
    нажмите параметр «Параметры поля значений»
  3. Откроется диалоговое окно «Параметры значений: Настройки полей». На вкладке «Итоги по» выберите «Уникальное количество» и нажмите «ОК» (см. снимок экрана).
    выберите опцию «Уникальное количество»

Результат:

Теперь созданная Сводная таблица отобразит уникальное количество элементов в списке данных, как показано на приведённом ниже снимке экрана:

Результат сводной таблицы для подсчета различных значений

Советы:

  1. Если вы обновите исходные данные, чтобы получить актуальные значения, просто щёлкните правой кнопкой мыши по сводной таблице и выберите «Обновить».
  2. Опция «Подсчёт различных значений» доступна только в Excel 2013 и новее.

Подсчет уникальных значения в Excel 365/2021 с формулами

В Excel 365 или Excel 2021 вы можете легко создать простую формулу, используя новую функцию UNIQUE в сочетании со стандартной функцией COUNTA.

Скопируйте или введите приведённую ниже формулу в пустую ячейку и нажмите клавишу «Enter», чтобы получить результат:

=COUNTA(UNIQUE(A2:A12))
Примечание. В этой формуле «A2:A12» — это список данных, для которого необходимо Подсчет уникальных значения.

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

Советы:

  1. Если список данных включает разные типы значений — текст, числа, логические значения, ошибки и т.д., — эта формула подсчитает количество уникальных элементов, независимо от их типа.
  2. Приведённая выше формула подсчитывает уникальные значения, включая пустые ячейки. Чтобы исключить пустые ячейки, используйте приведённую ниже формулу и нажмите клавишу «Enter», чтобы получить результат:
    =COUNTA(UNIQUE(FILTER(A2:A12, A2:A12<,>,"")))

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

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

  • Подсчитать количество уникальных значений в диапазоне в Сводная таблица
  • По умолчанию при создании сводной таблицы на основе диапазона данных, содержащего дублирующиеся значения, учитываются все записи. Однако иногда для получения корректного результата необходимо подсчитать уникальные значения только по одному столбцу. В этой статье рассказывается, как подсчитать уникальные значения в сводной таблице.
  • Объединение уникальных значений
  • Если у меня есть длинный список значений с дубликатами, как быстро и легко найти только уникальные значения и объединить их в одну ячейку в Excel?