Количество уникальных значений в Excel

5 способов посчитать количество уникальных значений в Excel с помощью формулы массива, сводных таблиц, новой функции УНИК и макроса.
00:38 - Формула массива
03:25 - Без Ctrl + Shift + Enter
04:12 - Пустые ячейки в данных
06:51 - Удаление дубликатов
07:29 - Уникальные в сводной таблицей
08:39 - Сводная по Модели данных
10:31 - Новая функция УНИК
11:56 - Макрофункция на VBA
Скачать пример www.planetaexc...
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Подпишитесь на наш канал в Telegram, чтобы оперативно узнавать о всех новых возможностях и лайфхаках при работе в Excel и Power BI - t.me/planetaexcel

[@UC0LMA1CQ6b3s6nrBlO4]

Я твой фанат ! Я так скучал по тоему контенту! Спасибо тебе и пусть Бог даст тебе и твоей семе мира, добра и здоровя!!!!

[@planetaexcel]

Спасибо! 🙏🙏🙏

[@VadimNikulinNIKUZAVR]

лучший

[@ASGol]

Очень рады, что Вы с нами, Николай! Благодарю Вас за ролик!

[@user-11_01]

Спасибо Вам за труд, уважаемый Николай! Я не представляю эксель без Вас😊 Вы мой кумир🎉🎉🎉

[@planetaexcel]

😁✌

[@nat29011985]

Спасибо! От вас можно учиться не только работе с ексель, но и подаче материала. Все красиво и вкусно. Эффективно и полезно. Да просто супер!

[@ДмитрийДобриков-к8з]

Приобрёл Plex на прошлой неделе. Доволен. Полезные функции за даром

[@planetaexcel]

Спасибо :)

[@ВикторОсадчий-ы5п]

Классно! Спасибо, Николай, за очередные полезности Excel ! Всех благ и удачи Вам!

[@planetaexcel]

Спасибо! Рад был помочь :)

[@SolutionsByPVV]

Как всегда, есть чему поучиться у Вас, Николай!

[@АркадийКурцев]

Огроменная благодарность Вам, Николай! Привет из Днепропетровска!

[@planetaexcel]

🙏🙂

[@АлексейЛ-ж4с]

Всё как всегда - доступно и понятно. Нужный контент! Большое Вам спасибо Николай, за ваш труд и наше обучение!

[@Пешков_Алексей_Максимович]

Спасибо Николай! Благодаря Вам, тысячи людей научились правильно работать в этой замечательной программе! До знакомства с Вашими роликами и книгами, мой максимум был - плюс, минус, равно! И я думал что так и надо работать😊

[@Феруз-п7в]

Уважаемый Николай. Спасибо Вам за ваш труд! Привет из Узбекистана

[@planetaexcel]

🙏🙏🙏

[@yaroslavcurl]

Ничего не понял, но очень интересно...)) На самом деле подчерпнул много иных подходов, но все гениальное - просто. Всё ждал этого решения, но нет... Всё решается одной функцией СЧЕТЕСЛИ из первого примера. В формуле (которая в ячейке B2) ячейку A2 нужно сделать условной ссылкой, то есть без долларов. Тогда, при протягивании вниз, верхние значения не будут считаться. Мы получим значение "1" на уникальных значениях, которые можно отфильтровать и посчитать просто выделив столбец... Не благодарите))

[@Midavok]

00:38 - Формула массива 03:25 - Без Ctrl + Shift + Enter 04:12 - Пустые ячейки в данных 06:51 - Удаление дубликатов 07:29 - Уникальные в сводной таблицей 08:39 - Сводная по Модели данных 10:31 - Новая функция УНИК 11:56 - Макрофункция на VBA

[@БекзодШавкиев]

Это супер! Николай спасибо!!! Выручили и сохранили нам кучу времени!

[@SamSambl4]

Спасибо за просвещение!

[@mahnush.alieva]

как же просто и доступно объясняете и показывате! столько искала как посчитать уникальные значения в больших массивах в два щелчка! спасибо огромное!!!!

[@resident_of_Leningrad]

Благодарю Вас за полезные уроки🤝🏻 Подача, подсказки, разъяснение, всего ровно в меру, как всегда.

[@Rusel80]

Приятно слушать профессионала своего дела. Спасибо большое за просвещение.

[@Оксана-х8д9с]

Как всегда все чётко, понятно и главное нужно! Спасибо вам огромное!

[@ОльгаСкворцова-ж8д]

Благодарю автора за исчерпывающий рассказ. Локаничность, точность, многовариантность. Испытала удовольствие от качества увиденного. Реально здорово!!!

[@Оксик-з2м]

Прекрасно! Спасибо❤

[@planetaexcel]

Не за что - рад был помочь :)

[@fenix7450]

Николай, Вы как знали. На днях задумался над модернизацией старого проекта менеджера паролей на VBA. Шифровал перестановочным шифром. Но в то время не стал сильно заморачиваться и продменные пароли генерировал на сайтах и проверял в Экселе с помощью функции "Удаление дубликатов", то есть раз новый пароль не исчез, значит он уникален ). Спасибо вам за познавательные материалы.

[@АлександрКонтарь]

Прекрасный ролик, как все, что делает уважаемый Николай. Но чего здесь не хватает (мне, по крайней мере) - подсчет количества уникальных ВИДИМЫХ ячеек (при фильтрации строк). Пока использую UDF, взятую с известного всем присутствующим сайта ;). Спасибо!

[@LonlyLokly]

Давно всю математику делаю в power query и power pivot, но спасибо за экскурс в формулы экселя

[@КимораКим]

Николай, спасибо огромное!!!

[@АрайБатырбекова-б8к]

Николай, спасибо Вам!

[@Lilly.Yakovlevna]

Супер, огромное спасибо. Пригодилось по работе 😊

[@BORISVLEONOV]

Николай, спасибо! Очень полезная функция. Была задача была у меня на работе: нужно было подсчитывать количество заказов (а в каждом заказе несколько строк), и показывать это над таблицей. Пользовался ужасно длинной формулой, которую встретил в ютубе, и формулу эту нужно было корректировать каждый раз при добавлении новых заказов в таблицу. Ваш вариант намного проще и удобнее и при применении для "умной таблицы" не нуждается в корректировке каждый раз.

[@planetaexcel]

Супер! :)

[@ГеоргийБелозор]

дякую, дуже допомогли!

[@planetaexcel]

Нема за що :)

[@Alexey_Qwerty]

Огромное спасибо!

[@yurd563]

Благодарю за видеурок!

[@АндрейГалин-г9ж]

Николай, спасибо! )

[@someone-there]

Спасибо! Полезно, как всегда.

[@НиколайЗубов-н6в]

Николаю, как всегда, большое спасибо. Пользователей же хотел предостеречь: у меня Счетз(Уник(Фильтр(...))) при отсутствии значений дает не ожидаемый Ноль, а Единицу. Тем самым сводя на нет этот способ подсчета. Возможно, есть способ решения, но я его пока не нашел. Точнее, нашел через дополнительную таблицу, но это уже не так красиво и эффективно.

[@kruzo_o]

Спасибо вам, Николай! УНИКа у нас нет, у нас есть Plex.

[@maxg2371]

А еще можно меру в сводной накатать с функцией distinctcount. А знаю я это благодаря некоему Николаю Павлову, крутому челу, чьи книги и ролики я читаю и смотрю уже долгое время 👍👍

[@planetaexcel]

🙏🙂

[@ДмитрийСорокин-ь9б]

Давно эту задачу решали через СЧЕТЕСЛИ и где номер вхождения был один вернуть значение строки, далее с помощью НАИМЕНЬШИЙ формировали массив уникальных значений. Сейчас же это просто сказка, а не работа 😀

[@babybmwer]

Когда знаешь все просто 🎉 вопрос откуда ты всё знаешь 😅 учился и продолжаю вдохновляться от видео.

[@ВладимирРубцов-т7о]

Спасибо за урок, Николай. А если немного усложнить таблицу с грузами: Добавить колонку "количество", и при этом грузы продублировать по разным вкладкам (по годам). Т.е. в каждой вкладке есть таблица с грузами и их количеством. Как собрать в отдельную таблицу все уникальные грузы и их общее количество со всех вкладок? Может быть даже количество тоже по годам разделить, чтобы можно было например на "складе" посмотреть, какие товары поступали на склад, в каком количестве и как менялась "популярность" товаров от года к году...

[@planetaexcel]

Если под вкладками вы понимаете листы, то собрать все таблицы по годам сначала с помощью Power Query в одну общую таблицу, а затем проанализировать эту всю кучу с помощью сводной. Я бы так делал, наверное :)

[@Shorek_chelvok]

спасибо за видео, актуальные задачи. только вчера думал почему в Plex в инструменте "Поиск дубликатов и уникальных" нет варианта "подсчитать количество уникальных", или даже в буфер обмена поместить значение расчетное ( или вариант список уникальных в буфер ;) иногда хочется) п.с. кстати в последней версии Plex, инструмент сравнение диапазонов подкрашивает ячейки соседнего столбца а не в искомом, при опции "залить цветом"

[@planetaexcel]

Да, мой косяк - исправлю в ближайшем обновлении до конца года ;)

[@Viacheslav_Ch]

Первый раз тут. И, очень впечатляет! Спасибо большое!

[@planetaexcel]

Спасибо, стараемся :)

[@alexeynaymushin2342]

Супер!!! Применил на р7.

[@planetaexcel]

Класс. И как там жизнь на Р-7?

[@Utesov4825]

Потрясно👌🙌🤝

[@SWooy...]

За мной кто-то следит, только мне понадобилось найти уникальные значения и тут в рекомендациях это видео...мы все под колпаком!!!!

[@identitynotestablished9133]

Как всегда спасибо за видео! Скажите, пожалуйста, будут еще видео по DAX?

[@planetaexcel]

Обязательно! Накидайте, кстати, интересных и актуальных вам тем ;)

[@ВикторОсадчий-ы5п]

@@planetaexcel Николай, был бы интересен для всех, наверное, с Вашей стороны ещё один урок✍, посвящённый примерам с использованием разных функций куба: CUBEVALUE(...), CUBEMEMBER(...), CUBEMEMBERPROPERTY(...), CUBESETCOUNT(...), CUBESET(...), CUBERANKEDMEMBER(...), CUBEKPIMEMBER(...) ;-)

[@MrTrylobeat]

​@@planetaexcelочень интересно как в даксе присвоить индекс по условию. Например индекс количества попыток прозвона на уникальный номер от старых к новым. Когда номеров больше миллиона. Партишн какой-нибудь

[@identitynotestablished9133]

Да все интересно, от банальных расчетов, которые встречаются в любом отчете, разбора функций до контекстов с моделированием)@@planetaexcel

[@hokmuns]

Как всегда доступно, вариант с PowerPivot самый полезный ИМХО, но есть нюанс что при "проваливании" в значение сводной "по умолчанию" таблица отдает первые 1000 строк. Так что если уникальных значений будет большее 1000 и user захочет быстро получить их список не досчитается данных😅

[@ФилиппИванов-щ7д]

Классно👍я через "если" и "впр" считал. Если искомого значения нет на "следе" т.е на протягиваемом диапаозоне то выводить искомое, если есть то пусто

[@planetaexcel]

Тоже вариант. Только ВПР - не очень быстрая функция, на большой таблице поттупливать будет, скорее всего.

[@MonaLakse]

Super.

[@an1man1ac]

оч круто

[@ДмитрийНикитин-и8о]

Странно, что Николай не показал решения этой задачи в Power Query. Можно посчитать количество уникальных грузов по формуле List.Count(List.Distinct(Источник[Груз])))

[@Anilid.]

Я обычно использую для этого такую формулу: =СЧЁТЕСЛИ( $G$3 : G4 ; G4 ) Суть в том, что диапазон поиска закреплён только сверху, т.е. при протягивании формулы на всю таблицу, первое вхождение элемента будет считаться только один раз. Если нужно кол-во уникальных эл-ов, то останется только посчитать кол-во единиц.

[@alexshavlo6701]

Хорошая задачка для собеседований

[@ВячеславИванов-с7ю]

Николай, прокомментируйте программу Р7. Мне кажется у нас на работе катастрофа. Сносят Excel и устанавливают аналог! Или не всё так страшно? Я уже про PQ. и PP.

[@ЕвгенийЧ-х7и]

Вы работаете в госконторе?

[@planetaexcel]

По моим ощущениям, из всех аналогов Р-7 - самый адекватный. Но ни Power Query, ни Power Pivot, ни нормальных макросов в нём нет. И как он будет тянуть большие и сложные книги с кучей тяжелых формул и связей - большой вопрос. Сочувствую.

[@Prelen_finance]

Спасибо за ролики! Подскажите как получить расчет итогов по встроенной формуле в сводной таблицы, где есть формула деления, если в ее строках есть нулевые значения, итоги не считаются. Может у Вас уже есть такое видео?

[@АлексейЛеонидович-ъ2ш]

Николай, спасибо большое. Подскажите пожалуйста, Вы планируете выпустить книгу про VBA?

[@planetaexcel]

В ближайших планах пока нет :)

[@АлексейЛеонидович-ъ2ш]

@@planetaexcel подождем)))

[@steelforcerock5803]

Здравствуйте, недавно начал смотреть вас, спасибо за вашу работу. Но, формула суммпроизвод у меня не работает, подскажите пожалуйста в чем проблема... Ваша формула на 5ой минуте.

[@moiskon]

Круто!

[@Nomerel]

Николай, а можно ли сделать также, только наоборот, чтобы показывалось сколько дубликатов имеется в массиве

[@planetaexcel]

А если просто вычесть из общего количества число уникальных?

[@axelrose3018]

интересно, а можно ли для вызова этого или любого другого макроса создать кнопку на панели инструментов и, чтобы при его вызове появлялось окошко для указания диапазона, как это обычно предлагает эксель ?

[@planetaexcel]

Да, конечно.

[@yerkebulantaskynbayev1490]

Можно в дашборд использовать запрос через power query.

[@po100lit7]

а ведь ещё можно в PowerQuery использовать Table.Profile =)

[@MrTrylobeat]

Я бы ещё по олдскуллу предложил отсортировать столбец для подсчёта уникальных от а до я, и затем в соседнем столбце формулу прописать, например в ячейке B2:= ЕСЛИ(A2=A1;"";1+СУММ($B$1:B1)) - протянуть на весь диапазон. А потом можно и просуммировать. когда знаешь всё просто😊

[@planetaexcel]

Тоже вариант.

[@УлугбекСаатов]

Здравствуйте! Спасибо за все! Можно вопрос? знаем что формула "СЦЕПИТЬ", например, несколько текст и число соединяет, а можно здесь цифры брал с определенной ячейки и дал результат что цифры с разделителем группы разрядов, чтоб читать удобно среди текстов? СПАСИБО БОЛЬШОЕ!!!

[@litwinczuk_jd]

0:40 Формула массива, функция COUNTIF, формула массива 3:25 использование функции SUMPRODUCT 4:13 Пустые ячейки в данных 6:50 Удалениемдубликатов 7:26 Уникальные в сводной таблице, функция 8:40 Сводная по модели данных 10:30 Новая функция UNIQUE 11:50 Макрофункция VBA

[@Midavok]

Николай, можно как-то закрепить мои комментарии с таймкодами? Мне кажеться это будет полезно для тех, кто будет пересматривать видео в будущем.

[@planetaexcel]

Закрепить не получилось, но скопировал ваши таймкоды в описание видео. Спасибо 🙏🙏🙏

[@user-lg9tg7zm4x]

Подскажите, как посчитать количество уникальных значений в сводной таблице, не считая пустых строк или нулей. Число разных элементов добавляет 1 если пустая или 0.

[@ytolk]

а можно загрузить в PQ -> Удалить дубликаты--> Подсчитать количество строк

[@Болеюрыбалкой]

Спасибо за ролик. Подскажите, под прошлым видео писал комментарий. Есть ли возможность что бы эксель используя изменяемые данные в 2х ячейках, поставлял заранее известный вариант в третью? Например я знаю используемую ширину заготовки и знаю качество обработки по параметрам. Так же я знаю сколько времени мне потребуется на 1 метр обработки при ширине Х и качестве У, а так же знаю количество времени которое нужно при ширине А и качестве В. Ну и так же ширина Х и качество В и наоборот то же возможны. Задача просто посчитать итоговое время для той или иной ширины при разном качестве обработки. Можно конечно сделать для каждого сочетания свою строку, но это не красиво и не удобно

[@alibeksartay8177]

Здравствуйте Подскажите при сборе всех вкладок через power query ексел сколько максимально строк может загрузить больше 500 000 строк это возможно?

[@666saturation7]

а Если нужно посчитать кол уникальных по признаку из другого столбца, который может быть не следующий, а через один ? что в таком случае можно сделать ?

[@ДмитрийНикитин-и8о]

У меня Power Pivot нет, но в скаченном файле с примером была функция посчитать итоги по числу разных элементов. Почему так?

[@ИванМомот-ы9ж]

Мне кажется, скоро в РФ не будет офиса. Вы участвуете в разработке "нашего" софта?

[@daryayurenka3077]

Добрый день, подскажите, пожалуйста, почему надстройка vba не работает? Фактически формула отображается при классическом вводе через "=", но при выборе диапазона выдаёт ошибку "имя".

[@ВячеславИванов-с7ю]

И ещё. Р7 - это сколько шагов назад?

[@xjHy4IVbu_d]

За одну жизнь столько не нашагать )

[@МаринкаМайер-н5н]

Николай, здравствуйте ! Подскажите пожалуйста, при построении сводной таблицы, выходит ошибка, что один из столбцов имеет слишком большое количество уникальных значений, и таблица не может быть построена или будет построена без него, есть ли какой-то выход?

[@planetaexcel]

У вас сводная на лист не влезает, судя по всему. Посмотрите по какому столбцу вы превышаете лимит - 1 млн строк и 16 тыс столбцов на листе.

[@janerogachyova9636]

Добрый день. Если в ячейке больше 255 символов, то формула счётесли не работает - выдает #знач (у нас выгрузке наименования образовательных организаций, поэтому столько символов).

[@Тимур-ь4ч]

=СЧЁТЗ(УНИК(A2:A)) или по уловию =СЧЁТЗ(УНИК(ФИЛЬТР(A2:A;B2:B="ФРУКТЫ")))

[@Лана-ъ7р]

Вспомнить все

[@adamadamov5787]

люди подскажите как посчитать, есть сумма которую нам надо собрать, есть общее количество жителей, из них часть пенсионеры, как посчитать по сколько каждой семье нужно скинутся, чтоб категория пенсионеров платила меньше? (сумма 6500000, домов 250 из них 60 пенсионеров)? спасибо

[@ЛилианЛилиан]

Всем добра! Ребята, кто сможет подскажите. Имеем: 1) в налии 800 файлов Еxcel (все файлы содержат однотипные таблицы. Кроме того, в файлах есть скрытые листы с формулами); 2) есть дополнительный сводный файл Excel, в котором прописаны ссылки на эти 800 файлов. Задача: из каждого из 800 файлов выудить и свести в одну общую таблицу 2 численных значения (значение температуры и давления). Вопрос: как это сделать?

[@edoi34]

Добрый день! У меня на компе не работает f4, что может заменить функции клавиша f4?

[@planetaexcel]

Попробуйте нажимать F4 удерживая клавишу Fn

[@ЕленаПрендзевская]

Где можно прочитать по Plex, хочу купить

[@planetaexcel]

www.planetaexcel.ru/plex/

[@Ilya.Vityugov]

Можно ли использовать "уник" в выподающем списке?

[@planetaexcel]

Для создания списка уникальных значений, который потом будет использоваться в качестве источника данных для выпадающего списка? Да, конечно! :)

[@Ilya.Vityugov]

@@planetaexcel Спасибо. Но я надеялся что можно напрямую как то проверку данных функцию видеть (как двыссыл) :)

[@ОлегПаламарчук-в9у]

Все знают, но хочу лишь напомнить: ещё через Power Query можно

[@МакаровАндрей-ш2з]

Почему excel 2019 года тормознутее 2007 года. У меня куча кнопок и 2019 вообще тупит.

[@planetaexcel]

Погуглите анекдот про "И сейчас мы со всем этим попробуем взлететь" :)

[@МакаровАндрей-ш2з]

@@planetaexcel может есть возможность отключать что то в 2019 для большей производительности. Типа визуализацию снизить.

[@1iuh]

УНИК ( ) можно обернуть в СОРТ ( ).

[@xjHy4IVbu_d]

Зачем УНИК оборачивать в СОРТ? Как СОРТ может помочь посчитать уникальные значения??

[@1iuh]

@@xjHy4IVbu_d Для сортировки того, что в УНИК.

[@xjHy4IVbu_d]

​​​​@@1iuh, можно конечно. А после сортировки можно ещё и с помощью функции ФИЛЬТР отфильтровать только те значения, которые оканчиваются на "нихренассекакойнеожиданныйповоротсобытий" и начинаются на "ёпырный театр!". Только ни то, ни другое к теме ролика не относится. От слова 'совсем'.

[@Andersen_911]

Like нумеро 90

[@Rice0987]

А где наши любимые Power Query и Lambda? :)

[@planetaexcel]

Ну, Power Query сюда можно, а вот лямбду с ходу не могу придумать как за уши притянуть :)

[@Rice0987]

@@planetaexcel Ну, из-за одного параметра заводить целый комбайн Power Query, наверное, не целесообразно, а вот с лямбдой можно было бы поиграться. С ней всегда интересно. :)

[@kirylsavitski3007]

меня забавит, что канал КХЛ подписан только на каналы хоккейных клубов и на этот канал 😄

[@planetaexcel]

😉👍

[@ВинниПых-п7з]

упс. начал смотреть про уникальные значения, а узнал про АЛТ =

[@ГеннадійПетровичКореновський]

Не понимаю. Какое практическое значение имеют такие примеры. Разве только для баловства, а может быть "хайпа". Ни больше, ни меньше. Спасибо.

[@yaroslavcurl]

Не понимаю зачем вы смотрите канал, в котором ничего не понимаете. Есть же Ольги Бузовы, Моргенштерны...

[@ГеннадійПетровичКореновський]

, а также "чайники"...

[@planetaexcel]

Ну, за хайпом я точно не охотник. Возможно, задачи, с которыми вы сталкиваетесь в Excel просто не требуют таких инструментов (пока). Баловством тут и не пахнет, поверьте - для очень многих это суровая ежедневная правда жизни :)

[@ГеннадійПетровичКореновський]

Допустим. В таком случае подготовьте и покажите для всех, если можно, прикладную задачу из реальной жизни применения данной темы.

[@BORISVLEONOV]

Очень прикладная задача была у меня на работе. Нужно было подсчитывать количество заказов (а в каждом заказе несколько строк), и показывать это над таблицей. Пользовался ужасно длинной формулой, которую встретил в ютубе, и формулу эту нужно было корректировать каждый раз при добавлении новых заказов в таблицу. Формула Николая намного проще и удобнее и при применении для "умной таблицы" не нуждается в корректировке каждый раз. @@ГеннадійПетровичКореновський