Использование пакета анализа

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

Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности.

Ниже описаны инструменты, включенные в пакет анализа. Чтобы получить доступ к этим инструментам, выберите Анализ данных на вкладке Данные . Если команда "Анализ данных " недоступна, необходимо загрузить и активировать надстройку Analysis ToolPak .

Загрузка и активация пакета анализа

1. Чтобы загрузить и активировать пакет анализа, выполните следующие действия.

   В Excel для Mac в меню "Файл" выберите "Сервис>и надстройки Excel".

   В Excel для Windows:

   1. Выберите "Файл", "Параметры", а затем выберите "Надстройки".
   2. В раскрывающемся списке "Управление " выберите пункт "Надстройки Excel ", а затем нажмите кнопку "Перейти".

2. В окне надстроек установите флажок для проверки пакета анализа и нажмите кнопку "ОК".

   • Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы найти ее.
   • Если появится сообщение о том, что пакет анализа в настоящее время не установлен на вашем компьютере, нажмите кнопку " Да ", чтобы установить его.

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

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

Однофакторный дисперсионный анализ

Этот инструмент выполняет простой дисперсионный анализ данных для двух или более выборок. Анализ обеспечивает проверку гипотезы о том, что каждая выборка взята из одного и того же базового распределения вероятностей, против альтернативной гипотезы о том, что базовые распределения вероятностей не одинаковы для всех выборок. Если имеется только два выборки, можно воспользоваться функцией листа СТЬЮДЕНТ.ТЕСТ. При наличии более чем двух выборок нет удобного обобщения T.TEST, и вместо нее можно обратиться к однофакторной модели Anova.

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

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам. Например, в эксперименте по измерению высоты растений последние обрабатывали удобрениями от различных изготовителей (например, A, B, C) и содержали при различной температуре (например, низкой и высокой). Таким образом, для каждой из 6 возможных пар условий {удобрение, температура}, имеется одинаковый набор наблюдений за ростом растений. С помощью этого дисперсионного анализа можно проверить следующие гипотезы:

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

Извлечены ли шесть выборок, представляющих все пары значений {удобрение, температура}, используемые для оценки влияния различных марок удобрений (для первого пункта в списке) и уровней температуры (для второго пункта в списке), из одной генеральной совокупности. Альтернативная гипотеза предполагает, что влияние конкретных пар {удобрение, температура} превышает влияние отдельно удобрения и отдельно температуры.

Двухфакторный дисперсионный анализ без повторений

Этот инструмент анализа применяется, если данные можно систематизировать по двум параметрам, как в случае двухфакторного дисперсионного анализа с повторениями. Однако в таком анализе предполагается, что для каждой пары параметров есть только одно измерение (например, для каждой пары параметров {удобрение, температура} из предыдущего примера).

Корреляция

Функции рабочего листа КОРРЕЛ и ПИРСОН вычисляют коэффициент корреляции между двумя переменными измерения, когда измерения каждой переменной наблюдаются для каждого из N субъектов. (Любое пропущенное наблюдение для любого предмета приводит к тому, что этот предмет игнорируется в анализе.) Инструмент Корреляционный анализ особенно полезен, когда для каждого из N субъектов есть более двух переменных измерения. Он предоставляет выходную таблицу, матрицу корреляции, которая показывает значение CORREL (или PEARSON), примененное к каждой возможной паре переменных измерения.

Коэффициент корреляции, как и ковариация, является мерой степени, в которой две переменные измерения «изменяются вместе». В отличие от ковариации, коэффициент корреляции масштабируется таким образом, что его значение не зависит от единиц, в которых выражаются две переменные измерения. (Например, если двумя переменными измерения являются вес и рост, значение коэффициента корреляции остается неизменным, если вес пересчитывается из фунтов в килограммы.) Значение коэффициента корреляции должно находиться в диапазоне от -1 до +1 включительно.

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

Ковариация

Инструменты Корреляция и Ковариация можно использовать в одной и той же настройке, когда у вас есть N различных переменных измерения, наблюдаемых у набора людей. Каждый из инструментов Корреляция и Ковариация выдает выходную таблицу, матрицу, которая показывает коэффициент корреляции или ковариацию, соответственно, между каждой парой переменных измерения. Разница заключается в том, что коэффициенты корреляции масштабируются так, чтобы лежать в диапазоне от -1 до +1 включительно. Соответствующие ковариации не масштабируются. И коэффициент корреляции, и ковариация являются мерами степени, в которой две переменные «изменяются вместе».

Средство ковариации вычисляет значение функции листа КОВАРИАЦИЯ. P для каждой пары переменных измерения. (Прямое использование КОВАРИАЦИИ. P вместо инструмента ковариации является разумной альтернативой, когда есть только две переменные измерения, то есть N=2.) Запись по диагонали выходной таблицы инструмента Ковариация в строке i, столбец i - это ковариация i-й переменной измерения с самой собой. Это просто дисперсия генеральной совокупности, вычисленная функцией ДИСП.Г на листе.

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

Описательная статистика

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

Экспоненциальное сглаживание

Инструмент анализа "Экспоненциальное сглаживание" применяется для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. При анализе используется константа сглаживания a, величина которой определяет степень влияния на прогнозы погрешностей в предыдущем прогнозе.

Двухвыборочный t-тест для дисперсии

Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.

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

С помощью этого инструмента вычисляется значение f F-статистики (или F-коэффициент). Значение f, близкое к 1, показывает, что дисперсии генеральной совокупности равны. В выходной таблице, если f < 1 "P(F <= f) one-tail" дает вероятность наблюдения значения F-статистики меньше f при равных дисперсиях генеральной совокупности, а "F Critical one-tail" дает критическое значение меньше 1 для выбранного уровня значимости, Alpha. Если f > 1, то "P(F <= f) one-tail" дает вероятность наблюдения значения F-статистики больше f при равенстве дисперсий генеральной совокупности, а "F Critical one-tail" дает критическое значение больше 1 для Alpha.

Анализ Фурье

Инструмент "Анализ Фурье" применяется для решения задач в линейных системах и анализа периодических данных на основе метода быстрого преобразования Фурье (БПФ). Этот инструмент поддерживает также обратные преобразования, при этом инвертирование преобразованных данных возвращает исходные данные.

Гистограмма

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

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

Скользящее среднее

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

где

• N — число предшествующих периодов, входящих в скользящее среднее;
• Aj — фактическое значение в момент времени j
• Fj — прогнозируемое значение в момент времени j

Генерация случайных чисел

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

Ранг и персентиль

Средство анализа рангов и процентилей создает таблицу, содержащую порядковый и процентный ранг каждого значения в наборе данных. Можно анализировать относительное положение значений в множестве данных. Этот инструмент использует функции листа РАНГ. EQ и PERCENTRANK. ИНК. Для учета связанных значений используйте функцию РАНГ. EQ , которая рассматривает связанные значения как имеющие один и тот же ранг, или используйте РАНГ. Функция AVG , которая возвращает средний ранг для связанных значений.

Регрессия

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

Инструмент Регрессия использует функцию листа ЛИНЕЙН.

Выборка

Инструмент анализа "Выборка" создает выборку из генеральной совокупности, рассматривая входной диапазон как генеральную совокупность. Если совокупность слишком велика для обработки или построения диаграммы, можно использовать представительную выборку. Кроме того, если предполагается периодичность входных данных, то можно создать выборку, содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж, создание выборки с периодом 4 разместит в выходном диапазоне значения продаж из одного и того же квартала.

t-тест

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

Для всех трех средств, перечисленных ниже, значение t вычисляется и отображается как "t-статистика" в выводимой таблице. В зависимости от данных это значение t может быть отрицательным или неотрицательным. В предположении о том, что базовая совокупность равна среднему, если t < 0, "P(T <= t) одностороннее" дает вероятность того, что будет наблюдаться более отрицательное значение t-статистики, чем t. Если t >= 0, то "P(T <= t) one-tail" дает вероятность того, что будет наблюдаться более положительное значение t-статистики, чем t. "t критическое одностороннее" дает пороговое значение, так что вероятность наблюдения значения t-статистики большего или равного "t критическое одностороннее" равно "Альфа".

"P(T <= t) two-tail" дает вероятность того, что будет наблюдаться значение t-статистики, которое больше по абсолютному значению, чем t. "P критическое двустороннее" выдает пороговое значение, так что значение вероятности наблюдения значения t- статистики, по абсолютному значению большего, чем "P критическое двустороннее", равно "Альфа".

Парный двухвыборочный t-тест для средних

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

Двухвыборочный t-тест с одинаковыми дисперсиями

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

Двухвыборочный t-тест с различными дисперсиями

Этот инструмент анализа выполняет двухвыборочный критерий Стьюдента. В этой форме t-теста предполагается, что два набора данных получены из распределений с неравными дисперсиями. Он называется гетероскедастическим t-критерием. Как и в предыдущем случае с равными дисперсиями, этот t-критерий можно использовать, чтобы определить, вероятно, что эти две выборки получены из распределений с равными средними генеральной совокупностью. Используйте этот тест, когда в двух выборках есть разные испытуемые. Используйте парный тест, описанный в следующем примере, когда есть одна группа испытуемых и две выборки представляют измерения для каждого субъекта до и после лечения.

Для определения тестовой величины t используется следующая формула.

Следующая формула используется для вычисления степеней свободы, df. Поскольку результатом вычислений обычно не является целое число, значение df округляется до ближайшего целого, чтобы получить критическое значение из таблицы t. Функция T.ТЕСТ на листе Excel использует вычисленное значение df без округления, так как вычисление значения для функции СТЬЮ ТЕСТ можно и с нецелочисленным значением df. Из-за этих разных подходов к определению степеней свободы результаты функции T.ТЕСТ и этого инструмента t-теста будут отличаться в случае неравных дисперсий.

Z-тест

Инструмент анализа z-Критерий: две выборки для средних выполняет двухвыборочный z-тест для средних с известными дисперсиями. Этот инструмент используется для проверки нулевой гипотезы об отсутствии разницы между двумя средними генеральной совокупностью на основе односторонних или двусторонних альтернативных гипотез. Если дисперсия неизвестна, следует использовать функцию листа Z.ТЕСТ .

При использовании этого инструмента следует внимательно просматривать результат. "P(Z <= z) однохвостый" на самом деле является P(Z >= ABS(z)), вероятностью z-значения дальше от 0 в том же направлении, что и наблюдаемое значение z, когда нет разницы между средними генеральной совокупностью. "P(Z <= z) two-tail" на самом деле является P(Z >= ABS(z) или Z <= -ABS(z)), вероятностью того, что z-значение будет дальше от 0 в любом направлении, чем наблюдаемое z-значение, когда нет разницы между средними генеральной совокупностью. Двусторонний результат является односторонним результатом, умноженным на 2. Инструмент "z-тест" можно также применять для гипотезы об определенном ненулевом значении разницы между двумя средними генеральных совокупностей. Например, этот тест можно использовать для определения разницы выступлений на соревнованиях двух автомобилей разных марок.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

См. также

Создание гистограммы в Excel 2016

Создание диаграммы Парето в Excel 2016

Загрузка пакета анализа в Excel

ИНЖЕНЕРНЫЕ функции (справка)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)