Задачи на применение функции ВПР
Задание 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.
![]() | |||
![]() |