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

Обновлено: 06.07.2024

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

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

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

Но прежде чем начать, давайте проясним нашу цель.

Глядя на изображение выше, мы ясно видим, что мы подразумеваем под подсчетом уникальных значений в Excel.

Оглавление

Метод – 1: Использование формулы СуммаПроизведение и Счетесли:

Самый простой и легкий способ подсчета различных значений в Excel – использование формулы СуммаПродукт и СчитатьЕСЛИ.

Ниже приведена общая формула, которую вы можете использовать:

"данные" — данные представляют собой диапазон, содержащий значения

Давайте посмотрим, как работает эта формула:

Эта формула состоит из двух функций, объединенных вместе. Давайте сначала разберемся с ролью внутренней функции CountIF.

Функция CountIF просматривает диапазон данных (B2:B9) и подсчитывает, сколько раз каждое значение появляется в диапазоне данных.

Результатом является массив чисел, и в нашем примере он может выглядеть примерно так: .

Далее результаты формулы CountIF используются как делитель с 1 в качестве числителя. Это изменяет результат формулы CountIF таким образом, что; значения, которые появляются в массиве только один раз, становятся равными 1, а числа, соответствующие повторяющимся значениям, становятся дробями, соответствующими множеству.

Это означает, что если значение появляется в диапазоне 4 раза, то его значение станет равным 1/4 = 0,25, а значение, которое присутствует только один раз, станет равным 1/1 = 1

Наконец, формула SumProduct суммирует все элементы массива и возвращает окончательный результат.

Прочитайте эти статьи, чтобы узнать больше о функции CountIF или SumProduct.

Загвоздка:

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

Поскольку функция CountIF генерирует "0" для каждой пустой ячейки, а когда 1 делится на 0, она возвращает ошибку деления на ноль.

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

"данные" — данные представляют собой диапазон, содержащий значения

Метод 2: Использование формулы массива СУММ, ЧАСТОТЫ И ПОИСКПОЗ

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

Поэтому для больших наборов данных вы можете переключиться на формулу, основанную на функции ЧАСТОТА.

Общая формула выглядит следующим образом:

Здесь «данные» представляют собой диапазон, содержащий значения.

И firstcell представляет собой первую ячейку диапазона.

Примечание. Это формула массива, поэтому после написания формулы нажмите «Control-Shift-Enter», и формула будет заключена в фигурные скобки, как показано ниже.

Давайте попробуем понять, как работает эта формула:

Эта формула использует функцию частоты для подсчета уникальных значений, но проблема здесь в том, что функция ЧАСТОТА предназначена только для работы с числами. Итак, здесь наша первая цель — преобразовать значения в набор чисел.

Начиная изнутри, функция ПОИСКПОЗ в этой формуле дает нам первое вхождение или номер позиции каждого элемента, который появляется в диапазоне данных. Если есть повторяющиеся значения, функция ПОИСКПОЗ вернет только позицию первого появления этого значения в диапазоне данных.

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

Этот массив передается в функцию ЧАСТОТА, которая возвращает, как часто значения встречаются в наборе данных, и, наконец, внешняя функция ЕСЛИ устанавливает для каждого уникального значения значение 1, а для повторяющегося значения значение ЛОЖЬ.

И окончательный результат равен 4.

Способ – 3. Использование сводной таблицы (работает только в Excel 2013 и более поздних версиях)

Интеграция Power Pivot с Excel (известная как модель данных) предоставила пользователям ряд мощных функций. Теперь сводные таблицы также могут помочь вам получить различное количество уникальных значений в Excel.

Для этого выполните следующие действия:

1. Перейдите к параметру «Вставить» на верхней ленте и выберите параметр «Сводная таблица».

1. Откроется диалоговое окно "Сводная таблица", выберите диапазон данных и установите флажок "Добавить эти данные в модель данных" и, наконец, нажмите кнопку "ОК".

1. Создайте сводную таблицу, поместив столбец, содержащий диапазон данных «Значения», в квадрант «Строки», как показано ниже.

1. При этом будет показано общее количество, включая повторяющиеся значения, однако нам нужно получить количество уникальных значений. Итак, щелкните правой кнопкой мыши столбец "Число" и выберите параметр "Параметры поля значений".

1. Далее в окне "Параметры поля значений" выберите параметр "Число уникальных" и нажмите кнопку "ОК".

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

Метод 4: использование функций СУММ и СЧЁТЕСЛИ

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

Здесь «диапазон» представляет собой диапазон, содержащий значения.

Примечание. Это формула массива, поэтому после написания формулы нажмите «Control-Shift-Enter», и формула будет заключена в фигурные скобки, как показано ниже.

Давайте посмотрим, как работает эта формула:

В этой формуле мы использовали функцию ISTEXT. Функция ISTEXT возвращает значение true для всех текстовых значений и значение false для остальных значений.

Если значение является текстовым, функция СЧЁТЕСЛИ выполняется и просматривает диапазон данных (B2:B9) и подсчитывает, сколько раз каждое значение появляется в диапазоне данных.

После этого результат функции CountIF используется как делитель с 1 в качестве числителя (так же, как и в первом методе). Это означает, что если значение появляется в диапазоне 4 раза, его значение станет равным 1/4 = 0,25, а когда значение присутствует только один раз, оно станет равным 1/1 = 1

Наконец, функция СУММ вычисляет сумму всех значений и возвращает результат.

Способ 5 – функция COUNTUNIQUE, определяемая пользователем:

Если ни один из вышеперечисленных вариантов вам не подходит, вы можете создать собственную определяемую пользователем функцию (UDF), которая будет подсчитывать уникальные значения в Excel.

Ниже приведен код для написания собственной пользовательской функции, которая делает то же самое:

Чтобы внедрить эту функцию в Excel, выполните следующие действия:

1. Нажмите клавишу Alt + F11 в Excel, откроется окно VBA.
2. В окне VBA щелкните правой кнопкой мыши «Объекты Microsoft Excel» > «Вставить» > «Модуль».

1. Нажав «Модуль», вы откроете новый модуль в Excel. Вставьте код UDF в окно модуля, как показано ниже.

1. Наконец, сохраните таблицу, и формула готова к использованию.

Как использовать UDF:

Чтобы использовать UDF, вы можете просто ввести имя UDF ‘ CountUnique ’, как обычную функцию Excel.

'data_range' — представляет диапазон, содержащий значения.

count_blanks — логический параметр, который может принимать два значения true или false. Если вы установите для этого параметра значение true, пустые строки будут считаться уникальными. Если установить для этого параметра значение false, пользовательская функция исключит пустые строки.

Итак, это были все методы, которые могут помочь вам подсчитать уникальное значение в Excel. Сообщите нам свои собственные методы или приемы, чтобы сделать то же самое.

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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

Как подсчитать уникальные и уникальные значения в Excel

Уникальные значения – это значения, которые появляются в списке только один раз и не повторяются. Отдельные значения — это все разные значения в списке.

В этом примере у вас есть список чисел от 1 до 6. Уникальные значения — это те, которые появляются в списке только один раз, без каких-либо дубликатов. Отдельные значения — это все разные значения в списке. В таблицах ниже показаны уникальные и отличные значения в этом списке.

< бр />

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

Вы можете использовать комбинацию функций СУММ и СЧЕТЕСЛИ для подсчета уникальных значений в Excel. Синтаксис этой комбинированной формулы: = СУММ(ЕСЛИ(1/СЧЕТЕСЛИ(данные, данные)=1,1,0)). Здесь формула СЧЁТЕСЛИ подсчитывает, сколько раз появляется каждое значение в диапазоне.

Результирующий массив выглядит так. На следующем шаге вы делите 1 на полученные значения. Функция ЕСЛИ реализует логику таким образом, что если значения появляются в диапазоне только один раз, на этом шаге будет сгенерирована 1, в противном случае это будет дробное значение. Затем функция СУММ суммирует все значения и возвращает результат. Это формула массива, поэтому вы должны назначить ее, используя Ctrl + Shift + Enter.

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

< бр />

Это подсчитывает количество уникальных значений в списке. Он следует синтаксису, упомянутому выше, и возвращает количество уникальных элементов, равное 5.

Это подсчитывает количество уникальных числовых значений в списке. Единственное отличие от предыдущей формулы заключается в том, что здесь используется вложенная формула ISNUMBER, которая гарантирует, что вы подсчитываете только числовые значения. Возвращает число 3, которое представляет собой количество уникальных числовых значений.

Работает так же, как и предыдущая формула, вместо этого подсчитывает уникальное количество текстовых значений. Функция ISTEXT используется, чтобы убедиться, что подсчитываются только текстовые значения. Возвращает число 2.

Подсчет различных значений в Excel

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

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

• Выделите диапазон ячеек A1:A11.
• Выберите Данные > Сортировка и фильтрация > Дополнительно .

< бр />

• В диалоговом окне "Расширенный фильтр" нажмите "Копировать в другое место" .
• Задайте для диапазонов списка и диапазона критериев значение $A$1:$A$11 .
• Задайте для параметра Копировать значение $F$2 .
• Оставьте флажок "Только уникальные записи" установленным. Нажмите "ОК".

< бр />

• Столбец F теперь будет содержать уникальные значения.
• Выберите H6 .
• Введите формулу =СТРОКИ(F3:F9) . Нажмите «Ввод».

Это покажет количество уникальных значений, 7.

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

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

< бр />

Подсчитывает количество различных значений между ячейками с A2 по A11. Как и уникальный счет, здесь функция СЧЁТЕСЛИ возвращает количество для каждого отдельного значения, которое затем используется в качестве знаменателя для деления 1. Затем возвращаемые значения суммируются, если они не равны 0. Это дает вам количество различных значений, независимо от их типов.

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(A2:A11,A2:A11,0),ПОИСКПОЗ(A2:A11,A2:A11,0))>0,1))

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

=СУММ(ЕСЛИ(ЧАСТОТА(A2:A11,A2:A11)>0,1))

Подсчитывает количество различных числовых значений. Поскольку функция ЧАСТОТА игнорирует текст и пробелы, она возвращает 5, то есть количество числовых значений.

Возвращает количество различных текстовых значений в диапазоне. Как и в первом примере, здесь подсчитываются уникальные значения, но функция ISTEXT гарантирует, что учитываются только текстовые значения.

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

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

Чтобы подсчитать отдельные элементы с помощью сводной таблицы:

• Выберите ячейки A1:B11. Выберите «Вставка» > «Сводная таблица» .
• В появившемся диалоговом окне установите флажок «Новый рабочий лист» и «Добавить его в модель данных».

• Перетащите поле «Код продукта» в «Строки» , а поле «Имена» — в «Значения» в полях сводной таблицы.

< бр />

• Щелкните правой кнопкой мыши любое значение в столбце B. Перейдите к настройкам поля значения . На вкладке "Суммировать значения по" перейдите к полю "Суммировать значение по" и установите для него значение "Количество уникальных".

< бр />

Это покажет количество уникальных элементов 7 в ячейке B11.

< бр />

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

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

<р>1. Мы используем функцию СЧЁТЕСЛИ. Например, чтобы подсчитать количество пятерок, используйте следующую функцию.

<р>2. Чтобы подсчитать уникальные значения (не перегружайтесь), мы добавляем функцию SUM, 1/, и заменяем 5 на A1:A6.

<р>3. Завершите, нажав CTRL + SHIFT + ENTER.

Примечание. Строка формул указывает, что это формула массива, заключая ее в фигурные скобки <>. Не вводите их самостоятельно. Они исчезнут, когда вы отредактируете формулу.

Объяснение: Диапазон (константа массива), созданный функцией СЧЁТЕСЛИ, хранится в памяти Excel, а не в диапазоне. Константа массива выглядит следующим образом:

- (три семерки, одно солнце, одна луна, одна пятерка, три семерки, три семерки)

Эта константа массива используется в качестве аргумента для функции СУММ, что дает результат 1/3+1+1+1+1/3+1/3 = 4.

<р>4. Приведенная ниже формула массива подсчитывает количество значений, встречающихся ровно один раз.

Ширина

Объяснение: функция ЕСЛИ преобразует константу массива в .Эта константа массива используется в качестве аргумента для функции СУММ, что дает результат 3.

<р>5. Если у вас Excel 365 или Excel 2021, просто используйте волшебную функцию UNIQUE для извлечения уникальных значений.

Примечание: эта функция динамического массива, введенная в ячейку C3, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.

<р>6. Затем добавьте функцию COUNTA для подсчета уникальных значений в Excel 365/2021.

<р>7. Приведенная ниже формула подсчитывает количество значений, встречающихся ровно один раз.

Примечание: функция UNIQUE имеет 2 необязательных аргумента. Значение по умолчанию 0 (второй аргумент) указывает функции UNIQUE извлекать значения из вертикального массива. Значение 1 (третий аргумент) указывает функции UNIQUE извлекать значения, которые встречаются ровно один раз.

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

Как подсчитать уникальные значения в столбце Excel

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

< бр />

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

• это первая ячейка в столбце, с которого начинается счет.
• является последней ячейкой в ​​столбце, до которого ведется счет.

Например. Допустим, у нас есть список видов спорта и игроков, выигравших в каждом виде спорта. Выигравшие игроки перечислены в ячейках от B3 до B12. Формула для уникальных значений игроков будет выглядеть так:

Поместите эту формулу в ячейку, где вам нужно количество уникальных значений (скажем, в ячейку D3), и нажмите CTRL+SHIFT+ENTER. Это активирует формулу массива. Теперь щелкните в любом месте за пределами ячейки, и вы получите необходимое количество уникальных значений.

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

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

• это первая ячейка в столбце, с которого начинается счет.
• является последней ячейкой в ​​столбце, до которого ведется счет.

Например. Предположим, у нас есть список видов спорта и игроков, выигравших в каждом виде спорта. Выигравшие игроки перечислены в ячейках от B3 до B12. Формула для различных значений игроков будет выглядеть так:

Поместите эту формулу в ячейку, где вам нужно количество различных значений (скажем, в ячейку E3), и нажмите CTRL+SHIFT+ENTER. Это активирует формулу массива.

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

Надеюсь, это поможет!

Дата: 24 июля 2020 г. Метки: Excel

Похожие записи

Как использовать объединение в Excel для улучшения форматирования данных

Где скачать Microsoft Word, Excel, PowerPoint для Windows 11/10

[электронная почта защищена]

Каран Кханна — страстный пользователь Windows, который любит устранять неполадки Windows 11/10 в частности и писать о технологиях Microsoft в целом.

Читайте также:

  
• Установить Google как бесплатный браузер
  
• Китайские программы для андроид
  
• Скажите, что это за программа для Android
  
• Sfc не является внутренней или внешней командой для исполняемой программы или пакетного файла
  
• Безопасное программное обеспечение