Список предметов
Анализ распределения рядов данных
4 / 18
Развернуть структуру обучения Свернуть структуру обучения

Цель работы – получить умения по анализу распределения данных в программе MS Excel.

Задание работы – провести анализ статистических рядов распределения с помощью надстройки “анализ данных”.

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

мода

Формула вычисления моды статистического ряда данных

медиана

Формула вычисления медианы статистического ряда данных

дисперсия

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

Среднее квадратичное отклонение

Формула вычисления среднего квадратического отклонения статистического ряда данных

Коэффициент вариации

Формула вычисления коэффициента вариации статистического ряда данных

Решать задачи по анализу рядов распределения типа в Excel можно с помощью использования программной надстройки Пакет анализа и встроенных статистических функций.

Анализ не сгруппированных статистических данных. Построим таблицу с первичными данными.

Статистический ряд для анализа

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

Выбор из списка вариантов анализа

В диалоговом окне данного режима задаются следующие параметры:

1. Входной интервал – ячейки, в которых содержатся статистические данные.

2. Группирование – устанавливается положение По столбцами или По строкам в зависимости от расположения данных во входном диапазоне.

3. Метки в первом ряду – активизируется, если первая строка (столбец) во входном диапазоне содержит заголовки. Если заголовки отсутствуют, то метку необходимо деактивировать. В этом случае будут автоматически созданы стандартные названия для данных выходящего диапазона.

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

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

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

5. Итоговая статистика – активизируется, если в выходном диапазоне необходимо получить строку для предельной погрешности выборки при установленном уровне надежности.

6. Уровень надежности – активизируется, если в выходную таблицу необходимо включить строку для предельной погрешности при установленном уровне надежности.

7 К-тое наибольшее – активизируется, если в выходную таблицу необходимо включить строку для к-того наибольшего (начиная с максимума хmax) значения элемента совокупности. В поле напротив нужно ввести чисто к. Если к=1, то строка будет содержать максимальное значения элемента выборки.

8 К-тое наименьшее – активизируется, если в выходную таблицу необходимо включить строку для к=того наименьшего (начиная с хmin) значения элемента выборки. Введенные параметры представлены на рисунке снизу

Диалог выбора параметров анализа статистического ряда

Далее на рисунке можно увидеть сам статистический отчет

Статистический отчет на основе данных статистического ряда

Согласно полученным данным:

Коэффициент осцилляции равен

Вычисление коэффициента осциляции на примере статистического ряда

Коэффициент вариации равен

Пример расчета коэффициента вариации статистического ряда

Анализ сгруппированных статистических данных

Построим таблицу с исходными данными.

Статистический ряд, сгруппированный

Нам нужно посчитать среднюю взвешенную арифметическую. Данная функция не представлена в Excel. Но мы можем сгруппировать несколько функций, чтобы получить нужную формулу. Используем функции СУММПРОИЗВ и СУММ. Функция СУММПРОИЗВ перемножает две группы массивов между собой и складывает полученные данные после произведения. Построим формулу =СУММПРОИЗВ(C3:C8;B3:B8)/СУММ(B3:B8)

Для вычисления моды и медианы необходимо произвести несколько расчетов

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

Рассчитаем модальное количество рабочих. Это можно сделать с помощью формулы =МАКС(B3:B8). Мы находим максимальное значение на выбранном диапазоне.

Найдем сдвиг в столбце на модальное значение. Это можно сделать с помощью формулы =ПОИСКПОЗ(C9;B3:B8;0). Мы находим положение нужного нам значения.

Найдем модальный интервал. Это можно сделать с помощью формулы =ИНДЕКС(A3:A8;C10;1). По заданным координатам находим значение на диапазоне.

Найдем нижнюю границу модального интервала. Сделаем это с помощью формулы =ЛЕВСИМВ(C11;1). Отделяем нужное значение от модального интервала.

Найдем количество предприятий, которые имеют меньший объем продаж. Используем формулу =ИНДЕКС(B3:B8;C10-1;1). ПО заданным координатам находим положение нужного нам значения.

Теперь найдем количество предприятий, которые имеют больший уровень продаж =ИНДЕКС(B3:B8;C10+1;1). Работает по такому же принципу.

Посчитаем моду объема продаж с помощью формулы =C12+2*((C9-C13)/((C9-C13)+(C9-C14))).

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

Расчет медианы в excel на основе сгруппированного статистического ряда данных

В массиве С3:С8 рассчитывается накопительная частота. Считается с помощью формулы =C3+B4 (клетка С3).

Рассчитаем общее количество рабочих с помощью формулы =СУММ(B3:B8). Мы рассчитали количество совокупности.

Найдем половину всех рабочих с помощью формулы =B9/2. Мы рассчитали половину совокупности.

Найдем смещение max≦N/2 по формуле =ПОИСКПОЗ(C10;C3:C8;1). В массиве С3:С8 рассчитывается номер позиции числа, которое является наибольшим среди чисел меньших или равных вредине интервала. Это число равно 99.

Найдем значение max≦N/2 с помощью формулы =ИНДЕКС(C3:C8;C11;1). Из числового массива С3:С8 берется число, которое удовлетворяет условиям поиска, сформированными нами в клетке С11.

Найдем смещение на интервал медианы с помощью формулы =ЕСЛИ(C10=C12;C11;C11+1).

Найдем частоту интервала медианы с помощью формулы =ИНДЕКС(B3:B8;C13;1). Формула отображает это значение.

Найдем интервал медианы по формуле =ИНДЕКС(A3:A8;C13;1). В заданном массиве находится интервал медианы.

Найдем нижнюю границу интервала по формуле =ЛЕВСИМВ(C15;1). Формула отображает нужное нам значение.

Найдем значение накопительной частоты предыдущего интервала с помощью формулы =ИНДЕКС(C3:C8;C13-1;1). В заданном интервале находится искомое значение.

Найдем медиану сумм оборотных активов с помощью формулы =C16+2*((B9/2-C17)/C14).

Расчет среднего квадратичного отклонения позволяет посчитать коэффициент вариации.

Расчет коэффициента вариации в excel на основе данных статистического ряда

Расчет Показателей на рисунке происходит по следующим формулам:

Дисперсию мы считаем по следующей формуле

=(СУММПРОИЗВ(СТЕПЕНЬ(C3:C8-C9;2);B3:B8))/СУММ(B3:B8).

Среднее квадратичное отклонение мы считаем по формуле

=КОРЕНЬ(C10).

Коэффициент вариации находим по формуле

=(C11/C9)*100.

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

Расчет квартильного коэффициента вариации (первый квартиль)

Вычисления на этом рисунке практически аналогичны расчету медианы кроме следующих пунктов:

Мы должны посчитать 25% (1/4) рабочих. Сделаем это по формуле =B9*0,25.

М должны посчитать первый квартиль. Расчет происходит по формуле =C16+2*((B9*0,25-C17)/C14).

Третий квартиль находится в интервале 11-13 лет. Он равняется приблизительно 11,5. Для его вычисления мы должны поменять несколько пунктов таблицы:

Мы должны посчитать 75% рабочих. Сделаем это по формуле =B9*0,75.

Скорректируем формулу по нахождению нижней границы квартильного интервала =ЛЕВСИМВ(C15;2).

Рассчитаем третий квартиль по формуле =C16+2*((B9*0,75-C17)/C14).

Расчет с помощью excel квартильного коэффициента (третий квартиль)

Таким образом, квартильное отклонение равняется

Формула расчета квартильного отклонения на примере

Квартильный показатель вариации равняется

Расчет квартильного показателя вариации на примере

Дисперсионный анализ

Исследовалась сила влияния внешнего вида упаковки товара на покупательную способность в разных точках продажи с помощью дисперсионного анализа.

Дисперсионный анализ на практическом примере с помощью excel

Формулы расчета:

Средний уровень продаж рассчитывается по формуле =(B9+C9)/(5+5).

Средний уровень продаж по 1 упаковке рассчитывается по формуле =B9/5.

Средний уровень продаж по 2 упаковке рассчитывается по формуле =C9/5.

Внутригрупповая дисперсия по 1 упаковке рассчитывается по формуле =ДИСПР(B4:B8).

Внутригрупповая дисперсия по 2 упаковке рассчитывается по формуле =ДИСПР(C4:C8).

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

=(D13×5+D14×5)/10.

Межгрупповая дисперсия рассчитывается по формуле =((СТЕПЕНЬ(D11-D10;2))*5+(СТЕПЕНЬ(D12-D10;2))*5)/10.

Общая дисперсия рассчитывается по формуле =СУММ(D15;D16).

Вычислим дисперсию и среднее квадратичное отклонение альтернативного признака.

Пример вычисления дисперсии

Выпишем формулы, с помощью которых все посчитано:

Средний процент пригодной продукции в трех партиях вычисляется по формуле =(СУММ(C4:C6))/СУММ(B4:B6).

Средний процент бракованной продукции вычисляется по формуле =1-E7

Дисперсия удельного веса пригодной продукции(дисперсия альтернативного признака) вычисляется по формуле =E7*E8.

Среднее квадратичное отклонение рассчитывается по формуле =КОРЕНЬ(E9).

Определение коэффициентов асимметрии и эксцесса предусматривает расчет моментов третьего и четвертого порядка.

Пример определения коэффициентов асимметрии и эксцесса в excel

Формулы:

Расчет момента третьего порядка происходит по формуле =(СУММПРОИЗВ(СТЕПЕНЬ(C3:C8-C9;3);B3:B8))/СУММ(B3:B8)

Расчет момента четвертого порядка происходит по формуле =(СУММПРОИЗВ(СТЕПЕНЬ(C3:C8-C9;3);B3:B8))/СУММ(B3:B8)

Теперь мы можем рассчитать коэффициент эксцесса по формуле =(C13/СТЕПЕНЬ(C10;4))-3.

С помощью этой работы можно научится анализировать ряды распределения с помощью программы MS Excel.

0  


 Процент выполнения плана | Описание курса | Коэффициент концентрации 
   

Обсудить на форуме
Записаться на курсы
Обратиться к консультанту
Пройти тест
Полный список курсов обучения
Бесплатные видеоуроки
Нужна информация!




Нажмите, чтобы рекомендовать эту страницу другим:
Рейтинг@Mail.ru