Функции просмотра и ссылок Лабораторная работа №9. Использование функций просмотра и ссылок

Функции просмотра и ссылок позволяют просматривать и обрабатывать информацию, хранящуюся в списке или таблице. Так, функции ВПР и ГПР используются для поиска информации в прямоугольных таблицах. Данные функции имеют следующие аргументы:

ВПР(искомое_знач; таблица; номер_столбца; тип_просмотра);

ГПР(искомое_знач; таблица; номер_строки; тип_просмотра),

искомое_знач - это значение, которое необходимо найти в первом столбце (строке) таблицы;

таблица - массив или имя диапазона, который определяет таблицу с данными. Первый столбец (строка) должны быть упорядочены в алфавитном порядке текстовых значений или в порядке возрастания числовых данных, а также значений даты/времени;

номер_столбца (номер_строки) - указывает, из какого столбца (строки) таблицы следует выбирать возвращаемое значение;

тип_просмотра - определяет логическое значение для указания типа соответствия: точное или приближенное. Если аргумент опущен, то используется приближенное сравнение, что означает поиск наибольшего сравниваемого значения, которое меньше или равно искомому значению.

Поиск осуществляется по следующему правилу:

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

Задание

Выполнить расчет зарплаты сотрудников по данным, приведенным в таблице (рис. 27) используя функции просмотра. Таблицу данных и диаграмму отобразить на одном листе в текстовом процессоре MS Word. Выполнить задание по варианту.

Р ис. 27. Таблицы исходных данных

Решение

1. Заполните основную (А3:F12) и вспомогательную (H3:I13) таблицы по представленным данным. Формат ячеек I4:I13 – процентный.

2. В ячейку Е4 введите формулу =$D$4*ВПР(С4;$H$4:$I$13;2). Наиболее удобный способ ввода формулы – работа с окном Аргументы функции. Для этого вызовите Мастер функций меню Вставка/Функция, в раскрывающемся списке выберите категорию функции Ссылки и массивы. В появившемся списке функций найдите функцию ВПР, подтвердите выбор функции нажатием кнопки ОК.

В появившемся окне Аргументы функции (рис. 28) укажите Искомое_значение С4 (используется относительная адресация, т.к. при автозаполнении ячеек Е5:Е12 этот адрес будет меняться, определяя стаж каждого работника). В поле Таблица указывается диапазон значений вспомогательной таблицы, который имеет абсолютную адресацию $H$4:$I$13. В поле Номер_столбца укажите 2. Это будет соответствовать второму столбцу вспомогательной таблицы, а именно ячейкам I4:I13.

Р ис. 28. Окно аргументов функции ВПР

Для получения результата в столбце Всего, необходимо просуммировать значения «Тариф» и «Надбавка за стаж». В ячейку F4 введите формулу = $D$4+E4.

3. Установите формат ячеек E4:F12 – денежный.

4. Подберите подходящий тип и постройте диаграмму заработной платы.

Окончательный вариант основной таблицы с применением условного форматирования для значений стажа > 5 лет и добавлением итоговой суммы приведен на рис. 29.

Р ис. 29. Заполненная основная таблица с применением условного форматирование в столбце «Стаж»

Самостоятельная работа

Выполнить расчет зарплаты сотрудников по данным, приведенным в таблице (рис. 27) используя функции просмотра. Таблицу данных и диаграмму отобразить на одном листе в текстовом процессоре MS Word. Укажите собственные значения надбавок за стаж. Примените условное форматирование для столбца «Стаж» (не менее 2-х условий).