Решаване на някои класове математически задачи в Excel
Архив за разработка (527 kb, WinRAR)
Предефинирани системи от линейни уравнения.
Линейната алгебра разглежда подробно решението на различни класове системи от линейни уравнения (по-нататък SLE): SLE, в които броят на уравненията съвпада с броя на неизвестните, в които броят на неизвестните надвишава броя на уравненията, хомогенни SLE.
Нека представим прост проблем по икономически теми, който води до концепцията за свръхдетерминирани SLE. В града има три пекарни, които произвеждат и продават хляб от първи и втори клас. Данните за производството на два вида хляб и общите приходи от продажбата му са дадени в таблицата.
Числените данни са дадени в произволни единици, например хиляди ролки, хиляди рубли. Въз основа на предоставените данни се изисква да се определи средната цена на хляба и от двата сорта. Ясно е, че нито един магазин не е продавал хляб на средна цена, но е очевидно, че средната цена съществува и посочените данни са достатъчни, за да се определи.
Нека въведем неизвестните: "x" - средната цена на хляба от първи клас, "y" - средната цена на хляба от втори клас. Въз основа на данните в таблицата системата от уравнения е очевидна:
| 5x + 2y = 52 (1) 2x + 3y = 35 2x + y = 20 |
Защо такива системи по правило не се разглеждат в учебниците по класическа математика? Отговорът на този въпрос е очевиден. Геометрично уравненията на съставената система са прави, като в общия случай три прави не се пресичат в една точка. По този начин предефинираният SLE е като цяло непоследователен, няма точка от равнината, която да принадлежи и на трите линии. Но смисълът на дадения икономически проблем казва товатрябва да има решение!
Какво да приемем като решение на свръхдетерминирания SLN? Правите нямат обща точка, но има точка (дали тя е единствената?), сумата от разстоянията от която до дадените прави е най-малка. Тази точка, нейните координати, се приемат като решение на предефинираната SLE. Вижте чертежа.
За по-голяма яснота сме дали най-простия проблем, същото разсъждение може да се приложи към по-голям брой уравнения и към по-голям брой неизвестни. Така например за три променливи уравненията могат да се интерпретират геометрично като равнини, 4 или повече от които в общия случай нямат обща точка. За линейни уравнения с голям брой неизвестни се използва понятието "хиперравнина".
За намиране на описаното решение се използва методът на най-малките квадрати. Същността му е, че минимизира сумата от разстоянията от точка до линии и сумата от квадратите на разстоянията (в този пример) от точка до линиите. Този метод се използва в много области на математиката и приложните изследвания. Нека намерим решението, без да даваме алгоритъма.
Решението на предефинираната SLE чрез метода на най-малките квадрати се оказа двойка числа: (7.6666; 6.5000)
Както е известно от аналитичната геометрия, разстоянието от точката (x0;y0) до правата ax + by + c = 0 се изчислява по формулата:
В клетки A1, B1 въведете 1, можете да въведете всякакви други числа. Излизаме в търсене на решение и инсталираме:
В Excel получихме решението (7.818; 6.454), сумата от разстоянията от точката до линиите е 0.935. Кое е "правилното" решение? Невъзможно е да не повярвате на класическия метод на най-малките квадрати! Но не е трудно да се оцени "качеството" на получените решения. Очевидно решението, получено в Excel, отразява по-точно реалността. Резултатите се различават със стотни и въпреки това се различават. Методът на най-малките квадрати работи с квадратни отклонения, докато Excel минимизира директно сумата от модулите на отклонение. Ние приемаме и двете решения за правилни, като предпочитаме по-точното за практическа употреба.
Оказва се, че когато решавахме тези задачи в "предкомпютърната ера" и казвахме, че методът на най-малките квадрати дава една точка, сборът от разстоянията, от които до линиите е минимален, сме сгрешили!
Проверете, като решите заменените SLE, като използвате метода на най-малките квадрати и предложения метод в Excel. Варианти на задачите.
Excel даде решението:
Минимално отклонение на пет равнини от точка в пространството: 0.948314 Вижте!