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

Подписаться 226 тыс.

Просмотров 30 тыс.

50% 1

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

Опубликовано:

9 ноя 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 137

@UC0LMA1CQ6b3s6nrBlO4 6 месяцев назад

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

@planetaexcel 6 месяцев назад

Спасибо! 🙏🙏🙏

@VadimNikulinNIKUZAVR 6 месяцев назад

лучший

@ASGol 6 месяцев назад

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

@user-11_01 6 месяцев назад

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

@planetaexcel 6 месяцев назад

😁✌

@nat29011985 6 месяцев назад

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

@user-vi5ey4rg8b 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-gt9ot4ot4l 6 месяцев назад

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

@SolutionsByPVV 6 месяцев назад

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

@user-xp4zt4kb6l 6 месяцев назад

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

@planetaexcel 6 месяцев назад

🙏🙂

@user-kv2fx4jo8l 6 месяцев назад

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

@user-lm5ji3pz1n 6 месяцев назад

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

@planetaexcel 6 месяцев назад

Спасибо :)

@yaroslavcurl 6 месяцев назад

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

@user-gk6zr6ql7g 6 месяцев назад

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

@planetaexcel 6 месяцев назад

🙏🙏🙏

@mahnush.alieva Месяц назад

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

@user-yw9tp8pd5b 6 месяцев назад

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

@resident_of_Leningrad 6 месяцев назад

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

@Midavok 6 месяцев назад

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

@Rusel80 6 месяцев назад

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

@user-wv5bo1lj5i 6 месяцев назад

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

@user-rv3bu9um3o 5 месяцев назад

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

@SamSambl4 6 месяцев назад

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

@fenix7450 6 месяцев назад

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

@user-xx2vo8nm3q 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-ww9dc4we4o 6 месяцев назад

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

@someone-there 6 месяцев назад

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

@user-jt6hk5sn8t 6 месяцев назад

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

@Lilly.Yakovlevna 6 месяцев назад

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

@user-ks4md4vw9e 6 месяцев назад

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

@yurd563 6 месяцев назад

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

@Alexey_Qwerty 6 месяцев назад

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

@user-rr7fn5nd5l 3 месяца назад

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

@planetaexcel 3 месяца назад

Нема за що :)

@user-wx4ew4zg8z 6 месяцев назад

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

@user-xf8gu1mw3t 6 месяцев назад

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

@LonlyLokly 6 месяцев назад

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

@SWooy... 6 месяцев назад

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

@steelforcerock5803 2 месяца назад

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

@Viacheslav_Ch 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@Utesov4825 6 месяцев назад

Потрясно👌🙌🤝

@user-jb1mg1eu6h 3 месяца назад

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

@BORISVLEONOV 6 месяцев назад

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

@planetaexcel 6 месяцев назад

Супер! :)

@moiskon 6 месяцев назад

Круто!

@kruzo_o 6 месяцев назад

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

@user-gz4zh4nu7p 6 месяцев назад

Super.

@an1man1ac 6 месяцев назад

оч круто

@user-td7sd2yl5v 6 месяцев назад

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

@user-kc5yu4bf9v 5 месяцев назад

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

@maxg2371 6 месяцев назад

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

@planetaexcel 6 месяцев назад

🙏🙂

@babybmwer 6 месяцев назад

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

@alibeksartay8177 6 месяцев назад

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

@user-db7dq7zi6b 5 месяцев назад

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

@user-lg9tg7zm4x 17 дней назад

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

@user-mt6ff3do5u 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-ww5wg7qe3j 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@hokmuns 6 месяцев назад

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

@user-lw9dw4fq1v 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@Anilid. 6 месяцев назад

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

@alexshavlo6701 6 месяцев назад

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

@yerkebulantaskynbayev1490 5 месяцев назад

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

@identitynotestablished9133 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-vi5ey4rg8b 6 месяцев назад

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

@MrTrylobeat 6 месяцев назад

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

@identitynotestablished9133 6 месяцев назад

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

@axelrose3018 6 месяцев назад

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

@planetaexcel 6 месяцев назад

Да, конечно.

@user-ui2wu6mm9l 5 месяцев назад

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

@user-ui2wu6mm9l 5 месяцев назад

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

@666saturation7 Месяц назад

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

@daryayurenka3077 3 месяца назад

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

@user-wy1wq6lj4l 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-wy1wq6lj4l 6 месяцев назад

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

@user-pw5ix7mb4u 4 месяца назад

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

@user-sw9mx7ow7s 6 месяцев назад

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

@Parallacs 6 месяцев назад

Страшно) тоже был пользователем P7. Ужс

@user-xt8cl8fu4l 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@janerogachyova9636 6 месяцев назад

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

@Nomerel 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@ytolk 6 месяцев назад

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

@edoi34 3 месяца назад

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

@planetaexcel 3 месяца назад

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

@MrTrylobeat 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-ik7sx2kw1z 6 месяцев назад

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

@planetaexcel 6 месяцев назад

www.planetaexcel.ru/plex/

@user-fb4is4oc1n 5 месяцев назад

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

@adamadamov5787 6 месяцев назад

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

@user-sw9mx7ow7s 6 месяцев назад

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

@xjHy4IVbu_d 6 месяцев назад

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

@Ilya.Vityugov 4 месяца назад

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

@planetaexcel 4 месяца назад

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

@Ilya.Vityugov 4 месяца назад

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

@Midavok 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-iq3db6re4h 6 месяцев назад

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

@user-px6kx9gj6u 6 месяцев назад

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

@Andersen_911 6 месяцев назад

Like нумеро 90

@user-xg4pd7en9d 6 месяцев назад

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

@1iuh 6 месяцев назад

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

@xjHy4IVbu_d 6 месяцев назад

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

@1iuh 6 месяцев назад

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

@xjHy4IVbu_d 6 месяцев назад

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

@kirylsavitski3007 6 месяцев назад

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

@planetaexcel 6 месяцев назад

😉👍

@user-cc8vs2zy9b 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-cc8vs2zy9b 6 месяцев назад

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

@user-co6de5tc6q 6 месяцев назад

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

@Rice8520 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@Rice8520 6 месяцев назад

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

@user-rv8kz6de6z 6 месяцев назад

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

@yaroslavcurl 6 месяцев назад

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

@user-rv8kz6de6z 6 месяцев назад

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

@planetaexcel 6 месяцев назад

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

@user-rv8kz6de6z 6 месяцев назад

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

@BORISVLEONOV 6 месяцев назад

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