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

Лаб.5. Применение электронных таблиц для учета расхода топлива

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

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

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

Рис. 8.1. Схема потоков информации при вводе и обработке данных, и создании различных отчетов по этим данным

На рис. 8.1. приведена схема потоков информации в создаваемом приложении, необходимая для организации его функционирования при вводе данных по путевым листам и последующем создании различных отчетов по введенным данным.

Рабочий лист Модель

На рабочем листе Модель (рис. 8.2.) в области А2:С7 находится таблица, предназначенная для ввода наименования модели автомобиля и его базовой линейной нормы расхода топлива на 100 км. пробега автомобиля. Данные в нее вводятся на основании соответствующих нормативов. Представленная таблица при необходимости может быть расширена в зависимости от имеющегося на предприятии автопарка.

Рис. 8.2. Рабочий лист Модель

Рабочий лист Нормы

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

Рис. 8.3. Рабочий лист Нормы

Таблица на листе Нормы занимает область А2:С14 и состоит из трех составляющих:

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

Для присвоения имен ячейкам с введенными данными, выделите диапазон ячеек В2:С14 и с помощью комбинации клавиш Ctrl+Shift+F3 вызовите диалоговое окно Создать имена и нажмите кнопку ОК. При необходимости этот список может быть расширен

Таблица имен ячеек используемых в приложении

В создаваемом приложении будет присваиваться довольно большое количество имен, поэтому есть смысл на отдельном листе поместить эти имена и адреса ячеек, которым они будут присвоены. Для этого на выделенном для этой цели рабочем листе Имена выделите ячейку, с которой будет формироваться этот список, и выполните команду Вставка/Имя/Вставить или нажмите клавишу F3. В появившемся диалоговом окне Вставка имени (рис. 7.24.) нажмите на кнопку Все имена, после чего произойдет вставка всех имен присвоенных в этом приложении. В качестве справки имена, используемые в книге Расход топлива, показаны на рис. 8.4.

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

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

На рабочем листе Водители (рис. 8.5.) находятся два списка:

в столбцах А:В списочный состав водителей;
в столбцах D:Е государственные номерные знаки автомобилей, находящихся на предприятии.

Рис. 8.5. Рабочий лист Водители

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

Рабочий лист БазаДанных показан на рис. 8.6. и предназначен для сбора и хранения информации по вводимым путевым листам. На нем слева направо расположены следующие данные:

Модель автомобиля, государственный регистрационный. номер, фамилия и инициалы водителя;

Дата выезда из гаража;
Дата возвращения в гараж;
Количество дней действия путевого листа (дней);
Показание спидометра при выезде из гаража (км.);
Показание спидометра при возвращении (км.);
Пробег (км.);
Часов на обогрев (час.);
Выполнение транспортной работы (т-км);
Расход ГСМ согласно путевого листа (л.);
Линейная норма на пробег (л.);
Обогреватель (л.);
Транспортная работа (л.);
Частые технологические остановки (л.);
Битумное покрытие за городом (л.);
Срок эксплуатации автомобиля более 8 лет (л.);
За работу при температуре (л.);
За работу в городе (л.);
За горную местность (л.);
Общий расход ГСМ по нормам (л.);
Экономия ГСМ (л.);
Перерасход ГСМ (л.).

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

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

Рабочий лист ВводДанных

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

Составляющие расхода топлива

Составляющие нормативного расхода топлива можно определить по формуле:

ОРТ = ЛРТ + (ЛРТ х ПК) + ДРТ

, где
ОРТ - общий расход топлива;
ЛРТ - линейный расход топлива;
ПК - поправочные коэффициенты
ДРТ - дополнительный расход топлива.

Линейный расход топлива (ЛРТ) прямо пропорционально зависит от пробега автомобиля и линейной нормы расхода топлива на 100 км пробега на эту модель автомобиля. Пробег автомобиля определяется показаниями спидометра, как разность между его показаниями при возвращении в гараж и выезде из него.

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

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

В свою очередь поправочные коэффициенты бывают:

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

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

Вид рабочего листа ВводДанных

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

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

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

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

Желательно ввести в лист и контролирующие функции, позволяющие вовремя заметить введенные ошибки.

Рабочий лист ВводДанных показан на рис. 8.7. и будет рассмотрен поблочно в последовательности вводимых данных и выполняемых операций вычисления.

Рис. 8.7. Рабочий лист ВводДанных

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

Данные о модели автомобиля вводятся в ячейку 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

Дата добавления: 2018-06-27; просмотров: 377; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!