Приклади використання функції МОБР в матрицях Excel

Функція МОБР – це обчислювальне визначення матриці. Вона повертає зворотну матрицю для матриці, що зберігається у масиві. Зворотні матриці, як і визначники, зазвичай використовуються для вирішення систем рівнянь із кількома невідомими. Деякі квадратні матриці не можуть бути обернені: у таких випадках функція МОБР повертає значення помилки #ЧИСЛО!. Визначник такої матриці дорівнює 0.

Опис використання функції МОБР в Excel

Як використовувати функцію МОБР в Excel, розглянемо нижче на прикладах. Але спочатку ознайомимося як влаштована ця функція.

Аргумент функції МОБР – це масив. Він може бути заданий як діапазон осередків, наприклад, A1:C3 як масив констант, наприклад, або як ім'я діапазону або масиву. Якщо хоча б одна з осередків масиву порожня або містить текст, функція повертає значення помилки #ЗНАЧ!

Масив повинен мати однакову кількість рядків та стовпців. Якщо вони не рівні, то функція МОБР також повертає значення помилки #ЗНАЧ!

Формули, які повертають масиви, мають бути введені як формули масиву.

Для виведення зворотного масиву необхідно після вибору діапазону цієї функції натиснути комбінацію клавіш Ctrl+Shift+Enter, а чи не просто Enter.

Функція МОБР здійснює обчислення з точністю до 16 значущих цифр, що може призвести до незначних помилок округлення. Розглянемо застосування цієї функції на конкретних прикладах.

Пошук зворотної матриці Excel за допомогою функції МОБР

Приклад 1. Використовуючи Excel, знайти зворотну матрицю для матриці, наведеної в таблиці 1.

Для вирішення цієї задачі відкриває пакет Excel, у довільному осередку вводимо вихідні дані, далі вибираємо функцію МОБР.Як масив вибираємо діапазон з введеними даними і контролюємо отриманий результат. У Excel загальний вигляд функції виглядає так:

Малюнок 1 – Результат розрахунку.

Як знайти валовий показник по матриці взаємозв'язків?

Приклад 2. Зв'язок між трьома галузями представлений матрицею прямих витрат А. Попит (кінцевий продукт) заданий вектором X. Знайти валовий випуск продукції галузей Х. Описати формули, що використовуються, подати роздруківку зі значеннями і з формулами.

Вихідні дані наведено на малюнку 2:

Рисунок 2 – Початкові дані.

Це завдання пов'язані з визначенням обсягу виробництва кожної з N галузей, щоб задовольнити всі потреби у продукції даної галузі. При цьому кожна галузь виступає і як виробник деякої продукції і як споживач своєї та виробленої іншими галузями продукції. Завдання міжгалузевого балансу – відшукання такого вектора валового випуску X, який за відомої матриці прямих витрат забезпечує заданий вектор кінцевого продукту Y.

Матричне вирішення цієї задачі:

де Е – поодинока матриця.

Для вирішення задачі в прикладі використовуємо наступні 4 функції для роботи з матрицями Excel:

1. МОБР – знаходження зворотної матриці.
2. МУМНІЖ - множення матриць.
3. МОПРЕД – знаходження визначника матриці.
4. МЕДІН – знаходження одиничної матриці.

Результати наведено на малюнку 3:

Рисунок 3 – Результат обчислень.

Функція МОБР повертає помилку #ЧИСЛО!

Приклад 3. Знайти обернену матрицю для матриці, наведеної в таблиці 2.

Результат рішення наведено малюнку 4. Видно, що визначник даної матриці дорівнює 0, тому функція МОБР виводить у результаті значення #ЧИСЛО!.

Рисунок 4 – Остаточний результат.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади

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

У програмі Excel із матрицею можна працювати як із діапазоном. Тобто сукупністю суміжних осередків, які займають прямокутну область.

Адреса матриці – лівий верхній і правий нижній осередок діапазону, вказані черга двокрапки.

Формули масиву

Побудова матриці засобами Excel здебільшого вимагає використання формули масиву. Основна їхня відмінність – результатом стає не одне значення, а масив даних (діапазон чисел).

Порядок застосування формули масиву:

1. Виділити діапазон, де має з'явитись результат дії формули.
2. Ввести формулу (як і слід, зі знака «=»).
3. Натиснути клавіші Ctrl + Shift + Введення.

У рядку формул з'явиться формула масиву у фігурних дужках.

Щоб змінити або видалити формулу масиву, потрібно виділити весь діапазон та виконати відповідні дії. Для введення змін застосовується та сама комбінація (Ctrl + Shift + Enter). Частину масиву змінити неможливо.

Рішення матриць в Excel

З матрицями в Excel виконуються такі операції, як: транспонування, додавання, множення на число/матрицю; знаходження зворотної матриці та її визначника.

Транспонування

Транспонувати матрицю - поміняти рядки та стовпці місцями.

Спочатку відзначимо порожній діапазон, куди транспонуватимемо матрицю. У вихідній матриці 4 рядки – у діапазоні для транспонування має бути 4 стовпці. 5 колонок – це п'ять рядків у порожній області.

• 1 спосіб. Виділити вихідну матрицю. Натиснути "копіювати". Виділити порожній діапазон. "Розгорнути" клавішу "Вставити". Відкрийте меню «Спеціальна вставка».Відзначити операцію "Транспонувати". Закрити діалогове вікно, натиснувши кнопку ОК.
• 2 спосіб. Виділити комірку у лівому верхньому кутку порожнього діапазону. Викликати «Майстер функцій». Функція ТРАНСП. Аргумент – діапазон із вихідною матрицею.

Натискаємо ОК. Поки що функція видає помилку. Виділяємо весь спектр, куди необхідно транспонувати матрицю. Натискаємо кнопку F2 (переходимо в режим редагування формули). Натискаємо клавіші Ctrl + Shift + Enter.

Перевага другого способу: при внесенні змін до вихідної матриці автоматично змінюється транспонована матриця.

Додавання

Складати можна матриці з однаковою кількістю елементів. Число рядків і стовпців першого діапазону має дорівнювати числу рядків і стовпців другого діапазону.

У першому осередку результуючої матриці потрібно запровадити формулу виду: = перший елемент першої матриці + перший елемент другий: (=B2+H2). Натиснути Enter та розтягнути формулу на весь діапазон.

Розмноження матриць в Excel

Щоб помножити матрицю на число, потрібно кожен елемент помножити на це число. Формула в Excel: =A1*$E$3 (посилання на комірку з числом має бути абсолютною).

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

У результуючій матриці кількість рядків дорівнює числу рядків першої матриці, а кількість колонок - числу стовпців другої.

Для зручності виділяємо діапазон, куди будуть розміщені результати множення. Робимо активним перший осередок результуючого поля. Вводимо формулу: =МУМНОЖ(A9:C13;E9:H11). Вводимо як формулу масиву.

Зворотня матриця в Excel

Її має сенс знаходити, якщо ми маємо справу з квадратною матрицею (кількість рядків та стовпців однакова).

Розмір зворотної матриці відповідає розміру вихідної.

Виділяємо першу комірку поки що порожнього діапазону для зворотної матриці.

Знаходження визначника матриці

Це одне єдине число, яке знаходиться для квадратної матриці.

Ставимо курсор у будь-якому осередку відкритого аркуша.

Таким чином, ми вчинили дії з матрицями за допомогою вбудованих можливостей Excel.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади