Таблица расчета топлива на авто по километражу в excel
Калькулятор топлива в Excel (Можно Google docks)
Всем привет!
Вот не могу понять, брешет бортовик по поводу расхода, или нет… Поискал приложение на андроид по расчету расхода — ничего подходящего не нашел. Одно считает только если полный бак залил, другое остаток в баке не учитывает. На бумажку писать — то забыл, то бумажку потерял, да и в конце концов мы с вами в какое время живем?!
Короче я вот что подумал… Аккаунт на гугле есть, мобильник на андрюшке, всегда со мной. Так а почему бы не пользоваться прямо во время заправки гугл онлайн Excel`ем? За 7 минут накидал калькулятор, который устраивает меня по всем параметрам. Представляю на ваш суд! Вот ссыль на скачивание в Excel формате:
www.fayloobmennik.net/5536543 пароль для скачивания 111111. Вот ссыль на гугл докс:docs.google.com/spreadshe…dM7hHKPI/edit?usp=sharing Если берете с гугла — Выделить все — Скопировать — создать документ в своем аккаунте — вставить.
Что мы там увидим? (Достоверно введена только первая строка — остальные числа пока вымышлены)
Нужно заполнить поля, выделенные серым цветом: №заправки п/п, дата, Марка бензина, пробег по одометру на момент заправки, остаток бензина в баке, заправка (прим. Лукойл на северном), Сколько залил литров, сколько заплатил. «Бенза в баке всего» считается автоматически по формуле: «Остаток» + «Сколько залил», Цена за 1л — тоже считается сама.
Далее — таблица справа вся считается автоматом. ВНИМАНИЕ и ВЫНИМАНИЕ! Корректные показания во второй таблице будут считаться на предыдущую заправку ТОЛЬКО после следующей! Т.е. первая строка таблицы корректно расчитается, когда ты уже заполнил вторую в первой таблице! Т.е. во второй всегда на одну строку меньше, чем в первой.
Тож ничего сложного:
Параметр — вычисление(«сколько залил2» — значит значение из 2 строки колонки сколько залил) — примечание
Проехал — «пробег2» минус «пробег1» (т.е. расстояние от заправки до заправки)
Сжег литров — «Бенза в баке всего1» минус «остаток бенза 2»
Сжег рублей — «Сжег литров» * «Цена за 1л. 1»
Расход на 100км — «Сжег литров1» * 100 / «Проехал1»
Стоимость 1 км пути — «Сжег рублей1» / «Проехал1».
Из плюсов:
-онлайн доступ при наличии смартфона хоть за 600р.
-автоматический расчет
-бесплатно
-своими руками
-можно добавить любой интересующий вас параметр, хоть остаточная стоимость бенза в баке!
Минусы:
-душа требует эстетики… 🙂
Спасибо за внимание, надесь кому-то будет полезным.
Внимательного вам ангела хранителя!
Таблица Excel по содержанию автомобиля, мелочи
Всех приветствую. Для удобства, я сделал себе такую таблицу в Excel, в которую записываю затраты на обслуживание и заправку бензином.
В ней видны все расходы (страховку только не написал), с момента покупки автомобиля. Соответственно за чуть более полутора лет владения Вестой и скромным пробегом 14 тык (в основном по городу), её содержание мне обошлось в 104 тысячи рублей, из которых 39.900 на различные детали, масла и прочее, а 64.250 — на бензин (1368 литров). Сумму по итогам таблица считает автоматически. Можете использовать её для себя, предварительно почистив её. Правую часть (где итоги) с формулами, чистить не надо. Подходит к любой машине 😉 Ссылка на файл Я.Диск disk.yandex.ru/i/eqR3G3d8igbMEQ
Ну и немножко по мелочам. Были стуки спереди, отвратительно неприятные, мелкие. Заехал на диагностику по акции, приговорили стойку стабилизатора. Замена одной — 1700 рублей. Нормальная цена, я считаю, но такие вещи предпочитаю сам менять. Купил новые стойки фирмы Трек.
У нас в городе есть такое предприятие, «гараж на час», где я, на яме и поменял эти стойки. Также, я хотел поменять сзади колодки, т.к. ручник высоко и регулировать его уже было некуда. Но когда снял барабаны (совсем не сложно, зря я боялся), увидел почти новые колодки. Поэтому просто помыл разводные планки, разобрался, как они работают. Потом надел барабаны, и сквозь отверстие, отвёрткой, выдвинул их до конца, после чего натянул ручник из салона. Тормоза стали приятнее, а колодки сдал назад в магазин.
Однажды в морозы у меня затроил двигатель. Сканера OBD у меня не было, поэтому вывернув свечи нашёл сырую. Заменил её и скинул ошибку клемой, но ничего не изменилось. Поэтому взял сканер у друга и он показал пропуски зажигания по 3-му цилиндру. Я поменял местами катушки и пропуск уже появился на другом месте. Поэтому просто купил новую катушки и проблема ушла.
Учёт расходов и замен на Авто в Excel
Всем привет, решил заглянуть на ДрайвТу спустя более 3х лет [чем было сынициировано данное действие — расскажу в четвёртой записи бортжурнала за сентябрь 2020, в планах ещё четыре записи] :). И поделиться своим способом учёта расходов и замен с пробегом. Это только начало х)
13 февраля 2017, 20 часов вечера.
Спустя 17 дней с момента покупки моего первого автомобиля (28 января 2017, 14:30) была создана таблица Excel для учёта расходов из пяти колонок: Дата, Действие, Краткое описание, Сумма, Полное описание.
Также, хорошо, что с самого начала сохранял чеки почти на все приобретения товаров и услуг для автомобиля в папке дома. Что в будущем очень пригодилось :).
Примерно в то же время создал отдельный лист для учёта замененных деталей/жидкостей.
Но было немного муторно всё это контролировать и заносить в таблицу (редактировать, объединять колонки, менять цвет и т.д.). Записи заносились без группировки и в порядке наступления событий. Не понравилось мне потом, что не было привязки ко времени, а только к пробегу и то – примерно. Со временем, когда уже не так активно стал использовать автомобиль, ремонтов и замен стало меньше – лист «Замены» – перестал вести, сохранял только чеки. И вёл лист «Расход ДС таблица».
На сегоднящний день: что касается самой машины — серьёзных поломок не было, были только мелкие внеплановые. Пробег +20тыс.км.
Автоматизация и моделирование бизнес-процессов в 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