Рівень 3. Урок 1. Пошук розв’язку в MS Excel

Підбір параметра — інструмент MS Excel, призначений для обчислення вхідного значення (параметра) деякої формули для того, щоб формула давала потрібний результат.

Таблиці підстановки — це діапазон суміжних комірок, в яких показані результати обчислення певних формул при зміні значень, що впливають на ці формули.

Завдання 1. Обчислення діаметра, довжини кола та площі круга.

1. Створити книгу MS Excel.

2. Знайти радіус при якому площа круга становить 100 (кв. од.). Для цього:

Ø Вибираємо Сервис — Подбор параметра

Ø В полі Установить в ячейке: вказуємо посилання на комірку C6.

Ø Значение: потрібне значення (в даному випадку 100).

Ø Изменяя значение ячейки: C3

Ø OK

3. Знайти радіус кола, при якому його довжина становитиме 500.

Завдання 2. Позика на покупку.

1. На іншому листі книги заповнити наступні комірки:

2. За допомогою сервісу Подбор параметра знайти:

• Ø Скільки місяців доведеться виплачувати позику 25000 з відсотковою ставкою 7%, виплачуючи по 2000.
• Ø Сума позики — 50000, строк погашення — 5 років. Який максимальний відсоток допустимий, щоб клієнт міг розрахуватися, виплачуючи не більше 1000 за місяць?
• Ø Яку максимальну суму можна позичити виплачуючи по 100 грн на протязі 10 років з процентною ставкою 20%?

Завдання 3. Одновимірна таблиця підстановки

Побудувати графіки функцій \(y=x^{1/3}+x^{1/2}\) та \(y=x^2/(x+1)\)  на [0;5]  з кроком 0,5 використовуючи таблицю підстановки.

1. В комірках A3:A13 вводимо числа від 0 до 5 з кроком 0,5.

2. В комірці B2 вводимо першу формулу, посилаючись, наприклад, на комірку B1. В С2 — другу формулу.

3. Виділяємо діапазон A2:С13.

4. Данные — Таблица подстановки…

5. В поле «Подставлять значения по строкам в:» вказуємо посилання на комірку B1:

6. ОК

7. По отриманих даних побудувати графіки.

Завдання 4. Пошук рішення

У буфеті студентської їдальні реалізуються бутерброди 3-х видів А, В, С. Їх підготовка і реалізація вимагають використання 3-х видів ресурсів, норми витрат яких наведені у таблиці:

Види ресурсів	Норми витрат ресурсів на 1 партію бутербродів			Запас ресурсів
	А	В	С
І	2	1	2	38
ІІ	1	3	2	44
ІІІ	3	2	1	40
Прибуток (грн.)	7	6	4

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

1. Побудувати відповідну таблицю в MS Excel.

2. Задати початкові значення для x₁, x₂, x₃. (x₁ — кількість виготовлених бутербродів виду А, x₂ — виду B, x₃ — виду C).

3. В комірці F3 обраховуємо число витрачених ресурсів виду І.

4. Аналогічно в комірках F4 та F5 число витрачених ресурсів ІІ та ІІІ відповідно.

5. В комірці E8 обраховуємо отриманий прибуток.

6. Сервис — Поиск решения…

7. Цільова комірка: E8. Будемо шукати максимальне значення, змінюючи B9:B11.

8. Ограничения: Добавить

9. Вказуємо, що число використаних ресурсів не має перевищувати число доступних:

10. Оскільки кількість партій бутербродів має бути числом цілим, додаємо наступні обмеження:

11. Вибираємо «Выполнить»

12. ОК

Завдання 5. Двовимірна таблиця підстановки.

Користуючись довідковою системою MS Excel побудувати двовимірну таблицю підстановки (наприклад, таблицю множення чисел від 2 до 20).

Завдання 6. Ще раз пошук рішення

Виконати Завдання 1 використовуючи Поиск решения.