Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей.
Путевая скорость всех типов а/м считается равной 50 км/час. Здесь <Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт). <Стоимость перевозок> состоит из зарплаты и стоимости аренды. <Зарплата> водителя определяется <Временем в пути>. Кроме того, если автомобиль находится в пути в оба конца больше 12 часов, водителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждом маршруте.
В области D3:D5 подсчитывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м. Для извлечения данных из таблицы тарифов следует использовать функцию ВПР( ).
Контроль А8:A10: Тип а/м ={Зил, Газ, Камаз}.
Форматирование А8:A10: если Пробег>1000км.
Решение.
Создадим документ в MS Excel. Внесем все исходные данные в таблицу.
Рассчитаем число ездок. Оно равно весу груза, деленному на грузоподъемность машины. Грузоподъемность находится в разделе тарифы. Для выбора нужного значения воспользуемся функцией поиска ВПР(). Эта функция ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Полученное значение округлим с помощью функции ОКРВВЕРХ(). Итоговая формула:
Время в пути рассчитаем как расстояние, деленное на скорость (50 км/час) и умноженное на 2. Пробег равен расстоянию, умноженному на число ездок и на 2.
Рассчитаем зарплату водителей за рейс (зарплата за час * время в пути * число ездок). При расчете нужно учитывать продолжительность в оба конца. Если она больше 12 часов, то за каждые 12 часов водитель получает доплату 50 руб.
Для выбора варианта оплаты применим функцию ЕСЛИ. Итоговая формула:
Для ввода типа автомобиля установим контроль вводимых данных с помощью инструмента Проверка (Данные – Проверка).
Пользователю предлагается ввести данные из списка. Источник данных – диапазон с типами в разделе Тарифы. Если пользователь введет значение не из списка, то программа выдаст сообщение об ошибке. При установке курсора на ячейку с типом выводится подсказка.
Построим график по типам автомобилей и объему заказов. Тип графика – круговая диаграмма (поскольку значений всего три).
Выделяем столбцы с заголовками (Тип а/м и Объем заказов), затем нажимаем на панели инструментов кнопку Мастер диаграмм.
В появившемся окне выбираем тип диаграммы – круговая.
Выбираем показ легенды, затем подписи данных:
Затем делаем выбор поместить диаграмму на текущий лист. Результат:
Путевая скорость всех типов а/м считается равной 50 км/час. Здесь <Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт). <Стоимость перевозок> состоит из зарплаты и стоимости аренды. <Зарплата> водителя определяется <Временем в пути>. Кроме того, если автомобиль находится в пути в оба конца больше 12 часов, водителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждом маршруте.
В области D3:D5 подсчитывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м. Для извлечения данных из таблицы тарифов следует использовать функцию ВПР( ).
Контроль А8:A10: Тип а/м ={Зил, Газ, Камаз}.
Форматирование А8:A10: если Пробег>1000км.
Решение.
Создадим документ в MS Excel. Внесем все исходные данные в таблицу.
Рассчитаем число ездок. Оно равно весу груза, деленному на грузоподъемность машины. Грузоподъемность находится в разделе тарифы. Для выбора нужного значения воспользуемся функцией поиска ВПР(). Эта функция ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Полученное значение округлим с помощью функции ОКРВВЕРХ(). Итоговая формула:
=ОКРВВЕРХ(B8/ВПР(A8;$A$2:$C$5;3);1)Чтобы ее можно было копировать без изменений, постоянные ячейки выделены символом «$».
Время в пути рассчитаем как расстояние, деленное на скорость (50 км/час) и умноженное на 2. Пробег равен расстоянию, умноженному на число ездок и на 2.
Рассчитаем зарплату водителей за рейс (зарплата за час * время в пути * число ездок). При расчете нужно учитывать продолжительность в оба конца. Если она больше 12 часов, то за каждые 12 часов водитель получает доплату 50 руб.
Для выбора варианта оплаты применим функцию ЕСЛИ. Итоговая формула:
=ЕСЛИ(E8>12;(ВПР(A8;$A$2:$H$5;8)*E8+E8*50/12)*D8;ВПР(A8;$A$2:$H$5;8)*E8*D8)Стоимость перевозки равна сумме зарплаты и аренды. Аренда считается как произведение цены за час на время в пути на число ездок. Для выбора цены аренды каждого автомобиля используем ВПР().
=G8+ВПР(A8;$A$2:$B$5;2)*D8*E8Рассчитаем объем заказов на каждый автомобиль. Применим функцию СУММЕСЛИ, которая суммирует числа в указанном столбце, если значения другого столбца удовлетворяют определенному критерию (в данном случае типу а/м). Формула:
=СУММЕСЛИ($A$8:$A$12;A3;$E$8:$E$12)Применим условное форматирование к ячейкам с пробегом. Если значение больше 1000, то цвет текста – красный:
Для ввода типа автомобиля установим контроль вводимых данных с помощью инструмента Проверка (Данные – Проверка).
Пользователю предлагается ввести данные из списка. Источник данных – диапазон с типами в разделе Тарифы. Если пользователь введет значение не из списка, то программа выдаст сообщение об ошибке. При установке курсора на ячейку с типом выводится подсказка.
Построим график по типам автомобилей и объему заказов. Тип графика – круговая диаграмма (поскольку значений всего три).
Выделяем столбцы с заголовками (Тип а/м и Объем заказов), затем нажимаем на панели инструментов кнопку Мастер диаграмм.
В появившемся окне выбираем тип диаграммы – круговая.
Выбираем показ легенды, затем подписи данных:
Затем делаем выбор поместить диаграмму на текущий лист. Результат: