Статистические функции Excel: LOGEST

Сводка

В этой статье описывается функция LOGEST в Microsoft Office Excel 2003 и более поздних версиях Excel. Он показывает, как используется функция, и сравнивает результаты функции в Excel 2003 и более поздних версиях Excel с результатами функции в более ранних версиях Excel.

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

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

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

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

Функция LOGEST(known_y, known_x, констант, статистика) используется для выполнения регрессии, где установлена экспоненциальная кривая. Используется критерий наименьшего квадрата, и LOGEST пытается найти наиболее подходящее для этого критерия. Known_y представляет данные для зависимой переменной, а known_x — данные о одной или нескольких независимых переменных. Второй аргумент является необязательным. Если он опущен, предполагается, что это массив того же размера, что и known_y, содержащий значения (1, 2, 3, ...).

При условии, что существуют переменные прогнозирования p, LOGEST соответствует уравнению следующей формы:

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

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

Для последнего аргумента LOGEST задано значение TRUE, если требуется дополнительная статистика, включая различные суммы квадратов, r-квадрат, f-статистику и стандартные ошибки коэффициентов регрессии. В этом случае logest необходимо ввести в виде формулы массива. Последний аргумент является необязательным. Если он опущен, он интерпретируется как FALSE. Размеры массива — это пять строк по числу столбцов, равных числу независимых переменных, плюс один, если третий аргумент имеет значение TRUE (плюс 0, если третий аргумент не имеет значения TRUE).

Если для третьего аргумента "константы" задано значение TRUE, необходимо, чтобы модель регрессии включала константу b в предыдущее уравнение в своей модели. Если задано значение FALSE, мультипликативная константа b исключается из модели регрессии путем принудительного ее значения равным одному. Третий аргумент необязателен. Если он опущен, он интерпретируется как TRUE.

В этой статье предполагается, что данные упорядочены по столбцам таким образом, что known_y — это столбец данных y, а known_x — один или несколько столбцов данных x. Размеры или длины каждого из этих столбцов должны быть равными. Все следующие наблюдения в равной степени верны, если данные не расположены по столбцам, но проще обсудить наиболее часто используемый случай. Кроме того, предположим, что последний аргумент logest всегда имеет значение TRUE и вас всегда интересуют подробные выходные данные. Это предположение помогает выявить числовые проблемы в более ранних версиях Excel. Некоторые числовые проблемы по-прежнему присутствуют, если для этого аргумента используется значение FALSE.

В этой статье используются следующие примеры, чтобы показать, как LOGEST связан с LINEST, и указать на проблемы с LINEST в более ранних версиях Excel, которые приводят к проблемам с LOGEST. Хотя код для LOGEST не был переписан для Excel 2003 и более поздних версий Excel, в код для LINEST были внесены значительные изменения и улучшения. LOGEST эффективно вызывает LINEST, выполняет LINEST, изменяет выходные данные LINEST и представляет его вам. Поэтому следует знать о проблемах при выполнении LINEST. Чтобы получить дополнительные сведения о LINEST, щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:

828533 описание функции LINEST в Excel 2003 и более поздних версиях Excel

Файл справки LINEST также был изменен для Excel 2003 и более поздних версий Excel и полезен для получения дополнительных сведений. Поскольку в этой статье основное внимание уделяется числовым проблемам в более ранних версиях Microsoft Excel, в этой статье не содержится много практических примеров LOGEST. Файл справки LOGEST содержит два полезных примера.

Синтаксис

LOGEST(known_y's, known_x's, constant, statistics)

Аргументы, known_y и known_x, должны быть массивами или диапазонами ячеек, имеющими связанные измерения. Если known_y — это один столбец за строками m, known_x должны быть c столбцами по m строкам, где c больше или равен одному. C — количество переменных прогноза; m — количество точек данных. Аналогичные связи должны сохраняться, если known_y размещены в одной строке; в этом случае known_x должны быть в строках r. (Значение R должно быть больше или равно 1.) Константа и статистика — это логические аргументы, для которых необходимо задать значение TRUE или FALSE. В Excel эти аргументы должны иметь значение 0 или 1 и интерпретируются Excel как FALSE или TRUE соответственно. Последние три аргумента для LOGEST являются необязательными. Если опустить второй аргумент, logest предполагает, что один прогностический метод содержит записи {1, 2, 3, ...}. Если опустить третий аргумент, он будет интерпретирован как TRUE. Если опустить четвертый аргумент, он интерпретируется как FALSE.

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

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

Два отдельных примера листа Excel иллюстрируют следующие основные понятия:

• Взаимодействие LOGEST с LINEST
• Проблемы в Microsoft Excel 2002 и более ранних версиях Excel, когда третий аргумент LOGEST или LINEST имеет значение FALSE или опущен.
• Проблемы из-за коллинеарных known_x в LOGEST или LINEST в Excel 2002 и более ранних версиях.

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

828533 описание функции LINEST в Excel 2003 и более поздних версиях Excel

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

1. Создайте пустой лист Excel, а затем скопируйте таблицу далее в этом разделе.

2. Щелкните ячейку A1 на пустом листе Excel. Затем вставьте записи, чтобы таблица заполняла ячейки A1:H17 на листе.

3. Вставив таблицу на новый лист Excel, нажмите кнопку Параметры вставки, а затем выберите Пункт Форматирование назначения.

4. Пока по-прежнему выбран вставленный диапазон, выполните одну из следующих процедур в соответствии с используемой версией Excel:

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

Данные для LOGEST находятся в ячейках A4:B6. Подробные результаты для Excel 2002 и более ранних версий Excel, а также для Excel 2003 и более поздних версий Excel представлены в ячейках D4:E8 и G4:H8 соответственно. Ячейки A11:B13 отображают те же known_x, но known_y в ячейках A4:A6 были преобразованы путем принятия естественного логарифма с помощью функции LN Excel. Затем метод LINEST вызывается для этих преобразованных данных, а результаты отображаются в ячейках G11:H15. Обратите внимание, что результаты в ячейках G12:H15 совпадают с результатами LOGEST в ячейках G5:H8. Коэффициенты LINEST в ячейках G11:H11 преобразуются с помощью экспоненциации. То есть коэффициенты LINEST в ячейках преобразуются с помощью функции Excel EXP для вычисления коэффициентов LOGEST в ячейках G4:H4. Эту связь можно проверить с помощью функции EXP в ячейках G17:H17. Чтобы подвести итоги взаимодействия LOGEST и LINEST, выполните следующую последовательность действий:

1. Вы вызываете logest(known_y, known_x, constant, TRUE).
2. LOGEST вызывает LINEST (LN known_y, known_x, constant, TRUE).
3. LOGEST получает таблицу результатов из этого вызова LINEST.
4. LOGEST изменяет коэффициенты LINEST в первой строке таблицы результатов LINEST по экспонентации. Например, замените каждый коэффициент LINEST, m, на EXP(m).
5. LOGEST возвращает эту измененную таблицу результатов LINEST в виде таблицы результатов LOGEST.

Если logest возвращает соответствующие результаты, linest должен создать соответствующие результаты на шаге 3. Проверьте ячейки D13:D15. Ячейка D13 содержит значение в квадрате r, ячейка D14 — статистику f, а ячейка D15 — сумму квадратов регрессии LINEST.

В Excel 2002 и более ранних версиях Excel, когда метод LINEST вызывается с третьим аргументом, равным FALSE, он всегда вычисляет некорректную сумму квадратов регрессии, так как использует формулу, которая не является правильной. Эта проблема устранена в Excel 2003 и более поздних версиях Excel. Обратите внимание, что эта проблема не влияет на значения в первых двух строках выходной таблицы. В Excel 2002 и более ранних версиях Excel в статье LINEST описывается обходное решение для создания соответствующих значений в последних трех строках выходной таблицы LINEST. Если вы используете более раннюю версию Excel и хотите задать для третьего аргумента значение LOGEST значение FALSE, рекомендуется явно выполнить шаги 2 и 3 предыдущей процедуры, а затем использовать обходной путь из статьи LINEST, чтобы изменить последние три строки выходной таблицы LINEST.

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

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

1. Создайте пустой лист Excel, а затем скопируйте следующую таблицу.

2. Щелкните ячейку A1 на пустом листе Excel. Затем вставьте записи, чтобы таблица заполняла ячейки A1:N27 на листе.

3. Вставив таблицу на новый лист Excel, нажмите кнопку Параметры вставки, а затем выберите Пункт Форматирование назначения.

4. Пока по-прежнему выбран вставленный диапазон, выполните одну из следующих процедур в соответствии с используемой версией Excel:

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

Данные включаются в ячейки A1:C6. Записи в ячейках D2:D6 не являются частью данных. Эти записи используются для иллюстрации в следующем абзаце. Результаты двух разных вызовов LOGEST отображаются для Excel 2002 и более ранних версий Excel в ячейках E8:G20, а для Excel 2003 и более поздних версий Excel — в ячейках I8:K20.

Первая модель в строках от 8 до 13 использует столбцы B и C в качестве прогнозов и запрашивает в Excel модель константы, где третий аргумент имеет значение TRUE. Затем Excel эффективно вставляет дополнительный столбец прогнозора, который выглядит так же, как ячейки D2:D6. Обратите внимание, что записи в столбце C в строках 2–6 точно равны сумме соответствующих записей в столбцах B и D. Таким образом, коллинеарность возникает потому, что столбец C является суммой, кратной столбцу B, а дополнительный столбец Excel с 1 вставляется, так как третий аргумент logest был опущен или true, что является "нормальным" случаем. Эта коллинеарность вызывает числовые проблемы, и Excel 2002 и более ранних версий Excel не могут вычислить результаты, а таблица выходных данных LOGEST заполнена #NUM!.

Любая версия Excel может обрабатывать вторую модель в строках от 15 до 20. Коллинарность не возникает в этой модели, и пользователь просит Excel моделировать константу. Этот пример приведен здесь по следующим двум причинам:

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

В Excel 2003 и более поздних версиях Excel сведения отображаются в выходной таблице LOGEST, а не в сообщении или текстовой строке. Коэффициент регрессии, равный единице, и стандартная ошибка которого равна нулю, соответствует коэффициенту для столбца, который был удален из модели. (Пример см. в записях в ячейках I9:I10.) В этом случае LOGEST удаляет столбец C (коэффициенты в ячейках I9, J9, K9 соответствуют столбцам C, B и в столбце константы Excel соответственно). При возникновении коллинеарности любой из участвующих столбцов может быть удален.

Во второй модели в строках от 16 до 20 коллинеарность не возникает, и ни один из столбцов не удаляется. Прогнозируемые значения y одинаковы в обеих моделях, так как удаление избыточного столбца, который является суммой кратных других (первая и вторая модели), не снижает правильность соответствия результирующей модели. Такие столбцы удаляются именно потому, что они не представляют собой дополнительную ценность при поиске наиболее подходящих квадратов. Кроме того, в выходных данных Excel 2003 и более поздних версий Excel в ячейках I8:K20 последние три строки выходных таблиц совпадают, а записи в ячейках I16:J17 и ячейках J9:K10 совпадают. Это показывает, что те же результаты получаются, когда столбец C включен в модель, но признан избыточным (выходные данные в I9:K13), как и при удалении столбца C до запуска LOGEST (выходные данные в I16:J20). Эти выходные данные соответствуют основному принципу при наличии коллинеарности.

Коллинарность определяется в LINEST в Excel 2003 и более поздних версиях Excel с помощью совершенно другого подхода, QR-декомпозиции, для решения коэффициентов регрессии. В статье LINEST описывается пошаговое руководство по алгоритму декомпозиции QR для небольшого примера.

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

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

ФУНКЦИЯ LINEST использовала формулу, которая не является правильной для общей суммы квадратов, если для третьего аргумента в LINEST задано значение FALSE. Эта формула привела к неправильным значениям суммы квадратов регрессии. Кроме того, значения, зависящие от суммы квадратов регрессии, r в квадрате и статистики f, являются неправильными. (См. обходной путь в статье LINEST, если вы используете более раннюю версию Excel.) Поэтому пользователи LOGEST должны использовать это обходное решение при вызове LOGEST с третьим аргументом, равным FALSE.

Независимо от значения третьего аргумента, функция LINEST вычислялась с помощью подхода, который не устранял проблемы с коллинарностью. Коллинарность вызывала ошибки округления, стандартные ошибки коэффициентов регрессии, которые не подходили, и степени свободы, которые не подходили. В некоторых случаях ошибки округления были достаточно серьезными, чтобы таблица выходных данных LINEST была заполнена #NUM!. Функция LINEST обычно предоставляет приемлемые результаты, если выполняются следующие условия:

• Пользователи уверены, что столбцы прогнозора не являются коллинеарными (или почти коллинеарными).
• Третий аргумент linest имеет значение TRUE или опущен.

Таким образом, LOGEST обычно предоставляет приемлемые результаты, если столбцы прогнозора не являются коллинеарными (или почти коллинарными) и если третий аргумент logest имеет значение TRUE или опущен.

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

В LINEST были внесены следующие улучшения:

• Исправлена формула для общей суммы квадратов, где третий аргумент LINEST имеет значение FALSE.
• Метод QR-декомпозиции используется для определения коэффициентов регрессии.

Декомпозиция QR имеет два преимущества:

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

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

Выводы

Производительность LOGEST была повышена, так как функция LINEST была значительно улучшена в Excel 2003 и более поздних версиях Excel. Если вы используете более раннюю версию Excel, убедитесь, что столбцы прогнозора не являются коллинеарными, прежде чем использовать LOGEST. Кроме того, будьте осторожны используйте обходной путь, представленный в статье LINEST, когда для третьего аргумента LOGEST задано значение FALSE. Хотя эта информация в этой статье и в статье LINEST может показаться тревожной для пользователей Excel 2002 и более ранних версий Excel, в небольшом проценте случаев коллинарность является проблемой. Вызовы LOGEST с третьим аргументом, равным FALSE, также, вероятно, встречаются относительно редко на практике. Более ранние версии Excel дают приемлемые результаты LOGEST, если нет коллинеарности и если третий аргумент LOGEST имеет значение TRUE или опущен.