Розглянемо як розв’язувати рівняння за допомогою електронних таблиць, які дозволяють швидко, правильно та раціонально розв’язати рівняння, побудувати графік за потреби.
Розв’язування задач на Підбір параметра
У багатьох задачах певний результат є відомим, а от значення параметрів, за яких цей результат досягається, — ні. Як приклад можна навести задачу, у якій потрібно визначити, через скільки годин скисне молоко або за якого обсягу випуску продукції фірма отримає прибуток у 1 000 000 грн. У математиці клас таких задач є найширшим. Це, зокрема, задачі на розв'язання алгебраїчних рівнянь та нерівностей або на пошук екстремумів.
У всіх подібних задачах використовується поняття цільової функції — вона має досягти певного значення або оптимізуватися (мінімізуватися чи максимізуватися). В електронній книзі формулу обчислення цільової функції записують у певну клітинку, яку також називають цільовою. Цільова функція залежить від параметрів (часто — від одного параметра), значення яких зберігаються в інших клітинках електронної таблиці.
Власне кажучи, задача полягає у підборі таких значень параметрів, за яких у цільовій клітинці буде отримано бажаний результат. У табличному процесорі Excel є спеціальні засоби, які автоматично підбирають потрібні значення у клітинках параметрів. Вони називаються Підбір параметра та Пошук розв'язків. Перший із них дозволяє отримати в цільовій клітинці певне значення, а другий — оптимізувати значення цільової функції.
Засіб Підбір параметра застосовують наступним чином:
1. В одну з клітинок електронної таблиці слід увести формулу цільової функції. Це буде цільова клітинка (на рис. 1 — клітинка В2).
рис.1
2. На вкладці Дані у групі Знаряддя даних натиснути кнопку Аналіз "якщо" та вибрати пункт Підбір параметра. Заповнити поля у вікні, що відкриється:
● поле Установити у клітинці має містити адресу цільової клітинки;
● у поле Значення слід ввести значення, якого має набути цільова функція;
● у поле Змінюючи значення клітинки слід увести адресу клітинки-параметра.
Наприклад, на рис. 1 параметр міститиметься у клітинці В1, і якщо в цільовій клітинці В2 потрібно отримати значення 0, вікно Підбір параметра слід заповнити так, як на рис. 2.
рис. 2.
3. На завершення потрібно клацнути кнопку ОК. У цільовій клітинці буде відображено значення, якого має набувати цільова функція, а в клітинці параметра — шукане значення параметра.
Наприклад, на рис. 3 показано, як у клітинці В1 знайдено значення параметра (число 5), за якого цільова функція у клітинці В2 набуває значення 0. Тобто фактично розв'язано рівняння х - 5 = 0.
рис. 3.
Примітка. Підбір параметра майже завжди дає наближені значення результату. Тому, якщо в клітинці параметра після його підбору виводиться число 4,99999, то, скоріш за все, справжнім розв'язком задачі є число 5. Використовуючи засіб Підбір параметра, клітинку параметра можна залишити порожньою, однак бажано попередньо визначити деяке початкове значення, адже від цього залежить швидкість отримання результату (особливо коли йдеться про складні цільові функції), а у деяких випадках і сам результат. Якщо цільова функція складна, може виникнути ситуація, коли не одне, а кілька значень параметра відповідають її шуканому значенню. Яке з них буде знайдено, залежить від початкового значення в клітинці параметра. У таких випадках, перш ніж підбирати параметр, доцільно побудувати графік цільової функції, щоб визначити початкове значення параметра наближено.
Практичне завдання
Увага! Під час роботи з комп'ютером дотримуйтеся вимог безпеки життєдіяльності та санітарно-гігієнічних норм.
Задача 1. Знайти довжину сторін прямокутника, периметр якого дорівнює 42 см, а площа 108 см2.
Розв'язання. Нехай одна сторона х, тоді друга 21-х, складемо рівняння x*(21-x)=108. Спростимо це рівняння і отримаємо x2-21x+108=0. Ми отримали квадратне рівняння, побудуємо графік функції на проміжку наприклад, [-2;10] з кроком 0,5. Виділимо діапазон А3:В28→Вставка→Діаграми→Точкова. Отримаємо розв'язки два корені: 9 і 12.
Розв'яжемо рівняння за допомогою засоба Підбір параметрів. Нам потрібно підібрати такий Х, щоб Y перетворився в 0. Створимо таблицю за зразком: Е1- Х, Е2 - Y, F1- порожня, F2=F1^2-21*F1+108. Виділимо діапазон F1:F2→Дані→Аналіз «якщо»→Підбір параметрів. У вікні, що появилося: встановимо в клітинці функції - F2, значення 0, змінюючи значення Х - $F$1. ОК. Отримаємо результат 9. Підберемо другий корінь - 12.
Задача 2. За 7 кг апельсинів і 4 кг лимонів заплатили 453 грн. Скільки коштує 1 кг апельсинів і скільки 1 кг лимонів, якщо 5 кг апельсинів дорожчі за 2 кг лимонів на 71 грн?
Розв'язання. Нехай 1 кг апельсинів коштує х грн., 1 кг лимонів y грн. складемо і розв’яжемо систему рівнянь:
Для розв’язку системи рівнянь використаємо метод Пошук рішень.
1.Перейдемо в нашій книзі на аркуш Система
2.В клітинці В7 введемо перше рівняння =7*В3+4*В4, В3 і В4 наші значення х і y. В клітинці В8 – друге рівняння =5*В3-2*В4 . Отримаємо 0, бо В3 і В4 - порожні.
3.Виділяємо В7→Дані→Аналіз→Розв'язувач (Пошук рішень)
4.Вибираємо цільову функцію, в нашому випадку 7х+4y, за допомогою вікна вибору і надаємо значення 453, вказуємо діапазон х і y, який шукаємо В3:В4. Далі вказуємо значення другого рівняння вибравши кнопку Додати В8=71. ОК. Отже, параметри задані→Знайти розв’язок→ОК.
5.Отримали корені рівняння: 35 та 52.
Задача 3 (стародавня). Купець придбав 138 аршин чорного та червоного сукна за 540 карбованців (крб). Скільки аршин кожного сукна придбав купець, якщо червоне коштувало 5 крб. за аршин, а чорне — 3 крб.?
Задача 4 (самостійно). Для вітання учасників марафону придбали 75 кг фруктів – яблук і апельсинів вартістю 700 грн. Скільки кг яблук і апельсинів придбали для привітань, якщо вартість яблук – 7 грн, апельсин 12 грн – за 1 кг. Побудувати графік для знаходження коренів та розв'язати задачу методом Підбір параметрів.
Завантажити файл-заготовку та розв'язати задачі.