Как сделать левый ВПР в Excel
Вставка
- Опубліковано 23 січ 2017
- Как реализовать "левый ВПР", т.е. извлечение данных из столбца левее того, в котором ищем совпадения. Файл-пример можно скачать тут www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Меня зовут Александр и я экселеголик. Экселезависимость у меня около 8 месяцев. Недавно открыл для себя ВПР. И вот теперь у меня есть ВЫБОР! Спасибо большое Николай!
Главное - не пробовать Power Query. С него не соскочить уже никак 😁
Здравствуйте! Благодарю! Четко, внятно, без музыкального сопровождения и с хорошей дикцией! Лучший из лучших.
Спасибо :)
Наконец я научился левому ВПР с помощью Индекс и Поискпоз! Спасибо!
Вы самый полезный человек на планете! Спасибо брат!!! Вы мой БРАТ!)))
Николай, Вы, как всегда, даже для "давно решённых" задач наглядно показываете новые и, обычно, более быстрые и удобные варианты решения! Огромное Вам спасибо!!!
Николай, ВПР и ВЫБОР это супер!) Спасибо!)
случайно наткнулась на ваше видео, но теперь я ваш фанат) Надеюсь, что найду решение своей проблемы в ваших видео уроках. ваши видеоуроки одни из немногих, которые слушаешь без перемотки. Творческих успехов вам, Николай. Да и не только творческих ;-)
Спасибо! :)
Спасибо Николай, как всегда выручил!
Очень интересное сочетание choose и vlookup. Думаю пригодится)
Красота все это.
Спасибо за видеоурок.
Думал знаю про ВПР всё, ВПР+выбор просто супер
Спасибо большое Вам!!! Очень помогли
Были времена, когда надо было индексы и поискпозы писать) но просмотрх класс теперь)
Спасибо вам огромное
Очень интересное и познавательное видео. спасибо!
P.S. Являюсь обладателем книги "Microsoft Excel. Готовые решения - бери и пользуйся!", а вот второй книги "Microsoft Excel: Мастер формул" на сайте не нашёл.
Спасибо 👍
Супер!
Круть!
Николай, я уже очень много лет плотно работаю в Excel, знаю много фишек и функций. Но Вы удивляете простотой и гибкостью Ваших приёмов!👍👍👍 Как Вам удаётся найти, разсекретить и скомпоновать это всё?😃
😉
отлично. )
МОЩНО!!!!!!!!!!!!!!!!!!!
интересное видео, спасибо большое, VLookUp + Choose отличная штука, а можно что то похожее сделать для HLookUp? Например, если строка поиска идет под строкой значений?
Давно нечего нового не открывал для себя в Excel. Но вариант использования функции ВЫБОР с возможностью склеивать {1;2} / или менять порядок {2;1} столбцов меня удивил. Спасибо! Николай!
Интересно, а можно заменить{1;2} ссылками на ячейки со значениями, типо ="{"&G2&";"&G3&"}"
Напрямую - вряд ли. Но можно попробовать засунуть все это в ДВССЫЛ :)
Побывал =ДВССЫЛ("{"&G2&";"&G3&"}"), что-то не помогло ..
Зачем делать ссылку на ячейку с постоянной величиной, если эта величина никогда не станет переменной.
По аналогии с ИНДЕКС и ПОИСКПОЗ можно использовать комбинацию ДВССЫЛ, АДРЕС и ПОИСКПОЗ.
Можно, но учтите, пожалуйста, что ДВССЫЛ - волатильная функция, т.е. тормозить это дело будет в разы сильнее, к сожалению :(
Мне кажется, сами создатели Эксель так круто не могут))).
Могут-могут, я видел :)
Ты такой красивый
А как получит ссылки на последние ячейки(в нижней строке) динамического диапазона?
Добрый вечер. А как посмотреть исходный код функции поискпоз. А то мне не очень понятно,почему для поиска данных нужно упорядочивать массив, а для поиска заведомо несуществующего значения в рамках поиска последней ячейки - не нужно.
подскажите Николай, у меня такая ситуация
Может ли одна из функций ВПР, ПОИСКПОЗ или другие искать не по 1 столбцу, а по двум (в чем суть) у меня база телефонов, у кого-то он один, у кого-то 2 и есть у кого 3 - впихнуть в одну ячейку - не то, поэтому пришлось добавить напротив каждого сотрудника по 3и ячейки, у большинства 1 номер, но есть те, у кого есть 2 и 3 номера - они записаны в рядом стоящих ячейках по одному.
По одному столбцу находит все - офрмула работает на ура.
Но, к примеру, когда я ввожу для поиска номер телефона со сторого столбца или третьего - мне выдает Н/Д, задаю поиск $E$3:$F$178 - чтобы типа искало в 2х столбцах - выдает снова ошибку.
Вопрос: как сделать так чтобы искало номера телефонов не только в первом, но и во втором и в третьем столбце одновременно?
Заранее спасибо
Николай, у меня возник вопрос по поводу работы вашей функции VlookupS(в последнем примере): под результаты ее работы предусмотрели 8 ячеек для вывода результатов поиска. Что произойдет при выводе результатов(в данном таблице), если результатов у функции VlookupS будет больше восьми, например 12 ?То есть "лишние" четыре результата поиска будут просто "утеряны"?
И как заранее предусмотреть необходимое количество ячеек для вывода, если мы не знаем, какое количество результатов будет получено в результате работы функции VlookupS ? Например, количество результатов поиска будет равно 1000 и все они будут нам нужны для дальнейшего анализа или работы с ними.
Спасибо.
Нужно ввести формулу с запасом, а ошибки на лишних ячейках можно скрыть функцией ЕСЛИОШИБКА (IFERROR), например.
ИНДЕКС+ПОИСКПОЗ оказался в разы быстрее варианта через ВЫБОР
Про функцию выбор хотелось бы еще, первый раз с ней сталкиваюсь, где она еще может быть применена?
www.planetaexcel.ru/techniques/25/2639/
@@planetaexcel Спасибо !!
Привет Николай если в функции впр вести название неполностью пропустит допустим цвет товара ошибка н/д выходит, как выйти из этой ситуации погли бы это показать.
Посмотрите www.planetaexcel.ru/techniques/7/5636/
Николай как с вами связаться?
я правильно понимаю: если ячейки в текстовом формате, то ИНДЕКС+ПОИСКПОЗ не работает, а так же не работает VLookUp + Choose... необходимо формат в numbers переводить
Здравствуйте, хотел спросить у вас по поводу функции выбор: почему в английской версии {1\2} а не {1;2} как написано в самом экселе? Благодарю
Разделители в массивах констант различаются в зависимости от региональных настроек ПК. Как и разделители между целой и дробной частью и разделители тысяч и т.д.
Спасибо Вам за вопрос! Из него я узнал, почему у меня этот вариант не работает!)))
У меня английская версия.
Вопрос по второму способу. Что значит в формуле 1.2?
Это порядковые номера склеиваемых столбцов. Если из видео не очень понятно, то сходите по ссылке в описании и почитайте статью (там и файл-пример есть).
6:25
в чем разница между VLOOKUPS и VLOOKUP3?
Если Диапазон строк будет более 800 тыс, скажите будет сильно думать при обработке?
На таблице в 500 000 строк при тестировании на быстродействие результаты были примерно такие:
ВПР - 5.5 сек
ИНДЕКС+ПОИСКПОЗ - 5.3 сек
СУММЕСЛИ вместо ВПР - 19 сек
ВПР+ВЫБОР - 34 сек
Конкретные числа зависят от компа и разрядности Office, само-собой.
Макрофункцию VLOOKUPS не тестировал, но будет совсем долго, подозреваю.
Добрый день, можете подсказать что делать если номер заказа вводишь от руки и выходит ошибка н/д
Может быть у вас номера в таблице в виде текста?
Почему у меня ИНДЕКС+ПОИСКПОЗ не работает с разными листами?или он и не должен? когда ИНДЕКС+вычисляемая ячейка на одном листе, а ПОИСКПОЗ (что искать-на том же листе, а где искать-на другом)
ПС: много у вас полезных видео, но выходят очень редко..+они сильно начинают отставать по необходимости.. как и писали ниже Power Query много что упрощает, и даже мои, ну очень поверхностные знания по нем, меня сильно выручают..
у меня и на одном листе не работает, даже при полном копировании. Office 2016
Объясните пожалуйста, почему # заказа (и соотв. остальные данные в строке) нельзя просто найти ctrl+f или даже фильтром? В чем будет ошибка? Спасибо
2023
А как же ПРОСМОТР?
Да даже тупо через ЕСЛИ и массивные скобки можно сделать.
Левый ВПР в 2017-м?! Серьезно?! Лучше бы Николай обновил тему консолидация данных с разных листов с помощью Power Query, которая по умолчанию идет в Excel 2016, Office 365.
Вадим Окладников Nikolay Pavlov скорее сделает 100-е видео по функции ВПР и ГПР, чем Power Query. Сертифицированный тренер и эксперт в Excel нам всё азбуку объясняет, как сайентологи доказывают людям, что они не так говорят на своём родном языке, и дурачат их.
Спасибо за мнение, Николай учтет :)
Для информации: за весь прошлый год из 74 проведенных мной корпоративных тренингов Excel 2016 встретился аж целых 6 раз.
Умные таблицы тоже не часто используют, но видео по ним Николай исправно выкладывал:)
А если серьезно, то консолидация данных с разных листов, а также книг, без использования макросов стала возможна только при использовании Power Query.
Дык, я ж не спорю. У меня тут просто свой план по видеоурокам на 2017 год есть - и я по нему планомерно работаю. Про Power Query там есть, честное слово :)
Здравствуйте, как можно приобрести Ваши книги? Могут ли мне оказать платную поддержку для простых на мой, возможно ошибочный взгляд операций. Для таких, как Вы, специалистов это не займет много времени, я думаю. Приблизительно надо следующее: на листе 1 в шапке таблицы в ячейке А1 Наименование, в ячейке D1 Сумма. В теле таблицы наименования повторяются с точностью, а суммы меняются. Необходимо на новом листе извлекать из Листа 1 Наименование в одну конкретную ячейку, которая будет зафиксирована в алфавитном порядке, с возможностью добавления в случае появления нового наименования и добавлять суммы из столбца D первого листа, по этому наименованию. Смысл в том, что нужна простая формула, насколько это возможно, однажды мне написали макрос, но таблица стала неудобной для перемещения столбцов и прочего. Благодарю заранее, мне нравится как Вы доступно изъясняетесь.
Извините, небольшая поправка. Если на втором листе зафиксированные наименования, то я при помощи СУММЕСЛИ этого добилась. А вот возможности не делать этого вручную, а чтобы Excel делал сам выборку из наименований не могу никак найти. А это очень важно для контроля ошибки в буквенной части наименований. Один незаметный пробел и сумма уже неверная...
Может надо сделать первый лист сделать сводной таблицей и потом вводить суммесли? Но сводная таблица не терпит пустых ячеек...
Сколько же можно эту ВПР мурыжить? С тех пор, как есть ИНДЕКС + ПОИСКПОЗ, все эти ВПР и ГПР, как минимум, не актуальны.
Если вы смотрели видео, то там кроме ИНДЕКС+ПОИСКПОЗ еще 4 способа разбирается как бы...
к сожалению эксель уже давно не актуален в использовании... слишком глупая и не логичная программа, как при работе с таблицами, так и если брать те же таблицы из сети интернет, прогружая их через вкладку данные. Как минимум, эксель не умеет работать с числами, вида 1:1, он их переименовывает в дату. Скажете, так поставьте текстовый формат.. Но тут тоже не задача, при обновлении этих данных, которые берутся с определенного сайта, формат автоматом меняется, и это еще раз доказывает, что эксель самая убогая программа которую вообще можно было придумать. Есть масса других примеров, где эксель только увеличивает твою работу и время на эту работу.