Курс: Работа с Excel

Лекция: Алгоритм базового анализа данных опроса

Базовый анализ статистических данных можно проводить и в распространенной системе MS Excel или ее бесплатных аналогах, таких как Open Office или online редакторах типа Google Docs.

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

Результаты опроса в Excel

Для начала найдем распределение ответов при помощи функций СУММ, ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИи СЧЕТЕСЛИМН.

Описание функций Excel

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

Подсчет мужчин в Excel

В приведенном примере только два дихотомических параметра: пол и замужество. Их можно проверить на зависимость. Для этого подсчитаем "Коэффициент контингенции Пирсона"

\[r_a = \frac{ad-bc}{\sqrt{(a+b)(c+d)(a+c)(b+d)}}, \]

где \(a\), \(b\), \(c\), \(d\) - элементы четырехпольной корреляционной таблицы, в которой представлены частоты или количество совместных событий.

 Событие 1 
Событие 2\(a\)\(b\)\(a+b\)
\(c\)\(d\)\(c+d\)
 \(a+c\)\(b+d\) 

Например, если за "событие 1" принять пол (м,ж), а за "событие 2" - замужество (да, нет), то \(a\) - это количество женатых мужчин, \(b\) - количество замужних женщин, \(c\) - количество неженатых мужчин, \(d\) - количество незамужних женщин.

Коэффициент контингенции в Excel

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

Коэффициент0,1 - 0,30,3 - 0,50,5 - 0,70,7 - 0,9 0,9 - 0,99
Характеристика зависимостиСлабаяУмереннаяЗаметная ВысокаяВесьма высокая

Найдем ошибку коэффициента корреляции по формуле:

\[m_{r_a} = \frac{1-|r_a|}{\sqrt{n-1}}\]

Она составила 0,100318. Ниже приведена таблица с исходными данными:

НомерВозрастПолОператорЖенат/За мужемГрафик работы
120мДругоеДаСвободный
222жМегафонДаНет
332мМегафонНетПлавающий
435мМегафонДаНеделя
521мМегафонНетНеделя
640жТеле2НетНеделя
737жМегафонНетПлавающий
823мТеле2НетНет
933жТеле2НетСвободный
1025мБилайнНетСвободный
1123мБилайнНетНеделя
1235мБилайнДаНеделя
1334жМТСДаНет
1436жТеле2ДаНеделя
1519мБилайнДаНет
1623жМТСНетСвободный
1727жБилайнДаПлавающий
1838жБилайнНетНеделя
1941жТеле2ДаНеделя
2033жМТСДаНеделя
2129жБилайнНетСвободный
2220мМТСДаПлавающий
2324мТеле2НетПлавающий
2428жБилайнНетНеделя
2531мМТСНетНеделя
2637жБилайнДаНет
2735мБилайнНетСвободный
2834жТеле2ДаНеделя
2933мМТСНетНеделя
3032жБилайнДаСвободный
3140жТеле2НетНеделя
3225мМегафонДаПлавающий
3328жМегафонДаПлавающий
3420мМТСНетНет
3523жБилайнДаПлавающий
3637мМегафонНетНеделя
3741жТеле2НетНеделя
3840жМегафонДаПлавающий
3931жМегафонДаСвободный
4025жДругоеДаНет
4131жМТСДаНеделя
4239жТеле2ДаНеделя
4319мМегафонДаНет
4421мТеле2НетСвободный
4534мМегафонДаНеделя
4622жМегафонДаГрафик
4728жБилайнДаГрафик
4829жМТСДаГрафик
4938жМегафонДаГрафик
5039жТеле2ДаПлавающий