Задачи на применение функции ВПР

Задание 1

Известно расписание занятий на четверг

№ занятия	Предмет	Начало занятия
	Экономическая теория	8:30
	Иностранный язык	10:20
	Информатика	12:10

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

Задание 2

Известны фамилии ответственных квартиросъемщиков каждой из 12 квартир. Оформить лист таким образом, чтобы можно было по номеру квартиры, задаваемому в одной из ячеек, получать в другой ячейке фамилию и инициалы ответственного квартиросъемщика этой квартиры.

Задание 3

Имеются два массива: студенты и сессия. Оформить лист таким образом, чтобы можно было по фамилии студента получить выборочную информацию его личных данных и результатах сессии.

Студенты

Фамилия	Имя	Отчество	Дата рождения	Условия обучения
Соколов	Иван	Петрович	11.12.83	ОО
Петров	Роман	Федорович	11.10.83	СН
Валина	Инна	Львовна	12.03.83	ОО

Сессия

Фамилия	Информатика	Физика	Отечественная история
Соколов	отлично	хорошо	отлично
Петров	хорошо	удовлетворительно	хорошо
Валина	хорошо	отлично	отлично

Выписка

Фамилия	Имя	Отчество	Условия обучения	Информатика	Физика	Отечественная история
Петров

Задание 4

Рассчитайте на основе справочных данных по единой тарифной сетке оклад работников бюджетной организации.

Единая тарифная сетка

Мин. оклад
Разряд	Коэффициент
	3,99
	4,51
	5,10
	5,76
	6,51
	7,36

Ведомость

Фамилия И.О.	Разряд	Оклад
Иванов А.Л.
Лукина П.Р.
Рощева С.Д.
Летин В.В.

Задание 5

Имеются сведения о результатах контрольных точек. Вычислить среднее количество баллов и проставить оценку в соответствии со шкалой:

	неудовлетворительно
	удовлетворительно
	хорошо
	отлично

Ведомость

Фамилия И.О.	1 точка	2 точка	Средний балл	Оценка
Иванов А.Л.
Лукина П.Р.
Рощева С.Д.
Летин В.В.

Задачи на применение функции ГПР

Задание 6

В таблице записан график дежурств на апрель.

День			…
Фамилия	Гусев	Бойко		Якин

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

Задание 7

В таблице записаны телефоны сотрудников.

Фамилия	Гусев	Бойко	…	Якин
Номер телефона	22-44-15	24-55-16		25-88-74

Оформить лист для нахождения номера телефона сотрудника, фамилия которого будет указываться в одной из ячеек. Рассмотреть два случая: список фамилий отсортирован или перечислен в произвольном порядке. В обоих случаях принять, что в списке однофамильцев нет.

Задачи на совместное использование функций ВПР, ГПР

И ПОИСКПОЗ

Задание 8

На рабочем листе Прейскурант расположен прейскурант следующего вида

Товар
Болты	2,5	2,3	1,9
Гайки	2,3	2,1	1,8
Шурупы	3,2	3,0	2,5
Гвозди	1,8	1,5	0,9

Здесь в строке Товар проставлены граничные значения количества товара, а ниже – цены за единицу товара. Для оптовых покупателей цены снижаются. Например, если покупатель приобретает 10 гвоздей, он платит 1,8 руб., если партия составляет 40 шт – то 1,5 руб., если больше 50 – то 90 коп. На следующем листе Накладная нужно получить таблицу следующего вида

Товар	Количество	Цена за 1 шт.	Сумма
Гайки		2,1	75,6
Болты		1,9	106,4
Гвозди		1,8
Итого

Здесь пользователь вводит наименования товаров и их количество. В столбце «Цена за 1 шт» автоматически выставляется цена единицы товара в соответствии с прейскурантом (в зависимости от количества). Для решения задачи нужно искать количество товара из первой строки таблицы Прейскурант (функция ГПР), а номер строки, из которой будет извлекаться цена одной штуки товара, найдите с помощью функции ПОИСКПОЗ.

Задание 9

На рабочем листе нормы расположены нормы расхода продуктов для приготовления одного блюда:

	Яйца	Молоко	Творог	Соль	Сахар	Масло
Запеканка
Оладьи
Омлет		0,5

На следующей странице нужно получить таблицу Расход продуктов

	Выпуск	Масло	Молоко	Сахар	Соль	Творог	Яйца
Итого		3600,0	50,0	400,0	160,0	8500,0	530,0
Омлет		2000,0	50,0				400,0
Оладьи		1000,0		250,0	100,0	2500,0	100,0
Запеканка		600,0		150,0	60,0	6000,0	30,0

Здесь пользователь вводит наименование блюда, например, омлет и количество выпускаемых блюд (в нашем случае 100 шт). В столбцах с наименованием продуктов автоматически проставляется расход продуктов на весь выпуск в соответствии с нормами. Обратите внимание, что порядок следования продуктов в обеих таблицах не совпадает. Для решения задачи нужно искать название блюда в первом столбце таблице Нормы (функция ВПР), а номер столбца, из которого будет извлекаться норма расхода одного продукта, найдите с помощью функции ПОИСКПОЗ. Формулу ввести один раз и затем скопировать в остальные ячейки. В формуле должны присутствовать смешанные и абсолютные ссылки на ячейки.

Библиографический список

1. Симонович, С.В. Информатика: Базовый курс [Текст]: учебник для вузов / С.В. Симонович [и др.]. – СПб.: Питер, 2001. – 640 с.

2. Додж, М. Эффективная работа с Microsoft Excel 2000 [Текст] / М. Додж, К. Стинсон. – СПб.: Питер, 2000. – 1056 c.

3. Гарнаев, А.Ю. Использование MS Excel и VBA в экономике и финансах [Текст] / А.Ю. Гарнаев. – СПб.: БХВ – СПб., 1999. – 336 с.

4. Овчаренко, Е.К. Финансово-экономические расчеты в Excel [Текст] / Е.К. Овчаренко. – 3-е изд., перераб. и доп. – М.: ФилинЪ, 1999. – 328 с.

Учебно-методическое пособие обсуждено и рекомендовано к внутривузовскому изданию на заседании кафедры «Информатика», протокол № 1 от 03.09.2005 г. Согласовано на заседании НМС специальности «Сервис», протокол № 5 от 28.10.2005 г.

Ответственный за выпуск Н.В. Ковбасюк

Редакторы: В.В. Крайнова, М.И. Товпинец, И.Н. Щухомет

Технический редактор Е.Г. Воротникова

Компьютерная верстка Е.Н. Черненко

ИД № 06457 от 19.12.01 г. Подписано в печать 22.02.06 г.

Формат бумаги 60х84/16. Печать оперативная. Усл. п.л. 3,0. Уч.-изд. л. 2,4.

Тираж 100 экз. Заказ № 54.

ПЛД № 65-175 от 05.11.99 г.

Издательство ЮРГУЭС. Типография Издательства ЮРГУЭС.

346500, г. Шахты, Ростовская обл., ул. Шевченко, 147.