Лабораторная работа 7 Excel

ЛАБОРАТОРНАЯ РАБОТА № 8

Тема: Анализ данных в MS Excel

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

Краткий комментарий

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

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

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

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

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

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

Уравнение для прямо пропорциональной парной связи между признаками имеет вид:

Y = A + BX,

где Y- зависимая переменная;

X – независимая переменная;

A и B – параметры уравнения связи.

Коэффициент B показывает, на сколько должна измениться зависимая переменная Y при изменении на 1 единицу независимой переменной величины X.

Уравнение для прямо пропорциональной множественной связи между признаками имеет вид:

Y = A + B₁X₁ + … + B_nX_n,

где X₁, …, X_n – независимые переменные;

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

Коэффициент корреляции до ± 0,25 – плохая связь, до ± 0,6 - средняя, до ± 0,8 – высокая, до ± 1,0 – полная связь.

Практическое задание

1. Загрузить MS Excel 2007.

2. На Листе 1 рабочей книги на основании данных, представленных на рис. 8.1, создать таблицу. Установить при этом достаточную ширину столбцов и высоту строк, в «шапке» таблицы выполнить центрирование заголовков. Оформить таблицу линиями.

Рис.8. 1 Зависимость себестоимости яиц от расхода кормов, себестоимости кормов и трудоемкости

3. Получить зависимость себестоимости 1000 яиц от расхода кормов на 1000 яиц. ц корм.ед. Проанализировать полученные результаты. Записать уравнение прямолинейной корреляционной зависимости.

Для выполнения корреляционного анализа автоматически необходимо выбрать команду Анализ данных на вкладке Данные в группе Анализ (Рис.8.2)

Рис.8. 2 Команда Анализ данных

При этом откроется диалоговое окно «Анализ данных» (Рис.8. 3)

Рис.8. 3 Диалоговое окно Анализ данных

Выбрав соответствующий инструмент анализа: Регрессия, открывается новое диалоговое окно «Регрессия» (Рис. 8.4).

Рис.8. 4 Диалоговое окно Регрессия

Таким образом, под таблицей разместится таблица регрессий.

4. Дополнить таблицу колонкой «Расчетная себестоимость в зависимости от расхода кормов». По полученному уравнению рассчитать себестоимость.

5. Получить зависимость себестоимости 1000 яиц от себестоимости 1 ц корм.ед., тыс.руб. Проанализировать полученные результаты. Записать уравнение парной корреляционной зависимости. Таблицу регрессий разместить ниже.

6. Дополнить таблицу колонкой «Расчетная себестоимость в зависимости от себестоимости кормов». По полученному уравнению рассчитать себестоимость.

7. Получить зависимость себестоимости 1000 яиц от затрат труда на 1000 яиц, чел./час. Проанализировать полученные результаты. Записать уравнение парной корреляционной зависимости.

8. Дополнить таблицу колонкой «Расчетная себестоимость в зависимости от затрат труда». По полученному уравнению рассчитать себестоимость.

9. Получить зависимость себестоимости 1000 яиц одновременно по трем факторам: от расхода кормов на 1000 яиц. ц корм.ед., от себестоимости 1 ц корм.ед., тыс.руб., от затрат труда на 1000 яиц, чел./час. Проанализировать полученные результаты. Записать уравнение множественной корреляционной зависимости.

10. Дополнить таблицу колонкой «Расчетная себестоимость в зависимости от расхода кормов, себестоимости кормов и затрат труда». По полученному уравнению рассчитать себестоимость.

11. Сохранить документ в фамильной папке под именем лр8зад1.

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

1. Как называется связь между двумя признаками?

2. Что отражает положительная связь между признаками?

3. Что показывает отрицательная связь между изучаемыми признаками?

4. Чем выражается линейная корреляционная зависимость?

5. Как выглядит уравнение прямо пропорциональной парной линейной зависимости?

6. Что показывает коэффициент корреляции, и в каких пределах он может варьировать?

7. Как выполнить корреляционный анализ данных с помощью ЭВМ на платформе Excel?

8. Как выполнить регрессионный анализ данных автоматически?