На этом шаге мы рассмотрим особенности некоторых областей этого рабочего листа.

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

    Данные о модели автомобиля вводятся в ячейку D1 (рисунок 1).

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

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

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

    Рассмотрим, как можно автоматизировать этот процесс.

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

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

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

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

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

    Для выбора модели автомобиля нажмите на кнопку открытия списка и выберите ее наименование в открывшемся списке (рисунок 3).

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

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

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

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

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

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

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

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

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

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

    На рисунке 5 показана табличная база данных на рабочем листе БазаДанных по введенным ранее путевым листам.

Рис.5. Рабочий лист БазаДанных

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

    В ячейке 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 (рисунок 6).

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

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

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

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

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

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

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

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

Ввод дат

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

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

    На следующем шаге мы продолжим изучение этого вопроса.

Предыдущий шаг Содержание Следующий шаг