Теоретичні відомості. Контрольні запитання

Практична робота 13

Контрольні запитання

Хід роботи

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) Які задачі можна розв’язати, використовуючи інструмент Подбор параметра?

Тема. Використання інструменту Поиск решения.

Мета. Дати поняття про використання в програмі 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)

Дата добавления: 2015-05-24