Как считать уникальные значения в excel

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

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

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

Если вы уверены, что в массиве с данными нет пустых ячеек, счет уникальных значений excel делаем так:

Если применить такую формулу для массива с пустыми значениями, она вернет ошибку #ДЕЛ/0. Как это обойти – читайте дальше.

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

Считаем данные в массиве с пустыми ячейками

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

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

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

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

Теперь получили правильный результат без учёта пустот.

Следующие две формулы выглядят сложнее, но работают быстрее. Используйте их, если программа подвисает при выполнении.

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

Чтобы в Excel посчитать количество уникальных значений в столбце с числами, используем функцию ЧАСТОТА :

Количество уникальных чисел

Количество уникальных строк с текстом

Похожая «скоростная» но для счёта текстовых строк:

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ(диапазон; диапазон;0);СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

Такая формула не подходит, когда в массиве есть пустые клетки. Если это критично, можно еще усложнить:

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ПОИСКПОЗ(диапазон; диапазон;0));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1);1))

Это формула массива, после ввода нажимайте не Enter , а Ctrl+Shift+Enter . Иначе работать не будет.

Выглядит монструозно, а результат верный. Можно пользоваться!

Пересчёт числовых значений с условием

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

Формула получилась такая:

Это формула массива, подтверждаем ввод комбинацией Ctrl+Shift+Enter !

Счёт текстовых строк по условию

Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.

А формула такая:

=СУММ(—(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ЕСЛИ(условие;ПОИСКПОЗ(диапазон; диапазон;0)));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

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

Это всё на сегодня, Задавайте ваши вопросы в комментариях. Понравилась статья – поделись с другом!

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

2 комментариев

Дмитрий :

ОГОНЬ, спасибо за эту статью, только на этом сайте смог найти решение под мою задачу подходящее.
У меня была таблица с фамилиями сотрудников в одном столбце и датами в другом столбце
Мне необходимо было отдельно посчитать по каждому сотруднику, сколько индивидуальных значений в поле дата есть по нему, иными словами, сколько рабочих дней он отработал.

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

Александр Томм :

Дмитрий, спасибо за положительный отзыв. Рад, что смог Вам помочь!

Советы и лайфхаки по работе с Excel

Иногда в работе нам нужно посчитать уникальные значения в определенной колонке, однако Excel имеет функции, которые суммируют только количество записей в заданном поле, например функцияCOUNT(). Проблема в том, что один и тот же код товара или клиента может повторяться несколько раз.

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

Итак, давайте соединим функции SUM() — суммирует, IF() — проверка условия,FREQUENCY() — подсчитывает кол-во значений, попадающих в определенный интервал, LEN() — считает кол-во символов, MATCH() — ищет позицию элемента в массиве:

1. Вычисление количества уникальных числовых значений

=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1))

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

2. Вычисление количества уникальных числовых и текстовых значений (не работает, если есть пустые ячейки)

=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10;B2:B10;0);ПОИСКПОЗ(B2:B10;B2:B10;0))>0;1))

3. Вычисление количества уникальных значений (универсальная формула)

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1))

=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»);ЕСЛИ(ДЛСТР(A2:A10)>0;ПОИСКПОЗ(A2:A10;A2:A10;0);»»))>0;1))

Последнюю формулу нужно вводить как формулу массива, т.е. нажать не простоEnter, а Ctrl + Shift + Enter. После этого в строке формул мы увидим, что формула взята в фигурные скобки (<>), это признак того, что введенная формула массива.

Ссылка на основную публикацию