Функция ВПР в Excel for Mac 2011


В данной статье будет рассмотрена пошаговая инструкция использования функции ВПР в экселе, так сказать «Функция ВПР для чайников». Мы рассмотрим использование функции ВПР с несколькими условиями, а также основные ошибки, которые допускаются при работе с формулой ВПР.  

Функция ВПР в Excel используется для поиска данных в таблице или диапазоне по строкам и извлечения их в другую. Эта функция является очень полезным инструментом при работе большим количеством данных.

Рассмотрим пример использования функции ВПР. Пусть у нас есть таблицы, которые содержат информацию о продаже растений и прайс-лист.

Funktsiya-VPR-v-Excel-for-Mac-2011-1-funktciia-vpr-v-excel-for-mac-2011

Функция ВПР в Excel for Mac 2011 – Продажи

Funktsiya-VPR-v-Excel-for-Mac-2011-2-funktciia-vpr-v-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 в группе «Редактирование») нажимаем «Заполнить» --> «Вниз». Таблица приобретет вид:

Funktsiya-VPR-v-Excel-for-Mac-2011-3-funktciia-vpr-v-excel-for-mac-2011

Функция ВПР в Excel for Mac 2011 – заполнение столбца формулой

Но данный результат нас не устраивает. Так как в примере наименования в таблице «Прайс-лист» неуникальны, функция ВПР выводит первое встречающееся значение.

В нашем прайс-листе есть несколько одинаковых наименований, потому что некоторые виды растений продаются как в горшках, так и черенками или цветочной рассадой и соответственно по разным ценам, поэтому будем использовать функцию ВПР с несколькими условиями.

 

Использование функции ВПР с несколькими условиями

 Рассмотрим пример функции ВПР с несколькими условиями. Мы будем использовать для выбора два критерия: наименование и соответствующий тип растения.

1. Но для этого нам сперва нужно создать дополнительный столбец на листе «Прайс-лист», который будет содержать информацию о типе и наименовании растения.

Создаем пустой столбец на листе «Прайс-лист» и в ячейке А2 вводим следующую формулу:

=B2&C2

При помощи этой формулы мы сцепляем значение столбца «Тип» и «Наименование посадочного материала». Заполняем все ячейки.

Прайс-лист видоизмениться таким образом:

Funktsiya-VPR-v-Excel-for-Mac-2011-4-funktciia-vpr-v-excel-for-mac-2011

Функция ВПР в Excel for Mac 2011 – создание вспомогательного столбца

2. Переходим на лист «Продажи» в ячейке С2 вводим следующую формулу:

=ВПР(A2&B2;'Прайс-лист'!$A$2:$D$147;4;0)

Аналогично заполняем остальные ячейки (см. пункт 2).

В результате при помощи функции ВПР мы получаем цены для каждого наименования с учетом типа растения.

 

Почему не работает функция ВПР

В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.

Ошибка

Причина

Решение

#Н/Д

Неверное расположение столбца, по которому происходит поиск

 

Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым.

  • Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы.
  • Или создайте вспомогательный дублирующий  столбец, слева в таблице.

#Н/Д

Не закреплен диапазон таблицы

Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен.

  • Используйте абсолютные ссылки ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон.

#Н/Д

Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0) 

 

#Н/Д

В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована.

Отсортируйте первый столбец таблицы по возрастанию наименований.

#Н/Д

Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.

Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.

#ССЫЛКА!

Значение номер столбца превышает число столбцов в таблице

Проверьте номер столбца, содержащий возвращаемое значение.

#ИМЯ?

В формуле пропущены кавычки

Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки.

Например:

=ВПР("Пиперомия"; 'Прайс-лист'!C2:D147; 2;0)

 На этом закончим знакомство с функцией ВПР (англ. VLOOKUP).


Категории: MS Excel