Как сделать левый ВПР в Excel

Как реализовать "левый ВПР", т.е. извлечение данных из столбца левее того, в котором ищем совпадения. Файл-пример можно скачать тут www.planetaexcel.ru/techniques...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/

[@Terrible_Ivan_]

Здравствуйте! Благодарю! Четко, внятно, без музыкального сопровождения и с хорошей дикцией! Лучший из лучших.

[@planetaexcel]

Спасибо :)

[@Maslennikov_Alex]

Меня зовут Александр и я экселеголик. Экселезависимость у меня около 8 месяцев. Недавно открыл для себя ВПР. И вот теперь у меня есть ВЫБОР! Спасибо большое Николай!

[@planetaexcel]

Главное - не пробовать Power Query. С него не соскочить уже никак 😁

[@user-dx9ou4gg5o]

Наконец я научился левому ВПР с помощью Индекс и Поискпоз! Спасибо!

[@zoyirturkmanov1647]

Вы самый полезный человек на планете! Спасибо брат!!! Вы мой БРАТ!)))

[@zeza1986]

Николай, ВПР и ВЫБОР это супер!) Спасибо!)

[@JackFamous]

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

[@Delphino14]

Спасибо Николай, как всегда выручил!

[@Sunny-jm2fx]

Очень интересное сочетание choose и vlookup. Думаю пригодится)

[@May-yc1ew]

случайно наткнулась на ваше видео, но теперь я ваш фанат) Надеюсь, что найду решение своей проблемы в ваших видео уроках. ваши видеоуроки одни из немногих, которые слушаешь без перемотки. Творческих успехов вам, Николай. Да и не только творческих ;-)

[@planetaexcel]

Спасибо! :)

[@371MonaLiza]

Красота все это.

[@yurd563]

Спасибо за видеоурок.

[@user-lf4fu6re5v]

Спасибо большое Вам!!! Очень помогли

[@RobertKirakosyan]

Думал знаю про ВПР всё, ВПР+выбор просто супер

[@Exce11en1]

Очень интересное и познавательное видео. спасибо! P.S. Являюсь обладателем книги "Microsoft Excel. Готовые решения - бери и пользуйся!", а вот второй книги "Microsoft Excel: Мастер формул" на сайте не нашёл.

[@teimurazgagua614]

Спасибо вам огромное

[@fraisertinko]

Были времена, когда надо было индексы и поискпозы писать) но просмотрх класс теперь)

[@dumanmakulbekov6347]

Супер!

[@stephane7211]

интересное видео, спасибо большое, VLookUp + Choose отличная штука, а можно что то похожее сделать для HLookUp? Например, если строка поиска идет под строкой значений?

[@user-fl7xn7cl2l]

Николай, я уже очень много лет плотно работаю в Excel, знаю много фишек и функций. Но Вы удивляете простотой и гибкостью Ваших приёмов!👍👍👍 Как Вам удаётся найти, разсекретить и скомпоновать это всё?😃

[@planetaexcel]

😉

[@Tommy7417]

Спасибо 👍

[@annacartusce1464]

МОЩНО!!!!!!!!!!!!!!!!!!!

[@defender2130]

Круть!

[@eminorujov5915]

отлично. )

[@biweb13]

Давно нечего нового не открывал для себя в Excel. Но вариант использования функции ВЫБОР с возможностью склеивать {1;2} / или менять порядок {2;1} столбцов меня удивил. Спасибо! Николай! Интересно, а можно заменить{1;2} ссылками на ячейки со значениями, типо ="{"&G2&";"&G3&"}"

[@planetaexcel]

Напрямую - вряд ли. Но можно попробовать засунуть все это в ДВССЫЛ :)

[@biweb13]

Побывал =ДВССЫЛ("{"&G2&";"&G3&"}"), что-то не помогло ..

[@user-fi2jc1xk4i]

Зачем делать ссылку на ячейку с постоянной величиной, если эта величина никогда не станет переменной.

[@tamerlan4602]

Николай как с вами связаться?

[@ISockol]

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

[@user-re4ez1xr1t]

А как получит ссылки на последние ячейки(в нижней строке) динамического диапазона?

[@millenniuym]

подскажите Николай, у меня такая ситуация Может ли одна из функций ВПР, ПОИСКПОЗ или другие искать не по 1 столбцу, а по двум (в чем суть) у меня база телефонов, у кого-то он один, у кого-то 2 и есть у кого 3 - впихнуть в одну ячейку - не то, поэтому пришлось добавить напротив каждого сотрудника по 3и ячейки, у большинства 1 номер, но есть те, у кого есть 2 и 3 номера - они записаны в рядом стоящих ячейках по одному. По одному столбцу находит все - офрмула работает на ура. Но, к примеру, когда я ввожу для поиска номер телефона со сторого столбца или третьего - мне выдает Н/Д, задаю поиск $E$3:$F$178 - чтобы типа искало в 2х столбцах - выдает снова ошибку. Вопрос: как сделать так чтобы искало номера телефонов не только в первом, но и во втором и в третьем столбце одновременно? Заранее спасибо

[@gonzagonsales4428]

Николай, у меня возник вопрос по поводу работы вашей функции VlookupS(в последнем примере): под результаты ее работы предусмотрели 8 ячеек для вывода результатов поиска. Что произойдет при выводе результатов(в данном таблице), если результатов у функции VlookupS будет больше восьми, например 12 ?То есть "лишние" четыре результата поиска будут просто "утеряны"?

[@gonzagonsales4428]

И как заранее предусмотреть необходимое количество ячеек для вывода, если мы не знаем, какое количество результатов будет получено в результате работы функции VlookupS ? Например, количество результатов поиска будет равно 1000 и все они будут нам нужны для дальнейшего анализа или работы с ними. Спасибо.

[@planetaexcel]

Нужно ввести формулу с запасом, а ошибки на лишних ячейках можно скрыть функцией ЕСЛИОШИБКА (IFERROR), например.

[@gamemirror9093]

Мне кажется, сами создатели Эксель так круто не могут))).

[@planetaexcel]

Могут-могут, я видел :)

[@user-ik3pd4sq4v]

По аналогии с ИНДЕКС и ПОИСКПОЗ можно использовать комбинацию ДВССЫЛ, АДРЕС и ПОИСКПОЗ.

[@planetaexcel]

Можно, но учтите, пожалуйста, что ДВССЫЛ - волатильная функция, т.е. тормозить это дело будет в разы сильнее, к сожалению :(

[@andrewzakharov4430]

я правильно понимаю: если ячейки в текстовом формате, то ИНДЕКС+ПОИСКПОЗ не работает, а так же не работает VLookUp + Choose... необходимо формат в numbers переводить

[@Sedef.Sarmasik]

Здравствуйте, хотел спросить у вас по поводу функции выбор: почему в английской версии {1\2} а не {1;2} как написано в самом экселе? Благодарю

[@planetaexcel]

Разделители в массивах констант различаются в зависимости от региональных настроек ПК. Как и разделители между целой и дробной частью и разделители тысяч и т.д.

[@ilyaerokhov]

Спасибо Вам за вопрос! Из него я узнал, почему у меня этот вариант не работает!))) У меня английская версия.

[@AzizAziz-kr7sl]

Привет Николай если в функции впр вести название неполностью пропустит допустим цвет товара ошибка н/д выходит, как выйти из этой ситуации погли бы это показать.

[@planetaexcel]

Посмотрите www.planetaexcel.ru/techniques/7/5636/

[@ana-he7ih]

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

[@planetaexcel]

www.planetaexcel.ru/techniques/25/2639/

[@ana-he7ih]

@@planetaexcel Спасибо !!

[@vlad050987]

в чем разница между VLOOKUPS и VLOOKUP3?

[@RealMadrid-tr1jr]

Вопрос по второму способу. Что значит в формуле 1.2?

[@planetaexcel]

Это порядковые номера склеиваемых столбцов. Если из видео не очень понятно, то сходите по ссылке в описании и почитайте статью (там и файл-пример есть).

[@user-kr6xe4pt8b]

Если Диапазон строк будет более 800 тыс, скажите будет сильно думать при обработке?

[@planetaexcel]

На таблице в 500 000 строк при тестировании на быстродействие результаты были примерно такие: ВПР - 5.5 сек ИНДЕКС+ПОИСКПОЗ - 5.3 сек СУММЕСЛИ вместо ВПР - 19 сек ВПР+ВЫБОР - 34 сек Конкретные числа зависят от компа и разрядности Office, само-собой. Макрофункцию VLOOKUPS не тестировал, но будет совсем долго, подозреваю.

[@gaya1942]

Ты такой красивый

[@user-bh4qz8sm5f]

Добрый день, можете подсказать что делать если номер заказа вводишь от руки и выходит ошибка н/д

[@planetaexcel]

Может быть у вас номера в таблице в виде текста?

[@user-bk1jk4wh3w]

Почему у меня ИНДЕКС+ПОИСКПОЗ не работает с разными листами?или он и не должен? когда ИНДЕКС+вычисляемая ячейка на одном листе, а ПОИСКПОЗ (что искать-на том же листе, а где искать-на другом) ПС: много у вас полезных видео, но выходят очень редко..+они сильно начинают отставать по необходимости.. как и писали ниже Power Query много что упрощает, и даже мои, ну очень поверхностные знания по нем, меня сильно выручают..

[@sergeynidel6610]

у меня и на одном листе не работает, даже при полном копировании. Office 2016

[@user-lp4gb3tp7t]

Объясните пожалуйста, почему # заказа (и соотв. остальные данные в строке) нельзя просто найти ctrl+f или даже фильтром? В чем будет ошибка? Спасибо

[@user-og7gq9kj8s]

6:25

[@VitaliyZlobin]

ИНДЕКС+ПОИСКПОЗ оказался в разы быстрее варианта через ВЫБОР

[@user-qn5by5iv7u]

2023

[@sadialla]

Здравствуйте, как можно приобрести Ваши книги? Могут ли мне оказать платную поддержку для простых на мой, возможно ошибочный взгляд операций. Для таких, как Вы, специалистов это не займет много времени, я думаю. Приблизительно надо следующее: на листе 1 в шапке таблицы в ячейке А1 Наименование, в ячейке D1 Сумма. В теле таблицы наименования повторяются с точностью, а суммы меняются. Необходимо на новом листе извлекать из Листа 1 Наименование в одну конкретную ячейку, которая будет зафиксирована в алфавитном порядке, с возможностью добавления в случае появления нового наименования и добавлять суммы из столбца D первого листа, по этому наименованию. Смысл в том, что нужна простая формула, насколько это возможно, однажды мне написали макрос, но таблица стала неудобной для перемещения столбцов и прочего. Благодарю заранее, мне нравится как Вы доступно изъясняетесь.

[@sadialla]

Извините, небольшая поправка. Если на втором листе зафиксированные наименования, то я при помощи СУММЕСЛИ этого добилась. А вот возможности не делать этого вручную, а чтобы Excel делал сам выборку из наименований не могу никак найти. А это очень важно для контроля ошибки в буквенной части наименований. Один незаметный пробел и сумма уже неверная...

[@sadialla]

Может надо сделать первый лист сделать сводной таблицей и потом вводить суммесли? Но сводная таблица не терпит пустых ячеек...

[@evgeniytyumen4613]

А как же ПРОСМОТР?

[@evgeniytyumen4613]

Да даже тупо через ЕСЛИ и массивные скобки можно сделать.

[@Midavok]

Левый ВПР в 2017-м?! Серьезно?! Лучше бы Николай обновил тему консолидация данных с разных листов с помощью Power Query, которая по умолчанию идет в Excel 2016, Office 365.

[@1iuh]

Вадим Окладников Nikolay Pavlov скорее сделает 100-е видео по функции ВПР и ГПР, чем Power Query. Сертифицированный тренер и эксперт в Excel нам всё азбуку объясняет, как сайентологи доказывают людям, что они не так говорят на своём родном языке, и дурачат их.

[@planetaexcel]

Спасибо за мнение, Николай учтет :) Для информации: за весь прошлый год из 74 проведенных мной корпоративных тренингов Excel 2016 встретился аж целых 6 раз.

[@Midavok]

Умные таблицы тоже не часто используют, но видео по ним Николай исправно выкладывал:) А если серьезно, то консолидация данных с разных листов, а также книг, без использования макросов стала возможна только при использовании Power Query.

[@planetaexcel]

Дык, я ж не спорю. У меня тут просто свой план по видеоурокам на 2017 год есть - и я по нему планомерно работаю. Про Power Query там есть, честное слово :)

[@1iuh]

Сколько же можно эту ВПР мурыжить? С тех пор, как есть ИНДЕКС + ПОИСКПОЗ, все эти ВПР и ГПР, как минимум, не актуальны.

[@planetaexcel]

Если вы смотрели видео, то там кроме ИНДЕКС+ПОИСКПОЗ еще 4 способа разбирается как бы...

[@ShooterStar]

к сожалению эксель уже давно не актуален в использовании... слишком глупая и не логичная программа, как при работе с таблицами, так и если брать те же таблицы из сети интернет, прогружая их через вкладку данные. Как минимум, эксель не умеет работать с числами, вида 1:1, он их переименовывает в дату. Скажете, так поставьте текстовый формат.. Но тут тоже не задача, при обновлении этих данных, которые берутся с определенного сайта, формат автоматом меняется, и это еще раз доказывает, что эксель самая убогая программа которую вообще можно было придумать. Есть масса других примеров, где эксель только увеличивает твою работу и время на эту работу.