Как добавить вкладку анализ в excel

Надстройка Пакет анализа EXCEL

Надстройка Пакет анализа ( Analysis ToolPak ) доступна из вкладки Данные , группа Анализ . Кнопка для вызова диалогового окна называется Анализ данных .

Если кнопка не отображается в указанной группе, то необходимо сначала включить надстройку (ниже дано пояснение для EXCEL 2010/2007):

• на вкладке Файл выберите команду Параметры , а затем — категорию Надстройки .
• в списке Управление (внизу окна) выберите пункт Надстройки Excel и нажмите кнопку Перейти .
• в окне Доступные надстройки установите флажок Пакет анализа и нажмите кнопку ОК.

СОВЕТ : Если пункт Пакет анализа отсутствует в списке Доступные надстройки , нажмите кнопку Обзор , чтобы найти надстройку. Файл надстройки FUNCRES.xlam обычно хранится в папке MS OFFICE, например C :\ Program Files \ Microsoft Office \ Office 14\ Library \ Analysis или его можно скачать с сайта MS.

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

Ниже описаны средства, включенные в Пакет анализа (по теме каждого средства написана соответствующая статья – кликайте по гиперссылкам).

• Однофакторный дисперсионный анализ (ANOVA: single factor);
• Двухфакторный дисперсионный анализ с повторениями (ANOVA: two factor with replication);
• Двухфакторный дисперсионный анализ без повторений (ANOVA: two factor without replication);
• Корреляция (Correlation) ;
• Ковариация (Covariance) ;
• Описательная статистика (Descriptive Statistics) ;
• Экспоненциальное сглаживание (Exponential Smoothing);
• Двухвыборочный F-тест для дисперсии (F-test Two Sample for Variances) ;
• Анализ Фурье (Fourier Analysis);
• Гистограмма (Histogram);
• Скользящее среднее (Moving average);
• Генерация случайных чисел (Random Number Generation) ;
• Ранг и Персентиль (Rank and Percentile) ;
• Регрессия (Regression) — простая регрессия; для множественной регрессии см. здесь ;
• Выборка (Sampling) ;
• Парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means) ;
• Двухвыборочный t-тест с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances) ;
• Двухвыборочный t-тест с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances) ;
• Двухвыборочный z-тест для средних (z-Test: Two Sample for Means) .

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

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

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

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

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

1. Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.
2. В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти. Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.
3. В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.
   • Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.
   • Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

Примечание: Чтобы включить функции Visual Basic для приложений (VBA) для средства анализаПакет анализа, можно загрузить надстройку Analysis ToolPak — VBA так же, как и средство анализа. В поле Доступные надстройки выберите поле Инструмент анализаПакет — VBA проверка.

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

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

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

Это средство выполняет простой анализ дисперсии данных для двух или более выборок. Анализ позволяет проверить гипотезу о том, что каждая выборка извлекается из одного базового распределения вероятностей и альтернативной гипотезы о том, что базовые распределения вероятностей не одинаковы для всех выборок. Если есть только два примера, можно использовать функцию листа T.ТЕСТ. При использовании более двух выборок нет удобного обобщения T.Вместо этого можно использовать test и модель Anova Single Factor.

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

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

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

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

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

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

Корреляция

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

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

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

Ковариация

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

Средство ковариации вычисляет значение функции листа COVARIANCE. P для каждой пары переменных измерения. (Прямое использование COVARIANCE. P, а не средство ковариации является разумной альтернативой, если есть только две переменные измерения, то есть N=2.) Запись по диагонали выходной таблицы средства ковариации в строке i, столбец i является ковариантной i-й переменной измерения с самим собой. Это всего лишь дисперсии численности для этой переменной, вычисленная с помощью функции листа VAR.P.

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

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

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

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

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

Примечание: Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к большим промежуткам между предсказанными значениями.

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

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

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

Анализ Фурье

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

Гистограмма

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

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

Совет: В Excel 2016 теперь можно создавать гистограммы и диаграммы Парето.

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

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

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

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

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

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

Средство анализа ранга и процентиля создает таблицу, содержащую порядковый номер и процентный ранг каждого значения в наборе данных. Можно проанализировать относительное положение значений в наборе данных. Это средство использует функции листа RANK. EQ иPERCENTRANK. INC. Если вы хотите учесть связанные значения, используйте RANK. Функция EQ , которая обрабатывает связанные значения как имеющие одинаковый ранг, или использует RANK.Функция AVG, которая возвращает средний ранг для связанных значений.

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

Средство регрессии использует функцию листа LINEST.

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

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

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

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

Примечание: Одним из результатов теста является совокупная дисперсия (совокупная мера распределения данных вокруг среднего значения), вычисляемая по следующей формуле:

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

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

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

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

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

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

Средство анализа z-Test: Two Sample for Means выполняет два примера z-Test для средств с известными отклонениями. Этот инструмент используется для проверки нулевой гипотезы о том, что между двумя демографическими средствами нет различий в отношении односторонних или двусторонних альтернативных гипотез. Если отклонения не известны, функция листа Z.Вместо этого следует использовать TEST.

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

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

Как загрузить пакет инструментов анализа в Excel

Analysis ToolPak — это бесплатная надстройка Microsoft Excel, которая предоставляет инструменты, необходимые для выполнения сложных статистических, финансовых или инженерных анализов.

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

1. Щелкните вкладку « Файл » в левом верхнем углу, затем щелкните « Параметры » .

1. В разделе « Надстройки » нажмите « Пакет анализа», затем нажмите «Перейти» .

3. Установите флажок « Пакет анализа » и нажмите « ОК ».

1. На вкладке « Данные » в группе « Анализ » теперь у вас есть возможность щелкнуть « Анализ данных» , что даст вам возможность выполнять множество сложных анализов.

Как сделать анализ данных в Excel

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

Активация пакета анализа через параметры

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

1. Перейдите во вкладку «Файл» и затем в меню слева нажмите кнопку «Параметры».
2. В окне «Параметры Excel» перейдите в раздел «Надстройки». Здесь можно увидеть, какие расширения установлены на вашем ПК. Напротив строки «Управление», которая находится в нижней части окна, выберите «Надстройки Excel» и нажмите кнопку «Перейти».
3. Теперь установите галочку напротив «Пакет анализа». После этого уже подтверждаем активацию надстройки. Для этого нажимаем кнопку «OK».

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

1. Переходим во вкладку «Данные» и находим подключенный пакет анализа. Он располагается в правой части Панели инструментов. В списке функций найдите пункт «Корреляция».
2. На входе функция принимает несколько аргументов. В первую очередь, нужно указать диапазон ячеек, в которые добавлены числовые данные, отражающие статистическую информацию. Также следует выбрать способ группирования. Ниже задаем выходной интервал или выбираем новый лист для размещения результатов.
3. После того как программа проанализирует указанный пользователем диапазон, будет возвращен результат анализа. В данном случае нам удалось рассчитать коэффициент корреляции, который оказался равен единице. Это говорит о том, что переменные во втором столбце увеличиваются по мере увеличения чисел в первом.

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

Включение надстройки через панель «Разработчик»

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

1. В первую очередь откройте вкладку «Разработчик». Далее нажмите кнопку «Надстройки».
2. Оставьте отметку напротив строчки «Пакет анализа». После этого нажмите кнопку «OK».

У пользователей может возникнуть вопрос — что делать, если вкладки «Разработчик» в ленте нет. В данном случае ее нужно включить вручную через настройки. Для этого перейдите во вкладку «Файл» и откройте «Параметры». Далее на открывшейся странице в раздел «Настроить ленту» ставим галочку напротив строки «Разработчик». Подтверждаем активацию нажатием кнопки «OK». После этого можно пользоваться включенным пакетом в софте.

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

Подводим итоги

«Анализ данных» — встроенный пакет функций MS Excel, предназначенный для более сложных математических вычислений. Данный набор включает 19 инструментов, позволяющих проанализировать данные, используя сложные формулы и подробные отчеты. Подключить этот пакет можно через параметры программы или через соответствующий инструмент в панели «Разработчик».