최소 제곱 법 (OLS)은회귀 분석의 영역. 다른 간단한 함수로 주어진 함수를 대략적으로 표현할 수 있기 때문에 많은 응용 프로그램이 있습니다. OLS는 관측 처리에 매우 유용 할 수 있으며 임의 오류를 포함하는 다른 측정 결과에서 일부 수량을 추정하는 데 적극적으로 사용됩니다. 이 기사에서는 Excel에서 최소 제곱 계산을 구현하는 방법을 보여줍니다.
두 개의 지수 X와 Y가 있다고 가정합니다. 또한 Y는 X에 의존합니다. 회귀 분석의 관점에서 OLS (Excel에서는 그 방법이 내장 함수를 사용하여 구현 됨)에서 관심이 있으므로 즉시 특정 문제를 고려해야합니다.
따라서 X는 평방 미터로 측정되는 식료품 점의 소매 공간이고 Y는 수백만 루블로 측정되는 연간 매출액입니다.
매출액 (Y)을 예측해야합니다.특정 소매 공간이있는 경우 매장에 있습니다. 하이퍼 마켓이 매점보다 더 많은 상품을 판매하기 때문에 분명히 Y = f (X) 함수가 증가하고 있습니다.
n 개의 저장소에 대한 데이터로 만든 테이블이 있다고 가정 해 보겠습니다.
와 | ~와 함께1 | ~와 함께2 | ... | ~와 함께씨. |
그리고 | 그리고1 | 그리고2 | ... | 그리고씨. |
수학적 통계에 따르면 결과는적어도 5-6 개의 개체에 대한 데이터를 검사하면 다소 정확할 것입니다. 또한 "비정상"결과를 사용할 수 없습니다. 특히, 엘리트 소규모 부티크는 "마스 마켓"등급의 대형 소매점의 매출보다 몇 배나 더 많은 매출을 올릴 수 있습니다.
테이블 데이터는 데카르트 평면에 점 M으로 플로팅 될 수 있습니다.1 (엑스1, y1),… 미디엄씨. (엑스씨., y씨.). 이제 문제의 해결책은 점 M에 최대한 가깝게 지나가는 그래프와 함께 근사 함수 y = f (x)를 선택하는 것으로 축소됩니다.1, 남2, ..남씨..
물론 높은 다항식을 사용할 수 있습니다.하지만이 옵션은 구현하기 어려울뿐만 아니라 감지해야하는 주요 추세를 반영하지 않기 때문에 단순히 부정확합니다. 가장 합리적인 해결책은 실험 데이터에 가장 근접한 직선 y = ax + b 또는 더 정확하게는 계수 a와 b를 찾는 것입니다.
근사치의 경우 정확성 평가가 특히 중요합니다. 우리는 e로 표시그리고 점 x에 대한 기능적 값과 실험적 값의 차이 (편차)그리고, 즉 e그리고 = y그리고 -f (x그리고).
분명히 근사치의 정확도를 추정하려면편차의 합계를 사용할 수 있습니다.그리고 고려되는 모든 지점에서. 그러나 긍정적 인 편차와 함께 실제로 부정적인 편차가 있기 때문에 모든 것이 그렇게 간단하지는 않습니다.
편차 모듈을 사용하여 문제를 해결할 수 있습니다.또는 그들의 사각형. 후자의 방법이 가장 널리 사용됩니다. 회귀 분석 (Excel은 두 가지 기본 제공 함수를 구현 함)을 포함한 많은 영역에서 사용되며 오랫동안 그 가치가 입증되었습니다.
Excel에는 기본 제공 기능이있는 것으로 알려져 있습니다.선택한 범위에있는 모든 값의 값을 계산하는 autosum 함수입니다. 따라서 식의 값을 계산하는 데 방해가되는 것은 없습니다 (e12 + e22 + e32+ ... e씨.2).
수학적 표기법에서는 다음과 같습니다.
처음에는 직선을 사용하여 근사화하기로 결정 했으므로 다음과 같은 결과를 얻었습니다.
따라서 수량 X와 Y의 특정 종속성을 가장 잘 설명하는 직선을 찾는 문제는 두 변수의 함수의 최소값을 계산하는 것으로 축소됩니다.
이렇게하려면 새 변수 a와 b에 대한 편도 함수를 0으로 지정하고 다음 형식의 미지수가 2 개인 두 방정식으로 구성된 기본 시스템을 풀어야합니다.
2로 나누기 및 합계를 사용한 조작을 포함한 몇 가지 간단한 변환 후 다음을 얻습니다.
예를 들어 Cramer의 방법으로이를 해결하면 일부 계수가있는 고정 점을 얻을 수 있습니다.* 그리고 b*... 이것은 최소값입니다.*x + b*에 대한 회귀 모델입니다.문제의 예. 물론 정확한 결과를 찾을 수는 없지만 특정 지역에 대한 신용으로 상점을 구매할 것인지 여부에 대한 아이디어를 얻는 데 도움이 될 것입니다.
Excel에는 값을 계산하는 기능이 있습니다.OLS에 의해. 형식은 "TREND"(알려진 Y 값, 알려진 X 값, 새 X 값, const)입니다. Excel에서 OLS를 계산하는 공식을 테이블에 적용 해 보겠습니다.
이렇게하려면 있어야하는 셀에Excel에서 최소 제곱 법에 의한 계산 결과가 표시되면 "="기호를 입력하고 "TREND"기능을 선택합니다. 열리는 창에서 적절한 필드를 채우고 다음을 강조 표시합니다.
또한 수식에는 부울 변수 "Const"가 포함되어 있습니다. 해당 필드에 1을 입력하면 b = 0이라고 가정하여 계산을 수행해야 함을 의미합니다.
둘 이상의 x 값에 대한 예측을 알아야하는 경우 수식을 입력 한 후 "Enter"를 클릭하지 말고 키보드에 "Shift"+ "Control"+ "Enter"( "Enter") 조합을 입력해야합니다.
회귀 분석도 가능할 수 있습니다.찻 주전자. 알려지지 않은 변수 배열의 값을 예측하는 Excel 공식 인 "TREND"는 최소 제곱 법에 대해 들어 본 적이없는 사용자도 사용할 수 있습니다. 그녀의 작품의 일부 특징을 아는 것으로 충분합니다. 특히:
Excel의 회귀 분석은 다음으로 구현됩니다.여러 기능을 사용합니다. 그중 하나는 "FORECAST"입니다. TREND와 유사합니다. 즉, 최소 제곱 법을 사용한 계산 결과를 제공합니다. 그러나 Y 값을 알 수없는 하나의 X에만 해당됩니다.
이제 선형 추세에 따라 주어진 지표의 미래 가치를 예측할 수있는 더 미용 Excel의 수식을 알았습니다.
