Таблица для расчета расхода топлива легкового автомобиля excel

Учёт расходов и замен на Авто в Excel

Всем привет, решил заглянуть на ДрайвТу спустя более 3х лет [чем было сынициировано данное действие — расскажу в четвёртой записи бортжурнала за сентябрь 2020, в планах ещё четыре записи] :). И поделиться своим способом учёта расходов и замен с пробегом. Это только начало х)

13 февраля 2017, 20 часов вечера.
Спустя 17 дней с момента покупки моего первого автомобиля (28 января 2017, 14:30) была создана таблица Excel для учёта расходов из пяти колонок: Дата, Действие, Краткое описание, Сумма, Полное описание.

Также, хорошо, что с самого начала сохранял чеки почти на все приобретения товаров и услуг для автомобиля в папке дома. Что в будущем очень пригодилось :).

Примерно в то же время создал отдельный лист для учёта замененных деталей/жидкостей.

Но было немного муторно всё это контролировать и заносить в таблицу (редактировать, объединять колонки, менять цвет и т.д.). Записи заносились без группировки и в порядке наступления событий. Не понравилось мне потом, что не было привязки ко времени, а только к пробегу и то – примерно. Со временем, когда уже не так активно стал использовать автомобиль, ремонтов и замен стало меньше – лист «Замены» – перестал вести, сохранял только чеки. И вёл лист «Расход ДС таблица».

На сегоднящний день: что касается самой машины — серьёзных поломок не было, были только мелкие внеплановые. Пробег +20тыс.км.

Источник

Таблица Excel по содержанию автомобиля, мелочи

Всех приветствую. Для удобства, я сделал себе такую таблицу в Excel, в которую записываю затраты на обслуживание и заправку бензином.

В ней видны все расходы (страховку только не написал), с момента покупки автомобиля. Соответственно за чуть более полутора лет владения Вестой и скромным пробегом 14 тык (в основном по городу), её содержание мне обошлось в 104 тысячи рублей, из которых 39.900 на различные детали, масла и прочее, а 64.250 — на бензин (1368 литров). Сумму по итогам таблица считает автоматически. Можете использовать её для себя, предварительно почистив её. Правую часть (где итоги) с формулами, чистить не надо. Подходит к любой машине 😉 Ссылка на файл Я.Диск disk.yandex.ru/i/eqR3G3d8igbMEQ

Ну и немножко по мелочам. Были стуки спереди, отвратительно неприятные, мелкие. Заехал на диагностику по акции, приговорили стойку стабилизатора. Замена одной — 1700 рублей. Нормальная цена, я считаю, но такие вещи предпочитаю сам менять. Купил новые стойки фирмы Трек.

У нас в городе есть такое предприятие, «гараж на час», где я, на яме и поменял эти стойки. Также, я хотел поменять сзади колодки, т.к. ручник высоко и регулировать его уже было некуда. Но когда снял барабаны (совсем не сложно, зря я боялся), увидел почти новые колодки. Поэтому просто помыл разводные планки, разобрался, как они работают. Потом надел барабаны, и сквозь отверстие, отвёрткой, выдвинул их до конца, после чего натянул ручник из салона. Тормоза стали приятнее, а колодки сдал назад в магазин.

Однажды в морозы у меня затроил двигатель. Сканера OBD у меня не было, поэтому вывернув свечи нашёл сырую. Заменил её и скинул ошибку клемой, но ничего не изменилось. Поэтому взял сканер у друга и он показал пропуски зажигания по 3-му цилиндру. Я поменял местами катушки и пропуск уже появился на другом месте. Поэтому просто купил новую катушки и проблема ушла.

Источник

Программа Для Расчета Норм Расхода Топлива • 1 основные положения

Расчет ГСМ в Excel

Списание ГСМ по путевым листам — пример алгоритма такого списания вы найдете в статье — должно быть корректно организовано в любой компании. Рассмотрим особенности такого списания подробнее.

Для кого путевой лист обязателен?

Порядок расчета расходов по ГСМ

Пример расчета расходов: применяем нормативные правила

Как списывать ГСМ по фактическим показателям правильно?

Списание ГСМ по путевым листам в налоговом учете

Предъявляемые документы для услуги расчета расхода топлива:

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

Путевой лист: расход по норме и фактически. Cписание по факту

Правила списания ГСМ по путевым листам в случае превышения фактических расходов топлива над нормативными должны быть регламентированы отдельным приказом руководителя. Из путевого листа установлено, что седельный автомобиль-тягач МАЗ-5429 с полуприцепом МАЗ-5205А выполнил 9520 т км транспортной работы при пробеге 595 км.

Списание ГСМ по путевым листам (бухгалтерский учет). 1 основные положения

Расчет ГСМ по путевым листам в Excel, пример расчета расхода.

Легковые автомобили ВАЗ-2109 (два автомобиля), УАЗ – 315195, ВАЗ-21074, Chery INDIS S18D. Предмет исследования Расход разных видов топлива и смазочных материалов для дорожных машин и оборудования, работающих в различных условиях эксплуатации. Гипотеза Известно, что существуют различные способы определения расхода ГСМ: расчётные, экспериментальные.

Приложение N 5. Примеры расчета нормативного расхода топлива

1. Из путевого листа установлено, что легковой автомобиль ВАЗ-217030 Приора, работавший в городе с населением 500 тыс. человек, совершил пробег 180 км.

базовая норма расхода топлива на пробег для легкового автомобиля ВАЗ-217030 Приора составляет надбавка за работу в городе с населением 500 тыс. человек составляет D = 15%.

Нормативный расход топлива составляет:

2. Из путевого листа установлено, что легковой автомобиль ВАЗ-111840 Калина, работавший в горной местности на высоте 850 — 1500 м над уровнем моря, совершил пробег 220 км.

базовая норма расхода топлива на пробег для легкового автомобиля ВАЗ-111840 Калина составляет надбавка за работу в горной местности на высоте от 801 до 2000 м над уровнем моря составляет D = 10% (среднегорье).

Нормативный расход топлива составляет:

3. Из путевого листа установлено, что легковой автомобиль Волга Сайбер, работавший в городе с населением 1,5 млн. человек в зимнее время, совершил пробег 85 км.

базовая норма расхода топлива на пробег для легкового автомобиля Волга Сайбер составляет надбавка за работу в городе с населением 1,5 млн. человек составляет D = 25%, за работу в зимнее время D = 15%.

Нормативный расход топлива составляет:

4. Из путевого листа установлено, что легковой автомобиль Daewoo Nexia, оборудованный кондиционером и работавший в городе с населением 150 тыс. человек, совершил пробег 115 км.

базовая норма расхода топлива на пробег для легкового автомобиля Daewoo Nexia составляет надбавка за работу в городе с населением 150 тыс. человек составляет D = 10%, при использовании кондиционера при движении автомобиля составляет D = 7%.

Нормативный расход топлива составляет:

5. Из путевого листа установлено, что легковой автомобиль Mercedes-Benz S500, оборудованный установкой климат-контроль, в зимнее время за рабочую смену в городе с населением 4 млн. человек совершил пробег 75 км, при этом вынужденный простой автомобиля с работающим двигателем составил 2 часа.

базовая норма расхода топлива на пробег для легкового автомобиля Mercedes-BenzS500 составляет время вынужденного простоя с работающим двигателем T = 2,0 часа;

надбавка за работу в городе с населением 4 млн. человек составляет D = 25%; за работу в зимнее время D = 10%; при использовании установки климат-контроль при движении автомобиля D = 10%; при вынужденном простое автомобиля с работающим двигателем за один час простоя — 10% от значения базовой нормы, то же на стоянке при использовании установки климат-контроль — 10% от значения базовой нормы.

Дополнительный расход топлива на простой автомобиля с работающим двигателем составит:

Нормативный расход топлива составляет:

6. Из путевого листа установлено, что городской автобус НефАЗ-5299-10-15 работал в городе с населением 2 млн. человек в зимнее время с использованием штатных отопителей салона, совершил пробег 145 км при времени работы на линии 8 ч.

транспортная норма расхода топлива на пробег для городского автобуса НефАЗ-5299-10-15 составляет надбавка за работу в городе с населением 2 млн. человек составляет D = 20%; за работу в зимнее время составляет D = 8%;

норма расхода топлива на работу отопителя составляет Нормативный расход топлива составляет:

7. Из путевого листа установлено, что одиночный бортовой автомобиль КамАЗ-43253-15 при пробеге 320 км выполнил транспортную работу в объеме 1750 т·км в условиях эксплуатации, не требующих применения надбавок или снижений.

Какова форма бухгалтерской справки-расчета

Из путевого листа установлено, что седельный тягач МАЗ-5440-А8 с полуприцепом выполнил транспортную работу в объеме 16200 т км при пробеге 600 км в условиях эксплуатации, не требующих применения надбавок или снижений. При расчете движения топлива могут вводиться дополнительные поправочные коэффициенты расход повышается при работах во время зимы, в неблагоприятных условиях, в горах, при поездке на совершенно новом авто или машине после капремонта, а также в городах с населением свыше 1 миллиона и поселках.

Форма акта на списание ГСМ (образец) • Ваши автомобили

Анализ полученных результатов В результате проведённой работы составлено учебное пособие по расчёту нормативного расхода ГСМ, которое будет использовано в практических работах, в курсовом и диплом проектировании. В качестве примера расчета возьмем следующие условия легковой автомобиль марки ГАЗ-24-10 используется в горной местности на высоте от 300 до 800 метров над уровнем моря, а общий пробег за заданный промежуток времени составляет 244 км.

Как подсчитать нормативный расход бензина? © Cписание по факту

Печать листов

Как правило, в предпринимательской деятельности менеджментом предприятий используется форма, утверждённая Постановлением Госкомстата №78: форма 3 – для легкового транспорта, форма 4-П – для грузовых автомобилей.

Массовое создание и автозаполнение

В расчете используются базовые нормы расхода автотранспортной техники и соответствующие средневзвешенные нормы расхода топлива за базисный год, статистические данные по конкретному предприятию и мероприятия по экономии топлива. Типичные ошибки при расчетах подробнее Базовая норма расхода топлива не приговор подробнее Обоснованность норм и нормы расхода от завода-изготовителя подробнее.

Установление норм расхода топлива. Ваши автомобили

Для кого путевой лист обязателен? 1 основные положения

Пример списания ГСМ по путевым листам

базовая норма расхода топлива на пробег автомобиля-самосвала КамАЗ-5511 в снаряженном состоянии составляет норма расхода топлива на перевозку полезного груза составляет масса снаряженного самосвального прицепа норма расхода топлива на пробег автопоезда в составе автомобиля КамАЗ-5511 с прицепом составляет:

Бухгалтерский учет ГСМ

Как и все материально-производственные запасы, учет ГСМ в бухгалтерии ведется по фактической стоимости. Расходы, которые входят в фактическую стоимость, указаны в разделе II ФСБУ 5/2019 «Запасы».

Важно! С 2021 года учет МПЗ ведется в соответствии с новым федеральным стандартом ФСБУ 5/2019 «Запасы» (он заменил ПБУ 5/01). О том, как изменились правила учета МПЗ, подробно рассказали эксперты КонсультантПлюс. Вы можете посмотреть разъяснения, если получите пробный доступ к правовой системе. Он бесплатный.

Принятие к учету ГСМ может осуществляться на основании приложенных к авансовому отчету чеков АЗС (если ГСМ приобретал водитель за наличный расчет) или на основании корешков талонов (если бензин приобретался по талонам). Если же водитель приобретает бензин по топливной карте, то учет ГСМ по топливным картам ведется на основании отчета компании — эмитента карты. Списание ГСМ может производиться следующими методами (раздел III):

Есть еще один способ списания — по себестоимости каждой единицы. Но на практике для списания ГСМ он неприменим.

Самый распространенный способ списания ГСМ — по средней себестоимости, когда стоимость остатка материала складывается со стоимостью его поступления и делится на суммарное количество остатка и поступления в натуральном выражении.

Основные моменты

Организации, для которых использование транспортных средств является основным видом деятельности, должны применять форму ПЛ с реквизитами, которые указаны в разделе I приказа Минтранса от 11. Сложностей с подтверждением обоснованности и списанием не будет, если признавать расход топлива по путевым листам в налоговом учете по утвержденным нормам.

Правила расчета – Ваши автомобили

Поправочные коэффициенты, влияющие на расход топлива – Cписание по норме

Источник

Автоматизация и моделирование бизнес-процессов в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Область расчета расхода топлива и контроля правильности введенных данных

Область расчета расхода топлива с учетом всех поправочных коэффициентов расположена в диапазоне ячеек А17:D33 и приведена на рис. 8.23. с числовыми значениями и рис. 8.24. с формулами расчета. Эту область условно можно разделить на несколько частей.

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

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

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

В ячейку А32 введена формула контроля. Она поможет избежать ошибок при вводе данных. Например, эта формула анализирует одновременность введенных параметров в ячейку D22 (работа за городом) и D25 (работа в городе), что на практике невозможно. Если указан дополнительный расход топлива, только в одной из этих ячеек или данные отсутствует в обоих, то тогда функция ИЛИ возвращает значение ИСТИНА, и функция ЕСЛИ возвращает значение пробела.

Если же в ячейках D22 и D25 одновременно находятся значения отличные от нуля, то тогда формируется текст Определитесь! Работа в городе или за его пределами!, который привлечет внимание и позволит пользователю избежать ввода ошибки.

Формула контроля в ячейке А33 предназначена для анализа содержимого ячейки D15. Если ячейка D15 не содержит никакого значения, то будет сформирован текст Введите расход по путевому листу!.

Если же обнаружено значительное различие между введенным значением расхода топлива по путевому листу в ячейке D15 и определенному расходу топлива по расчету, то будет сформирован текст Слишком большая погрешность!, который говорит что возможно присутствие ошибок. Для формирования текста о погрешности в формулу введено значение расхождения равное 15%, которое может быть при необходимости изменено.

Рис. 8.23. Область расчета дополнительного расхода топлива с числовыми данными

Рис. 8.24. Область расчета дополнительного расхода топлива с формулами

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

Рабочий лист Контроль

Если в лист ВводДанных введена вся необходимая информация, то завершение операции ввода данных осуществляется нажатием на кнопку Просмотр записи (рис. 8.8.) и при этом производится переход на лист Просмотр (рис. 8.25.).

Задача рабочего листа Просмотр сгруппировать и расположить данные в строке 2 в таком виде, в каком они будут переноситься в лист БазаДанных (рис. 8.6.). Строка заголовка этой таблицы идентична таблице на листе БазаДанных.

Рис. 8.25. Рабочий лист Контроль

Таким образом лист ВводДанных готов для ввода следующей «порции» данных.

Рис. 8.26. Подпрограмма ПереносДанныхБаза

Формирование отчетов вручную

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

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

Отчет по расходу топлива за произвольный интервал времени

Ручной метод подготовки отчета

Автоматизированная форма подготовки отчета

Форма создаваемого отчета на листе ОтчетПериод показана на рис. 8.27. с формулами и на рис. 8.28. с числовым примером.

Формулы подготовки отчета

Все остальные формулы строки 7 сравнивают значение в ячейке А7 со значением 0. Значение 0 указывает на то, что эта строка в табличной базе данных не представляет интерес для отчета. И поэтому если это условие удовлетворяется, то и эти формулы возвращают значение 0.

Рис. 8.27. Лист ОтчетПериод с формулами создаваемого отчета

Рис. 8.28. Лист ОтчетПериод с числовым примером создаваемого отчета

Если же условие не удовлетворяется, то тогда формулы возвращают значения содержимого ячеек, на которые указаны ссылки в этих формулах.

Последовательность формирования отчета

Если строку 7 (или диапазон ячеек) с формулами скопировать в нижние строки таблицы (например, до строки 1000), то формулы «выхватят» только указанный диапазон времени, который вероятнее всего будет находиться где-то в середине таблицы. Кроме того, файл, содержащий такое количество формул, будет громоздок.

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

Макрос СоздатьОтчетТопливо, выполняющий формирование отчета, показан на рис. 8.29. Он производит ввод формул в строке 7, последующее копирование формул в заданный диапазон таблицы, замену формул на вычисленные ими значения и сортировку выделенного диапазона по убыванию.

Рис. 8.29. Макрос СоздатьОтчетТопливо, выполняющий формирование отчета по топливу

Следующий пример создаваемого отчета показан на листе ОтчетВодитель с введенными формулами (рис. 8.30.) и числовым примером (рис. 8.31.).

Для ввода фамилии водителя скопируйте созданный элемент управления Список ввода фамилии водителя, после чего произведите вставку скопированного на лист ОтчетВодитель. Аналогичную операцию произведите с формулой ячейки D3 на листе ВводДанных.

Последовательность действий создания макроса по формированию отчета аналогична предыдущему. Отличие заключается только в формуле анализа данных, введенной в ячейку А7 листа ОтчетВодитель.

Формула в ячейке А7 производит поиск текста фамилии водителя, введенной в ячейку D3 листа ОтчетВодитель в тексте, находящемся в ячейке А2 листа БазаДанных. Поиск текста основан на применении функции НАЙТИ, которая при нахождении искомого текста возвращает начальную позицию совпадения.

Сложность заключается в том, что если функция НАЙТИ не находит заданный текст, то она возвращает значение ошибки #ЗНАЧ!. Поэтому функция НАЙТИ помещена внутрь функции ЕОШИБКА, которая возвращает значение ИСТИНА, если ее аргумент содержит значение ошибки.

Функция ЕСЛИ возвращает значение 0 при отсутствии текста фамилии водителя в тексте, содержавшемся в ячейке А2 на листе БазаДанных.

В противном случае формула возвращает левые символы текста, находящегося в ячейке А2 листа БазаДанных, в количестве, уменьшенном на количество символов, находящихся в этом тексте справа до первой буквы фамилии водителя. Для этого применяется функция ЛЕВСИМВ.

Рис. 8.30. Рабочий лист ОтчетВодитель с формулами, применяемыми при создании отчета

Рис. 8.31. Рабочий лист ОтчетВодитель с числовым примером

Создание отчета выполняет макрос СоздатьОтчетВодитель (рис. 8.32.). Процедура выполнения его аналогична предыдущему макросу.

Рис. 8.32. Макрос СоздатьОтчетВодитель

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

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

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

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

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

© Интернет-проект «Корпоративный менеджмент», 1998–2021

Источник