Автоматизація обліку витрати палива на автотранспортному підприємстві

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

З метою полегшення введення найменування моделі автомобіля застосуйте елемент управління Список.

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

У полі Поміщати результат  в комірку вкажіть адресу комірки Е1. 

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

Для вибору моделі автомобіля натисніть на кнопку відкриття списку і виберіть її найменування в списку, (рис. 3.10.). Залежно від черговості, яку займає цей автомобіль в списку, в комірку Е1 вводиться його порядковий номер. А вже за цим номером формула в комірці D1 зробить пошук марки на робочому листі Марка.

 

Рис. 3.10. Елемент Список з відкритим списком моделей автомобілів

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

= ВПР (E2; Водії! $ D $ 4: $ E $ 20; 2; БРЕХНЯ)

= ВПР (E3; Водії! $ A $ 4: $ B $ 20; 2; БРЕХНЯ)

якими управляють такі елементи управління Список:

• для вибору реєстраційного номера автомобіля;

• прізвища водія. 

 

 

Рис. 3.11. Макроси створюють формули в осередках D1: D3

 

 

 

 

3.2. Пошук інформації за обраними даними

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

На рис. 3.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 (рис. 3.12.). 

Рис. 3.12. Фрагмент робочого аркуша ВводДанних з областю введення показань за подорожнім листом

Введення показань спідометра

У осередок D9 аркуша ВводДанних вводяться показання спідометра при виїзді з гаража. Цю інформацію можна вводити і з клавіатури, але якщо показання спідометра, визначене  в комірці D5 з повернення в гараж  за попереднім подорожнього листа відповідає дійсності, то тоді досить натиснути на кнопку ПереносКМ (рис. 3.8.). Цій кнопці призначений макрос ПереносКілометраж (рис. 3.15.), Що виробляє введення в клітинку D9 значення показання спідометра, визначеного формулою, що знаходиться комірці D5.

У осередок D10 вводиться показання спідометра при поверненні в гараж, а в комірку D15 - витрата палива, зазначені в дорожньому листі. Для того щоб не вводити ці дані безпосередньо в осередок, за допомогою функції InputBox (рис. 3.15.) Створіть користувальницькі діалогові вікна Спідометр (рис. 3.13.) І ПММ (рис. 3.14.). Введені в них з клавіатури свідчення будуть присвоєні відповідним осередкам.  

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

Рис. 3.14. Діалогове вікно ПММ для введення витрати палива за подорожнім листом 

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

Введення дат

При введенні дати по оброблюваному  подорожнього листа припускаємо, що ця дата більше дати за попереднім подорожнього листа при поверненні в гараж на один день. І наступне припущення - в цей же день автомобіль повернеться в гараж.

Отже, дату, визначену  в комірці D6 з табличної бази даних  необхідно збільшити на один день. Тоді при натисканні на кнопку Перенесення дата (рис. 2.8.) В осередки D11 (дата виїзду з гаража) і D12 (дата повернення в гараж) за оброблюваного подорожнього листа буде введена дата, збільшена на один день по відношенню до попереднього подорожнього листа. Макрос ПереносДата (рис. 3.15.) Виконає цю операцію. При необхідності, введені дати можна відкоригувати безпосередньо в осередках з клавіатури. Або ж ввести в макрос рядок коду VBA з викликом функції InputBox для введення дат виїзду і повернення в користувальницькі діалогові вікна і подальшого присвоєння введених значень осередкам D11 і D12.

Блок елементів управління для введення даних по додатковому  витраті палива

На аркуші ВводДанних знаходяться згруповані елементи управління, за допомогою яких здійснюється введення додаткової витрати палива і їх у свою чергу можна розділити на дві частини:

• самостійні;

• об'єднані в групи  елементів.

Наступна важлива деталь, на яку слід звернути увагу, що введення даних здійснюється не тільки за допомогою  застосування безпосередньо елементів управління, але і з використанням діапазону комірок в стовпці J, які пов'язані з елементами управління.

У початковому стані, коли користувач приступає до введення даних за подорожнім листом вміст  комірок стовпчика J очищено. У міру активізації різних елементів управління, комірки заповнюються різними значеннями і формулами, які розглянуті далі.

Самостійні елементи керування для введення додаткової витрати палива не залежного від  пробігу автомобіля

Використання елементів  управління Прапорець для введення текстової інформації наводилося в сьомий чолі. У цьому додатку за допомогою цих елементів необхідно ввести в комірки D13 і D14 відповідно:

• кількість годин  роботи обігрівача;

• обсяг виконаної  транспортної роботи.

Розглянемо послідовність  введення даних та їх подальшої обробки. Цим елементам управління призначені макроси ОбогревЧасов і ТранспортнаяРабота (рис. 3.16.), Які при зверненні до елементів управління за допомогою функцій InputBox активізують користувальницькі діалогові вікна Обігрів і Транспортна робота для наступного введення даних у комірки D13 і D14. Але ці діалогові вона активізуються як при установці в цих елементах управління прапорця, так і при його знятті.

Ці елементи управління за допомогою діалогового вікна  Формат елементів управління пов'язані з осередками J13 і J14, в які залежно від стану цих елементів управління, може бути введено вміст - БРЕХНЯ або ІСТИНА. Цим осередкам присвоєні імена Обігрів і Робота відповідно.

І якщо ці елементи управління активізовані і в осередках J13 і J14, знаходяться значення ІСТИНА, то введені в користувальницькі діалогові вікна дані будуть відображені не тільки в осередках D13 (годин на обігрів) і D14 (обсяг виконаної транспортної), а й у формулах в осередках D19 і D20 (рис. 3.24.), які засновані на функції IF. У першому аргументі функції IF знаходиться ім'я комірки і якщо вміст цього осередку ІСТИНА, то формула виконує розрахунок додатково витраченого палива, в іншому ж випадку повертає значення 0.

 

Рис. 3.16. Макроси ОбогревЧасов і ТранспортнаяРабота для виклику користувальницьких діалогових вікон, за допомогою яких виробляється введення даних

Самостійні елементи керування для введення додаткової витрати палива залежного від пробігу автомобіля

Три елементи управління Прапорець (рис. 3.17.), Вводять в розрахунки додаткову витрату палива залежить від лінійного пробігу автомобіля і їм присвоєно назву, відповідне їх призначенням:

• часті технологічні зупинки;

• бітумне покриття дороги за межами міста;

• на автомобілі, що мають  термін експлуатації більше 8 років.

Ці елементи управління пов'язані з осередками J9: J11, яким присвоєні імена: Зупинки, Бітум  і СтарийАвтомобіль. Якщо елементи управління активізовані, то в цим осередкам буде присвоєно значення ІСТИНА (див. рис. 3.17.), В іншому випадку значення БРЕХНЯ.

Цим же елементам управління призначені три макросу (мал. 8.18.), Які  при зверненні до елементів управління вводять формули в осередки D21: D23 (рис. 3.24.). 

Рис. 3.17. Елементи керування для введення даних 

Рис. 3.18. Макроси поновлення формул для обліку додаткової витрати палива

3.3.Групи елементів керування

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

Якщо на одному робочому листі створити 5 елементів управління Перемикач, то подальше призначення  адреси управління пов'язаної комірки (наприклад, Е4) для одного з них, буде командою автоматичного призначення адреси цієї ж комірки і для 4-х інших. При активізації одного з них, вміст пов'язаної осередку буде змінюватися від одного до п'яти залежно від хронологічної послідовності, в якій були створені ці елементи. Це добре видно на прикладі, наведеному на рис. 3.19. 

 

 

Рис. 3.19. Робочий лист із створеними елементами управління Перемикач, пов'язаних з осередком Е4

Якщо ж за допомогою  елемента управління Рамка укласти  частина елементів Перемикач  в групу (рис.2.20.), То перемикачі цієї групи елементів управління не зможуть керувати пов'язаної осередком Е4. Якщо одному з перемикачів группи1А задати зв'язок з осередком Е9, то тоді вміст комірки Е9 буде змінюватися від одного до трьох, залежно від активізації перемикачів Перекл.3 - Перекл.5., А два перших перемикача будуть керувати значенням вмісту комірки Е4.

Об'єднання елементів  управління до групи здійснюється елементом  управління Рамка, який створюється  за допомогою панелі інструментів Форми (рис. 3.6.). Для цього після вибору цього елемента керування на панелі інструментів Форма обведіть контур навколо групи елементів управління Перемикач. 

 

Рис. 3.20. Створення групи перемикачів

Якщо ж якийсь перемикач, наприклад, Перекл.4 перетягнути за межі группи1А (рис. 3.21.), То адреса пов'язаної осередку автоматично змінюється з Е9 на Е4. Тобто, перемикач "перейшов" в іншу групу. 

 

 

Рис. 3.21. Перемикач виведений за межі группи1А

Створення груп елементів  управління при обліку додаткової витрати  палива.

Це елементи управління, об'єднані в групу для розрахунку додаткової витрати палива, які мають дворівневу систему обліку.

Розглянемо групу елементів  управління Температура.

Перший рівень - показує, що даний коефіцієнт в розрахунках  присутня. Для цього використовується елемент керування Прапорець. Цей  елемент пов'язаний з осередком J15, якій присвоюється значення ІСТИНА при активізації елемента керування. У свою чергу це значення ІСТИНА служить дозволом для формули в комірці J17 проводити подальший розрахунок.

Другий рівень показує - який коефіцієнт додаткової витрати  палива буде використовуватися в залежності від температури навколишнього повітря. Для цього задіяні три елементи управління Перемикач. Перебуваючи в одній групі, всі три елементи:

2. Температура від  0 до мінус 5 градусів;

3. Температура від  мінус 5 до мінус 10 градусів;

4. Температура від  мінус 10 до мінус 15 градусів;

5. пов'язані з осередком  J16 і змінюють її вміст від  одиниці до трьох.

Осередок J17, з присвоєним їй ім'ям Температура містить формулу:

 

Якщо в комірці J15, якою управляє елемент управління Прапорець з ім'ям Температура, знаходиться значення БРЕХНЯ (опція не відзначена), то тоді перша функція ЯКЩО у формулі в комірці J17 повертає значення 0. Це і є перший рівень управління - присутність цього коефіцієнта в розрахунках при значенні ІСТИНА в комірці J15.