Розв’язання прикладних задач в Excel

 

 

Мета лекції –розглянути основні принципи знаходженнярозв‟язку прикладних задач у Microsoft Excel.

Питання лекції

1 Підбір параметрів.

 

2 Пошук рішення.

 

3 Робота з макролсами.

 

Підбір параметрів

 

Щоб визначити значення однієї комірки при змінені зна-чення іншої (такі комірки мають бути пов‟язані формулою) ви-користовують підбір параметрів.

 

Розглянемо цю процедуру на прикладі.

 

Приклад 7.1 Необхідно визначити термін кредиту,за яко-го перший внесок (поле «Всього до сплати, грн..») становить 500 грн. (у таблиці на рис. 7.1 – 566.7);

 

Розв’язання

Формули для розрахунку відображені на рис. 7.2.

 

 

Рисунок 7.1 – Розрахунок початкового внеску за кредит

 

Рисунок 7.2 – Формули розрахунку для початкового внеску за кредит

 

Для цього спочатку встановлюють курсор у комірку G2, після чого необхідно активізувати команди Сервис→Подборпараметра. Внаслідок цих дій з‟явиться вікно(рис.7.3),в яко-му в полі Установить в ячейке вводять адресу комірки, зна-чення якої необхідно знайти; в полі Значение: – числове зна-чення, яке потрібно знайти для активної комірки (G2); в полі Изменяя значение ячейки –адресу комірки С2,значення якоїнеобхідно знайти та активізувати кнопкою ОК.

 

 

Рисунок 7.3 – Підбір зна-	Рисунок 7.4 – Результат підбо-
чення	ру

 

Після чого виведеться результат виконання операції (рис. 7.4). Якщо рішення знайдено, то при активізації кнопки ОК нове значення залишається в комірці G2, а кнопки Отмена

– відновлюється попереднє значення.

 

У результаті знайдений термін кредиту становить 28.6 мі-сяців (рис. 7.5)

 

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

 

 

Рисунок 7.5 – Результат розрахунку

 

Приклад 7.2 За допомогоюExcelрозв‟язати рівнянняln(x) 10.

Тут необхідно в комірку А1 ввести значення 10, а у комір-ку В1 ввести формулу =Ln(A1) та натиснути клавішу Enter

 

(рис. 7.6).

 

 

Рисунок 7.6 – Завдання рівняння в Excel

 

Потім необхідно виділити комірку В1, та за допомогою меню Сервис→Подбор параметра викликати вікно Подбор па-

 

раметра. У цьому вікні задаємо значення підбору параметрів: Установить в ячейке –адресу комірки В1,де знаходиться фо-рмула, значення якої необхідно знайти, Значение – значення 10 (праву частину рівняння), Изменяя значение ячейки – адресу комірки А1, значення якої буде змінюватися.

 

 

Рисунок 7.7 – Підбір параметра

 

Після виконання одержимо результат, що зображено на рис. 7.8. Отже, результатом розв‟язку рівняння буде x 22025.84.Правильність результату можна перевірити за до-помогою функцій Excel, якщо в будь-якій комірці набрати таку функцію =LN(A1), де А1=22025,84. Результат виконання цієї функції буде 9,999972. Таким чином, рівняння розв‟язане прави-льно.

 

 

 

Рисунок 7.8 – Результат виконання пошуку параметра

Пошук рішення

 

 

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

 

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

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

 

Наприклад, на основі таблиці з обчисленнями прибутку за-лежно від витрат на рекламу можна визначити такі оптимальні витрати на рекламу, які забезпечать максимальний прибуток тощо.

 

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

 

Комірка з кінцевим значенням називається цільовою комі-ркою, а комірки з вхідними даними, що підлягають зміні, нази-ваються змінними комірками. Цільова комірка обов‟язково по-винна містити формулу або функцію, параметрами якої є зна-чення змінних комірок.

 

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

 

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

Поиск решения (рис.7.9).

 

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

 

Рисунок 7.9 – Вікно пошуку рішень

 

У вікні 1 – адреса цільової комірки, 2 – значення цільової комірки, яке необхідно встановити, 3 – діапазон значень, що змінюються, 4 – вікно задання обмежень, які можна добавити за допомогою кнопки Добавить, які можна редагувати за допомо-гою кнопки Изменить. Кнопка Выполнить активує програму.

 

Для кращого розуміння даного питання розглянемо прик-

лад.

 

 

Приклад 2.1 У таблиці на рис.7.10сумарна кількість това-рів Тип 1 – Тип 6 становить 1610 шт., дані витрати на кожну одиницю товару різного типу. Необхідно обчислити загальну вартість товарів. Визначити кількість одиниць продукції кожно-го типу, яку має виготовляти фірма, якщо на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, якщо на виготовлення всієї продукції виділяється 500000 грн.

 

Розв’язання

 

Спочатку обчислюються загальні витрати для кожного ти-пу товару за формулою

 

Загальні витрати=Кількість * Витрати на одиницю.

Спочатку створюється математична модель.

 

Позначимо кількість одиниць продукції першого типу як x ₁,другого типу–як x ₂,третього типу– x ₃і т.ін.

 

За умов задачі на виробництво продукції виділяється 500000 гривень, тобто загальні витрати мають сягати до 500000, то цільова функція матиме вигляд:

F (x_i) 300 x ₁200 x ₂100 x ₃250 x ₄80 x ₅

75 x ₆ 500000

 

На значення параметрів x_i за умовою задачі накладаються

 

обмеження. Оскільки виготовляється продукція, то значення комірок від В2 до В7 повинно бути цілим, оскільки половину від товару не виготовляють, а виготовляють цілу одиницю това-ру. Також значення цих комірок повинно бути додатним, оскі-льки одиниці товару не може бути від‟ємним.

 

Запишемо обмеження математично:

x 1, x 2, x 3, x 4, x 5,	x 6	0,
x 1, x 2,	x 3, x 4,	x 5,	x 6	цілі,
^x 1 ^x 2	^x 3 ^x 4	^x 5 ^x 6	2000.

 

 

 

Рисунок 7.10 – Таблиця товарів різного типу

 

Оскільки на даному обладнанні не можна виготовляти бі-льше ніж 2000 одиниць продукції, то на сумарну кількість това-ру (комірку В8) необхідно накласти обмеження ≤ 2000.

 

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

 

Викличемо програму Поиск решения, після чого з‟явиться вікно програми (рис. 7.11). У зоні Установить целевую ячейку

 

треба вказати адресу цільової комірки, тобто D8, і показати, якої величини повинно бути значення комірки, тобто 500000.

 

У зоні Изменяя ячейки необхідно вказати адреси змінних комірок, тобто В2:В7, оскільки змінюється кількість одиниць продукції.

 

 

Рисунок 7.11 – Вікно Поиск решения

 

У зоні Ограничения необхідно занести обмеження, натис-нувши на кнопку Добавить. З‟явиться вікно для обмежень (рис.

 

7.12).

 

Рисунок 7.12 – Вікно Добавление ограничения

 

У полі Ссылка на ячейку вказується адреса комірок з об-меженнями, посередині вибирається умова (>=, <=, =, цел., дво-ичн.), а праворуч у полі Ограничение заноситься значення об-меження.

 

Кожного разу після занесення обмеження натискається кнопка Добавить, доки всі обмеження не будуть вказані, а по-тім натискається кнопка Ок.

 

Потім натискається кнопка Выполнить. Через деякий час з‟являється повідомлення, що зображене на рис. 7.13.

 

Рисунок 7.13– Результати пошуку рішення при знайденому рішенні

 

Якщо економіко-математична модель створена неправиль-но (обмеження суперечать одне одному або їх недостатньо для визначення оптимального рішення), видається повідомлення, показане на рис. 7.14.

 

Рисунок 7.14 – Результати пошуку рішення за відсутності рішення

 

На основі знайденого рішення можна створити Звіт за ре-зультатами обчислень (тип Отчета – Результаты), який міс-титиме інформацію про попередні значення і отримані значен-ня, а потім натиснути Ок.

 

Після виконання пошуку оптимального розв‟язку з‟явиться таблиця з оптимальним значенням (рис. 7.15).

 

 

Рисунок 7.15 – Таблиця з оптимальним значенням

 

Звіт показаний на рис. 7.16

 

Рисунок 7.16 – Звіт знайденого оптимального розв’язку

Робота з макросами

 

Макроси використовують для автоматизації повторюваних

дій.

Макрос –певна послідовність дій,що записана в модулі

 

Visual Basic.

 

Макрос може бути використаний за допомогою команд меню, створеної користувачем комбінації клавіш, графічних об‟єктів панелі інструментів Формы або вбудованих рисунків чи компонентів панелі Рисование.

 

Перед тим, як записати або написати макрос, необхідно спланувати кроки і команди, які він буде виконувати. Якщо під час запису макросу була допущена помилка, дія, що виправляє її, буде також записана. Щоразу при запуску макросу осі дії, ви-конані користувачем, інтерпретуються у вигляді інструкцій мо-ви VBA, які зберігаються у спеціальному модулі, приєднаному до робочої книги. Кожному макросу надається ім‟я, за яким його можна викликати для виконання, відредагувати та знищити, як-що макрос містить помилки або якщо він уже не потрібний.

Для активізації макросу необхідно активізувати команди

 

Сервис→Макрос →Начать запись,внаслідок чого на екраніз‟явиться вікно, що зображене на рис. 7.17.

 

Рисунок 7.17 – Створення макросу

 

У поле Имя макроса необхідно ввести ім‟я макросу. Пер-шим символом в імені макросу має бути літера, іншими симво-лами можуть бути літери, цифри або знаки підкреслення, в імені макросу не ставлять пробілів (замість них використовують знаки підкреслення). У цьому самому вікні можна створити комбіна-цію клавіш для виконання макросу (наприклад, Ctrl+Q). Для цього в полі Сочетание клавиш вводять літеру (не цифру). У полі Описание можна створити коментар до дій, які виконує макрос.

 

Макрос активізується кнопкою Ок, внаслідок чого на ек-рані з‟являється панель Остановить запись (рис. 7.18), що міс-

 

тить дві кнопки (Остановить запись та Относительная ссыл-

 

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

 

 

Рисунок 7.18 – Панель Остановить запись

 

Потім необхідно задати макрокоманди, що записуються в макрос. Після запису всіх команд необхідно активізувати кнопку

Остановить запись.

 

Для виконання макросу необхідно активізувати команди Сервис→Макрос →Макросы. З переліку вибирають потрібниймакрос та активізують його за допомогою кнопки Выполнить

 

(рис. 7.19).

 

Рисунок 7.19 – Вікно для виконання та редагування макросу

 

Макрос активізують також за допомогою кнопки. Для цьо-го відкривають панель інструментів Формы (рис. 7.20), активі-зують кнопку Кнопка, створюють її на робочому листі Excel. Внаслідок цього на екрані з‟являється вікно з переліком макро-сів. Необхідно вибрати потрібний та активізувати його кнопкою Ок. Створеній кнопці для зручності можна присвоїти ім‟я.

 

Рисунок 7.20 – Панель інструментів Формы

 

 

Призначення інструментів панелі інструментів Формы по-дані в таблиці 7.1.

 

Таблиця 7.1 – Призначення інструментів з панелі інструментів

 

Формы

Вигляд	Назва	Виконувані дії
	Кнопка	Використовується для призначення
		дії макросу кнопці. Після натискан-
		ня на кнопку виконуються дії мак-
		росу
	Флажок	Це елемент	управління,	який
		пов‟язується з коміркою і набуває
		значення ИСТИНА та ЛОЖЬ залеж-
		но від значення зв‟язаної комірки.
		Пов‟язана комірка має логічний тип
		даних і може набувати значення так
		(встановлений	прапорець)	або ні
		(відсутній прапорець)
	Переключатель	Використовують здійснення пере-
		микань
	Поле со спи-	Використовують для задання поля
	ском	списку
	Список	Використовують для вибору елеме-
		нтів зі списку
	Надпись	Використовують для організації
		надписів
	Полоса про-	Використовують для утворення
	крутки	смуги прокручування
	Счетчик	Використовують для створення лі-
		чильника
	Сетка	Використовують для вмикання та
		вимикання відображення сітки

Для виконання макросу за допомогою інших графічних об‟єктів необхідно вставити такий об‟єкт в Excel, клацнути на ньому правою клавішею мишки, активізувати команди Назна-

 

чить макрос, вибрати потрібний макрос із переліку і активізува-ти кнопкою Ок.

 

Параметри створеного макросу можна змінити. Для цього активізують команди Сервис→Макрос→Макросы, внаслідок чого на екрані з‟являється вікно, в якому з переліку макросів обирають потрібний. Після цього активізують кнопку Парамет-ры та вносять потрібні зміні та активізують кнопкою Ок.

ВИСНОВКИ

 

Отже, електронна таблиця Microsoft Excel має потужні за-соби для розв‟язання прикладних задач: підбір параметра, по-шук рішення, макрокоманди.

Повинні знати

 

Після опрацювання теоретичного та практичного матеріа-

 

лу за темою «Розв’язок прикладних задач в Excel» студент повинен чітко знати основні застосування Excel при розв‟язанні прикладних задач: підбір параметра, пошук рішень та вміти створювати макрокоманди за допомогою панелі інструментів

 

Форми.

Список літератури

 

1. Інформатика: Комп‟ютерна техніка. Комп‟ютерні техно-логії: посіб. / за редакцією О. І. Пушкаря. – К.: ВЦ «Академія»,

2001.– 696с. – С. 408 – 425.

 

2. Баженов В.А. Інформатика. Комп‟ютерна техніка. Комп‟ютерні технології: підручник / В. А. Баженов. – 2-ге ви-

дання. – К.: Каравела, 2007.– 640с. – С. 330 – 335.

 

3. Тхір І. Л., Посібник користувача ПК / І. Л. Тхір, В. П. Галушка, А. В. Юзків. – 2-ге видання. – Тернопіль: СМП «Ас-

 

тон», 2002. – 718с. – С. 629 – 630.

 

4. Бондаренко С. Excel 2003. Популярный самоучитель

/ С. Бондаренко, М. Бондаренко. – СПб.:Питер, 2005. – 320с. –

 

С. 264 – 270.

5. Кузьмин В. Microsoft Office Excel 2003: учебный курс /

В. Кузьмин. – СПб.:Питер, 2004. – 493с. – С. 191 – 242.

 

6. Юдин В. И. Учебник в Microsoft Excel XP. – Крама-торск.: Электронный учебник. – Глава 8. Сводные таблицы; Глава 9. Инструменты анализа данных.

 

7. Дибкова Л. М. Інформатика і комп‟ютерна техніка: навч.

посіб. / Л. М. Дибкова. – 2-ге вид., перероб. і доповн. – К.:

 

Академвидав, 2007. – 416с. – С. 185 – 188, 194 – 211.

 

8. Корнеев В. П. Информатика и компьютерная техника: учебное пособие в 4-х частях. – Часть III. Обработка информа-ции с помощью электронных таблиц Microsoft Excel / В. П. Кор-неев, В. Й. Николайчук. – 2-е издание. – К.: ПП Графіка, 2005.

 

– 270с. – С. 97 – 122.

 

Лекція 8