Порядок выполнения работы

Лабораторная работа №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.* Разработайте макрос для поиска повторяющихся данных в таблице А рабочего листа БАЗА.