Создание таблицы «Конвертирование цены товаров»
Рассмотрим синтаксис поисковой функции ВПР, так как при создании таблицы «Конвертирование цены товаров» она используется для определения столбцов «Дата конвертирования» и «Курс конвертирования» (столбцы С, D).
Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.
ВПР означает «вертикальный поиск».
Синтаксис:
ВПР(искомое_значение;таблица; номер_столбца; интервальный_просмотр) или в несколько схематичной форме функция может быть приведена следующим образом: ВПР(<что искать>;<где искать>;<откуда взять>;<как искать>).
Искомое_значение — это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой;
Таблица — таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.
Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.
Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию.
Значения в первом столбце аргумента «таблица» могут быть текстовыми строками, числами или логическими значениями.
Текстовые строки сравниваются без учета регистра букв;
Номер_столбца — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_столбца» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!;
Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
Замечание1.
Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение.
Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента «таблица», то функция ВПР возвращает значение ошибки #Н/Д.
Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.
Замечание 2. Функция ГПР (горизонтальный поиск) используется аналогично функции ВПР, только третий аргумент – номер строки.
Постановка задачи. Создать таблицу конвертирования цены товара. При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а расчет с внутренними покупателями - в рублях. Ввиду этого нужно иметь возможность конвертирования в рубли исходной (в момент поступления) и текущей ("на сегодня") цены товара. Вся необходимая информация хранится в двух таблицах: курса доллара и расчет рублевого эквивалента товара.
Решение. Технология создания таблицы конвертирования цены товара.
Создадим два рабочих листа: лист Курс и лист Товар. Содержимое листа Курс (рисунок 5.10) - это последовательные значения дат и стоимости доллара. На листе Товар (рисунок 5.11) в ячейке В1 предъявляется текущая дата (функция СЕГОДНЯ()), а также содержатся сведения о дате закупки товара и о закупочной цене (столбцы А и В). В столбце Е вычисляется рублевый эквивалент этой цены, но прежде сформируем два столбца, нужные только для сведения оператора. В столбце С определяется ближайшая, найденная на листе Курс, дата СЗ = ВПР (АЗ; Курс!А$2:В$240;1;1), по курсу которой (столбец D) и определяется конвертирование доллара в рубли. В столбце D определяется найденный курс доллара, по которому будет осуществляться конвертирование цены товара по формуле DЗ=ВПР(А3;Курс!А$2:В$240;2;1). На основании его и находится закупочная цена товара в рублях E3=D3*B3. В столбце F определяется рублевый эквивалент цены “товара на сегодня” F3=ВПР(B$1;Курс!A$2:B$240;2;1).
В обеих формулах, для поиска даты (ячейка А3), используется функция ВПР() с четвертым аргументом равным 1, т.е. поиск даты в таблице курса доллара будет не точным, а интервальным, поскольку некоторых дат там нет (валютная биржа не работает в выходные дни) и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты, для которой она имеется. В качестве области поиска определена область листа Курс, содержащая два столбца А и В и число строк, равное числу рабочих дней в году (около 240) или диапазон А2:В240. В первой формуле поиск осуществляется в первом столбце диапазона (третий аргумент равен единице). Во второй формуле – во втором столбце (третий аргумент равен двум).
Рисунок 5.10. Таблица Курс доллара
Окончательные результаты представлены на рисунке 5.11 Конвертирование цены товаров.
Рисунок 5.11. Конвертирование цены товаров