close

Вход

Забыли?

вход по аккаунту

?

Грузовые перевозки (лабораторные работы)

код для вставкиСкачать
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ
ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«ВОРОНЕЖСКИЙ ГОСУДАРСТВЕННЫЙ ЛЕСОТЕХНИЧЕСКИЙ
УНИВЕРСИТЕТ ИМЕНИ Г. Ф. МОРОЗОВА»
Кафедра организации перевозок и безопасности движения
Грузовые перевозки
методические указания к лабораторным работам
для студентов по направлению подготовки
23.03.01 – Технология транспортных процессов
Воронеж 2016
УДК 656.13.07
Сподарев, Р.А. Грузовые перевозки [Электронный ресурс] : методические
указания к лабораторным работам для студентов по направлению подготовки
23.03.01 – Технология транспортных процессов / Р.А. Сподарев, В.П. Белокуров,
А.Ю. Артемов, Д.В. Лихачев ; М-во образования и науки РФ, ФГБОУ ВО
«ВГЛТУ». – Воронеж, 2016. – ЭБС ВГЛТУ.
Печатается по решению учебно-методического совета
ФГБОУ ВО «ВГЛТУ» (протокол № __ от __ января 20__ г.)
Рецензент заведующий кафедрой электротехники и автоматики
ФГБОУ ВО «Воронежский государственный аграрный
университет имени императора Петра 1», док. техн. наук,
профессор Д.Н. Афоничев.
Лабораторная работа 1
Автоколонна, состоящая из Acc  10 автомобилей ЗИЛ-130 с прицепами
общей грузоподъемностью q  10 т, находилась в крестьянском хозяйстве в
течении Д к  25 дней на уборке свеклы. Рассчитать объем перевозок Q и
грузооборот автоколонны P за это время, если известны следующие показатели
ее работы: время на маршруте за день Tм  11,25 ч, средняя длинна груженной
ездки l ге  45 км, средняя техническая скорость Vт  30 км/ч, среднее время
выполнения погрузочно-разгрузочных операций t п / р  0,66 ч, коэффициент
использования пробега за одну ездку  е  0,5 , статический коэффициент
использования грузоподъемности g ст  1 , коэффициент выпуска на линию
 в  0,85 .
Аналитическое решение.
Для расчета производительности определяются следующие параметры:
1.
Время одной ездки автомобиля, ч
te 
2.
ne 
l ег
 tп / р .
Vт   e
Количество ездок за рабочий день
Tм
.
te
Объем перевозок автоколонны, т
Q  q   ст  ne  Acc  Д к в .
4.
Грузооборот, ткм
Р  Q  lег .
Решение в EXCEL
1.
Запустите EXCEL и создайте новую рабочую книгу, которую
сохраните под именем Задача 1.
2.
Занесите на рабочий лист Лист 1 таблицу с исходными данными, как
показано на рисунке 1.
3.
Рисунок 1.
3.
Аналогично занесите на лист все данные из таблицы Расчеты, кроме
данных в столбце Е.
4.
В ячейку Е 20 занесите формулу =E12/(E13*E15)+E14, она
соответствует формуле 1, но вместо названий показателей используются адреса
ячеек EXCEL, содержащих значение этих показателей.
5.
В ячейки Е 21, Е 22, Е 23 занесите формулы =E11/E20;
=E10*E15*E21*E9*E8*E16; =E22*E12 соответственно рисунок 2.
Рисунок 2.
6.
Выделите диапазон В 7: Е 17, для этого переведите курсор EXCEL на
ячейку В7 и, удерживая левую клавишу мышки, переместите до ячейки Е 17.
Затем вызовите команду Формат-Автоформат и выберите в диалоговом окне
требуемый способ форматирования.
7.
Отдельно выполните форматирование таблицы с расчетами также с
помощью автоформата.
8.
Сохраните созданную рабочую книгу.
Лабораторная работа 2
АТП обслуживает торговую сеть города в течении Дк =365 дней.
Среднесписочное число автомобилей Асс = 40, коэффициент технической
готовности ат=0,84, коэффициент выпуска автомобилей на линию ав =0,78.
Сколько автомобиле-дней подвижной состав находится в ремонте АДр и
эксплуатации АДэ.
Аналитическое решение
Для выполнения вычислений используется формулы
1.
Автомобиле-дни в ремонта
АД р  (1   т )  Д к  Асс
2.
Автомобиле-дни в эксплуатации
АД э   в  Д к  Асс
Решение в EXCEL
1.
Создайте рабочую книгу, сохраните ее на диске под именем Задача 2 и
занесите в нее исходные данные в виде таблице.
2.
Подготовьте таблицу для проведения расчетов, занесите в нее всю
информацию, кроме формул.
Рисунок 3.
3.
Сделайте активной ячейку D7, в поле адреса сотрите значение D7 и
напечатайте Дк ( буквами русского алфавита) рис.4.
Рисунок 4.
4.
Аналогично присвойте ячейке D8 имя Аи, ячейке D10, имя АЛЬФАт,
ячейке D9, имя АЛЬФАв.
5.
В ячейку D13 занесите формулу =(1-АЛЬФАт)*Дк*Аи. Обратите
внимание, что эта формула почти полностью повторяет математическую запись
формулы. Единственным отличаем является то, что имена ячеек не могут
содержать греческих символов.
6.
В ячейке D14 занесите формулу =АЛЬФАв*Дк*Аи.
Рисунок 5.
7.
Окончательно
отформатируйте
автоформата и сохраните рабочую тетрадь.
таблицу
с
использованием
Задачи для самостоятельного решения.
1.
Среднесписочное число автомобилей парка Аи = 90 единиц,
коэффициент технической готовности ат = 0,75. В результате более качественного
выполнения технического обслуживания и ремонта ат доведен до 0,8. На сколько
единиц увеличится число годных к эксплуатации автомобилей А?
2.
Часовая производительность для автомобиля тягача КамАЗ с
самосвальным кузовом и самосвальным прицепом обще грузоподъемностью q
=14 т, составляет Uр.ч = 10 т/ч. Определить месячную производительность
автомобиля, если суточный пробег автомобиля Lсут =144 км, эксплуатационная
скорость Vэ = 18 км/ч, коэффициент выпуска автомобилей αв =0,75.
Распространение формул
Особенно эффективно использовать в EXCEL при выполнении расчетов по
однотипным формулам для различных наборов исходных данных. В таких
случаях достаточно ввести исходную формулу на рабочий лист один раз, а затем
скопировать ее во все необходимые ячейки. При этом во всех скопированных
формулах адреса ячеек будут автоматически изменены и для расчета будут
использованы необходимые данные.
Подобная операция является одной из наиболее часто используемых,
поэтому она получила специальное название – распространение формулы.
Для выполнения распространения формулы следует навести курсор мыши
на правый нижний угол ячейки (курсор мыши примет форму «тонкого крестика»)
и, удерживая левую клавишу мыши, перетащить ее содержимое во все
необходимые ячейки.
Изменение адресов происходит следующим образом: если формула
распространяется в следующую строку, то номера всех строк, содержащихся в
формуле, будут увеличены на один. Аналогичное правило действует и для
столбцов, только изменяются имена столбцов. Например, если в ячейке С5
содержится формула =В4*А3, то при ее распространении в ячейку С6 она примет
вид =В4*А4, а при распространении в ячейку Е4 вид =D3*С2.
В некоторых случаях необходимо, чтобы в EXCEL не изменял адреса ячеек
при распространении формулы, например, если аргументы функции имеют
постоянное значение. Существует несколько способов создания формул с
неизменными аргументами.
Во-первых, можно прямо в формуле использовать необходимые значения, а
не ссылаться на ячейки, их содержащие. Например, для формулы =29*В7
значение 29 не изменится при любых распространениях формулы.
Другой способ основан на использовании абсолютных ссылок. Абсолютная
ссылка – это особая форма записи адреса ячейки. Адреса ячеек, записанные с
использованием абсолютных ссылок, не изменяются
при операциях
распространения и копирования формулы. В остальном абсолютные ссылки
ничем не отличаются от обычных ссылок.
Для преобразования ссылки в абсолютную необходимо добавить в нее знаки
$. Например, В7 – обычная ссылка, $В$7 – абсолютная. Эти знаки можно либо
напечатать, либо, используя клавишу F4, автоматически вставить их (выделите
нужную ссылку и нажмите F4).
Существуют также смешанные ссылки, которые являются абсолютными
или по строкам , или по столбцам. Они имеют следующий вид: $D9 – абсолютная
ссылка по столбцам, С$12 – абсолютная ссылка по строкам.
Последний способ сохранения неизменных аргументов в формулах связан с
использованием именованных ячеек. Так как именованной является определенная
ячейка, то при выполнении любых операций над ячейками, содержащими
формулы со ссылками на именованные ячейки, никаких изменений в формуле не
происходит.
Лабораторная работа 3
В городе имеется 4 пассажирских автотранспортных предприятия,
показатели работы которых приведены в таблице. Определить общий пробег Lобщ
автобусов для каждого из предприятий за месяц.
Наименование показателя
Списочное число автобусов Асс
Дни календарные Дк
Коэффициент выпуска автобусов на линию αв
Предприятие
АТП - 1
АТП - 2
40
26
30
30
АТП - 3
29
30
АТП - 4
58
30
0,86
0,81
0,9
0,92
432
476
376
384
Суточный пробег одного автобуса Lсут, км
Аналитическое решение
Для решения задачи используется формула
Lоб   в  Асс  Д к  Lсут .
Расчеты выполняются для каждого АТП.
Решение в EXCEL
1. Создайте новую рабочую книгу и сохраните ее под именем Задача 3.
2. Занесите исходную таблицу на рабочий лист рисунок 6.
Рисунок 6.
3. Занесите в ячейку D12 формулу =D6*D7*D8*D9.
4.
Наведите указатель мыши на правый нижней угол ячейки D12, чтобы
он принял вид маленького тонкого крестика. Нажмите левую кнопку мыши и,
удерживая ее, распространите формулу в диапазоне D12:G12. Обратите
внимание, как EXCEL преобразовал адреса ячеек в распространенных формулах
и выполнил расчеты рисунок 7.
Рисунок 7.
5.
Оформите таблицы и сохраните рабочую книгу.
Лабораторная работа 4
Бригада в составе Асс =15 автомобилей-самосвалов МАЗ-503Б
грузоподъемностью q = 7 т осуществляет перевозку щебня на строительство
автомобильной дороги. Показатели работы бригады: среднее время погрузочноразгрузочных
операций
tп/р
=0,2
ч,
коэффициент
использования
грузоподъемности g = 1, коэффициент использования пробега β = 0,5.
Какой объем перевозок Q и грузооборот Р по дням выполняла бригада, если
остальные показатели составляют следующие величины:
Показатели
Дни
Средняя длина ездки с грузом lег, км
Техническая скорость Vт, км/ч
Время в наряде Тн, ч
1
2
30
11,2
2
3
27
12,3
3
3,5
31
13,2
4
4
32
12,3
5
4,2
28
12,1
6
4,7
28
12,5
7
5,2
30
11,9
Аналитическое решение
1. Объем перевозок для бригады автомобиле за рабочий день, т
Q
    Vт  q
.
l ег    Vт  t п / р
2. Грузооборот, выполняемый бригадой автомобилей за день, ткм
W  Q  lег
Решение в EXCEL с использованием абсолютных ссылок
1.
Создайте рабочую книгу, занесите в нее таблицу с исходными
данными. Вверху таблицы разместите показатели, которые имеют постоянные
значения Асс, q, tп/р, а, b, g, а внизу – показатели, которые изменяются по дням lег,
Vт, Tн.
2.
Для изменяемых показателей занесите все значения по дням.
3.
В
ячейку
занесите
D16
формулу
=$D$5*$D$6*E13*$D$8*$D$9*$D$10*E12/(E11+E12*$D$10*$D$7).
Заметьте, что для показателей, которые имеют постоянные значения, ссылка абсолютная, а на изменяемые показатели – обычная (рисунок 8,9).
Рисунок 8.
Рисунок 9.
4. Распространите формулу в диапазоне D16 : J16 и обратите внимание, как
произошло изменение адресов в формуле.
5. В ячейку D17 занесите формулу =D16*E11 и распространите ее в
диапазоне D17 : J17.
6. Отформатируйте таблицы и сохраните рабочую книгу рисунок 10.
Рисунок 10.
Решение в EXCEL с использованием именованных ячеек
1. Выполните 1 и 2 пункты предыдущего решения.
2. Присвойте имена тем ячейкам, которые хранят неизменяемые данные.
D5 – Асс, D6 – q, D7 -tпр , D8 – g, D9 – а, D10 – b.
3. В ячейку D 22 занесите формулу =Асс*q*E13*g*a*b*E12/
(E11+E12*b*tпр) и распространите ее в диапазоне D22: J22.
4. В ячейку D 23 занесите формулу =D22*E11 и распространите ее в
диапазоне D23: J23 (рисунок 11).
Рисунок 11.
5. Сравните результаты этого и предыдущего решения.
Задача для самостоятельного решения
Автомобили самосвалы ЗИЛ грузоподъемностью q=8 т в течении недели
работали на маятниковом маршруте с обратным холостым пробегом. Нулевой
пробег за день l0 = 10 км, длина ездки с грузом lег = 6 км, техническая скорость
Vт=24 км/ч. Каждый день недели на линии работало различное число
автомобилей:
Показатели
Число автомобилей, Асс
Время в наряде Тн, ч
1
8
10
Дни
2
12
12,2
3
14
11,7
4
10
11,4
5
6
10,9
6
12
14,2
7
9
17
Определить объем перевозок по каждому дню недели.
Использование функций
Помимо основных арифметических операций EXCEL позволяет
использовать в формулах функции. Функциями в EXCEL называют объединение
нескольких вычислительных операций для решения определенной задачи.
EXCEL представляет большое число встроенных функций, позволяющих
выполнять как простые расчеты, например суммировать, так и сложные,
например вычислять плотность нормального распределения.
Функция может быть записана в ячейку вручную или вставлена с помощью
Мастера функций. Использование Мастера функций значительно облегчает
работу с функциями, так как позволяет выбрать нужную функцию из списка и
задать ее аргументы. При ручном наборе функции следует обратить внимание на
правильность набора имени. Если имя функции будет набрано неправильно, то
возникнет ошибка #ИМЯ.
В качестве аргумента функции обычно выступает одна ячейка или диапазон
ячеек.
Использование диапазонов позволяет значительно сократить и упростить
запись формул. Для указания диапазонов используется операция объединения,
записываемая знаком «:». Например, если задан диапазон В7:В11, то значит, в
операции будет использованы ячейки В7, В8, В9, В10, В11.
Аналогично можно задавать и прямоугольные диапазоны, определяемые
адресами левого верхнего и правого нижнего углов.
EXCEL представляет пользователю более 200 встроенных функций,
используемых для различных расчетов. Каждая функция принимает различное
количество аргументов. Аргументы, в свою очередь, могут иметь различный вид.
Поэтому достаточно сложно вручную вводить функции в ячейки без ошибок.
Использование Мастера функций значительно облегчает ввод функции и
позволяет быстро найти необходимую функцию и задать ее аргументы.
Для удобства использования в Мастере функций все функции разбиты на
категории. Характеризующие принадлежность функции к определенному виду
вычислений. Выделено двенадцать категорий по видам расчетов: Финансовые,
Дата время, Математические, Статистические, Ссылки и массивы, Работа с
базой данных, Текстовые, Логические, Проверка свойств и значений,
Инженерные, Информационные, Мат. И тригонометрия. Дополнительно
имеются еще две категории общего характера: Полный алфавитный перечень и 10
недавно использовавшихся. Внутри категории все функции отсортированы по
алфавиту.
Для запуска Мастера функций выделите ячейку, в которую необходимо
занести функцию, и нажмите кнопку Мастер функций на панели инструментов.
Работа мастера разбита на два шага. Первым шагом производится выбор
категории функции и выбор нужной функции из списка. На втором шаге в
специальном диалоговом окне задают аргументы функции.
Каждый аргумент задается в отдельном поле. В нижней части диалогового
окна приводится краткое описание аргумента.
Рисунок 12.
В дальнейшем введенную функцию можно редактировать вручную или с
помощью мастера функций.
Для изменения порядка вычисления в зависимости от выполнения какихлибо условий или соотношений между данными рабочего листа используются
логические функции.
Эти функции сгруппированы в мастере функций в категорию Логические
функции. Ряд дополнительных логических функций содержится в разделах
Математические, Информационные и Проверка свойств и значений.
Функции из категории Логические функции
Функции этой категории позволяют выполнять проверку условий и
соответственно изменять порядок вычислений формул.
Функция ЕСЛИ является наиболее часто используемой логической
функцией. Она возвращает в результате одно из двух выражений в зависимости
от проверки условий. Эта функция имеет следующий синтаксис:
ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь),
где
лог_выражение – логическое значение, принимающее два значения
ИСТИНА и ЛОЖЬ.
значение_если_истина – значение, которое возвращается, если
лог_выражение равно ИСТИНА.
значение_если_ложь
–
значение,
которое
возвращается,
если
лог_выражение равно ЛОЖЬ.
Функция ЕСЛИ применяется в тех случаях, когда необходимо в
зависимости от содержимого ячеек выполнить расчет по различным функциям
или использовать различные значения.
Функция И используется для объединения нескольких логических условий.
Если все объединяемые логические условия имеют значение ИСТИНА, то
функция возвращает ИСТИНА. Если все условия имеют значение ЛОЖЬ, то
ИЛИ возвращает ЛОЖЬ.
Функции И и ИЛИ используются совместно с функцией ЕСЛИ для
проверки сложных условий.
Функции из категории Математические
Логические функции этой категории позволяют выполнять некоторые
математические операции не над всеми данными из заданного диапазона, а только
над теми, которые удовлетворяют условию.
Функция СУММЕСЛИ выполняет суммирование только тех ячеек,
которые удовлетворяют условию. Синтаксис функции СУММЕСЛИ:
СУММЕСЛИ (проверяемый_диапазон; условие; фактический_диапазон) где
проверяемый_диапазон – ячейки, для которых проверяется условие;
условие – логическое выражение, которому должны удовлетворять ячейки
из диапазона;
фактический_диапазон – ячейки, для которых будет выполняться
суммирование, если соответствующая им ячейка из проверяемого диапазона
удовлетворяет условию.
Аналогичный синтаксис имеет функция СЧЕТЕСЛИ. Эта функция не
суммирует, а подсчитывает количество ячеек, удовлетворяющих условию.
Функции из категории Проверка свойств и значений
В отличие от функции из категории Логические функции, функции этой
категории проверяют на соответствие типа, а не значение результата. Наиболее
часто используются следующие функции этой категории:
ЕОШИБКА – возвращает ИСТИНА, если заданная ячейка содержит
ошибку;
ЕТЕКСТ – возвращает ИСТИНА, если заданная ячейка содержит текст;
ЕЧИСЛО – возвращает ИСТИНА, если заданная ячейка содержит число.
Функции раздела Информационные
Раздел содержит две функции: ЕНЕЧЕТ и ЕЧЕТ. Эти функции
возвращают значение ИСТИНА, если заданное значение является соответственно
нечетным или четным числом.
Лабораторная работа 5
Коков общий пробег автомобиля КамАЗ 5511 за пять дней и
среднесуточный пробег автомобиля по зафиксированным в путевом листе
показаниям спидометра?
Пробег
При выезде, км
При возврате, км
1
14850
15050
2
15050
15230
3
15230
15500
4
15500
15690
5
15690
15880
Аналитическое решение
Вначале вычисляется ежесуточный пробег автомобиля как разность
показаний спидометра при выезде и возврате.
Затем, просуммировав ежесуточный пробег, можно найти общий пробег, а
разделив общий пробег на дни работы, - среднесуточный пробег.
Решение в EXCEL
1. Создайте новую рабочую книгу, сохраните ее под именем Задача 5,
занесите на рабочий лист таблицу с исходными данными рисунок 13.
Рисунок 13
2. В ячейку В8 занесите формулу =B5-B4, позволяющую рассчитать пробег
за сутки, и распространите ее в ячейки В8:F8.
3. Первый способ расчета общего пробега состоит в простом суммировании
ячеек диапазона В8:F8. Занесите в В9 формулу =B8+C8+D8+E8+F8.
4. Второй способ заключается в использовании специальной функции
СУММ и диапазона ячеек. Занесите в В10 формулу =СУММ(B8:F8).
5. Среднесуточный пробег можно вычислить по формуле =B9/5. Занесите
эту формулу в ячейку В9.
6. Более правильным для расчета
среднесуточного пробега будет
использование специальной функции СРЗНАЧ, возвращающей среднее значение
для выбранных ячеек. Занесите в ячейку В12 формулу =СРЗНАЧ(B8:F8).
7. Во многих случаях существует более простой способ выполнения
расчетов, так как EXCEL предлагает большое число специализированных
функций. Так можно вычислить общий пробег, не делая промежуточных расчетов
ежедневных пробегов. Для этого следует использовать специальную формулу
массива {СУММ}, которая возвращает сумму диапазонов ячеек и автоматически
выполняет промежуточные действия.
Рисунок 14.
Лабораторная работа 6
В результате подсчета числа вошедших и вышедших пассажиров по одному
рейсу для одного автобусного маршрута были получены следующие данные:
Название перегона
Перегон 1-2
Перегон 2-3
Перегон 3-4
Перегон 4-5
Перегон 5-6
Перегон 6-7
Конечная
Вошло
14
5
7
8
2
1
Вышло
0
14
5
4
3
9
2
Длина
перегона
0,5
0,7
0,3
0,8
0,4
0,5
-
Определить по этим данным среднюю длину поездки пассажира.
Аналитическое решение
Расчет средней длины поездки пассажира выполняется по формуле
l ср 
Р
Q
где Р- пассажирооборот;
Q – число перевезенных пассажиров.
Решение в EXCEL
1. Занесите исходные данные на рабочий лист (рисунок 14).
Рисунок 14.
2. Выполните расчет числа пассажиров, перевезенных по перегонам, для
этого занесите в ячейку Е6 формулу =E5+B6-C6 и распространение ее в
диапазоне Е7:Е12.
3. Выполните расчет пассажирооборота по участкам, занесите в ячейку F6
формулу =D6*E6 и распространите ее в диапазон F7:F11.
4. В ячейку В13 зенесите
с помощью мастера функций формулу
=СУММ(B6:B11) и распространите ее в ячейку С13. Эти формулы рассчитывают
количество перевезенных пассажиров, суммируя число вошедших и вышедших
соответственно.
5. Для расчета суммарного пассажирооборота по маршруту занесите с
помощью мастера функций в ячейку F12 формулу =СУММ(F6:F11).
6. Расчет средней длины поездки пассажира выполняется по формуле
=F12/B13, которая заносится в ячейку F13 (рисунок 15).
Рисунок 15.
7. Более простым способом расчета средней длины поездки пассажира,
исключающим выполнение промежуточных расчетов, является использование
формулы
=СУММПРОИЗВ(D6:D11;E6:E11)/CУММ(В6:В11), которая с
помощью мастера функций заносится в ячейку F15.
Лабораторная работа 7
Автотранспортное предприятие выполняет грузовые перевозки для разовых
клиентов. Для расчета стоимости перевозки используются различные виды
тарифов на перевозки.
Вид тарифа зависит от объема заказанной услуги и от пробега автомобиля.
Если грузооборот Р менее 1300 ткм, то используется тариф на тонну, если более,
то тариф за километр. В таблице приведены значения тарифов для различных
значений показателей:
Название тарифа
1 вид тарифа
2 вид тарифа
3 вид тарифа
Договорной тариф
Тариф за тонну
Тариф за километр
Объем перевозок, т Тариф, Руб/т Пробег, км Тариф, Руб/км
от 0
40
от 0
25
от 20
30
от 30
20
от 100
20
от 150
15
от 500
от 1000
-
Рассчитать стоимость перевозки грузов для следующих клиентов
Показатель
Объем перевозок (Q), т
Грузоподъемность автомобиля (q), т
Средняя длина едки(lег), км
Коэффициент использования пробега(b)
Коэффициент использования грузоподъемности (g)
Клиент
1
200
5
8
0,5
0,9
Клиент
2
115
10
14
0,65
0,7
Клиент
3
300
8
4
0,5
0,8
Клиент
4
120
6
8
0,61
0,6
Аналитическое решение
1. Грузооборот и общий пробег автомобилей рассчитывается по формулам:
P  Q  lег ;
Q  l ег
;
q   
S  Tk  Lo .
Lo 
В зависимости от величины грузооборота выбирается вид тарифа и
используется одна из формул расчета стоимости перевозки.
Себестоимость перевозок при тарифе за один километр
S  Tk  Lo
где Тк – тариф за один километр, руб/км.
Себестоимость перевозки при тарифе за одну тонну,
S  Tт  Q
где Тm – тариф за тонну, руб/т.
Решение в EXCEL
1. Занесите на рабочий лист исходную таблицу тарифов (рис. 16).
Рисунок 16.
2. Показатели по клиентам занесите в том же виде, в коком они
представлены в условиях задачи (рис. 17).
Рисунок 17.
3. Используя исходные формулы, выполните расчет грузооборота и общего
пробега по каждому клиенту. Занесите в ячейку В22 формулу =B15*B17 и
распространите ее на в В22:С22. В ячейку В23 занесите формулу
=B15*B17/(B16*B19*B19) и распространите ее в С23:Е23.
4. Для определения вида используемого тарифа необходимо проверить
условие: если грузооборот меньше 1300, то используется тариф за тонну. Если
больше – за километр.
5. Выделите ячейку В26 и запустите Мастер функций (кнопкой на панели
инструментов). Выберите категорию Логические и функцию ЕСЛИ. На
следующем шаге укажите параметры: Условие В22>$D$12, Значение если истина
– 2, Значение если ложь – 1. В результате в ячейке В26 будет формула
=ЕСЛИ(B22>$D$12;2;1). Распространите ее в диапазон С26. Результат расчета по
формуле показывает вид используемого тарифа. Если результат равен 1, то
используется тариф за тонн, если 2, то тариф за километр (рисунок 18).
Рисунок 18.
6. Произведите построение таблицы, в которой рассчитывается стоимость
перевозки по каждому тарифу. Для этого, используя Мастер функций, занесите в
ячейку В29 =ЕСЛИ(И(B$15>$B6;B$15<=$B7);$C6*B$15;0). Распространите
формулу в ячейки В29:Е31. В ячейку В32 занесите формулу
=ЕСЛИ(И(B$23>$D6;B$23<=$D7);B$23*$E6;0) и распространите ее в диапазон
В32:Е32 (рисунок 19).
Рисунок 19.
7. В заключении выполните расчет платы за перевозку. Используя Мастер
функций,
занесите
в
ячейку
В37
формулу
=ЕСЛИ(B26=1;СУММ(B29:B31);СУММ(B32:B34)). Распространите ее в
С37:Е37. По пулученной формуле вычсляетя общая сумма оплаты за перевозки в
зависимости от вида тарифа. Результаты расчетов помещаются в диапазон
В37:Е37.
Ссылки и автоподстановки
Существует большой класс автотранспортных задач, в которых исходные
данные задаются набором значений в виде таблицы или списка, например список
автомобилей или список грузов. При решении подобных задач необходимо
выбирать значения из списка и выполнять над ними различные действия.
В предыдущей задаче было показано, как можно использовать функции
условий для выбора данных с таблиц. Но в большинстве случаев формулы с
условиями получаются громоздкими и неудобными для использования.
EXCEL предлагает целый класс специальных функций для обработки
таблиц и списков и выбора из них нужных значении. Эти функции объединены в
разделе Функции ссылки и автоподстановки.
Среди них выделяются следующие группы функций.
Функции для выбора значений по индексу
Функция ВЫБОР возвращает значение по его номеру в списке
ВЫБОР(Номер _ индекса; Значение 1, Значение 2….),
где Номер _ индекса – номер выбираемого из списка значения;
Значение 1, Значение 2…. – список значений или ссылки на список
значений.
Функция ИНДЕКС возвращает значение из ячейки, находящейся на
пересечении указанной строки и столбца в заданном диапазоне.
ИНДЕКС (Ссылка_На_Диапазон; Номер_Строки; Номер_Столбца)
где Ссылка_На_Диапазон – адрес диапазона ячеек, в котором
производится выбор значений.
Номер_Строки – число, указывающее номер строки внутри диапазона;
Номер_Столбца – число, указывающее номер столбца внутри диапазона.
Функции для выбора значений по ключу
Функция ГПР ищет значение ключа в верхней строке таблицы и возвращает
значение из того же столбца, но для указанной строки.
ГПР (Искомое_значение ; Ссылка_На_Диапазон; Номер_Строки;
Интервальный_Просмотр),
где Искомое_значение - ключевое значение, по которому выполняется
поиск;
Ссылка_На_Диапазон – адрес диапазона ячеек, содержащего исходную
таблицу, в которой выполняется поиск;
Номер_Строки – номер строки внутри диапазона поиска, из которой будет
возвращено значение;
Интервальный_Просмотр – логическое значение, которое определяет,
нужно ли, чтобы функция искала приблизительное или точное значение ключа.
Если этот аргумент равен ИСТИНА, то находится ближайшее к заданному ключу
значение. Если этот аргумент равен ЛОЖЬ, то находится только точное значение,
если искомого значения нет в таблице, то возвращается ошибка.
Функция ВПР ищет значение ключа в крайнем левом столбце указанной
таблицы и возвращает значение из той же строки, но для заданного столбца.
ВПР (Искомое_значение; Ссылка_На_Диапазон; Номер_Столбца;
Интервальный _Просмотр),
где Номер_Столбца – номер строки внутри диапазона поиска, из которой
будет возвращено значение.
Остальные аргументы функции ВПР совподают со значением аргументов
функции ГПР.
Для
функции
ГПР
и
ВПР
в
случае,
если
аргумент
Интервальный_Просмотр равен ИСТИНА, то значения в диапазоне поиска
следует упорядочивать по возростанию.
Функция ПОИСКПОЗ возвращает относительное положение элемента
таблицы, который соответствует указанному значению в указанном порядке.
Функция ПОИСКПОЗ используется вместо функции ГПР или ВПР, когда
требуется найти позицию элемента в диапазоне, а не сам элемент.
ПОИСКПОЗи (Искомое_значение; Ссылка_На_Диапазон;
Тип_Сопоставления).
Тип_Сопоставления соответствует аргументу Интервальный_Просмотр,
но дополнительно позволяет указать способы сортировки.
Дополнительные функции
Функция АДРЕС возвращает адрес ячейки в виде текста, используя номер
строки и столбца.
АДРЕС (Номер_Строки, Номер_Столбца, Тип_Ссылки),
где Тип_Ссылки – аргумент, позволюящий указать в каком виде будет
возвращена ссылка (абсолютная или относительная).
Функция ДВССЫЛ возвращает значение из ячейки по ссылке заданной
текстовой строкой.
ДВССЫЛ (строка_Ссылка_На_Ячейку).
Функция СМЕШ возвращает ссылку на диапазон, отстоящий от ячейки или
диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может
быть отдельной ячейкой или диапазоном ячеек.
СМЕШ (ссылка; смещение_по_строкам; смещение_по_столбцам;
высота; ширина).
Лабораторная работа 8
Автотранспортное предприятие использует
три вида
тарифов:
покилометровый, сдельный за тонну и за час работы транспортного средства.
Значение тарифа зависит от марки используемого транспортного средства.
Марка
автомобиля
ГАЗ
ЗИЛ
КамАЗ
Грузоподъемность
5
8
10
Вид тарифа
За тонну
120,00
100,00
11,00
За км
8,6
9,5
13,4
За час
302,00
333,00
390,00
Вычислить стоимость перевозки следующих грузов
Вид груза
Кирпич
Доски
Размер
заказа
(тонны, Вид тарифа
километры, часы)
10 тонн
За тонну
30 километров
За км
Автомобиль
Класс груза
КамАЗ
ЗИЛ
1
2
Решение в Excel
1.
Занесите в таблицу с исходными данными на рабочий лист.
Рисунок 20
2.
Используя функции из раздела Функции ссылки и автоподстановки,
занесите следующие формулы в ячейки для расчетов:
Наименование показателя Адрес ячейки
Коэффициент
В 21
использования
грузоподъемности
Фактический
объем
В 22
перевозок
Грузоподъемность
В 23
Номер
автомобиля
в
В 24
списке
Размер тарифа
В 25
Формула
=ВПР(С17;$А$10:$В$12;2)
=ЕСЛИ(С18=»За тонну»;с21*С19;»»)
=ВПР(С20; $Ф$5: $В$7;2; ИСТИНА)
=ПОИСКПОЗ(С20; $А$5: $А$7)
=ГПР(С18; $С$4: $Е$;С24+1; ИСТИНА)
Размер оплаты
В26
=С25*С19
Рисунок 21.
Формулы массива
Использование формул массива позволяет одновременно выполнять
вычисления над несколькими наборами ячеек. В результате будет получено одно
или несколько значений. В качестве аргументов формулы массива используются
диапазоны ячеек. Каждый аргумент формулы массива должен включать
одинаковое число строк и столбцов.
Формула массива создается также, как и обычная формула, но для
завершения ввода такой формулы следует использовать комбинацию клавиш
Ctrl+Shift+Enter.
Формулы массива позволяют избежать выполнения промежуточных
вычислений, упростить схему вычислений, заменив несколько отдельных формул
одной формулой массива.
Рисунок 22
Например, по данным, приведенным на рисунке 22, необходимо вычислить
общую грузоподъемность парка.
Без использования формул массива было бы необходимо сначала вычаслить
общую грузоподъемность по каждой марке, затем сложить их. В таком случае
понадобилось бы записать четыре формулы. Формула массива требуется всего
одна. Запишите в в ячейку С5 формулу =СУММ (В2:В4*С2:С4) и нажмите
Ctrl+Shift+Enter. В результате выполнения формулы число автомобилей и
грузоподъемность по каждой марке будут перемножены, а полученные
результаты сложены.
Существует ряд специальных функций, которые в результате возвращают
несколько значений, поэтому эти функции также рассматриваются как формулы
массива.
Лабораторная работа 9
Автобус совершил три оборота по городскому тангенциальному маршруту.
Известны следующие показатели по каждому обороту:
Показатели
Техническая скорость Vт, км
Время простоя на промежуточной остановке tпо, с
Время оборота tко, ч
1 оборот
24
20
1,37
2 оборот
17
30
1,91
3 оборот
20
40
1,82
На конечных остановках автобус простаивал одинаковое время. Определить
показатели: длину маршрута Lм, число промежуточных остановок nпо, время
простоя на конечных остановках tко.
Аналитическое решение
Время затрачиваемое автобусом на один оборот
to 
2 LM 2nno 2tko


VT
3600 60
Для решения задачи необходимо составить систему из трех уравнений:
 2 LM 2nnotko1 2tko1


 to1

3600
60
 VT 1
 2 LM 2nnotko 2 2tko 2


 to 2

3600
60
 VT 2
 2 LM 2nnotko 3 2tko 3


 to 3

V
3600
60
 T3
где VТ1, VТ2, VТ3 – техническая скорость для каждого оборота;
tno1, tno2, tno3, - время простоя на промежуточных остановках для каждого
оборота.
Решение системы дает искомые показатели.
Решение в Excel
Для решения системы уравнений используется метод Крамера, он основан
на матричной записи системы уравнений. Решение находится перемножением
обратной матрицы коэффициентов на вектор свободных членов. Для
преобразований используются функции рабочего листа Excel: МОБР –
возвращает обратную матрицу, МУМНОЖ – вычисляет произведение двух
матриц.
1.
Занесите исходные данные на рабочий лист.
Рисунок 22
2.
Подготовьте матрицу коэффициентов, для этого занесите в ячейки
таблицы формулы:
Адрес ячейки
А12
Формула
=2/С5
Адрес ячейки
B12
Формула
=2*C6/3600
Адрес ячейки
C12
Формула
=2
А13
А14
=2/D5
=2E5
3.
Е12:Е14.
B13
B14
=2*D6/3600
=2*E6/3600
C13
C14
=2
=2
Запишите время оборота как матрицу свободных членов в ячейки
Рисунок 23
4.
Вычислите обратную матрицу, для этого занесите в ячейку А17
формулу =МОБР(А12:С14). Выделите диапазон А17:С19, нажмите клавишу F2 и
Ctrl+Shift+Enter. Этим будет выполнено преобразование исходной формулы в
формулу массива.
5.
Выполните перемножение матриц и получите решение уравнения.
Занесите в ячейку F 17 формулу = МУМНОЖ(А17:С19;Е12:Е14), выделите
диапазон F17:F19, нажмите клавишу F2 и Ctrl+Shift+Enter.
В ячейках F17:F19 будут рассчитаны искомые показатели: длина маршрута,
число промежуточных остановок, время простоя на конечных остановках.
6.
Для проверки полученного решения занесите в ячейку В23 формулу
=2*$F$17/C5+2*$F$18*C6/3600+2*$F$19 и распространите ее в ячейки
С27:D27.
7.
Сравните исходное время оборота (ячейки D23-D24) и рассчитанное
(А24-С24).
Рисунок 24
8.
Решение можно также получить, если воспользоваться функцией
ЛИНЕЙН, которая используется в статистических расчетах для построения
уравнений множественной линейной регрессии. Эта функция может быть
использована для решения систем линейных уравнений. Занесите в ячейку В12
формулу =ЛИНЕЙН(Е12:Е14;А12:С14;0).
Выделите ячейки D13-D14? И нажмите F2 и Ctrl+Shift+Enter.
9.
Сравните полученное решение с предыдущим.
Библиографический список
Основная литература:
1. Карпов А. С. Автомобильные перевозки [Текст] : учеб. пособие / А. С.
Карпов, В. И. Ключников; ВГЛТА. - Воронеж, 2009. - 336 с.
Дополнительная литература:
2. Гаджинский А. М. Логистика [Текст] : рек. М-вом образования Рос.
Федерации в качестве учеб. / А. М. Гаджинский. - 20-е. изд. - М. : Дашков и К,
2012. - 484 с.
3. Автомобильный транспорт [Текст] : ежемес. науч.-произв. журн. / М-во
автомоб. транспорта РСФСР, ЦК профсоюза рабочих автомоб. транспорта и
шоссейных дорог. - М. : Транспорт, 1923 -.
Сподарев Руслан Александрович
Белокуров Владимир Петрович
Артемов Александр Юрьевич
Лихачев Дмитрий Валерьевич
Грузовые перевозки
Методические указания к лабораторным работам
для студентов по направлению подготовки
23.03.01 – Технология транспортных процессов
Документ
Категория
Без категории
Просмотров
59
Размер файла
1 179 Кб
Теги
перевозки, грузовых, работа, лабораторная
1/--страниц
Пожаловаться на содержимое документа