Курс: Работа с Excel
Лекция: Алгоритм базового анализа данных опроса
Базовый анализ статистических данных можно проводить и в распространенной системе MS Excel или ее бесплатных аналогах, таких как Open Office или online редакторах типа Google Docs.
Первоначально результаты опросов должны быть оцифрованы в виде таблицы в столбцах которой представлены ответы на вопросы, а в строках результаты анкет. Такой вариант представления данных соответсвует реляционным базам данных.
Для начала найдем распределение ответов при помощи функций СУММ, ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИи СЧЕТЕСЛИМН.
Описание функций Excel
- СУММ(Диапазон) - функция для нахождения суммы значений указанного диапазона. Пример: =СУММ(B2:B25).
- ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще). У функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно. Пример: =ЕСЛИ(E2="Да";1;2).
- СУММЕСЛИ(Диапазон;Условие) - функция для нахождения суммы значений указанного диапазона,
соответствующие указанному условию.
Пример: =СУММЕСЛИ(B2:B25;">35").
Есть возможность разделения диапазонов сравнения и суммирования, для этого диапазон суммирования указывается в качестве третьего параметра: =СУММЕСЛИ(D2:D5; "Теле2"; B2:B5) суммирует только те значения из диапазона B2:B5, для которых соответствующие значения из диапазона D2:D5 равны "Теле2". - Функция СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1) похожа на второй вариант
использования функции СУММЕСЛИ(диапазон_условия;Условие;диапазон_суммирования), только диапазон
суммирования и диапазон условия переставлены местами.
Кроме этого есть возможность указания нескольких диапазонов условий СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2; ...). Можно ввести до 127 пар диапазонов и условий.
Пример: =СУММЕСЛИМН(B2:B9; D2:D9; "=М*"; C2:C9; "м") найдет сумму возрастов мужчин, оператор которых начинатеся на букву М. - Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН аналогичны функциям СУММЕСЛИ и
СУММЕСЛИМН, только они ничего не суммируют, а просто считают количество ячеек, удовлетворяющих
условиям.
Примеры: =СЧЁТЕСЛИ(C2:C10;$C$2) считает число мужчин.
=СЧЁТЕСЛИ(F2:F5;"<>"&$F$10) считает число анкет с графиком не равным "Свободный". Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке $F$10, в результате чего получается формула =СЧЁТЕСЛИ(F2:F5;"<>Свободный").
На представленном рисунке приведен пример подсчета мужских и женских анкет. Аналогично можно проанализировать и другие дихотомические (т.е. принимающие одно из двух значений) параметры.
В приведенном примере только два дихотомических параметра: пол и замужество. Их можно проверить на зависимость. Для этого подсчитаем "Коэффициент контингенции Пирсона"
\[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\) - количество незамужних женщин.
В рассматриваемом примере Коээффициент контингенции Пирсона получился равным -0,29778. Если взглянуть на шкалу Чедокка, то видим, что имеется слабая обратная зависимость между параметрами.
Коэффициент | 0,1 - 0,3 | 0,3 - 0,5 | 0,5 - 0,7 | 0,7 - 0,9 | 0,9 - 0,99 |
---|---|---|---|---|---|
Характеристика зависимости | Слабая | Умеренная | Заметная | Высокая | Весьма высокая |
Найдем ошибку коэффициента корреляции по формуле:
\[m_{r_a} = \frac{1-|r_a|}{\sqrt{n-1}}\]Она составила 0,100318. Ниже приведена таблица с исходными данными:
Номер | Возраст | Пол | Оператор | Женат/За мужем | График работы |
---|---|---|---|---|---|
1 | 20 | м | Другое | Да | Свободный |
2 | 22 | ж | Мегафон | Да | Нет |
3 | 32 | м | Мегафон | Нет | Плавающий |
4 | 35 | м | Мегафон | Да | Неделя |
5 | 21 | м | Мегафон | Нет | Неделя |
6 | 40 | ж | Теле2 | Нет | Неделя |
7 | 37 | ж | Мегафон | Нет | Плавающий |
8 | 23 | м | Теле2 | Нет | Нет |
9 | 33 | ж | Теле2 | Нет | Свободный |
10 | 25 | м | Билайн | Нет | Свободный |
11 | 23 | м | Билайн | Нет | Неделя |
12 | 35 | м | Билайн | Да | Неделя |
13 | 34 | ж | МТС | Да | Нет |
14 | 36 | ж | Теле2 | Да | Неделя |
15 | 19 | м | Билайн | Да | Нет |
16 | 23 | ж | МТС | Нет | Свободный |
17 | 27 | ж | Билайн | Да | Плавающий |
18 | 38 | ж | Билайн | Нет | Неделя |
19 | 41 | ж | Теле2 | Да | Неделя |
20 | 33 | ж | МТС | Да | Неделя |
21 | 29 | ж | Билайн | Нет | Свободный |
22 | 20 | м | МТС | Да | Плавающий |
23 | 24 | м | Теле2 | Нет | Плавающий |
24 | 28 | ж | Билайн | Нет | Неделя |
25 | 31 | м | МТС | Нет | Неделя |
26 | 37 | ж | Билайн | Да | Нет |
27 | 35 | м | Билайн | Нет | Свободный |
28 | 34 | ж | Теле2 | Да | Неделя |
29 | 33 | м | МТС | Нет | Неделя |
30 | 32 | ж | Билайн | Да | Свободный |
31 | 40 | ж | Теле2 | Нет | Неделя |
32 | 25 | м | Мегафон | Да | Плавающий |
33 | 28 | ж | Мегафон | Да | Плавающий |
34 | 20 | м | МТС | Нет | Нет |
35 | 23 | ж | Билайн | Да | Плавающий |
36 | 37 | м | Мегафон | Нет | Неделя |
37 | 41 | ж | Теле2 | Нет | Неделя |
38 | 40 | ж | Мегафон | Да | Плавающий |
39 | 31 | ж | Мегафон | Да | Свободный |
40 | 25 | ж | Другое | Да | Нет |
41 | 31 | ж | МТС | Да | Неделя |
42 | 39 | ж | Теле2 | Да | Неделя |
43 | 19 | м | Мегафон | Да | Нет |
44 | 21 | м | Теле2 | Нет | Свободный |
45 | 34 | м | Мегафон | Да | Неделя |
46 | 22 | ж | Мегафон | Да | График |
47 | 28 | ж | Билайн | Да | График |
48 | 29 | ж | МТС | Да | График |
49 | 38 | ж | Мегафон | Да | График |
50 | 39 | ж | Теле2 | Да | Плавающий |