Трюк Excel 25. Список уникальных значений
Вставка
- Опубліковано 30 вер 2024
- Создание списка уникальных значений из массива данных тремя способами: удаление дубликатов, сводная таблица, формула. Другие трюки Excel и файл с примером можно скачать здесь statanaliz.inf...
► Корпоративный тренинг "Продвинутый уровень MS Excel":
statanaliz.inf...
► Онлайн курс "Продвинутый уровень MS Excel":
statanaliz.inf...
**************************
** Мой сайт об Excel и статистике **
statanaliz.info/
** Странички и аккаунты в соцсетях **
Facebook: / statanaliz.info
Вконтакте: id_stat...
Твиттер: / statanaliz_info
Telegram: t.me/statanaliz
К сожалению формула не сработает, если в диапазоне будет пустая ячейка.
Спасибооо! Замена функции УНИК🎉
...о, оказывется есть уже такая формула
Только я еще добавляю игнорирование пустых значений в списке:
Ко второму аргументу ПОИСКПОЗ нужно прибабить массив проверки
+(вашдиапазонсисходнымсписком="")
...но всеравно лайк
Спасибо. Да, разновидности, учитывающие нюансы вроде пропусков, также полезны.
Спасибо, Дмитрий. Есть задача несколько сложнее, у меня не получилось ее решить. Вместо одного столбца два (например, в первом столбце цвета, во втором названия фруктов). Нужно создать уникальный список сочетаний (зеленый ананас и зеленый огурец - разные сочетания), кроме того отсортировать результат по алфавиту по обоим столбцам.
Скорее всего, для решения этой цели подойдёт такой инструмент как сводная таблица.
Орфография в данном случае не играет значения? "Свекла красный" пойдёт?
Данную задачу, на мой взгляд проще решить есть создать дополнительный столб с функцией сцепит
Спасибо огромное! Попробовала. Подскажите, пожалуйста, как справиться с циклической ссылкой, которая появляется, во время расчета? Или я делаю что-то не так?
спасибо за работу!
Спасибо за классный трюк!
Понимаю, что опоздал на 4 года, но на видео очень не хватало плашечки с клавишей F9 внизу. То, что делает эта кнопка - это настолько полезно, что я, более 15 лет проработав с Excel, теперь просто недоумеваю, почему этому не учат в школе...
Спасибо, за F9. только прочитав комментарий, поняла как посмотреть значения массива.
Спасибо! Замечательные и полезные трюки
Хороший пример. Только формула не будет работать с пустыми ячейками
Доброго дня, подскажите используемая формула подходит только для поиска уникальных значений из одного столбца? Если мне, например, нужно сделать список уникальных значений в один столбец, а исходные данные находятся в 10 столбцах?
Формула малость подлиннее будет )
В результате выдает вместо значения 0. При пересчете с помощью F9 - всё ОК
так это же получается циклическая ссылка. В этом случае ф-ция Счетесли ссылается на ячейку в которой находится
как сделать такой список сразу для 3 ячеек?
добрый день, спасибо за видео. а как составить список уникальных значений на другом листе по критерию? например выдать уникальные значения между датами. заранее спасибо за ответ.
Спасибо. Как посчитать повторяющиеся числа?
Очень много интересного и полезного почерпнул из этого видео. Большое спасибо, Дмитрий, за просвещение!
А у меня почему то не работает, что не так?
{=ЕСЛИОШИБКА(ИНДЕКС(Материалы_2019![D5:D1050];ПОИСКПОЗ(0;СЧЁТЕСЛИ($C$1:C2;Материалы_2019![D5:D1050]);0));””)}
Баксы нужны )
Несколько раз пересмотрел, все понял, спасибо, полезный урок)
Спасибо! Как раз нужно такую фишку.
внятно, ясно, лаконично и по делу. спасибо.
Пожалуйста и спасибо за отзыв!
в комментариях куча страждущих со своими "нерешаемыми" проблемами(((, но за ролики всё равно спасибо
Не всегда есть время отвечать, хотя очень стараюсь.
Спасибо за очень полезное видео!
Пожалуйста. Спасибо за комментарий )
А почему нельзя просто было использовать формулу УНИК?
Можно. Но тогда этой функции еще не было. Ее и сейчас почти ни у кого нет. )
великолепное решение. минус этого ролика, что самому не пришлось скрипеть мозгами :)
Здравствуйте! Сначало подумал, что тоже решение, которое мне подойдет, потом понял что не совсем, а если исходная таблица это данные за последние 90 дней и нужно сохранять данные, которые на следующий день удаляться так как уже являются 91 днем. Как ее использовать? В общем задача сохранять все данные после 91 дня.
Дмитрий, спасибо за урок, толково.
Если я правильно понял, при увеличении исходного списка, например добавятся 3 новые строки, мы протянем в формуле и расширим анализируемый массив исходной таблицы? Или формула анализирует именно таблицу и при увеличении количества строк в этой таблице автоматически расширит проверку?
Извините за такой вопрос, потому что в старом excele многие вещи нужно было делать вручную.
спасибо! я не поняла как переключиться, чтобы видеть в строке формулы результат промежуточного вычисления?
У меня на ноуте при выделении формулы счетесли - просматриваемый массив и нажатии клавиши F9 открывает окно Лучшие приложения и Последние действия. Подскажите
Похоже на конфликт горячих клавиш. Попробуйте отключить приложения, которые включаются при нажатии F9 и попробуйте еще раз в Excel.
@@АнтонинаВерник нужно с зажатой клавишей FN, либо в биосе перенастройти функии F'ок
Спасибо за урок! Скажите пожалуйста а как рядом (справа) от уникальных значений вывести еще и их количество?
Формула крутая, но тяжко понимаемая. Буду пересматривать, пока четко не пойму
Мужик
Супер!!! Спасибо
Данная формула не работает, если брать данные с другого листа.
Работает
Возникла проблема, так как дилетант - не могу решить. У меня есть такой же список, но он с пропусками, и в уникальном списке выскакивает 0, как от его избавиться или чтобы не учитывало поиск по пустым ячейкам? А так все шикарно. Видео хоть и старое, все же питаю надежды что автор поможет ^^
действительно трюк! спасибо!))
Спасибо за видео. А как сделать чтоб показывались не только уникальные значения, но и их количество повторений в списке?
Бомба💣
Благодарю🙏
Но надо ещё раз пересмотреть
Решил с помощью "F9" взглянуть на массив, сформированный из 60 тыс. строк таблицы. И впервЫе столкнулся с ограничением на длину формул - 8192 знака.
Так нет смысла делать такие расчеты. Все равно же ничего не понятно ))
@@statanaliz А за что нам деньги платят? Будем сидеть и изучать)
Хорошо бы вы в подписи к ролику написали текстом формулу ))
2021
Спасибище!! Всё получилось!
Спасибо!
Добра Вам!
Спасибо. Взаимно, всех благ! )
Спасибо за урок!
Ништяк формула... спасибо за урок!
Я из за таких уроков на работе мастером стал😂
Гениально
А что делать если вторая клетка в списке пустая? Функция перестает работать 😵. У меня была немного другая считала наибольшее повторение но и она не работает при пустых клетках =ЕСЛИОШИБКА(ИНДЕКС(DK157:DK168;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(DK$169:DK169;DK157:DK168));СЧЁТЕСЛИ(DK157:DK168;"
Смотри комент выше
Спасибо за урок!, всё понятно!
Здравствуйте.
Есть Ячейки с номерами телефонов (допустим 8908323224) и есть ячейки с номерами телефонов + с комментариями к ним (допустим 8908323224 не дозвонился.). Как мне находить ячейки с повторяющимися номерами телефонов в таком случае?
Ведь поиск ведется по абсолютно идентичным ячейкам, а у меня не везде есть комментарии.
Здравствуйте. Если пользуетесь ВПР, то критерий для поиска можно так записать: ВПР(Критерий & "*";........). То есть добавьте к критерию подстановочный символ *. Тогда по критерию "02" найдет и "02", и "02 не дозвонился". Только смотрите, чтобы везде текстовый формат был, т.к. & автоматически переделывает число в текст.
"...отправляем в функцию поиск позиции, которая будет искать номер позиции, в которой впервые появляется ноль". Не сразу уловил сию фразу. А так все понятно. Павлов делал, надолго запомнить не получилось. Может опосля вашего видео запомню )
Да пару раз повторить самому и станет понятней )
Здравствуйте,Дмитрий.Что делать,если формула пишет "ложь" и ещё какие-то ругательства?
Здравствуйте. Надо бы разобраться. Какая ошибка? Напишите прямо здесь формулу. Возможно, ошибка в синтаксисе.
Почему конечный список уникальных значений не форматируется по возрастанию/убыванию?
Потому что форматировать можно значения, а не формулы. Удалите формулы, например, через специальную вставку, и затем можно сортировать.
То есть ищем не наличие, а отсутствие. Прикольно.
Пы.Сы. Да, Дмитрий. Ответ на Ваш вопрос "Понятно?": нет. Даже мне, знающему большую часть ваших уроков из своего опыта, пришлось подумать =)
Дык, даже понятное объяснение не всегда заходит с первого раза. Вначале нужно понять по кускам. Потом все кусочки соединить, чтобы получилась ясная картина.
Отличная функция, но с множеством данных зависло и все
Вначале протестируйте на малых данных. Например, оставьте в самом начале запроса пару десятков строк. Потом удалите этот шаг. Возможно, зависло не из-за количества данных.
Здравствуйте. По работе никогда не сталкивалась с Excel. Благодаря Вашим урокам открыла его для себя. И обязательно постараюсь его освоить для себя. Однако, сейчас необходимо очень быстро посчитать в табличном варианте погашение обязательств перед банком в пределах определенного срока. Таблицы не большие, с указанием месяца, суммы ежемесячного платежа и остатка долга. Какое из Ваших видео Вы посоветуете?
Здравствуйте. У меня нет видео по финансовым формулам. Наберите в поиске ключевые слова вроде погашение кредита в Excel или кредитный калькулятор в Excel. Там нужно применить пару функций.
Спасибо! То что искала
Благодарю! Неоднократно возвращалась к Вашим роликам, Супер полезно и ясно донесено! БЛАГОДАРЮ!!!
Вам спасибо за отзыв!
Умище) Круто
Дмитрий, огромное спасибо за видео, но если в исходном списке только одно значение (одна строка например), то ваша формула возвращает пустую строку. Как элегантно обойти, чтоб формула возвращала это одно значение?
Пожалуйста. Не всегда получается универсальная формула. Попробуйте завернуть в ЕСЛИ, и в условии посчитать количество строк. Если больше 1, то указанная формула, в противном случае одно значение.
Дмитрий, здравствуйте! Спасибо за ваши труды! Очень полезно. В данном уроке заметил, что у меня при вводе формулы массива данные отображаются столбцом и я не могу их редактировать, т.к. выходит ошибка и не получается протянуть формулу. Добавляю ссылку на скриншот с ошибкой, может подскажете как решить.
yadi.sk/i/dWVWA2_5Q77MLw
Здравствуйте. Вводите Ctrl + Shift + Enter в одну ячейку? Почему в формуле изменилось название таблицы? Была таблица2, в Вашем скриншоте Таблица1?
Супер, спасибо. То, что было нужно. В Гугл таблицах работает корректно
Спасибо за комментарий!
Огонь 👍
OK!
Like!
Подскажите а каким сочетанием клавиш Вы при написании форсы в ячейке смотрите полученный результат и обратно возвращаетесь к написанию формулы?
F9
а назад Ctrl+Z или (Esc с выходом из редактирования)
@@ПавелсВостока Точно, спасибо, что ответили )
Каким сочетанием горячих клавиш в строке формул можно просмотреть предварительный вариант расчета?
выделить участок формулы и F9
@@statanaliz у меня на ноуте не работает. Браузер Хром открыт в то же время, перехватывает команду и выдает свою справку. Это в настройках браузера искать?
Мужик Ти красавчик)))
Что с качеством? Ничего не считать.
Может, надо в настройках видео поправить? Качество везде одинаковое.
@@statanaliz Я скачал это видео на память телефона на низкой качестве изображений. Из за этого изменить качество стало недоступно оказывается. Удалил с памяти, сразу все стало по прежней (появился возможность изменить качество)
@@bakytjakiev543 Про телефонные настройки не подскажу, не пользуюсь )
Уникальное значение это ID номер ?
В уроке пример с цветами. Уточните вопрос про ID.
Спасибо! Буду советовать этот канал.
И Вам спасибо. Заходите еще!
Благодарю, прямо то что нужно. Помогите пожалуйста разобраться, как создать список по двум условиям. Например, первое условие - список с уникальными значениями (Название кампании), второе - месяц даты заказа.
На ленте есть кнопка "Удалить дубликаты". Укажите там два столбца. Останутся уникальные значения по двум признакам.
@@statanaliz благодарю. Нашел ещё один способ. Результат формулы Счётесли суммирую с массивами, сверяющимися со вторым условием, так можно много условий создать
Для тех кому понадобится. Есть одна тонкость, необходимо результат совпадения массивов с условием инвертировать функцией "НЕ": ЕСЛИОШИБКА(ИНДЕКС(заказы[название];ПОИСКПОЗ(0;(СЧЁТЕСЛИ($A$5:A6;заказы[название])+(НЕ(ТЕКСТ(заказы[дата];"ММММ")=$B$2))+(НЕ(ГОД(заказы[дата])=$C$2)));0));"") В этой формуле в условии еще участвуют месяц и год (сделки к примеру)
Большое Вам спасибо
Не за что )
Дмитрий, огромное спасибо. А не подскажите как сделать , чтоб привязать этот уникальный список к еще одному кретерию. Ну например, у каждого автобренда есть несколько уникальных позиций
Пожалуйста. Новый критерий из двух можно склеить с помощью &.
@@statanaliz Не объясните поподробнее? Я пытаюсь перемножить счетесли с уникальными значениями и счетесли со вторым критерием. Ничего не получилось
@@ДенисСоби зачем перемножать?
Ко второму аргументу ПОИСКПОЗ прибавляете еще один массив проверки
Спасибо, интересно. Заодно сочетание "ctrl+D" узнал, жаль оно только на одну ячейку действует.
Ctrl + D действует на столько ячеек, сколько выделено, где верхняя ячейка - это копируемая ячейка
Точно, если захватить копируемую. Я пробовал ячейки внизу, без копируемой. Спасибо буду использовать.
@@Teraed )) Еще один трюк узнали. Удачи!
Большущее спасибо!!!))
Пожалуйста!
Спасибо!
Пожалуйста!
добрый день имею вот такую формулу : {=ИНДЕКС(Таблица2[исходный список];ПОИСКПОЗ(0;СЧЁТЕСЛИ($C$1:C2;Таблица2[исходный список]);0))} н показывает "0" везде. хотя при нажатии F9 выдает название цвета буквами.Почему?
вводите формулу через Ctrl + Shift+ Enter?
да именно так и ввожу!
@@Сергей-и8э5ж А фигурные скобки откуда в формуле? Их не нужно вводить вручную, они сами добавляются при вводе формулы через Ctrl + Shift+ Enter?
да правильно - они сами так и добавляются при нажатии Ctrl + Shift+ Enter, но после этого все равно нули в ячейках
@@Сергей-и8э5ж пришлите файл и укажите, где именно ошибка. Почта в описании канала.
Дмитрий подскажите пожалуйста как из массива чисел выбрать максимальное значение на убывание если таких одинаковых значений несколько, а надо только одно
Вопрос как-то связан с этим видео? Что такое "максимальное значение на убывание"?
@@statanaliz Необходимо найти в массиве первое максимальное число, затем второе макимальное , которой меньше первого, и .т.далее
@@МихаилЛанге-ч1х Функции Excel НАИБОЛЬШИЙ и НАИМЕНЬШИЙ ua-cam.com/video/FWZy3bcHnvE/v-deo.html
Спасибо. А можно ваш email я перешлю вам файл
@@МихаилЛанге-ч1х да, в описании канала.
Дмитрий, нет ли у вас видео по следующей теме? Предположим, я экспортирую данные в Exel. Данные в ячейках постоянно меняются. например, в ячейке первое значение было 100, следовательно в ячейке суммирования у нас так же пока 100, но через пару секунд стало 80. В ячейке суммирования по прежнему зафиксировано 100. Но, когда экспортируемое значение изменится на 90 (а это плюс 10), то в ячейке суммы так же должно прибавиться 10 (т.е. 110)
Откуда экспортируется? Автоматически да еще быстро обновляется только формула. Другие способы только вручную или в заданный момент.
@@statanaliz Экспортируется с бижи, терминал Quik (выводится через DDE сервер). Жму вывод через DDE сервер и торговый терминал копирует мне в эксель свою таблицу. В той таблице данные о спросе и предложении по акциям, которые в течении дня всё время меняются в ту или иную сторону. Например, спрос на акции сбербанка в первую половину дня был 300 тысяч лотов, затем он вырос к примеру на 150 тысяч и составил уже 450 тыс. Или же наоборот может снизится. Так же и с показаниями предложения. Вот я и думаю, можно ли как то настроить анализ в эксель. Например: Максимальный спрос и предложение за день, ну и самое важное, сколько всего выкидывалось на рынок спроса и предложения за день (т.е. суммировать только прибавку к числу,). Ну как то так... Спасибо.
Более наглядно. Дядя Стёпа принёс мне 10 яблок (что отпечаталось в таблице и колонке суммирования). Два яблока съели дети, в итоге у меня осталось 8 яблок, что отображает колонка в которую транслируются данные. НО, в колонке суммирования у меня по прежнему зафиксирован приход в 10 яблок, расход не нужно учитывать. Затем дядя Стёпа приносит мне ещё одно яблоко, таким образом в колонку суммирования должно прибавиться +1 яблоко (итого 11). То есть, мне нужно подсчитывать сколько яблок мне приносит дядя Стёпа без учёта сколько их съели дети)) Можно ли так сделать?
@@aA-gc2wj Надо смотреть, как устроены исходные данные. Если приход и расход в разных колонках, то все просто. Берете просто сумму в нужной колонке. Если все валится в кучу, тогда нужно, например, смотреть, на знак, или другой признак, по которому можно отделить приход от расхода. Далее по этому признаку суммировать, например, с помощью СУММЕСЛИ.
@@statanaliz в том то и дело, что все эти числа постоянно меняются, если можно так сказать динамически. В одной колонке было 100, затем стало 90, потом 120 и так далее. Я бы мог снять на видео, но тут вроде нельзя выкладывать в комментарий...
Супер решение, спасибо! А что будет, если в исходном списке будут пустые ячейки? Как их пропускать, проверку через ЕСЛИ сделать? Что посоветуете?
Резонный вопрос. Нужно подумать. Или кто-то подскажет в комментариях ))
Сообщите, пожалуйста, если будут идеи, как не учитывать пустые ячейки из исходного списка. У меня в исходном и в самом начале может быть пустая ячейка или в конце. Очень прям нужно. Спасибо!
@@martysh_ka если задача одноразовая или редкая, то просто поставьте фильтр на пустые ячейки и удалите. Для формулы нужно подумать. Пока не знаю.
@Elena Melnikova Насколько для Вас критично, что это будет именно пустая ячейка или ячейка с одним пробелом? Визуально они выглядят одинаково. Если не критично, то используйте в пустой ячейке 1 пробел. Так подойдёт?
@@martysh_ka INDEX(Исходный;MATCH(0;COUNTIF($E$1:E2;IF(Исходный="";"Уникальный";Исходный));0)) - в данном случае, нам нужно, чтобы пробелы исходного списка были преобразованы в какие-нибудь значения, которые точно будут в уникальном списке. В данном случае это шапка уникального списка в ячейке С1. Т.е. при нахождении в исходном списке пустой ячейки он будет приравнивать её к названию "Уникальный". Такое значение у нас есть в столбце с уникальными значениями, значит в проверке наличия в столбце пойдёт единица и пустую ячейку он пропустит.
Можно без формулы массива:=ЕСЛИОШИБКА(ИНДЕКС(Таблица2[Исходный список];ПОИСКПОЗ(0;ИНДЕКС(СЧЁТЕСЛИ($С$1:С1;Таблица2[Исходный список]);0);0));"")
Рабочая формула, спасибо!
Дмитрий, классный вариант решения Вы придумали. Он хоть и не сложен, но интересен. Большое спасибо Вам!
Здорово, что Вы создали это видео)
Спасибо за отзыв. Рад, если понравилось )