Подбор параметра и поиск решения

Відкрийте робочу книгу "Поиск решения" і завантажте собі копію.

Результати практичної роботи:

Прізвище, ім'я	Оцінка
Похилова, Прошинская, Вересова	7
Познанский, Кильчанов	10
Цыганов, Федоренко, Иваненко	11
Ханишин, Скляр, Китаев	7
Кемарский	12
Натарова	?
Борисевич, Крячун	6
Кузнецова, Полухова, Лоик, Дударь	8
Брилевич, Ясманович	9
Петренко	11

Що таке метод перебору?

 За великим рахунком, можна виділити дві групи методів вирішення будь-яких завдань:

Алгебраїчні методи	Чисельні методи
За невеликий відрізок відразу виходить точну відповідь	Зводять завдання до перебору варіантів
Чи можливо застосувати лише до невеликого числа не дуже складних завдань.	Дають результат у вигляді числового значення з похибкою

Існує клас задач, які легше вирішити методом перебору, ніж методом «наукового тику». Інструмент Підбір параметра допомагає вирішити задачу, коли відомо, що повинно вийти у відповіді, але не відомо, яке значення має мати одна з змінних. Говорячи простою математичною мовою, ми шукаємо рішення рівняння з одним невідомим.

Підбір параметра в Excel і приклади його використання

«Підбір параметра» - обмежений по функціоналу варіант надбудови «Пошук рішення». Це частина блоку завдань інструменту «Аналіз« Що-Якщо ".

У спрощеному вигляді його призначення можна сформулювати так: знайти значення, які потрібно ввести в одиночну формулу, щоб отримати бажаний (відомий) результат.

Де знаходиться «Підбір параметра»?

Відомий результат якоїсь формули. Є також вхідні дані. Крім одного. Невідоме вхідний значення ми і будемо шукати. Розглянемо функцію «Підбір параметра» в Excel на прикладі.

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

Відсоткова ставка невідома, тому осередок порожня. Для розрахунку щомісячних платежів використовуємо функцію ПЛТ.

Коли умови задачі записані, переходимо на вкладку «Дані». «Робота з даними» - «Аналіз« Що-Якщо "-" Підбір параметра ».

В полі «Встановити в осередку» задаємо посилання на клітинку з розрахункової формули (B4). Поле «Значення» призначене для введення бажаного результату формули. У нашому прикладі це сума щомісячних платежів. Припустимо, -5 000 (щоб формула працювала правильно, ставимо знак «мінус», адже ці гроші будуть віддаватися). В поле «Змінюючи значення осередки» - абсолютне посилання на комірку з потрібним параметром ($ B $ 3).

Після натискання ОК на екрані з'явиться вікно результату.

Щоб зберегти, натискаємо ОК або Enter.

Функція «Підбір параметра» змінює значення в осередку В3 до тих пір, поки не отримає заданий користувачем результат формули, записаної в осередку В4. Команда видає тільки одне рішення задачі.

Рішення рівнянь методом підбору параметрів

Функція «Підбір параметра» ідеально підходить для вирішення рівнянь з одним невідомим.

Візьмемо для прикладу вираз: 

20 * х – 20 / х = 25. 

Аргумент х - шуканий параметр. Нехай функція допоможе вирішити рівняння підбором параметра і відобразить знайдене значення в осередку Е2.

У комірку Е3 введемо формулу:

= 20 * Е2 – 20 / Е2.

А в клітинку Е2 поставимо будь-яке число, яке знаходиться в області визначення функції. Нехай це буде 2.

Запускаємо інструмент і заповнюємо поля:

• «Встановити в осередку» - Е3 (осередок з формулою);
• «Значення» - 25 (результат рівняння);
• «Змінюючи значення осередки» - $ Е $ 2 (осередок, призначена для аргументу х).

Результат функції:

Знайдений аргумент відобразиться в зарезервованої для нього осередку.

Рішення рівняння: х = 1,80.

Функція «Підбір параметра» повертає в якості результату пошуку перше знайдене значення. Незалежно від того, скільки рівняння має рішень. Якщо, наприклад, в клітинку Е2 ми поставимо початкове число -2, то рішення буде іншим.

Функція «Підбір параметра» працює правильно, якщо:

• Значення бажаного результату виражено формулою;
• Всі формули написані повністю і без помилок.

Надбудова "Пошук рішення" і підбір декількох параметрів в Excel

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

Основні відмінності між пошуком рішення і підбором параметра:

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

 Підключення надбудови «Пошук рішення»

В меню Office вибрати «Параметри Excel» і перейти на вкладку «Надбудови». Тут буде видно активні і неактивні, але доступні надбудови.

Якщо потрібна надбудова неактивна, то потрібно перейти за посиланням «Управління» (внизу таблички) та встановити надбудову. З'явиться діалогове вікно, в якому потрібно відзначити галочкою «Пошук рішення» і натиснути ОК.

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

Для нормальної роботи невеликого підприємства вистачить 4-6 робочих, 7-9 продавців, 2 менеджера, завідувач складом, бухгалтер, директор. Потрібно визначити їх оклади.

Обмеження: місячний фонд зарплати мінімальний; оклад робітника - не нижче прожиткового мінімуму в 100 доларів. Коефіцієнт А показує: у скільки разів оклад спеціаліста більше окладу робітника.

Таблиця з відомими параметрами:

• менеджер отримує на 30 доларів більше продавця (см. коефіцієнт В);
• завідувач складом - на 20 доларів більше робочого;
• директор - на 40 доларів більше менеджера;
• бухгалтер - на 10 доларів більше менеджера.

Знайдемо зарплату для кожного фахівця (на малюнку все зрозуміло).

Переходимо на вкладку «Дані» - «Аналіз» - «Пошук рішення» (так як ми додали надбудову, тепер вона доступна).

Заповнюємо меню. Щоб вводити обмеження, використовуємо кнопку «Додати». Рядок «Змінюючи осередки» повинна містити посилання на ті осередки, для яких програма буде шукати рішення. Заповнений варіант буде виглядати так:

Натискаємо кнопку «Виконати» і отримуємо результат:

Тепер ми знайдемо зарплату для всіх категорій працівників і порахуємо ФОП (Фонд Оплати Праці).