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

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

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

Порядок применения формулы массива:

1. Выделить диапазон, где должен появиться результат действия формулы.
2. Ввести формулу (как и положено, со знака «=»).
3. Нажать сочетание кнопок Ctrl + Shift + Ввод.

В строке формул отобразится формула массива в фигурных скобках.

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

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

Элементы линейной алгебры

Из высшей
математики хорошо известно, что матрицы можно использовать для решения систем
линейных уравнений. Для этого в NumPyсуществует модуль linalg. Давайте
рассмотрим некоторые из его функций.

Предположим,
имеется квадратная матрица 3×3:

a = np.array((1, 2, 3), (1, 4, 9), (1, 8, 27))

Первым делом
вычислим ранг этой матрицы, чтобы быть уверенным, что она состоит из линейно
независимых строк и столбцов:

np.linalg.matrix_rank(a) # рангравен 3

Если ранг
матрицы совпадает с ее размерностью, значит, она способна описывать систему из
трех независимых линейных уравнений. В нашем случае, система уравнений будет
иметь вид:

Здесь  —
некие числа линейного уравнения. Например, возьмем их равными:

y = np.array(10, 20, 30)

Тогда корни
уравнения можно вычислить с помощью функции solve:

np.linalg.solve(a, y) # array()

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

Откуда получаем
решения :

На уровне пакета
NumPy это делается
так:

invA = np.linalg.inv(a) # вычисление обратной матрицы
invA @ y # вычисление корней

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

Функция	Описание
linalg.cholesky()	Разложение Холецкого
linalg.qr()	QR-разложение матрицы
linalg.svd()	Сингулярное (SVD) разложение матрицы
linalg.norm()	Норма матрицы или вектора
linalg.cond()	Число обусловленности матрицы
linalg.det()	Определитель (детерминант) матрицы
linalg.matrix_rank()	Вычисление ранга матрицы по алгоритму SVD
np.trace()	Сумма диагональных элементов массива
linalg.eig()	Вычисление собственных значений и правых собственных векторов
linalg.eigvals()	Вычисление собственных значений матрицы
linalg.solve()	Решение линейного матричного уравнения
linalg.tensorsolve()	Решение линейного тензорного уравнения
linalg.lstsq()	Решает задачу поиска наименьших квадратов для линейного матричного уравнения
linalg.inv()	Вычисление обратной матрицы
linalg.pinv()	Вычисление псевдообратной (Мура-Пенроуза) матрицы
linalg.tensorinv()	Вычисление обратного тензора (N-мерного массива)

Конечно, это не
все математические функции пакета NumPy. Полное
описание смотрите на сайте с официальной документацией:

Видео по теме

#1. Пакет numpy — установка и первое знакомство | NumPy уроки

#2. Основные типы данных. Создание массивов функцией array() | NumPy уроки

#3. Функции автозаполнения, создания матриц и числовых диапазонов | NumPy уроки

#4. Свойства и представления массивов, создание их копий | NumPy уроки

#5. Изменение формы массивов, добавление и удаление осей | NumPy уроки

#6. Объединение и разделение массивов | NumPy уроки

#7. Индексация, срезы, итерирование массивов | NumPy уроки

#8. Базовые математические операции над массивами | NumPy уроки

#9. Булевы операции и функции, значения inf и nan | NumPy уроки

#10. Базовые математические функции | NumPy уроки

#11. Произведение матриц и векторов, элементы линейной алгебры | NumPy уроки

#12. Множества (unique) и операции над ними | NumPy уроки

#13. Транслирование массивов | NumPy уроки

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: вещание

Заметили общую тему здесь? Каждый раз мы должны умножить каждый рядс каждым столбцомВполучитьС, Вы замечаете повторение? Мы умножаем один и тот же вектор строки враз! И мы повторяем этот процессраз!

Вместо этого давайте посмотрим, что это за волшебный кусок кода

for i in range(ar):        c = (a.unsqueeze(-1).expand_as(b)*b).sum(dim=0)

Как обычно, давайте повторим в Excel! Это сделает вещи действительно легкими. Давайте сделаем это поэтапно.

Шаг 1)Выберите i-й ряд, Мы выбираем строку 0 в качестве примера.

Выберите строку 0 из

Шаг 2)Возьмите егоТранспонирование

Возьмите транспонирование строки 0 из A

Шаг 3)Развернуть матрицу столбца какВ

Развернуть матрицу столбца как B

Шаг-4)Элемент мудрый умножить наВ

Умножить элемент на B

Шаг-5)Суммируйте по строкам, чтобы получить строку 0 C

Суммируйте по измерению боковой строки, чтобы получить строку 0 C

Полный процесс вещания для каждого рядавыглядит примерно так:

Вещание в целом

Я надеюсь, что теперь вы получитевещания!Итак, давайте посмотрим, как это выглядит в коде ..

def matmul3(a,b):    ar,ac = a.shape    br,bc = b.shape    assert ac==br    c = torch.zeros(ar,bc)    for i in range(ar):        c = (a.unsqueeze(-1).expand_as(b)*b).sum(dim=0)        print(f"i:{i}")        print(c)    return c

Вывод выглядит так:

i:0tensor(,        ,        ,        ])i:1tensor(,        ,        ,        ])i:2tensor(,        ,        ,        ])i:3tensor(,        ,        ,        ])

Это именно то, как мы ожидаем, что это будет так же, как нашиВерсия Excel!

Вот и все! Мы успешно рассмотрели три метода умножения матриц, включая вещание.

Шаг 2. Вводим единичную матрицу

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

Можно представить, что есть число 100 — это «сто в первой степени», 1001

И есть число 0,01 — это «сто в минус первой степени», 100-1

При перемножении этих двух чисел получится единица:1001 × 100-1 = 100 × 0,01 = 1. 

Вот такое, только в мире матриц. 

Зная свойства единичных и обратных матриц, делаем алгебраическое колдунство. Умножаем обе известные матрицы на обратную матрицу А-1. Неизвестную матрицу Х оставляем без изменений и переписываем уравнение: 

Добавляем единичную матрицу и упрощаем запись: 

После введения единичной матрицы мы нашли способ выражения неизвестной матрицы X через известные матрицы A и B. 

Смотрите, что произошло: раньше нам нужно было найти неизвестную матрицу. А теперь мы точно знаем, как её найти: нужно рассчитать обратную матрицу A-1 и умножить её на известную матрицу B. И то и другое — замороченные процедуры, но с точки зрения арифметики — просто. 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Умножение матрицы на вектор

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

Здесь очень важно правильно установить, чем является результат
умножения матрицы на вектор: матрицей, вектором-столбцом или вектором-строкой. Поскольку слева всегда строки, а справа — столбец, результат — вектор-столбец,
число элементов которого равно числу строк матрицы-сомножителя (см

приложение
3). В нашем примере выделены ячейки .

Шаг 2. На панели инструментов (под полосой меню Файл,
Правка, Вид и т.д.) щелкнуть левой кнопкой мыши по кнопке Вставка
функции. Откроется диалог Мастер функций — шаг 1 из 2, как показано
на рис. П4.2.

Рис. П4.2. Диалог Мастер функций — шаг 1 из 2

Шаг 3. В левом окне диалога Категория выбрать
Математические и щелкнуть по этой строке левой кнопкой мыши. В левом
окне найти и выделить . После этого нажать ОК.
Появится диалог МУМНОЖ, как это показано на рис. П4.3.

Рис. П4.3. Диалог

Шаг 4. В окне Массив 1 надо указать адрес матрицы,
— левого сомножителя. Это можно сделать, набрав адрес с клавиатуры, но лучше
сделать это с помощью мыши. Для этого надо установить курсор в окне Массив
1 и мышью выделить ячейки, занимаемые массивом, т. е. установить курсор
в левом верхнем углу матрицы и, нажимая левую кнопку мыши, протащить его
до правого нижнего угла массива. Если диалог заслоняет матрицу, его можно
сдвинуть, используя мышь: поставить на него курсор (но не на кнопку и не
на окно), нажать левую кнопку мыши и, не отпуская ее, перетащить диалог
в нужное место. Затем таким же способом надо в окно Массив 2 ввести
адрес второго сомножителя. После этого, нажимая Shift и Ctrl,
нажать Enter. В ячейках появится результат умножения
матрицы на вектор, как это показано на рис. П4.4. Аналогичным образом выполняется
умножение матрицы на матрицу.

Рис. П4.4. Умножение матрицы на вектор

Простые операции в Excel: сложение, вычитание

≡  14 Апрель 2016   ·  Рубрика: Могучий MS Excel   

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

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

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

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

Простые операции в алгебре

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

1. Весь лист разбит на ячейки, и к каждой ячейке можно обратится по адресу. Обычно адрес ячейки выглядит так «А1», «В7». Эти адреса означают, что ячейка находится в столбце А и строке 1 или столбце В и строке 7.
2. Если нужно записать любую формулу, то она записывается в ячейку.
3. В том случае если используются простые операции, тогда, для того, чтобы перейти в режим ввода формулы, нужно поставить знак «=».
4. По завершению ввода формулы нужно нажать «Ввод»
5. В ячейке будет виден результат выполнения формулы, а не сама формула.

Простая операция: сложение, вычитание

Теперь построим формулы сложения и вычитания в Excel.

Допустим: значение А хранится в ячейке «А1», значение В в ячейке В1. В ячейку С1 нужно записать результат суммы А1 и В1, а в ячейке С2 произвести вычитание В1 из А1.

• Формулы сложения и вычитания будут выглядеть так.
• =А1+В1
• =А1-В1

Вид формулы сложения

Вид формулы вычитания

Все четко видно на картинках, хочется только добавить  — не забывайте нажимать Ввод после окончания ввода любой формулы, не обязательно сложения или вычитания. И понятно, что для сложения используется символ «+», для вычитания символ «-».

Сложные варианты сложения: Функция СУММ()

Одним из сложных вариантов является прибавление процентов. Из-за сложности вопроса я написал отдельную статью.

КАК ПРИБАВИТЬ ПРОЦЕНТЫ В EXCEL С ПОМОЩЬЮ ФОРМУЛЫ

Прежде всего, нужно рассмотреть сложение ячеек по столбцу. Наилучший способ использование в формуле функции «СУММ». Этой функции передается диапазон ячеек и/или указываются, через точку с запятой, ячейки, по которым нужно провести суммирование.

Вид задания

Посмотрите на рисунок, условия задачи таковы: в ячейку F6 нужно записать сумму чисел записанных в диапазоне С1:С5, прибавить D1 и прибавить диапазон чисел записанный в D3:D5.

1. Становимся курсором в позицию F6 и нажимаем на кнопку «Вставить функцию».
2. В открывшемся окне «Мастер функций», есть поле поиск функции , в него мы записываем «Сумм»(без кавычек) и нажимаем кнопку «Ок»(в диалоговом окне)!

Мастер функций

После проделанных действий откроется другое диалоговое окно «Аргументы функции».

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

Обращаю внимание, что таких параметров может быть 255 и при ручном вводе разделять их нужно символом «;»

Аргументы функций

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

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

Функции для работы с матрицами в 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 – МОБР.

Выделяем первую ячейку пока пустого диапазона для обратной матрицы. Вводим формулу «=МОБР(A1:D4)» как функцию массива. Единственный аргумент – диапазон с исходной матрицей. Мы получили обратную матрицу в Excel:

Нахождение определителя матрицы

Это одно единственное число, которое находится для квадратной матрицы. Используемая функция – МОПРЕД.

Ставим курсор в любой ячейке открытого листа. Вводим формулу: =МОПРЕД(A1:D4).

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

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

Следует отметить, что умножать матрицы можно только в том случае, если количество столбцов первой матрицы А равно количеству строк второй матрицы В.

Рассмотрим матрицы А размерностью 3х4 и В размерностью 4х2. При умножении этих матриц получится матрица С размерностью 3х2.

Вычислим произведение этих матриц С=А*В с помощью встроенной функции =МУМНОЖ(). Для этого выделим диапазон L3:M5 — в нём будут располагаться элементы матрицы С, полученной в результате умножения. На вкладке Формулы выберем Вставить функцию.

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

В диалоговом окне Аргументы функции выберем диапазоны, содержащие матрицы А и В. Для этого напротив массива1 щёлкнем по красной стрелке.

Выделим диапазон, содержащий элементы матрицы А (имя диапазона появится в строке аргументов), и щелкнем по красной стрелке.

Для массива2 выполним те же действия. Щёлкнем по стрелке напротив массива2.

Выделим диапазон, содержащий элементы матрицы В, и щелкнем по красной стрелке.

В диалоговом окне рядом со строками ввода диапазонов матриц появятся элементы матриц, а внизу — элементы матрицы С. После ввода значений нажимаем на клавиатуре сочетание клавиш Shift+Ctrl и щелкаем левой кнопкой мыши по кнопке ОК.

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

Мы получим результат умножения матриц А и В.

Мы можем изменить значения ячеек матриц А и В, значения матрицы С поменяются автоматически.

1.2. Книга, лист и ячейка

Файл Excel с расширением XLS (XLSX в версии 2007) называется
(рабочей) книгой. Если запустить программу Excel, например, щелкнуть на
рабочем столе иконку
,
то откроется новая пустая книга. . 

Рис. 2 Новая книга Excel

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

Рис. 3
Открытие книги Excel через проводник

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

Рис.
4 Операции с листами

Стандартное имя листа – Sheet1, но ему можно дать любое имя, например
Data. На лист можно вставлять рисунки, графики и другие необходимые
объекты.

Каждый лист состоит из ячеек, образующих таблицу, размером 256
столбцов и 65536 строк (В версии 2007 – 16384 столбцов и1048576 строк).
Строки на листе обозначены числами: 1,2, 3…, а столбцы имеют буквенную
кодировку: A, B, …,Z,
AA, AB .., и т.д. до последнего столбца
IV (в 2007
– до XFD). Этот стиль адресации называется A1. Реже применяется
альтернативный стиль R1C1, в котором столбцы также нумеруются. Мы не
будем использовать этот стиль, а прочитать об этом можно

здесь

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

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

здесь.

1.4. Область

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

Рис.6 Область на листе

Для ссылки на область используется выражение, в котором адреса
верхней левой и правой нижней ячеек области соединяются двоеточием.
Например, B2:D10 или
$B$2:$D$10.

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

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

Подробности смотри

здесь.

Переопределение операции равенства

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

В общем делается это так

попробуем теперь запустить тест еще раз:

красота! =)

ИТОГО

Что мы должны по идее были вынести из этой статьи:

1. Можно создавать свои типы объектов как комбинацию простейших (чисел, массивов и т.д.)
2. Можно создавать свойства, которые будут просто возвращать значения (см. свойство Size)
3. Можно реализовать собственную индексатор, чтобы сделать использование своего типа более приятным (смотри public int this)
4. Можно определять свои операции сложения, умножения и т.п. опять же для бы сделать ваш тип юзерфрендли.
5. Операции, конструкторы и функции можно определять по несколько раз с разным набором аргументов, это зовется перегрузкой.
6. Чтобы тестировать свои типы придется переопределять функцию Equals.

2.7. Регрессия

Для построения используются
несколько стандартных функций листа.

TREND / ТЕНДЕНЦИЯ

Строит

y=b+m₁x₁+…+m_J x_J+e

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

Синтаксис 

TREND(known_y’s
)

Примечания 

• Вектор
  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₁x₁+…+m_J x_J+e

Синтаксис 

LINEST(known_y’s
)

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

m_J, …,
m₂, m₁
и b – оценки регрессионных
коэффициентов;

s_J, …,
s₂, s₁
и s_b
– стандартные ошибки для оценок регрессионных коэффициентов;

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

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

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

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

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

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

Примечания 

• LINEST – это
  очень плохо сконструированная функция, очень неудобная в
  практическом применении;

• Примечания,
  представленные в описании функции полностью применимы к
  функции LINEST.

Пример 
 

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

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

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

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

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

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

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

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

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

Виртуальные массивы очень полезны при вычислении всяческих
вспомогательных характеристик в анализе многомерных данных: остатков,
собственных значений, и т.п. Подробно об этом рассказывается в пособии
Расширение возможностей Chemometrics Add-In.