Технологии решения задач статистического анализа

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

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

В MS Excel для вычисления значений функции (7.1) используется встроенная функция ЧАСТОТА, а также инструмент Гистограмма из Пакета анализа.

Синтаксис функции: ЧАСТОТА (Массив_данпых; Массив_интервалов) — вычисляет распределение значений по интервалам и возвращает вертикальный массив, содержащий на один элемент больше, чем массив интервалов, где Массивданных — массив или ссылка на множество данных, для которых вычисляются частоты (пробелы или текст не учитываются), Массив интервалов — массив интервалов или ссылка на интервалы, в которых группируются значения из массива данных. Результатом вычисления является таблица и гистограмма.

Чтобы открыть инструмент Гистограмма, необходимо задать команду ДАННЫЕ / Анализ / Анализ данных и в открывшемся окне Анализ данных из списка выбрать пункт Гистограмма, откроется окно, как на рис. 7.14. Если на ленте ДАННЫЕ нет пакета анализа, то выполните команды из подпараграфа 7.2.1.

Рис. 7.14. Окно Гистограмма Диалоговое окно Гистограмма имеет следующие поля:

Входной интервал — поле, предназначенное для указания адресной ссылки на диапазон, содержащий исследуемые данные;

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

Выходной диапазон — поле для ввода адресной ссылки на верхнюю левую ячейку выходного диапазона;

• — флажок Интегральный процент устанавливает режим генерации интегральных процентных соотношений и включает в гистограмму график интегральных процентов;
• — флажок Вывод графика устанавливает режим автоматического вывода графика на рабочий лист, содержащий входной диапазон.

Пример 7.14.

Построить выборочную функцию распределения рейтинга студентов, но результатам экзаменов, оцененных по 100-балльной шкале для следующей произвольной выборки: 35, 84, 47, 95, 68, 58, 71, 74, 84, 78, 16, 98, 39, 100, 54* 75, 87, 68, 64, 79, 83, 94, 89, 0, 65, 88. Задач}' решить двумя способами: с применением встроенной функции ЧАСТОТА и с применением инструмента Гистограмма.

Решение

Первый способ

• 1. В диапазон ячеек A2: F3 введите шапку таблицы, как показано на рис. 7.15.
• 2. В диапазон ячеек А4: В16 введите числа из данной выборки.
• 3. В диапазон ячеек С4: С7 введите граничные числа для вывода 5-балльной оценки — 49, 69, 85, 100. Баллы от 0 до 49 соответствуют «2», баллы от 50 до 69 соответствуют «3», баллы от 70 до 85 соответствуют «4», баллы от 86 до 100 соответствуют «5».
• 4. Выделите диапазон ячеек D4: D7 и задайте команду ФОРМУЛЫ / Библиотека функций, далее нажмите кнопку Другие функции и из раскрывшегося списка выберите пункт Статистические, далее из раскрывшегося списка функций выберите функцию ЧАСТОТА.
• 5. В окне Аргументы функции для функции ЧАСТОТА введите в поле Массив_ данных — диапазон ячеек А4: В16, в поле Массивинтервалов — С4: С7.
• 5. Нажмите комбинацию клавиш Ctrl + Shift + Enter.
• 6. В ячейку Е4 введите формулу =D4/CyMM ($D$ 4:$D$ 7) для вычисления относительной частоты (относительная частота = абсолютная частота / количество элементов выборки). Скопируйте формулу в диапазон ячеек Е5: Е7.
• 7. В ячейку F4 введите формулу =Е4. В ячейку F5 — формулу =F4+E5 (накопленная частота = сумме относительных частот). Скопируйте формулу в диапазон ячеек F6: F7.
• 8. Но полученным данным постройте гистограмму относительных и накопленных частот. В результате будет получена гистограмма (см. рис. 7.15).

Рис. 7.15. Решение примера 7.14 с использованием функции ЧАСТОТА

Второй способ

• 1. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 2. В открывшемся окне Анализ данных из списка выберите пункт Гистограмма.
• 3. Заполните поля, как показано на рис. 7.16.
• 4. Нажмите кнопку ОК.

Рис. 7.16. Решение примера 7.14 с использованием инструмента Гистограмма

В результате будет получена таблица с относительными и интегральными (накопленными) частотами, а также гистограмма на новом листе (рис. 7.16). Как видим, результаты, полученные двумя способами, совпадают, что позволяет нам определить выборочную функцию в виде:

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

• — 1-я группа: функция СРЗНАЧ (число1; число2;…), которая возвращает среднее арифметическое из одного или нескольких чисел; СРГАРМ (число1; число2;…) — возвращает среднее гармоническое для множества положительных чисел — величину, обратную среднему арифметическому обратных величин; СРГЕОМ (число1; число2;…) — возвращает среднее геометрическое для массива или диапазона из положительных чисел; МОДА. ОДН (число1; число2;…) — возвращает количество наиболее часто встречающихся значений в выборке (наиболее вероятная величина);
• — 2-я группа: функция ДИСП. В (число1; число2;…) — возвращает дисперсию по выборке (логические и текстовые значения игнорируются); СТАНДОТКЛОН. В (число1; число2;.") — возвращает стандартное отклонение по выборке (логические и текстовые значения игнорируются); ПРОЦЕНТИЛЬ. ВКЛ (массив; к) возвращает k-й процентиль для значений диапазона, при k от 0 до 1 включительно;
• — 3-я группа: функция ЭКСЦЕСС (число1; число2;…) — возвращает эксцесс множества данных, т. е. частоты появления значений, удаленных от среднего значения; СКОС (число1; число2;…) — возвращает асимметрию распределения относительно среднего.

Пример 7.15.

Требуется найти среднее значение, стандартное отклонение и скос курсов валют ЦБ РФ (евро и доллар) с 08.07.2014 по 19.07.2014, представленных на рис. 7.17.

Решение

• 1. На рабочем листе в диапазон ячеек А1: С2 введите шапку таблицы, как показано на рис. 7.17.
• 2. В диапазон ячеек АЗ: С12 введите исходные данные за указанный период (см. рис. 7.17).

Рис. 7.17. Таблица для примера 7.15.

• 3. В ячейку В13 введите формулу =СРЗНАЧ (ВЗ:В12) для вычисления среднего значения.
• 4. В ячейку В14 введите формулу =СТАПДОТКЛОП.В (ВЗ:В12) для вычисления стандартного отклонения.
• 5. В ячейку В15 введите формулу =СКОС (ВЗ:В12) для вычисления скоса.
• 6. Скопируйте диапазон ячеек В13: В15 на диапазон ячеек С13: С15.

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

Пример 7.16.

По условиям примера 7.15 с помощью инструмента Описательная статистика вычислить основные статистические характеристики.

Решение

• 1. Выполните пункты 1, 2 из примера 7.15.
• 2. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 3. В открывшемся окне Анализ данных из списка выберите пункт Описательная статистика.
• 4. В окне Описательная статистика заполните поля, как показано на рис. 7.18. Переключатель По столбцам исходные данные группирует по столбцам. Флажок Метки в первой строке указывает, что в результирующую таблицу будут помещены заголовки столбцов исходной таблицы, а флажок в поле Итоговая статистика обеспечивает вывод статистических характеристик по каждому столбцу (рис. 7.19).

Рис. 7.18. Окно инструмента Описательная статистика

Рис. 7.19. Образец результатов вычислений для примера 7.16

В примере нет повторяющихся значений, соответственно, характеристика Мода не имеет действительного значения.

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

где Р — вероятность; у — надежность сделанной оценки. Соответственно интервал (r_t;r₂) называется доверительным интервалом.

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

Синтаксис функции: ДОВЕРИТ. НОРМ (Альфа; Станд откл; Размер) — возвращает доверительный интервал для среднего генеральной совокупности с использованием нормального распределения, где Альфа — уровень значимости, используемый для вычисления доверительного уровня — число (равно 1 -j)_y Станд_откл — стандартное отклонение генеральной совокупности для интервала данных (предполагается известным и больше нуля), Размер — размер выборки.

Синтаксис другой функции: ДОВЕРИТ. СТЫОДЕНТ (Альфа; Станд_ откл; Размер) — возвращает доверительный интервал для средней генеральной совокупности с использованием распределения Стьюдента, где параметры функции аналогичны функции ДОВЕРИТ.НОРМ.

Пример 7.17

Найти границы 95%-го интервала для среднего значения, если по результатам 10 торгов среднее значение стоимости евро составило 47 руб., а стандартное отклонение — 40 коп.

Решение

• 1. Установите курсор в произвольной ячейке рабочего листа, например в ячейке В2.
• 2. Задайте команду ФОРМУЛЫ / Библиотека функций, нажмите кнопку Другие функции и из раскрывшегося списка выберите пункт Статистические, далее из раскрывшегося списка функций выберите функцию ДОВЕРИТ.НОРМ.
• 3. В открывшемся окне Аргументы функции для функции ДОВЕРИТ.НОРМ введите исходные данные: Альфа — 0,05; станд_откл — 0,40; размер — 10.
• 4. Нажмите ОК.

В результате в ячейке В2 будет вычислена полуширина 95%-го доверительною интервала для среднего значения выборки — 0,25 руб., т.с. с 95%-м уровнем надежности можно утверждать, что средняя стоимость евро лежит в диапазоне от 46,75 до 47,25 руб.

Пример 7.18

Дана выборка стоимости доллара из Примера 7.15: 35,16; 34,8; 34,38; 34,37; 34,31; 34,06; 33,84; 34,08; 34,43; 34,6 (руб.). Необходимо определить границы 90%-го доверительного интервала для среднего с помощью инструмента Описательная статистика.

Решение

• 1. В рабочем листе в диапазон ячеек А2:А11 введите данные (стоимости доллара) выборки из примера 7.15.
• 2. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 3. В открывшемся окне Анализ данных из списка выберите пункт Описательная статистика.
• 4. В окне Описательная статистика заполните поля, как показано на рис. 7.20.

Рис. 7.20. Окно настройки для примера 7.18

5. Нажмите кнопку OK.

В результате в указанном диапазоне будут выведены данные (рис. 7.21). Границы 90%-го доверительного интервала — 33,84 и 35,16.

Рис. 7.21. Образец результатов вычислений для примера 7.18

Технология решения задач однофакторного дисперсионного анализа.

Методы однофакторного дисперсионного анализа применяются для вычисления достоверности различий между несколькими группами наблюдений. Этот метод служит для анализа дисперсии по данным двух или нескольких выборок. При анализе гипотеза о том, что каждый пример извлечен из одного и того же базового распределения вероятности, сравнивается с альтернативной гипотезой, предполагающей, что базовые распределения вероятности во всех выборках разные. Сравнивая компоненты дисперсии друг с другом посредством F-критерия Фишера, можно определить, значимо ли статистически различие между средними.

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

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

• — Однофакторный дисперсионный анализ;
• — Двухфакторный дисперсионный анализ с повторениями;
• — Двухфакторный дисперсионный анализ без повторений.

Пример 7.19

Необходимо определить влияние уровня рекламы внутри фирмы на объемы продаж. Данные продаж по 30 филиалам приведены в табл. 7.5.

Решение

• 1. На рабочем листе в диапазон ячеек А1: С2 введите шапку таблицы (рис. 7.22).
• 2. В диапазон ячеек АЗ:С12 введите исходные данные по продажам в разных филиалах, как показано на рис. 7.22.
• 3. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 4. В открывшемся окне Анализ данных из списка выберите пункт Однофакторный дисперсионный анализ.

Определение влияния уровня рекламы на объемы продаж по данным филиалов фирмы.

• 5. В окне Однофакторный дисперсионный анализ заполните поля, как показано на рис. 7.23.
• 6. Нажмите кнопку ОК.

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

Рис. 7.23. Окно настройки для примера 7.19.

В результате получим следующие статистические характеристики:

• — групповые — счет, сумму, среднее, дисперсию;
• — внутригрупповые — дисперсию, внутригрупповую дисперсию и дисперсию между группами (SS), средний квадрат эффекта и средний квадрат ошибки (MS), /^критерий Фишера, Р-значение.

Технология решения задач корреляционного анализа. Для исследования связей между несколькими наблюдаемыми переменными в статистике используется корреляционный анализ, который используется, когда данные наблюдений можно считать случайными и выбранными из генеральной совокупности, распределенной по многомерному нормальному закону. Корреляционный анализ позволяет проанализировать развитие ситуации в случае изменения конкретных характеристик изучаемого объекта или процесса. Главная цель корреляционного анализа — получение матрицы генеральной совокупности по выборке и определении на ее основе оценок коэффициентов корреляции между различными параметрами. Корреляционная матрица — это квадратная таблица, на пересечении соответствующих строк и столбцов которой располагаются корреляционные коэффициенты.

Средством для корреляционного анализа в Excel является инструмент Корреляция.

Пример 7.20

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

Таблица 7.6

Решение

• 1. На рабочем листе в диапазон ячеек A1 :D1 введите шапку таблицы (рис. 7.24). В диапазон ячеек A2:D7 введите исходные данные по посещениям выставок и городских парков, как показано на рис. 7.24. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 2. В открывшемся окне Анализ данных из списка выберите пункт Корреляция.
• 3. В окне Корреляция заполните поля, как показано на рис. 7.25.
• 4. Нажмите кнопку ОК.

Из полученной корреляционной матрицы видно, что коэффициент корреляции между количеством ясных дней и посещаемостью выставки г= 0,767 639 018, т. е. прямая линейная связь между количеством ясных дней в месяце и посещаемостью выставки не очень высокая. Коэффициент корреляции между количеством ясных дней и посещаемостью городского парка г — 0,992 202 052, т. е. существует сильная прямая линейная связь между количеством ясных дней в месяце и посещаемостью городского парка.

Рис. 7.24. Результаты вычислений для примера 7.20.

Рис. 7.25. Окно инструмента Корреляция для примера 7.20.

Технология решения задач регрессионного анализа. Регрессионный анализ позволяет прогнозировать значения зависимой переменной при изменении одной или нескольких независимых переменных (факторов). В случае одной независимой переменной говорится о простой линейной регрессии, в случае нескольких независимых переменных — о множественной регрессии.

В общем виде уравнение регрессии имеет вид:

где а_х, …, а_п — коэффициенты при независимых переменных, или коэффициенты регрессии; я₀ — константа.

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

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

Качество регрессионной прямой, т. е. степень соответствия между уравнением регрессии и исходными данными, оценивается с помощью коэффициента детерминации «Я-квадрат». При Л-квадрат > 0,95 точность приближения высокая, если 0,8 < /^-квадрат < 0,95, точность удовлетворительная. При Л-квадрат < 0,6 точность приближения недостаточна, уравнение регрессии не соответствует исходным данным.

Значимость регрессионной модели оценивается с помощью^{-критерия} Фишера. Если величина /''-критерия значима (Р < 0,05), то уравнение регрессии является значимым.

Средством в MS Excel для решения задач регрессионного анализа является инструмен Регрессия.

Пример 7.21.

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

Таблица 7.7

Зависимость количества покупателей от затрат на рекламу.

Решение

• 1. На рабочем листе в диапазоне ячеек А1: С1 введите шапку табл. 7.7. В диапазоне ячеек А2: С8 введите исходные данные из табл. 7.7.
• 2. Задайте команду ДАННЫЕ / Анализ / Анализ данных.
• 3. В открывшемся окне Анализ данных из списка выберите пункт Регрессия.
• 4. В окне Регрессия заполните поля, как показано на рис. 7.26.

Рис. 7.26. Окно настройки для примера 7.21.

5. Нажмите кнопку OK.

В результате выполнения вычислений в выходном диапазоне будет получен результат регрессионного анализа (рис. 7.27). Коэффициент детерминации Я-квадрат равен 0,983 460 868, т. е. точность приближения высокая. Величина F-критсрия (значимость F) — 0,120 155 399 233 279, что меньше 0,05, т.с. уравнение регрессии значимо. У-пересечение равно 91,29 747 858, т. е. константа а₀ = -91,2 974 785 801 713. В столбце^{-значение} для независимой переменной (затраты па рекламу) равно 0,120 155 399 233 279, что меньше 0,05, т. е. коэффициент является значимым. Коэффициент а_л — 1,82 731 946. Уравнение регрессии будет иметь вид.

Рис. 7.27. Результаты расчетов для примера 7.21.