Подсчет количества уникальных значений
Постановка задачи
Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:
Задача - подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта.
Рассмотрим несколько способов ее решения.
Способ 1. Формула массива
Если вы уверены, что в исходном диапазоне данных нет пустых ячеек, то можно использовать короткую и элегантную формулу массива:
Не забудьте ввести её как формулу массива, т.е. нажать после ввода формулы не Enter, а сочетание Ctrl+Shift+Enter.
Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF). Если представить это в виде дополнительного столбца, то выглядело бы оно так:
Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:
Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):
В английской версии это будет:
=SUM(IF(ISEMPTY(A2:A10);0;1/COUNTIF(A2:A10;A2:A10)))
Ну, и наконец, если не охота заморачиваться с сочетанием клавиш Ctrl+Shift+Enter, то можно заменить функцию СУММ на аналогичную по смыслу (в данном случае) функцию СУММПРОИЗВ (SUMPRODUCT), которая понимает формулы массива "из коробки", т.е. при вводе обычным образом с помощью только Enter:
Вот и все дела.
Но на достаточно большой таблице (хотя бы несколько тысяч строк) такая формула будет тормозить нещадно, так что потребуются другие подходы.
Способ 2. Удаление дубликатов
Это хоть и рукопашный, но вполне рабочий вариант, если нужно быстро и единоразово оценить количество уникальных значений. Выделив исходные данные, идём на вкладку Данные - Удалить дубликаты (Data - Remove Duplicates). В открывшемся окне помечаем флажком столбцы, по которым нужно удалить повторы и после нажатия на ОК получаем сообщение с количеством оставшихся уникальных значений:
Просто, но минус в том, что при изменении исходных данных придётся повторять весь процесс.
Способ 3. Сводная таблица
Если построить по исходной таблице сводную, то можно поместить поле, по которому мы хотим подсчитать количество дубликатов, в область строк или столбцов и затем простой функцией СЧЁТЗ (COUNTA) посчитать количество значений в полученном списке (вычтя 2 лишних ячейки на заголовок и итоги):
Ещё красивее будет, если построить не простую сводную, а сводную по Модели Данных, включив в окне построения сводной соответствующий флажок через Вставка - Сводная таблица - Добавить эти данные в модель данных (Insert - Pivot Table - Add this data to Data Model). В этом случае можно закинуть искомый столбец уже в область значений и переключить функцию расчёта поля в подсчёт количества уникальных, щёлкнув по полю правой кнопкой мыши и выбрав команду Итоги по - Количество различных значений (Summarize Values By - Count Distinct):
Большим плюсом этого варианта будет возможность использовать другие области сводной таблицы для дополнительной фильтрации и детализации, например, по годам, категориям товаров и т.п.
Способ 4. Новая функция УНИК
В последних версиях, а именно начиная с Excel 2021 и в Excel 365, появилась поддержка динамических массивов и несколько новых мощных функций для работы с ними. Одна из этих функций УНИК (UNIQUE) - выводит массив уникальных значений из исходного списка. Если завернуть её снаружи в функцию подсчёта количества заполненных ячеек СЧЁТЗ (COUNTA), то получим простой и быстрый способ расчёта количества уникальных для таблицы любого размера:
Способ 5. Макрофункция на VBA
Если последней версии Excel у вас пока нет, а функцию УНИК иметь очень хочется, то вполне можно написать её аналог на VBA. Для этого выберите на вкладке Разработчик (Developer) команду Visual Basic или нажмите сочетание клавиш Alt+F11, чтобы открыть окно редактора макросов, вставьте туда новый модуль через меню Insert - Module и введите в него код следующей функции:
Function DistinctCount(dataRange As Range)
Dim coll As New Collection
Dim cell As Range
'если возникла ошибка при добавлении в коллекцию (т.е. добавляем дубль), то идём дальше
On Error Resume Next
'перебираем ячейки в исходном диапазоне
For Each cell In dataRange
'если ячейка не пустая, то пытаемся добавить её в коллекцию
If Not IsEmpty(cell) Then coll.Add cell.Value, CStr(cell.Value)
Next cell
DistinctCount = coll.Count
End Function
Эта функция принимает в качестве единственного аргумента диапазон, перебирает его ячейки и пытается добавить их в коллекцию. Если в коллекции уже есть такой элемент, то он игнорируется и, в итоге, мы получаем набор уникальных значений. Количество элементов в собранной коллекции - это и есть результат, который функция возвращает нам в итоге.
Использовать созданную функцию очень легко - она ничем не отличается от любой другой встроенной функции Excel:
Только не забудьте сохранить файл в формате с поддержкой макросов (xlsm или xlsb), чтобы не потерять созданный код.
Ссылки по теме
- Как извлечь из диапазона уникальные элементы и удалить дубликаты
- Как подсветить дубликаты в списке цветом
- Как сравнить два диапазона на наличие в них дубликатов
- Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки PLEX
=СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A10;A2:A10)^(2*ЕПУСТО(A2:A10)-1))
Для диапазона с пустыми ячейками:
1) В отдельной колонке вывел результат 1/Число вхождений, как на последней картинке в Способ 1
2) На эту колонку наложил функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;колонка)
В результате, когда применяю фильтр, получаю суммарное количество уникальных значений.
Недостаток один - если фильтр наложить по другим полям, получим неверное отображение количества уникальных значений.
=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(A2:A10;A2:A10;B2:B10;"Красный");0))
Вот что нашел на соседнем сайте. Эта формула у меня работет.
Тот же вопрос, например мне нужно посчитать кол-во уникальных значений, при том в определенном диапазоне дат.
Например Таблица содержит наименования продуктов и даты их выпуска )) Подскажите.. )8)
Дат в 2014 году было много, машин - тоже.....
Заранее спасибо!
Если версия более древняя, то прогнать список сначала через удаление дубликатов (Данные - Удалить дубликаты) с галочками Гос.№ и Дата, а потом построить сводную (Даты закинуть в строки, а Гос№ в значения).
Остался момент: в сводной таблице не получается найти кнопку "Уникальные значения". У меня 2013 эксель.
Потом в сводной щелкаете правой по полю значений - Параметры поля - Операции - Число различных элементов.
{=ЕСЛИ(G4<>"";СЧЁТЕСЛИ(ИНДЕКС(A1:A$2;ПОИСКПОЗ("ххх";G1:G$2)):A1;"<>"&A1:A$2);"")}Подскажите пожалуйста, как решить задачку. У меня к сожалению не получается самостоятельно.
Есть несколько строк из 10 чисел.
Нужно посчитать сколько раз одновременно в строку входят 2 или 3 других числа.
Заранее спасибо.
У меня на 35 тыс. строк (Офис 2013) не работает, выдает "0". При этом, ограничив в этом же массиве до 1500 строк, считает корректно
Имя Подсчет
Груша 1
Груша 2
Груша 3
Груша 4
Вместо такого, что выдает формула "счетесли"
Имя Подсчет
Груша 4
Груша 4
Груша 4
Груша 4
{=СУММ(--(ЧАСТОТА(ПОИСКПОЗ(A2:A10;A2:A10;0);СТРОКА(A2:A10)-СТРОКА(A2)+1)>0))}
И что ещё интереснее, так можно считать уникальные значения при наличии условия. Например, если в столбце B были бы названия магазинов (где, например, есть в наличии эти фрукты), можно написать формулу, считающую количество уникальных фруктов в данном магазине:
{=СУММ(--(ЧАСТОТА(ЕСЛИ(B2:B10=E1;ПОИСКПОЗ(A2:A10;A2:A10;0));СТРОКА(A2:A10)-СТРОКА(B2)+1)>0))}
(в ячейке E1 указываем условие с названием магазина)
Чтобы добавить ещё один критерий, напишите условия в скобках, разделённые знаком звёздочки: (условие1)*(условие2).
Например, так: "(ЕСЛИОШИБКА(ПОИСК(E1;B2:B10);0)>0) * (ЕСЛИОШИБКА(ПОИСК(E2;C2:C10);0)>0)"
Так же можно обработать ошибку, возникающую при пустых значениях в искомом поле - добавив условие …*(A2:A10<>"")
Сначала сортируем диапазон A1:A10 по возрастанию (или по убыванию, не важно). Потом вводим такую формулу массива:
{=СУММ(ЕСЛИ(A2:A10<>A1:A9;1;0))}
В этом варианте преимущество в том, что расчёт происходит очень быстро, даже если в исходнике много тысяч строк.
=СУММПРОИЗВ((A2:A10<>A1:A9)*(A2:A10<>"")
Должно получиться что-то типа такого:
Проблема в том, что надо искать каждое найденное в одном из двух столбцов по обоим столбцам. Если искать сначала в одном, затем во втором, затем сложить - по некоторым пунктам будет задвоение.
P.S.: Мысль посетила - может натолкнёт на идею: можно обнулять результаты счёта города до тех пор, пока не найден город с ненулевым числом рейсов. Для этого можно перемножать логическую 1 ("город найден";) на 1-1*(0 если есть хоть один ненулевой рейс, иначе 1).
Тогда если все рейсы нулевые, то найденный город помножится на 1-1*1 = 0 и не посчитается. Иначе помножится на 1-1*0 = 1.
{=СУММ(--(ЧАСТОТА(ЕСЛИ(B2:B10=E1;ПОИСКПОЗ(A2:A10;A2:A10;0));СТРОКА(A2:A10)-СТРОКА(B2)+1)>0))}
но не до конца. В моей таблице содержаться пустые ячейки.
Подскажите как можно это реализовать?
Спасибо заранее
=((СУММПРОИЗВ(СЧЁТЕСЛИ(B6:B35;B6:B35)^(2*ЕПУСТО(B6:B35)-1)))+0)
Далее не работает формула со ссылкой на эту ячейку
=(ВПР(B5;$D$5:$E$19;2;0))
Как можно исправить ситуацию?
Прошу помощи.
Моя формула выглядит так:
Проблема: формула уменьшает значение на "1" если приписка "доп" дублируется 2 и более раз, то есть если в столбце есть "доп123" и "доп1234" хочу чтобы вычитала 2, а если в столбце "доп123" и "доп123" хочу чтобы вычитала 1.
Заранее спасибо!
(общее количество значений-дубли-доп)+дубли доп
(общее количество значений-доп)-дубли кроме доп
Это реально?
Как прописать формулу, чтобы игнорировались пустые ячейки?
Прошу помощи. У меня есть огромная таблица. Мне нужно посчитать количество уникальных значений за определенную дату.
Формулы из статьи применить не смог. Удалять дубли стандартно нельзя.
Формула в ячейке D1
Для решения этой проблемы я создал дополнительный столбец в таблице и присвоил каждому уникальному значению 1, а остальным дублям 0. Таким образом при суммировании в сводной мы сможем увидеть только сумму уникальных. Ответ был кстати получен с помощью chatgpt. Сам я до него не додумался и на форумах ответ не нашел. А эта нейросетка сгенерила ответ за 2 мин.
=ЕСЛИ(ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;0;A2)=0;0;1)
В итоге можно построить сводную таблицу и сделать сумму по новому столбцу. Сумма будет считать только количество уникальных.
{=СЧЁТ(ЕСЛИ(ПОИСКПОЗ(A2:A10;A2:A10)>0;1;0))}
Прошу помощи знатоков! Может через VBA как-то можно это сделать.
Имеется - файл Эксель, 98 тысяч строк на каждом листе, а листов не более 20 шт.
Весит этот файл порядка 0,5 ГБ. Автовычисление выключено.
Задача: на каждом листе удалить повторяющиеся строки. Вручную не вариант, так как повторяющихся строк 106 шт из 98к строк. Необходимо чтобы из этих 106 осталось 53, и остальные неповторяющиеся значения.
Счетесли не работает, за 20 минут 0%
Подсвечивание дубликатов не работает - файл вылетает.
Сводная таблица не вариант.