Создание групп элементов управления при учете дополнительного расхода топлива
Это элементы управления, объединенные в группу для расчета дополнительного расхода топлива, которые имеют двухуровневую систему учета.
Рассмотрим группу элементов управления Температура.
Первый уровень - показывает, что данный коэффициент в расчетах присутствует. Для этого используется элемент управления Флажок. Этот элемент связан с ячейкой 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, ссылкой на именованную ячейку, возвращают значение коэффициента дополнительного расхода топлива для данной температуры, которые введены в таблицу на рабочем листе Нормы.
Следующие две объединенные группы элементов управления: Город и Горы функционируют как и группа Температура.
В ячейку J24 (имя ячейки Город) введена формула:
=ЕСЛИ(J22;ЕСЛИ(J23=1;Город1;ЕСЛИ(J23=2;Город2;Город3));0)
и похожая формула в ячейке J32 (имя ячейки Горы):
=ЕСЛИ(J30;ЕСЛИ(J31=1;ГорнМест1500;ЕСЛИ(J31=2;ГорнМестн2000;0)))
Всем элементам управления, объединенным в группы, назначены макросы, создающие формулы в ячейках, которые они контролируют - J17, J24 и J32.
Область расчета расхода топлива и контроля правильности введенных данных
Область расчета расхода топлива с учетом всех поправочных коэффициентов расположена в диапазоне ячеек А17:D33 и приведена на рис. с числовыми значениями и рис. с формулами расчета. Эту область условно можно разделить на несколько частей.
В ячейке 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%, которое может быть при необходимости изменено.
Для защиты от случайного удаления формул, на листе ВводДанных создана кнопка Обновление формул с назначенным ей макросом ОбновлениеФормул, который восстанавливает введенные формулы в ячейках, не задействованных другими элементами управления.