Материал: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78

Таблица 3Данные о работе различных отделов библиотеки.

Дата	Автор	Абонемент	Читальный зал	Книгохранение
18.12.04	Пушкин	8	53	2
18.12.04	Толстой	4	24	0
18.12.04	Лермонтов	7	35	4
18.12.04	Достоевский	1	23	0
18.12.04	Гоголь	2	14	0
19.12.04	Пушкин	…	…	…
19.12.04	Толстой
19.12.04	Пушкин	8	53	2
19.12.04	Толстой	4	24	0
19.12.04	Лермонтов	7	35	4
19.12.04	Достоевский	1	23	0
19.12.04	Гоголь	2	14	0
20.12.04	Пушкин	…	…	…
20.12.04	Толстой
20.12.04	Пушкин	8	53	2
20.12.04	Толстой	4	24	0
20.12.04	Лермонтов	7	35	4
20.12.04	Достоевский	1	23	0
20.12.04	Гоголь	2	14	0
21.12.04	Пушкин	…	…	…
21.12.04	Толстой

Таблица 4Данные по итогам ежедневной работы автосервиса

Дата	Услуга	Стоимость услуги, руб.	Количество по видам оплаты
			Наличными	Кредитная карточка
18.12.04	Шиномонтаж	765	3	1
18.12.04	Балансировка	820	4	2
18.12.04	Развал	510	5	5
18.12.04	Расхождение	430	8	11
18.12.04	Мойка	250	14	8
18.12.04	Окраска	3500	2	1
19.12.04	Шиномонтаж	765	3	1
19.12.04	Балансировка	820	4	2
19.12.04	Развал	510	5	5
19.12.04	Расхождение	430	8	11
19.12.04	Мойка	250	14	8
19.12.04	Окраска	3500	2	1
20.12.04	Шиномонтаж	765	3	1
20.12.04	Балансировка	820	4	2
20.12.04	Развал	510	5	5
20.12.04	Расхождение	430	8	11
20.12.04	Мойка	250	14	8
20.12.04	Окраска	3500	2	1
21.12.04	Шиномонтаж	765	3	1
21.12.04	Балансировка	820	4	2
21.12.04	Развал	510	5	5
21.12.04	Расхождение	430	8	11
21.12.04	Мойка	250	14	8
21.12.04	Окраска	3500	2	1
…	…	…	…	…

1.4. Работа со справочниками1.4.1. Общие сведенияФункция ВПРПозволяет работать со справочной информацией.Общий формат: ВПР(Искомое значение; Таблица; Номер),где Искомое значение - это значение, которое должно быть найдено;Таблица - это адрес справочной таблицы;Номер – это номер столбца в справочной таблице, из которого берется результат поиска.По умолчанию справочная таблица должна быть отсортирована по первому столбцу по возрастанию.Пример.Пусть магазин работает по следующей системе:– если сумма покупок менее 2000 руб., то покупателю не предоставляется никаких скидок;– если сумма покупок от 2000 руб. до 10000 руб., то покупателю предоставляется скидка, равная 5 %;– если сумма покупок больше 10000 руб., то покупателю предоставляется скидка, равная 10 %.И пусть на кассе должна накапливаться следующая информация:

	C	D	E
2	Сумма покупки	Скидка	Сумма к оплате
3	456
4	6543
5	12345
6	345
7

Столбец C заполняется естественно кассиром вручную. А вот столбцы D и E должны рассчитываться.Для этого организуется справочная таблица следующего вида:

	J	K
3	Сумма	Скидка, %
4	0	0
5	2000	0,05
6	10000	0,1

На основе такого расположения данных в D3 вводится формула:=ВПР(C3;$J$4:$K$6;2)а в E3 формула: =C3*(1–D3)которые затем копируются на нужный диапазон столбцов D и E.1.4.2. Варианты заданий

1. 
   «Разносортица»
   

В магазин «Меха» поставляют свои изделия 4 фирмы (А, В, С и D). Поставляемые товары могут быть следующих сортов: высшего, первого, второго и третьего. При этом величины наценки по отношению к базовой цене третьего сорта составляют соответственно – 40%, 25%, и 10%. Однако проверка качества товаров показала, что товары, поставляемые фирмой С, не соответствуют заявленным сортам. Поэтому магазин, беспокоясь о своей торговой марке, понижает сортность товаров фирмы С, т.е.: ее товар высшего сорта продается как первый сорт, товар первого сорта – как второго, товар второго сорта – как третьего, а третий сорт так и идет как третий.Имеются данные о поставках товаров следующего вида:

Товар	Фирма	Заявленный сорт	Количество	Базовая цена единицы товара, руб.
Шапки мужские	В	1	50	3500
Шапки женские	А	Высший	30	4100
Шкурка–сырец (норка)	С	Высший	120	990
Шуба женская	С	1	12	10000
…	…	…	…	…

По данным этой таблицы рассчитать объем планируемой выручки.

2. 
   «Маршрутное такси»
   

В схеме маршрутных такси имеется 7 маршрутов следующей длины:

№ маршрута	1	2	3	4	5	6	7
Длина, км	3	4	2,5	3,5	4	6	4,5

Водители такси получают зарплату в зависимости от выручки за минусом расходов на бензин в размере 20%. Стоимость бензина 10,3 руб./л. Расход бензина – 0,12 л/км. Стоимость проезда – 6 руб. Имеются данные по перевозкам за месяц следующего вида:

День	Маршрут	Количество перевезенных пассажиров	Количество рейсов
1	1	784	6
	2	556	5
	3	980	8
	4	887	8
…	…	…	…

Определить среднюю зарплату водителей.

3. 
   «Гостиница»
   

В гостинице имеется набор дополнительных услуг со следующими параметрами:

Код услуги	Наименование	Стоимость, руб.
01	Бизнес-завтрак	270
02	Бизнес-ленч	350
03	Бизнес-ужин	370
04	Кофе в постель	120
05	Шампанское в номер	590

В зависимости от класса номера на эти услуги устанавливается наценка:

Код номера	Номер	Наценка, %
01	Люкс–экстра	80
02	Люкс	60
03	1–ый класс (одноместный)	40
04	1–ый класс (двухместный)	20
05	2–ый класс (двухместный)	20
06	2–ый класс (четырехместный)	10

Имеются ежедневные данные по количеству оказанных услуг следующего вида: