Ребята, всем привет! 👋
Продолжаем изучать Excel. В этом уроке рассмотрим лучшие альтернативы ВПР в Excel.
✍️ Если вы только начинаете осваивать Excel мы уверены, каждый может найти для себя что-то новое!
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
К сожалению, самым большим ограничением ВПР является то, что она может искать только вправо от ключевого столбца. Это означает, что вы сможете получить данные только из колонок, находящихся справа от первого столбца.
Когда значения поиска находятся в первом (крайнем левом) столбце, это ограничение не имеет большого значения, поскольку все остальные уже находятся справа.
Однако, если область поиска находится внутри таблицы, вы сможете искать значения только справа от неё. Вам также нужно будет в качестве источника данных брать не всю таблицу, а только ее часть, которая начинается с области поиска.
Впрочем, это ограничение можно преодолеть, о чем, я сегодня и расскажу.
Рассмотрим пример: Пусть мы имеем некоторый перечень товаров, их цену и артикул. Требуется, зная артикул определить цену товара
⏩ Альтернатива №1. ИНДЕКС-ПОИСКПОЗ
ИНДЕКС -ПОИСКПОЗ - пожалуй самая известная альтернатива функции ВПР.
Столбец поиска вводится в функцию ПОИСКПОЗ , которая также выполняет процесс поиска, а столбец возврата вводится в функцию ИНДЕКС , которая возвращает значение, соответствующее результату функции ПОИСКПОЗ.
Решение:
=ИНДЕКС(Таблица1;ПОИСКПОЗ(E2;Таблица1[Артикул];0);2)
⏩ Альтернатива №2. Функция СУММПРОИЗВ()
Если мы ищем только числа, мы можем использовать функцию СУММПРОИЗВ в качестве альтернативы функции ВПР. Одним из преимуществ функции СУММПРОИЗВ является удобство применения нескольких критериев.
Решение:
=СУММПРОИЗВ((Таблица1[Артикул]=E2)*Таблица1[ЦЕНА])
⏩ Альтернатива №3. Функция СУММЕСЛИ()
Вместо функции СУММПРОИЗВ мы также можем использовать функцию СУММЕСЛИ или СУММЕСЛИМН для выполнения поиска по нескольким критериям для чисел. Это проще и удобнее в использовании по сравнению с функцией СУММПРОИЗВ, но, в отличие от функции СУММПРОИЗВ, она не может принимать входные данные массива, такие как результаты массива из других функций.
Решение:
=СУММЕСЛИ(Таблица1[Артикул];E2;Таблица1[ЦЕНА])
Суммарный_диапазон и критерии_диапазонов являются строго диапазонами:
⏩ Альтернатива №4. СМЕЩ-ПОИСКПОЗ
Еще одной альтернативой ВПР является формула СМЕЩ-ПОИСКПОЗ.
Решение:
=СМЕЩ(Таблица1[[#Заголовки];[Артикул]];ПОИСКПОЗ(E2;Таблица1[Артикул];0);-1)
Что важно: Функция СМЕЩ является энергозависимой функцией, что означает, что она всегда будет пересчитываться всякий раз, когда пересчитывается лист. В зависимости от сценария это может повлиять на скорость вашего листа.
⏩ Альтернатива №5. ДВССЫЛ-АДРЕС-ПОИСКПОЗ: динамическая ссылка на столбец
Другой гибкой альтернативой ВПР является формула НЕПРЯМОГО АДРЕСНОГО СОВПАДЕНИЯ.
Функция АДРЕС() возвращает текстовое значение в виде адреса ячейки.
Синтаксис функции:
АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1], [имя_листа])
Номер_строки Обязательный аргумент. Номер строки, используемый в ссылке на ячейку.
Номер_столбца Обязательный аргумент. Номер столбца, используемый в ссылке на ячейку.
Последние 3 аргумента являются необязательными.
[Тип_ссылки] Задает тип возвращаемой ссылки:
- 1 или опущен: абсолютная ссылка , например $D$7
- 2 : абсолютная ссылка на строку; относительная ссылка на столбец, например D$7
- 3 : относительная ссылка на строку; абсолютная ссылка на столбец, например $D7
- 4 : относительная ссылка, например D7
Решение:
=ДВССЫЛ(АДРЕС(ПОИСКПОЗ(E2;Таблица1[[#Все];[Артикул]];0);2))
Что важно: Функция ДВССЫЛ является энергозависимой функцией , что означает, что она всегда будет пересчитываться всякий раз, когда пересчитывается лист. В зависимости от сценария это может повлиять на скорость вашего листа.
А на этом сегодня все. 👏 Теперь вы знаете лучшие альтернативы ВПР в Excel. Я надеюсь, что вы нашли этот урок полезным.
Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
Понравился урок!? не забываем оставлять комментарий 📝 Нам очень важна Ваша обратная связь.
💝 А если у Вас пока нет вопросов, но вы дочитали данную статью до конца оставьте в комментариях просто смайлик 😉 (автору будет приятно)
И конечно же, за лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!