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

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

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

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

Аргументы, 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 Power of 10 to add to data 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 =ПЕРЕХВАТ(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**) * AVERAGE(known_x). Хотя код ДЛЯ INTERCEPT не был изменен напрямую для Excel 2003 и более поздних версий Excel, поведение ФУНКЦИИ INTERCEPT улучшается из-за улучшенного кода для НАКЛОНА.

Если у вас есть более раннюю версию 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 является значением НАКЛОНа, когда 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, и значения, которые должны были возвращаться INTERCEPT, если наклон не изменился. Эти пары значений отображаются в случаях, когда D3 = 7,5 и 8 соответственно. Ошибки округления стали настолько серьезными, что деление на 0 происходит, когда D3 = 8.

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

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

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

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

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

828142 Excel статистических функций: НАКЛОН

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

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

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

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

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

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

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