Главная страница
Навигация по странице:

  • Рис. 2.

  • Сервис – Анализ данных

  • Определение основных статистических характеристик Задание 3

  • Сервис

  • Построение доверительных интервалов для среднего Задание 5

  • Описательная статистика

  • Анализ двух выборок Задание 7

  • Двухвыборочный t -тест с различными дисперсиями.

  • Парный двухвыборочный t -тест для средних

  • Статистический анализ в Excel. Решение в ячейку А1 введите слова Результаты, в диапазон А2 А16 результаты тестирования


    Скачать 328.5 Kb.
    НазваниеРешение в ячейку А1 введите слова Результаты, в диапазон А2 А16 результаты тестирования
    АнкорСтатистический анализ в Excel.doc
    Дата16.10.2017
    Размер328.5 Kb.
    Формат файлаdoc
    Имя файлаСтатистический анализ в Excel.doc
    ТипРешение
    #1229
    страница1 из 4

    Подборка по базе: 4 июня решение 2.docx
      1   2   3   4

    Статистический анализ в Excel
    Выборочная функция распределения

    Задание 1

    Построить эмпирическое распределение результатов тестирования в баллах для следующей выборки: 69, 85, 78, 85, 83, 81, 95, 88, 97, 92, 74, 83, 89, 77, 93.

    Решение

    1. В ячейку А1 введите слова Результаты, в диапазон А2:А16 - результаты тестирования.

    2. Выберите ширину интервала 5 баллов. Тогда при крайних результатах 69 и 97 баллов, получится 7 интервалов. В ячейку С1 введите название интервалов Границы. В диапазон С2:С8 введите граничные значения интервалов: 70, 75, 80, 85, 90, 95, 100.

    3. Введите заголовки создаваемой таблицы: в ячейку D1 – Абсолютные частоты, в ячейку Е1 – Относительные частоты, в F1 – Накопленные частоты.

    4. Заполните столбец абсолютных частот. Для этого выделите для них блок ячеек D2:D8, вызовите Мастер функций, категория – Статистические, функция – Частота, в поле Массив данных введите диапазон данных тестирования А2:А16, в поле Массив интервалов введите диапазон интервалов С2:С8, нажмите комбинацию клавиш Ctrl+Shift+Enter. В столбце D2:D8 появится массив абсолютных частот.

    5. В ячейке D9 найдите общее количество результатов тестирования, с помощью Автосумма.

    6. Заполните столбец относительных частот. В ячейку Е2 введите формулу =$D2/$D$9 .

    Протягиванием скопируйте полученное значение в диапазон Е3:Е8. Получим массив относительных частот.

    1. Заполните столбец накопленных частот. В ячейку F2 скопируйте значение относительной частоты из ячейки Е2. В ячейку F3 введите формулу =F2+E3. Протягиванием скопируйте полученное значение в диапазон F4:F8. Получим массив накопленных частот.

    2. В результате получим таблицу, представленную на рисунке 1.







    A

    B

    C

    D

    E

    F



    Результаты




    Границы

    Абсолютные частоты

    Относительные частоты

    Накопленные частоты



    69

     

    70

    1

    0,067

    0,067

      1. д

    85

     

    75

    1

    0,067

    0,133



    78

     

    80

    2

    0,133

    0,267



    85

     

    85

    5

    0,333

    0,600



    83

     

    90

    2

    0,133

    0,733



    81

     

    95

    3

    0,200

    0,933



    95

     

    100

    1

    0,067

    1,000



    88

     

     

    15




     



    97

     

     

     

     

     



    92

     

     

     

     

     



    74

     

     

     

     

     



    83

     

     

     

     

     



    89

     

     

     

     

     



    77

     

     

     

     

     



    93

     

     

     

     

     

    Рис. 1. Результат вычислений относительных и накопленных частот


    1. Постройте диаграмму относительных и накопленных частот. Выделите диапазон Е1:F8, вызовите Мастер диаграмм, откройте вкладку Нестандартные, выберите тип диаграммы График/гистограмма2, нажмите Далее. Откройте вкладку Ряд и в поле Подписи оси Х выделите диапазон С2:С8, нажмите Далее. Откройте вкладку Заголовки, в поле Ось Х введите название Баллы, в поле Ось YОтносительная частота, в поле Вторая ось Х Накопленная частота. Нажмите Готово.

    Получите диаграмму как на рисунке 2.



    Рис. 2. Диаграмма относительных и накопленных частот
    Задание 2

    Для данных из задания 2 построить эмпирические распределения, воспользовавшись процедурой Гистограмма.

    Решение

    1. В ячейку А1 введите слова Результаты, в диапазон А2:А16 - результаты тестирования.

    2. В ячейку С1 введите название интервалов Границы. В диапазон С2:С8 введите граничные значения интервалов: 70, 75, 80, 85, 90, 95, 100.

    3. Для вызова процедуры Гистограмма выберите меню Сервис_–_Анализ_данных'>Сервис – Анализ данных, выделите Гистограмма, нажмите Ok.

    4. В окне Гистограмма в поле Входной диапазон введите диапазон исследуемых данных А2:А16, в поле Интервал карманов введите диапазон С2:С8, в поле Выходной диапазон введите ссылку на левую верхнюю ячейку выходного диапазона F1.

    Установите переключатели в положение Интегральный процент и Вывод графика, нажмите Ok.

    Сравните полученную диаграмму с диаграммой из задания 1.
    Упражнения

    1. Постройте эмпирические функции распределения успеваемости в группе из 20 студентов: 4, 4, 5, 3, 4, 5, 4, 5, 3, 5, 3, 3, 5, 4, 5, 4, 3, 5, 3, 5.

    2. Постройте эмпирические функции распределения для роста (в см) группы из 20 мужчин: 181, 169, 178, 178, 171, 179, 172, 181, 179, 168, 174, 167, 169, 171, 179, 181, 181, 183, 172, 176.


    Определение основных статистических характеристик
    Задание 3

    Найдите выборочные среднее, моду, дисперсию и стандартное отклонение для следующей выборки: 26, 35, 29, 27, 33, 35, 30, 33, 31, 29.

    Решение

    1. В диапазон А1:А10 введите данные выборки.

    2. В ячейке А11 с помощью Мастера функций, категория Статистические, вычислите среднее значение – функция СРЗНАЧ.

    3. В ячейке А12 вычислите наиболее часто встречающее значение в выборке, с помощью функции МОДА.

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

    5. В ячейке А14 найдите стандартное отклонение выборки от среднего значения, с помощью функции СТАНДОТКЛОН.


    Задание 4

    Для данных из задания 3 найдите основные статистические характеристики с помощью инструментов Пакета анализа.

    Решение

    1. В диапазон А1:А10 введите данные выборки.

    2. Выберите в меню Сервис команду Анализ данных, выделите строку Описательная статистика, нажмите Ok.

    3. В поле Входной интервал выделите диапазон А1:А10, активируйте переключателем поле Выходной интервал и укажите ячейку А11. В разделе Группировка, установите переключатель по столбцам. Установите флажок в поле Итоговая статистика и нажмите Ok.

    4. В результате анализа в указанном выходном диапазоне получим соответствующие значения.

    Медиана – число, которое является серединой выборки.

    Эксцесс – частоты появления удаленных от среднего значения.

    Асимметрия – величина, характеризующая несимметричность распределения элементов выборки относительно среднего значения. Принимает значение от -1 до 1. Для симметричного распределения асимметрия равна 0.

    Интервал – разница между максимальным и минимальным значениями элементов выборки.

    Счет – количество элементов в выборке.
    Упражнения

    В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (мг/м3): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Определите основные статистические характеристики с помощью Мастера функций и инструмента Описательная статистика.
    Построение доверительных интервалов для среднего

    Задание 5

    Пусть имеется выборка, содержащая числовые значения: 13, 15, 17, 19, 22, 25, 19. Необходимо определить границы 95%-ного доверительного интервала для среднего значения.

    Решение

    1. В диапазон А1:А7 введите исходный ряд чисел.

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

    3. В ячейке В2 найдите стандартное отклонение диапазона, с помощью функции СТАНДОТКЛОН.

    4. Выделите ячейку В3, вызовите Мастер функций, категория Статистические, функция ДОВЕРИТ. В поле Альфа введите число 0,05, которое означает 95%-й уровень значимости, в поле Станд_откл выделите ячейку В2, в поле Размер введите размер выборки - число 7. Нажмите Ok.

    5. В ячейке В3 появится полуширина 95%-ного доверительного интервала для среднего значения выборки. Для нахождения нижней границы доверительного интервала от среднего значения в В1 надо отнять полученное значение в В3, для нахождения верхней границы следует прибавить В1 и В3. В В4 введите формулу =В1-В3 , в В5 - формулу =В1+В3 .


    Задание 6

    Для данных из задания 5 найти границы 95%-ного доверительного интервала для среднего значения с помощью процедуры Описательная статистика.

    Решение

    1. В диапазон А1:А7 введите исходный ряд чисел.

    2. Выберите в меню Сервис команду Анализ данных, выделите строку Описательная статистика, нажмите Ok.

    3. В поле Входной интервал выделите диапазон А1:А7, активируйте переключателем поле Выходной интервал и укажите ячейку В1. В разделе Группировка, установите переключатель по столбцам. Установите флажок в поле Уровень надежности и введите 95, нажмите Ok.

    4. В указанном выходном диапазоне получим значение доверительного интервала. Для нахождения нижней границы доверительного интервала от среднего значения надо отнять полученное значение, для нахождения верхней границы – прибавить. Выборочное среднее значение находится обычно одновременно с доверительным интервалом процедурой Описательная статистика, следует только установить флажок в поле Итоговая статистика.


    Упражнения

    Определите границы 95%-ного доверительного интервала выборки 2, 3, 5, 7, 4, 9, 6, 4, 9, 10, 4, 7, 9. Используйте функцию ДОВЕРИТ и процедуру Описательная статистика.
    Анализ двух выборок

    Задание 7

    Сравнивается количество баллов по тесту у двух групп студентов. Необходимо определить достоверность различия между группами при двух вариантах постановки задачи:

    • группы состоят из разных студентов;

    • группы состоят из одних и тех же студентов, но первая – до посещения курсов, а вторая – после.




    A

    B

    C



    1 группа

    2 группа






    19

    32




    1. д

    22

    35






    34

    40






    42

    54






    50

    61

















    0,212244

    0,001081
















    Решение

    1. Введите исходные данные.

    2. Для выявления достоверности отличий выделите свободную ячейку А8, вызовите Мастер функций, категория Статистические, функция ТТЕСТ, в поле Массив1выделите диапазон данных первой группы А2:А6, в поле Массив2 выделите диапазон данных второй группы В2:В6, в поле Хвосты всегда вводится цифра 2, в поле Тип введите цифру 3. Нажмите Ok. В ячейке А8 появится значение вероятности.

    3. Поскольку величина вероятности случайного появления анализируемых данных 0,212244 больше уровня значимости 0,05, то нулевая гипотеза не отвергается. Следовательно различия между выборками могут быть случайными, поэтому на основании применения критерия Стьюдента нельзя сделать вывод о достоверности отличий двух групп студентов по количеству баллов, набранных по тесту.

    4. Для решения второго варианта задачи, выделите ячейку В8, вызовите Мастер функций, категория Статистические, функция ТТЕСТ, в поле Массив1выделите диапазон данных первой группы А2:А6, в поле Массив2 выделите диапазон данных второй группы В2:В6, в поле Хвосты всегда вводится цифра 2, в поле Тип введите цифру 1. Нажмите Ok. В ячейке В8 появится значение вероятности.

    5. Поскольку величина вероятности случайного появления анализируемых данных 0,001081 меньше уровня значимости 0,05, то нулевая гипотеза отвергается. Следовательно, различия между выборками не случайные и можно сделать вывод о том, что в двух группах студентов выявлены достоверные отличия по количеству набранных баллов, что явилось результатом посещения курсов.


    Упражнения

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




    A

    B

    C



    С рекламой

    Без рекламы






    162

    135




      1. д

    156

    126






    144

    115






    137

    140






    125

    121






    145

    112






    151

    130
















    Задание 8

    Пусть после окончания двух институтов экономического профиля трудоустроились по специальности из первого институту 90 человек, а из второго 60 (обе группы молодых специалистов включали по 1000 человек). Достоверны ли отличия по успешности трудоустройства?

    Решение

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

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

    3. Выделите ячейку А3, вызовите Мастер функций, категория Статистические, функция ХИ2ТЕСТ, в поле Фактический интервал выделите диапазон А1:В1, в поле Ожидаемый интервал выделите диапазон А2:В2. Нажмите Ok.

    4. Поскольку полученное значение вероятности 0,014306 меньше уровня значимости 0,05, то нулевая гипотеза отвергается. Следовательно, различия между выборками не могут быть случайными и выборки считаются достоверно отличающимися друг от друга. Поэтому на основании применения критерия хи-квадрат можно сделать вывод о том, что в двух группах выпускников выявлены достоверные отличия по успешности трудоустройства.


    Упражнения

    В ходе социологического опроса ответы распределились следующим образом:




    Мужчины

    Женщины

    Да

    58

    35

    Нет

    11

    25

    Не знаю

    10

    23

    Есть ли достоверные отличия в ответах мужчин и женщин?
    Задание 9

    Для данных из задания 7 определить достоверность различия между группами студентов с помощью процедур пакета Анализ данных.

    Решение

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

    1. Введите исходные данные.

    2. Поскольку данные не имеют попарного соответствия следует использовать процедуру Двухвыборочный t-тест с различными дисперсиями. Для этого в меню Сервис выберите команду Анализ данных, выделите Двухвыборочный t-тест с различными дисперсиями и нажмите Ok.

    3. В поле Интервал переменной1 выделите диапазон А2:А6, в поле Интервал переменной2 выделите В2:В6, установите переключатель в поле Выходной интервал и укажите ячейку А8. Нажмите Ok.

    4. В указанном выходном диапазоне появятся результаты процедуры. Величина вероятности случайного появления анализируемых выборок (P(T<=t) двухстороннее) 0,212174 больше уровня значимости 0,05, т.е. различия между выборками могут быть случайными.

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

    1. Поскольку данные имеют парное соответствие следует использовать процедуру Парный двухвыборочный t-тест для средних. Выберите эту процедуру через меню Сервис, команда Анализ данных.

    2. В поле Интервал переменной1 выделите диапазон А2:А6, в поле Интервал переменной2 выделите В2:В6, установите переключатель в поле Выходной интервал и укажите ячейку D8. Нажмите Ok.

    3. В указанном выходном диапазоне появятся результаты процедуры. Величина вероятности случайного появления анализируемых выборок (P(T<=t) двухстороннее) 0,00108 меньше уровня значимости 0,05, т.е. нулевая гипотеза отвергается. Следовательно, различия между выборками не могут быть случайными и выборки считаются достоверно отличающимися друг от друга.


    Упражнения

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




    Количество приобретаемых путевок

    Месяцы

    1

    2

    3

    4

    5

    6

    Семейные пары

    67

    75

    58

    89

    96

    94

    Отдельные туристы

    43

    56

    78

    87

    85

    90




    1. В таблице приведены результаты группы студентов по скоростному чтению до и после специального курса по быстрому чтению. Произошли ли статистически значимые изменения скорости чтения у студентов?

    Студент

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    До курса

    86

    83

    86

    70

    66

    90

    70

    85

    77

    86

    После

    82

    79

    91

    77

    68

    86

    81

    90

    85

    94



      1   2   3   4


    написать администратору сайта