Лабораторная работа №8
Функции Excel. Функции поиска и выбора
Цели работы:
1. Изучить структуру функций ГПР() и ВПР().
2. Научиться применять их в своих документах.
Краткие теоретические сведения
Достаточно часто в табличных документах присутствует справочная информация, оформленная в виде отдельных таблиц, например справочная информация о тарифной сетке или о величине скидок в зависимости от величины заказа. Такие таблицы могут быть представлены набором строк или набором столбцов (рис.1 и рис.2). Для организации вычислений требуется искать в справочных таблицах требуемые данные в одной строке (или столбце) и использовать соответствующие им значения из других строк (или столбцов) справочной таблицы. Например, по заданному номеру разряда находить величину оплаты или по заданной величине заказа находить процент скидки.
Для организации такого поиска используются функции:
ГПР() - для поиска в горизонтальных таблицах (см. рис.1)
ВПР()–для поиска в вертикальных таблицах подобных той, которая представлена на рис.2.
Синтаксис функций ГПР(), ВПР().
1. ГПР (<искомое значение>;<область поиска>,<номер строки извлечения>;<тип поиска>) – осуществляет поиск <искомого значения> в самой верхней строке <области поиска>. Эту строку будем называть ключевой. Результат извлекается из строки с заданным <номером> относительно ключевой строки <области поиска>. Номер ключевой строки блока поиска – всегда 1 (этот номер не имеет никакого отношения к фактическим номерам строк в рабочих листах книги).
Параметр <тип поиска> имеет значение ИСТИНА – 1 или ЛОЖЬ – 0. Если ЛОЖЬ, то поиск в первой строке будет точным; если ИСТИНА (или параметр не задан) – приблизительным – найденным считается наибольшее значение, меньшее или равное искомому. Ключевая строка должна быть отсортирована по возрастанию, иначе результаты могут быть непредсказуемы.
Рассмотрим порядок выполнения функции ГПР().
Пример 1: Определить значение функции ГПР(7;А2:Е4;3;0), если она работает с представленной таблицей:
 |
Решение.
è Рассмотрим аргументы заданной функции:
Аргументы функции «ГПР».
 |
Иными словами:
В верхней – ключевой – строке области поиска А2:Е4 (это строка 2 рабочего листа) функция ищет значение 7 (это ячейка В2) в этом же столбце В. Результат – число 12, выбирается из строки 3 области поиска (это строка 4 рабочего листа). На рис.3 показана работа функции ГПР.
Замечание. Искомое значение можно задать значением или именем ячейки.
Рис.3
ВПР (<искомое значение>;<область поиска>,<номер столбца извлечения>[;<тип поиска>]) – осуществляет поиск <искомого значения> в самом левом ключевом столбце <области поиска>. Результат извлекается из столбца с заданным <номером> относительно ключевого столбца <области поиска>.
Параметр <тип поиска> имеет значение ИСТИНА – 1 или ЛОЖЬ – 0. Если ЛОЖЬ, то поиск в первом столбце будет точным; если ИСТИНА (или параметр не задан) – приблизительным – найденным считается наибольшее значение, меньшее или равное искомому. Ключевой столбец должен быть отсортирован по возрастанию, иначе результаты могут быть непредсказуемы.
Пример 2: По разряду рабочего Петрова (клетка В9) требуется в тарифной сетке найти соответствующий ему тариф оплаты труда за день для дальнейшего начисления зарплаты по итогам месяца.
|
Решение.
èДля определения суточного тарифа оплаты труда для Петрова, соответствующего его разряду, необходимо просмотреть таблицу А3:В6, и в столбце В найти тариф, соответствующий разряду Петрова(ячейка В9). Так как таблица вертикальная, то нужно использовать функцию ВПР.
Аргументы для функции «ВПР».
 |
è В ячейку С9 ввести формулу: =ВПР(В9;А3:В6;2;0)
èНа рис. показан результат работы функции ВПР по извлечению тарифа для Петрова, соответствующего его разряду.
Пример 3. Создать таблицу для вычисления заработной платы работников производства. Зарплата зависит от числа отработанных дней в месяце, разряда рабочего и времени.
Правила расчета:
Зарплата = число отработанных дней * тариф(зависит от разряда)
Сумма к выдаче = Зарплата + премия
Решение.
 |
èПодготовить внешний вид рабочего листа следующим образом:
èЗаполнить столбец «Зарплата» по заданной формуле. Тариф Петра найти из таблицы «Тарифная сетка» с помощью функции ГПР.
Аргументы функции «ГПР».
 |
Таким образом, для подсчета Зарплаты Петру в ячейку D7 ввести формулу:
ГПР(С7;В$2:F$4;2;0)*B7
è Для подсчета зарплаты оставшимся рабочим, формулу ячейки D7 размножить на диапазон ячеек D7:D9.
è Заполнить столбец «Премия». Для подсчета премии Петру использовать функцию ГПР.
Аргументы функции «ГПР».
 |
Для нахождения премии для Петра в ячейку Е7 введем формулу:
= ГПР(С7;В$2:F$4;3;0)
èДля подсчета премии оставшимся рабочим размножить формулу ячейки Е7 на диапазон ячеек Е8:Е9.
èЗаполнить столбец F «Сумма к выдаче»: Сумма к выдаче = Зарплата + премия
 |
èРазмножим формулу ячейки F7 на диапазон ячеек F8:F9. Внешний вид рабочего листа после решения задачи:
Индивидуальные задания.
- Составить и оформить документ Excel согласно индивидуальному варианту.
- Сохранить таблицу в созданной ранее папке под названием «Лаб8+Фамилия».
Вариант 1.
Построить таблицу вычисления зарплаты с учетом прогрессивного подоходного налога и числа детей для 10 работников. Известно, что с месячного дохода до 2000 руб. налог не удерживается, от 2000 руб. удерживается в сумме 12%, от 4000 руб. – 15%, от 6000 руб. – 20%, от 10000 руб. – 30%. Причем налог удерживается не со всей суммы заработка, а с величины, меньшей на одну минимальную зарплату на каждого ребенка (иждивенца), т.е.:
Правила расчета:
Сумма обложения = ЕСЛИ Доход – кол-во детей * мин. з/п<=0, то сумма обложения равна 0, иначе Доход – кол-во детей * мин. з/п
Налог = Сумма обложения * Процент налога(зависит от суммы обложения)
Сумма на руки = Доход – налог
Контроль ввода:
Величины мин. з/п, доход работников и количество детей должны быть положительными.
Правила оформления:
Ячейки, работник должны выделяться фоном и красным курсивным шрифтом, если у него нет детей.
А также, если работник имеет нулевую сумму обложения, то соответствующая ему строка должна выделяться цветом фона.
Напоминание:
 |
Параметр <тип поиска> имеет значение ИСТИНА – 1 или ЛОЖЬ – 0. Если ЛОЖЬ, то поиск в первой строке будет точным; если ИСТИНА (или параметр не задан) – приблизительным – найденным считается наибольшее значение, меньшее или равное искомому.
Вариант 2.
Вычислить размер заработка 10 продавцов фирмы. Известны: разряды продавцов, сумма продаж для каждого продавца, норма продажи, оклады разрядов.
 |
Правила расчета:
Премия: Если объем продаж > норма * 2, то премия = 10% от Продаж + 1000руб.
иначе, если объем продаж меньше нормы, то премия составляет 10% от Продаж;
иначе (т.е. если объем продаж > нормы, то) премия составляет 20% от Продаж;
Зарплата = Оклад (зависит от разряда) + Премия (зависит от объема продаж)
Контроль ввода:
Ячейки «Разряд»– {1, 2, 3, 4}, ячейки «Продано» - положительные значения.
Правила оформления:
Строки продавцов, у которых величина «Продано» больше нормы в 2 раза, должны выделяться красным курсивным шрифтом.
А также, если величина «Продано» меньше нормы, то строка продавца должна выделяться фоном.
Вариант 3.
Вычислить величину суммы вклада для 10 клиентов банка на конец года. Все вкладчики получают Премию в зависимости от суммы вклада.
Правила расчета:
Средняя сумма = среднее значение остатков вклада за все кварталы с учетом годовой инфляции (сумма вклада увеличивается на 12%).
Премия = средняя сумма * процент (зависит от средней суммы)
Контроль ввода:
Ячейки «№ счета» – 5 символов.
Ячейки с остатками вкладов – величина больше 0.
Правила оформления:
Строки вкладов должны выделяться фоном и красным курсивным шрифтом, если премия данного вклада – больше или равно 35%.
Вариант 4.
 |
Построить таблицу расчетов страхования жизни 10 человек. Клиент может застраховаться на любую страховую сумму, для чего делает взнос в размере, зависящем от возраста застрахованного. Кроме того, необходимо рассчитать сумму выплаты в зависимости от тяжести травмы (всего 4 категории). При возникновении травмы или болезни клиенту возвращается соответствующий процент от страховой суммы – выплата.
Контроль ввода:
Год рождения >= 1900, страховая сумма больше нуля, категория травмы – список от 1 до 4.
Правила оформления:
Строка клиента должны выделяться красным курсивным шрифтом, если возраст клиента старше 70 лет.
А также, если травма 4 категории, то строка клиента выделяется красным полужирным курсивом и цветом фона.
Вариант 5**.
Построить таблицу расчетов страхования имущества от кражи, пожара и протечки (в произвольной комбинации). Желаемый вид страховки обозначается «+». Клиент может застраховаться на любую страховую сумму, для чего делает страховой взнос.
Правила расчета:
Если имущество страхуется от пожара, то страховой взнос = страховая сумма * 10%.
Если имущество страхуется от протечки, то страховой взнос = страховая сумма * 8%
Если имущество страхуется от кражи, то страховой взнос = страховая сумма * 7%.
Страховка от кражи снижается на 1% при наличии стальной двери и еще на 2%, если в подъезде имеется вахтер.
 |
Если страхование производится на все виды страховых случаев сразу, то взнос уменьшается еще на 1%.
Контроль ввода:
Страховая сумма кратна 1000руб. Вид страховки помечается только знаком +.
Правила оформления:
Строки клиента должна выделяться красным полужирным курсивным шрифтом, если страховка производится на все случаи жизни.
А также, если клиент получает скидку, строка должна выделяться фоном.
Вариант 6.
Вычислить зарплату 10 рабочих (колонка Заработано).
Правила расчета:
Зарплата = Кол-во изготовленных деталей * стоимость детали (зависит от разрядного коэффициента).
Если рабочий изготовил более 30 деталей, ему начисляется премия в размере 50% от стоимости каждой детали, начиная с 31-ой.
Иначе, если им изготовлено свыше 3 бракованных деталей – из зарплаты вычитается штраф 50 руб.
Зарплата рабочего должна быть уменьшена в случае,
Правили оформления: в колонке Брак выводится восклицательный знак, если бракованных деталей до 5; выводится сообщение Брак!, если бракованных деталей больше 5 и сообщение Аврал! На красном фоне, если бракованных деталей больше 7.
Вариант 7.
Вычислить заработанную 10 рабочими сумму в зависимости от количества отработанных им в неделю часов и их вида.
Правила расчета:
Зарплата определяется как число отработанных Нормальных часов, умноженных на Стоимость нормального часа плюс стоимость Сверхурочных часов и часов отработанных в Выходные. Стоимость таких часов увеличивается на 150% и 200% относительно Нормального часа.
Кроме того, если общее число отработанных часов превышает 52, работник получает Доплату в 100 руб., если более 60 – 200руб., больше 66 – 250руб. плюс 5% от Зарплаты.
На руки = Зарплата + Доплата (с учетом Налога).
Вариант 8.
Вычислить размер недельной заработной платы 10 рабочих.
Правила расчета:
Ежедневно рабочие могут находиться как в обычном, так и во вредном производстве (их часы работы указаны в двух строках для каждого человека).
По итогам недели вычисляются число дней, отработанных во вредных и обычных условиях, и сумма часов. На их основе определяется оплата труда:
Оплата труда = Всего часов * Часовую оплату.
Кроме того, рабочим полагается Доплата за сверхурочный труд.
Доплата за сверхурочный труд в обычных условиях производится при наличии сверхурочного времени. Разность между фактической длиной рабочей недели и 48 часами оплачивается по тарифу сверхурочных часов (клетка L1).
Доплата за сверхурочный труд во вредных условиях производится аналогично, но только если отработано свыше 20 вредных часов. Кроме того, в доплату входит сумма на покупку молока (клетка L2) за каждый день, отработанный во вредных условиях.
В столбце М формируется сообщение (слово Отгул), если отработано свыше 30 часов во вредном производстве (на следующей неделе работник получит один отгул).
Контроль ввода: в ячейках G7:G12 отработано в день <=14 час.
Форматирование: слово Отгул на красном фоне.
