Лабораторная работа №8 «Функции просмотра и ссылок»
Функции просмотра и ссылок позволяют обрабатывать информацию, хранящуюся в таблице. Например, необходимо составить ведомость аренды площади, исходя из стоимости одного кв.м. Для поиска соответствующей стоимости конкретного типа площади (рисунок 16) используются две встроенные функции ВПР и ГПР, которые имеют следующий синтаксис:
ВПР(искомое значение; таблица; номер столбца; тип просмотра),
ГПР(искомое значение; таблица; номер строки; тип просмотра),
где:
искомое значение – это значение, которое необходимо найти в первом столбце (для функции ВПР) или в первой строке (для функции ГПР),
таблица – это дополнительная таблица информации, в которой в первом столбце (для ВПР) или в первой строке (для ГПР) расположены в возрастающем алфавитном порядке (как текстовые данные, так и числовые данные и значения даты и времени) искомые (известные) значения.
номер столбца (строки) – это номер столбца (строки) относительно начала указанного диапазона во втором параметре.
тип просмотра – это логическое значение ИСТИНА или ЛОЖЬ, которое определяет поиск точного или приближенного соответствия. Если параметр имеет значение ИСТИНА или опущен, то определяется ближайшее меньшее значение к искомому. Если аргумент имеет значение ЛОЖЬ, ищется точное соответствие. Если соответствие не найдено, то возвращается значение ошибки #Н/Д.
Поиск осуществляется следующим образом. Искомое значение сравнивается с данными, расположенными в крайнем левом столбце (для функции ВПР) или верхней строке (для функции ГПР) дополнительной таблицы и т.о. определяется строка (столбец) в котором расположено возвращаемое значение. Номер столбца (строки) задается значением третьего параметра.
Пример. Сформировать ведомость учета аренды площади (рисунок 16).
Рисунок 22 – Исходные данные для ведомости аренды
Для заполнения в основной таблице поля «стоимость 1 кв. м.» в ячейку B2 введем формулу
=ГПР(A2,$B$9:$F$10,2).
В ячейку D2 вводим формулу, вычисляемую общую стоимость аренды площади в месяц:
=B2*C2.
Вычисляем общий итог:
=D2*E2.
Результат представлен на рисунке 17.
Рисунок 23 – Ведомость учета арендной платы.
Задание
Заполнить таблицу по аналогии (не менее 15 записей в основной таблице и не менее 5 записей в дополнительной, но меньше чем в основной) и с помощью функций ВПР и ГПР вычислить пустые столбцы.
Вариант 1.
№ пп |
Наименование товара |
Кол-во |
Дата реализации |
Цена, евро |
Цена, руб. |
стоимость, евро |
стоимость, руб. |
1 |
Утюг |
3 |
15 янв. |
12 |
|
|
|
2 |
пылесос |
7 |
16 февр. |
27 |
|
|
|
Дата |
15 янв. |
20 февр. |
Курс |
35,79 |
34,52 |
Вариант 2.
№ пп |
ФИО |
должность |
оклад |
премия |
Начислено, руб. |
Налог, 13%, руб. |
К выплате, руб. |
|
% |
сумма, руб. |
|||||||
1 |
Иванов И.С. |
Слесарь 5 кат. |
|
2 |
|
|
|
|
1 |
Петрова А.К. |
Инженер 2 кат. |
|
3 |
|
|
|
|
должность |
Инженер 2 кат. |
Слесарь 5 кат. |
Оклад, руб. |
3482 |
4217 |
Вариант 3.
Наименование товара |
Фирма производитель |
Цена, евро |
Дата реализации |
Цена, руб. |
1 |
2 |
3 |
4 |
5 |
СВЧ |
Samsung |
|
1 февр. |
|
Кофеварка |
Tefal |
|
24 мая |
|
Эл. чайник |
Bosch |
|
1 февр. |
|
Утюг |
LG |
|
24 мая |
|
СВЧ |
Tefal |
|
1 февр. |
|
|
|
|
24 мая |
|
Наименование |
Bosch |
LG |
Tefal |
Samsung |
1 |
2 |
3 |
4 |
5 |
Кофеварка |
€ 376 |
– |
€ 56 |
€ 43 |
Эл. чайник |
– |
– |
€ 43 |
€ 37 |
СВЧ |
€ 294 |
€ 271 |
€ 137 |
- |
Утюг |
€ 43 |
- |
- |
€ 56 |
Дата |
курс |
1 |
2 |
10 янв. |
34,57 |
20 февр. |
35,23 |
Пояснение. Использовать функции ВПР() и ГПР().
Вариант 4.
№ пп |
ФИО |
Должность |
Оклад, руб. |
Стаж, лет |
Надбавка за стаж, руб. |
Начислено, руб. |
Налог, 13%, руб. |
К выплате, руб. |
1 |
Иванов И.С. |
Слесарь 5 кат. |
|
2 |
|
|
|
|
1 |
Петрова А.К. |
Инженер 2 кат. |
|
3 |
|
|
|
|
должность |
Инженер 2 кат. |
Слесарь 5 кат. |
Оклад, руб. |
3482 |
4217 |
Стаж, лет |
2 |
5 |
10 |
15 |
Надбавка за стаж, % |
1% |
3% |
5% |
10% |
Вариант 5.
Наименование товара |
Фирма производитель |
Цена, евро |
Дата реализации |
Цена, руб. |
СВЧ |
Samsung |
|
1 февр. |
|
Кофеварка |
Tefal |
|
24мая |
|
Наименование |
Bosch |
LG |
Tefal |
Кофеварка |
€ 376 |
– |
€ 56 |
Эл. чайник |
– |
– |
€ 43 |
СВЧ |
€ 294 |
€ 271 |
€ 137 |
Дата |
15 янв. |
20 февр. |
Курс |
35,79 |
34,52 |
Пояснение. Использовать функцию ПОИСКПОЗ() и только одну функцию из ВПР() и ГПР().
Вариант 6.
№ пп |
ФИО |
Должность |
Оклад, руб. |
Стаж, лет |
Надбавка за стаж, руб. |
Начислено, руб. |
Налог, 13%, руб. |
К выплате, руб. |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
Иванов И.С. |
Слесарь 5 кат. |
|
2 |
|
|
|
|
1 |
Петрова А.К. |
Инженер 2 кат. |
|
3 |
|
|
|
|
Стаж \ Должность |
Инженер 2 кат. |
Слесарь 5 кат. |
1 |
2 |
3 |
2 |
3485 |
4300 |
5 |
3542 |
4607 |
10 |
3952 |
5149 |
Пояснение. Использовать функции ВПР() и ГПР().
Вариант 7.
№ пп |
ФИО |
Должность |
Оклад, руб. |
Стаж, лет |
Надбавка за стаж, руб. |
Начислено, руб. |
Налог, 13%, руб. |
К выплате, руб. |
1 |
Иванов И.С. |
Слесарь 5 кат. |
|
2 |
|
|
|
|
1 |
Петрова А.К. |
Инженер 2 кат. |
|
3 |
|
|
|
|
Стаж \ Должность |
Инженер 2 кат. |
Слесарь 5 кат. |
2 |
3485 |
4300 |
5 |
3542 |
4607 |
10 |
3952 |
5149 |
Пояснение. Использовать функцию ПОИСКПОЗ() и только одну функцию из ВПР() и ГПР().