Рішення систем лінійних рівнянь в excel

2. Операції з матрицями в Excel

1) МОПРЕД (матриця) - обчислення визначника матриці, 2) МОБР (матриця) - обчислення оберненої матриці, 3) МУМНОЖ (матріца1; матріца2) - твір матриць, 4) ТРАНСП (матриця) - транспонування матриці.

Перша з цих функцій як результат повертає число (визначник матриці), поетомувводітся як звичайна формула (ENTER).

Останні три повертають блок осередків, тому повинні вводитися як формули масиву (CTRL + SHIFT + ENTER).

Розглянемо задачурешенія СЛАР на наступному прикладі

8 x 1 2 x 2 8 x 3 24,

2 x 1 2 x 2 10 x 3 48,

2 x 1 4 x 2 8 x 3 18.

Матриця коефіцієнтів при невідомих A (3) має вигляд

Аналогічна формула (= МОПРЕД (A3: C5)) для обчислення визначника матриці A записана в комірку E8. Залишилося знайти рішення системи. Відповідні формули Excel запишемо в інтервал рішення B7: B9 (рис. 3), в якому і побачимо результат (рис. 4).

Зверніть увагу на те (рис. 3), що при обчисленні x i (i = 1, 2, 3)

аналізується значення визначника матриці системи A. обчислене в осередку E8. і, якщо воно дорівнює нулю. то в B7 поміщається текст «Рішення немає», а в осередку B8 і B9 - порожні рядки.

3. Рішення СЛАР з використанням інструменту Пошук рішення

Широкий клас виробничих завдань складають завдання оптимізації. Завдання оптимізації припускають пошук значень аргументів, що доставляють функції, яку називають цільовою. мінімальне або максимальне значення при наявності будь-яких додаткових обмежень. Excel має у своєму розпорядженні потужним засобом для вирішення оптимізаційних завдань.

Це інструмент-надбудова, який називається Пошук рішення (Solver)

(Доступний через меню Сервіс  Пошук рішення).

Завдання рішення СЛАР можна звести до оптимізаційної задачі.

Для чого одне з рівнянь (наприклад, перше) взяти в якості цільової функції, а що залишилися n -1 розглядати в якості обмежень.

Запишемо систему (1) у вигляді

a 11 x 1 a 12 x 2 a 1 n x n b 1 0,

Для вирішення цього завдання необхідно записати вирази (формули) для обчислення значень функцій, що стоять зліва в рівняннях системи (12). Відведемо для прикладу під ці формули інтервал C7: C9. У осередок C7 введемо формулу = A3 * $ B $ 7 + B3 * $ B $ 8 + C3 * $ B $ 9-D3 і скопіюємо її в решту C8 і C9. У них з'являться відповідно = A4 * $ B $ 7 + B4 * $ B $ 8 + C4 * $ B $ 9-D4 і = A5 * $ B $ 7 + B5 * $ B $ 8 + C5 * $ B $ 9-D5.

У вікні діалогу Пошук рішення (рис. 5) задати параметри пошуку (встановити цільову комірку C7 рівною нулю, рішення в змінюваних осередках B7: B9. Обмеження задані формулами в осередках C8 і С9). Після клацання по кнопці Виконати в

інтервалі B7: B9 отримаємо результат (рис. 6) - рішення СЛАР.

Для продовження скачування необхідно зібрати картинку: