Методические указания к зданию. < Цена билета > = ВПР ( Класс полета; $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чел ? ? ? ?
…            
Всего ? чел        
Крупные заявки: ?