Как сделать факторный анализ в excel пошагово
Microsoft Excel дает пользователю целый инструментарий для анализа финансовой деятельности предприятия, проведения статистических расчетов и прогнозирования.
Встроенные функции, формулы, надстройки программы позволяют автоматизировать львиную долю работы. Благодаря автоматизации пользователю нужно только подставлять новые данные, а на их основе автоматически будут формироваться готовые отчеты, которые многие составляют часами.
Пример финансового анализа предприятия в Excel
Задача – изучение результатов финансовой деятельности и состояния предприятия. Цели:
- оценить рыночную стоимость фирмы;
- выявить пути эффективного развития;
- проанализировать платежеспособность, кредитоспособность.
Основываясь на результатах финансовой деятельности, руководитель вырабатывают стратегию дальнейшего развития предприятия.
Анализ финансового состояния предприятия подразумевает
- анализ баланса и отчета о прибылях и убытках;
- анализ ликвидности баланса;
- анализ платежеспособности, финансовой стабильности предприятия;
- анализ деловой активности, состояния активов.
Рассмотрим приемы анализа балансового отчета в Excel.
Сначала составляем баланс (для примера – схематично, не используя все данные из формы 1).
Проанализируем структуру активов и пассивов, динамику изменений величины статей – построим сравнительный аналитический баланс.
- Представим значения на начало и на конец года в виде относительных величин. Формула: =B4/$B$14 (отношение значения на начало года к величине баланса на начало года). По такому же принципу составляем формулы для «конца года» и «пассива». Копируем на весь столбец. В новых столбцах устанавливаем процентный формат.
- Проанализируем динамику изменений в абсолютных величинах. Делаем дополнительный расчетный столбец, в котором отразим разницу между значением на конец года и на начало.
- Покажем изменения в относительных величинах. В новом расчетном столбце найдем разницу между относительными показателями конца года и начала.
- Чтобы найти динамику в процентах к значению показателя начала года, считаем отношение абсолютного показателя к значению начала года. Формула: =F4/B4. Копируем на весь столбец.
- По такому же принципу находим динамику в процентах для значений конца года.
С помощью простейших формул мы отобразили динамику по статьям баланса. Таким же образом можно сравнивать балансы разных предприятий.
Какие результаты дает аналитический баланс:
- Валюта баланса в конце отчетного периода стала больше в сравнении с начальным периодом.
- Внеоборотные активы приращиваются с более высокими темпами, чем оборотные.
- Собственный капитал предприятия больше, чем заемный. Причем темпы роста собственного превышают динамику заемного.
- Кредиторская и дебиторская задолженность приращиваются примерно в одинаковом темпе.
Статистический анализ данных в Excel
Для реализации статистических методов в программе Excel предусмотрен огромный набор средств. Часть из них – встроенные функции. Специализированные способы обработки данных доступны в надстройке «Пакет анализа».
Рассмотрим популярные статистические функции.
- СРЗНАЧ – Среднее значение – рассчитывает выборочное или генеральное среднее. Аргумент функции – набор чисел, указанный в виде ссылки на диапазон ячеек.
- ДИСП – для вычисления выборочной дисперсии (без учета текстовых и логических значений); ДИСПА – учитывает текстовые и логические значения. ДИСПР – для вычисления генеральной дисперсии (ДИСПРА – с учетом текстовых и логических параметров).
- Для нахождения квадратного корня из дисперсии – СТАНДОТКЛОН (для выборочного стандартного отклонения) и СТАНДОТКЛОНП (для генерального стандартного отклонения).
- Для нахождения моды совокупности данных применяется одноименная функция. Разделяет диапазон данных на две равные по числу элементов части МЕДИАНА.
- Размах варьирования – это разность между наибольшим и наименьшим значением совокупности данных. В Excel можно найти следующим образом:
- Проверить отклонение от нормального распределения позволяют функции СКОС (асимметрия) и ЭКСЦЕСС. Асимметрия отражает величину несимметричности распределения данных: большая часть значений больше или меньше среднего.
В примере большая часть данных выше среднего, т.к. асимметрия больше «0».
ЭКСЦЕСС сравнивает максимум экспериментального с максимумом нормального распределения.
В примере максимум распределения экспериментальных данных выше нормального распределения.
Рассмотрим, как для целей статистики применяется надстройка «Пакет анализа».
Задача: Сгенерировать 400 случайных чисел с нормальным распределением. Оформить полный перечень статистических характеристик и гистограмму.
- Открываем меню инструмента «Анализ данных» на вкладка «Данные» (если данный инструмент недоступен, то нужно подключить настройку анализа ). Выбираем строку «Генерация случайных чисел».
- Вносим в поля диалогового окна следующие данные:
- После нажатия ОК:
- Зададим интервалы решения. Предположим, что их длины одинаковые и равны 3. Ставим курсор в ячейку В2. Вводим начальное число для автоматического составления интервалов. К примеру, 65. Далее нужно сделать доступной команду «Заполнить». Открываем меню «Параметры Excel» (кнопка «Офис»). Выполняем действия, изображенные на рисунке:
- На панели быстрого доступа появляется нужная кнопка. В выпадающем меню выбираем команду «Прогрессия». Заполняем диалоговое окно. В столбце В появятся интервалы разбиения.
- Первый результат работы:
- Снова открываем список инструмента «Анализ данных». Выбираем «Гистограмма». Заполняем диалоговое окно:
- Второй результат работы:
- Построить таблицу статистических характеристик поможет команда «Описательная статистика» (пакет «Анализ данных»). Диалоговое окно заполним следующим образом:
После нажатия ОК отображаются основные статистические параметры по данному ряду.
Это третий окончательный результат работы в данном примере.
Сделать финансовый анализ состояния предприятия в Excel за 5 минут вам поможет программа QFinAnalysis.
Из данной статьи вы узнаете:
- О задачах, которые вы сможете решить с помощью программы;
- О возможностях финансового анализа в QFinAnalysis;
- Как работать с программой;
А также сможете скачать Excel файл с примером расчета финансового состояния с помощью QFinAnalysis.
С помощью QFinAnalysis вы сможете решить следующие задачи финансового анализа предприятия:
- определить отклонения в хозяйственной деятельности, которые могут нести потенциальный риск и требующие внимательной оценки,
- сформировать финансовые планы развития компании,
- повысить эффективность использования ресурсов,
- разработать политику отношений с кредиторами и дебиторами,
- сделать оценку платежеспособности с помощью регламентированных методик оценки вероятности банкротства,
- рассчитать оценку эффективности собственного бизнеса,
- управлять структурой капитала.
Программа QFinAnalysis поможет вам сделать:
- Диагностику вероятности банкротства — 23 современные методики расчета вероятности банкротства, включая методики с использованием аппарата искусственного интеллекта;
- Анализ финансовых показателей – около 20 финансовых коэффициентов (ликвидности, деловой активности, фин.устойчивости, рентабельности и др.);
- Оценку платежеспособности компании – регламентированный анализ платежеспособности по методике Правительства РФ;
- Расчет чистых активов предприятия;
Анализ ликвидности баланса предприятия;
Горизонтальный вертикальный анализ баланса компании;
Прогнозирование банкротства предприятия по наиболее популярным западным методикам (Альтмана, Бивера, Таффлера и др.) и адаптированным отечественным методикам (ИГЭА, Казанской школы, Зайцевой, Савицкой и др.).
Как работать с программой?
Открыв QFinAnalysis, мы попадаем в лист с меню, из которого мы можем перейти в любой лист с программой. нажимая на ссылки соответствующих разделов:
Перейдя в любой из разделов, вы увидите комментарии, которые помогут вам интерпретировать значения коэффициентов и показателей финансового состояния:
Для того, чтобы сделать финансовый анализ состояния предприятия с помощью QFinAnalysis, вам необходимо в программу ввести показатели баланса предприятия:
Баланс предприятия в старой форме вводим в лист «Баланс»:
Баланс в новой форме вводим в лист «Новый баланс»:
Переходим по страницам и получаем:
1. Анализ динамики баланса и доли по статьям в листе «аналит. баланс»
2. Коэффициенты анализа предприятия для:
- Оценки финансовой устойчивости;
- Оценки ликвидности;
- Оценки рентабельности;
- Оценки деловой активности;
3. Оценку ликвидности — лист «анализ ликв.»:
4. Модели множественного дискриминантного анализа (MDA-модели) оценки риска банкротства предприятия:
- Двухфакторная модель Альтмана
- Двухфакторная модель Федотовой
- Пятифакторная модель Альтмана
- Модифицированная модель Альтмана для России
- Четырехфакторная модель Таффлера
- Четырех факторная модель Лиса
- Четырехфакторная модель Спрингейта
- Четырех факторная модель ИГЭА
- Модель Сайфулина-Кадыкова
- Модель Пареной-Долголаева
- Модель Республики Беларусь
- Модель Савицкой
5. Логистические модели (Logit-модели) оценки риска банкротства предприятия показывают вероятность банкротства в процентах
- Модель Альтмана-Сабато (2007)
- Модель Лина-Пьессе (2004)
- Модель ДжуХа-Техонга (2000)
- Модель ДжуХа-Техонга (2000)
- Модель Грузчинского (2003)
- Модель Грузчинского (2003)
6. Экспертная модель «Зайцевой», в которой каждому показателю можно задавать вес вручную, — лист «экспертн. модели»
7. Рейтинговые модели банкротства:
- Модель Казанского Государственного Технологического Университета (КГТУ) для предприятий промышленности (которая позволяет определить класс кредитоспособности);
- Модель Бивера;
8. Модель правительства РФ (оценка платежеспособности и структуры баланса предприятия):
9. Расчет чистых активов предприятия — лист «ЧА»:
10. Сводный отчет — лист «Отчеты»:
Вы можете посмотреть видео с обзором программы:
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel.
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения Статья полезная? Поделитесь с друзьями
Анализ финансового состояния предприятия в Excel позволяет определить финансовое состояние предприятия в динамике по нескольким различным методикам, произвести расчет нескольких десятков финансовых коэффициентов и показателей, а также отразить информацию в наглядном графическом виде.
С помощью выложенной программы вы сможете решить следующие задачи финансового анализа предприятия:
- Анализ структуры активов и пассивов
- Анализ финансовой устойчивости
- Анализ ликвидности
- Анализ деловой активности
- Анализ финансовых результатов деятельности
- Оценка несостоятельности
- Факторный анализ
Расчеты построены на основании новой формы бухбаланса и отчета о финансовых результатах (приказ Минфина от 02.07.2010 №66н).
Это заключительная – пятая статья из серии про финансовый анализ в Excel, и касаться она будет анализа эффективности компании суммарно по показателям БДР, ОПУ и ОДДС. В отличие от всех остальных разделов анализа, здесь практически не будет никаких исходных данных – сугубо финансовые расчеты. В качестве примера используем реальный проект , который уже работает в виде бизнеса, однако цифры там будут условные.
Начнем с того, что определим, какие показатели мы хотим выделить и рассчитать. В даном случае это: EBIT, EBITDA, оборачиваемость активов, чистая прибыль, рентабельность активов, рентабельность персонала, рентабельность основных средств и рентабельность продаж. Все эти показатели нужно будет отдельно рассчитать – подчеркивем, именно рассчитать, т.к. исходные данные из имеющейся отчетности по EBIT и EBITDA не отражают точную картину.
Итак, создаем таблицу в листе фин анализ в Excel, и вписываем туда все перечисленные показатели.
Далее по порядку все рассчитываем. Рентабельность продаж рассчитывается просто: прибыль за вычетом налога на прибыль деленная на выручку. Этот показатель дает понимание эффективности бизнеса в целом – с учетом всех издержек. Вообще надо сказать, что показателей рентабельности всего несколько сотен, но в нашем случае достаточно взять только самые основные, чтобы можно было дать общую оценку актуальности инвестиций.
Следующий показатель – рентабельность основных средств (ROFA) рассчитывается как отношение стоимости основных средств труда к чистой прибыли. То есть, если речь идет, например, об автопроизводителе, то в качестве основных средств могут быть – оборудование, патенты, технологии и т.д. Не путайте с показателем рентабельности активов (ROA) – данная категория рассчитывается уже исходя из отношения чистой прибыли и стоимости всех активов, находящихся на балансе предприятия.
Далее – рассчитываем показатель рентабельности персонала. Во многом он похож на производительность труда, однако разница в том, что в данном случае рассчитывается не отдача от каждого сотрудника, а непосредственно эффективность бизнеса в плане отношения фонда оплаты труда и чистой прибыли. При этом, в фонд оплаты труда входит как непосредственно заработная плата работников, так и социальные выплаты. Тем самым, появитсяпонимание об эффективности нынешней кадровой политики и работы с имеющимся персоналом.
Теперь рассчитываем оборачиваемость активов. Этот показатель очень важен в оценке бизнеса, так как указывает на мобильность имеющихся активов и то, как часто они используются. Чем выше оборачиваемость капитала, тем более эффективным считается бизнес. Формула расчета простая: EBIT/Total assets. Отдельно стоит рассчитать показатели EBIT и EBITDA. В качестве первого можно применить показатель чистой прибыли минус налоги, EBITDA рассчитывается еще проще: EBIT+Depreciation+Interests. Лучше оба этих показатели сделать в этой же таблице – для удобства дальнейших расчетов. Наконец, для расчета чистой прибыли можно отнять от EBIT все налоговые вычеты. В итоге, все финансовые показатели должны быть собраны в таблице, показанной выше.
Основные показатели мы рассчитали, теперь перейдем к расчету точки безубыточности и NPV. Точка безубыточности указывает на минимально необходимый объем выручки при имеющихся затратах, чтобы обеспечить нулевую рентабельность бизнеса. Для его расчета создаем отдельный лист, делаем таблицу, на котором указываем: процент загрузки, выручка, постоянные и переменные затраты, налоги, полные затраты и итоговый результат. Группируем показатели которые относятся к переменным и постоянным затратам, применяем стандартную формулу для расчета налогов, и наконец проводим все оставшиеся расчеты. В итоге должна получится таблица, которая показана на рисунке выше. Эта таблица показывает, при каком уровне загрузки бизнеса будет достигнута нулевая рентабельность. В нашем случае получилась загрузка 16%.
Ну и наконец, осталось рассчитать NPV компании, чтобы было понимание о какой-либо актуальности в инвестициях вообще. В начале определимся со ставкой дисконтирования – нужно ли ее рассчитывать отдельно через модель CAPM или же достаточно просто взять норму альтернативной доходности, в качестве которой вполне может сгодиться, например, государственная облигация. На настоящее время доходность по ОФЗ составляет около 10%. Конкретно в этом проекте общий NPV получился на 2.2 млрд. рублей, срок дисконтированной окупаемости инвестиций – 31 месяц, и таким образом доходность получилась в районе 40% в годовом выражении, что указывает на то, что инвестировать в данную компанию – целесообразно.
На этом собственно и все. Это была последняя статья из серии про финансовый анализ в Excel.
Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.
Excel позиционирует себя как лучший универсальный программный продукт в мире по обработке аналитической информации. От маленького предприятия до крупных корпораций, руководители тратят значительную часть своего рабочего времени для анализа жизнедеятельности их бизнеса. Рассмотрим основные аналитические инструменты в Excel и примеры применения их в практике.
Инструменты анализа Excel
Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».
Средства анализа «Что-если»:
- «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
- «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
- «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
- «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.
Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.
Другие инструменты для анализа данных:
Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).
Сводные таблицы в анализе данных
Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.
Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:
- Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
- Откроется диалоговое окно «Создание таблицы».
- Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.
К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).
Составить отчет можно с помощью «Сводной таблицы».
- Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» - «Таблицы» - «Сводная таблица»).
- В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
- Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
- Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.
Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.
Анализ «Что-если» в Excel: «Таблица данных»
Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».
- данные должны находиться в одном столбце или одной строке;
- формула ссылается на одну входную ячейку.
Процедура создания «Таблицы данных»:
- Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
- Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
- В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.
Анализ предприятия в Excel: примеры
Для анализа деятельности предприятия берутся данные из бухгалтерского баланса, отчета о прибылях и убытках. Каждый пользователь создает свою форму, в которой отражаются особенности фирмы, важная для принятия решений информация.
Для примера предлагаем скачать финансовый анализ предприятий в таблицах и графиках составленные профессиональными специалистами в области финансово-экономической аналитике. Здесь используются формы бухгалтерской отчетности, формулы и таблицы для расчета и анализа платежеспособности, финансового состояния, рентабельности, деловой активности и т.д.
Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.
Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.
Регрессионный анализ в Excel
Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.
Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.
- линейной (у = а + bx);
- параболической (y = a + bx + cx 2 );
- экспоненциальной (y = a * exp(bx));
- степенной (y = a*x^b);
- гиперболической (y = b/x + a);
- логарифмической (y = b * 1n(x) + a);
- показательной (y = a * b^x).
Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.
Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.
Модель линейной регрессии имеет следующий вид:
Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.
В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).
В Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».
Активируем мощный аналитический инструмент:
- Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
- Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
- Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.
После активации надстройка будет доступна на вкладке «Данные».
Теперь займемся непосредственно регрессионным анализом.
- Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
- Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
- После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).
В первую очередь обращаем внимание на R-квадрат и коэффициенты.
R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».
Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.
Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.
Корреляционный анализ в Excel
Корреляционный анализ помогает установить, есть ли между показателями в одной или двух выборках связь. Например, между временем работы станка и стоимостью ремонта, ценой техники и продолжительностью эксплуатации, ростом и весом детей и т.д.
Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.
Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.
Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.
Для нахождения парных коэффициентов применяется функция КОРРЕЛ.
Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.
Ставим курсор в любую ячейку и нажимаем кнопку fx.
- В категории «Статистические» выбираем функцию КОРРЕЛ.
- Аргумент «Массив 1» - первый диапазон значений – время работы станка: А2:А14.
- Аргумент «Массив 2» - второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.
Чтобы определить тип связи, нужно посмотреть абсолютное число коэффициента (для каждой сферы деятельности есть своя шкала).
Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.
Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:
Корреляционно-регрессионный анализ
На практике эти две методики часто применяются вместе.
- Строим корреляционное поле: «Вставка» - «Диаграмма» - «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
- Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
- Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
- Жмем «Закрыть».
Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от 2-х факторов, значения которых мы можем контролировать, т.е. задавать с требуемой точностью. Покажем как методом дисперсионного анализа проверить гипотезу о наличии или отсутствии влияния указанных факторов на зависимую переменную Y .
Disclaimer : Эта статья – о применении MS EXCEL для целей Дисперсионного анализа, поэтому данную статью не стоит рассматривать, как пересказ главы из учебника по статистике. Статья не обладает ни полнотой, ни строгостью изложения положений статистической науки. Теоретические отступления приведены лишь из соображения логики изложения. Использование данной статьи для изучения теории Дисперсионного анализа – плохая идея. Хорошая идея - найти в этой статье формулы MS EXCEL для проведения Дисперсионного анализа.
Напомним, что дисперсионный анализ (ANOVA, ANalysis Of VAriance) позволяет проверить гипотезу о равенстве средних значений выборок (взяты ли выборки из одного распределения или из разных распределений). Данная задача возникает, например, когда необходимо исследовать зависимость некой количественной величины Y от одной или нескольких переменных (факторов), которые мы можем контролировать (устанавливать их значения). Действительно, если фактор оказывает влияние на зависимую переменную Y, то при разных уровнях фактора мы должны в среднем получать различные значения Y, т.е. мы должны получить «заметно отличающиеся» средние значения выборок . В статье будет показано, что значит средние выборок «заметно отличаются».
В этой статье рассмотрим метод дисперсионного анализа в случае двух факторов (Фактор А и Фактор В) (Two Factor ANOVA with Replication).
СОВЕТ : Перед прочтением этой статьи рекомендуется освежить в памяти Однофакторный дисперсионный анализ .
Обозначения
Отдельные, заданные значения каждого фактора называются уровнями ( levels ) или испытаниями ( treatments ).
Уровни фактора А будем обозначать буквой j (j изменяется от 1 до a ). Уровни фактора В будем обозначать буквой i (i изменяется от 1 до b ). Каждой паре уровней факторов соответствует одна выборка, которая состоит из m измерений, каждое измерение будем обозначать буквой k (k от 1 до m). Таким образом, измеренные значения Y при уровне j фактора А и при уровне i фактора В будем обозначать y ijk . Всего выборок a*b .
Предполагается, что дисперсии всех выборок σ 2 неизвестны, но равны между собой.
Рассмотрим двухфакторный дисперсионный анализ при решении задачи.
Задача
В компании, изготавливающей изделия путем механообработки, необходимо исследовать влияние на качество изделия двух факторов: Метода обработки поверхности детали, и Исходного материала детали (используется сталь с различным легированием).
Метод обработки представляет собой фактор А , который может принимать 3 значения (Метод 1, Метод 2, Метод 3), а Исходный материал представляет собой фактор В , который может принимать 2 значения (№ 1, № 2). Качество изделий будем определять по количеству дефектных изделий в партии (это будет зависимой переменной Y).
Всего различных комбинаций 2-х факторов 6=3*2=a*b. Для каждой комбинации факторов было проведено по 3 измерения (т.е. m=3). Исходные данные приведены в файле примера .
Другими словами мы имеем 6 выборок по 3 значения в каждой. Средние этих выборок для каждой комбинации факторов ij можно вычислить по формуле:
Также для дальнейших вычислений нам потребуется вычислить еще несколько средних значений. Во-первых, вычислим среднее всех измерений, относящихся к каждому уровню i Фактора А:
Во-вторых, вычислим среднее всех измерений, относящихся к каждому уровню j Фактора В:
Взаимодействие факторов
Теперь, используя эти 6 средних значений, построим диаграмму, которая состоит из 2-х рядов .
По оси Х (абсцисс) отложены уровни Фактора А , по оси ординат отложены средние значения переменной Y (среднее количество дефектов для заданных уровней факторов). Средние значения сгруппированы по 2-м уровням Фактора В (Синяя и красная линии. Каждая линия представляет собой отдельный ряд диаграммы).
Как видно из диаграммы – синяя и красная линии практически параллельны друг другу. Это означает, что взаимодействие между факторами практически отсутствует (они не влияют друг на друга). Действительно, выбор метода обработки никак не может влиять на выбор конкретного исходного материала.
Вот еще одна диаграмма, демонстрирующая независимость 2-х факторов.
Обратная ситуация показана на диаграмме ниже, когда оба фактора взаимодействуют.
Из этой диаграммы видно, что при уровне №1 фактора В (синяя линия) количество дефектов сначала возрастает, затем снижается (когда мы переходим от метода №1 к №2, затем к №3). Мы наблюдаем диаметрально противоположную ситуацию при уровне №2 фактора В (красная линия): количество дефектов сначала снижается, а затем возрастает. В этом случае говорят о наличии взаимодействия факторов.
В случае взаимодействия факторов А и В, эффект от их взаимодействия может быть рассмотрен как некий третий фактор АВ . Чтобы пояснить это рассмотрим задачу анализа влияния на урожайность свеклы 2-х факторов: Вид семян и Тип почвы . Очевидно, что факторы Вид семян и Тип почвы не являются независимыми: можно утверждать, что для всех с/х культур на разных почвах разные типы семян дадут разную всхожесть. Различные комбинации Вид семян - Тип почвы могут сильно влиять на урожайность и поэтому взаимодействие факторов может вносить определенный вклад в разброс исходных данных.
Взаимодействие факторов было рассмотрено столь подробно, так как отсутствие или наличие взаимодействия принципиально влияет на ход дисперсионного анализа . При отсутствии взаимодействия влияние каждого фактора на переменную Y может быть рассмотрено по отдельности. При наличии взаимодействия анализировать влияние каждого фактора по отдельности нельзя. Альтернативным вариантом анализа в этом случае является однофакторный дисперсионный анализ, целью которого может быть поиск оптимального сочетания 2-х факторов.
Возвращаемся к диаграммам взаимодействия. Очевидно, что делать заключение о наличии или отсутствии взаимодействия факторов невозможно лишь по взаимному расположению линий на диаграмме. Для формулирования утверждения о взаимодействии требуется составить математическое выражение. Это выражение должно вычисляться на основании исходных данных, а результат должен сравниваться с неким критическим значением. Займемся этим в следующем разделе.
Определяем причины изменчивости исходных данных
По аналогии с однофакторным дисперсионным анализом общую изменчивость (разброс) значений Y относительно общего среднего (SST = Sum of Squares Total, общая сумма квадратов) определим как сумму нескольких компонентов, в данном случае 4-х:
SST=SSA+SSB+ SS взаим +SSE
- SSA – изменчивость, которую можно объяснить выбором метода обработки (фактор А)
- SSВ - изменчивость обусловленная выбором материала детали (фактор В)
- SS взаим - изменчивость обусловленная взаимодействием 2-х факторов
- SSE - ошибка модели (Error Sum of Squares).
SST и все 4 компонента вычисляются на основании имеющихся исходных данных:
Примечание : Вычисления SST и всех 4-х компонентов выполнены в файле примера .
Также в дисперсионном анализе используется понятие среднего квадрата отклонений (Mean Square) или сокращенно MS. Соответственно для SST имеем MST=SST/(N-1), где N= a*b*m является общим количеством измерений (18). Для других SS степени свободы приведены в таблице ниже.
Таким образом, MS имеет смысл средней изменчивости на 1 наблюдение (с некоторой поправкой). Эта поправка отражает тот факт, что MS должна вычисляться не делением SS на соответствующее количество наблюдений, а делением на число степеней свободы (degrees of freedom, DF). Например, чтобы вычислить MST, мы из N (общего количества наблюдений) должны вычесть 1, т.к. в выражении SST присутствует одно (1) среднее значение (аналогично тому, как мы делали при вычислении дисперсии ).
В случае двухфакторного дисперсионного анализа формируется 3 нулевых гипотезы .
- Гипотеза Н 0 взаим об отсутствии взаимодействия Фактора А и Фактора В. Альтернативная гипотеза Н 1взаим формулируется о наличии взаимодействия.
- гипотеза Н 01 заключается в том, что уровень фактора А (метод обработки поверхности) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора А не отличаются статистически значимо (их различие может быть объяснено лишь случайностью выборок).
- гипотеза Н 0 2 заключается в том, что уровень фактора В (Исходный материал) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора В не отличаются статистически значимо.
Сначала тестируют гипотезу об отсутствии взаимодействия между факторами. Мы можем отклонить Н 0 взаим в пользу Н 1взаим при заданном уровне значимости α (альфа), если вычисленное значение тестовой статистики F= MS взаим /MSE больше F критич альфа – значения случайной величины F имеющей распределение Фишера с (b-1)*(a-1) и a*b*(m-1) степенями свободы.
Если взаимодействие между факторами отсутствует, то можно начинать тестировать гипотезы Н 01 и Н 0 2 . При наличии взаимодействия анализировать влияние каждого фактора по отдельности нельзя. Альтернативным вариантом анализа в этом случае является однофакторный дисперсионный анализ , целью которого может быть поиск оптимального сочетания 2-х факторов.
Чтобы проверить гипотезы необходимо вычислить значения тестовых статистик и сравнить их с соответствующими критическими значениями F крит ич , вычисленными для заданного уровня значимости альфа . Если вычисленное значение F 01 = MSА/MSE больше F 1крит ич , то нулевую гипотезу Н 0 1 об отсутствии влияния уровней Фактора А отклоняют. Аналогичные умозаключения справедливы и для Фактора В.
Проверить гипотезу Н 01 можно и через вычисление p -значения, которое представляет собой вероятность того, что случайная величина F 1 = MSА/MSE примет значение более F 01 . Далее p -значение сравнивают с уровнем значимости. Если p -значение менее уровня значимости, то нулевую гипотезу отклоняют. Действительно, если вычисленное значение F 01 получить маловероятно, то это ставит под сомнение справедливость того, что случайная величина F 1 = MSА/MSE имеет распределение Фишера с a -1 и a * b *( m -1) степенями свободы, а следовательно и саму нулевую гипотезу. В этом случае мы можем считать, что справедлива альтернативная гипотеза: уровни фактора А влияют на зависимую переменную Y.
Вычисления в MS EXCEL
В файле примера приведено решение вышеуказанной задачи: вычислены средние значения выборок, суммы квадратов (SS), степеней свобод, средние квадратов отклонений (MS).
Для вычислений критических значений в MS EXCEL имеется специальная функция = F.ОБР.ПХ()
Формула для вычисления F 1критич = F.ОБР.ПХ(a-1; a*b*(m-1);альфа)
В MS EXCEL первое p -значение (вероятность того, что случайная величина F 1 = MSА/MSE примет значение более F 01 ) можно вычислить по формуле:
= F.РАСП.ПХ((MSА/MSE; a-1; a*b*(m-1))
Второе p -значение (вероятность того, что случайная величина F 2 = MSВ/MSE примет значение более F 0 2 ) вычисляется по аналогичным формулам.
В нашей задаче p -значения получились 0,000 и 0,253, что значительно меньше обычно принимаемого в качестве уровня значимости 0,05. Таким образом, обе нулевых гипотезы отклоняются.
Пусть имеется случайная переменная Y , значения которой мы можем измерять. Исследователь предполагает, что эта переменная зависит от фактора, значения которого мы можем контролировать, т.е. задавать с требуемой точностью. Покажем как методом дисперсионного анализа ( ANOVA ) проверить гипотезу о наличии или отсутствии влияния указанного фактора на зависимую переменную Y .
Disclaimer : Эта статья – о применении MS EXCEL для целей Дисперсионного анализа, поэтому данную статью не стоит рассматривать, как пересказ главы из учебника по статистике. Статья не обладает ни полнотой, ни строгостью изложения положений статистической науки. Теоретические отступления приведены лишь из соображения логики изложения. Использование данной статьи для изучения теории Дисперсионного анализа – плохая идея. Хорошая идея - найти в этой статье формулы MS EXCEL для проведения Дисперсионного анализа.
Перед прочтением этой статьи рекомендуется освежить в памяти следующие понятия статистики:
- Проверка статистических гипотез ;
- Дисперсия и среднее значение ;
- Распределение Фишера и квантили этот распределения;
- F-тест ;
- Блочные диаграммы .
Дисперсионный анализ (ANOVA, ANalysis Of VAriance) позволяет проверить гипотезу о равенстве нескольких средних значений выборок (взяты ли выборки из одного распределения или из разных распределений).
Примечание : В статье Двухвыборочный t-тест с одинаковыми дисперсиями решалась подобная задача о сравнении средних значений 2-х распределений. Здесь рассмотрим более общую задачу – будем одновременно сравнивать несколько средних значений выборок (более 2-х).
Чтобы пояснить суть дисперсионного анализа приведем пример.
Сгенерируем 2 выборки: первую возьмем из нормального распределения со средним значением равно 4, вторую со средним - 5 ( стандартные отклонения одинаковые). Сказать, сильно ли они различаются или нет, невозможно, пока мы не знаем разброс (стандартное отклонение) значений в каждой выборке относительно среднего. Если зададим в распределениях небольшой разброс, скажем 0,1, то в каждой выборке получим близкое к нему значение. В этом случае, очевидно, что наблюдаемое различие между средними равное 1 (5-4=1) – значительное и можно говорить, что выборки взяты из разных распределений (см. картинку ниже).
Если же разброс в выборках составляет около 2, то наблюжаемое различие средних значений выборок равное 1 уже не кажется таким значительным.
В дисперсионном анализе эти значения выборок представляют собой значения зависимой переменной Y, а выборки берутся при различных уровнях фактора Х. В первом случае для того дать ответ о зависимости Y от фактора Х, даже не нужно проводить дисперсионный анализ : из диаграммы итак очевидно, что отличие между средними значениями выборок (5-4=1), гораздо больше разброса внутри выборки (0,1). Следовательно, очевидно, что выборки взяты из различных генеральных совокупностей (с различными распределениями), которые соответствуют разным значениям Х.
Во втором случае без дисперсионного анализа не обойтись. Различие между средними значениями может быть обусловлено просто случайностью выборок, взятых из одного распределения.
В конце статьи мы определим математически точно условие «значимости» различия средних выборок .
Немного теории
Примечание : Пользователи, уверенно владеющие методом дисперсионного анализа , могут перейти непосредственно к формулам MS EXCEL .
Пусть необходимо исследовать зависимость некой количественной случайной величины Y от одной переменной, которую мы можем контролировать (устанавливать их значения с требуемой точностью). В теории дисперсионного анализа переменная Y называется зависимой переменной ( dependent или response variable ), а переменные, от которых исследуется зависимость переменной Y, называются факторами или зависимыми переменными ( factors или dependent variables ).
Для целей этой статьи будем предполагать, что Y зависит только от одного фактора.
Примечание : Случай зависимости от 2-х факторов рассмотрен в статье Двухфакторный дисперсионный анализ .
Отдельные, заданные значения фактора называются уровнями ( levels ) или испытаниями ( treatments ).
Так как мы можем контролировать значения, которые принимает фактор , то данные (набор значений Y), которые получены в результате испытаний, мы назовем экспериментальными , а сам процесс получения этих данных - экспериментом .
Целью эксперимента является исследование влияния различных уровней фактора на переменную Y. В самом деле, так как фактор нами контролируется, то у нас есть возможность сделать несколько наблюдений (измерений) величины Y при определенном заданном уровне фактора. Зачем их делать несколько, ведь значения Y должны получиться одинаковыми? Нет. Так как мы предполагаем, что на переменную Y может влиять множество неконтролируемых нами факторов, то мы будем получать в ходе каждого измерения несколько отличающиеся значения Y. Единственное, что мы можем сделать, это обеспечить одинаковые условия проведения эксперимента для всех измерений.
Например, измеряя расход бензина на 100 км/ч одной и той же марки бензина на одном и том же автомобиле, мы будем получать несколько различные значения. Может непредсказуемо измениться направление ветра, состояние дороги или автомобиля, что в свою очередь повлияет на расход.
Уровни фактора (treatments) будем обозначать буквой j (j изменяется от 1 до a ). Каждому уровню фактора соответствует одна выборка (состоит из нескольких измерений). Предполагается, что дисперсии всех выборок σ 2 неизвестны, но равны между собой.
Непосредственно измеренные значения Y при заданном уровне фактора j будем обозначать y ij . Количество наблюдений для разных уровней факторов может быть одинаковым или отличаться.
Примечание : Чем больше количество измерений/наблюдений (т.е. размер выборки) мы сделаем, тем более обоснованным будет наш статистический вывод о равенстве средних значений этих выборок.
В тексте статьи будем рассматривать только равные выборки, их размер обозначим n. В Этом случае общее количество измерений N=n*a.
Примечание : В файле примера выполнены вычисления для обоих случаев (равные и неравные по размеру выборки).
Если фактор действительно оказывает влияние на зависимую переменную Y, то при различных уровнях фактора мы должны в среднем получать различные значения Y. Другими словами, мы должны получить «заметно различающиеся» средние выборок при различных уровнях фактора:
Остается выяснить, что значит средние выборок «заметно отличаются».
Стандартные обозначения дисперсионного анализа
Общий подход при проведении Дисперсионного анализа: проверить значимость различия средних значений выборок, сравнив один источник разброса (проверяемый фактор) с другим источником разброса (обоснованный лишь случайностью выборок/ случайным воздействием неконтролируемых факторов):
Введя нижеуказанные обозначения, выражение можно записать в компактной форме:
Эти общеупотребительные обозначения расшифровываются следующим образом: SS – это сокращение английского выражения Sum of Squares (сумма квадратов отклонений от среднего), T – это сокращение от Total (Общее среднее), А – это фактор А, E – это сокращение от Error (ошибка).
На основании данных определений, вышеуказанное выражение может быть преобразовано в вычислительную форму:
где, – общее среднее:
Обратите внимание, что квадраты отклонений имеют размерность дисперсии , т.е. меры изменчивости. Теперь очевидно, что левая часть выражения представляет собой общую изменчивость (разброс) каждого из наблюдений относительно общего среднего. Эта общая изменчивость (SST) состоит из двух частей: SSA - изменчивость, объясненная нашей моделью (междувыборочная изменчивость, основанная на различиях в уровнях фактора) и из SSE - ошибка модели (внутривыборочная изменчивость, сумма разбросов наблюдений внутри каждой выборки).
Также в дисперсионном анализе используется понятие среднего квадрата отклонений (Mean Square), т.е. MS. Соответственно для SST имеем MST=SST/(N-1), для SSA имеем MSA=SSA/(n-1), для ошибки модели SSE имеем MSE=SSE/(a(n-1)).
MS имеет смысл средней изменчивости на 1 наблюдение (с некоторой поправкой). Эта поправка отражает тот факт, что MS должна вычисляться не делением SS на соответствующее количество наблюдений, а на число степеней свободы (degrees of freedom, DF). Например, чтобы вычислить MST, мы из N (общего количества наблюдений) должны вычесть 1, т.к. в выражении SST присутствует одно среднее значение (аналогично тому, как мы делали при вычислении дисперсии выборки ). Одна степень свободы теряется при вычислении среднего – это видно в формуле выражения для SST.
В SSA мы имеем уже а средних значений (равно количеству уровней фактора, т.е. количеству выборок). Поэтому, из общего количества наблюдений a *n необходимо вычесть а – количество вычисленных средний значений выборок (an-a=a(n-1)).
Напомним, что в дисперсионном анализе проверяется гипотеза о равенстве средних значений этих выборок. Т.е. формулируется нулевая гипотеза Н 0 , которая утверждает, что Y не зависит от фактора и все выборки, измеренные при различных уровнях фактора, на самом деле взяты из одного распределения с общим средним.
Идем дальше. Оказывается, если нулевая справедлива , то:
- случайная величина MSА представляет собой оценку σ 2
- отношение MSА/MSE имеет распределение Фишера с а-1 и a(n-1) степенями свободы.
MSА/MSE обозначают как F 0 ( тестовая статистика для однофакторного дисперсионного анализа ).
Примечание : Можно показать, что MSE также представляет собой оценку σ 2 дисперсии выборок ( математическое ожидание случайной величины MSE равно σ 2 ). Но, в отличие от MSА, MSE представляет собой оценку σ 2 вне зависимости от того, справедлива ли нулевая справедлива или нет.
Теперь, введя основные понятия, рассмотрим вычислительную часть дисперсионного анализа на примере решения задачи.
Задача
В качестве задачи рассмотрим технологический процесс изготовления нити в химическом реакторе.
Пусть предполагается, что инженер исследует влияние некой добавки на прочность нити Y. Он решает провести эксперимент:
- Использовать 4 различных концентраций добавки (1%; 5%; 7% и 10%). Прим .: эти значения концентраций не участвуют в расчетах.
- Провести по 6 (n) измерений прочности нити для каждой концентрации добавки.
Таким образом, имеется только 1 фактор (концентрация добавки). Фактор имеет 4 (а=4) различные уровня (j=1; 2; 3; 4). Всего у нас имеется 24 (N=4*6) измерения.
Вроде бы эксперимент полностью описан, теперь инженеру требуется только провести измерения. Однако, есть еще одна сложность: на разброс результатов при различных уровнях фактора может повлиять то, как мы проводим эксперимент.
О рандомизированном эксперименте
Представим, что у нас есть только 1 реактор. Инженер включает реактор, делает 6 измерений для первого уровня, затем, для 2-го и т.д. В итоге, может случиться так, что первые 6 измерений у нас будут выполнены в реакторе, который только начал прогреваться, а последние 6, когда он полностью вышел в рабочий режим. Понятно, что такой подход не годится: на разброс выборок может влиять не только концентрация добавки, но и порядок, в котором проводились измерения.
Также не годится подход, когда используются 4 одинаковых, но отдельных реактора для каждого эксперимента: первый реактор для концентрации 1%, второй - для 5% и т.д. Однако, индивидуальные особенности каждого реактора (период эксплуатации, воздействие ремонтов, незначительное различие конструкции допущенное при изготовлении) могут сказаться на разбросе выборки.
То есть для постановки правильного эксперимента требуется исключить влияние конкретного устройства (experimental unit) на значение переменной Y.
Обычно используют полностью рандомизированный эксперимент (completely randomized experimental design) – это когда для каждого испытания ( treatment ) выбираются образцы экспериментального устройства выбираются случайным способом.
Например, для нашего случая можно предложить следующую схему полностью рандомизированного эксперимента : мы случайным образом выбираем из большого количества одинаковых ректоров (например, из 1000) 6 ректоров для наблюдений первого уровня фактора (для каждого наблюдения 1 реактор), 6 – для второго и т.д. Всего 24 ректора из 1000.
Или можно предложить схему попроще. Всего имеется 24 одинаковых реакторов. Для каждого наблюдения выбираем случайным образом свой реактор.
Или еще проще: каждому из 24 измерений случайным образом (вне зависимости от уровня фактора) назначаем один из 4 одинаковых реакторов. Каждый реактор участвует в 6 измерениях.
Примечание : Т.к. не всегда представляется возможным иметь в распоряжении множество одинаковых экспериментальных устройств для проведения полностью рандомизированного эксперимента , то в статистике часто используются и другие формы проведения экспериментов, например, блочный рандомизированный эксперимент ( randomized block design ).
Вычисления в MS EXCEL
Итак, предположим, что все измерения проведены в соответствии со схемой полностью рандомизированного эксперимент а. Результаты измерений представлены в таблице ниже (см. файл примера на листе Модель ).
Сначала изучим статистические характеристики набора данных, построив блочную диаграмму .
Из блочной диаграммы видно, что концентрация добавки влияет на прочность нити Y (чем выше концентрация, тем в среднем прочнее нить). Однако, мы пока не можем сделать статистически обоснованный вывод, о том что концентрация добавки влияет на прочность нити . Возможно, различие в средних значениях выборок обусловлено лишь случайностью выборок.
Примечание : Из блочной диаграммы видно, что разброс данных (его отражает дисперсия выборки) имеет примерно одинаковую величину для всех 4-х выборок, что является обязательным условием для корректности применения метода дисперсионного анализа .
Сделаем вспомогательные вычисления по формулам из предыдущего раздела статьи: вычислим средние значения каждой выборки, общее среднее, суммы квадратов SS, степени свободы, MSE, MSA.
Тестовая статистика вычисляется по формуле:
Т.к. тестовая статистика имеет F -распределение ( распределение Фишера ) , то ее значение, вычисленное на основании наблюдений, должно лежать около среднего значения F -распределения с соответствующими степенями свободы .
В нашем случае среднее значение F -распределения с 3 и 20 степенями свободы равно 1,11. Если вычисленное нами значение F 0 «значительно» превосходит это значение, то это является маловероятным событием и у нас есть основания для отклонения нулевой гипотезы .
В нашей задаче F 0 равно 5,3358. «Значительно» это или нет? Для ответа на этот вопрос вычислим вероятность этого события (т.е. вероятность события, что случайная величина F, имеющая распределение Фишера с указанными степенями свободы, примет значение 5,3358 или более). Эта вероятность не высока =0,0072. Этого и следовало ожидать, т.к. 5,3358 значительно больше среднего значения 1,11. В MS EXCEL эту вероятность можно вычислить по формуле:
0,0072 – это так называемое p -значение , т.е. вероятность, что статистика F 0 примет вычисленное значение.
Примечание : Обычно под F 0 понимается как сама случайная величина - тестовая статистика F 0 , так и ее конкретное значение F 0 , вычисленное из условий задачи (исходных данных).
Теперь сравним p -значение с уровнем значимости (обычно 0,05 или 0,01). Если p -значение меньше уровня значимости , то нулевую гипотезу отклоняют.
В начале статьи мы задались вопросом о том, как математически точно определить «значимое» отличие средних значений выборок (чтобы мы могли сделать вывод, что уровни фактора влияют на значение переменной Y). Теперь мы можем утверждать, что средние выборок статистически значимо отличаются, если вычисленное p -значение меньше заданного уровня значимости .
Таким образом, наша модель является полезной и наше предположение о зависимости Y (прочности нити) от фактора (концентрации добавки) является статистически обоснованным.
Примечание : Однофакторный дисперсионный анализ можно также выполнить с помощью надстройки Пакет анализа . Об этом см. в статье здесь .
Читайте также: