Примеры решения задач по статистике в Excel с помощью надстройки «анализ данных» и видеоурок
Ниже приведены условия задач и отчет в формате doc. Закачка полного решения(документы doc и xlsx в архиве zip) начнется автоматически через 10 секунд. Если закачка не началась, кликните по этой ссылке.
Сгенерировать ряд значений переменной «Доход», которая является случайной величиной, распределенной по нормальному закону с параметрами М и , где М — среднее значение (математическое ожидание) случайной величины, — стандартное (среднеквадратическое) отклонение.
Использование инструмента « Генерация случайных чисел »
1) Число переменных — это количество столбцов, в которых будут выведены случайные величины в соответствии с заданным законом распределения;
2) Число случайных чисел — количество ячеек со значениями случайной величины в каждом столбце;
3) Распределение — из списка выбирается один из законов;
Нормальное распределение — наиболее распространенный закон, описывающий случайные явления, для которых характерно воздействие на конечный результат многих независимых случайных факторов, причем среди этих факторов нет особенно выделяющихся. Применяется для описания непрерывных величин. Характеризуется двумя параметрами: средним значением и стандартным отклонением.
5) Случайное рассеивание — число, которое позволяет при повторной генерации получать те же числа, что и при первом запуске генератора;
6) Параметры вывода — указывается верхняя левая ячейка на текущем листе, начиная с которой будут выведены случайные числа, можно также указать вывод на новый лист или в новый файл.
В нашем случае, для генерации 36 значений одной переменной, распределенной по нормальному закону с параметрами: М=11; =1,95 , нужно заполнить диалог так, как показано на рис. 1. Тогда после нажатия ОК диапазон ячеек В4:В39 будет заполнен значениями случайной величины (см. рис. 2).
Пример.2
Вычислить, используя статистические функции, характеристики сгенерированного ряда: среднее значение, стандартное отклонение, медиана, асимметричность, минимум.
Чтобы найти характеристики сгенерированного ряда , нужно выполнить команду Сервис—Анализ данных— Описательная статистика— OK и заполнить открывшееся диалоговое окно.
Далее заполняем диалоговое окно . Вводим входной интервал , указываем параметры вывода . В результате получим данные , показанные на рисунке 3.
Пример.5
Для каждого значения случайной величины вычислить интегральную функцию распределения вероятности и процентранг.
Для вычисления плотности и интегральной функции используем функцию НОРМРАСП, так как в примере 1 была сгенерирована величина, распределенная по нормальному закону с параметрами: М=11; =1,95.
1) В ячейку В2 вводим формулу = НОРМРАСП(A2;$G$2;$G$3;0)и протягиваем ее маркером до ячейки В37. Результат вычисления плотности распределения вероятности приведен на рис. 4.
2) В ячейку С2 вводим формулу = НОРМРАСП(A2;$G$2;$G$3;1) и протягиваем ее маркером до ячейки С37. Результат вычисления интегральной функции распределения приведен на рис. 4.
3) В ячейку D 2 вводим формулу = ПРОЦЕНТРАНГ($A$2:$A$37;A2) и протягиваем ее маркером до ячейки D37. Результат вычисления интегральной функции распределения приведен на рис. 4.
Найти распределение частот для сгенерированного ряда. Количество интервалов разбиения ( n ) взять из таблицы:
1) найти максимальное ( S max ) и минимальное ( S min ) значение ряда (ячейки D 6 и D 7);
2) вычислить длину интервала (кармана) по формуле (ячейка D 5);
3) вычислить границу первого кармана по формуле L 1 = S min + (ячейка D 8);
4) протянуть формулу еще на 5 ячеек для получения границ остальных карманов, т.е. L 2 = L 1 + , … . Если все было сделано правильно, то верхняя граница последнего кармана должна быть равна максимальному значению ряда, т.е. L n = S max . Таким образом получено шесть карманов: в первый попадают значения ряда, соответствующие условию S i L 1 ; во второй — L 1 < S i L 2 ; …
Рис. 5. Распределение частот для ряда случайной величины
5) по функции ЧАСТОТА вычислить количество значений случайной величины, попадающих в каждый карман. Для этого:
— выделить диапазон ячеек, соответствующий количеству карманов: F 8: F 12;
— вставить функцию и заполнить аргументы, как показано на рисунке:
— не нажимая кнопку ОК, завершить ввод формулы комбинацией клавиш
Ctrl + Shift + Enter ;
— в результате формула в строке формул будет заключена в фигурные скобки, а выделенный диапазон ( F 8: F 12) будет заполнен значениями.
[expert_bq id=»1570″]Используйте это, когда вы делали два измерения одного и того же человека, например, измеряли артериальное давление до и после вмешательства. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы запустить t -тест, выберите соответствующий тест в окне инструментов анализа и выберите оба набора данных таким же образом, как вы делали для F-теста. Оставьте значение альфа на 0,05 и нажмите ОК .
Лабораторная работа " Статистические функции Excel" | Методическая разработка по теме: | Образовательная социальная сеть
У нас есть следующие данные, относящиеся к расходам на рекламу и продажам компании. Мы хотим выяснить взаимосвязь между ними, чтобы мы могли соответствующим образом планировать наш бюджет и ожидать продаж (установить цель, учитывая также другие факторы).
