Чем заменить функцию впр в excel
Альтернатива ВПР: функции ИНДЕКС и ПОИСКПОЗ
Здравствуйте, друзья. Когда мы собираемся что-то искать в таблице, первая функция, которая приходит в голову – это ВПР. Все её знают, любят и активно пользуются. Но что, если есть другой, не менее удачный способ найти значение в таблице и вернуть что-то ему соответствующее?
Пусть у нас есть таблица ежемесячных продаж. Поиск продаж какого-то менеджера, например, в марте, решается с помощью ВПР:
Как еще можно это сделать? Давайте размышлять.
Предположим, нам понадобились продажи Соколова в марте. Если не использовать ВПР, можем получить эти данные с помощью функции ИНДЕКС:
В функции мы вручную указали, что нужно вернуть значение на пересечении 8-й строки и 4 столбца, что соответствует искомым данным. Но такой «ручной» подход не имеет смысла. Лучше поручить определение номера строки и столбца Экселю. Сделаем это с помощью функции ПОИСКПОЗ.
Найдем порядковый номер строки, в которой расположена фамилия Соколов:
Аналогично, получим номер столбца с нужным месяцем:
Теперь подставим 2 формулы поиска в функцию ИНДЕКС. Получим такую конструкцию:
=ИНДЕКС( B2:G27 ; ПОИСКПОЗ(K2;B2:B27;0) ; ПОИСКПОЗ(K3;B2:G2;0) )
Здесь первый аргумент – ссылка на массив продаж, второй – расчет номера строки в зависимости от выбранной фамилии, третий – получение столбца по заданному месяцу.
Для более удобного выбора фамилии и месяца, я сделал в таблице итогов выбор в выпадающих списках, и теперь мне нужно писать эти данные вручную. Хотите поступить так же? Читайте эту статью.
Обратите внимание, мы получили тот же результат, что и с ВПР. Многие ученики спрашивают меня: «Зачем пользоваться такой длинной и громоздкой комбинацией функций, когда один только ВПР делает то же самое проще, лаконичнее и привычнее?». Вот вам список очевидных преимуществ использования ИНДЕКС+ПОИСКПОЗ:
Однако, справедливо утверждение, что ВПР проще, понятнее, короче. Ему легче научиться. Что использовать – выбирайте сами. Я по умолчанию использую ВПР, но легко переключаюсь на ИНДЕКС+ПОИСКПОЗ, когда это необходимо.
data_client
Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ
При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.
Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:
Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: «Функция ВПР в Excel»). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:
СМЕЩ(ссылка;смещение по строкам;смещение по столбцам)
ПОИСКПОЗ(искомое значение; просматриваемый массив; тип сопоставления)
К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ(«Петров»;B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.
Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.
Итак, для нашего примера формула будет следующая:
Таким образом, используя нехитрую комбинацию из двух функций мы получили универсальный аналог ВПР, который может возвращать значения, как со столбцов справа, так и слева.
Надеюсь, статья была для вас полезной. Спасибо за внимание.
Microsoft представила замену ВПР, ГПР а также ПОИСКПОЗ
Введение динамических массивов, по сути историческое обновление, которые в корне изменяет подход к построению формул, в чем то изменяет мышление работы в Excel.
С введением динамических массивов было вполне очевидно, что анонсом семи новых функций Microsoft не ограничится. В августе 2019, почти год после анонса масштабного обновления (сентябрь 2018), Microsoft представила замену одной из самых популярных функций в Excel.
Да, речь идет об ВПР или VLOOKUP в английской версии, попутно обновилась и ГПР (HLOOKUP, соответственно). На самом деле новая функция ПРОСМОТРХ (XLOOKUP) просто заменяет их обе. Кстати, в конце стоит именно латинский символ «X» (экс), а не русская «Х» (ха), что доставляет неудобств, если вы пользуетесь русской версией электронного процессора.
А вот для функции ПОИСКПОЗ (MATCH) была добавлена своя функция ПОИСКПОЗX XMATCH, которая, использует возможности динамических массивов, как и XLOOKUP.
Представление ПРОСМОТРХ (XLOOKUP)
Наименование функции намекает, на то, что она будет являться заменой, как для вертикального поиска, так и для горизонтального. Соответственно, данная функция будет заменять обе существующие и ВПР, и ГПР.
При всем при этом, синтаксис данной функции чрезвычайно прост:
Для тех, кто освоил использование ВПР, освоение данной функции не составит никакого труда. Простейший пример представлен на анимированном рисунке.
Почему Microsft представила новую функцию?
Все дело в тех недостатках, которые существуют при использовании ВПР (как и ГПР):
Последний пункт означает, что массивы в котором осуществляет поиск искомого значения и возвращаемого должны быть связаны, что самым неблагоприятным образом сказывается на производительности данной функции, при работе с большими таблицами. Также новая функция не требует, чтобы искомый и возвращаемый диапазоны были связаны, так и может работать как в горизонтальной плоскости, так и вертикальной.
Остальные пункты в той или иной мере могут быть решены умелым использованием самой ВПР или связкой функций ИНДЕКС и ПОИСКПОЗ, например, возвращение значения слева от искомого столбца.
О полных возможностях функции ПРОСМОТРХ в нашем справочнике.
Немного об ПОИСКПОЗX (XMATCH)
В дополнении к XLOOKUP была анонсирована и новая функция ПОИСКПОЗX (XMATCH), с похожим на XLOOKUP синтаксисом, однако возвращает индекс искомого значения.
Подробно об использовании данной функции, также можно узнать в справочнике.
Функция ВПР в Excel для чайников и не только
Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.
Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.
Стоимость материалов – в прайс-листе. Это отдельная таблица.
Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.
Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.
Теперь найти стоимость материалов не составит труда: количество * цену.
Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».
Формула в ячейках исчезнет. Останутся только значения.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.
Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.
Функция ВПР в Excel с несколькими условиями
До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.
Таблица для примера:
Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.
Дело осложняется тем, что от одного поставщика поступает несколько наименований.
Рассмотрим формулу детально:
Функция ВПР и выпадающий список
Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.
Сначала сделаем раскрывающийся список:
Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).
Изменяем материал – меняется цена:
Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.
Функция ВПР в Excel
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
Выделить ячейку и вписать функцию.
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
В нашем случае получится такая формула:
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
Формула сканирует таблицу по вертикали.
Находит в самом левом столбце совпадение с искомым значением.
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Итоги
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
Функцию можно вписать вручную или в специальном окне (Shift + F3).
Искомое значение – относительная ссылка, а таблица – абсолютная.
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
Порядок работы с функцией подходит для Гугл-таблиц.