Мульти-ВПР: ищем все совпадения
ฝัง
- เผยแพร่เมื่อ 16 ก.ย. 2024
- Два способа извлечь с помощью формул (обычных и формул массива) сразу ВСЕ значения из массива по условию, а не только первое, как делает обычная ВПР. Подробная статья и скачать файл-пример тут planetaexcel.ru...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Вы самый лучший тренер по экселю. Спокойно доходчиво и немаловажно с дикцией объясняете. Действия любого уровня сложности. Браво. Аплодирую стоя.
Николай, спасибо Вам большое за Ваш труд. Когда я ищу в поиске решение "екселевских" проблем, то всегда в результатах поиска выбираю Ваши статьи. Всегда по делу, всегда исчерпывающе, всегда понятно.
Вот и наступил один из многих моментов, когда специально нашёл это видео по теме, чтобы отдельно ПОБЛАГОДАРИТЬ Николая за мегаполезный материал, который пригодился на работе и сэкономил МАССУ времени и облегчил рутину! :)
СПА-СИ-БО!♥
Николай, спасибо большое. У Вас такой красивый голос, что обучение становится очень приятным и совсем не утомляет).
i dont mean to be offtopic but does someone know a way to log back into an Instagram account..?
I somehow lost my account password. I would appreciate any help you can give me
@Khari Kyle instablaster =)
@Maximus Deshawn I really appreciate your reply. I got to the site on google and I'm in the hacking process now.
Seems to take quite some time so I will get back to you later when my account password hopefully is recovered.
@Maximus Deshawn It did the trick and I actually got access to my account again. I am so happy!
Thanks so much, you really help me out!
@Khari Kyle Glad I could help :)
Даже по прошествии нескольких лет, это просто бомба. Спасибо, Николай!!!
Николай , сколько же вы пользы приносите людям ! Спасибо вам большое !
Николай, благодарю. Вы потрудились дать информацию доступно и понятно. Спасибо за ваш бескорыстный труд и помощь! Усехов вам во всем!
Воистину когда знаешь то все просто. Спасибо Вам Николай за ваш труд. Очень все доходчиво и просто объясняете
Вы самый лучший в мире УЧИТЕЛЬ! спасибо, что Вы делитесь своими знаниями!!!🤗
Спасибо Вам Николай
Здравствуйте, Николай! Спасибо! Как всегда вовремя, по делу, без лишней воды.
Ну не, Николай, ты все таки молодец братишка, голос чёткий, доходчиво, главное все слова по существу ! Не то что другие которые гоняют воду)) кроме денег получаешь много благодарности - а это больше чем деньги ! Удачи тебе, не стареей
Спасибо за видео! Но для составления сложных формул как раз таки удобней использовать промежуточные формулы, которые мы расписываем в соседних столбцах. А затем, когда мы имеем необходимые формулы, можно составлять одну большую сложную формулу.
Браво 👏 как же долго я перерывал интернет, чтобы найти это решение!
Как же долго я искал эту функцию! Как же сильно Вы облегчили мне работу! СПАСИБО ОГРОМНОЕ! :)
Довольно часто встречаются задачи, когда нужно отображать содержимое ячеек по двум и более значениям из других строк/столбцов.
Тогда промежуточный вариант с определением позиции по строкам включить не получится в одну формулу, так как сначала отработается событие по пересечению столбцов и строк, а потом будет ссылка на массив из функции "Small".
Ну и будут жуткие лаги, когда массив информации большой, поэтому предпочтительнее варианты с дроблением в несколько более простых функций;)
Благодарен за урок, коллега:)
Вы как всегда ....на высоте!!!
Спасибо большое за этот урок! Помог осуществить задуманное
супер, опять то, что нужно
Просто суперрррр . Спасибо вам огромная 🙏
Спасибо огромное за такие видео, очень помогло в работе😊
Спасибо Николай ака! Как Вы мне облегчаете жизнь ;(
Отдельная уважуха за то что в первом примере "зашел издалека"
Вы помогли кому-то этим видео даже спустя 8 лет
Рад это слышать :) Значит - не зря старался :)
спасибо!
Спасибо Вам ОГРОМНОЕ!!!!!!!!
спасибо за труд
Спасибо Николай! :)
Хорошо, а если на другом компьютере не установлен этот пакет - будет vlookup работать?
Спасибо. интересно. Но сразу вопрос как решить проблему кол-ва зеленых строк если не знаешь точное кол-во заполняемых ячеек. в вашем случае в заказе их не более 4 наименований. А если я не знаю сколько товаров в определенном заказе? протягивать до бесконечности формулу не совсем удобно. Как сделать так чтобы excel автоматом протягивал данную формулу если в заказах не закончились значения. Надеюсь я не сломал мозг своим вопросом.
Судя по шквалу ответов - сломал
Может быть, сводная таблица?
Помогло, спасибо!
Спасибо, как пронумеровать по порядку повторы содержимого ячейки в двух столбцах вместе, другими словами - содержимое ячейки рандомно повторяется в двух столбцах и нужно эти повторы пронумеровать попорядку сверху вниз в обоих столбцах сразу, т.е. нумерация она для обоих столбцов (НЕ каждый столбец в отдельности и по очереди)
Здравствуйте , Николаи а можно сделать так чтобы когда делаеши формулу из документа на другом документе скопировать и цвет ечеик из первого документа....
Спасибо большое!!
Спасибо за полезные и понятные видео. Мне многое нравится, кроме вставок с английскими названими, хорошо, что Вы похоже знаете английский, но не надо это тут помечать.
Николай, спасибо огромное за урок, у меня такая ситуация: в списке повторяются значения, из массива я уникальные не могу вывести, потому что сумму не корректно считает, можно ли усовершенствовать формулу, чтобы выводило только уникальные значения в данной ситуации, спасибо
Спасибо Николай
Николай, добрый день! Подскажите, пожалуйста, как можно расширить функционал описанный в этом уроке на два и более условия отбора? Простое добавление оператора "И" в условие "ЕСЛИ" не помогает, к сожалению :(
Это лайк!
Большое спасибо.
Import prices from Sheet 2 and display against relevant product code in Sheet 1
Show a total sum of how many of each product ( variant code ) has been sold to each customer (data on sheet 3)
How I can do it?
Николай, спасибо за все Ваши доступные для быстрого понимания ролики.
Время идет, эксель меняется. Используя "функции динамических массивов" (что стало доступно несколько позже - после 2019 года), как преобразуется формула?
Thanks
Круто) спасибо!
Гений 👍🚀
Круто!! Спасибо огромное!!
дякую за уроки все супер !!!
Доходчиво.
Спасибо большое
К способам до 09:14 можно применять файл (из которого надо "вытянуть" данные) расположенного в одной папке с книгой в которую собираются данные?
Спасибо большое что ответили. Я уж было подумал что не ответите из-за большого объема вопросов вроде моего. Одна сложность. У меня пока только версия excel 2003, официальная. Мне ее когда то подарили. Подскажите, где взять новую рабочую программу, самую свежую excel и какая сейчас последняя существует?
Николай здравствуйте все красиво и доступно, а у меня такой вопрос.Мне необходимо составлять требование накладную в ЕХЕЛЕ, есть исходная деталь например Вал в сборе которая состоит из других деталей к примеру подшипник -2шт. , вал-1шт., шестерня-2шт., фрикцион-1шт. ВОПРОС - как сделать формулами массивами , возможность выбрать например ВАЛ в СБОРЕ 1,2 или 3 шт и чтобы при этом в таблицу вытягивало все из чего ВАЛ состоит и сразу высчитывало сколько необходимо комплектующих , заранее благодарен ищу по инету ни где найти не могу
Подскажите пожалуйста, а если сравнить два списка в котором имеются повторяющиеся значения как с одной, так и с другой стороны. Данный способ поможет в решении такой проблемы?
Николай, спасибо за видео очень помогает в работе. У меня вопрос по описанному Вами способу, возможно ли этот способ применить если у меня не просто проверка на совпадение номера заказа, а попадание № заказа в определенный диапазон (например от 2 до 4), проверку которого я осуществляю с помощью функции И? Пробую поставить после функции ЕСЛИ, функцию И и проверить этот диапазон, но видимо не правильный синтаксис или таким способом не возможно решить данную задачу?
Спасибо. Удастся теперь осуществить сортировку по столбцам из той перемешки, что у меня была. Vlookup3 использовать буду.
класс
Спасибо!
А можно зеленую табличку заполнять не вертикально, а горизонтально, чтоб значения заполнялись в соседние ячейки правее?
Thank you
спасибо!!!
Доброго времени суток. Подскажите пожалуйста, есть таблица броней туров и даты туров выводятся на другой лист. За время пребывания клиент может купить экскурсии как на 1 день, так же и на 2 и 3 дня. Мне надо, чтобы в ячейке выводилась дата последнего дня тура. Как это сделать? То есть в строке найти последнюю поставленную дату, Спасибо
Отличная функция, спасибо!!! А можно все значения вывести например через запятую в одну ячейку???
Не вопрос - посмотрите www.planetaexcel.ru/techniques/7/205/
у меня вопрос по поводу последней макрофункции. допустим мы сделали формулу у себя. а если открыть файл в другом екселе (др ноутбук или комп) где нет этой функции или ексель 2007 она будет работать?
Добрый день, не знаю, смотрите ли Вы комментарии к давним видео, но спрошу. Была безумно рада, увидев ваш ролик: задача популярная и по сути не сложная, но почему то решение редко встречается в инете. И у меня не получилось у меня((. Мало того, я полностью скопировала ваши данные с примера, чтобы уж наверняка , все равно пустая ячейка. Очень прошу, подскажите, пож-та, какие есть нюансы у данного массива? Ни цифры , ни текст - ничего не ищет.
Добрый день! Николай, а если таких значений множество ?
Здравствуйте! А можно эту формулу как-то изменить для поиска всех совпадений из другой таблицы?
Спасибо. А что делать, если у меня целый список значений которых нужно найти. У вас на примере не больше 10 заказов. А что делать если у меня их более 10тыс.записей. Как обработать? Чтобы указывать не конкретное значение, а диапазон значений которых нужно найти.
Тут лучше использовать расширенный фильтр - см. www.planetaexcel.ru/techniques/2/197/ или Power Query
Николай спасибо за видео, очень помог, у тебя выборка сделана с цифрами, мне нужен с текстом, в конце не могу собирать все формулы в одну, как тебе отправить мой файл или сам как то сможешь объяснить
Вопрос
У меня 2 таблицы
Условно товар в виде шифров и их цены в 1 табл и шифры в разбросанном порядке в 2 таблице. Как цены подходящие к каждому шифру из 1 таблицы присвоить к шифрам во 2 таблице подскажите пожалуйста?
Через этот Ваш способ приходится для 1 категории шифров вставлять в формулу определенный шифр чтобы цены вставлялись, но остальные уже не вставляет для других шифров
Надо вручную каждый шифр вбивать
поставил лайк. Спасибо Николай. Вы существенно оптимизировали мои трудозатраты по экселю. Не трачу время на бессмысленный мартышкин труд.
Ну а теперь у меня два вопроса. 1) я скачал с интернета один эксель файл которая считает оптимизацию распила. Можно считать что это целая программа. Экселька просто бесподобная. Многие мои клиенты не перестают восхищаться. Но есть проблема. Эксель файл открывается только на виндоус 7. На восьмерке и на десятке матюкается на то что не хватает модуля lpsolve55. Почему так?
Ну и второй вопрос - какая падла может ставить дизлайк вашим роликам?
дизлайки ставят идиоты и завистники))
Скинь свой файлик, попробую победить проблему, если актуально ещё.
Здравствуйте! Нужно произвести вычисления в одной строке, например: (3+1)×2+4-2 , и получить результат в соседней строке. Подскажите, как это сделать, заранее спасибо!
Николай, как сделать впр массивом не вниз, а вправо?
Здравствуйте! Помогите решить проблему. Для определения мест участников соревнований использую "РАНГ РВ". Очень нравится как работает эта функция. Корректно расставляет места. Но столкнулся с проблемой: когда ввожу результаты забега на 800м. (минуты.секунды,сотые секунды) например 1.25,98 функция выдаёт ошибку изза точки после первого знака. А мне нужно записывать только в таком формате. Подскажите можно ли решить эту проблему добавив к функции "РАНГ РВ" какую-то формулу, и какую именно? Я не настолько силён в этом чтобы разобраться.
Добрый день! Подскажите, как дополнить формулу, чтобы поиск осуществлялся по неточному критерию (текст), например по первым 6 символам?
Спасибо Вам, Николай. Один вопрос, в первом примере Вы ввели формулу массива, а у меня странным образом эта формула сработала просто так. Почему, не подскажите?
"Просто так" она тоже работает, но неверно.
Вопрос. А если таблица Заказов постоянно пополняется . В неё входят кроме # заказа, название товара еще и количество и дата заказа. Можно ли выбирать по номеру и дате заказа.... Чтобы находило товар и количество. Суть в том, что хотел применить это при выписывании фактуры для клиента. Тогда при выписывании фактуры достаточно будет ввести номер и дату, а товар и его количество подтягиваются с таблицы заказы. Как можно решить эту задачу?
Уважаемый Нилоай Павлов,очень прошу,дайте пожалуйста какую-либо конструкцию,которая заменит функцию "УНИК" в эксел-2016-ом,так,чтоб вновь вносимые данные добавлялись как уникальные значенияв список уникальных значении! Заведомо благодарю!
Ответил вам по почте :)
Уважаемый Николай,да видел.Благодарю.
Спасибо за урок!
Вопрос: Можно ли подобное провернуть не с цифрами, а с какими-либо текстовыми данными. И если нет, какая функция может помочь при выдержке текстовых данных из массива?
Да, можно. в серии Ctrl+Shift+Enter Майк Гирвин объясняет как это делать. скоро в Эксель связанный с офис 365 будут специальные функции которые за раз смогут выдовать такой результат.
А если все это сделать при помощи сводной таблицы: номер заказа вывести в фильтр?
актуально и в 2019
До нового года осталось... всего ничего. Поэтому, актуально и в 20-м. :)
И в 2021
По второму варианту в итоге вышло #число!
А если нужно вытащить не все значения, а только первое (ну тут стандарт ВПР), ТОЛЬКО второе, только третье, ... ?
Мучаюсь месяц а то и больше, подскажите: Мне нужно найти минимальное (максимальное) значение в столбце таблицы одного наименования (по трем критериям (столбцам) - названия, цены и главное факт.наличия (до продажи) для дальнейшего анализа. Перепробовал массу ваших формул, массивов, индекс, поиспоз не помогло. Подробно могу сбросить на ваш емайл
Вот статья про поиск минимального или максимального значения по условию(ям) - посмотрите www.planetaexcel.ru/techniques/2/241/
А у меня почему то выдает не все нужные элементы и выдает даже те которые не попадают под притерий поиска.
А если надстройка PLEX не установлено на другом компьютере, расчеты будут производиться?
Нет :(
Спасибо, прикольная штука, но если таблица с исходными значениями и та в которую они будут подставляться, находятся на разных листах, это не работает? или я что то не так делаю? =VLOOKUP3(SKLAD!A12:V600;SKLAD!8;$A$33;3) Помогите разобраться, пожалуйста!
Нужно указать только номер столбца из уже выделенной таблицы, не важно где находится, таблица уже обозначена. Столбец 1 или 2 =VLOOKUP3(SKLAD!A12:V600; 1;$A$33;2). Ну и ктр шифт ентер. Понятно что тебе не актуально уже, но может кому пригодится.
Может кто подскажет как сделать. Если это возможно.
Одна книга это один договор с клиентом, и последний лист в каждой книге это накопительный реестр.
Я копирую предыдущую книгу и заполняю договор с новым клиентом.
Как сделать чтобы в этом накопительном реестре накапливалась построчно информация о клиентах.
Или как по другому сделать чтобы этот накопительный реестр?
Добрый день. Подскажите: при появлении формул массива таблица очень сильно начинает тормозить; можно ли как то отключить автоматический пересчет формул только на этом листе книги или не использовать массив?
Формулы массива очень медленные - это да. Автоматический пересчет отключается только во всей книге, к сожалению.
Спасибо за ответ!
А есть какой-то другой способ выбрать все позиции входящие в заказ, т.е. сделать тоже самое, но без массива, например через ВПР?
@@Alex-cz6pq ВПР находит первое совпадение по списку, Поискпоз - последнее.
1. Через ВПР находим первое совпадение по заказам.
2. Через поискпоз и индекс со смещением на 1 строку вниз выводим следующую информацию по заказам, основываясь на первую ячейку, выдавшую первое совпадение через ВПР.
@@tabhub-official а как это в одной формуле записать. Может есть пример? Мне нужно для распечатки накладных.
@@Alex-cz6pq в одну формулу - никак, так как первая формула, собственно, сам ВПР, а вторая формула, в ячейку ниже - индекс(диапазон;поискпоз по ВПР+1)
Если условно.
сделал эту формулу и протянул на 100 ячеек, чтобы автоматом вытягивать номера заказов. данные берутся из Умной таблицы экселя.
но при вводе информации по новому заказу ексель зависает на 4-7 секунд. и это на intel i7!!!
есть ли вариант, который не приводит к зависанию екселя при добавлении новой строки в анализируемой таблице?
Формулы массива очень медленные, да :(
Что будет, если в товаре будет две одинаковые строки? Например, яблоки два раза.
Выведутся в результатах обе.
Это можно сделать гораздо проще и быстрее (менее 1 минуты) - через сводную таблицу. Кидаем "№ заказа" в область в фильтра, а "Товар" - в строки, и на этом ВСЁ.
Вместо формулы массива можно использовать функцию Агрегат.
Здравствуйте, я живу за пределами России, как мне приобрести вашу книгу?
В некоторые страны бывшего СНГ и ближайшего зарубежья наша служба доставки тоже возит. Вы откуда?
@@planetaexcel из Азербайджана
Можно ведь просто поставить фильтр на необходимое значение. Я конечно уважаю формулы, но только когда они облегчают задачу, а не усложняют ее:)
Предполагается, что исходные данные меняются 100 раз на дню. Каждый раз руками фильтровать не хочется, пусть это лучше формула сделает автоматически.
Добрый день! Всем привет! Коля, помогите пжл.
Спасибо!!!! Функция VLOOKUP3, это просто гениально!! Значительно упрощает работу!
А впр где?
Кто-то встречал Способы вывести результат этой функции в одной ячейке через запятую?
Для этого можно использовать Power Query www.planetaexcel.ru/techniques/8/1640/
Просто унизил)
Это очень сильное колдунство...