Статистический анализ данных в Microsoft Excel

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

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

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

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

Степень зависимости двух величин определяется коэффициентом корреляции. Если коэффициент корреляции получен со знаком плюс, будет прямая связь, со знаком минус – обратная. Коэффициент изменяется в пределах от –1 до +1. Чем ближе его значение к единице, тем теснее связь, больше зависимость.

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

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

  1. На основании данных таблицы 1 сформировать ЭТ, начиная ввод данных с ячейки B2.
№ п/ п Общая земельная площадь, га

Площадь несельскохозяйственных угодий, га

Удельный вес несельскохозяйственных угодий, %

Площадь посева, га

Урожайность, ц/га

Валовый сбор, ц
Зерновых Многолетних трав Зерновых Многолетних трав Зерновых Многолетних трав
1 2 3 4 5 6 7 8 9 10
1 2550 179 1300 400 25 30
2 2600 195 1200 400 30 28
3 2600 195 1300 420 28 29
4 2550 212 1250 410 28 31
5 2680 231 1300 410 30 27
6 2700 232 1300 430 29 27
7 2820 282 1380 435 28 28
8 2880 288 1420 425 27 25
9 2900 290 1400 500 27 29
10 2940 315 1450 480 28 24
11 3000 345 1480 490 26 26
12 3000 351 1500 520 29 25
13 3220 377 1620 540 25 27
14 3300 393 1550 540 25 24
15 3300 396 1600 500 25 22
16 3350 402 1500 480 24 26
17 3400 439 1850 500 26 23
18 3600 464 1900 520 23 25
19 3700 503 1900 520 23 27
20 4150 573 2050 530 24 24
21 4200 580 2150 550 24 22
22 4600 644 2150 540 22 25
23 4920 689 2200 560 23 23
24 5010 752 2050 570 23 26
25 5100 791 2100 600 25 27
  1. Выполнить необходимые расчеты для следующих показателей:

Удельный вес несельскохозяйственных угодий, %=[Площадь несельскохозяйственных угодий]/[Общая земельная площадь].

Валовый сбор = [Урожайность]*[Площадь посева].

Группировочный анализ

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

  2. Определить максимальное и минимальное значения этого столбца.

  3. Определить число групп = 5(Определяется произвольно, исходя из условия конкретной задачи).

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

Размах=([Максимум]-[Минимум])/[Число групп].

  1. Определить границы каждой группы следующим образом: Граница первой группы = Минимум

Граница второй группы = Граница первой группы+Размах и т.д.

  1. Добавить к таблице еще один столбец (если вы правильно ввели данные, то это должен быть столбец L) (№11), поставить курсор в ячейку L5 и воспользоваться функцией ВПР.
  2. Заполнить вводные поля диалогового окна функции ВПР. Искомое значение – ячейка D5; Таблица – P12:Q16; Номер столбца – 2; ОК

Проанализировать результаты проведенной группировки данных.

Регрессионный анализ

Вычислить коэффициент парной корреляции между площадью зерновых и урожайностью зерновых по формуле: \[R_{xy}=\frac{\left<xy\right>-\left<x\right>\left<y\right>}{\sigma_x\sigma_y}\]

где

\(\left<xy\right>\) – среднее значение произведения площади и урожайности, т.е. среднее значение валового сбора;

\(\left<x\right>\) – среднее значение площади;

\(\left<y\right>\) – среднее значение урожайности;

\(σ_x\)– среднее квадратическое отклонение по площади;

\(σ_y\)– среднее квадратическое отклонение по урожайности.

Для выполнения расчета коэффициента корреляции провести следующие вычисления в таблице:

1. Среднее значение произведения площади и урожайности, среднее значение площади и среднее значение урожайности рассчитать с помощью встроенной функции СРЗНАЧ и поместить их внизу таблицы в соответствующих столбцах.

2. Затем рассчитать среднеквадратичные отклонения \[\sigma_x=\sqrt{\frac{1}{n}\sum(x-\left< x \right>)^2}\]

\[\sigma_y=\sqrt{\frac{1}{n}\sum(y-\left< y \right>)^2}\]

3. Выполнить проверку правильности расчета коэффициента корреляции, используя встроенную функцию КОРРЕЛ. Результат расчета значения коэффициента корреляции с использованием этой функции поместить в ячейку Q23.

  1. Построить график зависимости изменения урожайности от площади посева зерновых, используя уравнение \(y_p=a x+b\), где \(a=R_{xy}\frac{σ_y}{σ_x}\), \(b=\left<y\right>-a\left<x\right>\).

Проанализировать получившиеся результаты – табличные и графические.