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

Подсчет уникальных значений в Excel — 5 эффективных способов.

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

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

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

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

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

Оглавление.

Метод — 1: Использование формулы SumProduct и CountIF:

Самый простой и легкий способ подсчета различных значений в Excel — использовать формулы SumProduct и CountIF..

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

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

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

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

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

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

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

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

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

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

Улов:

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

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

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

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

Метод — 2: Использование формулы массива SUM, FREQUENCY и MATCH.

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

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

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

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

«Firstcell» представляет первую ячейку диапазона.

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

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

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

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

После функции ПОИСКПОЗ идет оператор ЕСЛИ. Причина, по которой функция IF требуется, потому что ПОИСКПОЗ вернет ошибку # Н / Д для пустых ячеек. Итак, мы исключаем пустые ячейки с данными <> "" .

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

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

И в итоге получается 4.

Метод — 3: Использование сводной таблицы (работает только в Excel 2013 и выше)

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

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

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

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

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

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

Затем в окне «Параметры поля значения» выберите параметр «Различное количество» и нажмите кнопку «ОК»..

Это приведет к получению различных счетчиков значений и их заполнению в сводной таблице..

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

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

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

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

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

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

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

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

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

Метод 5 — функция, определяемая пользователем COUNTUNIQUE:

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

Ниже приведен код для написания собственного UDF, который делает то же самое:

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

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

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

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

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

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

«Диапазон_данных» — представляет диапазон, содержащий значения.

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

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

Рекомендуемая литература.

Что ж, я Анкит Каул, основатель Excel Trick. Я убежденный поклонник Microsoft Excel и работаю с электронными таблицами более 10 лет. Моя единственная цель — превратить вас, ребята, в «гиков Excel». Узнайте больше обо мне здесь.

Похожие статьи