Анализ данных. Подбор параметра, поиск решения в табличном процессоре.

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

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

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

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

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

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

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

Корреляционный анализ

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

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

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

Ковариационный анализ

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

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

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

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

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

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

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

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

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

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

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

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

Анализ Фурье

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

Гистограмма

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

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

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

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

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

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

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

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

Используется для вывода таблицы, содержащей порядковый и процентный ранги для каждого значения в наборе данных. Данная процедура может быть применена для анализа относительного взаиморасположения данных в наборе. Она использует функции РАНГ и ПРОЦЕНТРАНГ. РАНГ не работает со связанными значениями. Если требуется учитывать связанные значения, можно воспользоваться функцией РАНГ вместе с коэффициентом изменения, описанным в файле справки для функции РАНГ.

Регрессия

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

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

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

Выборка

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

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

T-тест

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

Z-тест

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

При использовании функции z-тест следует внимательно просматривать результат. “P(Z <= z) одностороннее” на самом деле есть P(Z >= ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. “P(Z <= z) двустороннее” на самом деле есть P(Z >= ABS(z) или Z <= -ABS(z)), вероятность z-значения, удаленного от 0 в том же направлении, что и наблюдаемое z-значение при одинаковых средних значениях генеральной совокупности. Двусторонний результат является односторонним результатом, умноженным на 2. Функцию z-тест можно применять для гипотезы об особом ненулевом значении разницы между двумя средними генеральных совокупностей.

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