Учні повинні знати: використання в програмі Microsoft Ехсеl інструменту Подбор параметра
Учні повинні уміти: використовувати можливості інструмента для отримання розв’язків та прогнозування.
Програмне забезпечення: табличний процесор Microsoft Ехсеl.
Теоретичні відомості
Підбір параметра – це інструмент, який дозволяє відшукати значення одного невідомого параметра, від якого залежить результат.
Цей інструмент дає змогу цілеспрямовано перебрати множину значень одиночного параметра з одночасним контролем результуючого значення. По суті, він “підлаштовує” вихідні дані під бажану відповідь. При цьому чергове значення параметра підставляється у формулу, виконується обчислення – і отриманий результат порівнюється з шуканим (цільовим) значенням.
Під час підбору параметра потрібно визначити три складові:
1) місце розміщення цільової комірки (вміст якої обов’язково має бути формулою);
2) значення, яке має бути досягнуте в цільовій комірці після зміни параметра;
3) комірку, вміст якої (параметр) зміниться для досягнення цільовою коміркою шуканого значення.
Всі три складові об’єднані в діалоговому вікні Подбор параметра, яке викликається з меню Сервис.
Розв’язання з допомогою підбора параметра є наближеними і лише в деяких випадках дають точні значення.
Підбір параметра — це ітераційний процес. Для комірки, яку задано в полі Изменяя значения ячейки, Excel по черзі перевіряє різні значення доти, поки не відшукає найбільш придатне. За замовчуванням програма робить до 100 ітерацій або продовжує обчислювати значення, доки не буде досягнена відносна похибка 0, 001.
Приклад 1
Дана функція y = 2x + 89. З допомогою підбору параметра знайти значення х, при яких y = 0; 0,1; 101; 1001.
Послідовність дій.
1) Внести дані в таблицю.
2) В комірку В3 ввести формулу “=2*A3+89”.
3) Виконати команду Сервис/Подбор параметра. У діалоговому вікні Подбор параметра вказати наступні дані:
• Установить в ячейке - комірку В3;
• Значение – число 0 (виконуючи команду Сервис/Подбор параметра для пошуку інших значень змінної y, потрібно вводити значення 0,1; 101; 1001);
• Изменяя значение ячейки – комірку А3.
В наступному діалоговому вікні буде показано результат обчислення, який автоматично заноситься в комірку А3.
У випадку нелінійних функцій, які пов’язують параметр, що підбирається, зі значенням цільової комірки, може виникнути ситуація, коли не одне, а кілька значень параметра відповідають шуканому значенню цільової функції.
Приклад 2
Дана функція y = 10 – x2. З допомогою підбору параметра знайти значення х, при яких y = 6.
Внести дані в таблицю.
В комірку В3 ввести формулу “=10-A3^2”.
Виконати команду Сервис/Подбор параметра. У діалоговому вікні Подбор параметра вказати необхідні дані.
Шуканому значенню y = 6 відповідають два значення параметра х
(х=2 і х= - 2). Вирішальну роль у тому, яке значення буде знайдено внаслідок підбору, відіграє початкове значення параметра, що вводиться в комірку В3.
При від’ємних початкових значеннях х буде знайдене значення х= -2, що дає у = 6. а при нульовому або додатних початкових значення х буде отримано х=2, що також дає у=6.
У загальному випадку для довільної функції результат підбор параметрів вирішальним чином залежить від початкового значення параметра. Тому доцільно побудувати графік цільової функції, щоб зробити початкові припущення про можливий діапазон початкових значень параметра перед виконанням підбору параметра.
Приклад 3
Використовуючи засіб Подбор параметра, визначити, яку максимальну позику можна взяти на 10 років, щоб придбати квартиру при відсотковій ставці 7% і за умови щомісячної виплати не більш $400.
Послідовність дій
1) Внести потрібні дані в таблицю. Встановити табличний курсор у клітинці В5.
2) Вибрати команду Сервис/Подбор параметра.
3) В отриманому діалоговому вікні Подбор параметра задати посилання на відповідні комірки. У полі Установить в ячейке автоматично з'явиться посилання на комірку В5.
4) В поле Значение ввести бажане значення щомісячних виплат, а саме 400 (число вводиться зі знаком "мінус", оскільки гроші будуть виплачуватися).
5) В поле Изменяя значения ячейки ввести посилання на комірку В2.
6) Клацнути на кнопці ОК у вікні Подбор параметра, після чого з'явиться вікно Результат подбора параметра, де Excel повідомить, що розв’язок знайдено. В клітинці В5 відобразиться значення $400, а в клітинці В2 робочої таблиці – шукане значення позики.
Хід роботи
1. Використовуючи інструмент Подбор параметра знайти:
1) корені квадратного рівняння х2 + 2х + 1 = 0;
2) значення х, при яких у = 2; 3,5; 6,099.
2. Використовуючи інструмент Подбор параметра знайти:
1) корені рівняння х3 + 2х2 – х – 1 = 0;
2) значення х, при яких у = 0,2; 3,45; 100.
3. Використовуючи засіб Подбор параметра, визначити, яку максимальну позику можна взяти на 2 роки при відсотковій ставці 9% і за умови щомісячної виплати не більш 130 грн.
4. Покупець бажає взяти на виплату товар вартістю 2750 грн. Використовуючи засіб Подбор параметра, визначити термін виплати, за умови щомісячного внеску 100 грн. і врахувавши, що в зв’язку з Новорічними святами надається подарунок – відсоткова ставка 0%.
Контрольні запитання
1) Що таке інструмент Подбор параметра?
2) Який механізм роботи інструмента Подбор параметра?
3) Які складові інструмента Подбор параметра?
4) Які задачі можна розв’язати, використовуючи інструмент Подбор параметра?
Практична робота 13
Тема. Використання інструменту Поиск решения.
Мета. Дати поняття про використання в програмі Microsoft Ехсеl інструменту Поиск решения. Дати приклад використання можливостей інструмента для розв’язування економічних задач.
Учні повинні знати: використання інструменту Поиск решения.
Учні повинні уміти: використовувати можливості інструмента Поиск решения для розв’язування економічних задач.
Програмне забезпечення: табличний процесор Microsoft Ехсеl.
Теоретичні відомості
Інструмент Поиск решения дозволяє знайти значення декількох параметрів, необхідних для отримання потрібного результату В цьому випадку можна задати деякі обмеження (умови), які повинні виконуватись під час пошуку розв'язання.
Інструмент Поиск решения є надбудовою Excel. Якщо в меню Сервис відсутня команда Поиск решения, треба завантажити відповідну надбудову, виконавши команду Сервис/Надстройки і в діалоговому вікні Надстройки встановити прапорець Поиск решения.
Процедура пошуку розв'язання дозволяє відшукати таке розв'язання задачі (або декілька розв'язань), при якому значення в заданій клітинці робочого аркуша (в клітинці результату) досягає максимуму чи мінімуму або дорівнює визначеному числу. Цей засіб працює із групою комірок, зв'язаних прямо чи опосередковано із формулою у клітинці результату. Для того щоб отримати потрібний результат за формулою, що міститься у клітинці результату, програма змінює значення у змінних комірках (їх ще називають впливаючими). Звузити множину значень, які використовуються в моделі, можна за допомогою обмежень. Користувач може накладати обмеження на значення змінних комірок комірки результату, а також на комірки, які прямо чи опосередковано пов'язані з комірками результату.
Засіб пошуку розв'язання, як правило, використовують для задач, що задовольняють таким умовам:
1) значення в клітинці результату залежить від значень в інших комірках або формул;
2) значення в змінних комірках належать певному діапазону або задовольняють деяким обмеженням;
Процедуру пошуку розв'язання можна, зокрема, застосовувати для розв'язання різних рівнянь та систем рівнянь.
Приклад 1
Знайти розв’язання системи нелінійних рівнянь:
у = х+4
у = x2 - 3x
Якщо побудувати графіки цих функцій, то буде видно, що вони перетинаються в двох точках, отже, система має два розв’язки.
Для пошуку розв’язків системи рівнянь методом пошуку розв’язку, слід виконати такі дії.
Спочатку потрібно скласти таблицю даних та використати статистичну функцію СРОТКЛ(число1, число2, число3, …), яка дозволяє знайти середнє абсолютних значень відхилень точок даних від середнього. У точках перетину графіків функцій середнє абсолютних значень відхилень повинно бути рівним 0.
1. В комірки А1, А2 і A3 ввести написи Рівняння системи, Середнє абсолютних значень відхилень та Корінь.
2. В комірку В1 і С1 ввести наступні формули:
=В3 + 4
=В3^2 – 3
3. В комірку В2 ввести формулу обчислення середнього абсолютних значень відхилень значень з комірок В1 і С1 від середнього:
=СРОТКЛ(В1; С1)
4. В комірку В3 ввести довільне початкове значення кореня, наприклад 15.
5. Вибрати команду Сервис/Поиск решения, в результаті чого з'явиться діалогове вікно Поиск решения.
6. В поле Установить целевую ячейку ввести посилання на комірку В2.
7. В полі Равной встановити перемикач значению, а в полі вводу залишити задане за замовчуванням значення 0.
8. В полі Изменяя ячейки задати змінну комірку (у цьому прикладі В3).
9. Клацнути на кнопці Выполнить, щоб активізувати процедуру пошуку розв'язання.
Якщо розв'язання знайдене, з'явиться діалогове вікно Результаты поиска решения, а в таблиці відобразиться обчислене значення кореня 3,19. Встановивши відповідні перемикачі в цьому вікні, можна замінити вихідні значення в комірках новими або відновити вихідні значення в змінних комірках.
Якщо в комірку В3 ввести інше початкове значення кореня, буде знайдено інший корінь.
Приклад 2
Розв'язати задачу маркетингу: визначити витрати на рекламу, за яких прибуток від реалізації товару буде максимальним.
Розв'язання цієї задачі допомагає з'ясувати, чи варто вкладати додаткові засоби в рекламу, щоб збільшити прибуток.
Вихідні дані задачі складаються з 2 блоків: Заплановані показники та Дані про продукцію.
У І кварталі заплановані такі показники реалізації:
• Сезонний чинник (комірка В2) - 0,9. Сезонний чинник відображає коливання попиту на товар в залежності від пори року (наприклад, взимку частіше купують теплі речі);
• Витрати на заробітну плату персоналу (комірка В8) - 8 тис. грн.
• Витрати на рекламу (комірка В9) –10 тис. грн.
Дані про продукцію:
• Ціна реалізації (комірка В17) – 40 грн.
• Собівартість (комірка В18) – 25 грн.
Обчислення показників виконують так:
• Обсяг збуту продукції (комірка В3) залежить від сезонного чинника і витрат на рекламу:
= 35*В3*(В10+3000)^0,5.
• Прибуток з обігу (комірка В5) визначають як очікувану кількість проданих одиниць продукції, помножену на собівартість продукції, тому в комірку В5 введемо формулу:
= В4*В18.
• Собівартість реалізованої продукції мовою математики подається так:
В6=В4*В19.
• Валовий прибуток визначається так:
=В5 – В6.
• Припустимо, “накладні витрати” фірми нараховуються в обсязі 15% прибутку з обігу, тобто в комірку В5 введемо формулу:
=0, 15 * В5.
• “валові витрати” обчислимо як суму витрат на заробітну плату персоналу, рекламу та накладні витрати, тобто в комірку В12 введемо формулу:
=СУММ(В9: В11).
• прибуток від реалізованої продукції визначимо як валовий прибуток мінус валові витрати, тобто в комірку В14 запишемо формулу:
В14 = В8 – В12.
• рентабельність:
В15 = В14/В5.
Якщо перелічені дані та формули ввести в таблицю, то результати обчислень виглядатимуть так, як показано на малюнку.
Активізуємо Сервис/Поиск решения та опишемо умови задачі:
• задати комірку В14 як таку, що містить цільову функцію;
• встановити мету оптимізації Максимальное значение;
• вказати комірку, значення якої змінюватиметься під час пошуку розв'язання – В10;
• під час встановлення параметрів вказати на нелінійність моделі. Для інших параметрів використати параметри за замовчуванням, які застосовують для розв'язання більшості задач.
Після натиснення кнопки Выполнить отримається результат оптимізації.
Висновок. Як видно з отриманого результату, при витратах на рекламу в розмірі 17 093 грн. прибуток від реалізації товару буде максимальним. Однак слід зазначити, що максимізація прибутку не обов'язково відповідає найвищому значенню рентабельності, її значення зменшилося до 8%.
Приклад 3
Для виготовлення виробів х, у, z використовують три види сировини: І, IІ, III. У таблиці задано норми витрат сировині на один виріб кожного виду, ціна одного виробу, а також кількості сировини кожного виду, які можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний?
| x | y | z | Загальна кількість сировини | |
| І | ||||
| ІІ | ||||
| ІІІ | ||||
| Ціна |
Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити цілі значення x, y, z, для яких досягається максимум функції прибутку f = 9х + 10у + 16z за таких обмежень:
18х + 15 у + 12 z <= 360
6х + 4у + 8z <= 192
5х + 3у + 3z <= 180
x, у, z >= 0; x, у, z – цілі.
Розв'язування. Для розв'язування потрібно виконати таку послідовність дій:
1. Коміркам А1, В1, С1 присвоїти імена х, у, z, виконавши команду Вставка/Имя/Присвоить/ … ввести відповідні імена.
2. У комірку D1 ввести формулу: = 9*х + 10* у + 16* z;
3. Виконати команду Сервис/Поиск решения.
4. Задати адресу цільової комірки D1 і зазначити дію Достижение максимума функции.
5. Задати комірки, де має міститися розв'язок: х; у; z;
6. За допомогою кнопки Добавить додати обмеження у вигляді дев’яти умов:
х <= (360 – 15*y – 12*z) 18
у <= (192 – 6*x – 8*z)/4
z <= (180 – 5*x – 3 *y)/3
x, у, z >= 0; x, у, z – цілі.
7. Натиснути на кнопку Параметры, зазначити, що модель лінійна.
8. Натиснути кнопку Выполнить для отримання розв’язку.
(Відповідь: x = 0, у = 8, z = 20, f = 400)
Хід роботи
Завдання 1
Знайти розв’язання системи лінійних рівнянь, використовуючи засіб Поиск решения:
у = 5х+4
у =-2 x+9
(Відповідь:0,71)
Завдання 2
Знайти розв’язання системи нелінійних рівнянь, використовуючи засіб Поиск решения:
у = х2 – 4
у = x2+2х+1
(Відповідь:-2,5)
Завдання 3
Розв'язати приклад 2 з теоретичних відомостей.
Завдання 4
Розв'язати приклад 3 з теоретичних відомостей.
Завдання 5
Розв'язати приклад 3 з теоретичних відомостей з такими вихідними даними:
| x | y | z | Загальна кількість сировини | |
| І | ||||
| ІІ | ||||
| ІІІ | ||||
| Ціна |
Контрольні запитання
1) Для чого призначений інструмент Поиск решения?
2) В чому полягає процедура пошуку розв'язання?
3) Для розв’язування яких задач використовують засіб Поиск решения?
Висновки
У збірнику подаються лабораторно-практичні завдання для вивчення основ роботи з табличним процесором Microsoft Ехсеl, коротко і доступно розглянуто основні етапи створення, редагування, обробки робочих таблиць, способи побудови графіків та функцій, виконання обчислень з допомогою формул і функцій, наведено приклади роботи зі списками, опис інструментів аналізу та оптимізації даних.
Виконавши запропоновані завдання, учні навчаться:
q вносити дані в комірки, використовувати маркер автозаповнення, вставляти та видаляти рядки та стовпці;
q використовувати форматування чисел та шрифтів, використовувати умовне форматування;
q виконувати обчислення в таблицях, вводити формули, використовувати формули автоматичного обчислення, використовувати в формулах відносні та абсютні адреси;
q фіксацувати заголовки, додавати та видіяти примітки, встановлювати захист даних в комірках;
q використовувати математичні функцій, вводити функції в комірки;
q використовувати логічні та статистичні функції, вводити функції в комірки;
q будувати графіки та діаграми, використовувати їх форматування;
q використовувати фінансові функцій, вводити функції в комірки;
q створювати список, використовувати форму вводу, виконувати сортування та фільтрацію даних;
q створювати зведені таблиці для обчислення підсумків;
q використовувати можливості аналізу “що-як” для обчислень;
q використовувати можливості інструмента для отримання розв’язків та прогнозування;
q використовувати можливості інструмента Поиск решения для розв’язування економічних задач.
Література
1. Леонтьев В.П. Новейшая энциклопедия персонального компьютера 2003 —М., ОЛМА-ПРЕСС, 2003 – 920 с.:ил.
2. Microsoft Excel 2003 / Стислий курс. —М., Видавничий дім “Вільямс”, 2004. — 288 с.: іл.
3. Економічні задачі в Excel / Упоряд. Вовковінська Н.. – К., Ред. загальнопед. газ., 2005. – (Б-ка “шкільного світу”)