Описание эффектов улучшенных статистических функций пакета анализа Excel

Переводы статьи Переводы статьи
Код статьи: 829208 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

Аннотация

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

Сведения о Microsoft Excel 2004 для Mac

Статистические функции в Excel 2004 для Mac обновлены по тем же алгоритмам, которые использовались для обновления статистических функций Microsoft Office Excel 2003 и последующих версий Excel. Любые сведения в этой статье, описывающие работу или обновление функции в Excel 2003 и последующих версиях Excel также применимы к Excel 2004 для Mac.

Дополнительная информация

Код пакета анализа не изменялся напрямую, за исключением усовершенствований в трех средствах пакета анализа ANOVA.

В некоторых инструментах пакета анализа в Excel 2003 и последующих версиях Excel повысилась математическая производительность, поскольку инструмент вызывает статистическую функцию Excel, которая усовершенствовалась в Excel 2003 и последующих версиях Excel. Если в ранних версиях Excel и последующих результаты различаются, результаты в Excel 2003 и более новых версиях будут более точными.

Большинство пользователей не заметит разницы в результатах между различными версиями Excel. Причина в том, что различие обычно вызвано ошибками округления, заметными только в отдельных случаях. Но эта статья прежде всего рассматривает случай, когда различие возникает из-за неверной формулы в Microsoft Excel 2002 и предыдущих версиях Excel. Избегайте инструмента этих версий.

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

Во-первых, избегайте средства Регрессия при установке флажка Константа – ноль. Это устранено в Excel 2003 и последующих версиях Excel. Не требуется избегать инструмента Регрессия при снятии флажка Константа – ноль (более частый в практике случай).

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

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

Ниже в этой статье приводятся разделы, посвященные отдельным инструментам пакета анализа. Инструменты, не перечисленные ниже не получили улучшения в Excel 2003 и последующих версиях Excel.

ANOVA: Однофакторный, двухфакторный с повторениями и двухфакторный без повторений

Каждый из этих трех инструментов ANOVA был переписан с целью обновления процедуры вычисления по двухпроходному алгоритму, математически более точному. Эти улучшения похожи на улучшения в статистических функциях, подсчитывающих суммы квадратичных отклонений от средней (например: ДИСП, СТАНДОТКЛОН, НАКЛОН, ПИРСОН).

Дополнительные сведения о пакете анализов ANOVA см. в следующей статье базы знаний Майкрософт:
829215 Описание математических улучшений инструментов пакета анализа ANOVA в Excel (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Корреляция

Этот инструмент не изменялся. В то же время существует небольшое различие между инструментом Корреляция и Ковариация, присутствующая во всех версиях Excel. Средство Корреляция возвращает нижнюю треугольную корреляциционную таблицу с первым на диагонали и корреляциями вне диагонали. Средство использует функцию КОРРЕЛ для расчета внедиагональных элементов и заполняет эти элементы значениями, возвращаемыми КОРРЕЛ. (Таким образом, при изменении любых элементов данных, элементы таблицы не изменяются. Сравните это поведение с поведением Ковариации.)

Ковариация

Этот инструмент возвращает нижнюю треугольную ковариационную таблицу с дисперсией на диагонали и ковариациями вне диагонали. Ячейки на диагонали содержат формулу "=ДИСПР(...)" поэтому при изменении элементов данных результат в таблице также меняется. Функция ДИСПР была улучшена в Office Excel 2003 и более поздних версиях Excel.

Дополнительные сведения о ДИСПР см. в следующей статье базы знаний Майкрософт:
826393 Статистические функции Excel: ДИСПР (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Средство Ковариация использует функцию КОВАР для расчета внедиагональных элементов и заполняет эти элементы значениями, возвращаемые КОВАР. Таким образом при изменении элементов данных внедиагональные элементы не изменяются.

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

Этот инструмент вызывает статистические функции Excel для любых вычислений. Поскольку функции ДИСП и СТАНДОТКЛОН были улучшены в Excel 2003 и более поздних версиях Excel, в некоторых случаях возможны различные значения при округлении.

Дополнительные сведения о ДИСП см. в следующей статье базы знаний Майкрософт:
826112 Статистические функции Excel: ДИСП (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

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

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

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

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

Средство Генерация случайных чисел также создает различные значения из различных распределений вероятности, хотя СЛЧИС представляет собой отдельную опцию средства: Равномерное распределение в диапазоне между 0 и 1. В этой статье описано, как сочетать СЛЧИС со статистическими функциями Excel для получения таких значений.

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

Известно, что в Excel 2002 и более ранних версий генератор случайных чисел пакета анализа и СЛЧИС слабо выполняли стандартные тесты на случайность. Эффективность была низкая, поскольку продолжительность цикла перед повтором псевдослучайных чисел была слишком короткой. Это становилось помехой только тогда, когда требовалось много случайных цифр.

Функция СЛЧИС была улучшена в Excel 2003 и более поздних версий Excel, поэтому сейчас СЛЧИС проходит все подобные стандартные тесты. Последовательность случайных чисел СЛЧИС начинает повторяться после генерации более миллиона цифр.

Дополнительные сведения о СЛЧИС см. в следующей статье базы знаний Майкрософт:
828795 Описание функции RAND в Excel 2007 и в Excel 2003
В то же время сам генератор случайных чисел в пакете анализа не обновлялся. Известно, что как и СЛЧИС в Excel 2002 и более ранних версиях Excel, сам генератор случайных чисел пакета анализа плохо проходит стандартные тесты на случайность и имеет короткий цикл повторения. Это ведет к негативным последствиям только когда требуется длительная последовательность случайных значений (например, 1 миллион).

Средство Генерация случайных чисел формирует случайные значения из распределений различной вероятности и равномерное распределение [0,1], распределение используется для выхода случайных чисел с помощью функции СЛЧИС. Пакет анализа сначала создает случайное число равномерно распределенные на отрезке [0,1] (или более одного такого числа), затем переводит ответ в значение из одного из следующих распределений. Для тех, кто использует СЛЧИС, для генерации множества значений в таблице ниже предлагаются формулы, использующие СЛЧИС. При использовании этой таблицы следует учитывать ряд предупреждений о случае нормального распределения пакета анализа.
Свернуть эту таблицуРазвернуть эту таблицу
РаспределениеФормула Excel с использованием СЛЧИС()
Бернулли(p)=ЕСЛИ (СЛЧИС() <= p, 1, 0)
Биномиальное(n,p)=КРИТБИНОМ (n, p, СЛЧИС())
ДискретноеСм. ниже
Нормальное(мю, сигма)=НОРМОБР(СЛЧИС(), мю, сигма)
ШаблонноеНе истинно случайное
Пуассона (среднее)См. ниже
Равномерное(мин, макс)= мин + (макс – мин) * СЛЧИС()
Существует две причины прибегнуть к функции СЛЧИС и к формуле в этой таблице вместо использования генератора случайных чисел пакета анализа в случае нормального распределения (мю, сигма). Во-первых, СЛЧИС лучше создает случайные числа, равномерно распределенные на отрезке[0,1], чем пакет анализа. Во-вторых, пакет анализа не вызывает функцию Excel НОРМОБР, но вместо этого обладает своей встроенной версией обратного нормального распределения. Она не так точна, как версия НОРМОБР в составе Excel 2003 и последующих версиях Excel. Она уступает в точности используемой аппроксимации нормального распределения (Excel использует более усовершенствованную функцию НОРМРАСП) и в точности двоичного поиска (Excel проводит его более точно, что дает значение, которое ближе к аргументу вероятности НОРМОБР). В двух словах, использование пакета анализа в этом случае не дает преимуществ из-за улучшений в Excel 2003 и последующих версиях Excel в функциях НОРМОБР, НОРМРАСП и СЛЧИС.

Для рассмотрения дискретного распределения предположим, что значения записаны в столбце B, а их вероятность – в столбце C. Понадобилось заполнить все строки столбца A значениями, достоверно меньшими по сравнению с значениями в аналогичных строках столбца B. Предположим, что есть 10 значений, и эти данные записаны в ячейках A1:C10. Затем, поскольку A1 содержит вероятность наблюдения величины, достоверно меньше первой величины, ее необходимо записать как 0. Можно использовать ВПР(СЛЧИС(), A1:C10, 2); четвертый аргумент ВПР необязательный и может быть пропущен или установлен как ИСТИНА. "2" означает, что вы хотите возвратить результат из второго столбца (столбец B в этом примере).

Пакет анализа использует адаптированный метод создания распределения Пуассона, описанный в работе Press, W.H., S.A. Teukolsky, W. T. Vetterling, и B.P. Flannery, Numerical Recipes in C, The Art of Scientific Computing, второе издание., Cambridge University Press, 1992, стр. 293-295. Воспользоваться преимуществами существующих функций Excel можно двумя способами.

Первый использует наблюдение, что случайная переменная ПУАССОН со средним m имеет распределение, хорошо аппроксимированным BINOMIAL(n, m/n) для больших n. Затем можно вызвать функции CRITBINOM(n, m/n, СЛЧИС()). Выбор n зависит от m; при n – более 1000 раз m должно быть достаточно большим.

Второй относится к распределению ПУАССОН показательной функции. Если событие происходит по процессу ПУАССОН с частотой m в единицу времени, время между событиями имеет показательное распределение со средним 1/m. Для наблюдения ПУАССОН можно взять последовательность наблюдений из этого показательного распределения и сосчитать, как сколько из них произойдет перед тем, как их сумма превысит 1. Для оценки показательного распределения используйте ГАММАОБР(СЛЧИС(), 1, 1/m). Этот метод подойдет, когда m относительно близко к 0.

Регрессия

Средство Регрессия вызывает функцию Excel ЛИНЕЙН. Статья о функции ЛИНЕЙН описывает важные преимущества Excel 2003 и последующих версий Excel.

Дополнительные сведения о ЛИНЕЙН см. в следующей статье базы знаний Майкрософт:
828533 Описание функции ЛИНЕЙН в Excel (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
При использовании Excel 2002 или более ранних версий Excel помните о двух недостатках средства Регрессия пакета анализа в отношении ЛИНЕЙН:
  • Статистические значения регрессии сумм квадратов, r в квадрате и f всегда некорректны в случае, когда регрессия проходит через начало.

    Для ЛИНЕЙН это означает "третий аргумент устанавливается как ЛОЖЬ вместо ИСТИНА или игнорируется". Для пакета анализа это означает "установлен флажок Константа – ноль".
  • ЛИНЕЙН и пакет анализа нечувствительны к проблемам коллинеарности. Статья о функции ЛИНЕЙН обсуждает вычислительный подход к ЛИНЕЙН в Excel 2003 и последующих версиях Excel, разработанных для поиска коллинеарности или около-коллинеарности, если она существует и выполнения определенных действий.
Оба этих недостатка функции ЛИНЕЙН устранены в Excel 2003 и последующих версиях Excel. Производительность средства Регрессия пакета анализа также будет повышена. Изменений в код не вносилось, улучшения коснулись вызова улучшенной функции Excel. Автор рассматривает улучшение функции ЛИНЕЙН как наиболее важное улучшение статистической функции.

Следующая таблица показывает результат работы инструмента Регрессия для более ранних и последующих версий Excel установленным флажком Константа – ноль. Она иллюстрирует первый рассмотренный ранее недостаток. В предыдущих версиях Excel сумма регрессии отрицательна, как и значение R в квадрате.
Свернуть эту таблицуРазвернуть эту таблицу
XY
111
212
313
Excel 2002 и более ранние версии
ОБЩИЙ ВЫХОД
Статистика регрессии
Множественный R65535
R в квадрате-20.4285714
Adjusted R Square-20.9285714
Standard Error4.629100499
Observations3
ANOVA
dfSSMSFSignificance F
Regression1-40.85714286-40.85714286-1.90666667#NUM!
Residual242.8571428621.42857143
Total32
Excel 2003 and later versions of Excel version
SUMMARY OUTPUT
Regression Statistics
Multiple R0.949342311
R Square0.901250823
Adjusted R Square0.401250823
Standard Error4.629100499
Наблюдения3
ANOVA
dfSSMSFЗначимость F
Регрессия1391.1428571391.142857118.253333330.14637279
Остаточный242.8571428621.42857143
Всего3434

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

Как отмечалось ранее, избегайте этого инструмента, если есть хотя бы подозрение на отсутствующие данные. Приложения-прототипы этого теста – это эксперимент с измерениями субъектов До и После воздействия (например, вес до и после 60-дневной диеты). Если нет отсутствующих наблюдений, инструмент отлично работает. Если имеется разное число отсутствующих измерений до и после, появляется сообщение об ошибке и инструмент не выполняет расчетов совсем. Если количество отсутствующих наблюдений до и после одинаково, инструмент выдает ответ с серьезными ошибками.

Стандартная процедура – удалить субъект из данных, если отсутствуют любые измерения, до или после и анализировать данные только субъектов, содержащие измерения до и после. Функция Excel ТТЕСТ обрабатывает отсутствующие данные по этой стандартной процедуре.

Два других инструмента t-тест, двухвыборочный т-тест с одинаковыми дисперсиями и двухвыборочный т-тест с разными дисперсиями не имеют такого дефекта.

z-тест: двухвыборочный для средних

В этой статье отмечено, что в случае нормального распределения генератор случайных чисел не вызывает функцию НОРМСТОБР (или, более точно, НОРМОБР, которая вызывает НОРМСТОБР) но обладает собственной процедурой нижнего уровня для нахождения нормальных обратных величин.

Инструмент z-тест не вызывает функцию НОРМСТОБР и использует преимущества улучшений в Excel 2003 и более поздних версий Excel.

Результаты в более ранних версиях Excel

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

Три инструмента пакета анализа ANOVA были улучшены путем изменения кода пакета анализа для замещения более надежным вычислительным алгоритмом (в том же направлении, что и улучшения в функции ДИСП в Excel). Пользователи этих инструментов в предыдущих версиях Excel должны заметить разницу в отдельных ситуациях.

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

Результаты в Excel 2003 и более новых версиях Excel

Значительные улучшения были внесены в статистические функции Excel. Они переносятся в улучшения многих средств пакета анализа, вызывающих эти функции. Существует один инструмент пакета анализа, генератор случайных чисел, который не улучшился благодаря улучшению функции СЛЧИС (поскольку он работает автономно и не использует вызов СЛЧИС). Это неприятно, однако еще более неприятны отдельные случаи нормального распределения случайных наблюдений. Обратное нормальное распределение также работает автономно и не вызывает улучшенную функцию НОРМСТОБР.

В следующей таблице перечислены инструменты пакета анализа и вызываемые ими функции Excel, которые были улучшены в Excel 2003 и последующих версиях Excel. Для каждой вызываемой функции Excel можно обратиться к отдельной статье.
Свернуть эту таблицуРазвернуть эту таблицу
Инструмент пакета анализаВызываемая им функция Excel
ANOVA: Один факторДИСП, FРАСПОБР
ANOVA: Двухфакторный с повторениямиДИСП, FРАСПОБР
ANOVA: Двухфакторный без повторенийДИСП, FРАСПОБР
Корреляция
Ковариация
Описательная статистикаСТАНДОТКЛОН, СТЬЮДРАСПОБР, ДИСП
Экспоненциальное сглаживание
Двухвыборочный F-тест для дисперсииДИСП, FРАСПОБР
Анализ Фурье
Гистограмма
Скользящее среднее
Генератор случайных чисел
Ранг и персентиль
РегрессияЛИНЕЙН
ВыборкаСЛЧИС
t-Тест: Парный двухвыборочный для среднихДИСП, ПИРСОН, СТЬЮДРАСПОБР
t-Тест: Двухвыборочный с одинаковыми дисперсиямиДИСП, СТЬЮДРАСПОБР
t-Тест: Двухвыборочный с разными дисперсиямиДИСП, СТЬЮДРАСПОБР
z-Тест: Двухвыборочный для среднихНОРМРАСП, НОРМСТОБР
Для всех функций, представленных в таблице, кроме функций ЛИНЕЙН и СЛЧИС, вы вероятно увидите разницу между более ранними и последующими версиями Excel только в ошибках округления в отдельных ситуациях. Функция ЛИНЕЙН была улучшена, как описано выше. Функция СЛЧИС также была улучшена. Любопытно, что инструмент Выборка вызывает СЛЧИС, но генератор случайных чисел использует только встроенный генератор, имеющий более низкую эффективность, когда нужна длительная последовательность случайных чисел.

Выводы

За исключением изменений в коде трех инструментов пакета анализа ANOVA, код пакета анализа не переписывался. В то же время он получил преимущества благодаря вызову улучшенных функций Excel, как видно из таблицы средств пакета анализа. Дефекты t-теста: Парный двухвыборочный тест для средних не был исправлен в Excel 2003 и более новых версиях Excel. Наиболее заметное улучшение вероятно, имеется в средстве Регрессия, где функция ЛИНЕЙН больше не возвращает неверный результат при установке флажка Константа – ноль и где ЛИНЕЙН служит для надлежащей обработки коллинеарности.

Свойства

Код статьи: 829208 - Последний отзыв: 29 декабря 2007 г. - Revision: 4.0
Информация в данной статье относится к следующим продуктам.
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
Ключевые слова: 
kbinfo kbformula kbfunctions kbfuncstat kbexpertisebeginner KB829208

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com