Пошаговое руководство по инверсии матриц в Excel
Чтобы вычислить обратную матрицу в Excel, используйте встроенную функцию МИНВЕРС (англ. MINVERSE). Выделите диапазон ячеек того же размера, что и исходная матрица, введите формулу =МИНВЕРС(диапазон) и подтвердите её нажатием Ctrl+Shift+Enter (в старых версиях) или просто Enter (в Excel 365). Матрица должна быть квадратной, а её определитель не должен равняться нулю.
Что такое обратная матрица и условия её существования
Обратная матрица $A^{-1}$ существует только для квадратных матриц, у которых определитель ($\det$) не равен нулю. При умножении исходной матрицы $A$ на обратную $A^{-1}$ получается единичная матрица $I$, где на главной диагонали стоят единицы, а остальные элементы — нули.
В Excel эта операция критически важна для:
- Решения систем линейных уравнений.
- Эконометрического моделирования и регрессионного анализа.
- Инженерных расчетов и оптимизации ресурсов.
Перед инверсией всегда проверяйте определитель функцией МОПРЕД (MDETERM). Если результат близок к нулю (например, $10^{-12}$), матрица считается вырожденной, и точное решение получить невозможно.
Алгоритм вычисления функцией МИНВЕРС
Процесс зависит от версии Excel, так как в новых версиях появились динамические массивы.
Для Excel 365 и Excel 2021 (Динамические массивы)
В этих версиях результат «разливается» автоматически.
- Введите исходную матрицу, например, в диапазон
A1:B2. - Кликните в любую свободную ячейку (например,
D1). - Введите формулу:
=МИНВЕРС(A1:B2). - Нажмите Enter. Результат займет нужный диапазон автоматически.
Для Excel 2019, 2016 и старше (Классический метод)
Здесь требуется ручной захват диапазона и ввод формулы массива.
- Выделите пустой диапазон ячеек, строго равный по размеру исходной матрице (для матрицы 2x2 выделите 2 строки и 2 столбца).
- Не снимая выделения, введите формулу:
=МИНВЕРС(A1:B2). - Нажмите комбинацию Ctrl + Shift + Enter.
- Формула в строке формул возьмется в фигурные скобки:
{=МИНВЕРС(A1:B2)}.
Если выделен слишком маленький диапазон, Excel покажет только часть результата. Если слишком большой — возникнет ошибка #Н/Д (#N/A) в лишних ячейках. Размер областей ввода и вывода должен совпадать идеально.
Примеры расчета для разных размеров
Матрица 2x2
Исходные данные в A1:B2:
| A | B | |
|---|---|---|
| 1 | 4 | 3 |
| 2 | 3 | 2 |
Формула: =МИНВЕРС(A1:B2).
Результат:
| D | E | |
|---|---|---|
| 1 | 2 | -3 |
| 2 | -1.5 | 2 |
Проверка: Определитель $4 \cdot 2 - 3 \cdot 3 = 1$. Обратная матрица существует.
Матрица 3x3
Для матрицы размером 3x3 алгоритм идентичен, но важно выделить блок 3x3 для результата.
- Исходные данные:
A1:C3. - Выделите диапазон
E1:G3. - Введите
=МИНВЕРС(A1:C3)и нажмите Ctrl+Shift+Enter.
Если определитель равен нулю, вместо чисел вы увидите ошибку #ЧИСЛО! (#NUM!).
Как проверить правильность результата
Лучший способ убедиться в отсутствии ошибок — перемножить исходную и полученную обратную матрицы. Результатом должна стать единичная матрица.
- Используйте функцию МУМНОЖ (
MMULT). - Формула:
=МУМНОЖ(исходный_диапазон; обратный_диапазон). - На выходе вы должны получить матрицу, где по диагонали стоят
1, а остальные значения —0(допустимы минимальные погрешности округления, например1.00000001).
| Тип проверки | Ожидаемый результат |
|---|---|
| Определитель | Любое число, кроме 0 |
| Произведение $A \cdot A^{-1}$ | Единичная матрица |
| Ошибка #ЗНАЧ! | Неверно выделен диапазон или тип данных |
| Ошибка #ЧИСЛО! | Матрица вырожденная (определитель = 0) |
Частые ошибки при работе с матрицами
- #ЧИСЛО! (#NUM!): Попытка инвертировать вырожденную матрицу. Проверьте данные на линейную зависимость строк или столбцов.
- #ЗНАЧ! (#VALUE!): В диапазоне есть текст, пустые ячейки или вы забыли нажать
Ctrl+Shift+Enterв старой версии Excel. - Частичный вывод: В версиях без динамических массивов формула была введена только в одну ячейку вместо всего блока. Исправляется удалением формулы и повторным вводом с полным выделением диапазона.
- Несовпадение размеров: Исходная матрица не квадратная (например, 2x3). Функция
МИНВЕРСработает только с квадратными матрицами ($N \times N$).
FAQ
Можно ли найти обратную матрицу для неквадратной таблицы? Нет, математически обратная матрица существует только для квадратных матриц. Для прямоугольных массивов используют псевдообратные матрицы, которые в стандартном Excel требуют сложных вычислений или надстроек.
Почему в результате дроби, а не целые числа? Элементы обратной матрицы часто являются дробными числами, даже если исходная матрица состояла из целых чисел. Это нормальная математическая особенность. Увеличьте количество отображаемых знаков после запятой в формате ячеек для точности.
Работает ли функция на мобильных устройствах?
Да, приложение Excel для iOS и Android поддерживает функцию MINVERSE (на русском МИНВЕРС), но ввод формул массива через Ctrl+Shift+Enter на сенсорных экранах может быть неудобен. Рекомендуется использовать десктопную версию для сложных расчетов.