Как решать статистические задачи в excel

Порядок

Использование Excel для расчета статистических характеристик случайной величины

Разделы: Математика

  • Совершенствование умений и навыков нахождения статистических характеристик случайной величины, работа с расчетами в Excel;
  • применение информационно коммутативных технологий для анализа данных; работа с различными информационными носителями.
  1. Сегодня на уроке мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
  2. Для начала вспомним:

– что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)

– Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)

– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).

– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).

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

  1. Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.

Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):

1. Занести данные в EXCEL, каждое число в отдельную ячейку.

23 25 24 25 30 24 30 26 28 26
32 33 31 31 25 33 25 29 30 28
23 30 29 24 33 30 30 28 26 25
26 29 27 29 26 28 27 26 29 28
29 30 27 30 28 32 28 26 30 26
31 27 30 27 33 28 26 30 31 29
27 30 30 29 27 26 28 31 29 28
33 27 30 33 26 31 34 28 32 22
29 30 27 29 34 29 32 29 29 30
29 29 36 29 29 34 23 28 24 28

2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем — статистические, в списке: МОДА

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.

Используя тот же путь вычисляем медиану.

Вставка – Функция – Статистические – Медиана.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.

Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.

Вставка – Функция – Статистические – МАКС.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили наибольшее значение = 36.

Вставка – Функция – Статистические – МИН.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили наименьшее значение = 22.

36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.

Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.

xi 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
ni

Чтобы сосчитать частоту каждого значения воспользуемся

Вставка – Функция – Статистические – СЧЕТЕСЛИ.

В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22

Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.

xi 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
ni 1 3 4 5 11 9 13 18 16 6 4 6 3 0 1

Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические — СУММА). Должно получиться 100 (количество всех фирм).

Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)

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

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

Диаграмма – Стандартные – Круговая.

Диаграмма – Стандартные – Гистограмма.

4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.

Решение статистических задач в EXCEL: Практикум , страница 2

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

GeekBrains

Вторая часть содержит описание команд EXCEL, используемых для решения статистических задач практикума.

В последней части даны задания для самостоятельного решения.

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

Раздел 1. Вычисление базовых статистических показателей.

Средняя арифметическая величина может вычисляться по простой и взвешенной формулам.

Простая средняя арифметическая

Для расчетов в Excel используется функция:

СРЗНАЧ (диапазон данных).

Рассмотрим методику расчета на следующем примере: определить среднюю зарплату в подразделении

1. Исходные значения признака хi надо записать в массив ячеек расположенных в столбце или строке (в примере в строке A2-L2);

2. Количественные данные следует определить как «Числовые».

1. В ячейку результата (например «Е3») установить функцию СРЗНАЧ (А2:L2).

Функция находит простое среднее арифметическое для заданных хi в диапазоне (А2:L2).

Взвешенная средняя арифметическая

Для расчета взвешенной средней арифметической эффективно использовать функции СУММ (диапазон данных) и СУММПРОИЗВ (диапазоны перемножаемых данных).

Взвешенная форма используется как правило для:

· непрерывных сгруппированных данных.

Взвешенная средняя арифметическая для
дискретных данных

Рассмотрим методику расчета на примере расчета среднего размера семей в группе:

1. Исходные значения признака хi и частоту fiнадо записать в массивы ячеек расположенных в столбце или строке (в примере в строках (O13:T13) и (O14:T14).

2. Количественные данные следует определить как «Числовые».

1. В ячейку результата (например «R16») установить функцию СУММПРОИЗВ(O13:T13;O14:T14) реализует числитель

Функция выполняет попарное умножение элементов массива хi в ячейках (O13:T13)на элементы массива fi , в ячейках (O14:T14), а затем суммирует произведения.

СУММ (O14:T14) – знаменатель

Функция суммирует элементы массива fi , в ячейках O14:T14.

«/» — знак деления.

Взвешенная средняя арифметическая для
непрерывных сгруппированных данных

Рассмотрим методику расчета на примере расчета средних затрат на выпуск 1 тыс. руб. продукции.

1. Значение признака х задано интервалом. Для ввода интервала используйте 2 ячейки (в примере используются столбцы V и W). Левые границы интервала заданы в ячейках (V19:V23), правые (W19:W23). Частота fi записана в массив ячеек (Х19:Х23).

  • АлтГТУ 419
  • АлтГУ 113
  • АмПГУ 296
  • АГТУ 267
  • БИТТУ 794
  • БГТУ «Военмех» 1191
  • БГМУ 172
  • БГТУ 603
  • БГУ 155
  • БГУИР 391
  • БелГУТ 4908
  • БГЭУ 963
  • БНТУ 1070
  • БТЭУ ПК 689
  • БрГУ 179
  • ВНТУ 120
  • ВГУЭС 426
  • ВлГУ 645
  • ВМедА 611
  • ВолгГТУ 235
  • ВНУ им. Даля 166
  • ВЗФЭИ 245
  • ВятГСХА 101
  • ВятГГУ 139
  • ВятГУ 559
  • ГГДСК 171
  • ГомГМК 501
  • ГГМУ 1966
  • ГГТУ им. Сухого 4467
  • ГГУ им. Скорины 1590
  • ГМА им. Макарова 299
  • ДГПУ 159
  • ДальГАУ 279
  • ДВГГУ 134
  • ДВГМУ 408
  • ДВГТУ 936
  • ДВГУПС 305
  • ДВФУ 949
  • ДонГТУ 498
  • ДИТМ МНТУ 109
  • ИвГМА 488
  • ИГХТУ 131
  • ИжГТУ 145
  • КемГППК 171
  • КемГУ 508
  • КГМТУ 270
  • КировАТ 147
  • КГКСЭП 407
  • КГТА им. Дегтярева 174
  • КнАГТУ 2910
  • КрасГАУ 345
  • КрасГМУ 629
  • КГПУ им. Астафьева 133
  • КГТУ (СФУ) 567
  • КГТЭИ (СФУ) 112
  • КПК №2 177
  • КубГТУ 138
  • КубГУ 109
  • КузГПА 182
  • КузГТУ 789
  • МГТУ им. Носова 369
  • МГЭУ им. Сахарова 232
  • МГЭК 249
  • МГПУ 165
  • МАИ 144
  • МАДИ 151
  • МГИУ 1179
  • МГОУ 121
  • МГСУ 331
  • МГУ 273
  • МГУКИ 101
  • МГУПИ 225
  • МГУПС (МИИТ) 637
  • МГУТУ 122
  • МТУСИ 179
  • ХАИ 656
  • ТПУ 455
  • НИУ МЭИ 640
  • НМСУ «Горный» 1701
  • ХПИ 1534
  • НТУУ «КПИ» 213
  • НУК им. Макарова 543
  • НВ 1001
  • НГАВТ 362
  • НГАУ 411
  • НГАСУ 817
  • НГМУ 665
  • НГПУ 214
  • НГТУ 4610
  • НГУ 1993
  • НГУЭУ 499
  • НИИ 201
  • ОмГТУ 302
  • ОмГУПС 230
  • СПбПК №4 115
  • ПГУПС 2489
  • ПГПУ им. Короленко 296
  • ПНТУ им. Кондратюка 120
  • РАНХиГС 190
  • РОАТ МИИТ 608
  • РТА 245
  • РГГМУ 117
  • РГПУ им. Герцена 123
  • РГППУ 142
  • РГСУ 162
  • «МАТИ» — РГТУ 121
  • РГУНиГ 260
  • РЭУ им. Плеханова 123
  • РГАТУ им. Соловьёва 219
  • РязГМУ 125
  • РГРТУ 666
  • СамГТУ 131
  • СПбГАСУ 315
  • ИНЖЭКОН 328
  • СПбГИПСР 136
  • СПбГЛТУ им. Кирова 227
  • СПбГМТУ 143
  • СПбГПМУ 146
  • СПбГПУ 1599
  • СПбГТИ (ТУ) 293
  • СПбГТУРП 236
  • СПбГУ 578
  • ГУАП 524
  • СПбГУНиПТ 291
  • СПбГУПТД 438
  • СПбГУСЭ 226
  • СПбГУТ 194
  • СПГУТД 151
  • СПбГУЭФ 145
  • СПбГЭТУ «ЛЭТИ» 379
  • ПИМаш 247
  • НИУ ИТМО 531
  • СГТУ им. Гагарина 114
  • СахГУ 278
  • СЗТУ 484
  • СибАГС 249
  • СибГАУ 462
  • СибГИУ 1654
  • СибГТУ 946
  • СГУПС 1473
  • СибГУТИ 2083
  • СибУПК 377
  • СФУ 2424
  • СНАУ 567
  • СумГУ 768
  • ТРТУ 149
  • ТОГУ 551
  • ТГЭУ 325
  • ТГУ (Томск) 276
  • ТГПУ 181
  • ТулГУ 553
  • УкрГАЖТ 234
  • УлГТУ 536
  • УИПКПРО 123
  • УрГПУ 195
  • УГТУ-УПИ 758
  • УГНТУ 570
  • УГТУ 134
  • ХГАЭП 138
  • ХГАФК 110
  • ХНАГХ 407
  • ХНУВД 512
  • ХНУ им. Каразина 305
  • ХНУРЭ 325
  • ХНЭУ 495
  • ЦПУ 157
  • ЧитГУ 220
  • ЮУрГУ 309

Полный список ВУЗов

  • О проекте
  • Реклама на сайте
  • Правообладателям
  • Правила
  • Обратная связь

Чтобы распечатать файл, скачайте его (в формате Word).

Корпорация "Центр"

Ссылка на основную публикацию