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

Ниже приведены условия задач и отчет  в формате doc. Закачка полного решения(документы  doc и xlsx в архиве zip) начнется автоматически через 10 секунд. Если закачка не началась, кликните по этой ссылке.

Видеурок по решению этих задач  - внизу страницы.  

Задание 1.

Пример 1.9.

Сгенерировать ряд значений переменной «Доход», которая является случайной величиной, распределенной по нормальному закону с параметрами М и , где М — среднее значение (математическое ожидание) случайной величины, — стандартное (среднеквадратическое) отклонение.

Решение.

Использование инструмента «Генерация случайных чисел»

Данный инструмент предназначен для заполнения диапазона ячеек случайными числами, распределенными по какому-либо закону. С помощью данной процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей. Чтобы сгенерировать на рабочем листе ряд значений случайной величины, нужно выполнить команду Сервис—Анализ данных— Генерация случайных чисел—OK и заполнить открывшееся диалоговое окно.

 

Рис 1. Диалоговое окно «Генерация случайных чисел»

1)  Число переменных — это количество столбцов, в которых будут выведены случайные величины в соответствии с заданным законом распределения;

2)  Число случайных чисел — количество ячеек со значениями случайной величины в каждом столбце;

3)                Распределение — из списка выбирается один из законов;

Нормальное распределение — наиболее распространенный закон, описывающий случайные явления, для которых характерно воздействие на конечный результат многих независимых случайных факторов, причем среди этих факторов нет особенно выделяющихся. Применяется для описания непрерывных величин. Характеризуется двумя параметрами: средним значением и стандартным отклонением.

4)  Параметры — зависят от выбранного закона:

5) Случайное рассеивание — число, которое позволяет при повторной генерации получать те же числа, что и при первом запуске генератора;

6) Параметры вывода — указывается верхняя левая ячейка на текущем листе, начиная с которой будут выведены случайные числа, можно также указать вывод на новый лист или в новый файл.

В нашем случае, для генерации 36 значений одной переменной, распределенной по нормальному закону с параметрами: М=11; =1,95 , нужно заполнить диалог так, как показано на рис. 1. Тогда после нажатия ОК диапазон ячеек В4:В39 будет заполнен значениями случайной величины (см. рис. 2).

Рис.2

Пример.2

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

Решение.

Чтобы найти характеристики сгенерированного ряда , нужно выполнить команду Сервис—Анализ данных— Описательная статистика—OK и заполнить открывшееся диалоговое окно.

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

Рис.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) В ячейку D2 вводим формулу = ПРОЦЕНТРАНГ($A$2:$A$37;A2) и протягиваем ее маркером до ячейки D37. Результат вычисления интегральной функции распределения приведен на рис. 4.

Рис.4.

Пример 6.1.

Найти распределение частот для сгенерированного ряда. Количество интервалов разбиения (n) взять из таблицы:

n

5

Решение.

1) найти максимальное (Smax) и минимальное (Smin) значение ряда (ячейки D6 и D7);

2) вычислить длину интервала (кармана) по формуле (ячейка D5);

3) вычислить границу первого кармана по формуле L1 = Smin +   (ячейка D8);

4) протянуть формулу еще на 5 ячеек для получения границ остальных карманов, т.е. L2 = L1 + , … . Если все было сделано правильно, то верхняя граница последнего кармана должна быть равна максимальному значению ряда, т.е. Ln = Smax . Таким образом получено шесть карманов: в первый попадают значения ряда, соответствующие условию Si L1; во второй — L1 < Si L2; … 

Рис. 5. Распределение частот для ряда случайной величины

5) по функции ЧАСТОТА вычислить количество значений случайной величины, попадающих в каждый карман. Для этого:

-       выделить диапазон ячеек, соответствующий количеству карманов: F8:F12;

-       вставить функцию и заполнить аргументы, как показано на рисунке:

-       не нажимая кнопку ОК, завершить ввод формулы комбинацией клавиш
Ctrl + Shift + Enter;

-       в результате формула в строке формул будет заключена в фигурные скобки, а выделенный диапазон (F8:F12) будет заполнен значениями.

 

Пример7.8

Вычислить k-ую персентиль и j-ую квартиль сгенерированного ряда.

Вариант

8

k

55%

j

4

Решение.

  1. Задаем в ячейке Е:11 функцию =ПЕРСЕНТИЛЬ(A1:A36;D11) . Получим 55% персентиль .
  2. Задаем в ячейке Е:14 функцию=КВАРТИЛЬ(A1:A36;D14) . Получим 4-ю квартиль.

Результаты представлены на рисунке 6.

Рис.6.

 

Скачать решение:

Имя файла: Urok1.zip

Размер файла: 119.43 Kb

Если закачивание файла не начнется через 10 сек, кликните по этой ссылке