Статистический анализ в Excel

Статистический анализ в Excel

 

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

 

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

 

ВОПРОСЫ ДЛЯ  САМОКОНТРОЛЯ

 

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

Если в Microsoft Excel в меню Сервис отсутствует команда Анализ данных, то необходимо установить статистический пакет анализа данных.

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

В меню Сервис выберите команду Надстройки. Если в списке надстроек нет пакета анализа данных, нажмите кнопку Обзор и укажите диск, папку и имя файла для надстройки пакет анализа, Analys32.xll (как правило, папка Library\Analysis) или запустите программу Setup, чтобы установить эту надстройку.

Установите флажок Пакет анализа, выберите кнопку OK.

Чтобы запустить пакет анализа:

1. В меню Сервис выберите команду Анализ данных.
2. В списке Инструменты анализа выберите нужную строку.

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

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

Регрессия – зависимость среднего значения какой-либо величины от некоторой другой величины или от нескольких величин. В отличие от чисто функциональной зависимости y=f(x), когда каждому значению независимой переменной x соответствует одно определённое значение величины y, при регрессионной связи одному и тому же значению x могут соответствовать в зависимости от случая различные значения величины y. Если при каждом значении x=x_i наблюдается n_i значений y_i1…y_in1 величины y, то зависимость средних арифметических =(y_i1+…+y_in1)/n_i от x=x_i и является регрессией в статистическом понимании этого термина.

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

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

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

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

	Столбец 1	Столбец 2
Столбец 1	1
Столбец 2	0,046723	1

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

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

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

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

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

	Столбец 1	Столбец 2
Столбец 1	1
Столбец 2	0,046723	1

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

 

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

В разделе Параметры вывода установите переключатель Новая рабочая книга и нажмите ОК. Откроется новая книга, которую можно сохранить под любым нужным именем. Выберете в меню Файл пункт Сохранить как… и сохраните книгу под именем «Результаты».

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

Если установлен флажок Стандартные погрешности, то выводится состоящий из двух столбцов диапазон, содержащий значения стандартных погрешностей в правом столбце. Если предшествующих данных недостаточно для построения прогноза, Microsoft Excel возвратит ошибочное значение #Н/Д.

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

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

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

№ заводов	Стоимость ОПФ, млрд. руб. x	Выработано  сахара, тыс.руб., y
1	4,99	130	#Н/Д
2	6,93	158	#Н/Д
3	6,9	161	6,273333333
4	8,09	120	7,306666667
5	4,88	171	6,623333333
6	7,05	101	6,673333333
7	7,88	188	6,603333333
8	4,85	244	6,593333333
9	4,35	134	5,693333333
10	3,43	122	4,21
11	6,06	169	4,613333333
12	6,11	165	5,2
13	9,79	139	7,32
14	3,85	133	6,583333333
15	6,69	172	6,776666667
16	6,63	145	5,723333333
17	6,47	142	6,596666667
18	6,08	144	6,393333333
19	8,11	109	6,886666667
20	9,47	206	7,886666667

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

Цели регрессионного анализа

1. Определение степени детерминированности  вариации критериальной (зависимой)  переменной предикторами (независимыми  переменными).

2. Предсказание значения зависимой  переменной с помощью независимой(-ых).

3. Определение вклада отдельных  независимых переменных в вариацию зависимой.

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

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

Регрессионный (линейный) анализ – статистический метод исследования зависимости между зависимой переменной Y и одной или несколькими независимыми переменными X₁,X₂,...,X_p. Независимые переменные иначе называют регрессорами или предикторами, а зависимые переменные – критериальными. Терминология зависимых и независимых переменных отражает лишь математическую зависимость переменных, а не причинно-следственные отношения.

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

 

 

 

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

 

 

 

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

• уровень надежности : 99%
• вывести результаты в новую книгу «Результаты регрессионного анализа»

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

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

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

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

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

При анализе влияния стоимости  ОПФ на выработку сахара получим:

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

 

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

 

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

 

ВОПРОСЫ ДЛЯ  САМОКОНТРОЛЯ

 

1. Какие типы таблиц данных можно создать с помощью команды Данные/ Работа с данными/ Анализ «что если» /Таблица данных и в чем их отличие?

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

2. Предприятие оценивает возможность покупки станка. Имеются предложения по цене 200, 210, 220, 245 и 250 тыс. руб. Срок службы всех станков 10 лет, остаточная стоимость 20 тыс. руб. Определите суммы амортизационных отчислений для станков с различной стоимостью при использовании метода равномерного списания.

Формула для вычисления ежегодного начисления на износ равно

(S–P) / n, где

S – цена станка, Р – остаточная  стоимость, n – срок службы станка.

3. У вас есть возможность вложить 10 000 руб. или 20 000 руб. в банк на 5 лет. Каждый год вы планируете помещать в банк 1000 руб. Определите накопленную сумму в конце пятого года при ставках банковского процента 5, 10, 15, 20, 25, 30 и 35%, если выплаты в конце года не производятся.

 

 

 

4. Создайте таблицу данных, которая вычисляет ежегодные выплаты по ссуде под закладную в 100 000 руб. для процентной ставки 5, 10, 15, 20 и 25 процента при сроках 10,15, 20 и  25 лет.

 

 

 

5. Создайте пессимистический, оптимистический и средний вариант сценариев при следующих условиях. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. В «худшем» варианте сценария цена одной единицы прогнозируется в размере 1 000, 1 050, 1 200, 1 300 руб. для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 1100, 1200, 1300, 1400 руб. для I, II, III и IV кварталов соответственно. Для среднего варианта сценария цена составит 1050, 1100, 1250 и 1350 руб. для I, II, III и IV кварталов соответственно. Объем реализации составит 1000, 1100, 1200, 1400 ед. продукции для  I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 400 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 25 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 200 тыс. руб. ежеквартально и переменных затрат 15 руб. с каждой ед. продукции. Ставка налога на прибыль составляет 35%.