Рішення системи рівнянь в excel, металознавство
На цьому тижні в понеділок у мене була цікава задача: розрахувати вартість сотої (тисячної) частки кожного легуючого елемента на тонну сталі іншого підприємства. Вихідні дані були наступні: кілька хімічних складів, тобто кілька плавок, і вартість тонни кожної плавки. Все, начебто, здорово: знаючи вартість кожної сотої або тисячної частки відсотка, можна в майбутньому, розробляючи концепцію легування нової стали або ж граючись з хім. складом існуючої стали, відразу ж оцінювати економічний ефект від зменшення легування або заміни одного елемента іншим у порівнянні з іншими виробниками.
Природно, матрицю скласти можна і рішення знайти теж можна, ось тільки вийде повна брехня. Наприклад, може вийти так, що вартість легування ніобієм буде негативна, а вартість легування вуглецем обчислюватися мільйонами. Коротше, затія невдала: адже крім набору хімічних елементів є ще купа факторів, що впливають на вартість кінцевого продукту. Проте знайшлися два позитивних моменти:
- я згадав, як вирішувати системи лінійних рівнянь, і що примітно, зрозумів, як вирішувати системи системи рівнянь в Exce l, тому в майбутньому це вміння стане в нагоді, коли, наприклад, доведеться розрахувати компоненти тензора напружень або ще чого-небудь такого.
- з'явилася тема для статті.
Отже, для вирішення системи рівнянь в Excel існує кілька способів:
- метод оберненої матриці;
- метод Гаусса;
- метод Зейделя.
style = "display: inline-block; width: 320px; height: 100px"
data-ad-client = "ca-pub-9341405937949877"
data-ad-slot = "7535111348">
Метод Гаусса полягає в послідовному виключенні невідомих: в одному рівнянні залишається одна невідома, в другому - дві, в третьому - три, і т.п. А потім починаємо обчислювати невідомі, починаючи з рівняння, де вона одна. Потім підставляємо знайдене значення першої невідомої в рівняння з двома невідомими і обчислюємо другу і т.п. Більш детально дізнатися про метод можна знову ж в [1, стор. 272-279].
На ньому ми концентруватися не будемо. Ми ж хочемо отримати результат швидко. а не повправлятися в математиці.
По кроках при бажанні його можна виконати, озброївшись папером і ручкою чисто в академічних цілях, коли потрібно просто продемонструвати собі, що ви це знаєте, вмієте і можете. Ось, наприклад, один з викладачів математики в моєму університеті інтегральчікі обчислював заради втіхи, поки в метро додому їхав (я, до речі, пишу цю статтю теж в метро, поки їду додому). Нам цікавий метод оберненої матриці, тому як Excel має набір функцій, який дозволяє застосовувати цей метод для вирішення систем рівнянь. Як сказав один з найрозумніших людей минулого століття: «Теорія повинна крокувати в ногу з практикою», - так що покрокову інструкцію рішення рівнянь буду приправляти теорією по ходу справи. У нас є система n рівнянь з n невідомими:
Нехай у нас є приклад:
Чи можемо нашу систему рівнянь записати в матричному вигляді. Складемо матрицю коефіцієнтів (A), стовпець невідомих (X) і стовпець вільних членів (B):
Наше рівняння з урахуванням введених позначень може бути записано в стислому вигляді: AX = B. Якщо визначник матриці коефіцієнтів не дорівнює нулю, то наша система рівнянь має єдине рішення [1, стор. 269] (тобто набір значень x1 ,, x2, ... xn), що дорівнює
де A -1 - обернена матриця.
А тепер до практики. Запишемо наше рівняння у вигляді таблиць A, X, B:
Тепер потрібно переконатися, що визначник матриці A НЕ дорівнює 0. Для обчислення визначника в Excel є функція «МОПРЕД».
В одній з комірок нижче обчислюємо визначник нашої матриці:
Він дорівнює «-6,3803», а, значить, не нулю. Йдемо далі.
Виділяємо таку ж за розміром область осередків, як і область таблиці з коефіцієнтами (в нашому випадку це 3 × 3), натискаємо «=» і вводимо функцію «МОБР» і діапазон комірок матриці коефіцієнтів. Натискаємо «Ctr» + «Shift» + «Enter» на клавіатурі - функція «МОБР» вирахує зворотну матрицю.
Тепер у нас є зворотна матриця і стовпець коефіцієнтів, а значить, ми можемо знайти рішення рівняння, помноживши зворотну матрицю на стовпець вільних членів: X = A-1B. Для множення матриць в Excel є функція «МУМНОЖ» Виділяють порожній стовпець, в якому кількість рядків дорівнює кількості невідомих нашого рівняння, тобто трьом. Після цього натискаємо «=» і вводимо функцію «МУМНОЖ», задаємо діапазони осередків - спочатку зворотної матриці (A-1), потім - стовпці вільних членів (B).
Натискаємо «Ctr» + «Shift» + «Enter» на клавіатурі. В результаті в стовпці будуть виведені значення невідомих:
- записати рівняння у вигляді двох таблиць: матриці коефіцієнтів і вектора-стовпця вільних членів;
- переконатися, що визначник матриці коефіцієнтів не дорівнює нулю, скориставшись функцією «МОПРЕД»;
- знайти матрицю, зворотну матриці коефіцієнтів, за допомогою функції «МОБР»;
- помножити зворотну матрицю на вектор-стовпець вільних членів за допомогою функції «МУМНОЖ».
[1] Демидович Б.П. Марон І.А. Основи обчислювальної математики. М: «Наука», 1966, 664 с.