Мульти-ВПР: ищем все совпадения
Вставка
- Опубліковано 4 жов 2015
- Два способа извлечь с помощью формул (обычных и формул массива) сразу ВСЕ значения из массива по условию, а не только первое, как делает обычная ВПР. Подробная статья и скачать файл-пример тут planetaexcel.ru/techniques/2/81/
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Вы самый лучший тренер по экселю. Спокойно доходчиво и немаловажно с дикцией объясняете. Действия любого уровня сложности. Браво. Аплодирую стоя.
Вот и наступил один из многих моментов, когда специально нашёл это видео по теме, чтобы отдельно ПОБЛАГОДАРИТЬ Николая за мегаполезный материал, который пригодился на работе и сэкономил МАССУ времени и облегчил рутину! :)
СПА-СИ-БО!♥
Николай , сколько же вы пользы приносите людям ! Спасибо вам большое !
Даже по прошествии нескольких лет, это просто бомба. Спасибо, Николай!!!
Николай, спасибо Вам большое за Ваш труд. Когда я ищу в поиске решение "екселевских" проблем, то всегда в результатах поиска выбираю Ваши статьи. Всегда по делу, всегда исчерпывающе, всегда понятно.
Воистину когда знаешь то все просто. Спасибо Вам Николай за ваш труд. Очень все доходчиво и просто объясняете
Николай, благодарю. Вы потрудились дать информацию доступно и понятно. Спасибо за ваш бескорыстный труд и помощь! Усехов вам во всем!
Отдельная уважуха за то что в первом примере "зашел издалека"
Здравствуйте, Николай! Спасибо! Как всегда вовремя, по делу, без лишней воды.
Вы самый лучший в мире УЧИТЕЛЬ! спасибо, что Вы делитесь своими знаниями!!!🤗
Николай, спасибо большое. У Вас такой красивый голос, что обучение становится очень приятным и совсем не утомляет).
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 лет
Рад это слышать :) Значит - не зря старался :)
супер, опять то, что нужно
Спасибо огромное за такие видео, очень помогло в работе😊
Помогло, спасибо!
Спасибо Вам ОГРОМНОЕ!!!!!!!!
Круто) спасибо!
спасибо за труд
Спасибо Николай
Круто!! Спасибо огромное!!
Спасибо Николай! :)
спасибо!
Спасибо Николай ака! Как Вы мне облегчаете жизнь ;(
Большое спасибо.
Спасибо большое!!
Это лайк!
Николай, спасибо огромное за урок, у меня такая ситуация: в списке повторяются значения, из массива я уникальные не могу вывести, потому что сумму не корректно считает, можно ли усовершенствовать формулу, чтобы выводило только уникальные значения в данной ситуации, спасибо
Николай, спасибо за все Ваши доступные для быстрого понимания ролики.
Время идет, эксель меняется. Используя "функции динамических массивов" (что стало доступно несколько позже - после 2019 года), как преобразуется формула?
Гений 👍🚀
Николай, спасибо за видео очень помогает в работе. У меня вопрос по описанному Вами способу, возможно ли этот способ применить если у меня не просто проверка на совпадение номера заказа, а попадание № заказа в определенный диапазон (например от 2 до 4), проверку которого я осуществляю с помощью функции И? Пробую поставить после функции ЕСЛИ, функцию И и проверить этот диапазон, но видимо не правильный синтаксис или таким способом не возможно решить данную задачу?
Спасибо большое что ответили. Я уж было подумал что не ответите из-за большого объема вопросов вроде моего. Одна сложность. У меня пока только версия excel 2003, официальная. Мне ее когда то подарили. Подскажите, где взять новую рабочую программу, самую свежую excel и какая сейчас последняя существует?
дякую за уроки все супер !!!
Николай, добрый день! Подскажите, пожалуйста, как можно расширить функционал описанный в этом уроке на два и более условия отбора? Простое добавление оператора "И" в условие "ЕСЛИ" не помогает, к сожалению :(
Спасибо большое
Доходчиво.
Спасибо!
Здравствуйте , Николаи а можно сделать так чтобы когда делаеши формулу из документа на другом документе скопировать и цвет ечеик из первого документа....
Хорошо, а если на другом компьютере не установлен этот пакет - будет vlookup работать?
спасибо!!!
Спасибо. Удастся теперь осуществить сортировку по столбцам из той перемешки, что у меня была. Vlookup3 использовать буду.
Thanks
Николай здравствуйте все красиво и доступно, а у меня такой вопрос.Мне необходимо составлять требование накладную в ЕХЕЛЕ, есть исходная деталь например Вал в сборе которая состоит из других деталей к примеру подшипник -2шт. , вал-1шт., шестерня-2шт., фрикцион-1шт. ВОПРОС - как сделать формулами массивами , возможность выбрать например ВАЛ в СБОРЕ 1,2 или 3 шт и чтобы при этом в таблицу вытягивало все из чего ВАЛ состоит и сразу высчитывало сколько необходимо комплектующих , заранее благодарен ищу по инету ни где найти не могу
Thank you
Добрый день! Подскажите, как дополнить формулу, чтобы поиск осуществлялся по неточному критерию (текст), например по первым 6 символам?
у меня вопрос по поводу последней макрофункции. допустим мы сделали формулу у себя. а если открыть файл в другом екселе (др ноутбук или комп) где нет этой функции или ексель 2007 она будет работать?
К способам до 09:14 можно применять файл (из которого надо "вытянуть" данные) расположенного в одной папке с книгой в которую собираются данные?
Доброго времени суток. Подскажите пожалуйста, есть таблица броней туров и даты туров выводятся на другой лист. За время пребывания клиент может купить экскурсии как на 1 день, так же и на 2 и 3 дня. Мне надо, чтобы в ячейке выводилась дата последнего дня тура. Как это сделать? То есть в строке найти последнюю поставленную дату, Спасибо
Спасибо. интересно. Но сразу вопрос как решить проблему кол-ва зеленых строк если не знаешь точное кол-во заполняемых ячеек. в вашем случае в заказе их не более 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?
Спасибо за полезные и понятные видео. Мне многое нравится, кроме вставок с английскими названими, хорошо, что Вы похоже знаете английский, но не надо это тут помечать.
класс
Спасибо. А что делать, если у меня целый список значений которых нужно найти. У вас на примере не больше 10 заказов. А что делать если у меня их более 10тыс.записей. Как обработать? Чтобы указывать не конкретное значение, а диапазон значений которых нужно найти.
Тут лучше использовать расширенный фильтр - см. www.planetaexcel.ru/techniques/2/197/ или Power Query
Добрый день, не знаю, смотрите ли Вы комментарии к давним видео, но спрошу. Была безумно рада, увидев ваш ролик: задача популярная и по сути не сложная, но почему то решение редко встречается в инете. И у меня не получилось у меня((. Мало того, я полностью скопировала ваши данные с примера, чтобы уж наверняка , все равно пустая ячейка. Очень прошу, подскажите, пож-та, какие есть нюансы у данного массива? Ни цифры , ни текст - ничего не ищет.
Добрый день! Николай, а если таких значений множество ?
Вопрос. А если таблица Заказов постоянно пополняется . В неё входят кроме # заказа, название товара еще и количество и дата заказа. Можно ли выбирать по номеру и дате заказа.... Чтобы находило товар и количество. Суть в том, что хотел применить это при выписывании фактуры для клиента. Тогда при выписывании фактуры достаточно будет ввести номер и дату, а товар и его количество подтягиваются с таблицы заказы. Как можно решить эту задачу?
Спасибо за урок!
Вопрос: Можно ли подобное провернуть не с цифрами, а с какими-либо текстовыми данными. И если нет, какая функция может помочь при выдержке текстовых данных из массива?
Да, можно. в серии Ctrl+Shift+Enter Майк Гирвин объясняет как это делать. скоро в Эксель связанный с офис 365 будут специальные функции которые за раз смогут выдовать такой результат.
Здравствуйте! А можно эту формулу как-то изменить для поиска всех совпадений из другой таблицы?
Здравствуйте! Помогите решить проблему. Для определения мест участников соревнований использую "РАНГ РВ". Очень нравится как работает эта функция. Корректно расставляет места. Но столкнулся с проблемой: когда ввожу результаты забега на 800м. (минуты.секунды,сотые секунды) например 1.25,98 функция выдаёт ошибку изза точки после первого знака. А мне нужно записывать только в таком формате. Подскажите можно ли решить эту проблему добавив к функции "РАНГ РВ" какую-то формулу, и какую именно? Я не настолько силён в этом чтобы разобраться.
Николай, как сделать впр массивом не вниз, а вправо?
А можно зеленую табличку заполнять не вертикально, а горизонтально, чтоб значения заполнялись в соседние ячейки правее?
Здравствуйте! Нужно произвести вычисления в одной строке, например: (3+1)×2+4-2 , и получить результат в соседней строке. Подскажите, как это сделать, заранее спасибо!
Отличная функция, спасибо!!! А можно все значения вывести например через запятую в одну ячейку???
Не вопрос - посмотрите www.planetaexcel.ru/techniques/7/205/
Спасибо Вам, Николай. Один вопрос, в первом примере Вы ввели формулу массива, а у меня странным образом эта формула сработала просто так. Почему, не подскажите?
"Просто так" она тоже работает, но неверно.
Мучаюсь месяц а то и больше, подскажите: Мне нужно найти минимальное (максимальное) значение в столбце таблицы одного наименования (по трем критериям (столбцам) - названия, цены и главное факт.наличия (до продажи) для дальнейшего анализа. Перепробовал массу ваших формул, массивов, индекс, поиспоз не помогло. Подробно могу сбросить на ваш емайл
Вот статья про поиск минимального или максимального значения по условию(ям) - посмотрите www.planetaexcel.ru/techniques/2/241/
Вопрос
У меня 2 таблицы
Условно товар в виде шифров и их цены в 1 табл и шифры в разбросанном порядке в 2 таблице. Как цены подходящие к каждому шифру из 1 таблицы присвоить к шифрам во 2 таблице подскажите пожалуйста?
Через этот Ваш способ приходится для 1 категории шифров вставлять в формулу определенный шифр чтобы цены вставлялись, но остальные уже не вставляет для других шифров
Надо вручную каждый шифр вбивать
А если все это сделать при помощи сводной таблицы: номер заказа вывести в фильтр?
Добрый день. Подскажите: при появлении формул массива таблица очень сильно начинает тормозить; можно ли как то отключить автоматический пересчет формул только на этом листе книги или не использовать массив?
Формулы массива очень медленные - это да. Автоматический пересчет отключается только во всей книге, к сожалению.
Спасибо за ответ!
А есть какой-то другой способ выбрать все позиции входящие в заказ, т.е. сделать тоже самое, но без массива, например через ВПР?
@@Alex-cz6pq ВПР находит первое совпадение по списку, Поискпоз - последнее.
1. Через ВПР находим первое совпадение по заказам.
2. Через поискпоз и индекс со смещением на 1 строку вниз выводим следующую информацию по заказам, основываясь на первую ячейку, выдавшую первое совпадение через ВПР.
@@tabhub-official а как это в одной формуле записать. Может есть пример? Мне нужно для распечатки накладных.
@@Alex-cz6pq в одну формулу - никак, так как первая формула, собственно, сам ВПР, а вторая формула, в ячейку ниже - индекс(диапазон;поискпоз по ВПР+1)
Если условно.
Может кто подскажет как сделать. Если это возможно.
Одна книга это один договор с клиентом, и последний лист в каждой книге это накопительный реестр.
Я копирую предыдущую книгу и заполняю договор с новым клиентом.
Как сделать чтобы в этом накопительном реестре накапливалась построчно информация о клиентах.
Или как по другому сделать чтобы этот накопительный реестр?
сделал эту формулу и протянул на 100 ячеек, чтобы автоматом вытягивать номера заказов. данные берутся из Умной таблицы экселя.
но при вводе информации по новому заказу ексель зависает на 4-7 секунд. и это на intel i7!!!
есть ли вариант, который не приводит к зависанию екселя при добавлении новой строки в анализируемой таблице?
Формулы массива очень медленные, да :(
А если надстройка PLEX не установлено на другом компьютере, расчеты будут производиться?
Нет :(
А у меня почему то выдает не все нужные элементы и выдает даже те которые не попадают под притерий поиска.
Спасибо, прикольная штука, но если таблица с исходными значениями и та в которую они будут подставляться, находятся на разных листах, это не работает? или я что то не так делаю? =VLOOKUP3(SKLAD!A12:V600;SKLAD!8;$A$33;3) Помогите разобраться, пожалуйста!
Нужно указать только номер столбца из уже выделенной таблицы, не важно где находится, таблица уже обозначена. Столбец 1 или 2 =VLOOKUP3(SKLAD!A12:V600; 1;$A$33;2). Ну и ктр шифт ентер. Понятно что тебе не актуально уже, но может кому пригодится.
А если нужно вытащить не все значения, а только первое (ну тут стандарт ВПР), ТОЛЬКО второе, только третье, ... ?
По второму варианту в итоге вышло #число!
актуально и в 2019
До нового года осталось... всего ничего. Поэтому, актуально и в 20-м. :)
И в 2021
Здравствуйте, я живу за пределами России, как мне приобрести вашу книгу?
В некоторые страны бывшего СНГ и ближайшего зарубежья наша служба доставки тоже возит. Вы откуда?
@@planetaexcel из Азербайджана
Вместо формулы массива можно использовать функцию Агрегат.
поставил лайк. Спасибо Николай. Вы существенно оптимизировали мои трудозатраты по экселю. Не трачу время на бессмысленный мартышкин труд.
Ну а теперь у меня два вопроса. 1) я скачал с интернета один эксель файл которая считает оптимизацию распила. Можно считать что это целая программа. Экселька просто бесподобная. Многие мои клиенты не перестают восхищаться. Но есть проблема. Эксель файл открывается только на виндоус 7. На восьмерке и на десятке матюкается на то что не хватает модуля lpsolve55. Почему так?
Ну и второй вопрос - какая падла может ставить дизлайк вашим роликам?
дизлайки ставят идиоты и завистники))
Скинь свой файлик, попробую победить проблему, если актуально ещё.
Что будет, если в товаре будет две одинаковые строки? Например, яблоки два раза.
Выведутся в результатах обе.
Это можно сделать гораздо проще и быстрее (менее 1 минуты) - через сводную таблицу. Кидаем "№ заказа" в область в фильтра, а "Товар" - в строки, и на этом ВСЁ.
А впр где?
Можно ведь просто поставить фильтр на необходимое значение. Я конечно уважаю формулы, но только когда они облегчают задачу, а не усложняют ее:)
Предполагается, что исходные данные меняются 100 раз на дню. Каждый раз руками фильтровать не хочется, пусть это лучше формула сделает автоматически.
Кто-то встречал Способы вывести результат этой функции в одной ячейке через запятую?
Для этого можно использовать Power Query www.planetaexcel.ru/techniques/8/1640/
Спасибо!!!! Функция VLOOKUP3, это просто гениально!! Значительно упрощает работу!
Добрый день! Всем привет! Коля, помогите пжл.
Это очень сильное колдунство...
Просто унизил)
Урок записан очень качественно! Но мне не понятно зачем все эти танцы с бубнами!? Не проще использовать фильтр по первому столбцу? Убедите - поставлю лайк, а пока пользы из урока не извлёк :)
Это не танцы с бубнами,а эффективный инструмент для создания ДИНАМИЧЕСКОЙ выборки,конечно при этом не самый простой для понимания.Дает следующее преимущество по сравнению с обычным или расширенным фильтром-вам не надо каждый раз сначала применять фильтр,а потом его отключать.Вы просто меняете значение критерия отбора.Т.е. в данном случае вместо двух действий вы выполняете одно.Далее, что если у вас отбор не по одному условию,а по нескольким?Вам придется либо задавать несколько условий с помощью автофильтра,либо использовать расширенный фильтр,а потом,опять же,отменять их действие.Просто также посчитайте количество действий при этом,а потом еще потраченное на это время.А в случае применения метода,описанного в уроке количество действий равно все также одному.Конечно,если перед вами стоят такие не сложные задачи,которые можно быстро решить одноразовым применением автофильтра,то в создании динамической выборки с использованием формул массива нет необходимости.
Хоть я уже не помню о чём шла речь, ответили достойно. Можно сказать, убедили! Ставлю лайк, как и обещал)
@@GukovTimur дополню:
Данный метод применим к большому объему информации с невозможностью применения фильтров.
Частный пример - примитивный.
Куда больше подходит вариант для формирования файлов к импорту, где мы отбираем информацию из большого массива, скажем, из склада товаров.
Сортируем по признакам (в примере - одинаковые заказы), рассовываем по вкладкам или по иерархии увеличения от минимума к максимуму, с учетом небольшого дополнения по наличию этих товаров у поставщика и т.д.
Т.е., просто так фильтр не применить, а если применить - будет как минимум на пару-тройку действий до результата больше, чем бы оно автоматически сортировало и выписывало из общего массива данных нужные позиции.
В сухом остатке: скопировали данные в массивное поле -> получили рассортированный результат по признакам (наличию, одинаковым категориям, одинаковым параметрам, и т.д.).
Это же в разы быстрее, чем фильтровать ;)
Ну и, если есть возможность избегать формулы массивов (ctrl+shift+enter) - стоит это делать, потому, что - тормозит нереально это дело, но система рабочая)
2023
Да, не старют душой ветераны, нет... 😁
@@planetaexcel 🫡