6.9.5. Спеціальні прийоми роботи в табличному процесорі
Підбір параметра. Це така технологія яка дозволяє знайти необхідне числове значення в одній комірці із списку значень якщо відомий результат який пов’язаний формулою із числовим списком.
Розглянемо задачу яка дана на рис.6.53.
Як визначити кредит за лютий якщо він невідомий але відома формула для розрахунку значення Усього.
Рисунок 6.53. Визначення кредиту за лютий місяць
Для рішення таких задач використовують технологію яка називається Подбор параметра, для того щоб скористатись такою технологією треба виконати команду Сервис ►Подбор параметра, при цьому потрібно щоб у комірці B8 була формула: СУММ(B2:B7). Після виконання команди з’явиться вікно Подбор параметра рис.6.54.
В цьому вікні треба вказати адресу комірки з формулою та вказати її числове значення, крім того треба вказати комірку значення якої треба змінювати.
Рисунок 6.54. Вікно для введення даних для підбору параметра
Після натиску кнопки ОК одержимо інформацію про виконання підбору параметра (рис. 6.55.).
Рисунок 6.55. Інформативне вікно, в якому вказано, що рішення знайдено
А в таблиці яка приведена на рис.6.53. в комірці B2 буде знайдено число 20 рис.6.56.
Рисунок 6.56. Таблиця в якій знайдено рішення
Для підбору параметра необхідно дотримуватись наступних правил:
Підбір параметра виконується для комірок, які зв’язані формулою.
Робимо копію таблиці, з якою і працюємо.
Розташовуємо курсор в комірці з формулою, наприклад, де сума кредитів.
У пункті меню Сервис обираємо команду Подбор параметра.
У діалогову вікні, що з’явиться у рядок Значение вводимо необхідне число.
У рядку Изменяя значение ячейки вводимо посилання на комірку, від якої залежить результат обчислень.
Клацаємо по кнопці ОК.
В результаті програма зробить підбір необхідних значень.
Пошук рішення. Технологія пошуку рішення яка використовується для рішення задач оптимізації це підбір даних в одних комірках таблиці Excel на основі обмежень, накладених на інші взаємозв’язані комірки.
При цьому повинна бути визначена цільова функція, яка пов’язана формулою з комірками в яких відбувається підбір параметрів.
Задачу оптимізації пошуку рішення можна сформулювати у загальному виді у такий спосіб.
Знайти змінні x1,x2, …, xn які задовольняють системі нерівностей або рівнянь, які являються обмеженням для задачі оптимізації
i (x1, x2, x3, …, xn) ≤ bi, i = 1, 2, …, m
і при цьому досягає максимуму (мінімуму чи 0 ) цільова функція, тобто
Z = f (x1,x2, …, xn ) max (min, 0)
Умови позитивності змінних, якщо вони є, теж входять в обмеження: x1 > = 0, x2 > = 0, x3 > = 0, …, x n > = 0.
Оптимізація. Оптимізацію рішення задачі розглянемо на прикладі вирішення задачі. Внутрішній податок на товари і послуги в першому кварталі 2008 року склав 2796 тисяч гривень (рис. 6.57).
В другому кварталі, не змінюючи загальної суми податку (2796 тисяч гривень), варто зменшити на 35- 45 тисяч гривень акцизний збір на вітчизняні товари і збільшити на таку ж суму акцизний збір на імпортні товари.
Рисунок 6.57. Таблиця на перший квартал