Постановка задачи. Решение

Использование функции ВПР (VLOOKUP) для подстановки значений

Категория: Функции, просмотров: 2821, опубликовано: 11.09.2006 Скачать пример

Постановка задачи

Итак, имеем две таблицы - таблицу заказов и прайс-лист:

Задача - подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP). Эта функция ищет заданное значение (в нашем примере это слово "Яблоки") в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь - дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме "шапки" (G2:H19), выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте мастер функции (меню Вставка - Функция). В категории Ссылки и массивы найдите функцию ВПР и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

Искомое значение - то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае - слово "Яблоки" из ячейки B3.
Таблица - таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя "Прайс" данное ранее.
Номер_столбца- порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
Интервальный_просмотр - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

Если введено значение ЛОЖЬ, то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, "Кокос"), то она выдаст ошибку #Н/Д (нет данных).
Если введено значение ИСТИНА, то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с "кокосом" функция попытается найти товар с наименованием, которое максимально похоже на "кокос" и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле, поэтому для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением являются не текстовые, а числовые искомые значения, например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

P.S.¹

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д если:

1. Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.

3. Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;ЛОЖЬ)

4. Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;ЛОЖЬ)

P.S.²

Для подавления сообщения об ошибке #Н/Д в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться вот такой конструкцией:

Функция ЕНД проверяет - не возникла ли ошибка#Н/Д как результат работы ВПР и если да, то выводит пустую строку ("") или ноль, а если нет - то выводит результат работы ВПР.

Улучшаем функцию ВПР (VLOOKUP)

Категория: Функции, просмотров: 2115, опубликовано: 29.10.2006 Скачать пример

«Как правильно уложить парашют?»
Пособие. Издание 2-е, исправленное.

Допустим, у нас имеется вот такая таблица заказов:

Нам необходимо узнать, например, какова была сумма третьего заказа Иванова или когда Петров оформил свою вторую сделку. Встроенная функция ВПР (VLOOKUP) умеет искать только первое вхождение фамилии в таблицу и нам не поможет.

Напишем свою функцию, которая будет искать не только первое, а, в общем случае, N-ое вхождение. Назовем ее, допустим, VLOOKUP2.

Откройте меню Сервис - Макрос - Редактор Visual Basic, вставьте новый модуль (меню Insert - Module) и скопируйте туда текст этой функции:

Function VLOOKUP2(Table As Range, SearchColumnNum As Integer, SearchValue As Variant, _
N As Integer, ResultColumnNum As Integer)
Dim i As Integer
Dim iCount As Integer

For i = 1 To Table.Rows.Count
If Table.Cells(i, SearchColumnNum) = SearchValue Then
iCount = iCount + 1
End If
If iCount = N Then
VLOOKUP2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
End Function

Закройте редактор Visual Basic и вернитесь в Excel.

Теперь в Мастере функций в категорииОпределенные пользователем можно найти нашу функцию VLOOKUP2 и воспользоваться ей. Синтаксис функции следующий:

=VLOOKUP(таблица; номер_столбца_где_ищем; искомое_значение; N; номер_столбца_из_которого_берем_значение)

Т.е. для того, чтобы найти, например, сумму третьего заказа Иванова нужно будет ввести:

=VLOOKUP2(A1:D21;1;"Иванов";3;4)