Область ввода марки, номера автомобиля и фамилии водителя

Данные о модели автомобиля вводятся в ячейку D1 (рис. 8.8.). Можно вводить данные с клавиатуры, но подобный метод требует значительного времени на выполнение этой операции и внимательности. Поэтому введите в ячейку D1 формулу:

=ВПР(E1; Модель! $A$3: $B$20; 2; ЛОЖЬ)

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

Рис. 8.8. Фрагмент рабочего листа ВводДанных с областью ввода данных марки автомобиля, государственного регистрационного номера и данных о водителе

Элемент управления Список

С целью облегчения ввода наименования модели автомобиля примените элемент управления Список.

После создания элемента управления вызовите командой Формат/Элемент управления или комбинацией клавиш Ctrl+1 диалоговое окно Формат элемента управления. На вкладке Элемент управления в поле Формировать список по диапазону выделите область ячеек В3: В20 на рабочем листе Модель, в которую введены названия моделей автомобилей.

В поле Помещать результат в ячейку укажите адрес ячейки Е1.

Рис. 8.9. Диалоговое окно Формат элемента управления для элемента Список

Для выбора модели автомобиля нажмите на кнопку открытия списка и выберите ее наименование в открывшемся списке (рис. 8.10.). В зависимости от очередности, которую занимает этот автомобиль в списке, в ячейку Е1 вводится его порядковый номер. А уже по этому номеру формула в ячейке D1 произведет поиск марки на рабочем листе Марка.

Рис. 8.10. Элемент Список с открытым списком моделей автомобилей

Для того чтобы застраховаться от случайного удаления формулы в ячейке D1 запишите макрос, который будет создавать эту формулу (рис. 8.11.) и назначьте его элементу управления Список. При каждом обращении к этому элементу управления формула будет обновляться. Аналогичные формулы находятся в ячейке D2 и D3 соответственно:

=ВПР(E2; Водители! $D$4: $E$20; 2; ЛОЖЬ)

=ВПР(E3; Водители! $A$4: $B$20; 2; ЛОЖЬ)

которыми управляют следующие элементы управления Список:

• для выбора регистрационного номера автомобиля;
• фамилии водителя.

Рис. 8.11. Макросы создающие формулы в ячейках D1: D3

Поиск информации по выбранным данным

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

На рис. 8.6. показана табличная база данных на рабочем листе БазаДанных по введенным ранее путевым листам. В столбце А находится текст, объединяющий марку автомобиля, государственный регистрационный номер и фамилию водителя.

В ячейке D5 листа ВводДанных находится формула поиска последнего показания спидометра автомобиля, находящегося в табличной базе данных на основании заданных условий поиска:

=ВПР(СЦЕПИТЬ($D$1; " Гос. Номер "; $D$2; " "; $D$3); БазаДанных! $A$2: $F$1000; 6; ЛОЖЬ)

В качестве искомого значения использующегося для поиска информации используется текст объединенный функцией СЦЕПИТЬ.

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

Еще раз обратите внимание - поиск данных осуществляется на основании комбинации объединенных данных: выбранной модели, регистрационного номера и фамилии водителя. Это условие может не соответствовать действительности, если на данном автомобиле работает несколько водителей, но этот тот частный случай, который можно устранить, если в объединенный функцией СЦЕПИТЬ текст в столбце А на листе БазаДанных, не вводить фамилию водителя.

В ячейке D6 (последняя дата возвращения в гараж) находится аналогичная формула, но в третий аргумент введено значение 3 - поиск в столбце С листа БазаДанных.

И в ячейке D7 (линейная расхода топлива норма на 100 км) введена формула, которая определяет базовую норму расхода топлива по выбранной модели автомобиля из таблицы на листе Модель:

=ВПР(ВводДанных! $D$1; Модель! $B$3: $C$100; 2; ЛОЖЬ)

Блок ввода данных по путевому листу

Следующий блок - область ввода данных, находится в диапазоне ячеек А9: D17 (рис. 8.12.).

Рис. 8.12. Фрагмент рабочего листа ВводДанных с областью ввода показаний по путевому листу

Ввод показаний спидометра

В ячейку D9 листа ВводДанных вводятся показания спидометра при выезде из гаража. Эту информацию можно вводить и с клавиатуры, но если показание спидометра, определенное в ячейке D5 по возвращению в гараж по предыдущему путевому листу соответствует действительности, то тогда достаточно нажать на кнопку ПереносКМ (рис. 8.8.). Этой кнопке назначен макрос ПереносКилометраж (рис. 8.15.), производящий ввод в ячейку D9 значения показания спидометра, определенного формулой, находящейся ячейке D5.

В ячейку D10 вводится показание спидометра при возвращении в гараж, а в ячейку D15 - расход топлива, указанные в путевом листе. Для того чтобы не вводить эти данные непосредственно в ячейку, с помощью функции InputBox (рис. 8.15.) создайте пользовательские диалоговые окна Спидометр (рис. 8.13.) и ГСМ (рис. 8.14.). Введенные в них с клавиатуры показания будут присвоены соответствующим ячейкам. О создании пользовательских диалоговых окон было написано в главе 5.

Рис. 8.13. Диалоговое окно Спидометр для ввода показания спидометра по возвращению в гараж

Рис. 8.14. Диалоговое окно ГСМ для ввода расхода топлива по путевому листу

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

Ввод дат

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

Следовательно, дату, определенную в ячейке D6 из табличной базы данных необходимо увеличить на один день. Тогда при нажатии на кнопку Перенос дата (рис. 8.8.) в ячейки D11 (дата выезда из гаража) и D12 (дата возвращения в гараж) по обрабатываемому путевому листу будет введена дата, увеличенная на один день по отношению к предыдущему путевому листу. Макрос ПереносДата (рис. 8.15.) выполнит эту операцию. При необходимости, введенные даты можно откорректировать непосредственно в ячейках с клавиатуры. Или же ввести в макрос строку кода VBA с вызовом функции InputBox для ввода дат выезда и возвращения в пользовательские диалоговые окна и последующему присвоению введенных значений ячейкам D11 и D12.

Блок элементов управления для ввода данных по дополнительному расходу топлива

На листе ВводДанных находятся сгруппированные элементы управления, с помощью которых осуществляется ввод дополнительного расхода топлива и их в свою очередь можно разделить на две части:

• самостоятельные;
• объединенные в группы элементов.

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

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

Самостоятельные элементы управления для ввода дополнительного расхода топлива не зависящего от пробега автомобиля

Использование элементов управления Флажок для ввода текстовой информации приводилось в седьмой главе. В этом приложении с помощью этих элементов необходимо ввести в ячейки D13 и D14 соответственно:

• количество часов работы обогревателя;
• объем выполненной транспортной работы.

Рассмотрим последовательность ввода данных и их последующей обработки. Этим элементам управления назначены макросы ОбогревЧасов и ТранспортнаяРабота (рис. 8.16.), которые при обращении к элементам управления с помощью функций InputBox активизируют пользовательские диалоговые окна Обогрев и Транспортная работа для последующего ввода данных в ячейки D13 и D14. Но эти диалоговые она активизируются как при установке в этих элементах управления флажка, так и при его снятии.

Эти элементы управления с помощью диалогового окна Формат элементов управления связаны с ячейками J13 и J14, в которые в зависимости от состояния этих элементов управления, может быть введено содержимое - ЛОЖЬ или ИСТИНА. Этим ячейкам присвоены имена Обогрев и Работа соответственно.

И если эти элементы управления активизированы и в ячейках J13 и J14, находятся значения ИСТИНА, то введенные в пользовательские диалоговые окна данные будут отображены не только в ячейках D13 (часов на обогрев) и D14 (объем выполненной транспортной), но и в формулах в ячейках D19 и D20 (см. рис. 8.24.), которые основаны на функции ЕСЛИ. В первом аргументе функции ЕСЛИ находится имя ячейки и если содержимое этой ячейки ИСТИНА, то формула выполняет расчет дополнительно израсходованного топлива, в противном же случае возвращает значение 0.

Рис. 8.16. Макросы ОбогревЧасов и ТранспортнаяРабота для вызова пользовательских диалоговых окон, с помощью которых производится ввод данных

Самостоятельные элементы управления для ввода дополнительного расхода топлива зависящего от пробега автомобиля

Три элемента управления Флажок (рис. 8.17.), вводят в расчеты дополнительный расход топлива зависящий от линейного пробега автомобиля и им присвоено название, соответствующее их назначению:

• частые технологические остановки;
• битумное покрытие дороги за пределами города;
• на автомобили, имеющие срок эксплуатации более 8 лет.

Эти элементы управления связаны с ячейками J9: J11, которым присвоены имена: Остановки, Битум и СтарыйАвтомобиль. Если элементы управления активизированы, то в этим ячейкам будет присвоено значение ИСТИНА (см. рис. 8.17.), в противном случае значение ЛОЖЬ.

Этим же элементам управления назначены три макроса (рис. 8.18.), которые при обращении к элементам управления вводят формулы в ячейки D21: D23 (см. рис. 8.24.).

Рис. 8.17. Элементы управления для ввода данных

Рис. 8.18. Макросы обновления формул для учета дополнительного расхода топлива

Группы элементов управления

Прежде чем продолжить создание приложения, рассмотрим возможность создания в Excel групп элементов управления. Для этого рассмотрите пример приведенный ниже.

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

Рис. 8.19. Рабочий лист с созданными элементами управления Переключатель, связанных с ячейкой Е4

Если же с помощью элемента управления Рамка заключить часть элементов Переключатель в группу (рис.8.20.), то переключатели этой группы элементов управления не смогут управлять связанной ячейкой Е4. Если одному из переключателей Группы1 задать связь с ячейкой Е9, то тогда содержимое ячейки Е9 будет изменяться от одного до трех, в зависимости от активизации переключателей Перекл.3 - Перекл.5., а два первых переключателя будут управлять значением содержимого ячейки Е4.

Объединение элементов управления в группу осуществляется элементом управления Рамка, который создается с помощью панели инструментов Формы (рис. 4.6.). Для этого после выбора этого элемента управления на панели инструментов Форма обведите контур вокруг группы элементов управления Переключатель.

Рис. 8.20. Создание группы переключателей

Если же какой-то переключатель, например, Перекл.4 перетянуть за пределы Группы1 (рис. 8.21.), то адрес связанной ячейки автоматически изменяется с Е9 на Е4. То есть, переключатель " перешел" в другую группу.

Рис. 8.21. Переключатель выведен за пределы Группы1

Создание групп элементов управления при учете дополнительного расхода топлива

Это элементы управления, объединенные в группу для расчета дополнительного расхода топлива, которые имеют двухуровневую систему учета.

Рассмотрим группу элементов управления Температура.

Первый уровень - показывает, что данный коэффициент в расчетах присутствует. Для этого используется элемент управления Флажок. Этот элемент связан с ячейкой J15, которой присваивается значение ИСТИНА при активизации элемента управления. В свою очередь это значение ИСТИНА служит разрешением для формулы в ячейке J17 проводить дальнейший расчет.

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

• Температура от 0 до минус 5 градусов;
• Температура от минус 5 до минус 10 градусов;
• Температура от минус 10 до минус 15 градусов;
• связаны с ячейкой J16 и изменяют ее содержимое от единицы до трех.

Ячейка J17, с присвоенным ей именем Температура содержит формулу:

=ЕСЛИ(J15; ЕСЛИ(J16=1; Температура5; ЕСЛИ(J16=2; Температура10; Температура15)); 0)

Если в ячейке J15, которой управляет элемент управления Флажок с именем Температура, находится значение ЛОЖЬ (опция не отмечена), то тогда первая функция ЕСЛИ в формуле в ячейке J17 возвращает значение 0. Это и есть первый уровень управления - присутствие этого коэффициента в расчетах при значении ИСТИНА в ячейке J15.

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

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

Следующие две объединенные группы элементов управления: Город и Горы функционируют как и группа Температура.

В ячейку J24 (имя ячейки Город) введена формула:

=ЕСЛИ(J22; ЕСЛИ(J23=1; Город1; ЕСЛИ(J23=2; Город2; Город3)); 0)

и похожая формула в ячейке J32 (имя ячейки Горы):

=ЕСЛИ(J30; ЕСЛИ(J31=1; ГорнМест1500; ЕСЛИ(J31=2; ГорнМестн2000; 0)))

Всем элементам управления, объединенным в группы, назначены макросы, создающие формулы в ячейках, которые они контролируют - J17, J24 и J32. Макросы показаны на рис. 8.22.

Рис. 8.22. Макросы ввода формул в ячейки J17, J24 и J32