Лабораторные работы по дисциплине «Информационные и компьютерные технологии в обеспечении транспортных процессов 1» (5 семестр) Использование MS Excel в решении задач на транспорте

Выполнены Лабораторные работы по дисциплине «Информационные и компьютерные технологии в обеспечении транспортных процессов 1» (5 семестр) Использование MS Excel в решении задач на транспорте

4 лабораторные работы + отчёт

Лабораторная работа 1. Простые задачи с линейным алгоритмом расчета в одну колонку

Задача 1

Автоколонна, состоящая из Асс=10 автомобилей ЗИЛ-130 с прицепами общей грузоподъемностью q=10 m, находилась в крестьянском хозяйстве в течение Дк=25 дней на уборке свеклы. Рассчитать объем перевозок Q и грузооборот автоколонны P за это время, если известны следующие показатели рее работы: время на маршруте за день Тм=11,25 ч, средняя длина груженой ездки 1ге = 45 км, средняя техническая скорость Vт= 30 км/ч, среднее время выполнения погрузочно-разгрузочных операций tп/р = 0,66 ч, коэффициент использования пробега за одну ездку  =0,5, статический коэффициент использования грузоподъемности gст =1, коэффициент выпуска автомобилей на линию в = 0,85.

Решение в Excel

1. Запустите Excel и создайте новую рабочую книгу.

2. Занесите на рабочий лист Лист1 таблицу с исходными данными, как показано на рисунке 1.

3. Закончите графическое оформление задания, добавив разлиновку таблицы, настроив ширину колонок и т.п. 

Задача 2

АТП обслуживает торговую сеть города в течение Дк =365 дней. Среднесписочное число автомобилей Aсс =40, коэффициент технической готовности т=0,84, коэффициент выпуска автомобилей на линию в=0,78. Сколько автомобиле-дней подвижной состав находится в ремонте АДр и эксплуатации АДэ.

Решение в Excel

1. Запустите Excel и создайте новую рабочую книгу.

2. Занесите на рабочий лист Лист1 таблицу с исходными данными, как показано на рисунке 3. Расчетные формулы пока не вводите.

3. Прежде, чем вводить формулы для расчета автомобиле-дней, необходимо задать имена ячеек с исходными данными. Для этого установите текущую ячейку на С5 (это значение Дк=25), затем в поле Имя на панели инструментов удалите адрес этой ячейки С5 и вместо него введите имя переменной Дк, закончив ввод нажатием на клавишу Enter.

4. Введите две формулы обычным образом с использованием мыши для указания ячеек в качестве операндов. При этом автоматически вместо их адресов будут отображаться заданные вами имена.

5. Закончите графическое оформление задания, добавив разлиновку таблицы, настроив ширину колонок и т.п.

Лабораторная работа 2. Задачи с размещением данных в таблице 

Задача 3

B городе имеется 4 пассажирских автотранспортных предприятия, показатели работы которых приведены в таблице. Определить общий пробег Lобщ автобусов для каждого из предприятий за месяц.

Расчеты выполняются для каждого АТП.

Решение в Excel

1. Создайте новую рабочую книгу и сохраните ее под именем Задача 3.

2. Занесите исходную таблицу на рабочий лист (рис. 6).

3. Введите в ячейку D10 расчетную формулу общего пробега с использованием исходных данных для АТП-1. Распространите эту формулу вправо на оставшиеся АТП. Для этого сделайте ячейку D10 текущей, затем наведите указатель мыши на квадратный маркер в правом нижнем углу ячейки; нажмите левую кнопку мыши и, удерживая ее, распространите формулу в диапазоне E10:G10.

4. Закончите графическое оформление задания, добавив разлиновку таблицы, настроив ширину колонок и т.п. 

Задача 4

Бригада в составе Acc = 15 автомобилей-самосвалов МАЗ-503Б грузоподъемностью q = 7 m осуществляет перевозку щебня на строительство автомобильной дороги. Показатели работы бригады: среднее время погрузочно-разгрузочных операций tп/р = 0,2 ч, коэффициент использования грузоподъемности  = 1, коэффициент использования пробега  = 0,5, коэффициент выпуска автомобилей на линию  = 1. Какой объем перевозок Q и грузооборот P по дням выполняла бригада, если остальные показатели составляют следующие величины:

Решение в Excel с использованием абсолютных ссылок

Обращаем внимание, что исходные данные в данной задаче можно разделить на две группы: постоянные, не зависящие от дня недели, и переменные, варьирующиеся по дням. Поэтому, при создании формул нам потребуется использовать абсолютные ссылки на постоянные исходные данные.

1. Создайте рабочую книгу, занесите в нее таблицу с исходными данными. Вверху таблицы разместите показатели, которые имеют постоянные значения Acc, q, t , а, b, g , а внизу — показатели, которые изменяются по дням lге; Vт , Tн (рис. 7).

2. Для изменяемых показателей занесите все значения по дням.

3. B ячейку D16 занесите формулу =$D$5*$D$6*D14*$D$8*$D$9*$D$10*D13/(D12+D13*$D$10*$D$7). Заметьте, что для показателей, которые имеют постоянные значения, — ссылка абсолютная, а на изменяемые показатели — относительная (обычная) (рис. 7). Абсолютные ссылки удобнее вводить так: щелкнуть мышью по адресуемой ячейке (например, D5), затем нажать клавишу F4, при этом в адрес будут добавлены символы $ ($D$5), затем набираем знак операции, например *.

4. Формулу в ячейке D16 распространите вправо до ячейки J16.

5. Введите в ячейку D17 формулу =D16*D12 и аналогично распространите ее вправо.

6. Закончите графическое оформление задания, добавив разлиновку таблицы, настроив ширину колонок и т.п. 

Решение в Excel с использованием именованных ячеек

1. Выполните пункты 1 и 2 предыдущего решения.

2. Присвойте имена тем ячейкам, которые хранят неизменяемые данные. D5 – Acc, D6 – q, D7 – Тпр, D8 – гамма, D9 — альфа , D10 – бета.

3. B ячейку D16 занесите формулу =Асс*q*D14*гамма*альфа*бета*D13/(D12+бета*D13*Тпр) и распространите ее в вправо на диапазон E16:J16. Напомним, что имена ячеек в формуле удобнее вводить щелчком мыши по адресуемым ячейкам, а не набором с клавиатуры.

4. Введите в ячейку D17 формулу =D16*D12 и аналогично распространите ее вправо.

Лабораторная работа 3. Использование функций MS Excel

Задача 5

Каков общий пробег автомобиля ГАЗ-53А за пять дней и среднесуточный пробег автомобиля по зафиксированным в путевом листе показаниям спидометра?

Аналитическое решение

Вначале вычисляется ежесуточный пробег автомобиля как разность показаний спидометра при выезде и возврате. Затем, просуммировав ежесуточный пробег, можно найти общий пробег, а разделив общий пробег на дни работы, — среднесуточный пробег.

Решение в Excel

1. Создайте новую рабочую книгу, сохраните ее под именем Задача 5, занесите на рабочий лист таблицу с исходными данными.

2. B ячейку B7 занесите формулу =B6 – B5, позволяющую рассчитать пробег за сутки, и распространите ее вправо на ячейки C7:F7.

3. Первый способ расчета общего пробега состоит в простом суммировании ячеек диапазона B8:A8. Занесите в B9 формулу =B7+C7+D7+E7+F7.

4. Второй способ заключается в использовании специальной функции СУММ и диапазона ячеек. Занесите в B9 формулу =CУМM(B7:F7).

5. Среднесуточный пробег можно вычислить по формуле =B8/5. Занесите эту формулу в ячейку B10.

6. Более правильным для расчета среднесуточного пробега будет использование специальной функции СРЗНАЧ, возвращающей среднее значение для выбранных ячеек. Занесите в ячейку B11 формулу =CPЗHAЧ(B7:F7).

7. Bo многих случаях существует более простой способ выполнения расчетов, так как Excel предлагает большое число специализированных функций. Так можно вычислить общий пробег, не делая промежуточных расчетов ежедневных пробегов. Для этого следует использовать специальную формулу массива {СУММ}, которая возвращает сумму диапазонов ячеек и автоматически выполняет промежуточные действия.

8. Для создания формулы массива занесите в ячейку B12 формулу =СУММ(B6:F6-B5:F5), затем нажмите клавишу F2 и комбинацию клавиш Ctrl+Shift+Enter. К формуле будут добавлены фигурные скобки: {=СУММ(B6:F6-B5:F5)}.  

Задача 6

В результате подсчета числа вошедших и вышедших пассажиров по одному рейсу для одного автобусного маршрута были получены следующие данные:

Определить по этим данным среднюю длину поездки пассажира.

Решение в Excel

1. Занесите исходные данные на рабочий лист (рис. 9). Строку 5 оставьте пустой.

2. Выполните расчет числа пассажиров, перевезенных по перегонам, для этого занесите в ячейку E6 формулу =Е5+В6–С6 и распространите ее в диапазоне E7:E12.

3. Выполните расчет пассажирооборота по участкам, занесите в ячейку F6 формулу =D6*E6 и распространите ее в диапазон F7:F11.

4. B ячейку B13 занесите с помощью мастера функций формулу =СУММ(В6:В11) и распространите ее в ячейку C13. Эти формулы рассчитывают количество перевезенных пассажиров, суммируя число вошедших и вышедших соответственно.

5. Для расчета суммарного пассажирооборота по маршруту занесите с помощью мастера функций в ячейку F13 формулу =CУMM(F6:F12).

6. Расчет средней длины поездки пассажира выполняется по формуле =F12/B13, которая заносится в ячейку F13.

7. Более простым способом расчета средней длины поездки пассажира, исключающим выполнение промежуточных расчетов, является использование формулы =СУММПРОИЗВ(B6:В11;Е6:Е11)/СУММ(В6:В11), которая с помощью мастера функций заносится в ячейку F15. 

Задача 7

Автотранспортное предприятие выполняет грузовые перевозки для разовых клиентов. Для расчета стоимости перевозки используются различные виды тарифов на перевозки. Вид тарифа зависит от объема заказанной услуги и от пробега автомобиля. Если грузооборот P составляет менее 1300 ткм, то используется тариф за тонну, если более, то тариф за километр. B таблице приведены значения тарифов для различных значений показателей: 

Решение в Excel

1. Занесите на рабочий лист исходную таблицу тарифов.

2. Показатели по клиентам занесите в том же виде, в каком они представлены в условиях задачи.

3. Используя исходные формулы, выполните расчет грузооборота и общего пробега по каждому клиенту. Занесите в ячейку B22 формулу =B15*B17 и распространите ее в C22:E22, B ячейку B23 занесите формулу =B15*B17/(B16*B19*B19.) и распространите ее в C23:E23.

4. Для определения вида используемого тарифа необходимо проверить условие: если грузооборот меньше 1300, то используется тариф за тонну, если больше — за километр.

5. Выделите ячейку B26 и запустите Мастер функций (кнопкой на панели инструментов ). Выберите категорию Логические и функцию ЕСЛИ. Ha следующем шаге укажите параметры: Условие B22>$D$12, Значение если истина – 2, Значение если ложь – 1. B результате в ячейке B26 будет формула =ECЛИ(B22>$D$12;2;1). Распространите ее в диапазон C26. Результат расчета по формуле показывает вид используемого тарифа. Если результат равен 1, то используется тариф за тонну, если 2, то тариф за километр (рис. 11).

6. Произведите построение таблицы, в которой рассчитывается стоимость перевозки по каждому тарифу. Для этого, используя Мастер функций, занесите в ячейку B29 =ЕСЛИ(И(В$15>$В6; B$15<=$B7);$C6*B$15;0). Будьте внимательны при указании абсолютной адресации знаком $. Распространите формулу в ячейки B29:E31. B ячейку В32 занесите формулу =ECЛИ(И(B$23>$D6; B$23<=$D7); B$23*$E6; 0) и распространите ее на диапазон B32:E34.

7. B заключение выполните расчет платы за перевозку. Используя Мастер функций, занесите в ячейку B37 формулу =ЕСЛИ(В26=1; СУММ(В29:В31); СУММ(В32:В34)). Распространите ее в C37:E37. По полученной формуле вычисляется общая сумма оплаты за перевозки в зависимости от вида тарифа. Результаты расчетов помещаются в диапазон B37:E37.

Лабораторная работа 4. Ссылки и автоподстановки

Задача 8

Автотранспортное предприятие использует три вида тарифов: покилометровый, сдельный за тонну и за час работы транспортного средства. Значение тарифа зависит от марки используемого транспортного средства.

Вычислить стоимость перевозки следующих грузов.

Решение в Excel

1. Занесите таблицу с исходными данными на рабочий лист.

2. Занесите исходные данные по перевозкам в таблицу.

3. Используя функции из раздела Функции ссылки и авто-подстановки, занесите формулы в ячейки для расчетов.