Как посчитать количество различных значений в столбце excel
Перейти к содержимому

Как посчитать количество различных значений в столбце excel

  • автор:

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

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

  • числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
  • строки «Руслан», «Сергей», «Сергей», «Сергей», будут найдены два уникальных значения — «Руслан» и «Сергей».

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

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

С помощью диалогового окна Расширенный фильтр можно извлечь уникальные значения из столбца данных и вставить их в новое местоположение. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

  1. Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
  3. Установите переключатель скопировать результат в другое место.
  4. В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно

для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите Развернуть диалоговое окно

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

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

  • Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
  • Вычислите сумму, используя функцию СУММ.
  • Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
  • Узнайте позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
  • Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

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

  • Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
  • Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите Вычислить формулу.

Описание функций

  • Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
  • Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, так как элемент 25 является вторым в диапазоне.
  • Функция ДЛСТР возвращает число символов в текстовой строке.
  • Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
  • Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как посчитать количество различных значений в столбце excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Подсчет Уникальных ТЕКСТовых значений в EXCEL

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений «а»,»а»,»а» >. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным . Соответственно, подсчитываются остальные значения, т.е. «а» и «b». Ответ очевиден: количество уникальных значений равно 2.

Задача

Произведем подсчет числа уникальных текстовых значений в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в диапазоне кроме текстовых значений содержатся также и числа, то формула подсчитает и их. Чтобы игнорировать числовые значения нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в список постоянно добавляются значения, то можно создать Динамический именованный диапазон Исходный_список .

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

Примечание : Уникальные значения в файле примера выделены с помощью Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL ).

Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст («») , а первая их игнорирует.

Приведем пример, когда это бывает важно.

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;»») определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в «пустых» ячейках С31:С34 на самом деле содержатся 4 значения «», которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.

СОВЕТ : Как подсчитать уникальные числовые значения показано в одноименной статье Подсчет уникальных числовых значений .

СОВЕТ : Как подсчитать уникальные числовые значения с дополнительными условиями (критериями) показано в статье Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL .

покупка

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

Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, теперь вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или отдельных значений (все разные значения в списке, это означает уникальные значения + 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% и сокращает количество щелчков мышью на сотни каждый день!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *