Визначення функціональних властивостей табличних процесів Excel для їх використання в галузі сільськогосподарської інженерії. Розробка інформаційної технології визначення терміну експлуатації тракторів, сільськогосподарських машин та іншого обладнання.
Аннотация к работе
The existing management problems in enterprises have been analyzed and proposals for their solution have been given. Наведено результати дослідження функціональних можливостей табличного процесора MS Excel при розвязувані однієї з типових задач експлуатації машино-тракторного парку - визначення терміну ефективного використання машин та обладнання підприємства.. Ktriali слова: машинно-тракторний парк, заміна обладнання, оптимізація, табличний процесор, Excel. Задача визначення терміну заміни обладнання, коли витрати на його експлуатацію та ремонт проходять точку мінімуму і починають зростати, є типовою задачею, що розвязується як технологами [1, с.284-288], так і економістами [2, с.495-499], [3] та математиками [4, сс.199-221], [5, с.108-111]. Актуальність розвязання таких задач особливо зростає у час, коли зношеність засобів механізації, електрифікації та автоматизації виробничих процесів у сільському господарстві та необхідність їх заміни досягла значного рівня.Потрібно визначити оптимальний термін експлуатації обладнання, якщо відомі постійні і змінні витрати на обслуговування обладнання, параметр зміни витрат у часі та початкова вартість обладнання. За результатами дослідження для побудови прикладної інформаційної технології розвязування задачі заміни машин і обладнання доцільно скористатись функцією загальних витрат E(t), яка має вигляд: E(t) = a btn Значення цих параметрів потрібно зробити змінними, а не фіксувати як константи у формулі розрахунку значень функції загальних витрат та її похідної. При створенні такого елементу управління задається не тільки адреса повязаної з ним клітинки, а й мінімальне та максимальне значення, малий і великий кроки зміни значень тощо. Формули цієї таблиці динамічно повязані зі значеннями параметрів (клітинки "Е2:Н2"); при зміні значення будь-якого з параметрів дані таблиці автоматично перераховуються і відображаються на графіку.
Вывод
Постановка задачі. Потрібно визначити оптимальний термін експлуатації обладнання, якщо відомі постійні і змінні витрати на обслуговування обладнання, параметр зміни витрат у часі та початкова вартість обладнання. Залежно від терміну експлуатації витрати описують певною функцією.
За результатами дослідження для побудови прикладної інформаційної технології розвязування задачі заміни машин і обладнання доцільно скористатись функцією загальних витрат E(t), яка має вигляд: E(t) = a btn A/t, (2) де а - постійні витрати, b - змінні витрати на обслуговування обладнання, n - параметр зміни витрат у часі, А -початкова вартість обладнання
Для знаходження екстремуму знайдемо першу похідну цієї функції і прирівняємо її до нуля: E?(t) = nbtn-1 A/t2 (3) nbtn-1 A/t2 = 0. (4)
Можна знайти аналітичний розвязок наведеного рівняння, однак особливість застосування побудованої нами інформаційної технології полягає у тому, що цього робити немає потреби. Знайти розвязок рівняння з одним невідомим можна використовуючи функцію Excel "Підбір параметра".
214
Зовнішній вигляд інтерфейсу розробленої прикладної інформаційної технології наведено на рисунку.
Крім точного розвязку задачі заміни обладнання, побудована інформаційна технологія дозволяє також наочно побачити тенденцію поведінки функції загальних витрат на графіку, повязаному з моделлю, яка її описує.
Як правило, інформаційні технології розробляються для розвязування цілого класу однорідних задач. Тому у моделі має бути передбачено, що задача буде розвязуватись при будь-яких значеннях параметрів a, b, n та A, що належать відповідним діапазонам їх допустимих значень. Значення цих параметрів потрібно зробити змінними, а не фіксувати як константи у формулі розрахунку значень функції загальних витрат та її похідної. Ці величини доцільно вписувати у окремі комірки робочого аркуша електронної таблиці, а у формулах використовувати посилання на них.
Набір елементів управління типу "Смуга прокрутки" для введення даних у клітинки "E2:H2"
Рис. Інтерфейс прикладної інформаційної технології розрахунку оптимального терміну заміни тракторів і сільськогосподарських машин в ТП Excel
Параметри a, b, n та A, вводяться у клітинки "E2:H2" вручну. Для полегшення введення значень параметрів пропонуємо використовувати елементи управління "Смуга прокрутки". Ці засоби дозволяють вводити дані у клітинки електронної таблиці без допомоги клавіатури, шляхом переміщення мишкою повзунка відповідної смуги вгору чи вниз (рис.). Смуги прокрутки можуть також бути орієнтовані горизонтально.
При створенні такого елементу управління задається не тільки адреса повязаної з ним клітинки, а й мінімальне та максимальне значення, малий і великий кроки зміни значень тощо.
215
У клітинках "В2:С22" наведена таблиця з розрахованими значеннями функції загальної вартості витрат, повязаних з використанням досліджуваного виду машин чи обладнання. Таблиця розрахована на 20 років їх експлуатації і при потребі може бути легко розширена.
Таблиця у клітинках "В2:С22" служить джерелом даних графіка, який наочно демонструє поведінку функції витрат у заданому діапазоні.
Формули цієї таблиці динамічно повязані зі значеннями параметрів (клітинки "Е2:Н2"); при зміні значення будь-якого з параметрів дані таблиці автоматично перераховуються і відображаються на графіку. Завдяки цьому користувач може миттєво спостерігати ефект від такої зміни не тільки у таблиці, а й на графіку.
Іншим важливим елементом інформаційної технології є знаходження точних значень (а) терміну експлуатації машини t, при якому досягається мінімальна сума витрат E(t), а також (б) самої мінімальної суми витрат. Для цього у клітинки "С25:С27" введені: - значення змінної t (спочатку туди вноситься довільне значення з її допустимого діапазону значень) - клітинка "С25";
- формула для розрахунку функції витрат E(t) у заданій у клітинці "С25" точці t (за рівнянням (2)) - клітинка "С26";
- формула для розрахунку похідної функції витрат E ?(t) узадан? "С25" точці t (за рівнянням (3)) -клітинка "С27".
Для пошуку значення t, при якому досягається нульове значення першої похідної, скористаємось функцією "Підбір параметра", яка міститься у Excel 2010 на закладці "Данные" у групі "Анализ "что если". Функція має власне діалогове вікно, у якому вказується: - у якій клітинці встановити значення; - яке значення встановити;
- за допомогою зміни якої клітинки цього потрібно досягти.
Оскільки виклик такого вікна і встановлення у ньому значень параметрів носить рутинний характер (при виконанні цієї операції щоразу виконуються одні й ті ж самі дії), автоматизуємо її виконання за допомогою створення макросу на мові VBA.
Макрос з назвою "Optimum" являє собою програму з однієї команди, яка викликає метод GOALSEEK (Пошук цільового значення) обєкту Range (Діапазон), який вказує на клітинку "С27", де потрібно отримати цільове значення "нуль": Sub Optimum()
Range("C27").GOALSEEK Goal:=0, CHANGINGCELL:=Range("C25") End Sub
Виклик цієї програми звязується з подією натискання спеціально створеної кнопки "Знайти оптимум" (рис.). Тепер кожного разу, коли користувач натисне мишкою цю кнопку, значення похідної зміниться на нуль за рахунок підбору t (значення у клітинці "С25").
Таким чином, застосування створеної нами прикладної інформаційної технології визначення терміну експлуатації тракторів, сільськогосподарських машин та іншого обладнання полягає у: - введенні значень параметрів у клітинки "Е2:Н2" - вручну або за допомогою смуг прокрутки та 216
- натисканні на кнопку "Знайти оптимум" для визначення точних координат точки, що містить оптимальне значення функції загальних витрат.
Удосконалений варіант інформаційної технології дозволяє обійтись без кнопки "Знайти оптимум". Програма на мові VBA (макрос) при цьому стає значно складнішою, а координати точки оптимуму автоматично знаходяться при будь-якій зміні значення кожного з чотирьох параметрів задачі.
Висновки. Задачу оптимізації терміну заміни машин та обладнання доцільно подати у середовищі табличного процесора Excel у вигляді моделі, утвореної за рахунок послідовних посилань у формулах на клітинки електронної таблиці. Для розробки інтерфейсу прикладної інформаційної технології розвязування задач цього класу можна використовувати широкий спектр вбудованих засобів, а також програмування на мові VBA.
Список литературы
1. Баженов С. П. Основы эксплуатации и ремонта автомобилей и тракторов / С. П. Баженов, Б. Н. Казьмин, С. В. Носов. —М.: ACADEMIA, 2005. — 336 с.
2. Ковалев В. В. Введение в финансовый менеджмент / В. В. Ковалев. — М.: Финансы и статистика, 2004. — 768 с.
3. Организация и управление производством на сельскохозяйственных предприятиях / Под ред. В. Т. Водянникова. — М.: Изд-во "КОЛОСС", Изд-во СТГАУ "АГРУС", 2006. — 506 с.
4. Карагодова О. О. Дослідження операцій / О. О. Карагодова, В. Р. Кігель, В. Д. Рожок. — К.: Центр учбової літератури, 2007. — 256 с.
5. Вагнер Г. Основы исследования операций / Г. Вагнер. — Т. 2. — М.: Мир, 1973. — 489 с.
6. Гуськов В. Я. Использование электронных ТАБЛИЦEXCEL в инженерных расчетах / В. Я. Гуськов, Р. Л. Кочубиевская, Г. А. Руев, Н. Н. Федорова. — Новосибирск: НГАСУ, 1999. — 80 с.
7. VBA and Macros: Microsoft Excel 2010. — Indianapolis: Que, 2010. — 630 p.
8. Winston W. L. Microsoft Excel 2010: Data Analysis and Business Modeling. — Redmond: Microsoft Press, 2011. — 700 p.