Подсчет количества уникальных значений
Постановка задачи
Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:
Задача - подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта.
Рассмотрим несколько способов ее решения.
Способ 1. Если нет пустых ячеек
Если вы уверены, что в исходном диапазоне данных нет пустых ячеек, то можно использовать короткую и элегантную формулу массива:
Не забудьте ввести ее как формулу массива, т.е. нажать после ввода формулы не Enter, а сочетание Ctrl+Shift+Enter.
Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF). Если представить это в виде дополнительного столбца, то выглядело бы оно так:
Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:
Способ 2. Если есть пустые ячейки
Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):
Вот и все дела.
Ссылки по теме
- Как извлечь из диапазона уникальные элементы и удалить дубликаты
- Как подсветить дубликаты в списке цветом
- Как сравнить два диапазона на наличие в них дубликатов
- Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки 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 указываем условие с названием магазина)
Сначала сортируем диапазон 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.
Заранее спасибо!
(общее количество значений-дубли-доп)+дубли доп
(общее количество значений-доп)-дубли кроме доп
Это реально?
Как прописать формулу, чтобы игнорировались пустые ячейки?