Если системных ресурсов недостаточно

Лабораторная работа 6.

Планирование маршрута в среде Excell.

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

Это касается не только транспортных предприятий, но и различных курьерских служб - как построить правильно маршрут, просто людей - как спланировать свой день, если необходимо побывать в течение дня в нескольких местах.

Казалось бы, что приведенные примеры различны, но при более внимательном рассмотрении видно, что задача у них одна - спланировать время в пути, выбрать наиболее оптимальный маршрут, а самое главное, знать в какое время машина (человек) будет находиться в заданной географической точке.

Создадим приложение в Excel, позволяющее перед выездом автомобиля распланировать время передвижения если маршрут пролегает через различные населенные пункты Европы. Задача приложения - выбрать оптимальный маршрут, при котором с определенной погрешностью было бы известно время прибытия автомобиля в назначенные населенные пункты. При этом предполагается, что приложение должно предлагать различные возможные сценарии при вводе в него данных предполагаемого маршрута.

Рабочей книге с создаваемым приложением присвойте имя Расчет времени в пути, и она будет содержать три рабочих листа:

• База;
• Ввод;
• Города.

Рабочий лист База

На рабочем листе База (рис. 9.1.) находится табличная база данных (список) с названием городов (населенных пунктов) и расстояний между ними. В столбец А введены названия населенных пунктов, являющихся точкой отправления, а в столбец В названия населенных пунктов - точки прибытия. В столбце С соединены через пробел тексты названия городов, введенных в столбцы А и В, а расстояния между ними введено в столбце D.

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

Особенностью списка на этом листе является ввод двух строк с указанием расстояния между двумя городами. То есть при вводе расстояния между городами А (пункт отправления) и В (пункт прибытия) необходимо дополнительно ввести расстояние и между пунктами В (пункт отправления) и А (пункт прибытия). Иначе алгоритм поиска значения расстояния между этими двумя населенными пунктами будет неоправданно усложнен.

На этом листе для удобства перехода на другие рабочие листы, созданы два элемента управления - кнопки. Такие же кнопки присутствуют и на других листах книги. Этим кнопкам назначены макросы, вся запись которых заключается в активизации рабочего листа, на который должен осуществляться переход после нажатия на эту кнопку.

Рис. 9.1. Рабочий лист База

Рабочий лист Города

Основная задача рабочего листа Города (рис. 9.2.) - ввод данных в лист База. Кроме того, на нем внедрен ряд сервисных функций, которые будут рассмотрены далее. На этом же листе находится список (столбцы А:В) названий городов, присутствующих в табличной базе данных на листе База. В столбец А введен порядковый номер названия города в списке, а в столбец В введено само название.

На первом этапе создания списка названий городов на листе Города:

• в ячейку А2 введите значение 1;
• в ячейку В2 текст введите НЕТ.

Выбранное значение ячейки В2 в будущем при выборе маршрута будет указывать на то, что в этой точке маршрут закончен.

Рис. 9.2. Рабочий лист Города

Названия населенных пунктов (далее НП), между которыми требуется ввести расстояния в табличную базу данных на листе База, вводятся в ячейки D2 и Е2, а расстояние между ними в ячейку G2. Эти данные можно вводить в ячейки с клавиатуры, но есть и возможность автоматизации этого процесса, который будет рассмотрен далее.

Ввод названия города в список городов на листе Города

Первый элемент автоматизации создаваемого приложения, проверка - находится ли название выбранного НП в списке городов на листе Города.

Предварительное создание формул поиска информации

В ячейку D2 введите формулу, которая по порядковому, номеру, введенному в ячейку J1, производит поиск названия города в списке диапазона ячеек А2:В200 листа Города:

=ВПР(J1;$A$2:$B$200;2;ЛОЖЬ)

Аналогичная формула находится и в ячейке Е2, но порядковый номер названия этого города в списке, вводится в ячейку J2.

Формула в ячейке G2 отличается от предыдущей тем, что находящаяся в первом аргументе функции ВПР функция СЦЕПИТЬ объединяет текст названия, введенных в ячейки D2 и Е2, городов. Объединенный текст названий городов и является искомым текстом поиска для определения введенного расстояния между этими НП в столбце D листа База. Если расстояния между этими городами введено в табличную базу данных на листе База, то это значение возвращается формулой:

=ВПР(СЦЕПИТЬ(D2;" ";E2);База!C:D;2;ЛОЖЬ)

Задача автоматизации ввода текста названия города

Задача автоматизации процесса ввода текста названий городов в ячейки D2:Е2 заключается в следующем:

• возможный отказ от ввода в эти ячейки текста названия НП с клавиатуры, если такое название имеется в списке на листе Города;
• если название НП отсутствует в списке на листе Города, то это должно визуально отражаться на экране монитора, и ввод названия этого НП в список должно осуществляться автоматически, после ввода названия в ячейки D2 или E2 с клавиатуры;
• если расстояние между выбранными названиями городов присутствует в табличной базе данных на листе База, то оно должно быть отражено в ячейке G2;
• автоматическое восстановление формул, введенных в ячейки D2, E2 и G2.

Необходимость автоматизации процесса ввода текста названия города

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

После ввода формул в ячейки D2, E2 и G2 пользователь должен просмотреть список названий НП на листе Города и если присутствуют нужные ему наименования городов, то ввести их порядковые номера в списке в ячейки J1 и J2. Это не совсем удобно. На этом этапе можно применить элементы управления с раскрывающимся списком введенных названий НП и при выделении какого-либо из них, автоматическому помещению его порядкового номера в ячейки J1 и J2.

Если же название НП отсутствует в списке на листе Города, то должна присутствовать возможность вводить текст его названия в ячейки D2 и Е2 непосредственно с клавиатуры, несмотря на то, что эти ячейки содержат формулы. В таком случае это название НП должно автоматически вводиться в список названий НП, а формулы должны в последующем автоматически обновляться.

Может быть следующая ситуация - названия городов в столбце В на листе Города имеются, а расстояние между ними отсутствует на рабочем листе База. Тогда при вводе названий городов в ячейки D2 и Е2, формула в ячейке G2 возвратит значение ошибки #Н/Д. Поэтому должна присутствовать возможность ввода значения расстояния между этими НП в ячейку G2 поверх формулы, которая в последующем также должна автоматически обновляться.

Создание элементов управления для ввода названий городов

Для автоматизации ввода информации в ячейки D2 и E2 создайте на листе Города два элемента управления Список, задача которых будет состоять в том, чтобы:

• производить выбор в раскрывающемся списке названия городов;
• обновлять формулы в ячейках D2, E2, G2, Е4 и Е5.

После создания каждого элемента управления на рабочем листе выделите его и комбинацией клавиш Ctrl+1 вызовите диалоговое окно Формат элемента управления. На вкладке Элемент управления в поле Формировать список по диапазону введите адрес диапазона ячеек В2:В200 (раскрывающийся список названий городов). В поле Помещать результат в ячейку укажите ссылку на адреса ячеек J1 и J2 соответственно для каждого элемента управления. В таком случае порядковый номер названия города выбранного в раскрывающемся списке будет помещен в ячейку J1 или J2, а формулы в ячейках E2 и D2 по этому номеру определят название города.

Элементам управления назначены макросы ФормулаГород1 и ФормулаГород2 (рис. 9.3.)., которые при обращении к этим элементам управления производят ввод формул в ячейки D2, G2 и в ячейки Е4 и Е5 (назначение этих ячеек и формул в них будет раскрыто далее). Запись макросов заключается в:

• предварительном вводе в эти ячейки формул;
• для записи непосредственно кода VBA ввода этих формул в ячейки, после начала записи макроса, выделите поочередно указанные ячейки и нажмите клавиши F2 и Enter;
• после остановки записи макроса отредактируйте код VBA.

Рис. 9.3. Макросы обновления формул ввода наименований городов и расстояний между ними

Ввод в список нового названия города

Следующий этап автоматизации - что делать, если пользователь не обнаружил в списке городов на листе Города нужное ему название. Поэтому он должен ввести текст названия НП в ячейку D2 или/и E2 с клавиатуры.

Формула в ячейке Е4 ответственна за отображение введенного в ячейку D2 названия НП, если таковой отсутствует в списке НП на листе Города:

=ЕСЛИ(ЕНД(ВПР(D2;$B:$B;1;ЛОЖЬ));D2;0)

Формула в первом аргументе функции ЕСЛИ производит с помощью функции ВПР поиск названия НП в списке городов в столбце В, и если не находит его, то функция ЕНД исправляет возвращенное функцией ВПР значение ошибки на значение ИСТИНА. Это значение и является разрешением для функции ЕСЛИ отобразить наименование НП введенное в ячейку D2 с клавиатуры.

Задача формулы в ячейке Е4 - показать визуально пользователю, что название НП отсутствует в существующем списке НП. Следовательно, это название нужно ввести в этот перечень. Для автоматического ввода названия нового города в список НП, справа от ячейки Е4 расположена кнопка - Город1.

Задача этой кнопки - запуск на выполнение макроса НаименованиеГорода1, который несколько отличается от созданных ранее и выполняет следующее:

• присваивает переменной Row значение количества заполненных (непустых) ячеек в столбце В;
• определяет координаты первой пустой ячейку в конце списка в столбце В, имеющую координаты строки Row и столбца В (номер 2);
• присваивает этой ячейке значение, возвращенное формулой в ячейке Е4;
• выделяет диапазон ячеек от ячейки с определенными координатами (Row, 2), в которую введено название нового города и до ячейки В3;
• производит сортировку выделенного диапазона по убыванию, не считая ячейку В3 заголовком, о чем говорит то, что аргументу Header метода Sort присвоено значение xlNo;
• в ячейку столбца А вводит порядковый номер для названия города, находящегося последним в списке. Для этого использована функция МАХ (Max) определения максимального номера в столбце А, увеличенному затем на единицу.

После ввода названия нового города, оно отображается в раскрывающемся списке НП, элемента управления Список.

Рис. 9.4. Подпрограмма введения нового названия НП в список

Для создания макроса поступайте комбинированным способом - сначала механическая запись описанных построчно действий с последующим редактированием кода VBA подпрограммы.

В ячейке Е5 находится формула аналогичная формуле в ячейке Е4, но анализирующая название НП, введенного в ячейку Е2.

Кнопка Город2 активизирует аналогичный макрос НаименованиеГорода2, по вводу названия НП из ячейки Е5 в список городов. Повторять действия по записи и редактированию макроса необходимости нет. И отличие этого макроса от предыдущего заключается только в замене адреса ячейки с Е4 на Е5 и изменении в имени макроса значения 1 на 2. Создание макроса будет заключаться в копировании текста макроса в окне программы Редактора Visual Basic. Для этого:

• в окне программ выделите текст макроса НаименованиеГорода1 и скопируйте в буфер обмена;
• при помощи комбинации клавиш Ctrl+End переместите курсор в конец макроса НаименованиеГорода1 и после нажатия на клавишу Enter (переход на следующую строку) произведите вставку скопированного;
• замените в имени макроса значение 1 на 2 - НаименованиеГорода2;
• выделите текст нового макроса и выполните команду Правка/Заменить или наберите комбинацию клавиш Ctrl+H;
• в появившемся диалоговом окне Замена (рис. 9.5.) введите в поле Образец адрес ячейки - Е4, а в поле Заменить на - Е5;
• нажмите кнопку Заменить все;
• появившееся диалоговое окно Microsoft Visual Basic (рис. 9.6.) сообщит, что выполнена одна замена;
• закройте диалоговые окна;
• копированием кнопки Город1 создайте кнопку Город2 и назначьте ей созданный макрос.

Рис. 9.5. Диалоговое окно Замена

Рис. 9.6. Диалоговое окно с сообщением о выполнении операции

Для замены всего лишь одного значения можно было бы и не проделывать такую трудоемкую операцию с вызовом диалогового окна Замена. Но важно было отработать саму технологию создания макроса методом копирования и последующей заменой в нем отдельных деталей текста.

Диалоговое окно Замена

Квалифицированное использование диалогового окна Замена значительно повышает эффективность создания подпрограмм. Коротко опишем его возможности.

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

Поле Заменить на предназначено для ввода текста замены. Для удаления из документа текста, указанного в поле Образец следует оставить поле Заменить на пустым. В эти поля возможна вставка скопированного текста из буфера обмена.

Использование переключателей Области поиска задает диапазон поиска и замены текста:

• Текущая процедура - поиск выполняется только в текущей процедуре;
• Текущий модуль - поиск выполняется только в текущем модуле;
• В текущей базе данных - поиск выполняется во всех модулях текущей базы данных;
• Выделенный текст - поиск выполняется в выделенном блоке текста.

Раскрывающийся список Просмотр - устанавливает направление поиска: Вверх, Вниз от положения курсора или Все в выбранном диапазоне.

Опция Только слово целиком - выполняет поиск целого слова и игнорирует его вхождения в более длинные слова.

Опция С учетом регистра - выполняет поиск вхождений с комбинацией строчных и заглавных букв, указанной в поле Образец.

Опция Использовать шаблон - выполняет поиск с использованием подстановочных символов.

При нажатии на кнопку Заменить запрашивается подтверждение перед каждой заменой искомого текста.

При нажатии на кнопку Заменить все - выполняется замена всех вхождений искомого текста на текст замены, не запрашивая подтверждения.

Оптимизация операции ввода в список нового названия города

С одной стороны контроль за вводом нового названия НП в ячейки D2 и Е2 оправдан - наглядность. Визуально отображены новые названия городов в ячейках Е4 и Е5, не содержащиеся в списке названий НП. Но с другой стороны второй элемент управления Кнопка и второй макрос "утяжеляют" файл. Минимизацию можно осуществить вводом в ячейку Е4 одной формулы, заменяющей введенные ранее две формулы в ячейки Е4 и Е5:

=ЕСЛИ(ЕНД(ВПР(D2;$B:$B;1;ЛОЖЬ));D2;ЕСЛИ(ЕНД(ВПР(E2;$B:$B;1;ЛОЖЬ));E2;0))

которая проверяет вначале первое название НП (ячейка D2) на наличие его в списке городов, а затем второе название НП (ячейка Е2). Если введены два новых названия НП, то для ввода их в список городов требуется:

• нажать кнопку Город1 для ввода в список первого НП;
• после этого формула в ячейке Е4 возвратит название второго НП, который вводится в список городов вторым нажатием на эту же кнопку.

Таким образом, необходимость в кнопке Город2 и назначенном ему макросе отпадает.

Ввод новой информации в табличную базу данных расстояний между городами

Для ввода в табличную базу данных на листе База расстояния между НП и их названий, запишите макрос РасстоянияВвести (рис. 9.7.). Этот макрос коренным образом отличается от всех созданных ранее. Одно из его отличий заключается в том, что при его выполнении для ввода данных Excel не активизирует рабочий лист База и не активизирует ячейки, в которые эти данные будут введены. Следующее его отличие - его запись осуществляется не в механической записи последовательности действий, а в наборе кода VBA с клавиатуры.

Присвоение ячейкам листа База значений, введенных в диапазон ячеек D2:G2 на рабочем листе Города, производится с помощью кода VBA. Последовательность выполнения подпрограммы следующая:

• трем переменным X, Y и Z присваиваются значения, предварительно введенные в ячейки D2, E2 и G2 активного рабочего листа. Так как макрос РасстоянияВвести будет запускаться на выполнение кнопкой Расстояние ввести, находящейся на рабочем листе Города, то необходимости указания имени этого рабочего листа в тексте подпрограммы нет. Макрос довольно простой, и особой необходимости вводить в подпрограмму переменные не было. Это сделано с позиции читаемости.

Следующей переменной Row присваивается значение определяемое с помощью функции CountA, которая проводит подсчет непустых ячеек в столбце В (столбец может быть любой от А до D) на листе База и увеличенное на единицу. Значение переменной Row указывает на номер первой пустой строки, с которой будет осуществляться ввод данных;

В табличную базу данных на листе База необходимо ввести две строки, по четыре записи в каждой: НП отправления (столбец А), НП прибытия (столбец В), соединенное название пункта отправления и прибытия, разделенные символом пробел (столбец С) и расстояние между НП (столбец D). Поэтому эти значения последовательно присваиваются четырем ячейкам слева направо сначала первой, а затем второй строки. Следовательно адреса ячеек изменяются в следующей последовательности: первая строка - Cells(Row, 1), Cells(Row, 2), Cells(Row, 3), Cells(Row, 4) и вторая строка - Cells(Row + 1, 1), Cells(Row + 1, 2), Cells(Row + 1, 3) и Cells(Row + 1, 4).

Рассмотрим более подробно строку кода VBA для ввода данных в ячейку во второй заполняемой строке столбца С рабочего листа База:

Sheets("База").Cells(Row + 1, 3).Value = X & " " & Y

Sheets("База") - ранее, при выделении или активизации ячейки, рабочий лист не указывался. В этой строке кода VBA присвоение значения ячейке листа База задается без его активизации и с другого листа. По иерархии объект Sheets (рабочий лист) находится выше, чем объект Cells (ячейка, диапазон). И если не указать достаточный путь, в частности рабочий лист, то ввод данных произведется в ячейки на активном рабочем листе.

Cells(Row + 1, 3) - координаты ячейки с адресом: Row+1(строка); 3 (столбец С).

Value - ячейке будет присвоено значение;

= - операция присвоения.

X & " " & Y - значение ячейки D2 (или значение возвращаемое формулой, находящейся в этой ячейке) объединить через пробел со значением ячейки Е2 (или значение возвращаемое формулой). Знак & - операция объединения.

Рис. 9.7. Подпрограмма РасстояниеВвести

Проверка наличия в табличной базе данных повторяющихся записей

Отсутствие названий НП и расстояния между ними на рабочем листе База довольно легко обнаружить. Например, при изменении названия НП в ячейках D2 и Е2 на листе Города формула в ячейке G2 возвратит ошибку. Повторяющиеся же данные на листе База могут привести в будущем к ошибке. Рассмотрим алгоритм поиска повторяющихся записей на этом рабочем листе.

Алгоритм поиска повторяющихся данных

Для поиска повторяющихся записей на листе База (например, предполагаем что их будет не больше десяти) выполните следующую последовательность действий:

• перейдите на лист База;
• проведите сортировку списка по столбцу С по убыванию или по возрастанию;
• введите в ячейку F2 формулу: =ЕСЛИ(C2=C3;C2;0)
• смысл которой заключается в том, чтобы сравнить на идентичность два текста введенных населенных пунктов, находящиеся в соседних ячейках столбца С после сортировки. Если значения идентичны, то формула возвращает значение ячейки расположенной выше, если нет, то значение 0.
• введите эту формулу в диапазон ячеек F2:F5000 (если ожидается до 2500 введенных между НП расстояний);
• скопируйте весь диапазон ячеек с формулами в буфер обмена и произведите вставку вычисленных формулами значений в тот же диапазон через диалоговое окно Специальная вставка;
• произведите сортировку этого же диапазона по убыванию (не считая первую ячейку заголовком);
• выделите диапазон F2:F11 (десять ячеек с повторяющимися данными, если такие присутствуют в списке), скопируйте в буфер обмена, и после перехода на лист Города, выделив ячейку D14, произведите вставку;
• перейдя на лист База, удалите полученные в столбце F значения и вернитесь на лист Города.

Для записи макроса выполните указанную последовательность действий, после чего отредактируйте код VBA подпрограммы, как показано на рис. 9.8. в записанном макросе ПроверкаПовторения.

Обратите внимание на две последние строки выполняемой последовательности при механическом выполнении процедуры и как это реализовано в тексте подпрограммы ПроверкаПовторения. После копирования диапазона ячеек F2:F11 на листе База и для вставки этих данных на лист Города, лист Города не активизируется, а выполнение всей операции записано в одной строке, в которой указан адрес вставки с указанием имени листа. А лист Города активизируется уже после очистки содержимого области проведенных промежуточных вычислений (столбец F) на листе База.

Рис. 9.8. Подпрограмма ПроверкаПовторений

Если при выполнении подпрограммы будут обнаружены повторяющиеся записи, то список из десяти повторяющихся названий НП отобразится на листе Города (рис. 9.9.).

Рис. 9.9. Фрагмент рабочего листа Города с обнаруженным списком повторений

Поиск и удаление повторяющихся данных на листе База

Повторяющаяся информация в табличной базе данных на листе База при автоматической обработке информации может иметь неоднозначное толкование. Если указаны названия НП, а расстояния между ними различны, то функция ВПР выберет и укажет первое встретившееся ей значение. А, следовательно, присутствует возможность получения ошибочной информации.

Рис. 9.10. Диалоговое окно о невозможности вызова диалогового окна Форма

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

Самый оптимальный вариант, нажав кнопку База>> перейти на лист База и выполнив команду Данные/Форма вызвать диалоговое окно Форма.

Для того чтобы не проделывать каждый раз вручную эту трудоемкую операцию по вызову диалогового окна Форма перед переходом на лист База запишите макрос выполнения этой операции. Но здесь может ожидать неудача:

• если при активизации рабочего листа База табличный курсор не находится в области списка, то появится диалоговое окно (рис. 9.10.), предупреждающее, что эта операция невозможна;
• либо при появлении диалогового окна Форма и попытке остановить запись макроса, Excel не выполнит операцию остановки записи макроса.

Для записи макроса по вызову диалогового окна Форма, выполните следующую последовательность действий:

• активизируйте лист База;
• выделите любую ячейку списка в столбцах А:D;
• выполните команду Данные/Форма;
• в появившемся диалоговом окне Форма (рис. 9.11.) нажмите любую кнопку, например, Далее;
• остановите запись макроса.

В последующем в тексте кода VBA макроса можно будет удалить строку ссылки на активизацию ячейки. Важно чтобы в нем осталась строка вызова диалогового окна Форма (рис. 9.12.) после активизации листа База:

ActiveSheet.ShowDataForm

Рис. 9.11. Диалоговое окно Форма со списком База

Диалоговое окно Форма (в нашем примере оно называется по имени листа - База) предназначено для облегчения труда при работе со списками. В полях диалогового окна указано наименование каждого заголовка списка, данные, введенные в поля, могут редактироваться с последующим присвоением ячейкам списка этих значений. Если в каком-либо поле списка находится формула, то это поле будет показано на сером фоне и редакции не поддается.

В правом верхнем углу диалогового окна находится индикатор, показывающий общее число записей в списке и порядковый номер расположения выбранной записи. Просмотр списка можно осуществлять с помощью кнопок Назад и Далее, или с помощью полосы прокрутки. При нажатии на кнопку Добавить происходит добавление новой записи, которая размещается в конце списка, а с помощью кнопки Удалить - удаление выбранной записи.

Если добавление новых записей в нашем приложении осуществляется в автоматическом режиме, то удаление записи желательно проводить с помощью диалогового окна Форма.

Рис. 9.12. Подпрограмма УдалениеПовторений для вызова диалогового окна Форма

Для быстрого поиска строки, содержащей повторяющуюся запись, которую нужно удалить (рис. 9.9.) в диалоговом окне Форма нажмите на кнопку Критерии. Диалоговое окно Форма обновится.

Для поиска необходимой записи в поле, по которому будет происходить поиск, введите первые символы текста названия НП (рис. 9.13.) и после нажатия на кнопку Правка нажмите кнопку Далее. С помощью диалогового окна Форма произойдет фильтрация списка, по введенным символам и при необходимости удаления выбранной записи, нажмите на кнопку Удалить (рис. 9.11.).

Рис. 9.13. Диалоговое окно Форма с внесенными первыми символами поиска записи

При возникновении предупреждающего диалогового окна Excel (рис. 9.14.) подтвердите действие удаления записи нажатием на кнопку ОК.

Рис. 9.14. Диалоговое окно Excel с предупреждением об удалении записи из списка

Удаление записи при помощи диалогового окна Форма имеет преимущество перед удалением или очисткой строк, содержащими запись при помощи команд Правка/Очистить или Правка/Удалить.

В первом случае могут измениться формулы, в которых указаны ссылки на диапазон ячеек списка. Во втором случае список перестает быть списком, потому что теряет свои качества (см. глава 3).

Рабочий лист Ввод

Рабочий лист Ввод (рис. 9.15.) предназначен непосредственно для прокладывания маршрута через выбранные НП и расчета времени в пути по этому маршруту с учетом средней скорости и возможных задержек. Его условно можно разделить на четыре составляющих:

• область выбора маршрута;
• область ввода прогнозируемых задержках в пути;
• область вывода данных о времени прибытии и выбытия из населенных пунктов указанных в маршруте;
• справочный блок.

Область выбора маршрута

Область выбора маршрута (рис. 9.15.) в свою очередь состоит из:

• элементов управления Поле со списком для ввода наименований НП;
• области вывода названий НП и расстояний между ними;
• область вывода текста проложенного маршрута через выбранные НП и суммарного расстояния.

Элементы управления Поле со списком для ввода названий НП

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

Каждому элементу управления в диалоговом окне Формат элемента управления в поле Формировать список по диапазону задайте область В2:В200 на рабочем листе Города, в которую введены названия НП (рис. 9.2.). Элементы Поле со списком помещают результат (порядковый номер названия НП по списку на листе Города) в ячейки R1:R8.

Рис. 9.15. Область выбора маршрута рабочего листа Ввод

Область вывода названий НП и расстояний между ними

В ячейки F2:F8 вводятся названия НП, которые являются отправными пунктами части маршрута, а в ячейки I2:I8 - названия НП конечной точки части маршрута. Для автоматизированного ввода данных в ячейку F2 введена формула поиска названия НП в списке на листе Города:

=ВПР(R1;Города!$A$2:$B$200;2;ЛОЖЬ)

Искомым значением, по которому формулой производится поиск названия НП, является порядковый номер в ячейке R1, под которым этот НП находится в списке на листе Города. В остальные ячейки диапазона F3:F8 введены ссылки на ячейки, в которых находятся формулы поиска названий НП конечной точки предыдущей части маршрута. Например, в ячейке F3 находится ссылка на ячейку G2.

В ячейке G2 формула, которая производит поиск названия НП конечного пункта части маршрута, но если в списке НП указан текст НЕТ, то тогда возвращает значение "": =ЕСЛИ(ВПР(R2;Города!$A$2:$B$200;2;ЛОЖЬ)="НЕТ";"";ВПР(R2;Города!$A$2:$B$200;2;ЛОЖЬ))

В ячейке G3 находится формула аналогичная предыдущей:

=ЕСЛИ(F3="";"";ЕСЛИ(ВПР(R3;Города!$A$2:$B$200;2;ЛОЖЬ)="НЕТ";"";ВПР(R3;Города!$A$2:$B$200;2;ЛОЖЬ)))

но с помощью добавленной первой логической функции ЕСЛИ, при отсутствии значения в ячейке F3 названия НП возвращает значение "". Это позволяет не отражать в последующих ячейках никаких наименований, что делает работу по вводу данных более удобной.

В диапазоне ячеек Н2:Н8 находятся формулы соединения текста названия НП, находящиеся в столбцах F и G.

В ячейке I2 находится формула, которая при значении в ячейке G2 отличном от "" производит поиск расстояния между НП на листе База:

=ЕСЛИ(G2="";"";ВПР(H2;База!C:D;2;ЛОЖЬ))

Весь маршрут, с наименованиями населенных пунктов, через которые проложен путь, формируется в ячейке F11 по формуле, соединяющей названия НП через знак прочерка:

=СЦЕПИТЬ(F2;" - ";G2;" - ";G3;" - ";G4;" - ";G5;" - ";G6;" - ";G7;" - ";G8)

В ячейке I11 находится формула суммирования всех отрезков пути между НП, формируемая в диапазоне ячеек I2:I8.

В верхней части листа находится кнопка Обновить все формулы, задачей которой является обновление всех формул на листе Ввод с помощью назначенного ей макроса ОбновитьВсеФормулы. Этот макрос создается механической записью всех введенных на этом листе формул и последующей редакцией кода VBA.

Область ввода прогнозирующих составляющих

В разрабатываемом приложении предполагается, что автомобиль будет двигаться по всему маршруту со средней скоростью, значение которой вводится в ячейку I13 (рис. 9.16.). При желании возможен ввод средней скорости на каждом отрезке маршрута, для чего в расчетах времени прохождения каждого отрезка пути, задайте ссылку не на ячейку I13, а на диапазон выбранных пользователем ячеек, с введенными в них значениями средней скорости. Возможен также ввод не средней скорости движения, а предполагаемого времени нахождения в пути на каждом отрезке маршрута, для чего потребуется незначительное изменение формул. Причем можно создать дополнительную табличную базу данных с указанием средней скорости (времени) прохождения каждого отрезка введенных расстояний между НП.

В ячейки диапазона F16:F22 введите ссылки на ячейки диапазона G2:G8, указывающие название НП, являющегося конечным пунктом отрезка пути.

В области I16:I22 введите с клавиатуры время в часах необходимое для остановок на каждом отрезке пути или городе, являющимся конечным пунктом одного из отрезков пути, название которого указано в диапазоне F16:F22. Например, на обед, на отдых, время на разгрузку/погрузку в этом пункте и так далее.

И в ячейку I24 вводится предполагаемые дата и время выезда из начальной точки отправления.

Рис. 9.16. Область ввода прогнозных данных рабочего листа Ввод

Область определения времени прибытия и отправления

Область расчета прибытия в НП и отправления из них, расположена в диапазоне F26:I33 (рис. 9.17.).

В диапазон ячеек F27:F33 введите ссылки на ячейки диапазона F16:F22, в которых указаны названия НП конечных пунктов отрезков пути.

Рис. 9.17. Область расчета времени прибытия в НП и выезда из них

В диапазоне ячеек Н27:Н33 указано время прибытия в НП, являющийся конечным на отрезке пути, название которого указано в диапазоне ячеек F27:F33. Формула в ячейке Н27:

=I24+(I2/$I$13)/24

к дате и времени выезда, указанного в ячейке I24, добавляет время в пути на этом отрезке. Время пути определяется в часах делением расстояния этого отрезка маршрута, определенного в ячейке I2, на среднюю скорость, введенную в ячейку I13. Но если результат деления, полученный в часах прибавить к дате, введенной в ячейке I24, то Excel воспримет целое число часов за целое количество дней. Поэтому полученное значение нужно разделить на количество часов в сутках - равное 24.

Время отправления из первого НП определяется в ячейке I27 по формуле:

=H27+I16/24

и равно сумме времени прибытия в этот пункт (ячейка Н27) и количества часов на остановки, значение часов которых введено в ячейку I16 и разделенному на 24 часа.

Формулы в других ячейках области аналогичны, только ссылка производится на ячейку времени прибытия/выбытия предыдущей части маршрута.

Справочный блок

На практике может сложиться ситуация, при которой названия НП введены в список НП на листе Города, а расстояние между ними не введено в список на лист База. В таком случае формулы в диапазоне ячеек I2:I8 возвращают значение ошибки #Н/Д.

Рис. 9.18. Элементы управления для определения НП между которыми указано расстояние

Для просмотра названий НП, с которыми введены расстояния на листе База, создайте еще один элемент управления Поле со списком, который раскрывает список по заданному диапазону ячеек В2:В200 на листе Города и помещает результат в ячейку R10 (порядковый номер выбранного НП в раскрывающемся списке). На рабочем листе Ввод этот элемент управления расположен в правом верхнем углу и ему назначен макрос ПоискСвязанныхГородов (рис. 9.19.). Эта подпрограмма формирует в столбце Р листа База список НП, с которыми у искомого (выбранного) НП введено расстояние между ними на листе База.

Просмотр сформированного списка названий НП макросом ПоискСвязанныхГородов, осуществляется с помощью следующего элемента управления Список. Для этого после его создание, вызвав диалоговое окно Формат элемента управления задайте ссылку на диапазон ячеек Р2:Р200 в поле Формировать список по диапазону вкладки Элемент Управления.

Создание подпрограммы для определения названий НП, с которыми указаны расстояния между ними

Алгоритм подпрограммы формирования списка названий НП, расстояние между которыми и искомым НП введено на рабочем листе База, довольно похож на подпрограмму поиска повторяющихся данных (рис. 9.8.).

Рис. 9.19. Подпрограмма ПоискСвязанныхГородов

Последовательность процедуры формирования списка следующая:

• при обращении к элементу управления Поле со списком и нажатии на кнопку, раскрывается список, в котором представлены все названия НП, введенные в диапазон ячеек В2:В200 рабочего листа Города;
• при выборе названия НП элем