Функция ВПР в Excel for Mac 2011
В данной статье будет рассмотрена пошаговая инструкция использования функции ВПР в экселе, так сказать «Функция ВПР для чайников». Мы рассмотрим использование функции ВПР с несколькими условиями, а также основные ошибки, которые допускаются при работе с формулой ВПР.
Функция ВПР в Excel используется для поиска данных в таблице или диапазоне по строкам и извлечения их в другую. Эта функция является очень полезным инструментом при работе большим количеством данных.
Рассмотрим пример использования функции ВПР. Пусть у нас есть таблицы, которые содержат информацию о продаже растений и прайс-лист.
Функция ВПР в Excel for Mac 2011 – Продажи
Функция ВПР в Excel for Mac 2011 – Прайс-лист
Нам необходимо проставить цены в соответствии с каждым наименованием и типом растения из прайс-листа.
1. В ячейке С2 мы вводим следующую формулу:
=ВПР(B2;'Прайс-лист'!$B$1:$C$147;2;0)
Более подробно рассмотрим синтаксис формулы ВПР на данном примере.
Элемент формулы |
Имя аргумента |
Описание |
B2 |
искомое_значение |
Значение для поиска. В данном случае это значение наименования посадочного материала «Пиперомия». |
'Прайс-лист'!$B$1:$C$147 |
таблица |
Диапазон ячеек, в котором происходит поиск искомого значения и возвращаемого значения с помощью функции ВПР. !!! Первый столбец в диапазоне ячеек обязательно должен содержать искомое значение !!! Незабываем закрепить адрес в ссылке на таблицу абсолютными ссылками при помощи символа $. |
2 |
номер_столбца |
Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение. У нас это цена за единицу. |
0 |
интервальный_просмотр |
Логическое значение, которое определяет тип совпадения: точное или приблизительное. 1 (ИСТИНА) – выполняется поиск ближайшего значения 0 (ЛОЖЬ) – поиск точного значения |
Таким образом, функция ВПР ищет значение «Пиперомия» в первом столбце (столбце B) таблицы на листе «Прайс-лист» и возвращает соответствующее значение цены 50 грн., найденное во втором столбце этой же таблицы. Так как для аргумента задано значение ЛОЖЬ, то функция ВПР возвращает точное совпадение.
2. Выделяем все ячейки, и на вкладке «Главная» в группе «Правка» (для пользователей MS Excel для Windows в группе «Редактирование») нажимаем «Заполнить» --> «Вниз». Таблица приобретет вид:
Функция ВПР в Excel for Mac 2011 – заполнение столбца формулой
Но данный результат нас не устраивает. Так как в примере наименования в таблице «Прайс-лист» неуникальны, функция ВПР выводит первое встречающееся значение.
В нашем прайс-листе есть несколько одинаковых наименований, потому что некоторые виды растений продаются как в горшках, так и черенками или цветочной рассадой и соответственно по разным ценам, поэтому будем использовать функцию ВПР с несколькими условиями.
Использование функции ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. Мы будем использовать для выбора два критерия: наименование и соответствующий тип растения.
1. Но для этого нам сперва нужно создать дополнительный столбец на листе «Прайс-лист», который будет содержать информацию о типе и наименовании растения.
Создаем пустой столбец на листе «Прайс-лист» и в ячейке А2 вводим следующую формулу:
=B2&C2
При помощи этой формулы мы сцепляем значение столбца «Тип» и «Наименование посадочного материала». Заполняем все ячейки.
Прайс-лист видоизмениться таким образом:
Функция ВПР в Excel for Mac 2011 – создание вспомогательного столбца
2. Переходим на лист «Продажи» в ячейке С2 вводим следующую формулу:
=ВПР(A2&B2;'Прайс-лист'!$A$2:$D$147;4;0)
Аналогично заполняем остальные ячейки (см. пункт 2).
В результате при помощи функции ВПР мы получаем цены для каждого наименования с учетом типа растения.
Почему не работает функция ВПР
В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.
Ошибка |
Причина |
Решение |
#Н/Д |
Неверное расположение столбца, по которому происходит поиск
|
Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.
|
#Н/Д |
Не закреплен диапазон таблицы |
Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен.
|
#Н/Д |
Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0) |
–
|
#Н/Д |
В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована. |
Отсортируйте первый столбец таблицы по возрастанию наименований. |
#Н/Д |
Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы. |
Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ. |
#ССЫЛКА! |
Значение номер столбца превышает число столбцов в таблице |
Проверьте номер столбца, содержащий возвращаемое значение. |
#ИМЯ? |
В формуле пропущены кавычки |
Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: =ВПР("Пиперомия"; 'Прайс-лист'!C2:D147; 2;0) |
На этом закончим знакомство с функцией ВПР (англ. VLOOKUP).
Популярные рубрики
Популярные теги