Скалярное произведение матриц в excel. Функции для работы с матрицами в Excel

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

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

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

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

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

МУМНОЖ(массив1;массив2)

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

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

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

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

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



2. Мастер функций запускается. Перемещаемся в блок операторов «Математические» , но на этот раз выбираем наименование СУММПРОИЗВ . Клацаем по кнопке «OK» .



3. Происходит открытие окна аргументов вышеуказанной функции. Данный оператор предназначен для перемножения различных массивов между собой. Его синтаксис следующий:

   СУММПРОИЗВ(массив1;массив2;…)

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

   Ставим курсор в поле «Массив1» . Тут нам нужно будет ввести адрес первой строки первой матрицы. Для этого, зажав левую кнопку мыши, нужно просто выделить её на листе курсором. Тут же координаты данного диапазона будут отображены в соответствующем поле окна аргументов. После этого следует зафиксировать координаты полученной ссылки по столбцам, то есть, эти координаты нужно сделать абсолютными. Для этого перед буквами в выражении, которое вписано в поле, устанавливаем знак доллара ($ ). Перед координатами, отображенными в цифрах (строки), это делать не следует. Также, можно вместо этого выделить всё выражение в поле и трижды нажать на функциональную клавишу F4 . В данном случае абсолютными тоже станут лишь координаты столбцов.



4. После этого устанавливаем курсор в поле «Массив2» . С этим аргументом будет посложнее, так как по правилам умножения матриц, вторую матрицу нужно «перевернуть». Для этого используем вложенную функцию ТРАНСП .

   Чтобы перейти к ней, клацаем по значку в виде треугольника, направленного острым углом вниз, который размещен слева от строки формул. Открывается список недавно используемых формул. Если вы в нем найдете наименование «ТРАНСП» , то щелкайте по нему. Если же вы давно использовали данный оператор или вообще никогда не применяли его, то в этом списке указанное наименование вы не отыщите. В этом случае требуется нажать по пункту «Другие функции…» .



5. Открывается уже хорошо знакомое нам окно Мастера функций . На этот раз перемещаемся в категорию «Ссылки и массивы» и выбираем наименование «ТРАНСП» . Щелкаем по кнопке «OK» .



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

   ТРАНСП(массив)

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

   Итак, устанавливаем курсор в поле «Массив» и выделяем первый столбец второй матрицы на листе с зажатой левой кнопкой мыши. Адрес отобразится в поле. Как и в предыдущем случае, тут тоже нужно сделать определенные координаты абсолютными, но на этот раз не координаты столбцов, а адреса строк. Поэтому ставим знак доллара перед цифрами в ссылке, которая отображается в поле. Можно также выделить всё выражение и дважды кликнуть по клавише F4 . После того, как нужные элементы стали иметь абсолютные свойства, не жмем на кнопку «OK» , а так же, как и в предыдущем способе, применяем нажатие комбинации клавиш Ctrl+Shift+Enter .



7. Но на этот раз у нас заполнился не массив, а только одна ячейка, которую мы ранее выделили при вызове Мастера функций .



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

   Для копирования прибегнем к использованию маркера заполнения. Наводим курсор на нижний правый угол ячейки, в которой расположена формула. Курсор преобразуется в черный крестик. Это и есть маркер заполнения. Зажимаем левую кнопку мыши и протягиваем курсор по всему вышеуказанному диапазону. Сама начальная ячейка с формулой должна стать левым верхним элементом данного массива.



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

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

Вычислим определитель (детерминант) матрицы с помощью функции МОПРЕД() или англ. MDETERM, разложением по строке/столбцу (для 3 х 3) и по определению (до 6 порядка).

Определитель матрицы (det) можно вычислить только для квадратных матриц, т.е. у которых количество строк равно количеству столбцов.

Для вычисления определителя в MS EXCEL есть специальная функция МОПРЕД() . В аргументе функции необходимо указать ссылку на диапазон ячеек (массив), содержащий элементы матрицы (см. файл примера ).

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

Для матриц порядка 2 можно определитель можно вычислить без использования функции МОПРЕД() . Например, для вышеуказанной матрицы выражение =A7*B8-B7*A8 вернет тот же результат.

Для матрицы порядка 3, например размещенной в диапазоне A16:C18 , выражение усложняется =A16*(B17*C18-C17*B18)-B16*(A17*C18-C17*A18)+C16*(A17*B18-B17*A18) (разложение по строке).

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

Свойства определителя

Теперь о некоторых свойствах определителя (см. файл примера ):

• Определитель равен определителю исходной матрицы
• Если в матрице все элементы хотя бы одной из строк (или столбцов) нулевые, определитель такой матрицы равен нулю
• Если переставить местами две любые строки (столбца), то определитель полученной матрицы будет противоположен исходному (то есть, изменится знак)
• Если все элементы одной из строк (столбца) умножить на одно и тоже число k, то определитель полученной матрицы будет равен определителю исходной матрицы, умноженному на k
  
• Если матрица содержит строки (столбцы), являющиеся линейной комбинацией других строк (столбцов), то определитель =0
• det(А)=1/det(А -1), где А -1 - матрице А (А - квадратная невырожденная матрица).

Вычисление определителя матрицы по определению (до 6 порядка включительно)

СОВЕТ : Этот раздел стоит читать только продвинутым пользователям MS EXCEL. Кроме того материал представляет только академический интерес, т.к. есть функция МОПРЕД() .

Как было показано выше для вычисления матриц порядка 2 и 3 существуют достаточно простые формулы и правила. Для вычисления определителя матриц более высокого порядка (без использования функции МОПРЕД() ) придется вспомнить определение:

Определителем квадратной матрицы порядка n х n является сумма, содержащая n! слагаемых (=ФАКТР(n) ). Каждое слагаемое представляет собой произведение n элементов матрицы, причем в каждом произведении содержится элемент из каждой строки и из каждого столбца матрицы А . Перед k-ым слагаемым появляется коэффициент (-1) , если элементы матрицы А в произведении упорядочены по номеру строки, а количество инверсий в k-ой перестановке множества номеров столбцов нечетно.

где (α 1 ,α 2 ,...,α n ) - перестановка чисел от 1 до n , N(α 1 ,α 2 ,...,α n ) - число , суммирование идёт по всем возможным перестановкам порядка n .

Попытаемся разобраться в этом непростом определении на примере матрицы 3х3.

Для матрицы 3 х 3, согласно определения, число слагаемых равно 3!=6, а каждое слагаемое состоит из произведения 3-х элементов матрицы. Ниже приведены все 6 слагаемых, необходимых для вычисления определителя матрицы 3х3:

• а21*а12*а33
• а21*а32*а13
• а11*а32*а23
• а11*а22*а33
• а31*а22*а13
• а31*а12*а23

а21, а12 и т.д. - это элементы матрицы. Теперь поясним, как были сформированы индексы у элементов, т.е. почему, например, есть слагаемое а11*а22*а33, а нет а11*а22*а13.

Посмотрим на формулу выше (см. определение). Предположим, что второй индекс у каждого элемента матрицы (от 1 до n) соответствует номеру столбца матрицы (хотя это может быть номер строки (это не важно т.к. определители матрицы и ее равны). Таким образом, второй индекс у первого элемента в произведении всегда равен 1, у второго - 2, у третьего 3. Тогда первые индексы у элементов соответствуют номеру строки и, в соответствии с определением, должны определяться из перестановок чисел от 1 до 3, т.е. из перестановок множества (1, 2, 3).

Теперь понятно, почему среди слагаемых нет а11*а22*а13, т.к. согласно определения (в каждом произведении содержится элемент из каждой строки и из каждого столбца матрицы А ), а в нашем слагаемом нет элемента из строки 3.

Примечание : Перестановкой из n чисел множества (без повторов) называется любое упорядочивание данного множества, отличающиеся друг от друга лишь порядком входящих в них элементов. Например, дано множество их 3-х чисел: 1, 2, 3. Из этих чисел можно составить 6 разных перестановок: (1, 2, 3), (1, 3, 2), (2, 3, 1), (2, 1, 3), (3, 1, 2), (3, 2, 1). См. статью

Число перестановок множества из 3-х чисел =3!=6 (что, конечно, равно числу слагаемых в выражении для расчета определителя, т.к. каждому слагаемому соответствует своя перестановка). Для матрицы 3х3 все перестановки приведены в примечании выше. Можно убедиться, что в каждом слагаемом первые индексы у элементов равны соответствующим числам в перестановке. Например, для слагаемого а21*а12*а33 использована перестановка (2, 1, 3).

СОВЕТ : Для матрицы 4 порядка существует 4! перестановок, т.е. 26, что соответствует 26 слагаемым, каждое из которых является произведением различных 4-х элементов матрицы. Все 26 перестановок можно найти в статье .

Теперь, когда разобрались со слагаемыми, определим множитель перед каждым слагаемым (он может быть +1 или -1). Множитель определяется через четность числа инверсий соответствующей перестановки.

Примечание : Об инверсиях перестановок (и четности числа инверсий) можно почитать, например, в статье

Например, первому слагаемому соответствует перестановка (2, 1, 3), у которой 1 инверсия (нечетное число) и, соответственно, -1 в степени 1 равно -1. Второму слагаемому соответствует перестановка (2, 3, 1), у которой 2 инверсии (четное число) и, соответственно, -1 в степени 2 равно 1 и т.д.

Сложив все слагаемые: (-1)*(а21*а12*а33)+(+1)*(а21*а32*а13)+(-1)*(а11*а32*а23)+(+1)*(а11*а22*а33)+(-1)*(а31*а22*а13)+(+1)*(а31*а12*а23) получим значение определителя.

В файле примера на листе 4+, и зменяя порядок матрицы с помощью , можно вычислить определитель матрицы до 6 порядка включительно.

Следует учитывать, что при вычислении матрицы 6-го порядка в выражении используется уже 720 слагаемых (6!). Для 7-го порядка пришлось бы сделать таблицу для 5040 перестановок и, соответственно, вычислить 5040 слагаемых! Т.е. без использования МОПРЕД() не обойтись (ну, или можно вычислить определитель вручную методом Гаусса).

Вычислить значения корней сформированной системы уравнений двумя методами: обратной матрицы и методом Крамера.

Введем данные значения в ячейки А2:С4 – матрица А и ячейки D2:D4 – матрица В.

Решение системы уравнений методом обратной матрицы

Найдем матрицу, обратную матрице А. Для этого в ячейку А9 введем формулу =МОБР(A2:C4). После этого выделим диапазон А9:С11, начиная с ячейки, содержащей формулу. Нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ENTER. Формула вставится как формула массива. =МОБР(A2:C4).
Найдем произведение матриц A-1 * b. В ячейки F9:F11 введем формулу: =МУМНОЖ(A9:C11;D2:D4) как формулу массива. Получим в ячейках F9:F11 корни уравнения:

Решение системы уравнений методом Крамера

Решим систему методом Крамера, для этого найдем определитель матрицы.
Найдем определители матриц, полученных заменой одного столбца на столбец b.

В ячейку В16 введем формулу =МОПРЕД(D15:F17),

В ячейку В17 введем формулу =МОПРЕД(D19:F21).

В ячейку В18 введем формулу =МОПРЕД(D23:F25).

Найдем корни уравнения, для этого в ячейку В21 введем: =B16/$B$15, в ячейку В22 введем: = =B17/$B$15, в ячейку В23 введем: ==B18/$B$15.

Получим корни уравнения:

Рис. 1 Настройка региональных опций

Изменить настройки компьютера можно с помощью Панели Управления , через закладку Regional and Language Options с переходом на опцию Customize.

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

SUM(A1:A9) .

Эта же функция в русской версии имеет вид

СУММ(A1:A9).

Список соответствия русско-английских имен функций приведен в файле FUNCS.XLS , который находится на вашем компьютере, обычно в директории C:\Program Files\Microsoft Office\OfficeVer\1049\ , где OfficeVer – это имя версии, например Office 11. Подробнее о локализации Exсel можно прочитать . В файле Excel_Functions.xls приведены имена всех функций на 16 языках.

В этом пособии мы используем английскую версию Excel 2003, в которой разделитель дробной части – точка, а разделитель списков – запятая. Русские имена функций приводятся для справки.

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

Ячейка может иметь содержание: число, текст или формулу. Для наглядности ячейки можно форматировать: менять фонт, цвет, рамку, и т.д.

Все операции на листе выполняются с помощью меню, представленного в верхней части окна (). Меню в Excel 2007 существенно отличается от прежней версии. Там, вместо обычных иконок, появилась лента. Мы не будем подробно разбирать отличия версий. Те, кому это интересно могут прочитать .

1.3. Адресация

В Excel каждая ячейка имеет адрес, составленный из заголовков столбцов и строк. Например, адрес первой ячейки на листе – A1 . Ячейка, находящаяся на пересечении третьего столбца и пятой строки, имеет адрес C5 . Текущая ячейка выделена жирной рамкой, и ее адрес высвечивается в окне Name Box . (см. ). Если мы хотим скопировать содержимое какой-то ячейки (например, A1 ) в другую клетку (например, F 1), нужно во второй ячейке написать формулу =адрес , например =A1 . Адресация (ссылка) может быть абсолютной, относительной и смешанной.

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

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

1.4. Область

Матрица занимает на листе область. Пример показан на Рис. 6, где расположена матрица, которая имеет 9 строк (с 2 по 10) и 3 столбца (с B по D).

При работе с матрицами бывает удобно присвоить ее области имя. Для этого есть два способа. Самый простой – это выделить нужную область на листе, а затем кликнуть в окно Name Box (см. ), стереть там адрес и вписать имя – например Data (Рис. 6). Другой способ – это действовать через меню: Insert-Name-Define .

Имя области может быть глобальным – доступным для любых листов книги, или локальным – определенным только для одного листа. В последнем случае, имя следует определять в форме: ListName!RangeName

1.5. Простейшие вычисления

Для проведения вычислений в Excel используются формулы. Формула начинается со знака равенства (=) и может включать в себя: ссылки, операторы, функции и константы.

Операторы позволяют проводить простейшие арифметические вычисления, примеры которых показаны на Рис. 7

H

Рис.7 Простейшие вычисления

1.6. Функции

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

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

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

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

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

SUM/ СУММ

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

Синтаксис :

SUM (number1 [,number2 ] [,... ])

Пример

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

SUMSQ / СУММКВ

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

Синтаксис :

SUMSQ (number1 [,number2 ] [,... ])

Пример

Рис.13 Функция SUM SQ

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

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

Синтаксис :

SUMPRODUCT (array1 , array2 , ...)

Пример

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

AVERAGE / СРЗНАЧ

VAR / ДИСП

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

CORREL / КОРРЕЛ

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

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

1.8. Ошибки в формулах

При вводе формул, а также при последующем изменении листов, возникают ошибки. При этом вместо ожидаемого результата в ячейке появляется один из символов, показанных на Рис. 20 .

Рис.20 Ошибки в формулах

Установить происхождение ошибки можно с помощью меню Formula Auditing, доступной через раздел Tools .

1 .9. "Растаскивание" формул

При работе с данными в виде матрицы, часто возникает необходимость ввести не одну, а целую серию формул. Например, при выполнении SNV преобразования спектральных данных нужно вычислить средние значения и среднеквадратичные отклонения по каждой строке. Очень утомительно было бы повторять одну и ту же формулу многократно, меняя в ней только аргумент, даже для сильно усеченного примера, показанного на . А в реальных данных число строк или столбцов может доходить до десятков тысяч. К счастью это и не нужно, поскольку можно воспользоваться техникой "растаскивания" формул.

Поясним эту технику на нашем примере. Начнем с ввода формулы-образца. В этом случае – это формула, помещенная в ячейку J3 .

Рис.21 Маркер заполнения

Формулы в соседние ячейки можно вставить при помощи маркера заполнения, которым называется небольшой черный квадрат в правом нижнем углу выделенной области ячеек. При наведении мышки на этот маркер, указатель принимает вид черного креста. После этого можно перетащить ячейку, содержащую формулу, в примыкающий диапазон. Тащить можно по вертикали, как показано на Рис. 22, и по горизонтали.

Рис.22 Растаскивание серии однотипных формул

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

Рис.23 Копирование серии однотипных формул

1 .10. Построение графиков

В Excel можно строить диаграммы разных типов. Но для нас интересны только два вида: диаграмма рассеяния (scatter) и график (line). Пример диаграммы рассеяния приведен на Рис. 24 .

Рис.24 Диаграмма рассеяния

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

Методы построения графиков в версиях 2003 и 2007 сильно отличаются. Поэтому мы не будем на этом останавливаться, предоставив эту тему для самостоятельного изучения .

2. Матричные операции

2.1. Формулы массива

Многие операции с матрицами выполняются с помощью формул специального вида, называемыми формулами массива . От других формул они отличаются тем, что их результатом является не одна величина (число), а набор величин – массив. При вводе таких формул требуется специальное подтверждение – вместо клавиши ENTER надо нажимать комбинацию из трех клавиш CTRL+SHIFT+ENTER .

Поясним использование формул массивов на простом примере. Предположим, что нам нужно выполнить автошкалирование (стандартизацию) данных, записанных в матрице X . Для этого сначала нужно вычислить средние значения m j и среднеквадратичные отклонения s j для каждого (j -го) столбца X , а затем вычесть из каждого столбца величину m j и поделить на величину s j

Такое преобразование можно сделать с помощью обычных формул, как это показано на Рис. 25.

Рис.25 Обычная формула

Надо только не забыть указать знак $ перед номерами строк 9 (m ) и 11 (s ) , чтобы зафиксировать положение соответствующих величин в строках.

Если матрица X велика, то удобнее воспользоваться формулой массива. (Рис. 26). Назовем соответствующие области на листе: X , m и s . Отметим пустую область N3:R7 , размеры которой совпадают с ожидаемым результатом. После этого введем в Formula Bar выражение =(X-m)/s . Завершает ввод комбинация клавиш CTRL+SHIFT+ENTER . Если все сделано правильно, то в Formula Bar появится формула { =(X-m)/s} , заключенная в фигурные скобки {} . Это – признак формулы массива. .

2.2. Создание и изменение формул массива

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

Если выделить слишком большую область, то при вычислении избыточные ячейки будут заполнены символами ошибки #N/A . Если область вывода будет меньше, чем нужно, то часть результатов пропадет. После выделения области, в Formula Bar записывается формула и нажимается CTRL+SHIFT+ENTER .

Альтернативно, сначала можно ввести формулу в одну ячейку, затем отметить область вывода, начиная с этой ячейки (право и вниз), потом перейти в Formula Bar и нажать CTRL+SHIFT+ENTER .

Для того, чтобы изменить формулу массива нужно выделить область содержащую результат. Затем нужно перейти Formula Bar . При этом фигурные скобки вокруг формулы { } исчезнут. После этого формулу можно изменить и нажать CTRL+SHIFT+ENTER .

Для того чтобы расширить область, которую занимает формула массива, достаточно выделить для нее новую область, перейти в Formula Bar и нажать CTRL+SHIFT+ENTER . А вот для того, чтобы уменьшить эту область (например, чтобы избавиться от символов #N/A ) придется потратить больше сил. Сначала нужно встать на любую ячейку области, перейти в Formula Bar и скопировать строку формулы. Затем нужно стереть содержимое старой области и отметить новую, меньшую область. После этого опять перейти в Formula Bar , вставить формулу и нажать CTRL+SHIFT+ENTER .

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

Рис.27 Предупреждение о недопустимой операции с формулой массива

2.3. Простейшие операции с матрицами

С помощью формул массива матрицы можно складывать и умножать на числа.

Рис.28 Сложение матриц и умножение на число

Для перемножения двух матриц используется функция MMULT .

2.4. Доступ к частям матрицы

Для доступа и отделения частей матрицы применяются две стандартные функции листа.

OFFSET / СМЕЩ

Синтаксис

OFFSET (reference , rows , cols [, height] [, width])

r eference – это ссылка на область, начиная с которой вычисляется смещение;

r ows – число строк, на которое надо сместиться (положительное – вниз, отрицательное вверх от начала);

cols –То же, но для столбцов (положительное – вправо, отрицательное влево от начала);

height – необязательный аргумент. Положительное число строк возвращаемой ссылки;

width – необязательный аргумент. Положительное число столбцов возвращаемой ссылки.

Примечания

Если аргументы height или width опущены, то предполагается, что используется такая же высота или ширина, как в аргументе reference ;

Аргумент reference – это ссылка на область, которая должна быть реальным, а не виртуальным массивом, т.е. находиться где-то на листе.

Пример

Рис.29 Функция OFFSET

OFFSET

INDEX / ИНДЕКС

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

Синтаксис

INDEX (reference [, row_num] [, column_num])

r eference – это массив значений (матрица);

r ow_num – необязательный аргумент. Номер строки, из которой берутся значения;

col_num –необязательный аргумент. Номер столбца, из которого берутся значения;

Примечания

Если аргумент row_num опущен, то выбирается весь столбец;

Если аргумент col_num опущен, то выбирается вся строка;

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

Аргумент reference может быть ссылкой, как на область, так и на виртуальный массив.

Пример

Рис.30 Функция INDEX

INDEX является функцией массива и ее ввод должен завершаться нажатием комбинации CTRL+SHIFT+ENTER .

2.5. Унарные операции

К матрицам можно применять следующие унарные формулы.

MINVERSE / МОБР

Возвращает обратную матрицу.

Синтаксис

MINVERSE (array )

array – это массив значений (матрица).

Примечания

Матрица array должна быть квадратной;

Если матрица вырождена, то выводится символ ошибки #VALUE!.

Пример

Рис.3 1 Функция MINVERSE

MINVERSE

TRANSPOSE / ТРАНСП

Возвращает транспонированную матрицу

Синтаксис

TRANSPOSE (array )

array

Пример

Рис.3 2 Функция TRANSPOSE

TRANSPOSE является функцией массива и ее ввод должен завершаться нажатием комбинации CTRL+SHIFT+ENTER .

MDETERM / МОПРЕД

Возвращает детерминант матрицы.

Синтаксис

MDETERM (array )

array – это массив значений (матрица).

Примечания

Матрица array должна быть квадратной.

M DETERM не является функцией массива и ее ввод должен завершаться нажатием одной клавиши ENTER .

2.6. Бинарные операции

К матрицам можно применять следующие бинарные операции.

MMULT / МУМНОЖ

Возвращает произведение двух матриц.

Синтаксис

MMULT (array1 , array2 )

array1 , array2 – перемножаемые матрицы.

Примечания

Число столбцов в матрице array1 должно быть равно числу строк в матрицу array2 , иначе выводится символ ошибки #VALUE!;

В получаемом массиве должно быть не более 5461 элементов (Excel 2003).

Пример

Рис.33 Функция MMULT

MMULT является функцией массива и ее ввод должен завершаться нажатием комбинации CTRL+SHIFT+ENTER .

2.7 . Регрессия

y =b +m 1 x 1 +…+m J x J +e

Аппроксимирует известные значения вектора откликов known_y"s для заданных значений матрицы предикторов known_x"s и возвращает значения y , для заданного массива new_x"s .

Синтаксис

TREND (known_y"s [,known_x"s] [,new_x"s] [,const])

known_y"s y (калибровочный набор);

known_x"s – необязательный аргумент. Матрица известных значений предикторов X (калибровочный набор);

new_x"s – необязательный аргумент. Матрица новых значений предикторов X

const – необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы параметр b был равен нулю. Если const имеет значение TRUE или опущено, то b b =0.

Примечания

Вектор known_y"s должен занимать один столбец, тогда каждый столбец матрицы массива known_x"s интерпретируется как отдельная переменная;

Если аргумент known_x"s опущен, то предполагается, что это вектор чисел {1;2;3;...} такого же размера, как и known_ y "s ;

Матрица новых значений new _x"s должна иметь столько же столбцов (переменных), как и матрица known_x"s ;

Если аргумент new _x"s опущен, то предполагается, что он совпадает с массивом known_x"s . Результат является вектором, в котором число строк равно числу строк в массиве new _x"s .

Пример

Рис.34 Функция TREND

Функция TREND является функцией массива и ее ввод должен завершаться нажатием комбинации CTRL+SHIFT+ENTER .

LINEST / ЛИНЕЙН

Дополняет функцию TREND и выводит некоторые статистические значения, связанные с регрессией

y =b +m 1 x 1 +…+m J x J +e

Синтаксис

LINEST (known_y"s [,known_x"s] [,new_x"s] [,const] [,stats] )

known_y"s – вектор известных значений откликов y (калибровочный набор);

known_x"s – необязательный аргумент. Матрица известных значений предикторов X (калибровочный набор);

new_x"s – необязательный аргумент. Матрица новых значений предикторов X new (проверочный набор) для которых вычисляются и выводятся значения откликов;

const – необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы параметр b был равен нулю. Если const имеет значение TRUE или опущено, то b вычисляется обычным образом, иначе b =0;

stats – необязательный аргумент. Логическое значение, которое указывает, нужно ли выводить дополнительные статистические значения. Если stats имеет значение FALSE или опущено, то выводятся только оценки коэффициентов m J , …, m 2 , m 1 и b . Иначе выводится целая таблица

Рис. 35 Таблица вывода функция LINEST

m J , …, m 2 , m 1 и b – оценки регрессионных коэффициентов;

s J , …, s 2 , s 1 и s b – стандартные ошибки для оценок регрессионных коэффициентов;

R 2 – коэффициент детерминации;

s y – стандартная ошибка оценки y;

F – F-статистика;

DoF – число степеней свободы;

SS reg – регрессионная сумма квадратов;

SS res – остаточная сумма квадратов.

Примечания

Пример

Рис.36 Функция LINEST

Функция LINEST является функцией массива и ее ввод должен завершаться нажатием комбинации CTRL+SHIFT+ENTER .

2.8. Критическая ошибка в Excel 2003

В Excel 2003 функции TR E ND и LINEST при определенных условиях дают неверный результат.

Так происходит когда одновременно:

среднее значение по каждой переменной в матрице предикторов X равно нулю;

среднее значение отклика Y не равно нулю.

2.9. Виртуальный массив

При анализе данных часто возникает проблема сохранения промежуточных результатов, которые нужны не сами по себе, а только для того, чтобы вычислить по ним другие, полезные значения. Например, остатки в методе PCA часто нам не интересны, а нужны только для определения полной объясненной дисперсии, ортогональных расстояний и т.п. При этом размеры таких промежуточных массивов могут быть очень велики, да и к тому же их приходится вычислять при различных значениях числа главных компонент. Все это ведет к заполнению рабочей книги большим количеством ненужных, промежуточных результатов. Этого можно избежать, если использовать виртуальные массивы. Поясним их суть на простом примере.

Рис.38 Пример использования виртуального массива

Предположим, что задана матрица A , а нужно вычислить детерминант матрицы A t A . На Рис. 38 показаны два способа вычисления. Первый – через последовательность промежуточных массивов, отмеченных красными стрелками. Второй – с помощью одной формулы, показанной зеленой стрелкой. Оба пути ведут к одному и тому же результату, но красный путь занимает на листе много места, а зеленый последовательно использует несколько промежуточных виртуальных массивов. Все они, по сути, совпадают с реальными массивами красного пути, но на лист не выводятся.

Первый массив – это транспонированная матрица A t , получаемая как результат функции (A).

Второй виртуальный массив получается тогда, когда первый виртуальный массив умножается на матрицу A с помощью функции (TRANSPOSE(A), A) .

И, наконец, к этому, второму виртуальному массиву применяется функция .

3 . Расширение возможностей Excel

3 .1. Программирование. Язык VBA

Иногда стандартных возможностей Excel не хватает и приходится добавлять свои собственные подпрограммы. Для этой цели служит специальный язык программирования – Microsoft Visual Basic for Applications (VBA) . С его помощью можно создавать макросы – наборы команд, выполняющих определенную последовательность действий, и функции – программы для специальных вычислений на листе. Макросы – это способ автоматизации стандартных процедур. Однажды создав макрос, его можно использовать для повтора рутинных действий. Обратиться к макросу можно через меню Tools-Macro-Macros . Иногда удобно бывает приписать макрос к новой кнопке на панели инструментов или на листе.

Функции, созданные пользователем, вызываются также как и стандартные, встроенные функции – через Formula Bar .

Для того, чтобы макросы и пользовательские функции были доступны для применения, нужно установить соответствующий уровень безопасности через меню Tools-Macro-Security (Excel 2003)

Рис.39 Выбор уровня безопасности в Excel 2003

В Excel 2007 установка уровня безопасности происходит через Office Button- Excel Options- Trust Center.

Рис.40 Выбор уровня безопасности в Excel 2007

Если выбран уровень Medium (2003) или Disable all macros with notification (2007), то при каждом входе в Excel система будет запрашивать разрешение на использование макросов. Мы рекомендуем установить уровни так, как показано на Рис. 39 или Рис. 40 , но не пренебрегать надежным антивирусом для проверки посторонних файлов Excel.

При начальной установке Excel 2007 возможности работы с VBA сильно ограничены. Чтобы восстановить их нужно пройти по цепочке Office Button– Excel Options–Popular и включить опцию Show Developer Tab in the Ribbon .

3 .2. Пример

Рассмотрим вопрос о применении средств VBA на примере.

При моделировании неизотермической кинетики (ДСК, ТГА, и т.п.) необходима интегральная показательная функция (integral exponential) E 1 (x ). По определению,

Для вычисления E 1 (x ) можно использовать бесконечный ряд

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

Рис.41 Вычисление функции E 1 (x ) на листе

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

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

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

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

Рис.42 Вычисление функции E 1 (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 . Результат можно увидеть, зайдя в редактор Visual Basic .

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

На Рис.

Рис.45 Функция IntExp

На Рис. 45 приведен код этой функции и пример обращения к ней.

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

VBA – это довольно медленный язык и он плохо подходит для больших вычислений. Например, не стоит писать на этом языке процедуру для PCA декомпозиции – на больших массивах она будет считать очень долго. Правильнее рассматривать Excel и VBA как интерфейс (front end) для ввода и вывода данных, которые затем передаются в динамическую библиотеку (DLL), написанную на быстром языке, таком как C++ (back end). Именно эта концепция и была реализована в надстройках Fitter и Chemometrics .

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

3 .5 . Надстройки

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

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

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

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. За рамками пособия осталось еще много всего важного. Частично заполнить эти пробелы поможет пособие

РХТУ им. Д.B. Менделеева Кафедра ИКМ Методическое пособие по изучению Excel

Операции с матрицами в Excel

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

Транспонирование .

Транспонированной называется матрица (A T), в которой столбцы исходной матрицы (А) заменяются строками с соответствующими номерами.

Пример . Пусть в диапазон ячеек А1:Е2 введена матрица размера 2x5. Необходимо получить транспонированную матрицу.

Выделить указателем мыши при нажатой левой кнопке блок ячеек, где будет находиться транспонированная матрица. В нашем примере блок размера 5 x2 в диапазоне А4:В8.

Стандартная  вставка функции.

Мастер функций в рабочем полеКатегория выбратьСсылки и массивы , а в рабочем полеФункция – имя функции ТРАСП (рис.1)

рис.1

Появившееся диалоговое окно ТРАСП мышью отодвинуть в сторону от исходной матрицы и ввести диапазон исходной матрицы А1:Е2 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После чего, не нажимая кнопку ОК, нажать сочетание клавишCTRL+SHIFT+ENTER(рис.2)

Если транспонированная матрица не появилась в заданном диапазоне А4:В8, то надо щелкнуть указателем мыши в строке формул и повторить нажатие клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне А4:В8 появится транспонированная матрица.

Рис.2

Вычисление определителя матрицы

Пусть в диапазон А1:С3 введена матрица. Необходимо вычислить определитель матрицы

Табличный курсор поставить в ячейку, в которой требуется получить значение определителя, например. В А4.

Нажать на панели инструментов Стандартная кнопкуВставка функции

В появившемся диалоговом окне Мастер функций в рабочем полеКатегории выбратьМатематические, а в рабочем полеФункция – имя функции МОПРЕД. После этого нажать на кнопку ОК.

Появившееся диалоговое окно МОПРЕД мышью отодвинуть в сторону от исходной матрицы и ввести диапазон исходной матрицы А1:С3 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После чего нажать кнопку ОК.

В ячейке А4 появится значение определителя матрицы.

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

Пусть в диапазон А1:С3 введена матрица. Необходимо в диапазоне А5:С7 получить обратную матрицу.

Выделить блок ячеек под обратную матрицу (в нашем примере А5:С7)

Нажать на панели инструментов Стандартная кнопкуВставка функции

В появившемся диалоговом окне Мастер функций в рабочем полеКатегории выбратьМатематические, а в рабочем полеФункция – имя функции МОБР. После этого нажать на кнопку ОК.

Появившееся диалоговое окно МОБР мышью отодвинуть в сторону от исходной матрицы и ввести диапазон исходной матрицы А1:С3 в рабочее поле Массив (указателем мыши при нажатой левой кнопке). После чего, не нажимая кнопку ОК, нажать сочетание клавишCTRL+SHIFT+ENTER

Если обратная матрица не появилась в заданном диапазоне А1:С3, то надо щелкнуть указателем мыши в строке формул и повторить нажатие клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне А1:С3 появится обратная матрица.

Сложение и вычитание матриц, умножение и деление матрицы на число

Пример. Пусть матрица А введена в диапазон А1:С2, а матрица В – в диапазон А4:С5. Необходимо найти матрицу С, являющуюся их суммой, в диапазоне Е1:G2.

Табличный курсор установить в левый верхний угол результирующей матрицы – ячейку Е1.

Ввести формулу для вычисления первого элемента результирующей матрицы =А1+А4 (предварительно установить английскую раскладку клавиатуры)

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

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

Подобным образом вычисляется разность матриц, только в формуле вместо знака +, ставится знак -.

Если необходимо умножить (разделить) матрицу А на число k, то формула будет иметь вид =А1*k.

Рис.3

Умножение матриц

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

Пример . Пусть матрица введена в диапазонA1:D3, а матрица В – в диапазон А4:В7. Необходимо найти произведение этих матриц С=Аx В.

Выделить блок ячеек указателем мыши при нажатой левой кнопке под результирующую матрицу. Если матрица А имеет размерность 3 x 4, а матрица В имеет размерность 4 x 3, то результирующая матрица С имеет размерность 3 x 3. Поэтому следует внимательно следить, чтобы размерность матрицы С в точности соответствовала определению произведения двух матриц. Пусть матрица С будет размещаться в диапазонеF1:G3.

Нажать на панели инструментов Стандартная кнопкуВставка функции

В появившемся диалоговом окне Мастер функций в рабочем полеКатегории выбратьМатематические, а в рабочем полеФункция – имя функции МУМНОЖ. После этого нажать на кнопку ОК.

Появившееся диалоговое окно МУМНОЖ мышью отодвинуть в сторону от исходной матрицы и ввести диапазон первой матрицы А1:D3 в рабочее полеМассив1 (указателем мыши при нажатой левой кнопке), а диапазон матрицы В – А4:В7 ввести в рабочее полеМассив2 . После чего, не нажимая кнопку ОК, нажать сочетание клавишCTRL+SHIFT+ENTER(рис.3)

Рис.4

Если произведение матриц не появилось в заданном диапазоне А1:С3, то надо щелкнуть указателем мыши в строке формул и повторить нажатие клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне F1:G3 появится обратная матрица.