Краткие теоретические сведения. Функции Excel. Функции поиска и выбора

Лабораторная работа №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 ввести формулу: