2. 6. Линейный регрессионный анализ

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

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

Входной интервал X. Введите ссылку на диапазон независимых данных. Microsoft Excel располагает независимые переменные этого диапазона слева направо в порядке возрастания. Максимальное число входных диапазонов равно 16.

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

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

Константа – ноль. Установите флажок, чтобы линия регрессии прошла через начало координат.

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

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

Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

Остатки. Установите флажок, чтобы включить остатки в выходной диапазон.

Стандартизированные остатки. Установите флажок, чтобы включить стандартизированные остатки в выходной диапазон.

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

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

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

При анализе влияния курса доллара США (Х) на курс ЕВРО (Y) получим:

!!! Вызовите метод анализа «регрессия», ознакомьтесь с методами работы с функциями метода

!!! Задайте входные и выходные интервалы. Получите результаты, показанные выше

!!! Ознакомьтесь с примерами работы с пакетами анализа

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

2. Вопросы для самоконтроля

1. Продемонстрируйте, как устанавливается и запускается пакет статистического анализа в MS Excel

Исходные данные для работы.

№ заводов	Стоимость ОПФ, млрд. руб. x	Выработано сахара, тыс.руб., y
1	4,99	130
2	6,93	158
3	6,90	161
4	8,09	120
5	4,88	171
6	7,05	101
7	7,88	188
8	4,85	244
9	4,35	134
10	3,43	122
11	6,06	169
12	6,11	165
13	9,79	139
14	3,85	133
15	6,69	172
16	6,63	145
17	6,47	142
18	6,08	144
19	8,11	109
20	9,47	206

2. Что такое корреляция, регрессия?

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

4. Что дает использование флажка «Метки в первой строке»?

5. Укажите, что создание итогов работы пакета анализа будет происходить на новом листе «Результаты».

6. Сформируйте итоговые данные.

7. Укажите, что создание итогов работы пакета анализа будет происходить в новой книге «Результаты».

8. Сформируйте итоговые данные.

9. Что означается «#Н/Д» в выходном диапазоне расчета Скользящего среднего?

10. Выведите график для скользящего среднего.

11. На новом листе сформируйте результаты работы без вывода графика.

12. Цель линейно регрессионного анализа?

13. Что такое линейный регрессионный анализ? Почему он назван линейным?

14. Как сказывается на анализе наличие «экстремальных» значений функции?

15. Что нужно сделать с «экстремальными» значениями, если они встречаются в исходных данных?

16. Проведите регрессионный анализ предложенных данных, при следующих условиях:

уровень надежности : 99%

вывести результаты в новую книгу «Результаты регрессионного анализа»

Отобразить графики «остатков» и «нормальной вероятности»

Анализ «что-если»

1. Цель и задачи лабораторной работы.

Приобретение навыков проведения анализа «что-если» в MS Excel.

2. Теоретические сведения.

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

2. 1. Таблицы данных

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

!!! Ознакомьтесь с работой команды Таблица данных.

Таблицы данных для одной переменной

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

Ч тобы создать такую таблицу, выполните следующие действия:

1. На новом рабочем листе введите интересующие вас процентные ставки. Для этого примера введите 6, 6,5, 7, 7,5, 8 и 8,5 процентов в ячейки ВЗ:В8. (Мы называем этот диапазон входным диапазоном, так как он содержит входные значения, которые мы хотим проверить.)

2. Затем введите формулу, которая использует входную переменную. В данном случае введите в ячейку С2 формулу:

=ПЛТ(А2/12;360;200000)

где А2/12 — месячная процентная ставка, 360 — срок ссуды в месяцах и 200000 - размер ссуды. Обратите внимание, что эта формула ссылается на ячейку А2, которая в данный момент пустая. (При расчете числовых формул Ms Excel присваивает пустым ячейкам значение 0.) Как вы можете заметить, поскольку А2 пустая, то функция возвращает величину ежемесячных выплат, необходимую для погашения ссуды при нулевой процентной ставке. Ячейка А2 является только меткой, через которую Excel будет подставлять значения из входного диапазона. На самом деле Excel не изменяет хранимое значение в этой ячейке, поэтому такой меткой может быть любая ячейка рабочего листа вне диапазона таблицы данных.

3. Выделите диапазон таблицы данных — минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. В данном случае выделите диапазон В2:С8.

4 . Выполните команду Данные/ Таблица подстановки. В окне диалога Таблица подстановки задайте местонахождение входной ячейки в поле Подставлять значения по строкам в или в поле Подставлять значения по столбцам в. Входная ячейка — это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае, А2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в поле Подставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте поле Подставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $А$2 в поле Подставлять значения по строкам в.

5. Нажмите кнопку ОК. Excel выведет значения формулы для каждого входного значения в ячейках диапазона таблицы данных. В нашем примере Excel выведет шесть результатов в диапазоне СЗ:С8. При создании этой таблицы данных Excel ввел формулу массива {=ТАБЛИЦА(;А2)} в каждую ячейку в диапазоне СЗ:С8 (диапазон результатов). В нашей таблице формула ТАБЛИЦА вычисляет значения функции ПЛТ для каждой процентной ставки в столбце В. Например, формула в ячейке С5 вычисляет размер выплаты при ставке, равной 7 процентам.

!!! Последовательно повторите все операции, описанные выше.

Функция ТАБЛИЦА, используемая в формуле, имеет следующий синтаксис:

=ТАБЛИЦА(входная ячейка для строки ;входная ячейка для столбца)

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

П осле построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества резуль­татов. Например, предположим, что для покупки дома вы решили занять только $185 000. Если вы измените формулу в ячейке С2 на =ПЛТ(А2/12;360; 185000) значения в выходном диапазоне изменятся.

!!! Создайте описанные таблицы и сравните полученные результаты