РАБОТА СО СПРАВОЧНИКАМИ. ИСПОЛЬЗОВАНИЕ ФУНКЦИИ ВПР
Функции для работы со ссылками и массивами
ВПР (искомое_значение; таблица; номер_столбца; интервальный_просмотр)- ищет значение в крайнем левом столбце и возвращает значение ячейки, находящейся в указанном столбце той же строки. (просматривает сверху вниз левый столбец таблицы пока не встретит искомое_значение, а затем просматривает строку до столбца с номером номер_столбца)
искомое_значение-значение, которое должно быть найдено в первом столбце массива (значение, ссылка или строка текста)
таблица-таблица с текстом, числами или логическими значениями, в котором производится поиск данных; может быть ссылкой или имя диапазона.
номер_столбца-номер столбца в таблице, из которого нужно вернуть значение. Первый столбец значений имеет номер 1.
интервальный_просмотр- логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА или отсутствие значения) должен производиться поиск в первом отсортированном столбце (отсортированном по возрастанию)
Функций МS Ехсеl - ВПР, облегчает работу по заполнению таблиц при необходимости использования справочных таблиц. Примером может служить заполнение накладной, в которой при вводе кода товара должно автоматически появляться наименование товара, цена товара, фирма производитель и т.д.
На первом шаге, на отдельном листе создайте справочную таблицу и присвойте ей имя «Справочник», например: __________________________________
Код товара
Название товара
Цена
Товар1
$200,00
Товар2
$250,00
Товар3
$280,00
Товар3
$300,00
Табл. 3. Справочник товаров
Предположим, на другом листе требуется вводить данные о проданных товарах. Создайте таблицу с заголовками столбцов: Код товара, Название товара, Цена, Кол-во, Сумма:________
Код товара
Название товара
Цена
Кол-во
Сумма
=ВПР(А2;справочник;2;0)
=ВПР(А2;справочник;3 ;0)
Табл. 4. Фрагмент накладной на продажу товаров
Введите код товара 0001. В первую строку табл. 4 в столбце «Название товара»введите формулу =ВПР(А2;справочник;2;0).
Функция ВПР имеет следующие аргументы:
Искомое_значение:в этом окошке указывается значение (код товара), определяющее остальные параметры (наименование товара, его цену). Это значение будет искаться в первом столбце таблицы-справочника.
Таблица:в этом окошке указывается адрес или имя справочной таблицы (в нашем примере - это имя «справочник»).
Номер_столбца:в этом окошке указывается номер столбца, содержащий соответствующую характеристику значения, введенного в первом окошке (в нашем случае номер 2 определяет столбец «Наименование СРШ)
Интервальный_просмотр:имеет два значения «0», если таблица-справочник не отсортирована по первому столбцу, и «1», если она отсортирована по первому столбцу.
Для определения цены товара, код которого будет введен в ячейке А2, следует ввести формулу: ВПР(А2;справочник;3;0).
При копировании функции ВПР вниз при отсутствии данных в столбце А в ячейках появляется сообщение об ошибке #Н/Д. Чтобы избежать его, следует предусмотреть ввод "пробела" в случае, если код соответствующего СР11 еще не выбран. Реализовать это можно с помощью функции ЕСЛИ. В рассматриваемой ситуации функция будет иметь вид: =ЕСЛИ(А2=0;""; ВПР(А2;справочник;2;0)).При вводе кода товара в столбцах «Наименование товара и «Цена» появятся соответствующие коду значения. Для ввода кодов товаров мы рекомендуем использовать возможность МS Ехсеl по вводу значений из фиксированного списка с помощью инструмента Данные\Проверка.
Использование функций в Ехсеl
Задание 1. Расчет значения премии сотрудникам с использованием функции ЕСЛИ
А
В
С
D
Е
ФИО
Тарифная ставка(руб.)
Отработано часов
Начислено
Премия
Сидоров В.И.
Андреева И.Т.
Ковалева О.А.
ПО
Лобанов А.О.
Если сотрудник отработал больше 20 часов, то назначим премиальный коэффициент равный 20%, в противном случае -10%.
Для расчета Начислено первому сотруднику щелкните в ячейке В4, введите формулу В2*С2 Для расчета премии первому сотруднику щелкните в ячейке Е4. Вызовите Мастер функций.
В категории Логические выберите функцию ЕСЛИ. В соответствующем диалоговом окне следует указать:
• в окошке Лог_выражение:С4>20;
• в окошке Значение_если_истина:Б4*$Е$2;
• в окошке Значение_если_ложь: 04*$Е$1. Нажмите кнопку ОК.
Задание 2. Начисление «сложной» премии.
Рассмотрим пример начисления премии по следующему алгоритму: Если отработано меньше или 10 часов, то премия не назначается.
Если отработано больше или 20 часов, то назначается премия в размере 20% от Начислено. В противном случае (отработано больше 10 часов и меньше 20 часов) назначается премия в размере 10% от Начислено.
Щелкните в ячейке Е4. Вызовите функцию ЕСЛИ. Введите:
• в окошке Лог_выражение:С4>=20;
• в окошке Значение_если_истина:Э4*$Е$2;
Щелкните в окошке Значение_если_ложь и вызовите вторую функцию ЕСЛИ. Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:
