Подсчет Уникальных ТЕКСТовых значений в EXCEL
history 23 апреля 2013 г.
- Группы статей
- Подсчет на основании повторяемости
- Уникальные
- Подсчет текстовых значений
- Условное форматирование
Произведем подсчет уникальных текстовых значений в диапазоне ячеек.
Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений <"а","b", "а","а","а" >. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным . Соответственно, подсчитываются остальные значения, т.е. "а" и "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 .
Подсчет уникальных значений в 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 комментариев
ОГОНЬ, спасибо за эту статью, только на этом сайте смог найти решение под мою задачу подходящее.
У меня была таблица с фамилиями сотрудников в одном столбце и датами в другом столбце
Мне необходимо было отдельно посчитать по каждому сотруднику, сколько индивидуальных значений в поле дата есть по нему, иными словами, сколько рабочих дней он отработал.
Адаптировав блок Пересчёт числовых значений с условием под мою задачу получилось решить!
Дмитрий, спасибо за положительный отзыв. Рад, что смог Вам помочь!