дено результат вирішення задачі за допомогою функції СТАВКА відповідно з малюнком 2.4.
Рис. 2.4 Рішення завдання
Засіб Підбір параметра дозволяє знайти аргумент, при якому виходить задаваемое рішення. Excel знаходить відповідь за допомогою послідовних ітерацій. Досить вказати цільову комірку, її бажане значення і змінювану осередок-параметр, що впливає на вміст цільової осередки. При цьому осередок-параметр повинна містити значення, але не формулу, і впливати на результат, який слід отримати. Цільова ж осередок повинна містити формулу, що включає посилання на комірку параметр (прямо чи опосередковано).
Результат використання Подбора параметра наведено нижче відповідно з малюнком 2.5.
Результати розрахунків, графіки та діаграми
Рис. 2.5 Результат роботи Підбір параметра
2.3 Оптимізаційні задачі в MS Excel
Постановка задачі та її математична формулювання
Четверо робітників можуть виконувати п'ять видів робіт. Вартість виконання кожного виду робіт становить: для першої робітника - 1, 6, 3, 5, 7; для другого - 5, 2, 7, 8, 3; для третього - 3, 5, 1, 9, 2; для четвертого - 6, 4, 2, 10, 5. Скласти план виконання робіт таким чином, щоб кожен робітник був зайнятий не більше ніж на одній роботі, а сумарна вартість виконаних робіт була мінімальною.
Опис вхідний і вихідний інформації
Засіб аналізу Пошук рішення застосовується для підбору оптимального рішення при заданих обмеженнях. Формулювання завдань, що вирішуються за допомогою цього засобу звичайно являє собою систему рівнянь з декількома невідомими і набір обмежень на рішення. Зазвичай за допомогою надбудови Пошук рішення вирішуються наступні завдання:
? Асортимент продукції (максимізація випуску товарів при обмеженнях на сировину для виробництва цих товарів).
? Складання штатного розкладу для досягнення найкращих результатів при найменших витратах.
? Планування перевезень (мінімізація витрат на транспортування товарів за умови задоволення потреб споживачів).
? Складання суміші (одержання заданої якості суміші при найменших витратах)
Всі ці завдання мають наступні властивості:
1. Наявність єдиної мети.
2. Наявність обмежень, що виражаються, як правило, у вигляді нерівностей.
. Наявність набору вхідних значень-змінних, безпосередньо або побічно впливають на обмеження і на що оптимізуються величини.
Правильне формулювання обмежень є найвідповідальнішою частиною при створенні моделі для пошуку рішення, наприклад:
1. Якщо в моделі в наявності декілька періодів часу, величина матеріального ресурсу на початок наступного періоду повинен дорівнювати величині цього ресурсу на кінець попереднього періоду.
2. У моделі поставок величина запасу на початок періоду плюс кількість отриманого повинна дорівнювати величині запасу на кінець періоду плюс кількість відправленого.
. Деякі величини в моделі по своєму фізичному глузду не можуть бути негативними або дробовими.
Обмеження мають той же синтаксис, що і логічні формули. Але, якщо у знайденому вирішенні логічні формули будуть виконані точно, то обмеження - з деякою можливої ??похибкою. Величина цієї похибки задається параметром Відносна похибка і за замовчуванням дорівнює 0,000001.
Хід вирішення.
Підготовку робочого аркуша здійснюємо відповідно до малюнком 2.6, формули для розрахунку наведені нижче в таблиці.
Таблиця: Формули для розрахунку в задачі
Малюнок 2.6 Підготовка робочого аркуша для вирішення завдання
Встановлюємо обмеження у вікні Пошук рішення, відповідно до малюнком 2.7. У вікні Параметри пошуку рішення необхідно також встановити прапорець Лінійна модель.
Малюнок 2.7 Установка параметрів у вікні Пошук рішення
Результати розрахунків, графіки та діаграми
Рішення завдання представлено на малюнку 2.8.
Малюнок 2.8 Рішення завдання
2.5 Аналіз бізнес-ситуацій за допомогою Диспетчера сценаріїв в MS Excel
Постановка задачі та її математична формулювання
Вас просять дати в борг
