Створення макросу

Розглянемо на прикладі процес створення макросу. Потрібно створити макрос, який би формував шапку такої таблиці:

Порівняльний аналіз використання сільськогосподарського капіталу господарства " "
Показник	2000 рік		2004 рік
	млн.дол.	%	млн.дол.	%
Грошова оцінка землі	97,4	55,31	63,7	53,22
Людський капітал	43,8	24,87	35,1	29,32
Основний і оборотний капітал	34,9	19,82	20,9	17,46
Всього с.-г. капітал	176,1	100	119,7	100

Для цього:

1. Виберіть команду Макрос у меню Сервис, а потім – команду Начать запись.

2. У поле Имя макроса введіть ім'я нового макросу „blank”.

3. При бажанні введіть короткий опис макросу в поле Описание.

4. Для виконання макросу можна призначити сполучення клавіш (наприклад, Ctrl-b), натисніть кнопку OK, щоб почати запис макросу.

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

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

6. Для закінчення запису макросу натисніть кнопку Остановить запись.

Виконання макросу

1. Щоб перевірити, як працює макрос перейдіть на інший лист і виконайте макрос командою СервисМакросМакросы.

Виберіть макрос “blank” і натисніть кнопку Выполнить. На листі має бути такий результат:

2. Перевірте чи виконується макрос натисненням призначеної комбінації клавіш.

3. Для присвоєння макросу кнопки на панелі інструментів або команди меню виберіть команду ВидПанели инструментовНастройка. Виберіть записуваний макрос у списку Команды і перетягніть „настриваемую” кнопку на панель інструментів (власну або будь-яку відкриту) або в меню. Натисніть кнопку Изменить выделенный объект і меню виберіть команду Назначить макрос (в списку доступних макросів виберіть потрібний). В тому ж меню можна змінити вигляд кнопки.

10. Використання інструменту “Подбор параметра” для розв’язання економічних та управлінських задач

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

• Підбір значення однієї зі статей у кошторисі витрат або бюджеті для одержання бажаного підсумкового значення.

• Іншим прикладом аналогічної задачі з нелінійним характером залежності результату від параметра є пошук величини разових (квартальних) витрат на рекламу для одержання заданої величини квартального прибутку. Нелінійна залежність результату від параметра робить розв’язок не єдиним.

Іноді залежність цільової функції від аргументів досить складна. Тому далеко не завжди можна знайти точний аналітичний розв'язок, який дає значення параметра. Нерідко недостатня математична підготовка користувача не дозволяє йому знайти аналітичний розв’язок. Для таких випадків розробники Excel пропонують інструмент, який названо Подбор параметра.

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

Якщо на черговому кроці обчислень різниця між поточним результатом і цільовим значенням стала менше заданої величини, тобто досягнута задана точність розв’язку, то обчислення припиняються, і поточне значення пропонується як підсумковий результат підбору. Зазначимо, що при підборі параметра практично завжди виходять наближені значення результату. Відмінності від “ручного” підбору полягають у тому, що він виконується швидко і з високою точністю, достатньою для типових економічних задач. У той же час, якщо мова йде про високоточні інженерні або наукові обчислення, слід звертати особливу увагу на точність отриманих результатів і точність (розрядність) представлення результатів.

Складові підбору параметра. При підборі параметра користувач повинен визначити три обов’язкові складові:

• місце розташування цільової клітинки (вміст якої обов'язково має бути формулою);

• значення, яке має бути досягнуто в цільовій клітинці при зміні параметра;

• клітинки, вміст якої (параметр) буде змінюватись для досягнення цільовою клітинкою шуканого значення.

Усі три названі складові пошуку об’єднані в діалоговому вікні – Подбор параметра, яке викликається з меню Сервис і наведене на рис. 11.

Рис.11. Складові підбору параметра

Перше поле (Установить в ячейке) діалогового вікна має містити адресу або ім’я цільової клітинки, що містить формулу для якої потрібно підібрати параметр. Друге поле (Значение) має містити шукане значення цільової клітинки. Третє поле (Изменяя значение ячейки) має містити адресу клітинки, вміст якої буде змінюватися в процесі підбору параметра. Ця клітинка повинна безпосередньо або опосередковано впливати на цільову клітинку.

Вміст змінюваної клітинки може бути порожнім. У цьому випадку він передбачається рівним 0 (нулю), однак бажано, щоб користувач, виходячи з апріорних знань про характер залежності, визначив деяке початкове значення параметра. Від цього початкового значення залежить швидкість одержання результату, особливо у випадку складних залежностей, а також і саме значення результату у випадку нелінійної залежності і наявності багатьох розв’язків.

Точність розв’язку. Розв’язки, які одержані за допомогою підбору параметра, є наближеними і лише в деяких випадках дають точні значення. Так, наприклад, обчислення по формулі Z=ХY при Х=2 і Y=2 дають добре відомий точний результат, рівний 4. Однак у випадку підбору параметра Х для досягнення Z=4 буде отриманий наближений результат. Аналогічна процедура підбору параметра Y дасть також наближений результат. Вправи над цим завданням дозволять оцінити точність підбору параметра і похибку представлення даних із обмеженою розрядністю при виконанні простої операції множення.

Множина розв’язків. У випадку складних нелінійних функцій, які зв’язують параметр, що підбирається, зі значенням цільової клітинки, може виникнути ситуація, коли не одне, а декілька значень параметра відповідають шуканому значенню цільової функції. Найпростіший приклад подібної ситуації наведено на рис. 12 для квадратичної залежності Z=10–x2. Два значення параметра Х (Х=–2 і Х=2) відповідають шуканому значенню Z=6. Тут вирішальну роль у тому, яке значення буде знайдено в результаті підбору, має початкове значення параметра, що вводиться у клітинку D12.

Так, при від’ємних початкових значеннях Х буде знайдене значення Х=–2, що дає Z=6 (див. на обчислення на рис. 12, що справа), а при нульовому або додатних початкових значеннях Х буде отриманий Х=2, що також дає Z=6 (див. на обчислення на рис. 12, що зліва). У загальному випадку для довільної багатоекстремальної

x 1,999999986 z=10-x2 6,000000056	x -2,000000106 z=10-x2 5,999999577

Рис. 12

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

Рис. 13.

Задача на складання штатного розпису

Часто при розв’язанні практичних задач виникають ситуації, коли необхідно досягти якоїсь конкретної мети. Наприклад, необхідно щоб собівартість продукції становила 20 грн. Специфіка таких задач полягає в тому, що у Вашому розпорядженні є математична модель досліджуваного процесу, наприклад, закон ціноутворення, але Ви не знаєте, при якому значенні вхідного в неї параметра можна досягти поставленої мети.

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

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

Познайомимося з цією процедурою на прикладі складання штатного розпису.

Нехай відомо, що в штаті фірми є 6 менеджерів, 8 менеджерів І кат., 10 провідних спеціалістів, 3 завідувачі відділами, головний бухгалтер, головний економіст, головний інженер і директор. Загальний місячний фонд зарплати становить 69000 грн. Необхідно визначити, якими мають бути оклади співробітників фірми.

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

A_i * О + B_i,

• де О – оклад менеджера; A_i і B_i – коефіцієнти.

Знаючи кількість людей на кожній посаді, нашу модель можна записати як рівняння

N₁* A₁ * О + N2*(A₂ * О + B₂) + ... + N₈*(A₈*О + B₈) = 69000,

де N₁ – число менеджерів, N₂ – число менеджерів І кат і т.д.

У цьому рівнянні нам відомі А₁... А₈, В₁ ... В₈ і N₁... N₈, а О невідомо.

Аналіз рівняння показує, що задача складання розкладу звелася до розв’язку лінійного рівняння відносно О. Розв’яжемо його.

Введіть вхідні дані в робочий лист електронної таблиці, як показано нижче.

У колонці D обчисліть заробітну плату для кожної посади. Наприклад, для клітинки D4 формула розрахунку має вигляд

= Е4 * $І$4 + F4.

У колонці G обчисліть загальну суму зарплати всіх робітників даної посади. Наприклад, для клітинки G4 формула розрахунку має вигляд

= D4 * С4.

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

Визначіть оклад менеджера так, щоб розрахунковий фонд був рівним заданому. Для цього:

• активізуйте команду Подбор параметра із меню Сервис;

• у полі “Установить в ячейке” діалогового вікна, що з’явилося, введіть посилання на клітинку G12, що містить формулу;

• у полі “Значение” наберіть шуканий результат 69000;

• у полі “Изменяя значение ячейки” введіть посилання на змінювану клітинку $I$4 і клацніть на кнопці ОК.

Збережіть таблицю у Вашій папці, наприклад, під ім'ям Штат_розклад.xls.

Аналіз задачі показує, що за допомогою табличного процесора Excel можна розв’язувати лінійні рівняння. Однак завдяки цьому простому прикладу стало очевидним, що пошук значення параметра формули, що задовольняє її конкретному значенню, – це не що інше, як чисельне розв’язання рівнянь. Іншими словами, використовуючи Excel, можна розв’язувати будь-які рівняння з однією змінною.

Для прикладу наведемо таку задачу: Очікується, що доходи по проекту протягом наступних 4 років складуть відповідно 50000 грн., 100000 грн., 300000 грн., 200000 грн. Визначити, якими мають бути початкові витрати, щоб забезпечити швидкість обороту 10 %.

Для розв'язання такої задачі необхідно також використовувати механізм “Подбор параметра” табличного процесора EXCEL, що викликається командою СервисПодбор параметра, оскільки відсутня відповідна фінансова функція EXCEL.

Нехай клітинки А2:А5 містять значення 50000, 100000, 300000, 200000. Помістимо в клітинку А1 прогнозоване значення витрат –300000. В клітинку В1 введемо функцію ВНДОХ(А1:А5).

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

,

де п – кількість виплат і надходжень;

value_і – значення виплат і надходжень;

R – внутрішня швидкість обороту.

Встановивши курсор у клітинку В1, вибираємо в меню EXCEL команду СервисПодбор параметра і заповнюємо діалогове вікно в такий спосіб:

У результаті в клітинці А1 буде отримане значення витрат по проекту: А1 = –489974.