Подсчет Уникальных ТЕКСТовых значений в EXCEL

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


Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений {"а","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 .


Комментарии
Только для авторизованных пользователей

Аноним, 13 января 2016 г.
У меня 3 столбца. Первый — номера договоров, второй — дата заключения, третий — номера счетов. Соответственно номера договоров и даты заключения повторяются, если по договору более одного счета. Задача: подсчитать количество договоров раздельно по месяцам. Описанный способ не позволяет её решить, т.к. считаются уникальные номера договоров, а условие в соседнем столбце.
Аноним, 18 июля 2016 г.
Цитата: Сразу оговорюсь, что посчитать это один раз я могу: убираю дубликаты договоров, затем считаю их количество по периодам.<...>Задача в том, чтобы считалось все на "живом" реестре. На живом реестре, это значит, что сотрудники вносят в файл изменения (при том файл в совместном доступе) и я, открывая его вижу текущую картину. Договор может появится и в прошедшем месяце, и даже квартал назад. Каждый раз копировать весь реестр в отдельный файл (чтобы не попортить случайным сохранением), удалять неуникальные, и считать построив сводную, это нудно, и отнимает время. Цель — автоматизировать процесс и видеть картину не прибегая к таким манипуляциям каждый раз.
Михаил, 19 июля 2016 г.
Вы меня прям заинтриговали )) я добавил новый лист в файле примера статьи http://excel2.ru/articles/podschet-unikalnyh-znacheniy-s-usloviyami-v-ms-excel Теперь при добавлении нового договора в исходную таблицу, таблица-отчет обновляется автоматически. Пишите, что не так ))
Аноним, 5 августа 2016 г.
Неужели никто не проверял!!!! Функция СчетЕсли вернет массив с нулями если в диапазоне есть пустые ячейки, а далее дел на 0 - не работает ваше решение с пустыми ячейками
Аноним, 21 октября 2016 г.
а как заставить чтобы большие тексты тоже просматривал на уникальность?
Аноним, 21 октября 2016 г.
[id164323228|Дмитрий], не пашет
Михаил, 22 октября 2016 г.
Приведите пример "больших текстов". Непонятно.
Аноним, 9 марта 2017 г.
Благодарю, отличное решение! Вот только небольшой вопрос для себя: что это и для чего <>"" ?
Михаил, 10 марта 2017 г.
Если в диапазоне есть значение Пустой текст (""), то эти значения не подсчитываются: выражение <>"" как раз отфильтровывает эти значения. Если Вы уверены, что таких значений нет, то используйте формулу =СУММПРОИЗВ(ЕТЕКСТ(Исходный_список)/СЧЁТЕСЛИ(Исходный_список;Исходный_список))
Аноним, 24 апреля 2017 г.
Добрый день подскажите как подсчитать число уникальных значений в столбце с условием... Например в столбце есть данные одинаковые (номер региона) их несколько видов и нужно подсчитать для определенного региона.
Михаил, 26 апреля 2017 г.
Аноним, 16 июня 2017 г.
А можно так посчитать не вертикальные строки, а горизонтальные. И с таким условием, чтобы одну букву, например "У" считал как "1", а букву "В" считал как "0"?
Михаил, 18 июня 2017 г.
О подсчете символов см. статью http://excel2.ru/articles/podschet-simvolov-v-diapazone-v-ms-excel Горизонтальные строки - это столбцы )))
Аноним, 25 августа 2017 г.
Здравствуйте! Возможно ли привести данную конструкцию =СУММПРОИЗВ(ЕТЕКСТ(A7:A15)/СЧЁТЕСЛИ(A7:A15;A7:A15)) для поиска уникальных текстовых значений с разных листов. Причем, чтобы листы рассматривались как единый массив. Заранее благодарен!
Михаил, 25 августа 2017 г.
Нет, нельзя, массив должен быть связным (типа А5:В10). Нужно создать новый диапазон, содержащий массивы с разных листов. Про объединение массивов см. раздел http://excel2.ru/gruppy-statey/obedinenie-i-razdelenie-spiskov
Михаил, 30 августа 2017 г.
ну, точнее, Вертикальные строки - это столбцы ))))
Аноним, 4 сентября 2017 г.
Добрый день! У меня похожее вычисление как в примере с отгрузками в январе, только отгрузки в определенные даты. Что нужно, чтобы по каждой дате подсчитать количество уникальных значений?
Михаил, 4 сентября 2017 г.
С датой формулы в EXCEL работают как с числами/ Используйте первую формулу из раздела Решение
Аноним, 18 июня 2019 г.
Объясните, пожалуйста, логику построения этих формул. Я такие записи в первый раз вижу, к примеру, когда критерием в формуле счетесли является диапазон, а также в формуле суммпроизв диапазоны через делитель - это вообще что?)) Перерыл, мне кажется, весь интернет так ничего и не нашел. Буду благодарен ссылке на статью или гайд.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved