6.2 Індивідуальні завдання

Добавити ще декілька Листів з даними для свого варіанту та виконати для них консолідацію. Знайти підсумки для нарахованих сум, встановити зв’язки з даними.

Лабораторна робота № 7

Тема роботи: Робота з матрицями в Excel

Мета роботи: навчитися знаходити визначник матриці, оберне­ну матрицю, перемножувати матриці, розв’язувати системи рівнянь за допомогою матриць.

7.1 Функції для роботи з масивами

В Excel існують спеціальні функції для роботи з масивами. Еле­менти масиву можна задати як масив чисел, в якому елементи відокре­млюються крапкою з комою, а рядки – двокрапкою. Але зручніше за­давати масив як інтервал клітинок, наприклад A1 : C3. Клітинки, які утворюють масив, повинні містити числа і не можуть бути вільними.

При використанні функцій роботи з масивами треба спочатку визначити розмір масиву, який буде отримано у відповіді. Далі треба виділити діапазон клітинок потрібного розміру і записати формулу масиву в фігурних дужках { } одразу для всіх клітинок. Щоб ввести таку формулу, треба натиснути клавіші CTRL+SHIFT+ENTER.

Мопред (масив)

Функція знаходить визначник матриці. Матриця – це двовимір­ний масив. Матриця повинна бути квадратною і містити тільки числа. Формула записується до однієї клітинки, тому не береться в фігурні дужки. Наприклад, = МОПРЕД(А3 : С5) або = МОПРЕД ( {1 ; 2 ; 3 : 4 ; -5 ; 6 : 7 ; 8 ; 9} )

Мумнож (массив1 ; масив2)

Функція знаходить добуток двох матриць. Обов’язкова вимога – кількість стовпчиків в масиві 1 повинна співпадати з кількістю рядків масиву 2. Результатом є матриця, яка має стільки ж рядків, як масив 1, і стільки ж стовпчиків, як масив 2.

Наприклад, перемножимо дві матриці, розміром 2 х 3 і 3 х 2, зображені на рис. 7.1. Елементи рядків першої матриці умножаються на елементи стовпчиків другої матриці. Матриця – результат містить два рядки і два стовпчики. Виділимо мишкою діапазон Н2 : І3, в рядку формул запишемо формулу =МУМНОЖ (А2 : С3 ; Е2 : F4) без фігурних дужок, натиснемо клавіші CTRL+SHIFT+ENTER, і в усі клітинки діапазону Н2 : І3 запишеться ця формула в фігурних дужках: {=МУМНОЖ (А2 : С3 ; Е2 : F4) }. Результат – див. рис. 7.1.

Рисунок 7.1 – Знаходження добутку матриць

Мобр (масив)

Функція знаходить обернену матрицю для тієї числової матриці, що зберігається в масиві. Матриця повинна мати рівну кількість ряд­ків та стовпчиків. Визначник матриці не може дорівнювати 0. За допомогою оберненої матриці розв’язують системи лінійних рівнянь.

Приклад. Розв’язати систему рівнянь:

, де

Запишемо на Лист Excel (див. рис. 7.2) матриці коефіцієнтів А і В та знайдемо визначник матриці А за формулою: =МОПРЕД(B2 : D4) Визначник дорівнює 5, тому існує єдине рішення, яке можна знайти за допомогою оберненої матриці за формулою:

Рисунок 7.2 – Розв’язування системи лінійних рівнянь

Щоб знайти обернену матрицю, виділимо діапазон B8 : D10, в рядку формул запишемо формулу =МОБР(B2 : D4) і натиснемо клавіші CTRL+SHIFT+ENTER. До клітинок B8 : D10 буде введена формула масиву {=МОБР(B2 : D4) } і обчислені елементи оберненої матриці. Для запису розв’язку виділимо діапазон клітинок G8 : G10 і запишемо туди формулу {=МУМНОЖ(B8:D10;G2:G4) }.