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

Основные задачи и виды регрессии

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

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

Основные задачи построения регрессионной модели заключаются в следующем:

1. Отбор значимых независимых переменных (Х1, Х2, …, Xk).
2. Выбор вида функции.
3. Построение оценок для коэффициентов.
4. Построение доверительных интервалов и функции регрессии.
5. Проверка значимости вычисленных оценок и построенного уравнения регрессии.

Регрессионный анализ бывает нескольких видов:

• парный (1 зависимая и 1 независимая переменные);
• множественный (несколько независимых переменных).

Уравнения регрессии бывает двух видов:

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

Инструкция построения модели

Чтобы выполнить заданное построение в Excel, необходимо следовать указаниям:

Для дальнейшего вычисления следует использоваться функцию «Линейн ()», указывая Значения Y, Значения Х, Конст и статистику. После этого определите множество точек на линии регрессии с помощью функции «Тенденция» — Значения Y, Значения Х, Новые значения, Конст. При помощи заданных параметров вычислите неизвестное значение коэффициентов, опираясь на заданные условия поставленной задачи.

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL

1. Создайте файл исходных данных в MS Excel (например, таблица 2)

2. Построение корреляционного поля

Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма . В появившемся диалоговом окне выберите тип диаграммы: Точечная ; вид: Точечная диаграмма , позволяющая сравнить пары значений (Рис. 22).

Рисунок 22 – Выбор типа диаграммы

Рисунок 23– Вид окна при выборе диапазона и рядов Рисунок 25 – Вид окна, шаг 4

2. В контекстном меню выбираем команду Добавить линию тренда.

3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.

Нажимаем ОК. Результат представлен на рисунке 27.

Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности

Аналогично строим корреляционное поле зависимости производительности труда от коэффициента сменности оборудования. (рисунок 28).

от коэффициента сменности оборудования

3. Построение корреляционной матрицы.

Для построения корреляционной матрицы в меню Сервис выбираем Анализ данных.

С помощью инструмента анализа данных Регрессия , помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки . Установите флажок Пакет анализа (Рисунок 29)

Рисунок 30 – Диалоговое окно Анализ данных

После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Результат расчетов представлен в таблице 4.

Что можно делать с матрицами

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

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

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

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

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

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

Там есть множество операций, но нам нужно найти радиокнопку «Транспонировать». После совершения этого действия нужно подтвердить его нажатием клавиши ОК.

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

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

1. Выделить набор ячеек, отведенных под транспонированную матрицу.
2. Нажать клавишу F2.
3. Нажать на горячие клавиши Ctrl + Shift + Enter.

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

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
существенно отличается от прежней версии. Там, вместо обычных иконок,
появилась лента. Мы не будем подробно разбирать отличия версий. Те, кому
это интересно могут прочитать

здесь.

Транспонирование матрицы в Excel (Эксель)

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

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

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

· Способ 1: использование оператора ТРАНСП

Оператор «ТРАНСП» является матричным оператором, поэтому результат работы оператора – массив данных, а не ячейка с результатом.Оператор имеет довольно простой синтаксис: «=ТРАНСП(массив)» с одним аргументом – самой исходной матрицей.

Попробуем сделать транспонирование матрицы на практике:

Создадим новый документ и занесем в его ячейки матрицу со значениями 4Х3. В другой произвольной ячейке создадим такой же диапазон, размером 4Х3, так как мы работаем уже с матричным оператором, и вставим функцию через «Мастер функций» – «Ссылки и массивы» – оператор «ТРАНСП».

В диалоговом окне укажем массив введенных данных и применяем оператор путем нажатия клавиш «Ctrl+Shift+Enter».

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

· Способ 2: транспонирование путем специальной вставки

Транспонирование матрицы можно произвести и без использования Мастера функций. Для начала скопируем исходную матрицу в буфер обмена либо через контекстное меню, либо сочетанием клавиш «Ctrl+C».

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

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

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

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

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

Как рассчитать коэффициент корреляции

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

Способ 1. Определение корреляции с помощью Мастера Функций

Функция КОРРЕЛ – один из самых простых методов, как можно реализовать поставленную задачу. В своем общем виде этот оператор имеет следующий вид: КОРРЕЛ(массив1;массив2). Как же ее ввести? Для этого нужно осуществлять следующие действия:

1. С помощью левой кнопки мыши выделяем ту ячейку, в которой будет находиться получившийся коэффициент корреляции. После этого находим слева от строки формул кнопку fx, которая откроет инструмент ввода функций. 
2. Далее выбираем категорию «Полный алфавитный перечень», в котором ищем функцию КОРРЕЛ. Как видно из названия категории, все названия функций располагаются в алфавитном порядке. 
3. Далее открывается окно ввода параметров функции. У нас два основных аргумента, каждый из которых являет собой массив данных, которые сравниваются между собой. В поле «Массив 1» указываем координаты первого диапазона, а в поле «Массив 2» – адрес второго диапазона. Для ввода данных массива, используемого для расчета, достаточно выделить нажать левой кнопкой мыши по соответствующему полю и выделить правильный диапазон. 
4. После того, как мы введем данные в аргументы, нажимаем кнопку «ОК», чем подтверждаем совершенные действия.

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

Способ 2. Вычисление корреляции с помощью пакета анализа

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

1. Нажимаем на кнопку «Файл», которая находится в левом верхнем углу сразу возле вкладки «Главная». 
2. После этого открываем раздел с настройками. 
3. В меню слева переходим в предпоследний пункт, озаглавленный, как «Надстройки». Делаем левый клик по соответствующей надписи. 
4. Открывается окно управления надстройками. Нам нужно переключить поле ввода, находящееся внизу, на пункт «Надстройки Excel» и нажать на «Перейти». Если это поле уже находится в таком положении, то не выполняем никаких изменений. 
5. Затем включаем пакет анализа в настройках. Для этого ставим соответствующую галочку и нажимаем на кнопку «ОК». 

Все, теперь наша надстройка включена. Теперь мы во вкладке «Данные» можем увидеть кнопку «Анализ данных». Если она появилась, то мы все сделали правильно. Нажимаем на нее. 

Появляется перечень с выбором разных способов анализа информации. Нам следует выбрать пункт «Корреляция» и нажать на «ОК». 

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

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

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

Поскольку мы оставили поле с данными о том, куда будут выводиться результаты, таким, каким оно было, мы переходим на новый лист. На нем можно найти коэффициент корреляции. Конечно, он такой же самый, как был в предыдущем методе – 0,97. Причина этого в том, что вычисления производятся одинаковые, исходные данные мы также не меняли. Просто разными методами, но не более. 

Таким образом, Эксель дает сразу два метода осуществления корреляционного анализа. Как вы уже понимаете, в результате вычислений итог получится таким же. Но каждый пользователь может выбрать тот метод расчета, который ему больше всего подходит.

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

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

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

Введем данные значения в ячейки А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.

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

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

1.1. Локализация

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

Рис. 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, в которой
разделитель дробной части – точка, а разделитель списков – запятая.
Русские имена функций приводятся для справки.

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

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

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

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

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

На показан как раз такой
случай: средние значения по всем столбцам матрицы Xc
равны нулю, а среднее по столбцу Yc отлично от нуля..

Пример 
 

Рис.37 Ошибка в регрессионных функциях Excel
2003

Ситуацию можно исправить, применяя функцию
TREND к
центрированным значениям отклика, с последующей коррекцией результата.
Для этого можно использовать формулу
=TREND(Yc-ym,
Xc)+ym, применение которой показано на том же рисунке.

Удивительно, но эта ошибка не была замечена
пользователями. Однако в новой версии 2007 она исправлена.  

Видеоурок

Учитель физики, информатики и ИКТ, МКОУ «СОШ», с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.

Рис. 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 существенно отличается от прежней версии. Там, вместо обычных иконок, появилась лента. Мы не будем подробно разбирать отличия версий. Те, кому это интересно могут прочитать .

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

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

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

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

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

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

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

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

5

Рассмотрим правило умножения двух квадратных матриц второго и третьего порядков. Пусть даны две матрицы

Произведением матрицы А на матрицу В называется матрица С= А В, элементы которой составляются следующим образом:

• Как видим, элемент матрицы-произведения, находящийся на пересечении i-й строки и k-го столбца, представляет собой сумму парных произведений элементов i-й строки первой матрицы на элементы k-го столбца второй матрицы.
• Например, элемент, стоящий во второй строке и первом столбце матрицы произведения АВ, равен сумме парных произведений элементов второй строки матрицы А на элементы первого столбца матрицы В.
• Это правило сохраняется для умножения квадратных матриц третьего и более высокого порядка, а также для умножения прямоугольных матриц, в которых число столбцов матрицы-множимого равно числу строк матрицы-множителя.
• Пример1

Пример2

Пример3.

С другой стороны, как установлено выше,

1. Следовательно, произведение двух матриц, вообще говоря, не подчиняется переместительному закону:
2. АВ ВА.
3. Можно проверить, что умножение матриц подчиняется сочетательному закону:

А(ВС) = (АВ)С.

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

Пример 4. Если

то

При умножении матриц второго порядка особое значение имеет квадратная матрица

При умножении любой квадратной матрицы

второго порядка на матрицу Е снова получается матрица А.

Действительно,

Аналогично EA =A.

Матрица Е называется единичной матрицей. Единичная матрица n-го порядка имеет вид

Если в матрице (1), обозначаемой буквой А, сделать все строки столбцами с тем же номером, то получим матрицу

• называемую транспонированной к матрице А.
• Понятие о матрице | Сложение матриц | Вычитание матриц и умножение матриц на число |
• Умножение матриц |   Контакты первого и второго порядков в эпидемиологии | Матрицы и сети |
• Главная

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

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра – товарный чек

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

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter

Пример 2. Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(   
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:

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

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.