Функция ВПР в Excel
ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований.
Синтаксис
Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).
Адрес таблицы – диапазон ячеек, где примерно находится значение.
Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.
Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.
В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.
Как работает функция?
Программа после поиска искомого значения переходит во второй столбец, чтобы извлечь возвращаемый элемент.
С одним условием
Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.
- Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
- В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.
С несколькими условиями
Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.
Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:
- Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «», в которые взята вся формула).
Поиск по нескольким столбцам
Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.
Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.
Важно! Самостоятельно писать фигурные скобки «» при вводе формулы нельзя, поскольку это не приведет к какому-либо результату.
Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;;ЛОЖЬ)).
Сравнение двух таблиц
Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:
- Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
По желанию теперь можно найти численную и процентную разницу.
Поиск в выпадающем списке
Необходимо настроить функцию так, чтобы при выборе элемента из выпадающего списка отображалось его числовое значение.
- В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».
Перенос данных
Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).
- Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
Ошибки
Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.
- Указанный диапазон не содержит искомый элемент.
- Искомый элемент меньше, чем минимальный в массиве.
- Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
- Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
- Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
- В коде есть пробелы или невидимые непечатаемые знаки.
- Используются значения времени или большие десятичные числа.
Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».
Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.
Вместо заключения
В Excel есть улучшенная версия ВПР – функция ПРОСМОТРХ. Считается более простой и удобной в использовании, работает в любом направлении и возвращает точные совпадения по умолчанию.
При необходимости задействовать горизонтальный поиск элемента стоит воспользоваться функцией ГПР.
[expert_bq id=»1570″]Вы можете найти общую сумму затрат по выбранному филиалу, группе затрат и периоду, так как наименование филиала, группа затрат и период будут указаны в разных графах. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В таких случаях используются формулы массива, а также функции БДСУММ, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ подробно рассмотрены на прошлых занятиях, а вот о работе с функциями БДСУММ, СУММПРОИЗВ и применении формул массива для суммирования в соответствии с условиями поговорим в наших следующих материалах.
Excel подстановка значений из другой таблицы
- В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».
Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «—») с последующим умножением на значение из смежного столбца (стоимость).
