Как найти определитель матрицы excel

Способ Крамера

Метод Крамера используют для решения квадратной системы уравнений, представленной в линейном виде, где определитель основной матрицы не равен нулю. Считается, что система обладает единственным решением. Например, задана система линейных уравнений:

Её необходимо заменить равноценным матричным уравнением.

Второй столбец вычисляют, а первый уже задан. Есть предположение, что определитель матрицы отличен от нуля. Из этого можно сделать выводы, что существует обратная матрица. Перемножив эквивалентное матричное уравнение на обратного формата матрицу, получим выражение:

В итоге получают выражения:

Из представленных уравнений выделяют формулы Крамера:

Метод Крамера не представляет сложности. Он может быть описан следующим алгоритмом:

1. Высчитывают определитель дельта базовой матрицы.
2. В матричной таблице А замещают первый столбец на вектор свободных элементов b.
3. Выполняют расчёт определителя дельта1 выявленной матрицы А1.
4. Определяют переменную Х1 = дельта1/дельта.
5. Повторяют шаги со 2 по 4 пункт в матрице А для столбов 2,3…n.

Проверить решение матрицы методом Крамера онлайн позволяет калькулятор автоматического расчёта. Для получения быстрого ответа в представленные поля подставляют переменные числа и их количество. Дополнительно может потребоваться указание вычислительного метода разложения по строке или столбу. Другой вариант заключается в приведении к треугольному виду.

Перемножение одной матрицы на другую в Microsoft Excel

Одной из частых операций, которую выполняют при работе с матрицами, является перемножение одной из них на другую. Программа Excel является мощным табличным процессором, который предназначен, в том числе и для работы над матрицами. Поэтому у него имеются инструменты, которые позволяют перемножить их между собой. Давайте узнаем, как это можно выполнить различными способами.

Процедура перемножения матриц

Сразу нужно сказать, что перемножить между собой можно далеко не все матрицы, а только те, которые соответствуют определенному условию: число столбцов одной матрицы должно быть равным числу строк другой и наоборот. Кроме того, исключается наличие в составе матриц пустых элементов. В этом случае тоже выполнить требуемую операцию не получится.

Способов перемножить матрицы в Экселе все-таки не так уж и много — всего два. И оба они связаны с применением встроенных функций Excel. Разберем в деталях каждый из данных вариантов.

Способ 1: функция МУМНОЖ

Наиболее простым и популярным вариантом среди пользователей является применение функции МУМНОЖ. Оператор МУМНОЖ относится к математической группе функций. Как раз его непосредственной задачей и является нахождение произведения двух матричных массивов. Синтаксис МУМНОЖ имеет такой вид:

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

Теперь давайте посмотрим, как используется функция МУМНОЖ на конкретном примере. Имеется две матрицы, число строк одной из которых, соответствует количеству столбцов в другой и наоборот. Нам нужно перемножить два этих элемента.

1. Выделяем диапазон, где будет отображаться результат умножения, начиная с его верхней левой ячейки. Размер данного диапазона должен соответствовать числу строк у первой матрицы и числу столбцов у второй. Клацаем по пиктограмме «Вставить функцию».

Способ 2: использование составной формулы

Кроме того, существует ещё один способ умножения двух матриц. Он более сложный, чем предыдущий, но тоже заслуживает упоминания, как альтернативный вариант. Данный способ предполагает использование составной формулы массива, которая будет состоять из функции СУММПРОИЗВ и вложенного в неё в качестве аргумента оператора ТРАНСП.

1. На этот раз выделяем на листе только левый верхний элемент массива пустых ячеек, который рассчитываем использовать для вывода результата. Щелкаем по значку «Вставить функцию».

В качестве аргументов из группы «Массив» используется ссылка на конкретный диапазон, который нужно перемножить. Всего может быть использовано от двух до 255 таких аргументов. Но в нашем случае, так как мы имеем дело с двумя матрицами, нам понадобится как раз два аргумента.

То есть, единственным аргументом данного оператора является ссылка на тот массив, который следует «перевернуть». Вернее, в нашем случае даже не на весь массив, а только на его первый столбец.

Как видим, несмотря на то, что был получен равнозначный результат, использовать функцию для умножения матриц МУМНОЖ значительно проще, чем применять для этих же целей составную формулу из операторов СУММПРОИЗВ и ТРАНСП. Но все-таки данный альтернативный вариант тоже нельзя оставить без внимания при изучении всех возможностей перемножения матриц в Microsoft Excel.

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12345 инструкций. Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

1.6. Функции

Функции – это стандартные формулы, проводящие вычисления по заданным
величинам, называемым аргументами. Некоторые примеры функций показаны на
Рис. 8.

Рис.8
Простейшие функции

Функция состоит из имени, за которым следует круглая
скобка, затем идет список аргументов, разделенных запятой и, наконец,
закрывающая скобка.

Например,
функция, показанная на Рис. 9 вычисляет
значение кумулятивного (cumulative=TRUE),
стандартного (mean=0,
standard_dev =1)
для величины, находящейся в ячейке
A1 .

Рис.9
Ввод функции через Formula Bar

Задать функцию можно по-разному. Проще всего ввести ее в окно
Formula Bar 
(см. ). Только предварительно нужно открыть
это окно через меню View-Formula Bar. Такой
способ удобен, когда вы хорошо помните синтаксис функции. Формулу можно
задать быстро, т.к. аргументы вводятся простым кликаньем по ячейкам, в
которых находятся аргументы.

Другой способ спасает тогда, когда мы плохо помним вид
функции, которая нам нужна. Тогда удобно воспользоваться кнопкой
Insert Function
(). После этого появится диалоговое окно (Рис. 10), из которого
можно выбрать нужную функцию .

Рис.1
Ввод функции через Insert Function

Как только функция выбрана, появляется  второе окно, специфическое для выбранной функции,
в котором устанавливаются ссылки на аргументы функции

Рис.11
Задание значений аргументов

Подробнее изучить эту тему можно
здесь.

Метод Гаусса

Методом Гаусса называют способ преобразования системы уравнений линейного вида к упрощённой форме для дальнейшего облегчённого решения. Операции упрощения уравнений выполняют с помощью эквивалентных преобразований. К таким относят:

• действия, когда в системе переставляются местами два уравнения;
• произведение одного из уравнений в системе на действительное ненулевое число;
• сложение первого уравнения со вторым, при этом последнее умножено на произвольное число.

Чтобы понять механизм решения, следует рассмотреть линейную систему уравнений.

Следует переписать эту систему в матричный вид:

В этом примере rang (A) = p. Способ эквивалентных преобразований не изменяет ранг таблицы коэффициентов.

Метод Гаусса предназначен для приведения матричной таблицы коэффициентов А к ступенчатому или диагональному виду. Расширенная система выглядит так:

Допустим, а11 не равен 0. В противном случае, если это не так, то меняют эту строку с другой, где в первом столбце находится элемент, отличный от нуля. Когда подобные строчки отсутствуют, переходят к другому столбцу. Все нижние элементы столбца после а11 обнуляют. Для этих целей выполняют операции сложения строк 2,3…m с первой строчкой, умноженной на а21/а11, -а31/а11….- аm1/a11. В результате система примет вид:

На втором шаге повторяют все действия с элементами столбца 2, которые расположены ниже а22. Если показатель равен нулю, строку также меняют местами со строчкой, лежащей ниже с ненулевым элементом во втором столбце. Затем обнулению подлежат все показатели ниже а22. Для этого складывают строки 2,3 ..m, как описано выше. Выполняя процедуру со всеми элементами, приходят к матричной таблице ступенчатого или диагонального вида. Полученная расширенная таблица будет выглядеть:

Обращают внимание на последние строки

В этом случае система уравнений имеет решение, но когда хотя бы одно из этих чисел отличается от нуля, она несовместима. Таким образом, система совместима, если ранг таблицы А равен расширенному рангу В (А|b).

Если rang А=rang (A|b), то существует множество решений, где n-p — многообразие. Из этого следует n-p неизвестных Хр+1,…Xn выбираются произвольно. Неизвестные X1, X2,…Xp вычисляют следующим образом: из последнего уравнения выражают Хр через остальные переменные, вставляя в предыдущие выражения. Затем из предпоследнего уравнения получают Хр-1 через прочие переменные и подставляют их в предыдущие выражения. Процедуру повторяют.

Найти быстро ответ и проверить себя позволяет онлайн-калькулятор. Решение матрицы методом Гаусса с помощью такого расчёта показывает подробные этапы операций. Для нахождения достаточно указать количество переменных и уравнений, отметить в полях значения чисел и нажать кнопку «Вычислить».

Принцип работы функции МОПРЕД в Excel

Функция МОПРЕД имеет следующую синтаксическую запись:

Единственным аргументом рассматриваемой функции является массив, который обязателен для заполнения. Он может быть указан в виде статического массива или ссылки на диапазон ячеек.

1. Диапазон ячеек или статический массив должен иметь равное количество строк и столбцов, иначе результатом работы функции МОПРЕД будет код ошибки #ЗНАЧ!.
2. Если диапазон ячеек или массив, переданные в качестве аргумента рассматриваемой функции, содержат текстовые данные или пустые значения, в результате будет возвращен код ошибки #ЗНАЧ!.
3. Функция МОПРЕД значительно упрощает процесс расчета детерминанта матрицы. Пользователь Excel может выполнить расчеты самостоятельно. Например, для прямоугольной матрицы, значения которой находятся в диапазоне A1:C3 рассчитать детерминант можно следующим способом: A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1).
4. Точность расчетов функции МОПРЕД составляет примерно 1E-16, то есть до 16 знаков после запятой. Для более точных расчетов (что требуется крайне редко) используют другие методы определения детерминанта матрицы.
5. Значение детерминанта используют для поиска решений системы линейных уравнений.

Замечания

Массив может быть задан как интервал ячеек, например A1:C3, как массив констант, например , как имя для интервала или массива.

Функция МОПРЕД возвращает значение ошибки #ЗНАЧ! в случаях, указанных ниже.

Если какая-либо ячейка в массиве пуста или содержит текст.

Если количество строк в массиве не равно количеству столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:

МОПРЕД(A1:C3)равно A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.

Функция МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к незначительным ошибкам. Например, определитель сингулярной матрицы отличается от нуля на 1E-16.

1.7. Некоторые важные функции

Excel предоставляет широкий выбор стандартных
(встроенных) функций. Мы не можем рассмотреть их все, поэтому
остановимся только на тех, которые представляются нам самыми важными.

SUM/ СУММ

Суммирует все числа в
списке аргументов или в области.

Синтаксис:

SUM(number1
) 

Пример

Рис.12
Функция SUM

SUMSQ / СУММКВ

Возвращает сумму квадратов аргументов.

Синтаксис:

SUMSQ(number1
) 

Пример

Рис.13 Функция SUMSQ

SUMPRODUCT / СУММПРОИЗВ

Перемножает соответствующие элементы заданных массивов и
возвращает сумму произведений

Синтаксис:

SUMPRODUCT (array1,
array2, …)  

Пример

Рис.14 Функция SUMPRODUCT

AVERAGE / СРЗНАЧ

Возвращает
своих аргументов

Синтаксис:

AVERAGE(number1
) 

Пример

Рис.15 Функция AVERAGE

VAR / ДИСП 

Оценивает по выборке .

Синтаксис:

VAR(number1
,number2,

…) 

Пример
 

Рис.16 Функция VAR

STDEV / СТАНДОТКЛОН

Оценивает по выборке
.

Синтаксис:

STDEV(number1
,number2,

…) 

Пример

Рис.17 Функция
STDEV

CORREL / КОРРЕЛ

Возвращает
между интервалами
ячеек array1 и array2

Синтаксис:

CORREL(array1,
array2)

Пример

Рис.17 Функция
CORREL

Функции можно объединять в
составные формулы, пример которой показан на Рис. 19.

Рис.19 Пример составной формулы

1.3. Адресация

A1C5Name BoxA1F=адрес=A1

Например, первая ячейка имеет абсолютный адрес –  $A$1, относительный адрес –
A1, и два
смешанных адреса – $A1 и
A$1. Различие в способе адресации проявляется,
прежде всего, тогда, когда формула копируется и переносится в другое
место. Поясним это на простом примере.

Рис. 5 Абсолютная и относительная адресация

На верхней панели показан фрагмент листа с
данными, выделенными желтым цветом. В зеленых областях (столбец
F и строка 6)
приведены различные варианты адресации одной и той же ячейки –
A1 (выделена оранжевым). Тип адресации
указан рядом с соответствующей ячейкой. Скопируем каждую из зеленых
областей (по очереди) и вставим рядом – в соседних столбцах:
G и H, и в
соседних строках: 7 и
8 (средняя панель ). Видно, что
результат зависит от типа адресации. Для абсолютной адресации ссылка на
первую ячейку сохранилась. Для относительной – ссылка сдвигается вправо
или вниз, сохраняя относительное положение двух ячеек: той, где стоит
ссылка, и той, на которую ссылаются. Для смешанной адресации результат
зависит от того, куда переносится копия, и от того, какая часть адреса
фиксируется значком доллара $. На правой
панели показаны соответствующие формулы, получающиеся после копирования.
Заметим, что ссылки на ячейки могут изменяться в зависимости от способа
адресации, но при перемещении ячейки с формулой содержащиеся в формуле
ссылки не изменяются.

Для адресации ячейки, которая находится на другом листе той же книги,
надо указывать еще и имя листа, например: Data!B2.
Восклицательный знак (!) отделяет имя листа
от адреса ячейки. Если имя листа содержит пробел, тогда имя надо
заключить в одинарные кавычки, например ‘Raw
Spectra’!C6. При адресации к другой книге, ее имя указывается
впереди, в квадратных скобках, например;
Results!P24

Подробнее о способах адресации можно прочитать

здесь.

Как сделать проверку обратной матрицы в excel?

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() или англ. MINVERSE .

Понятие обратной матрицы определено только для квадратных матриц, определитель которых отличен от нуля.

СОВЕТ: О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в MS EXCEL

Матрица А -1 называется обратной для исходной матрицы А порядка n, если справедливы равенства А -1 *А=Е и А*А -1 =Е, где Е единичная матрица порядка n.

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() .

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А8:В9, то для получения транспонированной матрицы нужно (см. файл примера ):

• выделить диапазон 2 х 2, который не пересекается с исходным диапазономА8:В9, например, Е8:F9
• в Cтроке формул ввести формулу = МОБР (A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER, т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2)

Если матрица большей размерности, то перед вводом формулы нужно выделить соответственно больший диапазон ячеек.

Массив может быть задан не только как интервал ячеек, например A8:B9, но и как массив констант, например =МОБР( ) . Запись с использованием массива констант позволяет не указывать элементы в отдельных ячейках, а разместить их в ячейке вместе с функцией. Массив в этом случае указывается по строкам: например, сначала первая строка 5;4, затем через двоеточие записывается следующая строка 3;2. Элементы отделяются точкой с запятой.

Ссылка на массив также может быть указана как ссылка на именованный диапазон.

Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР() возвращает значение ошибки #ЧИСЛО!. Матрицы не могут быть обращены, у которых определитель равен 0.

Если функция МОБР() вернула значение ошибки #ЗНАЧ!, то либо число строк в массиве не равно числу столбцов, либо какая-либо из ячеек в массиве пуста или содержит текст. Т.е. функция МОБР() пустую ячейку воспринимает не как содержащую 0 (как например, это делает СУММ() ), а как ошибочное значение.

Уровень 3

Задание 6. Самостоятельно выполнить с помощью Excel умножение матриц А и В. Даны А и В. В результате вычислений должна получиться матрица C (рис.14)

Рисунок 14.

Задание 7. Даны матрицы А, В, С и число a=2. Найти

Подсказка: Все вычисления выполнять на одном листе. Сначала вычислить, затем умножить матрицы , далее умножить матрицу С на число a, затем сложить матрицы и aС.Тест: результат Задание 8. Даны матрицы А, В, С и число a=2. Найти

Тест: результат

Вопросы на повторение:

Какая функция в Excel используется для транспонирования матрицы? Какая функция в Excel используется для умножения матриц?

Транспонирование данных из строк в столбцы и наоборот

Если у вас есть лист с данными из столбцов, которые нужно повернуть, чтобы изменить порядок строк, используйте функцию транспонировать . С его помощью можно быстро переключать данные из столбцов в строки и наоборот.

Например, если данные выглядят следующим образом: области продаж в заголовках столбцов и кварталов в левой части экрана, выполните указанные ниже действия.

Функция транспонировать переупорядочивает таблицу таким образом, что кварталы отображаются в заголовках столбцов, а области продаж — в левой части, как показано ниже.

Примечание: Если данные хранятся в таблице Excel, функция Транспонирование будет недоступна. Можно сначала преобразовать таблицу в диапазон или воспользоваться функцией ТРАНСП, чтобы повернуть строки и столбцы.

Вот как это сделать:

Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.

Примечание: Убедитесь, что вы хотите скопировать данные для этого, так как при использовании команды Вырезать или CTRL + X не работает.

Выберите новое расположение на листе, куда вы хотите вставить таблицу, и убедитесь в том, что для вставки данных достаточно места. Новая вставленная таблица будет полностью перезаписывать все уже имеющиеся данные и форматирование.

Щелкните правой кнопкой мыши верхнюю левую ячейку в том месте, куда нужно вставить таблицу, а затем выберите команду транспонировать .

После успешного поворота данных вы можете удалить исходную таблицу, и данные в новой таблице останутся без изменений.

Советы по транспонированию данных

Если данные содержат формулы, Excel автоматически обновит их в соответствии с новым расположением. Проверка использования абсолютных ссылок в формулах (в противном случае можно переключаться между относительными, абсолютными и смешанными ссылками перед поворотом данных.

Если вы хотите быстро поворачивать данные, чтобы просмотреть их в разных угловых обозначениях, попробуйте создать сводную таблицу для быстрой группировки данных путем перетаскивания полей из области строки в область столбцы (или наоборот) в списке полей сводной таблицы.

Вы можете вставлять данные в книгу как данные. Транспонировать перенакладывает содержимое скопированных ячеек при вставке. Данные строк будут вставлены в столбцы, и наоборот.

Вот как можно транспонировать содержимое ячейки:

Копирование диапазона ячеек.

Выделите пустые ячейки, в которые нужно вставить данные.

На вкладке Главная щелкните значок Вставить и выберите команду Вставить транспонировать.

Определение умножения

Рассмотрим две согласованные матрицы: $A=\left$ и $B=\left$. И определим для них операцию умножения.

По-моему, тут всё очевидно. Дальше можно не читать.

У тех, кто впервые видит это определение, сразу возникает два вопроса:

1. Что это за лютая дичь?
2. А почему так сложно?

Что ж, обо всём по порядку. Начнём с первого вопроса. Что означают все эти индексы? И как не ошибиться при работе с реальными матрицами?

Прежде всего заметим, что длинная строчка для расчёта ${{c}_{i;j}}$ (специально поставил точку с запятой между индексами, чтобы не запутаться, но вообще их ставить не надо — я сам задолбался набирать формулу в определении) на самом деле сводится к простому правилу:

1. Берём $i$-ю строку в первой матрице;
2. Берём $j$-й столбец во второй матрице;
3. Получаем две последовательности чисел. Перемножаем элементы этих последовательностей с одинаковыми номерами, а затем складываем полученные произведения.

Данный процесс легко понять по картинке:

Схема перемножения двух матриц

Ещё раз: фиксируем строку $i$ в первой матрице, столбец $j$ во второй матрице, перемножаем элементы с одинаковыми номерами, а затем полученные произведения складываем — получаем ${{c}_{ij}}$. И так для всех $1\le i\le m$ и $1\le j\le k$. Т.е. всего будет $m\times k$ таких «извращений».

Но хватит теории! Давайте посмотрим на реальные примеры. И начнём с самого простого случая — квадратных матриц.

Функция МОБР

Синтаксис и описание функции МОБР в Excel:

Рассмотрим расчет обратной матрицы посредством функции МОБР на конкретном примере. Предположим у нас имеется следующая квадратная матрица 3-го порядка:

E2:G4=МОБР(A2:C4)Ctrl + Shift + Ввод

• В том случае, когда исходная матрица является вырожденной (определитель равен нулю), то функция вернет ошибку #ЧИСЛО!;
• Если число строк и столбцов в матрице не совпадает, то функция возвратит ошибку #ЗНАЧ!;
• Функция также вернет ошибку #ЗНАЧ!, если хотя бы один из элементов матрицы является пустым или записан в текстовом виде.

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() или англ. MINVERSE .

Понятие обратной матрицы определено только для квадратных матриц, определитель которых отличен от нуля.

СОВЕТ: О нахождении определителя матрицы читайте статью Вычисление определителя матрицы в MS EXCEL

Матрица А -1 называется обратной для исходной матрицы А порядка n, если справедливы равенства А -1 *А=Е и А*А -1 =Е, где Е единичная матрица порядка n.

Для вычисления обратной матрицы в MS EXCEL существует специальная функция МОБР() .

Если элементы исходной матрицы 2 х 2 расположены в диапазоне А8:В9, то для получения транспонированной матрицы нужно (см. файл примера ):

• выделить диапазон 2 х 2, который не пересекается с исходным диапазономА8:В9, например, Е8:F9
• в Cтроке формул ввести формулу = МОБР (A8:B9) и нажать комбинацию клавиш CTRL+SHIFT+ENTER, т.е. нужно ввести ее как формулу массива (формулу можно ввести прямо в ячейку, предварительно нажав клавишу F2)

Если матрица большей размерности, то перед вводом формулы нужно выделить соответственно больший диапазон ячеек.

Массив может быть задан не только как интервал ячеек, например A8:B9, но и как массив констант, например =МОБР(<5;4: 3;2>) . Запись с использованием массива констант позволяет не указывать элементы в отдельных ячейках, а разместить их в ячейке вместе с функцией. Массив в этом случае указывается по строкам: например, сначала первая строка 5;4, затем через двоеточие записывается следующая строка 3;2. Элементы отделяются точкой с запятой.

Ссылка на массив также может быть указана как ссылка на именованный диапазон.

Некоторые квадратные матрицы не могут быть обращены: в таких случаях функция МОБР() возвращает значение ошибки #ЧИСЛО!. Матрицы не могут быть обращены, у которых определитель равен 0.

Если функция МОБР() вернула значение ошибки #ЗНАЧ!, то либо число строк в массиве не равно числу столбцов, либо какая-либо из ячеек в массиве пуста или содержит текст. Т.е. функция МОБР() пустую ячейку воспринимает не как содержащую 0 (как например, это делает СУММ() ), а как ошибочное значение.

3.3. Пример макроса

Второй способ опирается на рекуррентное соотношение,
связывающее два соседних члена в ряду  

Для того чтобы воспользоваться этим соотношением, надо
организовать на листе рекуррентную процедуру. Например, так, как
показано на Рис. 42  

Рис.42 Вычисление функции E₁(x)
итерационным способом

Один шаг итерации – это переход от значений в области
J2:J4 к значениям в области
L2:L4. Для того, чтобы сделать следующую
итерацию, нужно скопировать значения, получившиеся в области
L2:L4 и вставить их в область
J2:J4. При этом вставлять нужно только
величины, без формул. Величины в области H2:H4
дают исходные значения для начала итерации .Повторяя многократно
операцию Copy-Paste Special, можно получить в
ячейке L4 искомое значение. Однако
копирование – это скучное занятие и его было бы неплохо
автоматизировать. Для этого можно написать макрос.

Проще всего начать создание макроса через запись команд,
выполняемых на листе. Для этого идем в раздел меню Tools–Macro–Record
New Macro. Появляется окно (Рис. 43), в котором можно указать имя
макроса и где он будет расположен.  

Рис.43 Запись макро

После нажатия OK начинается
запись всех действий, выполняемых на листе. Когда все, что нужно
сохранено в макросе, запись надо остановить командой
Tools–Macro–Stop Recording. Результат можно увидеть, зайдя в
.   

Рис.44 Редактор Visual Basic

На Рис. 44 показан записанный макрос, который мы подвергли
небольшому редактированию – добавили цикл для повтора операции
Copy-Paste в числе nIter
раз. Величина nIter берется со
страницы из ячейки J6,
имеющей локальное имя n. Завершает
автоматизацию кнопка Repeat, к которой
привязан макрос Iteration.  

Такой подход использовался нами для построения
алгоритмов и в многомерном разрешении кривых.

3.5. Надстройки

Написанные пользователем программы хранятся в той книге
Excel, в которой они были созданы, в ее специальных разделах, называемых
модулями. Для того чтобы эти макросы были доступны в других
книгах, их можно туда скопировать, но можно поступить и по-другому –
создать надстройку.

Надстройка – это специальный файл Excel,
содержащий в  себе несколько VBA модулей, связанных со всеми
необходимыми библиотеками DLL. Надстройку можно подключить к системе
Excel для расширения ее возможностей.

Стандартная версия Excel включает несколько надстроек,
среди которых для нас примечательны две: Solver Add-In и Analysis
Toolpak.

Надстройка

Solver Add-In предназначена для оптимизации значения в целевой
ячейке. Решение находится с помощью поиска величин в других ячейках,
функционально связанных с целевой.

Надстройка

Analysis Toolpak содержит набор статистических инструментов
для анализа данных

В сети можно найти много надстроек для Excel, некоторые
из которых распространяются свободно, другие за деньги. Вот некоторые
примечательные.

J-Walk Chart Tools Add-In – бесплатная утилита для
управление диаграммами: добавление подписей, ярлыков, и т.п. – все то,
что отсутствует в стандартной версии.

XLStat
– большой (и дорогой) пакет статистического анализа, включающий, в
частности, и PLS регрессию.

Multivariate Analysis Add-in – условно бесплатный пакет для
анализа многомерных данных, созданный в Бристольском университете

Fitter – надстройка для нелинейного регрессионного анализа.

Chemometrics – пакет функций листа, выполняющий анализ
многомерных данных.

Подробнее о надстройках можно прочитать

здесь. 

Нахождение обратной матрицы в Excel

Матрица А -1
называется обратной для матрицы А
, если А
А -1 =А -1
А=Е
, где Е
— единичная матрица. Следует отметить, что обратную матрицу можно найти только для квадратной матрицы (одинаковое количество строк и столбцов).

Пусть дана матрица А
размерностью 3х3
, найдем для неё обратную матрицу с помощью функции =МОБР()
.

Для этого выделим диапазон G
3:
I
5
, который будет содержать элементы обратной матрицы, на вкладке Формулы
выберем Вставить функцию
.

В диалоговом окне Вставка
функции
выберем категорию Математические
— функция МОБР
— ОК
.

В диалоговом окне Аргументы функции
указываем диапазон массива В3:
D
5
, содержащего элементы матрицы А
. Нажимаем на клавиатуре сочетание клавиш Shift
+
Ctrl
и щелкаем левой кнопкой мыши по кнопке ОК
.

ВАЖНО.
Если просто нажать ОК
, то программа вычислит значение только первой ячейки диапазона матрицы А -1

Нажмите для увеличения

Мы получили обратную матрицу.

Как столбцы сделать строками в Excel, преобразовать строку в столбец

1. Позволять ar,ac быть количеством строк и столбцов вA.Точно так же, пусть br, bc быть количеством строк и столбцов вB.Тогда для выполнения умножения матриц необходимо, чтобы ac == br , Зачем? Итак, как вы видели в Excel, мы умножили строки и столбцы поэлементно, а затем добавили промежуточные продукты, чтобы получить конечный результат. Если есть несоответствие в размерах, и один вектор длиннее другого, мы больше не можем выполнять поэлементное умножение!
2. Размеры результирующей матрицы всегда будут ar,bc , То есть количество строк исходит от и количество столбцов исходит отB.

Теперь давайте посмотрим, как используется функция МУМНОЖ на конкретном примере. Имеется две матрицы, число строк одной из которых, соответствует количеству столбцов в другой и наоборот. Нам нужно перемножить два этих элемента.

Обратная матрица

Прежде чем переходить к понятию обратного выражения матрицы, следует рассмотреть алгоритм её транспонирования. Во время операции строки и столбцы переставляются местами. На рисунке представлен метод решения:

По аналогии обратная матрица сходна с обратными числами. Например, противоположной цифре 5 будет дробь 1/5 = 5 (-1) степени. Произведение этих чисел равно 1, выглядит оно так: 5*5 (-1) = 1. Умножение обычной матричной таблицы на обратную даст в итоге единичную: А* А (-1) = Е. Это аналог числовой единицы.

Но для начала нужно понять алгоритм вычисления обратной матрицы. Для этого находят её определитель. Разработано два метода решения: с помощью элементарных преобразований или алгебраических дополнений.

Более простой способ решения — путём алгебраических дополнений. Рассмотрим матричную таблицу А, обратная ей А (-1) степени находится по формуле:

Матрица обратного вида возможна только для квадратного размера таблиц 2*2, 3*3 и т. д. Обозначается она надстроенным индексом (-1). Задачу легче рассмотреть на более простом примере, когда размер таблицы равен 2*2. На первом этапе выполняют действия:

Обратного выражения матрицы не может быть, если определитель равен нулю. В рассматриваемом случае он равен -2, поэтому всё в порядке.

2 этап: рассчитывают матрицу миноров, которая имеет те же значения, что и первоначальная. Под минором k-того порядка понимается определитель квадратной матрицы порядка k*k, составленный из её элементов, которые располагаются в выбранных k- столбцах и k-строках.

При этом расположение элементов таблицы не меняется. Чтобы найти минор верхнего левого числа, вычёркивают строчку и столбец, в которых прописан этот элемент. Оставшееся число и будет являться минором. На выходе должна получиться таблица:

3 этап: находят алгебраические дополнения.

4 этап: определяют транспонированную матрицу.

Итогом будет:

Проверка решения: чтобы удостовериться, что обратная таблица чисел найдена верно, следует выполнить проверочную операцию.

3.6. Установка надстроек

Прежде чем  начать использовать надстройку, нужно
выполнить процедуру ее установки, которая состоит из двух частей.

В первой фазе файлы, входящие в пакет надстройки
размещают на компьютере. В некоторых пакетах имеется программа
Setup.exe, которая выполняет это автоматически. В других файлы нужно
размещать самостоятельно. Объясним, как это нужно сделать. В состав
пакета обязательно входит файл с расширением XLA и несколько
вспомогательных файлов с расширениями DLL, HLP и др. Все вспомогательные
файлы должны  размещаться в следующих директориях:
C:\Windows, или  C:\Windows\System или
C:\Windows\System32. Основной файл (с расширением XLA) может, в
принципе, находится в любом месте, но две директории являются
предпочтительными.

Microsoft рекомендует размещать файлы XLA в директории
C:\Documents and Settings\User\Application
Data\Microsoft\AddIns, где User – это
имя, под которым происходит вход в систему. Тогда этот файл можно быстро
загрузить на второй фазе установки. Однако, если рабочие книги
используются на нескольких компьютерах, с разными именами
User, то, при смене компьютера, связи с
основным файлом надстройки теряются и их приходится

обновлять.

Поэтому мы предлагаем поместить файл
Chemometrics.xla в директорию, которая имеет одно и то же имя на
разных компьютерах, например C:\Program
Files\Chemometrics. Автоматическая установка надстройки
Chemometrics Add-In
описана здесь.

Вторая фаза проводится из открытой книги Excel. В версии
2003 нужно выполнить последовательность команд
Tools-Add-Ins, а в версии 2007 последовательность:
Office Button-Excel Options-Add-Ins-Go. В
появившемся окне (см Рис. 45) нужно нажать Browse
и найти в компьютере нужный файл XLA.  

 

Рис.45 Установка надстройки

После того, как надстройка установлена, ее можно
активировать и деактивировать устанавливая отметку напротив имени. Для
удаления надстройки нужно снять галочку против ее имени в окне
Add-Ins, закрыть Excel и удалить все ранее
установленные файлы с компьютера.  

Заключение

Мы рассмотрели основные приемы работы с матрицами в
системе Excel

За рамками пособия осталось еще много всего важного. Частично заполнить эти пробелы поможет пособие Проекционные методы в
системе Excel