Аналог функции ВПР в Excel ➤ Поиск всех совпадений
👉 Курс по 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
Пікірлер: 75
Вы офигенно растолковали, Спасибо !!! )))
Большое спасибо за хороший пример! Отдельное большое спасибо за то что раскладываете формулы по отдельности! Это очень умно! Есть пожелание чтобы в текстовой версии четко была прописана каждая формула по отдельности. Это можно заменить наличием всех формул в примере как например у Николая Павлова что будет еще лучше. И тогда будет полный шикардос.
Спасибо огромное за Ваш труд! Успехов во всём!
Спасибо за полезность! Громоздкая формула, такую сложно запомнить) А если по-быстрому, то в работе использую всегда сводную)
Спасибо за потраченое время)! Хорошее видео!)👏👏👏👍
Очень полезный урок. Спасибо!!!
Дружище, Ты гений!!!
Очень полезно!!! Спасибо!!!
Лично мой опыт подсказывает - раскладывать сложные формулы на более простые, разместив части формул в скрытых столбцах, строках или листах. Проходит время, и потом тяжело разбираться в этих крокодилах, а по этапно - гораздо проще.
@user-jd7im8xl5d
9 ай бұрын
Или на отдельном листе оставлять описания к формулам, макросам и пр.
@arustik7
9 ай бұрын
@@user-jd7im8xl5d Все равно сложно. Проще дробить на блоки поменьше.
@user-jd7im8xl5d
9 ай бұрын
@@arustik7 Ну да, вы правы. Столбцы потом или скрыть или сгруппировать, чтобы глаз не мозолили.
Великолепное видео :)
Спасибо молодец всё понятное
Да, интересно. Однако своднаяв данном случае намного проще. Это мое мнение, а автору огромный респект за информацию ло использовании функции ИНДЕКС в формуле массива. Спасибо!
отлично!!!
Приходилось решать подобную задачу, использовал индекс/поискпоз/смещ +допстолбец для номеров строк
Спасибо
Не ищем легких путей) Спасибо
Очень полезное видео, у меня получилось сделать, но есть вопрос. Как можно сделать, что бы то же самое работало на 2 листах, а выводилось в один. И в нем можно было все сортировать по статусам?
Очень полезно даже для развития логического мышления
Спасибо большое за ролик. Но вот повторить данную формулу с учетом некоторых изменений я не смог. А есть хоть какая-то возможность в том числе за деньги получить у Вас консультацию в части решения задача автоматизации в Excel?
Использовал подобную формулу лет десять назад, ещё excel 2003 был, получилась ещё длиннее, так как не было функций типа ЕСЛИОШИБКА итд. Кстати поднял неплохие по тем временам деньги. Сейчас я бы написал макрос какой нибудь, вышло бы проще, и эффективнее!
@IsmanOsmon
Жыл бұрын
Я вообще не могу поднять деньги никакие. как вам удается?
Пожалуйста скажите есть ли аналог этой формулы для поиска по нескольким условиям?
Здравствуйте, спасибо за видео, Андрей, скажите пожалуйста, а как можно запихнуть этот список заказов по выбранному менеджеру в выпадающий список.
Привет подскажи пожалуйста как сделать в всплывающем списке не только чтобы он выдавал данные уже с готовых таблиц а была возможность менять значения а формулы оставались такие же как и были в таблице . Например из всплывающего списка выбераю молоко, меняю значения с право в ячейках он считает только для молока. Если сок то для сока?
@IsmanOsmon
Жыл бұрын
ВПР использовать может быть
Здравствуйте! С вами можно как-то связаться? Есть вопрос по Excel.
💯
👍👍👍
А как ви сделали с правой сторони ячейка "сергеева" что там можна вибрать другие имена?
Добрый день Андрей. мне надо функцию "поиск решений" у меня её нет (( я захожу в параметры иксель в параметрах стоит только функция язык. Подскажите как быть?
Андрей как с вами можно связаться?,
@andreysukhov
Жыл бұрын
можно написать на почту admin@pcsecrets.ru
ПросмотрХ Ведь им проще ?
В excel 2021(а так же в 365) не обязательно делать формулу формулой массива
Здорово. Но в данном случае мне проще и быстрее макрос сделать...
@andreysukhov
Жыл бұрын
Согласен. В решении таких задач макросы сильно упрощают жизнь.
@arustik7
Жыл бұрын
Макросы решают много задач, но лично я их рассматриваю, как костыль. Если можно решить проблему встроенными функциями и возможностями, я не прибегаю к макросам, хотя это иногд проще и быстрее.
=фильтр(A2:A18;B2:B18=L1) Но это работает только на последней версии эксель
@andreysukhov
Жыл бұрын
Да, это функция динамических массивов. Доступна в 2019, 2021 и 365 офисах.
@bih0use
Жыл бұрын
@@andreysukhov в 2019 её нет.
@andreysukhov
Жыл бұрын
2019-ым как-то не приходилось пользоваться, так как с 2016-ого сразу перешел на 365, но не так давно делал видео по функциям динамических массивов и уточнял на оф.сайте наличие функций в разных версиях. Там указан в том числе и 2019-ый Эксель. Допускаю, что у них там ошибка.
А какие бывают ситуации?
@andreysukhov
8 ай бұрын
Во-первых, ситуации бывают разные и далеко не всегда возможен дополнительный столбец. Во-вторых, "левый" ВПР легко решается с помощью ИНДЕКС+ПОИСКПОЗ. В-третьих, вернуть, все совпадения из диапазона не сможет ни ВПР, ни ИНДЕКС+ПОИКПОЗ. Только своя функция на VBA или показанная альтернатива. Возможно вам известен более легкий способ решения озвученной задачи. Буду рад, если поделитесь им в комментариях.
@andreysukhov
8 ай бұрын
Например, у вас есть готовый бланк, из которого подтягиваются данные в другой бланк. Просто так создать дополнительный столбец не получится, да это и не требуется. Повторю - "левый ВПР" ЛЕГКО реализуется связкой функций ИНДЕКС+ПОИСКПОЗ. А вот вернуть все совпадения из диапазона невозможно простой функцией, поэтому ВПР тут бессильна...
@FarikFFF
8 ай бұрын
@@andreysukhov спасибо за разъяснения 🤝
так а не проще добавить фильтр? и отфильтровать по менеджеру?
@andreysukhov
Жыл бұрын
Все зависит исключительно от задачи. Вряд ли ВПР кто-то использует для фильтрации данных. Обычно как раз функции применяются для объединения данных по определенным признакам из разных таблиц в одну. Фильтр в таком случае особо не поможет.
@user-uz6ed3zl2j
Жыл бұрын
@@andreysukhovааа, если из разных таблиц, то фильтр явно не поможет
Скажите, с помощью чего в эксель это можно сделать... Зоомагазин. таблица 1 в виде списка, на каждого животного 1 строка- /животное (собака, кошка ...)/ цвет/вес/ рост/возраст/длинна хвоста/цвет глаз/ болезни/ И это нужно перенести в большую таблицу № 2 отдельные на каждое животное, где в зависимости от этих характеристик животное занимает свою строку и свою колонку в виде единицы +1. если на этой ячейки будут несколько животных они складываются. животных до 1000 штук в списке. Не пойму как это можно сделать. Животные условно, на самом деле это деревья со своими характеристиками.
@andreysukhov
Жыл бұрын
Здравствуйте. Подобные описания не информативны. Вы в эту задачу погружены и поэтому ее описание кажется вам понятным. Я же мало что из него понял. Намного более наглядным был бы документ с двумя таблицами - исходной, и той, что в итоге нужно получить... Возможно, задача решается сводной таблицей, но вполне вероятно, что поможет лишь макрос на vba.
@user-ed8zt7pk7x
Жыл бұрын
@@andreysukhov могу скинуть на электронку. Правда, часов через 5, сейчас под рукой нет этих документов. Мне уже вызывались помочь на Ютубе и как-то терялись потом ))
@andreysukhov
Жыл бұрын
@@user-ed8zt7pk7x не буду обещать помощь, так как если задача сложная, то она потребует более глубокого погружения. Но оценить задачу на предмет ее решения стандартными инструментами Excel могу. email - admin@pcsecrets.ru
@user-ed8zt7pk7x
Жыл бұрын
@@andreysukhov хотя бы направление дадите, свиду просто, но начал смотреть и как то не тяну, а с vba алгоритм непонятен.. Скину по Москве часа в 3-4.
Урок полезен тем, что показывает способы сборки длинных формул и подробно рассказано про их функционал. Но именно для данной задачи такой подход - это ОГРОМНАЯ трата времени! Задача отображения всех заказов по конкретному менеджеру решается с помощью Сводной Таблицы за 1 минуту! Представьте, что у вас в подчинении несколько отделов, в которых по несколько менеджеров и вам вдруг захотелось узнать заказы всего отдела или нескольких менеджеров сразу? По методике этого урока надо неизбежно переделывать и без того сложную формулу. Это очень не практично. А вот Сводная Таблица с помощью фильтра решает и эту задачу очень легко и быстро. Вывод: этот урок о том как сделать простое сложным.
@andreysukhov
9 ай бұрын
Это видео о том, как создать АНАЛОГ функции ВПР. Функцию ВПР используют для подтягивания данных из одной таблицы в другую, возможно оформленную по каким-то внутренним правилам организации (готовый бланк). Сводные для этого обычно не годятся. Да, сводные таблицы - отличный инструмент для анализа данных, но далеко не во всех ситуациях они применимы. По сводным на канале есть отдельный плейлист.
@user-jd7im8xl5d
9 ай бұрын
@@andreysukhov Да, Индекс и ПоискПоз - это замечательная связка. И в этом видео о них вы хорошо расказали. Но на мой взгляд неудачно пример задачи подобрали. Неискушённые в Екселе пользователи не ловят кайф от виртуозного жонглирования формул. Как правило: есть конкретная проблема (в данном случае по фамилии менеджера найти все его сделки), которую нужно решить быстро и без излишних наворотов. Естественно, такие пользователи будут искать способы по-проще, сохранять такие видео в свои плейлисты и периодически к ним возвращаться. Попробуйте посмотреть на данную ситуацию их глазами. Безусловно, от вашего видео многие скажут "вау" (я тоже), но пожелание на будущее: под название ролика подбирать соответствующий пример решения задачи. Иначе решение задачи не будет выглядеть эффективним. Процветания вам, благодарю за труды.
@andreysukhov
9 ай бұрын
Вы изначально подменяете задачу, выдавая за нее рассматриваемый в видео пример. Задача состоит в создании функции "ВПР" с поиском всех значений. Это и есть "конкретная проблема", а не поиск всех сделок по фамилии менеджера. Я всегда выношу задачу в заголовок и обратите внимание - там не "поиск всех сделок по фамилии", а "функция ВПР со всеми совпадениями"... Регулярно в комментариях вижу замечания аналогичные вашим. При этом замечания всегда пишутся "продвинутыми" пользователями, которые видят решение рассматриваемого примера в более простом варианте, не понимая, что пример тут вторичен. Если мне нужна функция ВПР со всеми совпадениями, то я, как "неискушенный пользователь" и буду искать ее реализацию. Повторюсь, сводным таблицам и работе с ними на разном уровне посвящено не одно видео на этом канале, но, еще раз - в данном случае речь не идет об эффективном решении "примера", а о создании аналога функции ВПР... Тем более, что этот же пример можно рассматривать так - есть бланк, который должен заполняться данными - выбираем менеджера и подтягиваются все заказы с их параметрами (параметры заказа по его номеру могут браться и из других источников, а не только из основной таблицы, например, информация о комплектации заказа и подобное), затем бланк выводится на печать. Реализовать это простым способом с помощью сводной не выйдет.
Для матёрых юзеров ))
ехель от таких массивов дымиться невозможно работать будет
Хочется говорить с вами на одном языке
Поражает, с каким упорством люди продолжают есть "кактус" в виде MS Excel, когда подобные вещи решаются одним простым запросом в MS Access.
@andreysukhov
6 ай бұрын
Некорректное сравнение. Эксель и Аксесс изначально предназначены для разных задач. Многие пользователи Эксель никогда в жизни не запускали Access просто потому, что он им в работе не нужен. К тому же если подобные вещи приходится решать регулярно, то пишется простая пользовательская функция, например, с именем ВПР2 и задача решается не сложнее, чем с любой другой стандартной функцией Эксель. Это видео (как и весь канал в целом) о возможностях Эксель и об использовании его стандартных функций или инструментов, вроде формул массива. Я абсолютно не настаиваю на том, что рассмотренный вариант решения оптимален. Это лишь одна из возможностей...
ни фига не понял
Жесть.
мдэ... проще выучить VBA и свою функцию написать))
🙄🙄🙄🙄🙄Нуууу очень объёмная формула
Автофильтр. И сортируйте хоть по городам, хоть по яблокам, не только по менеджерам.
@andreysukhov
4 ай бұрын
Задача озвучена на первых секундах видео. Фильтры тут никак не помогут
=Индекс( поис поз(индекс)) это гораздо проще
Мало что понял (((
К сожалению сборка конечной формулы не описана детально устно или на бумаге. Так не ка не смог ее собрать ((( там есть нюанс в ЕСЛИ...