|
Производственная деятельность предприятий невозможна без использования транспорта в своей производственной деятельности. А раз есть автомобили, то существует и необходимость учета расхода топлива. Довольно часто для этого применяют 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
Область расчета расхода топлива и контроля правильности введенных данных
Область расчета расхода топлива с учетом всех поправочных коэффициентов расположена в диапазоне ячеек А17:D33 и приведена на рис. 8.23. с числовыми значениями и рис. 8.24. с формулами расчета. Эту область условно можно разделить на несколько частей.
В ячейке D17 осуществляется расчет пробега автомобиля, как разность между показаниями спидометра при выезде и возвращении в гараж. В ячейке D18 производится расчет линейного расхода топлива как произведение пробега автомобиля на линейную норму расхода топлива.
В ячейках D19 и D20 производится расчет дополнительного расхода топлива независимого от пробега автомобиля. Формулы производят вычисления при условии отмеченных элементов управления Флажок — работы обогревателя или выполнения транспортной работы, и введенных данных в ячейки D13 и D14.
Формулы в ячейках D21:D23 проводят вычисления дополнительного расхода топлива, зависящего от линейного пробега автомобиля и при активизации соответствующих элементов управления Флажок.
В ячейках D24:D26 введены простые формулы, производящие умножение вычисленного значения линейного расхода топлива автомобиля на соответствующий коэффициент. В качестве коэффициента указана ссылка на имя ячейки столбца J, значением которой управляют элементы управления объединенные в группы элементов.
В ячейках D29 и D30 находятся формулы определения перерасхода или экономии топлива. При перерасходе топлива, абсолютная величина перерасхода отображается в ячейке D30. При экономии — в ячейке D29.
В ячейку А32 введена формула контроля. Она поможет избежать ошибок при вводе данных. Например, эта формула анализирует одновременность введенных параметров в ячейку D22 (работа за городом) и D25 (работа в городе), что на практике невозможно. Если указан дополнительный расход топлива, только в одной из этих ячеек или данные отсутствует в обоих, то тогда функция ИЛИ возвращает значение ИСТИНА, и функция ЕСЛИ возвращает значение пробела.
Если же в ячейках D22 и D25 одновременно находятся значения отличные от нуля, то тогда формируется текст Определитесь! Работа в городе или за его пределами!, который привлечет внимание и позволит пользователю избежать ввода ошибки.
Формула контроля в ячейке А33 предназначена для анализа содержимого ячейки D15. Если ячейка D15 не содержит никакого значения, то будет сформирован текст Введите расход по путевому листу!.
Если же обнаружено значительное различие между введенным значением расхода топлива по путевому листу в ячейке D15 и определенному расходу топлива по расчету, то будет сформирован текст Слишком большая погрешность!, который говорит что возможно присутствие ошибок. Для формирования текста о погрешности в формулу введено значение расхождения равное 15%, которое может быть при необходимости изменено.
Рис. 8.23. Область расчета дополнительного расхода топлива с числовыми данными
Рис. 8.24. Область расчета дополнительного расхода топлива с формулами
Для защиты от случайного удаления формул, на листе ВводДанных создана кнопка Обновление формул с назначенным ей макросом ОбновлениеФормул, который восстанавливает введенные формулы в ячейках, не задействованных другими элементами управления.
Рабочий лист Контроль
Если в лист ВводДанных введена вся необходимая информация, то завершение операции ввода данных осуществляется нажатием на кнопку Просмотр записи (рис. 8.8.) и при этом производится переход на лист Просмотр (рис. 8.25.).
Задача рабочего листа Просмотр сгруппировать и расположить данные в строке 2 в таком виде, в каком они будут переноситься в лист БазаДанных (рис. 8.6.). Строка заголовка этой таблицы идентична таблице на листе БазаДанных.
Практически все формулы присутствующие на данном листе — ссылки на ячейки листа ВводДанных. Отличается только содержимое ячейки А2, введенная в которую формула показана в строке формул. Она объединяет текст данных марки машины, государственного номера и фамилии водителя.
Рис. 8.25. Рабочий лист Контроль
Если данные на листе Просмотр устраивают пользователя, то нажмите на кнопку Перенос в базу данных, которой назначен макрос ПереносДанныхБаза (рис. 8.26.). Задачей этого макроса является:
- скопировать диапазон ячеек с данными на листе Контроль;
- переход на лист БазаДанных и определение первой пустой строки;
- выделение в определенной строке ячейки в столбце А и вставка значений скопированной области ячеек с листа Контроль;
- сортировка по убыванию табличной базы данных по столбцу F учитывая то что первая строка является строкой заголовка;
- переход на лист ВводДанных и очистка содержимого диапазонов ячеек с введенными данными по путевому листу.
Таким образом лист ВводДанных готов для ввода следующей "порции" данных.
Рис. 8.26. Подпрограмма ПереносДанныхБаза
Формирование отчетов
Если существует какая-либо база данных, то, как правило, возникает и необходимость формирования отчетов. Отчеты могут быть самые разнообразные. Например, в разрезе рассматриваемого нами примера:
- когда и сколько было израсходовано топлива;
- есть ли отклонения от утвержденных норм использования топлива, как в сторону перерасхода, так и в сторону экономии;
- какой пробег у каждого автомобиля за определенный промежуток времени и так далее.
Рассмотрим автоматизацию процесса по подготовке отчетов на двух примерах:
- отчет по общему расходу, экономии и перерасходу топлива за произвольный промежуток времени;
- отчет по каждому водителю с указанием даты выезда и возвращения в гараж, и с указанием пробега автомобиля.
Формирование отчетов вручную
Довольно часто имея табличную базу данных, формирование отчетов происходит в полуручном режиме. Из этих табличных баз данных выделяются отдельные фрагменты (столбцы, строки), копируются, вставляются в другие таблицы. Из этих вновь созданных массивов информации и формируются отчеты. Операции, как правило, однотипные и повторяющиеся, но каждый месяц выполняются заново.
Поэтому если приходится создавать самостоятельно табличные базы данных и на основании их в дальнейшем формировать отчеты, то желательно потратить некоторое незначительное время на автоматизацию этого процесса.
Отчет по расходу топлива за произвольный интервал времени
Предположим поставлена задача — автоматическое формирование отчета, которые должен содержать данные по всем автомобилям, которые выезжали из гаража и возвращались в него за указанный промежуток времени. Отчет должен содержать:
- модель, регистрационный номер автомобиля и фамилию водителя;
- дата выезда из гаража;
- дата возвращения в гараж;
- расход топлива по нормам;
- экономия и перерасход топлива по каждому путевому листу.
Ручной метод подготовки отчета
Наиболее простой способ — создание точной копии листа БазаДанных, который после последующей обработки и будет являться отчетом. Обработка заключается в следующем:
- сортировка списка по дате выезда из гаража и потом по дате возвращения в гараж;
- удаление "лишних" столбцов и строк;
- придание созданной таблице формы отчета.
Недостаток — достаточно много механически выполняемой работы. При изменении временного интервала, за который готовился отчет, нужно всю работу повторить заново.
Автоматизированная форма подготовки отчета
Прежде чем непосредственно приступить к процессу автоматизации, необходимо определить какие основные критерии задают формирование отчета. Затем при необходимости ввод формул. И самый последний этап — последовательность выполнения действий по формированию отчета.
Форма создаваемого отчета на листе ОтчетПериод показана на рис. 8.27. с формулами и на рис. 8.28. с числовым примером.
Исходя из поставленной задачи основным критерием для создания отчета — заданный временной интервал, за который формируется отчет. Даты указывающие интервал выезда и возвращения в гараж будут вводиться в ячейки В2 и С2.
Формулы подготовки отчета
В ячейку А7 введена формула, которая сравнивает:
- дату, введенную в ячейку С2 и дату возвращения в гараж первой строчки созданной табличной базы данных (ячейка С2 листа БазаДанных);
- если дата в ячейке С2 более даты на листе БазаДанных, то тогда функция ЕСЛИ возвращает значение 0;
- если дата в ячейке С2 менее или равна дате на листе БазаДанных, то тогда вторая функция ЕСЛИ сравнивает дату в ячейке В2 с датой в ячейке В2 на листе БазаДанных;
- если начальная дата формирования отчета равна или более даты на листе БазаДанных, то тогда формула возвращает значение содержащееся в ячейке А2 листа БазаДанных или в противном случае возвращается значение 0.
Все остальные формулы строки 7 сравнивают значение в ячейке А7 со значением 0. Значение 0 указывает на то, что эта строка в табличной базе данных не представляет интерес для отчета. И поэтому если это условие удовлетворяется, то и эти формулы возвращают значение 0.
Рис. 8.27. Лист ОтчетПериод с формулами создаваемого отчета
Рис. 8.28. Лист ОтчетПериод с числовым примером создаваемого отчета
Если же условие не удовлетворяется, то тогда формулы возвращают значения содержимого ячеек, на которые указаны ссылки в этих формулах.
Последовательность формирования отчета
Если строку 7 (или диапазон ячеек) с формулами скопировать в нижние строки таблицы (например, до строки 1000), то формулы "выхватят" только указанный диапазон времени, который вероятнее всего будет находиться где-то в середине таблицы. Кроме того, файл, содержащий такое количество формул, будет громоздок.
Следующая предстоящая задача — сгруппировать эти данные. В Excel это возможно осуществить применив метод сортировки. Но процесс сортировки формул не приведет к желательному результату, потому что после сортировки произойдет перерасчет формул, и необходимые для отчета данные по прежнему будут находиться где-то "в середине таблицы".
Для формирования отчета нужно заменить формулы вычисленными ими значениями и применить сортировку по убыванию, например, по столбцу А, что позволит расположить выбранные данные в верхней части таблицы (отчета).
Макрос СоздатьОтчетТопливо, выполняющий формирование отчета, показан на рис. 8.29. Он производит ввод формул в строке 7, последующее копирование формул в заданный диапазон таблицы, замену формул на вычисленные ими значения и сортировку выделенного диапазона по убыванию.
Рис. 8.29. Макрос СоздатьОтчетТопливо, выполняющий формирование отчета по топливу
Отчет по водителям
Следующий пример создаваемого отчета показан на листе ОтчетВодитель с введенными формулами (рис. 8.30.) и числовым примером (рис. 8.31.).
Отчет должен содержать следующие данные:
- автомобиль и его государственный номер;
- дата выезда из гаража;
- дата возвращения в гараж;
- пробег автомобилей по спидометру
- и формироваться по введенной в ячейку D3 фамилии водителя.
Для ввода фамилии водителя скопируйте созданный элемент управления Список ввода фамилии водителя, после чего произведите вставку скопированного на лист ОтчетВодитель. Аналогичную операцию произведите с формулой ячейки D3 на листе ВводДанных.
Последовательность действий создания макроса по формированию отчета аналогична предыдущему. Отличие заключается только в формуле анализа данных, введенной в ячейку А7 листа ОтчетВодитель.
Формула в ячейке А7 производит поиск текста фамилии водителя, введенной в ячейку D3 листа ОтчетВодитель в тексте, находящемся в ячейке А2 листа БазаДанных. Поиск текста основан на применении функции НАЙТИ, которая при нахождении искомого текста возвращает начальную позицию совпадения.
Сложность заключается в том, что если функция НАЙТИ не находит заданный текст, то она возвращает значение ошибки #ЗНАЧ!. Поэтому функция НАЙТИ помещена внутрь функции ЕОШИБКА, которая возвращает значение ИСТИНА, если ее аргумент содержит значение ошибки.
Функция ЕСЛИ возвращает значение 0 при отсутствии текста фамилии водителя в тексте, содержавшемся в ячейке А2 на листе БазаДанных.
В противном случае формула возвращает левые символы текста, находящегося в ячейке А2 листа БазаДанных, в количестве, уменьшенном на количество символов, находящихся в этом тексте справа до первой буквы фамилии водителя. Для этого применяется функция ЛЕВСИМВ.
Рис. 8.30. Рабочий лист ОтчетВодитель с формулами, применяемыми при создании отчета
Рис. 8.31. Рабочий лист ОтчетВодитель с числовым примером
Создание отчета выполняет макрос СоздатьОтчетВодитель (рис. 8.32.). Процедура выполнения его аналогична предыдущему макросу.
Рис. 8.32. Макрос СоздатьОтчетВодитель
Применяя методики формирования отчетов, описанные выше, можно довольно быстро создавать любые удобные формы которые позволят экономить в будущем немало времени. Важно спланировать последовательность выполняемых действий.
Итоги
Учет расхода топлива необходим для определения эффективности использования предприятием ресурсов, в частности денежных средств потраченных на топливо. А для определения этой эффективности требуется потратить определенные ресурсы на учет их использования, которые являются производными от количества обрабатываемых путевых листов. Задача созданного приложения увеличить эффективность работы специалиста по этому учету и снизить в целом издержки предприятия.
В этой главе к автоматизации процесса был совершен подход совсем с другой стороны, но, тем не менее, использующий элементы и методику, освоенные в предыдущих главах.
Как и прежде используются табличные базы данных для получения из них необходимой информации, но теперь уже не одна база данных, как ранее, а несколько. Причем пользователь сам выбирает в автоматизированном режиме, какая ему информация нужна непосредственно.
Следовательно, основное направление этой главы, создание приложений, используя для выбора данных элементы управления. А вычисления производятся по заданному алгоритму, используя выбранную пользователем для расчетов информацию. А введенные формулы контроля за вводимой информацией помогут избежать механических ошибок.
Следующий основной вывод из материала этой главы — автоматизация подготовки самых различных отчетов в детализации определяемом пользователем, но требующемуся руководителю. И здесь немаловажная деталь — большинство отчетов относятся тоже к спискам. Поэтому отчет можно представить как список с позиции Excel, сформированный по заданным пользователем правилам.
|
