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

  • Статья
  • Применяется к:
    Microsoft Office Excel 2007, Microsoft Office Excel 2003

Сводка

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

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

Функция INTERCEPT(known_y,known_x) возвращает значение INTERCEPT линии линейной регрессии, которая используется для прогнозирования значений y из значений x.

Синтаксис

INTERCEPT(known_y's,known_x's)

Аргументы , known_y и known_x, должны быть массивами или диапазонами ячеек, содержащими равное количество числовых значений данных. Часто функция INTERCEPT включает в себя 2 диапазона ячеек, содержащих данные, например INTERCEPT(A1:A100, B1:B100).

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

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

A Б В D
Значения y значения x
1 = 3 + 10^$D$3 Мощность 10 для добавления к данным
2 =4 + 10^$D$3 0
3 =2 + 10^$D$3
4 =5 + 10^$D$3
5 =4+10^$D$3
6 =7+10^$D$3 Excel 2002 и более ранних версий
когда D3 = 7.5
=НАКЛОН(A2:A7;B2:B7) -23717082.0762629
=INTERCEPT(A2:A7;B2:B7) -24516534.4029667
= AVERAGE(A2:A7) - A9*AVERAGE(B2:B7) когда D3 = 8
=AVERAGE(A2:A7) - 0,775280899*AVERAGE(B2:B7) #ДЕЛ/0!
-77528089.6303371

Примечание.

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

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

Может потребоваться отформатировать ячейки B2:B7 как число с 0 десятичными знаками, а ячейки A9:D13 — как число с 6 десятичными знаками.

Ячейки A2:A7 и B2:B7 содержат значения y и x-значения, которые вызывают INTERCEPT в ячейке A10.

В версиях Excel, более ранних, чем Excel 2003, функция INTERCEPT может содержать ошибки округления. Excel 2003 и более поздних версий Excel улучшает поведение INTERCEPT. INTERCEPT(known_y, known_x) — результат оценки AVERAGE(known_y) – НАКЛОН(known_y, known_x) * СРЕДНЕЕ(known_x). Хотя код для INTERCEPT не был изменен напрямую для Excel 2003 и более поздних версий Excel, поведение INTERCEPT улучшено благодаря улучшенному коду для SLOPE.

Если у вас есть более ранняя версия Excel, вы можете использовать созданный ранее лист для запуска эксперимента, чтобы обнаружить, когда возникают ошибки округления. Добавление положительной константы к каждому из наблюдений в B2:B7 не должно влиять на значение НАКЛОН. Если отобразить пары x,y с x на горизонтальной оси и y на вертикальной оси, а затем добавить положительную константу к каждому значению x, данные просто смещаются вправо. Наиболее подходящая линия регрессии по-прежнему имеет тот же наклон. Однако смещенные данные имеют другой перехват.

Если значение по умолчанию равно 0 в D3, функция НАКЛОН в A9 имеет значение 0,775280899. Ячейка A10 отображает значение INTERCEPT, а ячейка A11 — значение выражения, вычисляемого при вычислении INTERCEPT:

AVERAGE(known_y) – НАКЛОН(known_y, known_x) * AVERAGE(known_x)

Значения в ячейках A9 и A10 всегда совпадают, так как значение в A10 является именно тем, что возвращает функция INTERCEPT. ПРИ добавлении различных положительных констант в known_x не должно меняться. Ячейка A11 отображает AVERAGE(known_y) — 0,775280899 * AVERAGE(known_x). Так как ЗНАЧЕНИЕ НАКЛОН не должно изменяться, а значение 0,775280899 является значением SLOPE, если D3 = 0, значения этого выражения в A11 также должны совпадать со значениями в ячейках A9 и A10.

При увеличении значения в D3 добавьте большую константу в B2:B7. Если D3 <= 7, ошибки округления не отображаются в первых 6 десятичных знаках НАКЛОН. Но если вы попробуете 7.25, 7.5, 7.75 и 8, наклон в A9 изменится. В результате значения в ячейках A11 (согласующиеся с A10) и A12 отличаются. Однако значения в A11 (или A10) и A12 должны быть одинаковыми, так как добавление константы в known_x не должно влиять на НАКЛОН.

В D7:D13 отображаются значения, возвращаемые intercept, и значения, которые должны были вернуться, если параметр SLOPE не изменился. Эти пары значений отображаются для случаев, когда D3 = 7,5 и 8 соответственно. Ошибки округления стали настолько серьезными, что деление на 0 происходит, когда D3 = 8.

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

Если у вас есть Excel 2003 или более поздняя версия Excel, разница между общими значениями в A10 и A11 и значением в A12 при попытке эксперимента не будет. Однако в ячейках D7:D13 отображаются ошибки округления, которые вы получили в более ранних версиях Excel.

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

В статье о наклоне описывается менее надежная с числовым числом формула, используемая в более ранних версиях. Для формулы требуется только один проход через данные. Только недостатки SLOPE в этих версиях приводят к тому, что INTERCEPT в крайних случаях дает ошибки округления.

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

В Excel 2003 и более поздних версиях Excel используется улучшенная процедура вычисления НАКЛОН. В результате повышается производительность INTERCEPT. Для улучшенной процедуры требуется два прохода данных. Опять же, в следующей статье о НАКЛОН описывается улучшение.

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

статистические функции Excel 828142 : SLOPE

Выводы

Поскольку Excel 2003 и более поздних версий Excel заменяют однопроходный подход двухпроходным подходом, числовая производительность SLOPE в Excel 2003 и более поздних версиях Excel лучше, чем в более ранних версиях Excel. Таким образом, числовая производительность INTERCEPT лучше. Результаты в Excel 2003 и более поздних версиях Excel никогда не будут менее точными, чем в более ранних версиях Excel.

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

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

  1. Найдите среднее значение примера.
  2. Вычислите каждое квадратное отклонение.
  3. Суммируйте квадратное отклонение.

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

  1. Найдите сумму квадратов всех наблюдений, размер выборки и сумму всех наблюдений.
  2. Вычислите сумму квадратов всех наблюдений минус ((сумма всех наблюдений)^2)/размер выборки).

Заменив эту последнюю однопроходную процедуру двухпроходной процедурой, которая находит среднее значение примера на первом проходе и вычисляет сумму квадратных отклонений о нем на втором проходе, Excel 2003 и более поздних версий Excel улучшает многие другие функции. Краткий список таких функций включает VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ и STEYX. Корпорация Майкрософт внесла аналогичные улучшения в каждый из трех средств анализа отклонений в пакете средств анализа.