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

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

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

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

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

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

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

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

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

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. 

Видеоурок

Кратко об авторе:

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

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

Есть мнение?Оставьте комментарий

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Деление в экселе: как применяется, формула, примеры

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

Формула и примеры, как делить

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

Простые числа

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

На заметку! Можно одновременно использовать несколько операторов. Последовательность вычислений определена математическими законами: сначала умножение и деление, а потом сложение и вычитание.

Формула будет иметь следующий вид:

Ссылки на ячейки

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

Можно совмещать два предыдущих способа, если, например, знаменатель является неизменным численным значением.

Деление колонки на колонку

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

Решение задачи будет выглядеть следующим образом:

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

Как известно из математики, провести деление на ноль нельзя. В редакторе также невозможно провести такую операцию. При этом достаточно не только нуля в знаменателе, но и пустой ячейки. Программа выдаст ошибку #ДЕЛ/0.

Функция

Помимо использования пользовательской формулы, в excel есть специальная функция, которая выводит результат в виде целых чисел. Она называется ЧАСТНОЕ. Выражение имеет два известных оператора – числитель и знаменатель.

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

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

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

Множественный корреляционный анализ в excel. Как рассчитать коэффициент корреляции в Excel

Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.

Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.

3.4. Пример пользовательской функции

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

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

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

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

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

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

Способ 1: использование 3-х циклов FOR

Вот еще один отказ от ответственности, при реализации умножения матриц в Excel, мы не только поняли это, но и повторили первый метод Джереми, который использовал 3 цикла FOR. Вот как это выглядит в коде:

def matmul1(a,b):    ar,ac = a.shape    br,bc = b.shape    assert ac==br    c = torch.zeros(ar, bc)    for i in range(ar):        for j in range(bc):            for k in range(ac): #or br                c += a*b    return c

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

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

• Почему первые две петли ва также? Ну, как вы помните изПравило-2наша результирующая матрица будет иметь размеры, И из Excel мы увидели, что мы пересекаем каждый элементСследовательно, один за другим, чтобы пройтистроки истолбцы нам нужно 2 цикла ва также,
• Итак, почему третий цикл FOR в? Ну изПравило-1, мы знаем этотак что на самом деле не имеет значения, ставим ли мыилив третьем цикле. По сути, именно здесь отдельные элементы умножаются вместе и добавляются. Из нашего предыдущего примера Excel, дляэто где шагпроизойдет, и, наконец, мы перейдем к следующей коробке. Количество предметов, которые умножаются и складываются вместе, равно,

Это действительно так! Это метод-1 для вас! Вот песня, чтобы помнить Матричное Умножение. (Я узнал об этом первым из fast.ai, оригинальный автор неизвестен)

Надеюсь, теперь вы понимаете, как две матрицы умножаются друг на друга. Если не,Вотэто еще один учебник Хан Академии

Простые операции в 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 будет записана формула содержащая функцию суммирования, но пользователь увидит результат суммирования.

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

Примеры решений

Пример 1

Даны матрицы $ A = \begin{pmatrix} 2&3 \\ -1& 4 \end{pmatrix} $ и $ B = \begin{pmatrix} 1&-3 \\ 2&5 \end{pmatrix} $. Выполнить сложение матриц, а затем вычитание.

Решение

Первым делом проверяем матрицы на размерность. У матрицы $ A $ размерность $ 2 \times 2 $, у второй матрицы $ B $ размерность тоже $ 2 \times 2 $. Это значит, что с данными матрицами можно провести совместную операцию по сложению и вычитанию. Напомним, что для суммы нужно выполнить попарное сложение соответствующих элементов матриц $ A \text{ и } B $. $$ A + B = \begin{pmatrix} 2&3 \\ -1& 4 \end{pmatrix} + \begin{pmatrix} 1&-3 \\ 2&5 \end{pmatrix} = $$ $$ = \begin{pmatrix} 2 + 1 & 3 + (-3) \\ -1 + 2 & 4 + 5 \end{pmatrix} = \begin{pmatrix} 3 & 0 \\ 1 & 9 \end{pmatrix} $$ Аналогично сумме находим разность матриц с помощью замены знака «плюс» на «минус»: $$ A — B = \begin{pmatrix} 2&3 \\ -1& 4 \end{pmatrix} + \begin{pmatrix} 1&-3 \\ 2&5 \end{pmatrix} = $$ $$ = \begin{pmatrix} 2 — 1 & 3 — (-3) \\ -1 — 2 & 4 — 5 \end{pmatrix} = \begin{pmatrix} 1 & 6 \\ -3 & -1 \end{pmatrix} $$ Если не получается решить свою задачу, то присылайте её к нам. Мы предоставим подробное решение. Вы сможете ознакомиться с ходом вычисления и почерпнуть информацию. Это поможет своевременно получить зачёт у преподавателя!

Ответ

$$ A + B = \begin{pmatrix} 3 & 0 \\ 1 & 9 \end{pmatrix}; A — B = \begin{pmatrix} 1 & 6 \\ -3 & -1 \end{pmatrix} $$

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

Способ 1

Рассмотрим матрицу А
размерностью 3х4
. Умножим эту матрицу на число k
. При умножении матрицы на число получается матрица такой же размерности, что и исходная, при этом каждый элемент матрицы А
умножается на число k
.

Введем элементы матрицы в диапазон В3:Е5
, а число k
— в ячейку Н4
. В диапазоне К3:
N
5
вычислим матрицу В
, полученную при умножении матрицы А
на число k
: В=А*
k
. Для этого введем формулу =B3*$H$4
в ячейку K
3
, где В3
— элемент а 11
матрицы А
.

Примечание:

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

С помощью маркера автозаполнения копируем формулу ячейки К3
В
.

Таким образом, мы умножили матрицу А
в Excel и получим матрицу В
.

Для деления матрицы А
на число k в ячейку K
3
введем формулу =B3/$H$4
В
.

Способ 2

Этот способ отличается тем, что результат умножения/деления матрицы на число сам является массивом. В этом случае нельзя удалить элемент массива.

Для деления матрицы на число этим способом выделяем диапазон, в котором будет вычислен результат, вводим знак «=», выделяем диапазон, содержащий исходную матрицу А, нажимаем на клавиатуре знак умножить (*) и выделяем ячейку с числом k
Ctrl+
Shift+
Enter

Для выполнения деления в данном примере в диапазон вводим формулу =B3:E5/H4, т.е. знак «*» меняем на «/».

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

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

здесь.

Текст этой презентации

Расчет корреляционных зависимостей в MS Excel Подготовила учитель информатики Яценко Е.В.

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

Корреляционная матрица — это квадратная таблица, в кото­рой на пересечении соответствующих строк и столбцов находятся коэффициент корреляции между соответствующими параметрами.

В MS Excel для вычисления корреляционных матриц используется процедура Корреляция из пакета Анализ данных. Процедура позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами.

Для реализации процедуры необходимо: выполнить команду Данные — Анализ данных; 2. в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку ОК; 3. в появившемся диалоговом окне указать Входной интервал, то есть ввести ссыл­ку на ячейки, содержащие анализируемые данные. Входной интервал должен содержать не менее двух столбцов. 4. в разделе Группировка переключатель установить в соответствии с введенными данными (по столбцам или по строкам); 5. указать выходной интервал, то есть ввести ссылку на ячейку, начиная с которой будут показаны результаты анализа. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. Нажать кнопку ОК.

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

Имеются ежемесячные данные наблюдений за состоянием погоды и посещаемостью музеев и парков . Необходимо определить, существует ли взаимосвязь между состоянием погоды и посещаемостью музеев и парков.Число ясных дней Количество посетителей музея Количество посетителей парка 8 495 132 14 503 348 20 380 643 25 305 865 20 348 743 15 465 541

Решение. Для выполнения корреляционного анализа введите в диапазон A1:G3 исходные данные . Затем в меню Сервис выберите пункт Анализ данных и далее укажите строку Корреляция. В появившемся диалоговом окне укажите Входной интервал (А2:С7). Укажите, что данные рассматриваются по столбцам. Укажите выходной диапазон (Е1) и нажмите кнопку ОК.

Вывод: видно, что корреляция между состоянием погоды и посещаемостью музея равна -0,92, а между состоянием погоды и посещаемостью парка — 0,97, между посещаемостью парка и музея — 0,92. В результате анализа выявлены зависимости: сильная степень обратной линейной взаимосвязи между посещаемостью музея и количеством солнечных дней ; очень сильная прямая связь между посещаемостью парка и состоянием погоды; сильная обратная взаимосвязь между посещаемостью музея и парка .

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

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

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

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

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

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

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

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

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

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

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

Множественный коэффициент корреляции в Excel (Эксель)

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

Коэффициент корреляции определяется некоторыми данными. Если уровень показателя составляет от 0 до 0.3, то в таком случае связи нет. Если показатель составляет от 0.3 до 0.5 — это слабая связь. Если показатель доходит до 0.7, то связь средняя. Высокой можно назвать когда показатель достигает отметки 0.7-0.9. Если показатель составляет 1 — это наиболее сильная связь.

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

Далее откроется новое окно. В нём нужно выбрать «Надстройки» и в поле управления параметрами выбрать среди элементов списка «Надстройки Excel»После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». После этого нажимаем «Перейти».

Далее откроется новое окно надстроек. Находим в списке «Пакет анализа» и ставим галочку. После этого подтверждаем действие. И пакет анализа данных будет подключён для документа Excel.

После этих действий можно начать работу. Создана таблица с данными и на её примере сделаем нахождение множественного коэффициента корреляции.Для начала откроем раздел «Данные» и среди инструментария выбираем «Анализ данных».

Откроется специальное окно с инструментами для анализа. Выбираем «Корреляция» и подтверждаем действие.

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

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

Находим значение определителя

Чтобы выполнить это действие, необходимо воспользоваться функцией МОПРЕД. Как именно это делается, рассмотрим на примере:

1. Записываем квадратную матрицу в любом свободном месте.
2. Выбираем свободную ячейку, после чего находим напротив строки формул кнопку «fx» («Вставить функцию») и кликаем по ней ЛКМ.

1

1. Должно открыться окно, где в строке «Категория:» останавливаемся на «Математические», а ниже выбираем функцию МОПРЕД. Соглашаемся с выполненными действиями кликнув по кнопке «ОК».
2. Далее в открывшемся окне заполняем координаты массива.

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

2

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

3

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

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

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

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

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

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

Пример 
 

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

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

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