Вирішення деяких класів математичних завдань у програмі Excel
Архів розробки (527 кб, WinRAR)
Перевизначені системи лінійних рівнянь.
Лінійна алгебра докладно розглядає рішення різних класів систем лінійних рівнянь (далі СЛУ): СЛУ, у яких кількість рівнянь збігається з числом невідомих, у яких кількість невідомих перевищує кількість рівнянь, однорідні СЛП.
Наведемо просте завдання на економічну тематику, що призводить до поняття перевизначених СЛУ. У місті три хлібокомбінати, вони виробляють та реалізують хліб першого та другого сорту. Дані з виробництва хліба двох сортів та сумарній виручці від його реалізації наведемо у таблиці.
Цифрові дані наведено в умовних одиницях, наприклад, тисяч булок, тисяч рублів. З наведених даних потрібно визначити середню ціну хліба обох сортів. Зрозуміло, що за середньою ціною жоден магазин хліба не продавав, але очевидно, що середня ціна існує і наведених даних достатньо для її визначення.
Введемо невідомі: "х" - середня ціна хліба першого ґатунку, "у" - середня ціна хліба другого ґатунку. З даних таблиці очевидна система рівнянь:
| 5х + 2у = 52 (1) 2х + 3у = 35 2х + у = 20 |
Чому такі системи, зазвичай, не розглядаються у підручниках класичної математики? Відповідь на це питання очевидна. Геометрично рівняння складеної системи являють собою прямі, а три прямі в загальному випадку не перетинаються в одій точці. Таким чином, перевизначена СЛУ в загальному випадку несумісна, точки на площині, яка належала б усім трьом прямим, немає. Але сенс наведеного економічного завдання свідчить, щорішення має бути!
Що ж ухвалити за рішення перевизначеної СЛУ? Загальної точки прямі немає, але є точка (чи єдина?), сума відстаней від якої до даних прямих є величина найменша. Ось цю точку, її координати і приймають за рішення перевизначеної СЛУ. Дивіться рисунок.
Ми для наочності привели просте завдання, ті ж міркування можна застосувати і для більшої кількості рівнянь і для більшої кількості невідомих. Так, наприклад, для трьох змінних рівняння можна геометрично інтерпретувати як площини, 4 і більше з яких у загальному випадку немає загальної точки. Для лінійних рівнянь із великою кількістю невідомих користуються поняттям "гіперплощина".
Для знаходження описаного рішення використовується метод найменших квадратів. Його суть у тому, що він мінімізує суму відстаней від точки до прямих, а суму квадратів відстаней (у даному прикладі) від точки до прямих. Цей метод застосовується у багатьох галузях математики та прикладних дослідженнях. Не наводячи алгоритму знайдемо рішення.
Рішенням перевизначеної СЛУ методом найменших квадратів виявилася пара чисел: (7,6666; 6,5000)
Як відомо з аналітичної геометрії відстань від точки (x0; y0) до прямої ax + by + c = 0 обчислюється за формулою:
У комірки А1, В1 ввели 1, можна було запровадити будь-які інші числа. Виходимо в пошук рішення та встановлюємо:
У Excel ми отримали рішення (7,818; 6,454), суму відстаней від точки до прямих 0,935. Яке ж рішення вважати "правильним"? Не можна не вірити класичному методу найменших квадратів! Але неважко дати оцінку "якості" отриманих рішень. Очевидно, що рішення, отримане в Excel, точніше відображає реальну дійсність. Результати відрізняються на соті частки і, тим не менш, відрізняються. Метод найменших квадратів оперує з квадратами відхилень, Excel мінімізує безпосередньо суму модулів відхилень. Ми приймаємо обидва рішення як вірні, надаючи перевагу практичному використанню більш точному.
Виходить так, що вирішуючи ці завдання в "докомп'ютерну епоху" і кажучи, що метод найменших квадратів дає єдину точку, сума відстаней від якої до прямих мінімальна, ми не мали рації!
Перевірте, вирішивши перевизначені СЛУ за методом найменших квадратів та за запропонованою методикою в Excel. Варіанти завдань.
Excel дав рішення:
Мінімальне відхилення п'яти площин від місця: 0,948314 Перевірте!