Как в офисе

Среднее отклонение в Excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных. Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи — следите за рекламой! Однако сухая теория без инструментов реализации — вещь не сильно полезная. Поэтому по мере появления теоретических выкладок, я стараюсь не отставать с заметками о соответствующих расчетах в программе Excel.

Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:

— максимальное и минимальное значение

— среднее линейное отклонение

— дисперсия (по генеральной совокупности и по выборке)

— среднее квадратическое отклонение (по генеральной совокупности и по выборке)

Факт возможности расчета упомянутых показателей в Excel свидетельствует о практическом их использовании. И, несмотря на очевидность некоторых моментов, я постараюсь расписать все подробно.

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

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

Шаг 1: расчет стандартного отклонения

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

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…) = СТАНДОТКЛОН.В(Число1;Число2;…)

    Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.

Переход в Мастер функций в Microsoft Excel

Переход к аргументам функции СТАНДОТКЛОН.В в Microsoft Excel

Аргументы функции СТАНДОТКЛОН.В в Microsoft Excel

Результат расчета функции СТАНДОТКЛОН.В в Microsoft Excel

Урок: Формула среднего квадратичного отклонения в Excel

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция — СРЗНАЧ. Вычислим её значение на конкретном примере.

    Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».

Перемещение в Мастер функций в Microsoft Excel

Переход к аргументам функции СРЗНАЧ в Microsoft Excel

Аргументы функции СРЗНАЧ в Microsoft Excel

Результат расчета функции СРЗНАЧ в Microsoft Excel

Урок: Как посчитать среднее значение в Excel

Шаг 3: нахождение коэффициента вариации

Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.

    Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.

Форматированиае ячейки в Microsoft Excel

Вычисление коэффициента вариации в Microsoft Excel

Результат расчета коэффициента вариации в Microsoft Excel

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

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

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

Расчет коэффициента вариации в Microsoft Excel

Результат расчета коэффициента вариации в программме Microsoft Excel

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.

Расчет среднего арифметического

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

  • открываете утилиту. В верхней строке набираете ряд нужных цифр;Строка данных
  • под первой цифрой ставите курсор. В верхней строке программы выбираете вкладку «Редактирование», затем кнопку «Сумма». В выпавшем окне выбираете значение «Среднее»;Вычисление среднего
  • после того, как кликните в том пункте на котором стоит курсор, появится формула;Вводим формулу
  • останется только выделить диапазон и кликнуть по кнопке «Ввод». А в ячейке теперь отобразится результат из взятых данных выше.

Рассчитываем коэффициент в Экселе

К сожалению, в Excel не заложена стандартная формула, которая бы позволила рассчитать показатель вариации автоматически. Но это не значит, что вам придётся производить расчёты в уме. Отсутствие шаблона в «Строке формул» никоим образом не умаляет способностей Excel, потому вы вполне сможете заставить программу выполнить необходимый вам расчёт, прописав соответствующую команду вручную.

Excel Формула Коэффициент вариации

Вставьте формулу и укажите диапазон данных

Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом — СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.

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

  1. Откройте вкладку «Главная».
  2. Найдите в ней категорию «Формат ячеек» и выберите необходимый параметр.

Как вариант, можно задать процентный формат ячейке при помощи клика по правой кнопке мыши на активированной клеточке таблицы. В появившемся контекстном меню, аналогично вышеуказанному алгоритму нужно выбрать категорию «Формат ячейки» и задать необходимое значение.

Excel - Формат ячеек

Выберите «Процентный», а при необходимости укажите число десятичных знаков

Возможно, кому-то вышеописанный алгоритм покажется сложным. На самом же деле расчёт коэффициента так же прост, как сложение двух натуральных чисел. Единожды выполнив эту задачу в Экселе, вы больше никогда не вернётесь к утомительным многосложным решениям в тетрадке.

Всё ещё не можете сделать качественное сравнение степени разброса данных? Теряетесь в масштабах выборки? Тогда прямо сейчас принимайтесь за дело и осваивайте на практике весь теоретический материал, который был изложен выше! Пусть статистический анализ и разработка прогноза больше не вызывают у вас страха и негатива. Экономьте свои силы и время вместе с табличным редактором Excel.

Примеры расчета

Пример 1

Портфельный менеджер должен оценить риски инвестирования в акции двух компаний А и Б. При этом он рассматривает 5 сценариев развития событий, информация по которым представлена в таблице.

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

Шаг 1. Рассчитаем математическое ожидание доходности для каждой из акций.

M(А) = -5%×0,02+6%×0,25+15%×0,40+24%×0,30+34%×0,03 = 15,62%

M(Б) = -18%×0,02+2%×0,25+16%×0,40+27%×0,30+36%×0,03 = 22,14%

Шаг 2. Подставим полученные данные в первую формулу.

Как мы можем видеть, акции Компании А характеризуются меньшим уровнем риска, поскольку у них ниже среднеквадратическое отклонение доходности. Следует также отметить, что и ожидаемая доходность у них ниже, чем у акций Компании Б.

Пример 2

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

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

Шаг 1. Рассчитаем ожидаемую доходность для каждой ценной бумаги как среднеарифметическое выборки.

X А = (7 + 15 + 2 – 5 + 6) ÷ 5 = 5%

X Б = (3 – 2 + 12 + 4 +8) ÷ 5 = 5%

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

Следует отметить, что обе ценные бумаги имеют равную ожидаемую доходность 5%. При этом стандартное отклонение доходности у ценной бумаги Б ниже, что при прочих равных делает ее более привлекательным объектом инвестирования в следствие лучшего профиля риск-доходность.

Алгоритм работы

а) Коэффициенты уравнения соответствуют данным столбца Коэффициенты (следующий за столбцомY-пересечения) (блок Дисперсионный анализ).
б) Стандартная ошибка регрессии соответствует значению Стандартная ошибка блока Регрессионная статистика.
Стандартные ошибки коэффициентов соответствуют значениям столбца Стандартная ошибка блока Дисперсионный анализ.
в) Доверительные интервалы соответствуют интервалам Нижние %, Верхние %.
г) Статистическая значимость коэффициентов уравнения соответствует столбцу t -статистика. Граничная точка t(α; n-m-1) вычисляется с помощью функции СТЬЮДРАСПОБР(0,05;n-m-1) . Если i -ое значение P-значения меньше a, то i -ый коэффициент статистически значим и влияет на результативный признак.
д) Коэффициент детерминации R-квадрат в блоке Регрессионная статистика. Скорректированный (нормированный) коэффициент детерминации R2n. Это означает, что модель объясняет R2n*100% общего разброса значений результативного признака с учетом поправки на число степеней свободы.
Проверка гипотезы о статистической значимости коэффициента детерминации:
Проводим правостороннюю проверку. Граничная точка Fα;n-m-1 определяется с помощью функции FРАСПОБР(α;m;n-m-1) .
Статистика F (определяется из блока Дисперсионный анализ).
Если F> Fα;n-m-1, то гипотеза отвергается H и принимает гипотеза H1 на уровне значимости α%.
Этот вывод подтверждает число из столбца Значимость F, которое должно быть меньше значения a.

  1. Среднее значение: СРЗНАЧ(диапазон)
  2. Квадратическое отклонение: КВАДРОТКЛ(диапазон)
  3. Дисперсия: ДИСП(диапазон)
  4. Дисперсия для генеральной совокупности: ДИСПР(диапазон)
  5. Среднеквадратическое отклонение: СТАНДОТКЛОН(диапазон)
  6. Уравнение регрессии y = b1x1+b2x2+. bnxn+b: ЛИНЕЙН(диапазон Y;диапазон X;1;1) .

  • Выделите блок ячеек размером (n+1) столбцов и 5 строк.

Методические пояснения. 1. Для вычисления коэффициентов регрессии воспользуйтесь встроенной функцией ЛИНЕЙН (функция находится в категории «Статистические»), обратите внимание, что эта функция является функцией массива, поэтому ее использование подразумевает выполнение следующих шагов:
1) В свободном месте рабочего листа выделите область ячеек размером 5 строк и 2 столбца для вывода результатов;
2) В Мастере функций (категория «Статистические») выберите функцию ЛИНЕЙН .
3) Заполните поля аргументов функции:
Известные_значения_y адреса ячеек, содержащих значения признака ;
Известные_значения_x адреса ячеек, содержащих значения фактора ;
Константа — значение (логическое), указывающее на наличие свободного члена в уравнении регрессии: укажите в поле Константа значение 1, тогда свободный член рассчитывается обычным образом (если значение поля Константа равно 0, то свободный член полагается равным 0);
Статистика — значение (логическое), которое указывает на то, следует ли выводить дополнительную информацию по регрессионному анализу или нет: укажите в поле Статистика значение равное 1, тогда будет выводиться дополнительная регрессионная информация (если Статистика=0, то выводятся только оценки коэффициентов уравнения регрессии);
4) После того, как будут заполнены все аргументы функции, нажмите комбинацию клавиш <CTRL>+<SHIFT>+<ENTER> .
Результаты расчета параметров регрессионной модели будут выведены в виде следующей таблицы:

Значение коэффициента b Значение коэффициента a
Стандартная ошибка mb коэффициента b Стандартная ошибка ma коэффициента a
Коэффициент детерминации R 2 Стандартное отклонение остатков Sост
Значение Fстатистики Число степеней свободы, равное n-2
Регрессионная сумма квадратов Остаточная сумма квадратов

2. Табличные значения распределения Стьюдента определите с помощью функции СТЬЮДРАСПОБР. Аргументы этой функции:
Вероятность — уровень значимости α (можно принять равным 0,05, т.е. 5%);
Степени_свободы — число степеней свободы, для парной линейной регрессии равно n-2, где n — число наблюдений.
3. Табличное значение распределения Фишера определите с помощью функции FРАСПОБР. Аргументы этой функции:
Вероятность — уровень значимости α (можно принять равным 0,05, т.е. 5%);
Степени_свободы1 — число степеней свободы числителя, для парной регрессии равно 1 (т.к. один фактор);
Степени_свободы2 — число степеней свободы знаменателя, для парной регрессии равно n-2, где n — число наблюдений.
4. Коэффициент корреляции вычислите с помощью функции КОРРЕЛ. Аргументы функции:
Массив 1ш и Массив 2 — адреса ячеек, в которых содержатся значения величин, для которых вычисляется коэффициент корреляции.
5. Для вычисления (X T X) -1
1) Построите матрицу .
2) Постройте транспонированную к ней матрицу X T . Для построения матрицы X T необходимо воспользоваться функцией ТРАНСП (категория Ссылки и массивы).
3) матрицу X T необходимо умножить на матрицу X;
Произведение матриц вычисляется с помощью функции МУМНОЖ, аргументами которой являются перемножаемые матрицы. Перемножаемые матрицы должны удовлетворять условию соответствия размеров: матрица размера mxn может быть умножена справа на матрицу размера nxk, в результате получится матрица размера mxk.
В случае множественной регрессии с тремя факторами матрица X будет иметь размер nx4, матрица X T — размер 4xn, а их произведение X T X — размер 4×4.
Функция МУМНОЖ является функцией массива! Поэтому перед использованием функции МУМНОЖ необходимо выделить область размером mxk, в которой будет выведен результат, затем вставить функцию МУМНОЖ, указав ее аргументы. После этого в левой верхней ячейке выделенной области появится первый элемент результирующей матрицы. Для вывода всей матрицы нажмите комбинацию клавиш <CTRL>+<SHIFT>+<ENTER> .
4) найти обратную матрицу (X T X) -1 ;
Обратную матрицу (X T X) -1 вычислите с помощью функции МОБР . Функция МОБР также является функцией массива и ее использование аналогично функции МУМНОЖ: сначала необходимо выделить область ячеек, в которой будет получена обратная матрица, вставить функцию МОБР, затем <CTRL>+<SHIFT>+<ENTER> .

6. Коэффициенты множественной линейной регрессии вычисляются с помощью функции ЛИНЕЙН . Для того чтобы использовать эту функцию для вычисления параметров множественной регрессии необходимо
1) Сначала выделить на рабочем листе область размером 5x(k+1), где k — число объясняющих переменных.
2) Затем заполнить поля аргументов этой функции, которые имеют тот же смысл, что и в случае парной регрессии:
Известные_значения_y адреса ячеек, содержащих значения признака y;
Известные_значения_x адреса ячеек, содержащих значения всех объясняющих переменных.
Обратите внимание: выборочные значения факторов должны располагаться рядом друг с другом (в смежной области), причем предполагается, что в первом столбце (строке) содержатся значения первой объясняющей переменной, во втором столбце — второй и т.д.
Константа значение (логическое), указывающее на наличие свободного члена в уравнении регрессии: укажите в поле Константа значение 1, тогда свободный член рассчитывается обычным образом (если значение поля Константа равно 0, то свободный член полагается равным 0);
Статистика значение (логическое), которое указывает на то, следует ли выводить дополнительную информацию по регрессионному анализу или нет: укажите в поле Статистика значение равное 1, тогда будет выводиться дополнительная регрессионная информация (если Статистика=0, то выводятся только оценки коэффициентов уравнения регрессии);

Как выполнить расчет Z-теста в Excel (пошаговый пример)

Excel Z TEST — это своего рода проверка гипотез, которая используется для проверки альтернативной гипотезы против нулевой гипотезы. Нулевая гипотеза — это гипотеза, которая относится к общему утверждению в целом. Путем проверки гипотезы мы пытаемся доказать, что нулевая гипотеза ложна против альтернативной гипотезы.

Z-ТЕСТ — одна из таких функций проверки гипотез. Это проверяет среднее значение двух наборов данных выборки, когда дисперсия известна и размер выборки большой. Размер выборки должен быть> = 30, иначе нам нужно использовать T-TEST. Для ZTEST нам нужно иметь две независимые точки данных, которые не связаны друг с другом или не влияют друг на друга, и данные должны быть нормально распределены.

Синтаксис

Z.TEST — это встроенная функция в Excel. Ниже приведена формула функции Z.TEST в excel.

  • Массив: это диапазон ячеек, содержащий точки данных, по которым нам нужно проверить X. Это значение ячеек по сравнению со средним значением выборки гипотез, которое необходимо проверить.
  • X: Из массива проверяемое значение X.
  • Сигма: это стандартное отклонение для всего населения. Это необязательный аргумент, если он не указан, используйте стандартное отклонение образца.

Как выполнить тест Z в Excel? (с примерами)

Пример # 1 — Использование формулы Z-теста

Например, посмотрите на данные ниже.

Используя эти данные, мы рассчитаем одностороннее значение вероятности Z TEST. Для этого предположим, что среднее значение гипотезы равно 6.

  • Шаг 1. Итак, откройте формулу Z TEST в ячейке Excel.

  • Шаг 2: Выберите массив в качестве баллов, то есть от A2 до A11.

  • Шаг 3: Следующий аргумент — «X» . Поскольку мы уже предположили, что предполагаемое среднее значение совокупности равно 6, примените это значение к этому аргументу.

  • Шаг 4. Последний аргумент является необязательным, поэтому закройте формулу, чтобы получить значение Z TEST.

  • Шаг 5: Это одностороннее значение Z TEST, чтобы получить двустороннее значение Z TEST для умножения этого значения на 2.

Пример # 2 — Z-ТЕСТ с использованием опции анализа данных

Мы можем провести Z TEST, используя опцию анализа данных в Excel. Чтобы сравнить два средних значения, когда дисперсия известна, мы используем Z TEST. Здесь мы можем сформулировать две гипотезы, одна — «Нулевая гипотеза», а другая — «Альтернативная гипотеза», ниже приводится уравнение обеих этих гипотез.

H0: μ1 — μ2 = 0 (нулевая гипотеза)

H1: μ1 — μ2 ≠ 0 (альтернативная гипотеза)

Альтернативная гипотеза (H1) утверждает, что два средних значения совокупности не равны.

В этом примере мы будем использовать результаты двух учеников по нескольким предметам.

  • Шаг 1: Первое, что нам нужно сделать, это вычислить переменные для этих двух значений с помощью функции VAR.P.

  • Шаг 2: Теперь перейдите на вкладку «Данные» и нажмите «Анализ данных».

Прокрутите вниз и выберите z-Test Two Sample для средних и нажмите Ok.

  • Шаг 3. Для диапазона переменной 1 выберите баллы «Студент 1», а для диапазона переменной 2 выберите баллы «Студент 2».

  • Шаг 4: Переменная 1 Дисперсия выберите Оценка дисперсии учащегося 1 и Переменная 1 Дисперсия выберите Оценка дисперсии учащегося 2.

  • Шаг 5: Выберите диапазон вывода в качестве ячейки и нажмите ОК.

мы получили результат.

Если Z <- Z Critical Two Tailor Z> Z Critical Two Tail, то мы можем отклонить нулевую гипотезу.

Итак, из результата ZTEST ниже приведены результаты.

  • Z <- Z Критический два хвоста = -1,080775083> — 1,959963985
  • Z> Z Критический двуххвостый = -1,080775083 <1,959963985

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

Назначение корреляционного анализа

Корреляционный анализ позволяет найти зависимость одного показателя от другого, и в случае ее обнаружения – вычислить коэффициент корреляции (степень взаимосвязи), который может принимать значения от -1 до +1:

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

Сила зависимости определяется по модулю коэффициента корреляции. Чем больше значение, тем сильнее изменение одной величины влияет на другую. Исходя из этого, при нулевом коэффициенте можно утверждать, что взаимосвязь отсутствует.

Построение графиков. Исследование статистических функций

Задание 1. Построить график плотности распределения хи-квадрат, протабулировав эту функцию на отрезке от 0 до 10 с шагом 0,2 и взяв степень
свободы k=5. Проанализировать зависимость параметра распределения k на график.
Решение:
Для построения графика функции, зададим значения аргумента х по формуле:
, где i=0,…,n-1, х0=0.
Значения функции плотности распределения хи-квадрат находим с помощью функции MS Excel «ХИ2РАСП».

Рис. 2.1
Проследим как влияет параметр распределения k на график. Для этого построим дополнительно два графика для k=8 и k=15 (при этом же изменении аргумента).
Из рис. 2.1 видим, что с ростом значения параметра k происходит растяжение графика вдоль оси Ох, т.е. на одном и том же отрезке график функции с большим значением параметра более пологий.

Задание 2. Построить график плотности распределения Стьюдента, протабулировав эту функцию на отрезке от 0 до 7 с шагом 0,2 и взяв степень
свободы k=4. Проанализировать зависимость параметра распределения k на график.
Решение:
Значения функции плотности распределения Стьюдента находим с помощью функции MS Excel «СТЬЮДРАСП».

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

Задание 3. Построить график плотности распределения Фишера, протабулировав эту функцию на отрезке от 0 до 5 с шагом 0,2 и взяв степени
свободы m=4 и n=5. Проанализировать зависимость параметров распределения m и n на график.
Решение:
Значения функции плотности распределения Фишера находим с помощью функции MS Excel «FРАСП».

Рис. 2.4
Влияние параметров распределения m и n на график отображено на рис. 2.3 и 2.4.

Порядок расчетов аналогичен предложенному выше.

Массив — это блок значений или интервал ячеек с числовыми значениями, для которых определяется значения квартилей.
Таблица 11 – Значения квартиля, которые необходимо рассчитать

  • Если массив пуст или содержит более 8191 точек данных, то функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!.
  • Если значение не целое, то оно усекается.
  • Если значение 4, то функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!.
  • МИН, МЕДИАНА и МАКС возвращают то же значение, что и функция КВАРТИЛЬ, если аргумент значение равен 0 (нулю), 2 или 4 соответственно.

Показатели формы распределения. Асимметрия и эксцесс

. (3.24)

Нетрудно проверить, что , , где — выборочная средняя.

Выборочный центральный момент k-го порядка определяется формулой

. (3.25)

Из определения (3.25) следует

, , , , (3.26)

где s 2 — выборочная дисперсия.

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

(3.27)

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

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

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

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

Массив — диапазон ячеек с выборочными данными, для которых вычисляется асимметрия.

Если имеется менее трех точек данных, или стандартное отклонение равно нулю, то функция СКОС возвращает значение ошибки «#ДЕЛ/0!».

Значение функции СКОС() в программе Excel вычисляется по формуле

, (3.28)

которая дает несмещенную состоятельную оценку асимметрии генеральной совокупности.

Выборочным эксцессом распределения называется величина

(3.29)

Для нормального распределения эксцесс равен нулю.

Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение. Отрицательный эксцесс обозначает относительно сглаженное распределение.

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

Массив — Диапазон ячеек, содержащий ряд.

Если задано менее четырех точек данных или если стандартное отклонение выборки равняется нулю, то функция ЭКСЦЕСС возвращает значение ошибки «#ДЕЛ/0!».

Значение функции ЭКСЦЕСС() в программе Excel вычисляется по следующей формуле

. (3.30)

Формула (3.30) дает несмещенную состоятельную оценку эксцесса генеральной совокупности.

Пример 3.11. Вычислить асимметрию и эксцесс для данного вариационного ряда (табл.3.5).

xi 2,4 2,8 3,2 3,6 4,0 4,4 4,8 5,2 5,6
ni

Решение.Введите исходные данные в диапазоне А1:В10, как показано на рис.3.11.

В ячейку В11 введите формулу =СУММ(B2:B10) для посчета суммы частот.

Для вычисления среднего значения в ячейку D2 введите формулу

Для вычисления среднеквадратического отклонения в ячейку D3 введите формулу

и нажмите комбинацию клавиш Ctrl + Shift + Enter, так как в параметрах функции СУММКВ(A2:A10*B2:B10-D2) используются операции с массивами.

Выражение A2:A10*B2:B10-D2 представляет операции с массивами, оно означает массив из девяти ячеек, значения который соответственно равны значениям выражений А2*В2-D2, А3*В3-D2, …, А10*В10-D2.

Для вычисления асимметрии в D5 введите формулу

и нажмите комбинацию клавиш Ctrl + Shift + Enter.

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

и нажмите комбинацию клавиш Ctrl + Shift + Enter.

Выводы. Асимметрия вариационного ряда положительна, это значит, что большая часть вариантов находится справа от среднего.

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

Для контроля правильности расчетных формул можно сравнить вычисляемые значения со значениями функций СКОС и ЭКСЦЕСС. Однако эти функции не применимы к вариационному ряду, их можно применить к несгруппированной выборке.

Замените в столбце В2:В10 числа на единицы, в столбце А2:А10 введите любые числа, а в ячейках D6 и D9 введите формулы =СКОС(А2:А10) и =ЭКСЦЕСС(А2:А10).

Получим результат, показанный на рис.3.12. В ячейках D5, D6 должны быть одинаковые значения, также как и в ячейках D8, D9.

Замечание. Для сгруппированной выборки при вычислении асимметрии и эксцесса нам пришлось вводить громоздкие формулы, так как функции СКОС и ЭКСЦЕСС применимы только к несгруппированной выборке.

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

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

Шаг 1: расчет стандартного отклонения

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

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)

  1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.

Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».

Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

    Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».

В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».

Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».

Шаг 3: нахождение коэффициента вариации

Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.

    Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.

Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.

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

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

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

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в 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 1

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

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

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

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

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

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

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

Основная идея

Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

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

Первое, что обычно приходит в голову – это рассчитать классический средний балл с помощью стандартной функции Excel СРЗНАЧ (AVERAGE).

На первый взгляд кажется, что лучше всех подходит Иван, т.к. у него средний бал максимальный. Но тут мы вовремя вспоминаем, что факультет-то наш называется "Программирование", а у Ивана хорошие оценки только по рисованию, пению и прочей физкультуре, а по математике и информатике как раз не очень. Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение.

Средневзвешенное – это среднее с учетом различной ценности (веса, важности) каждого из элементов.

В бизнесе средневзвешенное часто используется в таких задачах, как:

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

Расчет средневзвешенного формулами

Добавим к нашей таблице еще один столбец, где укажем некие безразмерные баллы важности каждого предмета по шкале, например, от 0 до 9 при поступлении на наш факультет программирования. Затем расчитаем средневзвешенный бал для каждого абитурента, т.е. среднее с учетом веса каждого предмета. Нужная нам формула будет выглядеть так:

Функция СУММПРОИЗВ (SUMPRODUCT) попарно перемножает друг на друга ячейки в двух указанных диапазонах – оценки абитурента и вес каждого предмета – а затем суммирует все полученные произведения. Потом полученная сумма делится на сумму всех баллов важности, чтобы усреднить результат. Вот и вся премудрость.

Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

Расчет средневзвешенного в сводной таблице

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

Обратите внимание, что я преобразовал ее в "умную" таблицу с помощью команды Главная – Форматировать как таблицу (Home – Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

Заметьте, что цена на один и тот же товар может различаться. Наша задача: рассчитать средневзвешенные цены для каждого товара. Следуя той же логике, что и в предыдущем пункте, например, для земляники, которая продавалась 3 раза, это должно быть:

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

Правда, с реализацией этой нехитрой логики именно в сводной таблице нас ждет небольшой облом. Если вы работали со сводными раньше, то, наверное, помните, что можно легко переключить поле значений сводной в нужную нам функцию, щелкнув по нему правой кнопкой мыши и выбрав команду Итоги по (Summarize Values By) :

В этом списке есть среднее, но нет средневзвешенного 🙁

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

Теперь можно рядом закинуть в область значений стоимость и количество – и мы получим почти то, что требуется:

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ – Поля, элементы, наборы – Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

В общем, как-то все неудобно, трудоемко и нагоняет тоску. Да еще и дополнительный столбец в исходных данных нужно руками делать. Но красивое решение есть.

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных – надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями – как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model) . Откроется окно Power Pivot и в нем появится наша таблица.

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Несколько нюансов по формуле:

  • В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула – на языке DAX.
  • Здесь WA – это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average – "средневзвешенное" по-английски).
  • Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
  • При вводе формулы будут выпадать подсказки – используйте их.
  • После завершения ввода формулы нужно нажать Enter , как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная – Сводная таблица (Home – Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений – и задача решена:

Вот так – красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности "тесноваты" – копайте в сторону Power Pivot и DAX – и будет вам счастье!

Значения показателя VAR в инвестиционном анализе

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

Нормативного значения этого показателя не существует. Однако имеются некоторые опорные критерии, которые помогают при его анализе и интерпретации.

Важный момент! Коэффициент CV имеет несколько недостатков — он не учитывает величины первоначальных вложений, предполагает симметричность разбросанных значений по отношению к среднему, а также не может использоваться для опционов, доходность которых может быть меньше 0. Оттого при наличии сомнений стоит дополнительно использовать показатели IRR и NPV.

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

Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.

Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

— Среднеквадратическое отклонение по генеральной совокупности СТАНДОТКЛОН.Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

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

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднее квадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

РАСЧЕТ ПОКАЗАТЕЛЕЙ ВАРИАЦИИ В MS EXCEL

Создать файл с исходными данными, по которым необходимо рассчитать показатели вариации, реализованные в MS Excel.

Определить дисперсию.

В MS Excel расчет дисперсии возможен с использованием статистических функций ДИСП, ДИСПА, ДИСПР, ДИСПРА.

ДИСП — оценивает дисперсию по выборке.

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.

ДИСП предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, вычисляйте дисперсию, используя функцию ДИСПР.

Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, пользуйтесь функцией рабочего листа ДИСПА.

ДИСП использует следующую формулу:

ДИСПА — оценивает дисперсию по выборке. В расчете помимо численных значений учитываются также текстовые и логические значения, такие, как ИСТИНА или ЛОЖЬ.

Значение1, значение2. — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.

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

Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие текст или значение ЛОЖЬ интерпретируются как 0 (ноль). Если текст и логические значения должны игнорироваться, следует использовать функцию рабочего листа ДИСП.

ДИСПА использует следующую формулу:

ДИСПР — вычисляет дисперсию для генеральной совокупности.

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности.

Логические значения, например ИСТИНА и ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, используйте функцию листа Excel ДИСПРА.

ДИСПР предполагает, что аргументы представляют всю генеральную совокупность. Если данные представляют только выборку из генеральной совокупности, то дисперсию следует вычислять, используя функцию ДИСП.

Уравнение для ДИСПР имеет следующий вид:

ДИСПРА — вычисляет дисперсию для генеральной совокупности. В расчете помимо численных значений учитываются также текстовые и логические значения, такие как ИСТИНА или ЛОЖЬ.

Значение1,значение2. — это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности.

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

Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие текст или значение ЛОЖЬ интерпретируются как 0 (ноль). Если текст и логические значения должны игнорироваться, следует использовать функцию рабочего листа ДИСПР.

ДИСПРА использует следующую формулу:

Учитывая специфику исходной информации, необходимо выбрать соответствующую функцию для расчета дисперсии и осуществить его в следующем порядке:

а) активизируйте ячейку, в которую вы хотите поместить значение дисперсии;

б) войдите в “Мастер функций”;

в) выберите категорию “Статистические”;

г
) выберите необходимую функцию дисперсии;

Рисунок 15 – Выбор необходимой функции

д) после того, как выбор будет закончен, нажмите ОК;

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

Рисунок 16 – Определение диапазона исходных данных

Определить среднее линейное отклонение.

В MS Excel среднее линейное отклонение определяется с использованием функции СРОТКЛ.

СРОТКЛ — возвращает среднее абсолютных значений отклонений точек данных от среднего. СРОТКЛ является мерой разброса множества данных.

Число1, число2, . — это от 1 до 30 аргументов, для которых определяется среднее абсолютных отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.

Уравнение для среднего отклонения следующее:

На результат СРОТКЛ влияют единицы измерения входных данных.

а) активизируйте ячейку для размещения значения СРОТКЛ;

б) войдите в “Мастер функций”;

в) выберите категорию “Статистические”;

г) выберите функцию СРОТКЛ (Рисунок 17);

е) укажите блок значений исходных данных, по которым будет производиться расчет (Рисунок 18);

Р
исунок 17 – Выбор функции СРОТКЛ

Р
исунок 18 – Определение диапазона исходных данных

Определить среднее квадратическое отклонение.

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

СТАНДОТКЛОН — оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию рабочего листа СТАНДОТКЛОНА.

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

Стандартное отклонение вычисляется с использованием "несмещенного" или "n — 1" метода.

СТАНДОТКЛОН использует следующую формулу:

СТАНДОТКЛОНА — оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего. В расчете также учитываются текстовые и логические значения, такие как ИСТИНА или ЛОЖЬ.

Значение1, значение2. — это от 1 до 30 значений, соответствующих выборке из генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

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

Аргументы, содержащие значение ИСТИНА, интерпретируются как 1. Аргументы, содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль). Если текст и логические значения должны игнорироваться, следует использовать функцию рабочего листа СТАНДОТКЛОН.

Стандартное отклонение вычисляется с использованием "не Байесовского" или "n — 1" метода.

СТАНДОТКЛОНА использует следующую формулу:

СТАНДОТКЛОНП — Вычисляет стандартное отклонение по генеральной совокупности. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию рабочего листа СТАНДОТКЛОНА.

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

Для больших выборок СТАНДОТКЛОН и СТАНДОТКЛОНП возвращают примерно равные значения.

Стандартное отклонение вычисляется с использованием "смещенного" или "n" метода.

СТАНДОТКЛОНП использует следующую формулу:

СТАНДОТКЛОНПА — вычисляет стандартное отклонение по генеральной совокупности, заданной аргументами, которые могут включать текст и логические значения. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

Значение1,значение2. это от 1 до 30 значений, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

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

Аргументы, содержащие значение ИСТИНА, интерпретируются как 1, аргументы, содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль). Если текст и логические значения должны игнорироваться, следует использовать функцию рабочего листа СТАНДОТКЛОНП.

Для больших выборок СТАНДОТКЛОНА и СТАНДОТКЛОНПА возвращают примерно равные значения.

Стандартное отклонение вычисляется с использование "Байесовского" или "n" метода.

СТАНДОТКЛОНПА использует следующую формулу:

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

Определить эксцесс и коэффициент асимметрии.

В MS Excel расчет эксцесса и коэффициента асимметрии реализован с помощью функций ЭКСЦЕСС И СКОС.

СКОС — Возвращает асимметрию распределения. Асимметрия характеризует степень несимметричности распределения относительно его среднего. Положительная асимметрия указывает на отклонение распределения в сторону положительных значений. Отрицательная асимметрия указывает на отклонение распределения в сторону отрицательных значений.

Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется асимметричность. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.

Если имеется менее трех точек данных, или стандартное отклонение равно нулю, то функция СКОС возвращает значение ошибки #ДЕЛ/0!.

Уравнение для асимметрии определяется следующим образом:

где — стандартное отклонение выборки.

ЭКСЦЕСС — Возвращает эксцесс множества данных. Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение. Отрицательный эксцесс обозначает относительно сглаженное распределение.

Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется эксцесс. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.

Если задано менее четырех точек данных или если стандартное отклонение выборки равняется нулю, то функция ЭКСЦЕСС возвращает значение ошибки #ДЕЛ/0!.

Как сделать XYZ анализ?

Теперь сегментируем наши коэффициенты вариации и присваиваем каждому одну из 3-х букв X Y и Z

  • X — для рядов с коэффициентом вариации от 0% до 10%
  • Y — для рядов с коэффициентом вариации от 10% до 25%
  • Z — для рядов с коэффициентом вариации от 25% и больше

Вводим в ячейку Excel формулу

N3 — ссылка на коэффициент вариации

XYZ анализ

7. Асимметрия и эксцесс эмпирического распределения

В предыдущих статьях мы познакомились с показателями центральной тенденции и вариации, и сейчас рассмотрим ещё пару характеристик статистической совокупности. Для тех, кто зашёл с поисковика и хочет изучить тему с азов, сразу ссылка на организационный урок: Математическая статистика для «чайников», там же, в конце, список всех статей курса. И до статьи 7-й – как рукой подать, после чего будет небольшой и очень приятый экзамен.

Итак, что такое асимметрия и эксцесс? Говоря простым языком, это показатели, характеризующие геометрическую форму распределения. Асимметрия характеризует меру скошенности графика влево / вправо, а эксцесс – меру его высоты.

Данные показатели рассчитываются как для эмпирических, так и для теоретических распределений, которые мы изучили в курсе теории вероятностей, и за «эталон» симметрии принято нормальное распределение:

Очевидно, что любое нормальное распределение строго симметрично относительно своего центра, следовательно, его асимметрия равна нулю. Данный график кажется пологим, но стандартное отклонение в данном примере достаточно велико (см. на синие точки) и на самом деле такая высота «гармонична». Поэтому эксцесс нормального распределения (любого) принимают за «отправную» нулевую точку.

Почему именно нормальное распределение? Потому что философское – обязательно прочитайте эту интереснейшую статью по ссылке, если ещё не успели этого сделать!

В теории вероятностей существуют строгие формулы для вычисления коэффициентов асимметрии и эксцесса (будут ниже), но на практике мне такие задачи не встречались. И поэтому я сразу перехожу к статистике и распределениям эмпирическим, т.к. здесь таких задач как раз выше крыши. С положительным эксцессом, если выразиться тематически 🙂

Начнём с асимметрии. Асимметрия характеризует меру скошенности полигона или гистограммы влево / вправо относительно самого высокого участка, и во многих случаях для «прикидки» асимметрии достаточно взглянуть на соответствующие чертежи. Так, например, посмотрим на полигон частот из Примера 8:

И, в принципе, тут всё видно – пациент скорее симметричен, чем асимметричен 🙂

Простейшим критерием симметрии является равенство средней, моды и медианы: но в жизни такого идеального совпадения, конечно, не бывает (даже тело человека немного асимметрично), и поэтому у «почти симметричных» распределений эти показатели должны располагаться очень близко друг к другу. И в самом деле, как мы вычислили в Примере 8: .

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

Правосторонняя асимметрия характеризуется удлинённым правым «хвостом», смотрим на гистограмму Примера 10:

Простейшим признаком правосторонней асимметрии является тот факт, что , и это неудивительно – ведь справа находится значительное количество вариант, и поэтому средняя смещена вправо. И поэтому английский статистик Карл Пирсон, который ещё не раз нас порадует своими методами, предложил следующую формулу для расчёта коэффициента асимметрии:

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

Левостороння асимметрия, наоборот, характеризуются удлинённым левый «хвостом» и неравенством ,…. картинки быстро не нашлось, поэтому просто разверну чертёж в графическом редакторе:

Из формулы следует, что в левостороннем случае коэффициент асимметрии отрицателен (т.к. ), а в правостороннем – положителен (), и чем больше по модулю – тем сильнее скос распределения.

Недостаток формулы Пирсона состоит в том, что она описывает лишь центральную часть распределения и практически не учитывает «периферию». И, чтобы вас томить, сразу продвинутая формула, которая охватывает все варианты, для определённости запишу её для выборочной совокупности объёма :

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

Смысл знаков тот же самый: если , то распределение скошено вправо, если – то влево. При этом принята следующая условная градация: если полученное значение по модулю меньше, чем 0,25, то асимметрия незначительна, если , то умеренная, и если , то существенная.

И чем МЕНЬШЕ по модулю , тем рассматриваемое эмпирическое распределение БЛИЖЕ к нормальному распределению с параметрами .

Справочно формулы теории вероятностей: асимметрия случайной величины рассчитывается по «родственной» формуле , где – среднее квадратическое отклонение, а – центральный теоретический момент 3-го порядка. Для дискретной случайной величины он рассчитывается так: , а для непрерывной – через интеграл: .

Теперь об эксцессе замолвим слово. Он характеризует высоту и очень коварный. В том смысле, что глаза будут часто обманывать. Так, например, посмотрим на чертёж Примера 7 из статьи об интервальном вариационном ряде:

Ну видно же – гистограмма и полигон серьёзно вытянуты вверх. Но это только кажется. Дело в том, что стандартное отклонение этого распределения невелико, и для сего небольшого рассеяния такая высота ДАЖЕ МАЛА. МалА – по сравнению с «эталонным» нормальным распределением с параметрами .

Поэтому аналитика и ещё раз аналитика. Коэффициент эксцесса эмпирического распределения рассчитывается по формуле:

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

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

Если , то эмпирическое распределение является более высоким («островершинным») – относительно «эталонного» нормального распределения с параметрами . Если же – то более низким и пологим. И чем больше по модулю, тем «аномальнее» высота в ту или иную сторону.

В примере выше, как ни странно, , и сейчас мы убедимся в этом аналитически:

Итак, сто пачек чая из Примера 7 ( – середины интервалов):

и нам требуется вычислить коэффициенты асимметрии и эксцесса

Решение: поскольку в формулах асимметрии и эксцесса фигурирует стандартное отклонение, то сначала нужно рассчитать выборочную среднюю и дисперсию.

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

Ловкость рук и никаких трудностей, вы удивитесь, как всё быстро:

Собственно, финальные расчёты:

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

Центральные моменты 3-го и 4-го порядков:

И, наконец, коэффициенты. Вычислим коэффициент асимметрии:
, то есть, распределение обладает существенной правосторонней асимметрией, что, кстати, хорошо было видно по чертежу.

Вычислим коэффициент эксцесса:
– вот оно как! Оказывается, распределение не то что выше, а заметно ниже, чем нормальное распределение с параметрами

Ответ:

Вот такой вот у нас получился эксекас 🙂

Помимо геометрических форм, эти коэффициенты позволяют «прикинуть», насколько близка к нормальному распределению не только выборочная, но и вся генеральная совокупность. Это одна из важнейших задач статистики, которую мы разберём в разделе Статистические гипотезы.

Ну а сейчас я предлагаю вам небольшое экзаменационное задание по первым семи урокам. Оно типично для студенческой практики – дана статистическая совокупность, и требуется выполнить много-много чего. Внимательно проверьте, всё ли вы усвоили, всё ли умеете:

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

…это ещё ерунда 🙂

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

1) Составить интервальный вариационный ряд, состоящий из 9 равных интервалов. Видео в помощь.

3) Найти моду и медиану.

5) Вычислить коэффициенты асимметрии и эксцесса, сделать выводы.

Не тушуйтесь – я с вами! Краткое решение для сверки внизу страницы.

И на этом, как вы правильно догадались, дело не заканчивается, поэтому сохраните файл с решением! Типовая задача содержит больше пунктов, и после изучения гипотезы о законе распределения генеральной совокупности, выполняем следующие задания:

6) По найденным характеристикам сделать вывод о форме эмпирического ряда распределения.

7) Построить нормальную кривую по опытным данным на графике гистограммы.

8) Произвести оценку степени близости теоретического распределения эмпирическому ряду с помощью критерия согласия Пирсона на уровне значимости 0,05.

До скорых встреч!

Решения и ответы:

Пример 20. Решение:

1) По статистическим данным находим: , .
Вычислим размах вариации: ед.
По условию, выборку следует разделить на равных интервалов, таким образом, длина частичного интервала:
ед.
Разметим интервалы и подсчитаем частоты по каждому интервалу, после чего убедимся, что объём выборки . Вычислим относительные частоты и относительные накопленные частоты :

2) Построим гистограмму относительных частот:

и эмпирическую функцию распределения:

3) Моду вычислим по формуле , в данном случае:
– нижняя граница модального интервала;
– длина модального интервала;
– частота модального интервала;
– частота предыдущего интервала;
– частота следующего интервала.

Таким образом:
ед.

Медиану вычислим по формуле , в данном случае:
– объём выборочной совокупности;
половину вариант содержит интервал и – его нижняя граница;
– длина медианного интервала;
– частота медианного интервала;
– накопленная частота предыдущего интервала.

Таким образом:
ед.

4) Найдём середины интервалов, произведения и вычислим выборочную среднюю ед., после чего заполним оставшуюся часть таблицы и рассчитаем остальные показатели:

Выборочная дисперсия:
,
выборочное среднее квадратическое отклонение:
ед.,
коэффициент вариации:

5) Вычислим центральные эмпирические моменты 3-го и 4-го порядков:

и коэффициент эксцесса:

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

Автор: Емелин Александр

(Переход на главную страницу)

«Всё сдал!» — онлайн-сервис помощи студентам

XYZ-анализ в Excel: оценка динамики продаж

XYZ исследование позволит увидеть изменения спроса на продукцию компании.

Выгружаем данные из учётной системы

Создаем таблицу с количеством продаж за 2020 год по каждой товарной группе по каждому кварталу.

XYZ-анализ в Excel: количество продаж по кварталам

XYZ-анализ в Excel: количество продаж по кварталам

Рассчитываем коэффициент вариации

Вариация — степень разброса значений в числовой последовательности. Показывает насколько данные отклоняются от средних показателей. В финансах этот коэффициент оценивает изменчивость, волатильность, сезональнальность. Чем он меньше, тем стабильнее оцениваемый параметр (спрос на товар, движение по складу, платежи и т.д.).

Создаем графу Средние продажи. В строку 3 вводим формулу =СРЗНАЧ(B3:E3) и копируем ее для всех товарных позиций.

XYZ-анализ в Excel: формула расчёта средних продаж

XYZ-анализ в Excel: формула расчёта средних продаж

Создаем графу Стандартное отклонение. Стандартное отклонение / Средние продажи.

В строку 3 вводим формулу =СТАНДОТКЛОН(B3:E3) и копируем ее для всех товарных позиций.

XYZ-анализ в Excel: формула расчёта стандартного отклонения

XYZ-анализ в Excel: формула расчёта стандартного отклонения

Создаем графу Вариация, %. Вводим формулу:

Столбец Стандартное отклонение / Столбец Средние продажи

Присваиваем значения XZY и соединяем с ABC

Руководство утвердило матрицу XYZ аналитики:

Группа Диапазон
X — постоянный спрос до 15%
Y — изменчивый спрос, сезональность от 15% до 50%
Z — случайный спрос больше 50%

Ранжируем полученные результаты с помощью функции Excel «ЕСЛИ».

В ячейку J3 вводим формулу: =ЕСЛИ(I3<=15%;»X»;ЕСЛИ(I3>=50%;»Z»;»Y»)) . Копируем формулу по всем товарным срокам.

Создаем графу Группа по методу ABC. Подтягиваем код группы из таблицы ABC анализа с помощью формулы: =ВПР(A3;ABC!$A$1:$G$12;7;0)

Как настроить формулу ВПР:

Задача функции: по коду товара в исходной таблице найти значение А, В или С и перенести его отчётную таблицу XYZ.

А3 — параметр, по которому ищем значение, например «Товар 6».

ABC!$A$1:$G$12 — ссылка на диапазон исходной таблицы. В ней строго в первом столбце должен быть параметр, по которому ищем значение «Товар 6».

7 — порядковый номер столбца, в котором в исходной находятся значения (коды А, В, С)

— значение ЛОЖЬ. Для Ecxel признак того, что искомый результат должен соответствовать всем 3-м предыдущим условиям.

По каждому товару получаем двойную кодировку ABC и XYZ аналитики.

Для наглядности можно скрепить лва кода по каждому товару.

В столбец L для каждой строки вводим формулу =K&J .

Товары AX — высокоприбыльные позиции, которые формируют 70% выручки. На них стабильный спрос.

Товары CZ — позиции с самым низким спросом. Сюда могут попасть как неликвиды, так и элитные товары с редким спросом. Требуется дополнительная аналитика.

Подробнее о сути, эффективности и недостатках ABC XYZ анализа читайте здесь.

Определение числовых характеристик по экспериментальным данным в табличном процессоре Excel

Рубрика Программирование, компьютеры и кибернетика
Вид лабораторная работа
Язык русский
Дата добавления 18.04.2013
Размер файла 31,4 K

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru/

ОПРЕДЕЛЕНИЕ ЧИСЛОВЫХ ХАРАКТЕРИСТИК ПО ЭКСПЕРИМЕНТАЛЬНЫМ ДАННЫМ В ТАБЛИЧНОМ ПРОЦЕССОРЕ EXCEL

1. Теоретические основы

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

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

На практике в психологии чаще всего используют именно дискретную случайную величину. Приведем пример: предположим, исследуется уровень интеллекта в какой-либо группе испытуемых. В результате эксперимента каждый из обследованных выдаст некое значение. Мы не можем заранее предсказать, какова будет величина этого значения: 70, 100, 130 баллов и т.п., — и потому наша величина СЛУЧАЙНА. Даже если людей в группе столько, что обязательно отыщутся те, у кого будет 71 балл, 72, 73, 74… и так далее — до 127, 128, 129, 130, то все равно не может быть человека, набравшего 129,5 балла или 71,5 балла — и потому наша случайная величина ДИСКРЕТНА.

Значения наблюдаемых в практике случайных величин более или менее колеблются около среднего значения. Это явление называется рассеянием величины около ее среднего значения. Числовые характеристики, характеризующие рассеяние случайной величины, называются характеристиками рассеивания, основными из которых являются дисперсия и средне квадратичное отклонение.

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

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

где pi — вероятность случайной величины.

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

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

Во многих экспериментах необходимо знать, как в среднем характеризуется данная исследуемая величина, какое, в среднем, она может принять значение. (Допустим, надо определить, каков, в среднем, показатель интеллекта в той или иной группе лиц.)Пусть известно, что средний показатель интеллекта в некой группе равен такому-то числу. Что можно сказать об интеллекте группы, на основании лишь знания этого среднего показателя? Решительно ничего. Ведь неизвестно, все ли значения тесно сгруппированы вокруг среднего (все демонстрируют средний интеллект) или половина показателей очень низких, а половина — очень высоких. Может статься, что большинство демонстрируют интеллект выше среднего, но показатели одного-двух человек так низки, что «тянут» назад всю группу. В каждом из трех вариантов может быть одно и то же среднее значение.

В таком случае требуется иметь такую характеристику, которая бы говорила о том, сколь велик разброс значений вокруг среднего, или сколь далеко, как правило, от среднего отстоит любое, случайно взятое, значение. Для ответа на этот вопрос и служит такая математическая величина, как ДИСПЕРСИЯ, то есть — мера рассеяния. Что касается среднеквадратического отклонения, то оно более удобно на практике, так как сохраняет размерность исследуемой величины.

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

Начальным моментом k-го порядка случайной величины X называют математическое ожидание величины x k :

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

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

Центральным моментом к-го порядка случайной величины X называют математическое ожидание величины (X-M(X)) k :

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

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

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

Третий центральный момент служит характеристикой асимметрии («скошенности») распределения. Так как третий центральный момент имеет размерность куба случайной величины, то обычно рассматривают безразмерную величину — отношение 3 к среднему квадратическому отклонению в третьей степени

величина носит название коэффициента асимметрии.

Четвертый центральный момент служит для характеристик островершинности или плосковершинности распределения. Эти свойства распределения описываются с помощью так называемого эксцесса. Эксцессом случайной величины Х называется величина

2. Задания на лабораторную работу

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

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

  • 1-й способ с помощью графика;
  • 2-й способ с помощью функции Excel =ЛИНЕЙН;
  • 3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

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

график полинома

На график добавляем полином 6-й степени.

добавляем линию тренда в Excel

polinom 6 stepeni

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

polinom na grafik

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

выделяем уравнение тренда

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

формат подписи полинома

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

формат подписи полинома

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

уравнение полинома

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

коэффициенты полинома

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

номер временного ряда для полинома

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

  • «известные значения y» (объёмы продаж за периоды),
  • «известные значения x» (порядковый номер временного ряда),
  • в константу ставим «1»,
  • в статистику «0»

Получаем следующего вида формулу:

Линейн формула Excel

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

  • ^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
  • ^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
  • ^ <1:2>— для расчета коэффициентов полинома 2-й степени

вводим степень полинома

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

ustanovit kursor

2. Нажать на клавишу F2

uravnenie polinoma 6stepeni 2sposob

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

uravnenie polinoma 6stepeni 2sposob

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

номер временного ряда для полинома

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

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

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

2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также этот расчет быстрее.

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

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

уравнение полинома

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

функция полинома

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

копируем полином

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

  1. Коэффициентов полиномиального тренда выведенных на график;
  2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
  3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

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