Порядок выполнения работы. Лабораторная работа №1
Лабораторная работа №1 Тема: Применение MS Excel при планировании маршрута Цель: Создать приложение в MS Excel, позволяющее выбрать оптимальный маршрут, при котором с определенной погрешностью было бы известно время прибытия автомобиля в назначенные населенные пункты. Общие положения Деятельность многих предприятий связана с перевозками. И если маршрут не однодневный, и проходит через множество географических пунктов, то при этом приходится планировать — в какое время транспортное средство будет находиться в определенной географической точке. Причем, если имеются различные альтернативные варианты, то маршрут может быть изменен в зависимости от сложившихся обстоятельств. Это касается не только транспортных предприятий, но и различных курьерских служб — как построить правильно маршрут, просто людей — как спланировать свой день, если необходимо побывать в течение дня в нескольких местах. Казалось бы, что приведенные примеры различны, но при более внимательном рассмотрении видно, что задача у них одна — спланировать время в пути, выбрать наиболее оптимальный маршрут, а самое главное, знать в какое время машина (человек) будет находиться в заданной географической точке. Создадим приложение в MS Excel, позволяющее перед выездом автомобиля распланировать время передвижения если маршрут пролегает через различные населенные пункты Европы. Задача приложения — выбрать оптимальный маршрут, при котором с определенной погрешностью было бы известно время прибытия автомобиля в назначенные населенные пункты. При этом предполагается, что приложение должно предлагать различные возможные сценарии при вводе в него данных предполагаемого маршрута. С помощью укрупненной блок-схемы (рисунок 1.1) поясним работу данного приложения.
Рисунок 1.1 –Укрупненная блок-схема алгоритма работы приложения В качестве исходных данных (блок 1) используются: Наименование населенных пунктов (НП) маршрута (начального, промежуточных, конечного); Время простоя транспортного средства (ТС) в каждом НП; Время и дата отправки ТС из НП. Определение расстояний между НП осуществляется с помощью предварительно созданной табличной базы данных. Поиск расстояний, а также операции блоков 3 и 4 осуществляется с использованием средств MS Excel. Порядок выполнения работы Открыть файл «Лабораторная работа №1» и сохранить его наличный диск. Рабочая книга с создаваемым приложением будет содержать два рабочих листа: База; Расчет. Рабочий лист База. На рабочем листе База находится табличная база данных (список) с названием городов (населенных пунктов) – Таблица Б, и расстояний между ними – Таблица А. В столбец А (Таблицы А) введены названия населенных пунктов, являющихся точкой отправления, а в столбец В названия населенных пунктов — точки прибытия. В столбце С соединены через пробел тексты названия городов, введенных в столбцы А и В, а расстояния между ними введено в столбце D. Рабочий лист РАСЧЕТ. Рабочий лист РАСЧЕТ (необходимо создать его) предназначен непосредственно для прокладывания маршрута через выбранные НП и расчета времени в пути по этому маршруту с учетом средней скорости и возможных задержек. Его условно можно разделить на три составляющих: область выбора маршрута; область ввода прогнозируемых задержек в пути; область вывода данных о времени прибытии и выбытия из населенных пунктов указанных в маршруте; Область выбора маршрута (рисунок 1.2) в свою очередь состоит из: элементов управления Поле со списком для ввода наименований НП; области вывода названий НП и расстояний между ними; область вывода текста проложенного маршрута через выбранные НП и суммарного расстояния. В разрабатываемом приложении предполагается, что автомобиль будет двигаться по всему маршруту со средней скоростью, значение которой вводится в ячейку. Создать на рабочем листе РАСЧЕТ таблицы (рисунок 1.3). Элементы управления Поле со списком расположены в правой части области выбора маршрута и предназначены для автоматизации ввода названий НП. Добавить элемент управления «Поле со списком» (Вид→Панель инструментов→Формы) для пяти пунктов назначения. Элемент добавляется справа от таблицы в ячейку строки, соответствующей искомому городу. В данном случае – ячейки E3...Е7. Рисунок 1.2 – Добавление инструмента «Поле со списком»
5. Настроить «Поле со списком» (правый клик по Полю→Формат объекта→Элемент управления): - «Формировать список по диапазону» - указать диапазон данных, по которому будет формироваться список. - Связь с ячейкой - желательно справа от элемента управления. - Количество строк списка - 20. 6. Оформить элементы управления. 7. В первом столбце Таблицы 1 с помощью функции ВПР осуществить поиск из таблицы Б (лист «БАЗА») первого задаваемого города отравления. Синтаксис функции ВПР: ВПР(искомое_значение; таблица; номер_столбца; интервалъный просмотр) Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое значение может быть значением, ссылкой или текстовой строкой. Таблица - таблица с информацией, в которой ищутся данные. Номер_столбца - это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. 8. Во втором столбце таблицы 1 осуществить поиск города прибытия. Если город не задан, выводить пустую ячейку, если предыдущий город не задан, также выводить пустую ячейку. Для этого необходимо использовать функцию ЕСЛИ. Синтаксис функции ЕСЛИ: ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложъ) Лог_выражение - это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Значение_если_истина - это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь - это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, для ячейки ВЗ (рисунок 1.3): =ЕСЛИ(АЗ="";"";ЕСЛИ(ВПР(F3;БАЗА!$F$3:$G$31; 2;ЛОЖЬ)="НЕТ";""; (ВПР (F4;БAЗА! $F$3:$G$31;2;ЛОЖЬ)))) Для ячейки А4: =В3 Для ячейки B4: =ЕСЛИ(А4="";"";ЕСЛИ(ВПР(F4;БАЗА!$F$3:$G$31; 2;ЛОЖЬ)="НЕТ";""; (ВПР (F5;БAЗА! $F$3:$G$31;2;ЛОЖЬ)))) И так далее для остальных. Можно просто скопировать формулы ячеек B.
Рисунок 1.3 – Рабочий лист «Расчет»
9. В третий столбец ввести маршрут следования, используя функцию «СЦЕПИТЬ». Если один из городов не задан, выводить пустую ячейку. Синтаксис функции СЦЕПИТЬ: СЦЕПИТЬ (текст 1; текст2;...) текст1, текст2, ... - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Например, для ячейки C3: =ЕСЛИ(B3=" ";" ";ЕСЛИ(B3="НЕТ";" ";СЦЕПИТЬ(A3;" ";B3))) 10. С использованием базы городов таблицы А (лист «БАЗА») найти расстояние между ними. Если города во втором столбце таблицы 1 не заданы - выводить пустую ячейку. Если города в 1 и 2 столбце таблицы 1 совпадают – выводить пустую ячейку. Для этого необходимо использовать функцию ЕСЛИ и функцию ВПР. Например, для ячейки D3: =ЕСЛИ(B3=" ";" ";ЕСЛИ(ВПР(F4;БАЗА!$F$3:$G$31;2;ЛОЖЬ)="НЕТ";" ";ЕСЛИ(A3=B3;" ";ВПР(C3;БАЗА!$C$3:$D$758;2;ЛОЖЬ)))) В остальные ячейки D4…D6 вводятся аналогичные формулы с учетом: =ЕСЛИ(B4=" ";" ";ЕСЛИ(ВПР(F5;БАЗА!$F$3:$G$31;2;ЛОЖЬ)="НЕТ";" ";ЕСЛИ(A4=B4;" ";ВПР(C4;БАЗА!$C$3:$D$758;2;ЛОЖЬ)))) 11. Указать полный маршрут с помощью функции СЦЕПИТЬ и суммарный пройденный путь (расстояние движения по маршруту находится как сумма расстояний между пунктами). 12. Создать таблицу потерь времени на остановки и задержки в пути. Указать среднюю скорость движения. 13. Ввести время отправления из начального пункта. Данная ячейка должна иметь формат ДД.ММ.ГГГГ ч:мм. 14. Создать таблицу времени прибытия в каждый промежуточный пункт и конечный пункт. Например, в ячейке С24 содержится формула: =ЕСЛИ(D3=" ";" ";C21+(D3/$C$20)/24) в ячейке С25: =ЕСЛИ(D4=" ";" ";C24+(D4/$C$20+D15)/24)
Содержание отчета Тема, цель, исходные данные. Последовательность выполнения работы с указанием использованных средств MS Excel. Распечатка разработанного приложения (только рабочий лист Расчет). Ответы на контрольные вопросы. Контрольные вопросы 1.Поясните порядок планирования маршрута с использованием MS Excel. 2. Разработайте блок-схему и опишите подробный алгоритм части приложения (по заданию руководителя). 3. Необходимость автоматизации планирования маршрута. 4. Преобразуйте (по заданию руководителя) приложение так, чтобы был возможен: а) ввод средней скорости на каждом отрезке маршрута; б) ввод предполагаемого времени нахождения в пути на каждом отрезке маршрута. При этом создать дополнительную табличную базу данных с указанием средней скорости (времени) прохождения каждого отрезка введенных расстояний между НП. 5.** Разработайте макрос(ы) VBA для заполнения и проверки данных в таблицах А и Б рабочего листа БАЗА. 6.* Разработайте приложение для оптимизации разборочного (сборочного) маршрута. 7.* Разработайте макрос для поиска повторяющихся данных в таблице А рабочего листа БАЗА.
Популярное: Личность ребенка как объект и субъект в образовательной технологии: В настоящее время в России идет становление новой системы образования, ориентированного на вхождение... Почему стероиды повышают давление?: Основных причин три... Генезис конфликтологии как науки в древней Греции: Для уяснения предыстории конфликтологии существенное значение имеет обращение к античной... ©2015-2024 megaobuchalka.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. (388)
|
Почему 1285321 студент выбрали МегаОбучалку... Система поиска информации Мобильная версия сайта Удобная навигация Нет шокирующей рекламы |