Excel 통계 함수: INTERCEPT

요약

이 문서에서는 Microsoft Excel의 INTERCEPT 함수에 대해 설명하고, 함수를 사용하는 방법을 설명하고, Excel 2003 및 이후 버전의 Excel에 대한 결과와 이전 버전의 Excel 결과를 비교합니다.

추가 정보

INTERCEPT(known_y,known_x) 함수는 x 값에서 y 값을 예측하는 데 사용되는 선형 회귀선의 INTERCEPT를 반환합니다.

구문

INTERCEPT(known_y's,known_x's)

known_y 및 known_x 인수는 동일한 숫자 데이터 값을 포함하는 배열 또는 셀 범위여야 합니다. INTERCEPT에는 INTERCEPT(A1:A100, B1:B100)와 같이 데이터를 포함하는 2개의 셀 범위가 포함됩니다.

사용 예

INTERCEPT 함수를 설명하려면 빈 Excel 워크시트를 만들고, 다음 표를 복사하고, 빈 Excel 워크시트에서 셀 A1 을 선택한 다음, 다음 표에서 워크시트의 A1:D13 셀을 채우도록 항목을 붙여넣습니다.

• Microsoft Office Excel 2007에서 홈 탭을 클릭하고 셀 그룹에서 서식을 클릭한 다음 열 너비 자동 맞춤을 클릭합니다.
• Excel 2003에서 서식 메뉴의 열을 가리킨 다음 선택 영역 자동 맞춤을 클릭합니다.

셀 B2:B7을 10진수 위치가 0인 숫자로 서식을 지정하고 셀 A9:D13을 10진수가 6인 숫자로 서식을 지정할 수 있습니다.

셀 A2:A7 및 B2:B7에는 셀 A10에서 INTERCEPT를 호출하는 y-values 및 x-values가 포함됩니다.

Excel 2003 이전 버전의 Excel에서는 INTERCEPT에서 반올림 오류를 나타낼 수 있습니다. Excel 2003 이상 버전의 Excel은 INTERCEPT의 동작을 개선합니다. INTERCEPT(known_y, known_x)는 AVERAGE(known_y) – SLOPE(known_y, known_x) * AVERAGE(known_x)를 평가한 결과입니다. EXCEL 2003 및 이후 버전의 Excel에서는 INTERCEPT 코드가 직접 변경되지 않았지만 SLOPE에 대한 코드가 향상되어 INTERCEPT의 동작이 향상되었습니다.

이전 버전의 Excel이 있는 경우 이전에 만든 워크시트를 사용하여 실험을 실행하여 반올림 오류가 발생하는 시기를 검색할 수 있습니다. B2:B7의 각 관찰에 양수 상수를 추가해도 SLOPE 값에는 영향을 미치지 않습니다. x,y쌍을 가로 축에 x, 세로 축에 y로 그리는 다음 각 x 값에 양수 상수를 추가하면 데이터가 오른쪽으로 이동하기만 하면 됩니다. 가장 적합한 회귀 선은 여전히 동일한 기울기를 가립니다. 그러나 이동된 데이터에는 다른 인터셉트가 있습니다.

기본값이 D3인 경우 A9의 SLOPE은 0.775280899입니다. 셀 A10은 INTERCEPT 값을 표시하고 셀 A11은 INTERCEPT를 계산할 때 계산되는 식의 값을 표시합니다.

AVERAGE(known_y) – SLOPE(known_y, known_x) * AVERAGE(known_x)

A9 및 A10 셀의 값은 A10의 값이 INTERCEPT가 반환하는 값이기 때문에 항상 동의합니다. KNOWN_X 서로 다른 양의 상수를 추가하기 때문에 SLOPE이 달라서는 안 됩니다. 셀 A11은 AVERAGE(known_y) – 0.775280899 * AVERAGE(known_x)를 표시합니다. SLOPE은 변경되지 않아야 하며 0.775280899는 D3 = 0인 경우 SLOPE 값이므로 A11의 이 식 값도 셀 A9 및 A10의 값과 일치해야 합니다.

D3에서 값을 늘리면 B2:B7에 더 큰 상수를 추가합니다. D3 <= 7이면 SLOPE의 처음 6 소수 위치에 표시되는 반올림 오류가 없습니다. 그러나 7.25, 7.5, 7.75 및 8을 시도하면 A9의 SLOPE이 변경합니다. 결과적으로 셀 A11(A10과 동의함) 및 A12의 값은 다릅니다. 그러나 A11(또는 A10) 및 A12의 값은 known_x 상수를 추가해도 SLOPE에 영향을 미치지 않으므로 동일해야 합니다.

D7:D13은 INTERCEPT가 반환하는 값과 SLOPE이 변경되지 않은 경우 INTERCEPT가 반환해야 하는 값을 표시합니다. 이러한 값 쌍은 D3 = 7.5 및 8이 각각 있는 경우에 나타납니다. 반올림 오류가 너무 심각해져서 D3 = 8일 때 0으로 나누기 발생합니다.

이전 버전의 Excel에서는 이러한 버전에서 사용하는 계산 수식으로 인해 반올림 오류의 영향이 더 커지므로 이러한 경우 잘못된 답변을 제공합니다. 그럼에도 불구하고 이 실험은 오류가 발생하는 사례가 극단적임을 보여 줍니다.

Excel 2003 이상 버전의 Excel이 있는 경우 실험을 시도하면 A10과 A11의 공통 값과 A12의 값 간에는 거의 또는 전혀 차이가 없습니다. 그러나 D7:D13 셀에는 이전 버전의 Excel에서 얻은 반올림 오류가 표시됩니다.

이전 버전의 Excel 결과

SLOPE에 대한 문서에서는 이전 버전에서 사용하는 덜 수치적으로 강력한 수식에 대해 설명합니다. 수식에는 데이터를 한 번만 통과하면 됩니다. 이러한 버전에서 SLOPE의 단점만 있으면 INTERCEPT가 극단적인 경우 라운드오프 오류를 발생합니다.

Excel 2003 이상 버전의 Excel 결과

Excel 2003 이상 버전에서는 향상된 프로시저를 사용하여 SLOPE을 계산합니다. 결과적으로 INTERCEPT의 성능이 향상됩니다. 개선된 프로시저에는 데이터를 통과하는 두 개의 패스가 필요합니다. 다시 말하지만, SLOPE에 대한 다음 문서에서는 개선 사항을 설명합니다.

Excel 2003 및 이후 버전의 Excel에 대한 SLOPE의 개선 사항에 대한 자세한 내용은 다음 문서 번호를 클릭하여 Microsoft 기술 자료의 문서를 확인합니다.

828142 Excel 통계 함수: SLOPE

결론

Excel 2003 이상 버전은 1-pass 접근 방식을 2-pass 접근 방식으로 대체하므로 Excel 2003 이상 버전의 EXCEL에서 SLOPE의 숫자 성능은 이전 버전의 Excel보다 좋습니다. 따라서 INTERCEPT의 숫자 성능이 더 좋습니다. Excel 2003 및 이후 버전의 Excel의 결과는 이전 버전의 Excel 결과보다 정확도가 떨어지지 않습니다.

일반적으로 Excel 2003의 결과와 이후 버전의 Excel에서는 데이터가 이 실험에서 보여 주는 비정상적인 방식으로 자주 작동하지 않으므로 이전 버전의 Excel 결과 간에는 차이가 없습니다. 데이터에 많은 유효 자릿수와 데이터 값 간의 약간의 변형이 포함된 경우 숫자 불안정성이 이전 버전의 Excel에 나타날 가능성이 큽니다.

다음 절차에서는 샘플 평균에 대한 제곱 편차의 합계를 찾습니다.

1. 샘플 평균을 찾습니다.
2. 각 제곱 편차를 계산합니다.
3. 제곱 편차를 합산합니다.

이 절차는 다음과 같은 대체 프로시저("계산기 수식"라고도 함)보다 더 정확합니다. 이는 적은 수의 데이터 요소를 계산기에 사용하기에 적합했기 때문입니다.

1. 모든 관찰의 제곱 합계, 샘플 크기 및 모든 관찰의 합계를 찾습니다.
2. 모든 관찰에서 빼기((모든 관측값 합계)^2)/샘플 크기의 제곱 합계를 계산합니다.

이 후자의 원패스 프로시저를 첫 번째 패스에서 샘플 평균을 찾아 두 번째 패스에서 제곱 편차의 합계를 계산하는 2-pass 프로시저로 바꿔서 Excel 2003 이상 버전의 Excel은 다른 많은 기능을 개선합니다. 이러한 함수의 짧은 목록에는 VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ 및 STEYX가 포함됩니다. Microsoft는 Analysis ToolPak의 세 가지 분산 분석 도구 각각에서 비슷한 개선을 이루었습니다.