Аналог функции ВПР в Excel ➤ Поиск всех совпадений
Вставка
- Опубліковано 5 чер 2024
- 👉 Курс по Excel - andreysukhov.ru
Функция ВПР в Excel является одной из самых популярных, но у нее есть ряд ограничений. Например, она ищет только первое совпадение с заданным условием. Но с помощью формулы массива можно смоделировать ВПР, которая будет искать все совпадения.
СОДЕРЖАНИЕ
00:00 | Функция ВПР и ее недостатки
01:20 | Функция ИНДЕКС
02:04 | Моделируем функцию ВПР
Видео по теме:
► Функция ВПР в Excel: • Функция ВПР в Excel ➤ ...
► Функции ИНДЕКС и ПОИСКПОЗ: • Функции ИНДЕКС и ПОИСК...
► Формулы массивов: • Формулы массивов ➤ Exc...
Дополнительные материалы:
➤ Скачать файл с примером: disk.yandex.ru/i/0PcxCqe1qylpEQ
➤ Текстовая версия: dzen.ru/media/id/5c4d8a530aae...
★ Авторские книги и курсы: andreysukhov.ru
★ Скачать курс по Excel: andreysukhov.ru/education/exc...
★ Телеграм: t.me/excelmate
★ Дзен: zen.yandex.ru/id/5c4d8a530aae...
★ Группа ВК: public212583018
#Excel
Лично мой опыт подсказывает - раскладывать сложные формулы на более простые, разместив части формул в скрытых столбцах, строках или листах. Проходит время, и потом тяжело разбираться в этих крокодилах, а по этапно - гораздо проще.
Или на отдельном листе оставлять описания к формулам, макросам и пр.
@@user-jd7im8xl5d
Все равно сложно.
Проще дробить на блоки поменьше.
@@arustik7 Ну да, вы правы. Столбцы потом или скрыть или сгруппировать, чтобы глаз не мозолили.
Спасибо за полезность! Громоздкая формула, такую сложно запомнить) А если по-быстрому, то в работе использую всегда сводную)
Спасибо огромное за Ваш труд! Успехов во всём!
Вы офигенно растолковали, Спасибо !!! )))
Очень полезный урок. Спасибо!!!
Большое спасибо за хороший пример!
Отдельное большое спасибо за то что раскладываете формулы по отдельности! Это очень умно!
Есть пожелание чтобы в текстовой версии четко была прописана каждая формула по отдельности.
Это можно заменить наличием всех формул в примере как например у Николая Павлова что будет еще лучше.
И тогда будет полный шикардос.
Спасибо за потраченое время)! Хорошее видео!)👏👏👏👍
Дружище, Ты гений!!!
Очень полезно!!! Спасибо!!!
Да, интересно. Однако своднаяв данном случае намного проще. Это мое мнение, а автору огромный респект за информацию ло использовании функции ИНДЕКС в формуле массива. Спасибо!
Великолепное видео :)
Спасибо молодец всё понятное
отлично!!!
Не ищем легких путей) Спасибо
Спасибо
Приходилось решать подобную задачу, использовал индекс/поискпоз/смещ +допстолбец для номеров строк
Рассказано хорошо, но с первого раза не понял, может конечно проблема что смотрю с телефона и сразу не повторяю для наглядности. Спасибо 🙂
Очень полезное видео, у меня получилось сделать, но есть вопрос. Как можно сделать, что бы то же самое работало на 2 листах, а выводилось в один. И в нем можно было все сортировать по статусам?
Здравствуйте, спасибо за видео, Андрей, скажите пожалуйста, а как можно запихнуть этот список заказов по выбранному менеджеру в выпадающий список.
Спасибо большое за ролик. Но вот повторить данную формулу с учетом некоторых изменений я не смог. А есть хоть какая-то возможность в том числе за деньги получить у Вас консультацию в части решения задача автоматизации в Excel?
👍👍👍
Использовал подобную формулу лет десять назад, ещё excel 2003 был, получилась ещё длиннее, так как не было функций типа ЕСЛИОШИБКА итд. Кстати поднял неплохие по тем временам деньги. Сейчас я бы написал макрос какой нибудь, вышло бы проще, и эффективнее!
Я вообще не могу поднять деньги никакие. как вам удается?
💯
Пожалуйста скажите есть ли аналог этой формулы для поиска по нескольким условиям?
Здравствуйте! С вами можно как-то связаться? Есть вопрос по Excel.
Привет подскажи пожалуйста как сделать в всплывающем списке не только чтобы он выдавал данные уже с готовых таблиц а была возможность менять значения а формулы оставались такие же как и были в таблице . Например из всплывающего списка выбераю молоко, меняю значения с право в ячейках он считает только для молока. Если сок то для сока?
ВПР использовать может быть
А как ви сделали с правой сторони ячейка "сергеева" что там можна вибрать другие имена?
Добрый день Андрей. мне надо функцию "поиск решений" у меня её нет (( я захожу в параметры иксель в параметрах стоит только функция язык. Подскажите как быть?
Андрей как с вами можно связаться?,
можно написать на почту admin@pcsecrets.ru
Урок полезен тем, что показывает способы сборки длинных формул и подробно рассказано про их функционал. Но именно для данной задачи такой подход - это ОГРОМНАЯ трата времени! Задача отображения всех заказов по конкретному менеджеру решается с помощью Сводной Таблицы за 1 минуту! Представьте, что у вас в подчинении несколько отделов, в которых по несколько менеджеров и вам вдруг захотелось узнать заказы всего отдела или нескольких менеджеров сразу? По методике этого урока надо неизбежно переделывать и без того сложную формулу. Это очень не практично. А вот Сводная Таблица с помощью фильтра решает и эту задачу очень легко и быстро. Вывод: этот урок о том как сделать простое сложным.
Это видео о том, как создать АНАЛОГ функции ВПР. Функцию ВПР используют для подтягивания данных из одной таблицы в другую, возможно оформленную по каким-то внутренним правилам организации (готовый бланк). Сводные для этого обычно не годятся. Да, сводные таблицы - отличный инструмент для анализа данных, но далеко не во всех ситуациях они применимы. По сводным на канале есть отдельный плейлист.
@@andreysukhov Да, Индекс и ПоискПоз - это замечательная связка. И в этом видео о них вы хорошо расказали. Но на мой взгляд неудачно пример задачи подобрали. Неискушённые в Екселе пользователи не ловят кайф от виртуозного жонглирования формул. Как правило: есть конкретная проблема (в данном случае по фамилии менеджера найти все его сделки), которую нужно решить быстро и без излишних наворотов. Естественно, такие пользователи будут искать способы по-проще, сохранять такие видео в свои плейлисты и периодически к ним возвращаться. Попробуйте посмотреть на данную ситуацию их глазами. Безусловно, от вашего видео многие скажут "вау" (я тоже), но пожелание на будущее: под название ролика подбирать соответствующий пример решения задачи. Иначе решение задачи не будет выглядеть эффективним. Процветания вам, благодарю за труды.
Вы изначально подменяете задачу, выдавая за нее рассматриваемый в видео пример. Задача состоит в создании функции "ВПР" с поиском всех значений. Это и есть "конкретная проблема", а не поиск всех сделок по фамилии менеджера. Я всегда выношу задачу в заголовок и обратите внимание - там не "поиск всех сделок по фамилии", а "функция ВПР со всеми совпадениями"... Регулярно в комментариях вижу замечания аналогичные вашим. При этом замечания всегда пишутся "продвинутыми" пользователями, которые видят решение рассматриваемого примера в более простом варианте, не понимая, что пример тут вторичен. Если мне нужна функция ВПР со всеми совпадениями, то я, как "неискушенный пользователь" и буду искать ее реализацию. Повторюсь, сводным таблицам и работе с ними на разном уровне посвящено не одно видео на этом канале, но, еще раз - в данном случае речь не идет об эффективном решении "примера", а о создании аналога функции ВПР... Тем более, что этот же пример можно рассматривать так - есть бланк, который должен заполняться данными - выбираем менеджера и подтягиваются все заказы с их параметрами (параметры заказа по его номеру могут браться и из других источников, а не только из основной таблицы, например, информация о комплектации заказа и подобное), затем бланк выводится на печать. Реализовать это простым способом с помощью сводной не выйдет.
Здорово. Но в данном случае мне проще и быстрее макрос сделать...
Согласен. В решении таких задач макросы сильно упрощают жизнь.
Макросы решают много задач, но лично я их рассматриваю, как костыль. Если можно решить проблему встроенными функциями и возможностями, я не прибегаю к макросам, хотя это иногд проще и быстрее.
=фильтр(A2:A18;B2:B18=L1)
Но это работает только на последней версии эксель
Да, это функция динамических массивов. Доступна в 2019, 2021 и 365 офисах.
@@andreysukhov в 2019 её нет.
2019-ым как-то не приходилось пользоваться, так как с 2016-ого сразу перешел на 365, но не так давно делал видео по функциям динамических массивов и уточнял на оф.сайте наличие функций в разных версиях. Там указан в том числе и 2019-ый Эксель. Допускаю, что у них там ошибка.
ПросмотрХ
Ведь им проще
?
В excel 2021(а так же в 365) не обязательно делать формулу формулой массива
Скажите, с помощью чего в эксель это можно сделать... Зоомагазин. таблица 1 в виде списка, на каждого животного 1 строка- /животное (собака, кошка ...)/ цвет/вес/ рост/возраст/длинна хвоста/цвет глаз/ болезни/ И это нужно перенести в большую таблицу № 2 отдельные на каждое животное, где в зависимости от этих характеристик животное занимает свою строку и свою колонку в виде единицы +1. если на этой ячейки будут несколько животных они складываются. животных до 1000 штук в списке. Не пойму как это можно сделать. Животные условно, на самом деле это деревья со своими характеристиками.
Здравствуйте. Подобные описания не информативны. Вы в эту задачу погружены и поэтому ее описание кажется вам понятным. Я же мало что из него понял. Намного более наглядным был бы документ с двумя таблицами - исходной, и той, что в итоге нужно получить... Возможно, задача решается сводной таблицей, но вполне вероятно, что поможет лишь макрос на vba.
@@andreysukhov могу скинуть на электронку. Правда, часов через 5, сейчас под рукой нет этих документов. Мне уже вызывались помочь на Ютубе и как-то терялись потом ))
@@user-ed8zt7pk7x не буду обещать помощь, так как если задача сложная, то она потребует более глубокого погружения. Но оценить задачу на предмет ее решения стандартными инструментами Excel могу. email - admin@pcsecrets.ru
@@andreysukhov хотя бы направление дадите, свиду просто, но начал смотреть и как то не тяну, а с vba алгоритм непонятен.. Скину по Москве часа в 3-4.
так а не проще добавить фильтр? и отфильтровать по менеджеру?
Все зависит исключительно от задачи. Вряд ли ВПР кто-то использует для фильтрации данных. Обычно как раз функции применяются для объединения данных по определенным признакам из разных таблиц в одну. Фильтр в таком случае особо не поможет.
@@andreysukhovааа, если из разных таблиц, то фильтр явно не поможет
А какие бывают ситуации?
Во-первых, ситуации бывают разные и далеко не всегда возможен дополнительный столбец. Во-вторых, "левый" ВПР легко решается с помощью ИНДЕКС+ПОИСКПОЗ. В-третьих, вернуть, все совпадения из диапазона не сможет ни ВПР, ни ИНДЕКС+ПОИКПОЗ. Только своя функция на VBA или показанная альтернатива. Возможно вам известен более легкий способ решения озвученной задачи. Буду рад, если поделитесь им в комментариях.
Например, у вас есть готовый бланк, из которого подтягиваются данные в другой бланк. Просто так создать дополнительный столбец не получится, да это и не требуется. Повторю - "левый ВПР" ЛЕГКО реализуется связкой функций ИНДЕКС+ПОИСКПОЗ. А вот вернуть все совпадения из диапазона невозможно простой функцией, поэтому ВПР тут бессильна...
@@andreysukhov спасибо за разъяснения 🤝
Поражает, с каким упорством люди продолжают есть "кактус" в виде MS Excel, когда подобные вещи решаются одним простым запросом в MS Access.
Некорректное сравнение. Эксель и Аксесс изначально предназначены для разных задач. Многие пользователи Эксель никогда в жизни не запускали Access просто потому, что он им в работе не нужен. К тому же если подобные вещи приходится решать регулярно, то пишется простая пользовательская функция, например, с именем ВПР2 и задача решается не сложнее, чем с любой другой стандартной функцией Эксель. Это видео (как и весь канал в целом) о возможностях Эксель и об использовании его стандартных функций или инструментов, вроде формул массива. Я абсолютно не настаиваю на том, что рассмотренный вариант решения оптимален. Это лишь одна из возможностей...
Хочется говорить с вами на одном языке
Для матёрых юзеров ))
ехель от таких массивов дымиться
невозможно работать будет
мдэ... проще выучить VBA и свою функцию написать))
Жесть.
ни фига не понял
🙄🙄🙄🙄🙄Нуууу очень объёмная формула
Автофильтр. И сортируйте хоть по городам, хоть по яблокам, не только по менеджерам.
Задача озвучена на первых секундах видео. Фильтры тут никак не помогут
=Индекс( поис поз(индекс)) это гораздо проще
Мало что понял (((
К сожалению сборка конечной формулы не описана детально устно или на бумаге. Так не ка не смог ее собрать ((( там есть нюанс в ЕСЛИ...