Аналог функции ВПР в Excel ➤ Поиск всех совпадений

[@КонстантинВерланов]

Очень странно, что так много зрителей ругают автора. Да, сложная формула. Но суть в другом. Это демонстрация. Теперь вы знаете, что есть такой способ. Вставляете такую формулу в свою задачу и пользуетесь следующие 20 лет, никогда больше не задумываясь о её сложности. Андрей, спасибо.

[@alexshat4715]

Спасибо за полезность! Громоздкая формула, такую сложно запомнить) А если по-быстрому, то в работе использую всегда сводную)

[@Albertus99]

Большое спасибо за хороший пример! Отдельное большое спасибо за то что раскладываете формулы по отдельности! Это очень умно! Есть пожелание чтобы в текстовой версии четко была прописана каждая формула по отдельности. Это можно заменить наличием всех формул в примере как например у Николая Павлова что будет еще лучше. И тогда будет полный шикардос.

[@arustik7]

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

[@АлексейЖигирь-я5т]

Или на отдельном листе оставлять описания к формулам, макросам и пр.

[@arustik7]

@@АлексейЖигирь-я5т Все равно сложно. Проще дробить на блоки поменьше.

[@АлексейЖигирь-я5т]

@@arustik7 Ну да, вы правы. Столбцы потом или скрыть или сгруппировать, чтобы глаз не мозолили.

[@aygerimuatkhanova6368]

Андрей? Можете помочь? У меня ошибка ПЕРЕНОС если в вашу формулу пишу два условия,а не одно. В целом она работает, но когда начинаю собирать тогда ошибки

[@СергейШонхор]

Дружище, Ты гений!!!

[@Serega_Zaicev]

Вы офигенно растолковали, Спасибо !!! )))

[@VernamGD]

Великолепное видео :)

[@sergeyharuk4461]

Да, интересно. Однако своднаяв данном случае намного проще. Это мое мнение, а автору огромный респект за информацию ло использовании функции ИНДЕКС в формуле массива. Спасибо!

[@НадеждаЖукова-б1р]

Очень полезный урок. Спасибо!!!

[@SWINE137]

Спасибо за потраченое время)! Хорошее видео!)👏👏👏👍

[@freegait7074]

Здравствуйте, спасибо за видео, Андрей, скажите пожалуйста, а как можно запихнуть этот список заказов по выбранному менеджеру в выпадающий список.

[@aygerimuatkhanova6368]

Здравствуйте, спасибо за отличный пример. Я хочу включить два условия в формуле Если и у меня возникает ошибка перенос

[@АндрейСахно]

Спасибо огромное за Ваш труд! Успехов во всём!

[@MrDimmon777]

Использовал подобную формулу лет десять назад, ещё excel 2003 был, получилась ещё длиннее, так как не было функций типа ЕСЛИОШИБКА итд. Кстати поднял неплохие по тем временам деньги. Сейчас я бы написал макрос какой нибудь, вышло бы проще, и эффективнее!

[@IsmanOsmon]

Я вообще не могу поднять деньги никакие. как вам удается?

[@jg6ez7vt4e]

Спасибо большое за ролик. Но вот повторить данную формулу с учетом некоторых изменений я не смог. А есть хоть какая-то возможность в том числе за деньги получить у Вас консультацию в части решения задача автоматизации в Excel?

[@TAYGAGIL]

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

[@IsmanOsmon]

ВПР использовать может быть

[@papaparovoz959]

Пожалуйста скажите есть ли аналог этой формулы для поиска по нескольким условиям?

[@АлександрСоловьев-ш7ь]

Приходилось решать подобную задачу, использовал индекс/поискпоз/смещ +допстолбец для номеров строк

[@edzu2601]

Очень полезно!!! Спасибо!!!

[@sasharom1991]

Очень полезное видео, у меня получилось сделать, но есть вопрос. Как можно сделать, что бы то же самое работало на 2 листах, а выводилось в один. И в нем можно было все сортировать по статусам?

[@ОльгаБ-в8к]

БОЖЕ! Что здесь происходит? Это прекрасно ))))

[@AlexanderParygin-y7h]

Спасибо!

[@sunnatsaydaxmedov9687]

Спасибо

[@Роман-з2ъ9я]

Спасибо! А как искать текст, если он является частью в ячейке, но уникален? "Яблоко" искать в "111 Яблоко 111"? Банан в "апвап Банан555 апвап" Ни через ВПР, Ни индексом не получается (

[@Iitellyouwhat]

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

[@РостиславМороз-щ3х]

А как ви сделали с правой сторони ячейка "сергеева" что там можна вибрать другие имена?

[@alexanderlessky7268]

отлично!!!

[@mrpletka]

А как вывести найденные номера заказов в одной ячейке через запятую?

[@ДанилТокарев-ш6в]

Не ищем легких путей) Спасибо

[@Natalia-bo8ki]

Спасибо молодец всё понятное

[@АльбертВаркентин]

Здравствуйте! С вами можно как-то связаться? Есть вопрос по Excel.

[@АлександрОберемченко-г6з]

Добрый день Андрей. мне надо функцию "поиск решений" у меня её нет (( я захожу в параметры иксель в параметрах стоит только функция язык. Подскажите как быть?

[@svitlana4652]

У меня ТТН в ексель.Кто подскажет, как сделать чтобы при выборе из выпадающего списка автомобиля, автоматом заполнялись параметры машины в горизонтальной строке на пять колонок. Заранее спасибо.

[@oxanabruner7941]

Андрей как с вами можно связаться?,

[@andreysukhov]

можно написать на почту [e-mail]

[@Crptos_]

Рассказано хорошо, но с первого раза не понял, может конечно проблема что смотрю с телефона и сразу не повторяю для наглядности. Спасибо 🙂

[@АлексейЖигирь-я5т]

Урок полезен тем, что показывает способы сборки длинных формул и подробно рассказано про их функционал. Но именно для данной задачи такой подход - это ОГРОМНАЯ трата времени! Задача отображения всех заказов по конкретному менеджеру решается с помощью Сводной Таблицы за 1 минуту! Представьте, что у вас в подчинении несколько отделов, в которых по несколько менеджеров и вам вдруг захотелось узнать заказы всего отдела или нескольких менеджеров сразу? По методике этого урока надо неизбежно переделывать и без того сложную формулу. Это очень не практично. А вот Сводная Таблица с помощью фильтра решает и эту задачу очень легко и быстро. Вывод: этот урок о том как сделать простое сложным.

[@andreysukhov]

Это видео о том, как создать АНАЛОГ функции ВПР. Функцию ВПР используют для подтягивания данных из одной таблицы в другую, возможно оформленную по каким-то внутренним правилам организации (готовый бланк). Сводные для этого обычно не годятся. Да, сводные таблицы - отличный инструмент для анализа данных, но далеко не во всех ситуациях они применимы. По сводным на канале есть отдельный плейлист.

[@АлексейЖигирь-я5т]

@@andreysukhov Да, Индекс и ПоискПоз - это замечательная связка. И в этом видео о них вы хорошо расказали. Но на мой взгляд неудачно пример задачи подобрали. Неискушённые в Екселе пользователи не ловят кайф от виртуозного жонглирования формул. Как правило: есть конкретная проблема (в данном случае по фамилии менеджера найти все его сделки), которую нужно решить быстро и без излишних наворотов. Естественно, такие пользователи будут искать способы по-проще, сохранять такие видео в свои плейлисты и периодически к ним возвращаться. Попробуйте посмотреть на данную ситуацию их глазами. Безусловно, от вашего видео многие скажут "вау" (я тоже), но пожелание на будущее: под название ролика подбирать соответствующий пример решения задачи. Иначе решение задачи не будет выглядеть эффективним. Процветания вам, благодарю за труды.

[@andreysukhov]

Вы изначально подменяете задачу, выдавая за нее рассматриваемый в видео пример. Задача состоит в создании функции "ВПР" с поиском всех значений. Это и есть "конкретная проблема", а не поиск всех сделок по фамилии менеджера. Я всегда выношу задачу в заголовок и обратите внимание - там не "поиск всех сделок по фамилии", а "функция ВПР со всеми совпадениями"... Регулярно в комментариях вижу замечания аналогичные вашим. При этом замечания всегда пишутся "продвинутыми" пользователями, которые видят решение рассматриваемого примера в более простом варианте, не понимая, что пример тут вторичен. Если мне нужна функция ВПР со всеми совпадениями, то я, как "неискушенный пользователь" и буду искать ее реализацию. Повторюсь, сводным таблицам и работе с ними на разном уровне посвящено не одно видео на этом канале, но, еще раз - в данном случае речь не идет об эффективном решении "примера", а о создании аналога функции ВПР... Тем более, что этот же пример можно рассматривать так - есть бланк, который должен заполняться данными - выбираем менеджера и подтягиваются все заказы с их параметрами (параметры заказа по его номеру могут браться и из других источников, а не только из основной таблицы, например, информация о комплектации заказа и подобное), затем бланк выводится на печать. Реализовать это простым способом с помощью сводной не выйдет.

[@Albertus99]

К сожалению сборка конечной формулы не описана детально устно или на бумаге. Так не ка не смог ее собрать ((( там есть нюанс в ЕСЛИ...

[@zopPes]

=фильтр(A2:A18;B2:B18=L1) Но это работает только на последней версии эксель

[@andreysukhov]

Да, это функция динамических массивов. Доступна в 2019, 2021 и 365 офисах.

[@bih0use]

@@andreysukhov в 2019 её нет.

[@andreysukhov]

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

[@FarikFFF]

А какие бывают ситуации?

[@andreysukhov]

Во-первых, ситуации бывают разные и далеко не всегда возможен дополнительный столбец. Во-вторых, "левый" ВПР легко решается с помощью ИНДЕКС+ПОИСКПОЗ. В-третьих, вернуть, все совпадения из диапазона не сможет ни ВПР, ни ИНДЕКС+ПОИКПОЗ. Только своя функция на VBA или показанная альтернатива. Возможно вам известен более легкий способ решения озвученной задачи. Буду рад, если поделитесь им в комментариях.

[@andreysukhov]

Например, у вас есть готовый бланк, из которого подтягиваются данные в другой бланк. Просто так создать дополнительный столбец не получится, да это и не требуется. Повторю - "левый ВПР" ЛЕГКО реализуется связкой функций ИНДЕКС+ПОИСКПОЗ. А вот вернуть все совпадения из диапазона невозможно простой функцией, поэтому ВПР тут бессильна...

[@FarikFFF]

@@andreysukhov спасибо за разъяснения 🤝

[@bih0use]

Здорово. Но в данном случае мне проще и быстрее макрос сделать...

[@andreysukhov]

Согласен. В решении таких задач макросы сильно упрощают жизнь.

[@arustik7]

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

[@ЮлияДемченко-о2п]

так а не проще добавить фильтр? и отфильтровать по менеджеру?

[@andreysukhov]

Все зависит исключительно от задачи. Вряд ли ВПР кто-то использует для фильтрации данных. Обычно как раз функции применяются для объединения данных по определенным признакам из разных таблиц в одну. Фильтр в таком случае особо не поможет.

[@ЮлияДемченко-о2п]

@@andreysukhovааа, если из разных таблиц, то фильтр явно не поможет

[@ВладимирОгай-т9щ]

👍👍👍

[@user-qj1mx4uh7q]

ПросмотрХ Ведь им проще ?

[@vba-c2o]

Скажите, с помощью чего в эксель это можно сделать... Зоомагазин. таблица 1 в виде списка, на каждого животного 1 строка- /животное (собака, кошка ...)/ цвет/вес/ рост/возраст/длинна хвоста/цвет глаз/ болезни/ И это нужно перенести в большую таблицу № 2 отдельные на каждое животное, где в зависимости от этих характеристик животное занимает свою строку и свою колонку в виде единицы +1. если на этой ячейки будут несколько животных они складываются. животных до 1000 штук в списке. Не пойму как это можно сделать. Животные условно, на самом деле это деревья со своими характеристиками.

[@andreysukhov]

Здравствуйте. Подобные описания не информативны. Вы в эту задачу погружены и поэтому ее описание кажется вам понятным. Я же мало что из него понял. Намного более наглядным был бы документ с двумя таблицами - исходной, и той, что в итоге нужно получить... Возможно, задача решается сводной таблицей, но вполне вероятно, что поможет лишь макрос на vba.

[@vba-c2o]

@@andreysukhov могу скинуть на электронку. Правда, часов через 5, сейчас под рукой нет этих документов. Мне уже вызывались помочь на Ютубе и как-то терялись потом ))

[@andreysukhov]

@@vba-c2o не буду обещать помощь, так как если задача сложная, то она потребует более глубокого погружения. Но оценить задачу на предмет ее решения стандартными инструментами Excel могу. email - [e-mail]

[@vba-c2o]

@@andreysukhov хотя бы направление дадите, свиду просто, но начал смотреть и как то не тяну, а с vba алгоритм непонятен.. Скину по Москве часа в 3-4.

[@kostiacohn4555]

💯

[@user-qj1mx4uh7q]

В excel 2021(а так же в 365) не обязательно делать формулу формулой массива

[@Малена-з1в]

не поняла

[@alright392]

Поражает, с каким упорством люди продолжают есть "кактус" в виде MS Excel, когда подобные вещи решаются одним простым запросом в MS Access.

[@andreysukhov]

Некорректное сравнение. Эксель и Аксесс изначально предназначены для разных задач. Многие пользователи Эксель никогда в жизни не запускали Access просто потому, что он им в работе не нужен. К тому же если подобные вещи приходится решать регулярно, то пишется простая пользовательская функция, например, с именем ВПР2 и задача решается не сложнее, чем с любой другой стандартной функцией Эксель. Это видео (как и весь канал в целом) о возможностях Эксель и об использовании его стандартных функций или инструментов, вроде формул массива. Я абсолютно не настаиваю на том, что рассмотренный вариант решения оптимален. Это лишь одна из возможностей...

[@larakroft2583]

Хочется говорить с вами на одном языке

[@cannibalcorpse8462]

Для матёрых юзеров ))

[@zinovkinpro]

ехель от таких массивов дымиться невозможно работать будет

[@olzhikggg6915]

ни фига не понял

[@Abci-voll]

🙄🙄🙄🙄🙄Нуууу очень объёмная формула

[@АлексийС-ч2д]

мдэ... проще выучить VBA и свою функцию написать))

[@dalero9925]

Жесть.

[@Евгения1-е4ж]

Автофильтр. И сортируйте хоть по городам, хоть по яблокам, не только по менеджерам.

[@andreysukhov]

Задача озвучена на первых секундах видео. Фильтры тут никак не помогут

[@ТатьянаЛогинова-ш5м]

Пипец, не понятно Я начинающий. Сложно! Это для саедующих

[@andreysukhov]

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

[@westroi5298]

=Индекс( поис поз(индекс)) это гораздо проще

[@Алексей188]

Мало что понял (((