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

Далі формула переходить до визначення розміру коефіцієнта, залежно від викликаного елемента управління Перемикач, які входять  в цю групу. Ці елементи управління пов'язані з осередком J16 і змінюють її значення від одиниці до трьох.

Наступні функції ЯКЩО, залежно від значення вмісту комірки J16, посиланням на іменовану комірку, повертають значення коефіцієнта додаткової витрати палива для даної температури, які введені в таблицю на робочому листі Норми (рис. 3.3.).

Наступні дві об'єднані групи елементів управління: Місто і Гори функціонують як і група Температура.

У осередок J24 (ім'я комірки  Місто) введена формула:

= ЕСЛИ (J22; ЕСЛИ (J23 = 1; Город1; ЕСЛИ (J23 = 2; Город2; Город3)); 0)

і схожа формула в  комірці J32 (ім'я комірки Гори):

= ЕСЛИ (J30; ЕСЛИ (J31 = 1; ГорнМест1500; ЕСЛИ (J31 = 2; ГорнМестн2000; 0)))

Всіх елементів управління, об'єднаним в групи, призначені макроси, що створюють формули в осередках, які вони контролюють - J17, J24 і J32. Макроси показані на рис. 3.22. 

 

Рис. 3.22. Макроси введення формул у комірки J17, J24 і J32

Область розрахунку витрати  палива та контролю правильності введених даних

Область розрахунку витрати  палива з урахуванням всіх поправочних  коефіцієнтів розташована в діапазоні  клітинок А17: D33 і наведена на рис. 3.23. з числовими значеннями і рис. 3.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%, яке  може бути при необхідності змінено.

 

Рис. 3.23. Область розрахунку додаткової витрати палива з числовими даними

Рис. 3.24. Область розрахунку додаткової витрати палива з формулами

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

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

Якщо в лист ВводДанних введена вся необхідна інформація, то завершення операції введення даних  здійснюється натисканням на кнопку Перегляд запису (рис. 3.8.) І при цьому проводиться перехід на лист Перегляд (рис. 3.25.).

Завдання робочого листа  Перегляд згрупувати і розташувати  дані в рядку 2 у такому вигляді, в  якому вони будуть переноситися в  лист БазаДанних (рис. 3.6.). Рядок заголовка  цієї таблиці ідентична таблиці на аркуші БазаДанних.

Практично всі формули  присутні на даному аркуші - посилання  на клітинки аркуша ВводДанних. Відрізняється  тільки вміст комірки А2, введена  в яку формула показана в рядку  формул. Вона об'єднує текст даних  марки машини, державного номера та прізвища водія.

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

Якщо дані на аркуші Перегляд влаштовують користувача, то натисніть  на кнопку Перенесення в базу даних, якої призначений макрос ПереносДаннихБаза (рис. 3.26.). Завданням цього макросу є:

2. скопіювати діапазон  комірок з даними на аркуші  Контроль;

3. перехід на лист  БазаДанних і визначення першої  порожній рядка;

4. виділення в певній  рядку клітинки у стовпці А  і вставка значень скопійованій  області осередків з аркуша  Контроль;

5. сортування за зменшенням  табличній бази даних по стовпці  F враховуючи те що перший рядок  є рядком заголовка;

6. перехід на лист  ВводДанних та очищення вмісту  діапазонів комірок з введеними  даними по подорожнього листа.

Таким чином лист ВводДанних готовий для введення наступної "порції" даних.

 

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

3.4. Формування звітів

Якщо існує будь-яка  база даних, то, як правило, виникає  і необхідність формування звітів. Звіти можуть бути найрізноманітніші. Наприклад, в розрізі розглянутого нами прикладу:

2. коли і скільки  було витрачено палива;

3. чи є відхилення  від затверджених норм використання палива, як у бік перевитрати, так і у бік економії;

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

Розглянемо автоматизацію  процесу з підготовки звітів на двох прикладах:

5. звіт за загальним  витраті, економії і перевитрати палива за довільний проміжок часу;

6. звіт по кожному  водієві із зазначенням дати  виїзду і повернення в гараж,  і з зазначенням пробігу автомобіля.

Формування звітів вручну

Досить часто маючи  табличну базу даних, формування звітів відбувається в напівручному режимі. З цих табличних баз даних виділяються окремі фрагменти (стовпці, рядки), копіюються, вставляються в інші таблиці. З цих новостворених масивів інформації і формуються звіти. Операції, як правило, однотипні і повторювані, але кожен місяць виконуються заново.

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

Звіт по витраті палива за довільний інтервал часу

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

7. модель, реєстраційний  номер автомобіля і прізвище  водія;

8. дата виїзду з гаража;

9. дата повернення  в гараж;

10. витрата палива за  нормами;

11. економія і перевитрата  палива по кожному подорожнього  листа.

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

Найбільш простий спосіб - створення точної копії листа  БазаДанних, який після подальшої обробки і буде звітом. Обробка полягає в наступному:

12. сортування списку  за датою виїзду з гаража  і потім за датою повернення  в гараж;

13. видалення "зайвих" стовпців і рядків;

14. додання створеній  таблиці форми звіту.

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

Автоматизована форма  підготовки звіту

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

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

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

Формули підготовки звіту

У осередок А7 введена формула, яка порівнює:

15. дату, введену в клітинку  С2 і дату повернення в гараж  першого рядка створеної табличній  бази даних (комірка С2 аркуша  БазаДанних);

16. якщо дата в комірці  С2 більш дати на аркуші БазаДанних, то тоді функція ЯКЩО повертає значення 0;

17. якщо дата в комірці  С2 менш або дорівнює даті  на аркуші БазаДанних, то тоді  друга функція ЯКЩО порівнює  дату у клітинці В2 з датою  в комірці В2 на аркуші БазаДанних;

18. якщо початкова дата  формування звіту дорівнює або  більше дати на аркуші БазаДанних, то тоді формула повертає значення міститься в комірці А2 аркуша БазаДанних або в іншому випадку повертається значення 0.

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

 

Рис. 3.27. Лист Звіт Період з формулами створюваного звіту

Рис. 3.28. Лист ОтчетПеріод  з числовим прикладом створюваного звіту

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

Послідовність формування звіту

Якщо рядок 7 (або діапазон комірок) з формулами скопіювати в нижні рядки таблиці (наприклад, до рядка 1000), то формули "вихоплять" лише вказаний діапазон часу, який найімовірніше буде знаходитися десь в середині таблиці. Крім того, файл, що містить таку кількість формул, буде громіздкий.

Наступна майбутня завдання - згрупувати ці дані. У Excel це можливо здійснити застосувавши метод сортування. Але процес сортування формул не приведе до бажаного результату, тому що після сортування станеться перерахунок формул, і необхідні для звіту дані по раніше будуть перебувати десь "в середині таблиці".

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

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

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

Звіт по водіям

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

Звіт повинен містити  такі дані:

2. автомобіль і його  державний номер;

3. дата виїзду з  гаража;

4. дата повернення  в гараж;

5. пробіг автомобілів  по спідометрі

6. і формуватися за  введеної в комірку D3 прізвища  водія.

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

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

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