티스토리 뷰엑셀으로 선형회귀식을 얻으실때, 차트의 "추세선"을 활용하시나요? 이를 위한 엑셀 함수를 사용해보세요. 예제로 알아봅시다. 예제 데이터는 다음과 같습니다.
1. 데이터 입력하기 2. linest 함수 사용 엑셀에 데이터를 입력하고 나서, 기울기, y-절편 R^2 등을 구하고 싶은 위치에 LINEST 함수를 사용합니다.
위의 예시는 y=a*x+b의 가장 기본적인 형태입니다. [기울기: a; y-절편: b;] 같은 데이터를 이용해서, 차트 기능으로 데이터를 정리해보면 위 그림과 같습니다.선형회귀식과 데이터가 잘 맞는지에 대해서는 눈으로 확인할 수도 있지만, R2나 잔차제곱합 (SSR; sum of squared residuals) 등으로 따져볼 수도 있을 것 같습니다. 혹시나 잔차제곱합이나 R2에 대해서 의문이 생기시는 분은 https://igija.tistory.com/256 에 정리가 되어 있는 것 같으니 참고 바랍니다. 선형회귀에서 항을 여러개 사용한다면, y = m1 * x1 + m2 * x2 + ... + b의 형태가 될 수 있습니다. 그 경우에는 출력되는 파라미터의 형태가 다음과 같습니다. (맨 오른쪽은 y-절편, 그 앞은 순서대로 mn, mn-1... 순서) 출력되는 파라미터의 형태 (마이크로소프트 오피스 매뉴얼 캡쳐)마이크로소프트의 매뉴얼을 참고하시면 조금 더 자세한 (번역투라서 읽기 힘들지만) 내용을 확인하실 수 있습니다. 캡쳐로 올려둔 파일은 예제파일로 첨부해두었습니다. LINEST.xlsx 0.02MB 감사합니다. 이 문서에서는 Microsoft Excel의 LINEST 함수에 사용되는 수식 구문과 이 함수를 사용하는 방법을 설명합니다. 차트 작성 및 회귀 분석에 대한 자세한 내용을 보려면 참고 항목 섹션에서 해당 링크를 클릭하세요. 설명LINEST 함수는 데이터에 가장 적합한 직선을 구하는 "최소 자승법"을 사용하여 선의 통계를 계산하고 선에 대한 배열을 구합니다. LINEST를 다른 함수와 결합하여 다항식, 로그, 지수, 멱급수 등 알 수 없는 매개 변수에서 다른 유형의 선형 모델에 대한 통계를 구할 수도 있습니다. 이 함수는 값을 배열로 반환하므로 배열 수식으로 입력해야 합니다. 지침은 이 문서의 예제를 참조하세요. 선의 방정식은 다음과 같습니다. y = mx + b 또는 y = m1x1 + m2x2 + ... + b x-values 범위가 여러 개 있는 경우 종속 y-값은 독립 x-값의 함수입니다. m-value는 각 x-value에 해당하는 계수로, b는 상수 값입니다. x, y, m은 벡터가 될 수 있습니다. LINEST 함수가 반환하는 배열은 {mn,mn-1,...,m1,b}입니다. LINEST는 추가 회귀 통계를 반환할 수도 있습니다. 구문LINEST(known_y's, [known_x's], [const], [stats]) LINEST 함수 구문에는 다음과 같은 인수가 사용됩니다. 구문
다음 그림은 추가 회귀 통계를 구하는 순서를 보여 줍니다.
주의
예제예제 1 - 기울기와 y 절편다음 표의 예제 데이터를 복사하여 새 Excel 워크시트의 A1 셀에 붙여 넣습니다. 수식의 결과를 표시하려면 수식을 선택하고 F2 키를 누른 다음 Enter 키를 누릅니다. 필요한 경우 열 너비를 조정하면 데이터를 모두 표시할 수 있습니다.
예제 2 - 단순 선형 회귀다음 표의 예제 데이터를 복사하여 새 Excel 워크시트의 A1 셀에 붙여 넣습니다. 수식의 결과를 표시하려면 수식을 선택하고 F2 키를 누른 다음 Enter 키를 누릅니다. 필요한 경우 열 너비를 조정하면 데이터를 모두 표시할 수 있습니다.
예제 3 - 다중 선형 회귀다음 표의 예제 데이터를 복사하여 새 Excel 워크시트의 A1 셀에 붙여 넣습니다. 수식의 결과를 표시하려면 수식을 선택하고 F2 키를 누른 다음 Enter 키를 누릅니다. 필요한 경우 열 너비를 조정하면 데이터를 모두 표시할 수 있습니다.
예제 4 - F 및 r2 통계 사용앞의 예제에서 결정 계수 또는 r2는0.99675(LINEST의 출력의 셀 A17 참조)입니다. 이는 독립 변수와 판매 가격 사이의 강력한 관계를 나타냅니다. 높은 r2 값의 결과가 우연한 것인지 판단하기 위하여 F 통계를 사용할 수 있습니다. 사실은 변수 사이에 아무런 관계가 없지만, 겨우 11개 사무실 건물을 표본으로 채택했기 때문에 통계적 분석 결과는 강한 상관 관계를 나타낸 것이라고 가정해 보세요. 상관 관계가 있다는 잘못된 결론을 내릴 확률을 고려하여 조건 "Alpha"를 사용합니다. LINEST 함수 결과의 F와 df 값을 사용하여 더 높은 F 값이 발생할 가능성을 평가할 수 있습니다. 게시된 F 분포표의 임계값과 F를 비교하거나 Excel의 FDIST 함수를 사용하여 더 큰 F 값이 발생할 확률을 계산할 수 있습니다. 적당한 F 분포의 자유도는 v1과 v2입니다. n이 데이터 요소의 개수이고 const가 TRUE이거나 생략되면 v1 = n - df - 1, v2 = df입니다. const가 FALSE이면 v1 = n - df, v2 = df입니다. FDIST(F,v1,v2) 구문이 있는 FDIST 함수는 더 높은 F 값이 발생할 확률을 계산합니다. 이 예제에서 df는 6(B18 셀)이고 F는 459.753674(A18 셀)입니다. Alpha 값이 0.05, v1 = 11 - 6 - 1 = 4, v2 = 6이라고 가정하면 F 임계값은 4.53입니다. F = 459.753674는 4.53보다 훨씬 크므로 이 정도로 높은 F 값이 우연히 발생할 가능성은 극히 희박합니다. alpha = 0.05인 경우, F가 임계값 4.53을 초과하면 known_y’s와 known_x’s 간에 관계가 없다는 가설은 기각됩니다. Excel의 FDIST 함수를 사용하여 이 정도 크기의 F 값이 우연히 발생할 확률을 계산할 수 있습니다. 예를 들어 FDIST(459.753674, 4, 6) = 1.37E-7, 즉 극히 적은 확률입니다. 표에서 F 임계값을 찾거나 FDIST 함수를 사용하면, 회귀 방정식은 이 지역 사무실 건물의 평가액을 예측할 때 유용하다는 결론을 내릴 수 있습니다. 앞 단락에서 계산된 정확한 v1과 v2 값을 사용하는 것이 매우 중요합니다. 예제 5 - t 통계 계산다른 가설 검정으로 각 기울기 계수가 예제 3에서와 같이 사무실 건물의 평가액을 어림잡는 데 유용한지 판단합니다. 예를 들어 건축 연수 계수의 통계적 중요성을 검정하기 위해 -234.24(건축 연수의 기울기 계수)를 13.268(A15 셀에 있는 건축 연수 계수의 표준 오차 추정값)로 나눕니다. 다음은 t 관측값입니다. t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7 f의 절대값이 충분히 높으면 기울기 계수가 예제 3에서와 같이 사무실 건물의 평가액을 추정하는 데 유용하다는 결론을 내릴 수 있습니다. 아래 표에서는 4개의 t 관측값을 보여 줍니다. 통계표에서 자유도 6, Alpha = 0.05인 양측 검정 t 임계값이 2.447임을 확인할 수 있습니다. Excel의 TINV 함수로도 이 임계값을 찾을 수 있습니다. 즉, TINV(0.05,6) = 2.447입니다. t(17.7)의 절대값이 2.447보다 크므로 사무실 건물의 평가액을 추정할 때 건축 연수가 중요한 변수가 됩니다. 다른 독립 변수도 유사한 방법으로 검정할 수 있습니다. 다음은 각 독립 변수의 t 관측값입니다.
이러한 값은 절대값이 2.447보다 크므로 회귀 방정식에 사용된 모든 변수는 이 지역 사무실 건물의 평가액을 추정하는 데 유용합니다. |