Поделиться через

Статистические функции Excel: РОСТ

  • Применяется к: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Excel 2004 for Mac

Сводка

В этой статье описывается функция GROWTH в Excel, иллюстрирует использование функции и сравнивает результаты функции для Excel 2003 и более поздних версий Excel с результатами GROWTH в более ранних версиях Excel. ФУНКЦИЯ GROWTH вычисляется путем вызова связанной функции LINEST. Приведены подробные изменения в LINEST для Excel 2003 и более поздних версий Excel, а также их последствия для РОСТА.

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

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

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

Функция GROWTH(known_y, known_x, new_x, константы) используется для выполнения анализа регрессии, в котором устанавливается экспоненциальная кривая. Используется критерий наименьших квадратов, и РОСТ пытается найти наилучшее соответствие в соответствии с этим критерием. Known_y представляют данные о "зависимой переменной" и known_x представляют данные для одной или нескольких независимых переменных. В файле справки GROWTH рассматриваются редкие случаи, когда второй или третий аргумент могут быть опущены.

Предположим, что существуют p предикторных переменных, РОСТ фактически вызывает LOGEST. LOGEST соответствует уравнению следующего вида:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

Значения коэффициентов b, m1, m2, ..., mp подбираются так, чтобы наилучшим образом соответствовать данным y.

Если для последнего аргумента "constant" задано значение TRUE, требуется, чтобы модель регрессии включала коэффициент умножения b в модель регрессии. Если задано значение FALSE, b исключается путем установки его значения на 1. Последний аргумент является необязательным; Если аргумент опущен, он интерпретируется как TRUE.

Для простоты экспозиции в оставшейся части этой статьи предполагается, что данные организованы в столбцах, чтобы known_y является столбцом данных y и known_x является одним или несколькими столбцами x данных. Измерения (длины) каждого из этих столбцов должны быть равными. Также предполагается, что new_x упорядочены в столбцах, и для них должно быть одинаковое количество столбцов, как и для known_x. Все приведенные ниже наблюдения равно верны, если данные не упорядочены в столбцах, но проще обсудить один (наиболее часто используемый) случай данных.

После вычисления оптимальной модели регрессии (по сути вызывая функцию LOGEST Excel), ФУНКЦИЯ GROWTH возвращает прогнозируемые значения, связанные с new_x.

В этой статье приводятся примеры, чтобы показать, как GROWTH связан с LOGEST, и указать на проблемы с LOGEST в версиях Excel, предшествующих Excel 2003, что ведёт к проблемам с GROWTH. GROWTH эффективно вызывает LOGEST, выполняет LOGEST, использует коэффициенты регрессии в результатах LOGEST в расчете прогнозируемых значений y, связанных с каждой строкой new_x's, а затем представляет столбец этих прогнозируемых значений y. Поэтому необходимо знать о проблемах при выполнении LOGEST. При вызове LOGEST он, в свою очередь, эффективно вызывает LINEST. Хотя код для GROWTH и LOGEST не был перезаписан для Excel 2003 и для более поздних версий Excel, были сделаны обширные изменения (и улучшения) в коде LINEST.

В дополнение к этой статье, следующая статья о LINEST настоятельно рекомендуется. Он содержит несколько примеров и проблем с документами с LINEST в версиях Excel, предшествующих Excel 2003.

Дополнительные сведения о LINEST выберите следующий номер статьи, чтобы просмотреть статью в Базе знаний Майкрософт:

828533 Описание функции LINEST в Excel 2003 и Excel 2004 для Mac

Также рекомендуется использовать файл справки LINEST для Excel 2003.

В следующей статье о LOGEST объясняется, как LOGEST взаимодействует с LINEST. Эти сведения опущены здесь.

Дополнительные сведения см. в разделе статистических функций Excel: LOGEST.

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

Синтаксис

GROWTH(known_y's, known_x's, new_x's, constant)

Аргументы known_y's, known_x's и new_x's должны быть массивами или диапазонами ячеек, которые имеют связанные размеры. Если известные_y представлены в виде одного столбца и m строк, то известные_x имеют c столбцов и m строк, где c больше или равно одному. C — это число переменных прогнозатора; m — это количество точек данных. New_x затем должны иметь c столбцов и r строк, где r больше или равно одному. (Аналогичные связи в измерениях должны содержаться, если данные размещаются в строках вместо столбцов.) Константа — это логический аргумент, который должен иметь значение TRUE или FALSE (или 0 или 1), который Excel интерпретирует как FALSE или TRUE соответственно. Последние три аргумента для GROWTH являются необязательными; см. файл справки GROWTH для параметров пропуска второго аргумента, третьего аргумента или обоих; Опущение четвертого аргумента интерпретируется как TRUE.

Наиболее распространенное использование GROWTH включает два диапазона ячеек, содержащих данные, такие как GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Так как обычно существует несколько переменных прогнозатора, второй аргумент в этом примере содержит несколько столбцов. В этом примере существует 100 субъектов, одно зависимое значение переменной (known_y) для каждого субъекта и пять зависимых значений переменных (known_x) для каждого субъекта. Существует восемь более гипотетических субъектов, в которых требуется использовать GROWTH для вычисления прогнозируемых значений y.

Пример использования

Пример листа Excel представлен для иллюстрации следующих ключевых понятий:

  • Взаимодействие GROWTH с LOGEST
  • Проблемы, возникающие с функцией GROWTH (или LOGEST и LINEST) из-за коллинарных известных_x в версиях Excel до Excel 2003

Замечание

Подробное обсуждение второго маркированного элемента в контексте LINEST представлено в статье о LINEST.

Чтобы проиллюстрировать функцию GROWTH, создайте пустой лист Excel, скопируйте следующую таблицу, выберите ячейку A1 в пустом листе Excel и вставьте записи, чтобы следующая таблица заполняла ячейки A1:K35 на листе.

А Б С Д Е Ф Г Х Я J К
y: x:
=EXP(F2) 1 2 1 1
=EXP(F3) 3 4 1 2
=EXP(F4) 4 5 1 3
=EXP(F5) 6 7 1 4
=EXP(F6) 7 8 1 5
новые x: 9 11
12 14
Рост с помощью столбцов B, C Значения для Excel 2002 и более ранних версий Excel:
Значения для Excel 2003 и более поздних версий Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE) #NUM! 3400.16400895377
GROWTH с использованием только столбца B
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE) 472,432432563203 472,432432563203
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE) 3400.16400895377 3400.16400895377
Подогнанные значения из результатов функции LOGEST в Excel 2003 и более поздних версиях Excel.
Использование столбцов B, C Использование столбца B
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7) =EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8) =EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST с помощью столбцов B,C: Значения для Excel 2002 и более ранних версий Excel: Значения для Excel 2003 и более поздних версий Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 1 1,9307233720034 1.26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 225 3 #N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) =LOGEST(A2:A6,B2:C6,TRUE,TRUE) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LOGEST с использованием только столбца Б
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 1,9307233720034 1.26724101129183 1,9307233720034 1.26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 224.999999999999 3 225 3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE) =LOGEST(A2:A6,B2:B6,TRUE,TRUE) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

Замечание

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

  • В Microsoft Office Excel 2007 перейдите на вкладку "Главная" , выберите "Формат " в группе "Ячейки ", а затем выберите "Автоподбор ширины столбца".
  • В Excel 2003 в меню Формат наведите указатель на Столбец, а затем выберите Автоподбор ширины столбца.

Данные для РОСТА находятся в ячейках A1:C8. (Записи в ячейках D2:D6 не являются частью данных, но используются для иллюстрации ниже.) Результаты GROWTH для двух разных моделей для предыдущих версий Excel и более поздних версий Excel представлены в ячейках E10:E16 и I10:116 соответственно. Результаты в ячейках A10:A16 будут соответствовать используемой версии Excel. Теперь сосредоточьтесь на результатах Excel 2003 и более поздних версиях Excel при изучении того, как GROWTH вызывает LOGEST и как GROWTH использует результаты LOGEST.

Функции GROWTH и LOGEST можно рассматривать как взаимодействие в следующих этапах:

  1. Вы вызываете функцию GROWTH(known_y, known_x, new_x, константа)
  2. GROWTH вызывает LOGEST(known_y, known_x, constant, TRUE)
  3. Из этого вызова LOGEST получены коэффициенты регрессии. Эти коэффициенты отображаются в первой строке выходной таблицы LOGEST.
  4. Для каждой строки new_x прогнозируемое значение y вычисляется на основе этих коэффициентов LOGEST и значений new_x в этой строке.
  5. Вычисляемое значение на четвертом шаге возвращается в соответствующую ячейку для выходных данных функции GROWTH, которое соответствует строке new_x.

Если РОСТ возвращает соответствующие результаты, logEST должен создать соответствующие результаты на шаге 3. Так как для оценки LOGEST на шаге 3 требуется вызов LINEST, важно, чтобы LINEST хорошо вел себя. Проблемы с LINEST в версиях Excel, предшествующих Excel 2003, возникают из-за коллинеарных столбцов прогнозаторов. (В более ранних версиях Excel возникают другие проблемы с LINEST и LOGEST, которые возникают, когда для последнего аргумента GROWTH задано значение FALSE. Однако эти проблемы не влияют на результаты РОСТА и не обсуждаются здесь.)

Столбцы прогнозора (known_x) являются коллинарными, если по крайней мере один столбец, c, может быть выражен в виде суммы нескольких других столбцов, c1, c2 и других столбцов. Столбец c часто называется избыточным, так как сведения, содержащиеся в нем, можно создать из столбцов c1, c2 и других столбцов. Основным принципом в существовании коллинарности является то, что результаты должны быть не затронуты тем, включен ли избыточный столбец в исходные данные или удален из исходных данных. Так как LINEST в версиях Excel, которые раньше, чем Excel 2003, не искали коллинарность, этот принцип был легко нарушен. Столбцы прогнозора почти коллинарны, если по крайней мере один столбец, c, может быть выражен почти равным сумме нескольких других столбцов, c1, c2 и других столбцов. В этом случае "почти равно" означает небольшую сумму квадратных отклонений записей в c от соответствующих записей в весовой сумме c1, c2 и других столбцов. "Очень маленький" может быть меньше 10^(-12), например.

Первая модель в строках 10–12 использует столбцы B и C в качестве прогнозаторов и запрашивает Excel моделировать константу (последний аргумент имеет значение TRUE). Затем Excel фактически вставляет дополнительный столбец прогнозора, который выглядит так же, как ячейки D2:D6. Легко заметить, что записи в столбце C в строках 2–6 точно равны сумме соответствующих записей в столбцах B и D. Таким образом, существует коллинарность, так как столбец C является суммой нескольких из следующих элементов:

  • Столбец B
  • Excel добавляет дополнительный столбец единиц из-за того, что третий аргумент для LOGEST был опущен или указан как TRUE (обычный случай).

Это приводит к таким числовым проблемам, что версии Excel, предшествующие Excel 2003, не могут вычислять результаты. Таким образом, таблица выходных данных GROWTH заполняется #NUM!.

Вторая модель в строках 14–16 — это модель, которая может успешно обрабатывать любую версию Excel. Нет коллинарности, и пользователь снова запрашивает Excel моделировать константу. Эта модель включена здесь по следующим причинам:

  • Во-первых, это наиболее типично для практических случаев: отсутствие коллинеарности. Эти случаи обрабатываются достаточно во всех версиях Excel. Должно быть успокаивающим знать, что числовые проблемы, скорее всего, не возникают в наиболее часто встречающихся практических случаях, если у вас более ранняя версия Excel.
  • Во-вторых, этот пример используется для сравнения поведения Excel 2003 и более поздних версий Excel в двух моделях. Большинство основных статистических пакетов анализируют коллинарность, удаляют столбец, который является суммой нескольких других, из модели, и оповещают пользователя сообщением, например, "столбец C линейно зависит от других столбцов прогнозатора и был удален из анализа".

В Excel 2003 и более поздних версиях Excel такое сообщение передается не в оповещении или текстовой строке, но в выходной таблице LOGEST. GROWTH не имеет механизма доставки такого сообщения пользователю. В выходной таблице LOGEST коэффициент регрессии, который является одним, и стандартная ошибка которого равна нулю, соответствует коэффициенту для столбца, который был удален из модели. Выходные таблицы LOGEST включаются в строки 23–35, соответствующие выходным данным GROWTH в строках 10–16. Записи в ячейках I24:I25 показывают удаленный избыточный столбец прогнозных данных. В этом случае LOGEST решил удалить столбец C (коэффициенты в ячейках I24, J24, K24 соответствуют столбцам C, B и столбцу Excel соответственно). При наличии коллинарности любой из участвующих столбцов можно удалить, и выбор является произвольным.

Во второй модели в строках с 30 по 35 отсутствует коллинеарность и столбцы не удалены. Вы можете увидеть, что прогнозируемые значения y одинаковы в обеих моделях. Эта проблема возникает из-за того, что удаление избыточного столбца, являющегося суммой нескольких других, не ухудшает качество соответствия результирующей модели. Такие столбцы удаляются именно потому, что не добавляют никакой ценности при попытке найти наилучшее приближение методом наименьших квадратов. Кроме того, при проверке выходных данных LOGEST в ячейках I23:K35 в Excel 2003 и более поздних версиях Excel вы заметите, что последние три строки выходных таблиц совпадают. Кроме того, записи в ячейках I31:J32 и ячейки J24:K25 совпадают. В нем показано, что те же результаты получены, когда столбец C включен в модель, но найден избыточным (выходные данные в ячейках I24:K28), как и при удалении столбца C до запуска LOGEST (выходные данные в ячейках I31:J35). Это удовлетворяет базовому принципу существования коллинарности.

В ячейках A18:C21 корпорация Майкрософт использует данные из Excel 2003 и более поздних версий Excel, чтобы продемонстрировать, как GROWTH принимает выходные данные LOGEST и вычисляет соответствующие прогнозируемые значения y. Проверив формулы в ячейках A20:A21 и ячейках C20:C21, вы можете увидеть, как коэффициенты LOGEST объединяются с данными new_x в ячейках B7:C8 для каждой из двух моделей (используя столбцы B, C в качестве прогнозаторов; используя только столбец B в качестве прогнозора).

Collinearity определяется в LOGEST в Excel 2003 и более поздних версиях Excel, так как LOGEST вызывает LINEST. LINEST использует другой подход к решению коэффициентов регрессии. Этот подход — QR-декомпозиция. В статье LINEST содержится пошаговое руководство по алгоритму qr-декомпозиции для небольшого примера.

Сводка результатов в более ранних версиях Excel

Результаты функции GROWTH в версиях Excel, которые более ранние, чем Excel 2003, страдают из-за неточных результатов LOGEST, которые, в свою очередь, возникают из-за неточных результатов в LINEST.

LINEST вычислялся с помощью подхода, который не обращал внимания на проблемы коллинарности. Существование коллинарности вызвало ошибки округления, неуместные стандартные ошибки коэффициентов регрессии и неуместные степени свободы. Иногда проблемы округления оказываются достаточно серьезными, чтобы LINEST заполнил свою выходную таблицу значениями #NUM!. Если, как в большинстве практических случаев, вы можете быть уверены, что не было коллинеарных (или почти коллинеарных) столбцов предикторов, то LINEST, как правило, даст приемлемые результаты. Таким образом, пользователи GROWTH могут быть аналогичным образом уверены, если они могут увидеть отсутствие коллинеарных (или почти коллинеарных) предикторных столбцов.

Сводка результатов в Excel 2003 и более поздних версиях Excel

Улучшения в LINEST включают переключение на метод QR-разложения для определения коэффициентов регрессии. QR-декомпозиция имеет следующие преимущества:

  • Улучшенная числовая стабильность (как правило, более мелкие ошибки округления)
  • Анализ проблем коллинарности

Все проблемы с версиями Excel, предшествующими Excel 2003, иллюстрированным в этой статье, были исправлены для Excel 2003 и более поздних версий Excel. Эти улучшения в LINEST приводят к улучшениям в LOGEST и GROWTH.

Выводы

Производительность GROWTH была улучшена, так как LINEST была значительно улучшена для Excel 2003 и более поздних версий Excel. Улучшения в LINEST также влияют на LOGEST, потому что LOGEST вызывается функцией GROWTH. Пользователи более ранних версий Excel должны убедиться, что столбцы прогнозора не являются коллинарными, прежде чем они используют GROWTH.

Большая часть материалов, представленных в этой статье и в статье LINEST, возможно, сначала покажется вызывающей тревогу пользователям версий Excel, предшествующих Excel 2003. Однако следует отметить, что коллинарность является проблемой только в небольшом проценте случаев. Более ранние версии Excel дают допустимые результаты РОСТА, если нет коллинарности.

К счастью, улучшения в LINEST также влияют на инструмент линейной регрессии Пакета анализа (этот инструмент вызывает LINEST), а также на две другие связанные функции Excel: LOGEST и TREND.