Здравствуйте. Сегодня у нас практикум, считаем количество уникальных значений в столбце Excel. Задача звучит проще, чем выполняется, поэтому, рассматриваем всевозможные ситуации и способы их решения.
Количество уникальных значений в списке
Если вы уверены, что в массиве с данными нет пустых ячеек, счет уникальных значений excel делаем так:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон ; диапазон))
Если применить такую формулу для массива с пустыми значениями, она вернет ошибку #ДЕЛ/0. Как это обойти – читайте дальше.
Считаем данные в массиве с пустыми ячейками
Давайте посчитаем количество уникальных значений Excel, когда в исходной выборке есть пустые клетки. Для этого сделаем лишь одну небольшую правку в формуле:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон ; диапазон &»»))
Легко заметить, что результат получился на 1 больше, чем в прошлом примере. Это потому, что формула посчитала и пустую ячейку, как уникальные данные. Чтобы этого не было, еще доработаем функцию:
=СУММПРОИЗВ((диапазон<>””)/СЧЁТЕСЛИ(диапазон ; диапазон &»»))
Теперь получили правильный результат без учёта пустот.
Следующие две формулы выглядят сложнее, но работают быстрее. Используйте их, если программа подвисает при выполнении.
Подсчет уникальных числовых данных
Чтобы в Excel посчитать количество уникальных значений в столбце с числами, используем функцию ЧАСТОТА:
=СУММ(—(ЧАСТОТА(диапазон ; диапазон)>0))
Количество уникальных строк с текстом
Похожая «скоростная» но для счёта текстовых строк:
=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ(диапазон; диапазон;0);СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))
Такая формула не подходит, когда в массиве есть пустые клетки. Если это критично, можно еще усложнить:
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ПОИСКПОЗ(диапазон; диапазон;0));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1);1))
Это формула массива, после ввода нажимайте не Enter, а Ctrl+Shift+Enter. Иначе работать не будет.
Выглядит монструозно, а результат верный. Можно пользоваться!
Пересчёт числовых значений с условием
Вы можете узнать, сколько значений в массиве, удовлетворяющих какому-то условию. Например, у меня есть таблица продаж, где указаны коды товара и продавцы, которые продавали. Меня интересует, сколько оригинальных позиций продавал менеджер Немятый.
Формула получилась такая:
=СУММ(—(ЧАСТОТА(ЕСЛИ(условие ; диапазон); диапазон)>0))
Это формула массива, подтверждаем ввод комбинацией Ctrl+Shift+Enter!
Счёт текстовых строк по условию
Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.
А формула такая:
=СУММ(—(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ЕСЛИ(условие;ПОИСКПОЗ(диапазон; диапазон;0)));СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))
Вот так можно организовать подсчет одинаковых значений в столбце Excel в самых распространенных случаях. Формулы универсальные, скопируйте их, вставьте свои данные и получите результат.
Это всё на сегодня, Задавайте ваши вопросы в комментариях. Понравилась статья – поделись с другом!