Методические указания к зданию. < Цена билета > = ВПР ( Класс полета; $B$2:$С$5; 2 строка ) * (< Количество человек > - < Дети >) + ВПР ( Класс полета; $B$2:$С$5; 2 строка

Решение:

< Цена билета > = ВПР ( Класс полета; $B$2:$С$5; 2 строка ) * (< Количество человек > - < Дети >) + ВПР ( Класс полета; $B$2:$С$5; 2 строка ) / 2

т.е. D8 = ВПР ( C8; $B$2:$C$5; 2 ) * (A8 – B8) + ВПР ( C8; $B$2:$C$5; 2 ) / 2

Задание 12. Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей. Путевая скорость всех типов а/м считается равной 50 км/час. Здесь <Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт), <Стоимость перевозок> состоит из зарплаты и стоимости арен­ды. <3арплата> водителя определяется <Временем в пути>. Кроме то­го, если автомобиль находится в пути в оба конца больше 12 часов, во­дителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждом маршруте. В области D3:D5 подсчитывается число машино-часов (время в пути), необходимых для обслу­живания заявок на перевозки разными типами а/м. Для извлечения данных их таблицы тарифов использовать функцию ВПР.

	A	B	C	D	E	F	G	H	I
	тарифы
	Тип автомобиля	Цена часа аренды	Грузоподъемность	Объем заказов				Цена часа работы водителя
	Зил	50р.
	Газ	30р.
	Камаз	50р.
	Скорость		км/ч
	АВТОПЕРЕВОЗКИ
	Тип а/м	Вес груза	Расстояние	Число ездок	Время в пути	Пробег	Зарплата	Стоимость перевозок	Командировочные
	Зил	ХХт	ХХкм	?	?	?	?	?	?
…
	Всего	?	?		?	?	?	?	?

Задание 13. Выполнить расчеты с клиентами на туристическое обслуживание их семей, включающее перелет и проживание в месте отдыха (найти <цену путевки>).

	A	B	C	D	E	F	G	H	I	J
	ТАРИФЫ ОБСЛУЖИВАНИЯ
	Отель	Полет
	Класс	***	****	*****	Класс
	Цена	$200	$280	$400	Цена	$200	$250	$300
	ТУРИЗМ
	Клиент	Человек в семье	Число детей	Класс полета	Класс отеля	Скидки	Цена полета	Цена проживания	Всего	Цена путевки
	Петров				*****	$40	$900	$1 800	$2 700	$2 660
…		х	х	х	х	?	?	?	?	?
	Всего	?	?				?	?	?	?
	Пятизвездочных путевок:	?

О каждой семье известны: имя покупателя путевки, общее число членов семьи и число детей в ней, а также желаемый класс салона в самолете (1-3) и класс отеля (от трех до пяти звезд). Стоимость авиа­билета для взрослого пассажира задана в таблице тарифов на перелет (детский билет в половину дешевле). Стоимость проживания взрослого туриста в период отдыха задана в таблице тарифов на проживание в отеле. Один ребенок в семье с двумя взрослыми проживает бесплатно. Во всех других случаях его проживание в половину дешевле. На семью от четырех взрослых делается скидка в 10% стоимости проживания (но только в пятизвездочном отеле). Для розыска тарифов использовать функцию ГПР. В ячейке E11 показать число путевок, с проживанием в пятизвездочном отеле.

Задание 14. Создать таблицу расчетов с клиентами отеля, о которых известны даты въезда, съезда и класс занимаемого номера от (от Люкс до 3-го). Оплата за номер определяется числом дней проживания, умноженным на тариф соответствующего класса (использовать функцию ГПР. Кроме того, имеются <Скидки/доплаты>. Если кли­ент проживает в номере больше 10 дней, ему делается скидка по опла­те в 15% за каждый день свыше десятого. Если номер клиентом был ранее предварительно забронирован, он доплачивает за бронь сумму в размере платы за один день проживания. <Общая сумма> складывается из <Оплаты> и <Доплаты/Скидки>. В области В4:Е4 формируется сводка по наполнению номеров. Здесь подсчитывается число занятых номеров соответствующего класса.

	A	B	C	D	E	F	G	H
	Тарифы
	Класс	Люкс
	Плата
	Число клиентов	?	?	?	?
	Расчеты с клиентами отеля
	Клиент	Бронь	Проживание	Класс номера	Оплата	Доплаты /скидки	Общая сумма
	с:	по:
	Иванов	+	1 фев	9 фев	х	?	?	?
	Петров		3 фев	4 фев	х	?	?	?
	Сидоров	+	5 фев	17 фев	х	?	?	?
	ххх		20 фев	3 мар	х	?	?	?
	Всего					?

Задание 15. Она определяется числом <Изготовленных им деталей>, умножен­ным на <Стоимость одной детали>. Заработок также зависит от <Разряда> рабочего. Он увеличивается на соответствующий <Разрядный коэффициент>. Кроме того, если рабочий произвел более 30 деталей ему начисляется премия в размере 50% от стоимости каждой детали начиная с 31-й. Зарплата рабочего может быть и уменьшена в случае, если им было изготовлено свыше трех бракованных деталей - из заработанных сумм вычитается штраф в размере 50 руб. Если бракованных деталей до пяти то в колонке <Брак> выводится восклицательный знак, если бракованных деталей больше пяти, вырабатывается сообщение "Брак", и если больше семи ''Аврал". В ячейке F13 подсчитывается число рабочих, допустивших брак в количестве от пяти деталей. Нужные разрядные коэффициенты извлекаются из таблицы функцией ВПР. В области F2:F5 подсчитать число рабочих, имеющих соответствующий разряд.

	A	B	C	D	E	F	G	H
	Стоим. деталей	10р.		Разрядный коэф.	Число рабочих
						1,1
						1,2
						1,4
	ЗАРПЛАТА
	ФИО	Разряд	Деталей	Зарабо тано			Брак
	Изготовлено, шт.	Брака, шт.	Премия	Штраф
	Иванов				?	?	?	?
…	xxxx	x	x	х	?	?	?	?
	Всего		?	?	?
	Бракоделы:	?

Задание 16.Вычислить размер заработка продавцов фирмы. Зapплата работника состоит из двух частей - фиксированного небольшого <Оклада>, определяемого <Разрядом>, и <Премии>, зависящей от фак­тического объема продаж (<Продано>). Если объем продаж меньше <Нормы>, она составляет 10% от <Продаж>, если больше - 20%, если больше в два раза, добавляется еще 1000 руб. В ячейке С8 вычислить количество человек, продавших товаров более чем на 50000 руб. Для определения оклада следует воспользоваться функцией ВПР.

[В облас­ти С8:С10 показать фамилии продавцов, занявших по объему продаж первые три места и суммы их продаж].

	A	B	C	D	E	F	G	H
	Норма:	100р.					разряд	оклад
	ЗАРПЛАТА ПРОДАВЦОВ			100р.
	ФИО	разряд	продано	премия	заработок			200р.
	Иванов	х	ххх р.	?	?			300р.
…								400р.
	Всего		? р.	?	?			500р.
	1 место	?
	2 место	?
	3 место	?

Задание17. Определить стоимость обслуживания туристических экскурсий на маршрутах А, Б и т.д. О каждом маршруте известна стоимость собственно экскурсии и стоимость транспортных расходов. Известна также емкость автобуса. В самой таблице фиксируется же­лаемый маршрут и число заявок (человек) на обслуживание. Мини­мальное число автобусов определяется как целая часть от <Число заявок>/<Вместимость автобуса>. Фирма обслуживает не всех туристов, а только такое их максимальное количество, чтобы не оказалось ни од­ного автобуса, заполненного менее чем на 30%. Фактическое число оп­ределяется в колонке <Выделено автобусов>. Для этого нужно выяс­нить, сколько туристов еще не размещено в автобусы. Если их оказа­лось больше чем 30% емкости автобуса, значит, <выделено автобусов> будет на единицу больше минимально необходимого их числа. В про­тивном случае, будет <выделен> этот минимум. <Стоимость> обслу­живания определяется произведением числа выделенных автобусов на сумму экскурсионного и транспортного обслуживания маршрута. [В ко­лонке <Примечание> следует показать число пустых мест в автобусе или число отклоненных заявок (что есть). В клетке Е13 показать число об­ращений для обслуживания более 1000 заявок]. Для выявления стоимости маршрута из таблицы тарифов следует воспользоваться функцией ГПР.

	A	B	C	D	E	F
	Тарифы на маршруты
	Маршрут	А	Б	В	Г
	Экскурсия	500р.	600р.	900р.	1 000р.
	Транспорт	350р.	400р.	600р.	900р.
	В автобусе:		человек
	ОБСЛУЖИВАНИЕ ТУРИСТОВ
	Номер маршрута	Число заявок	Автобусов	Стоимость обслуживания	Примечание
	миним.	выделено
	A	124чел	?	?	?	?
…
	Всего	? чел
	Крупные заявки:	?