엑셀에서 함수를 이용해 추세선 식(a, b 값), R^2값 구하기와 배열함수의 응용(배열함수의 원하는 값만 출력하기)

엑셀을 이용하면서 추세선의 식을 이용할 경우 그래프의 추세선 에서 복사, 붙여넣기를 하지 않고

함수를 이용하여 바로 셀에 y = ax+b의 식의 a, b값을 가져오는 방법입니다.

먼저 배열함수인 LINEST 함수를 이용하여 다음과 같이 값을 얻을 수 있습니다.

LINEST함수에 대한 MS의 설명은 링크를 참조하시면 됩니다.

(간단한 설명은 아래에도 있습니다)

H열과 I열의 1, 2, 3, 4 및 1.1, 1.9, 2.9, 4.2값은 임의로 정한 y, x값입니다.

LINEST함수는 LINEST([known_y's], [known_x's], [const], [stats])구문이며 따라서 [known_y's]값에는 H1:H4가, 

[known_x's]에는 I1:14가 들어 갔으며, 

[const]는 b를 0으로 설정 할지 결정합니다. TRUE이거나 생략되면 계산하며  FALSE일 경우 0으로 설정됩니다.

[stats]는 a와 b값 이외에 다른 추가적인 항목을 구할지 정합니다. R^2 값을 얻으려면 TRUE (혹은 1) 로 해야 합니다.

스크린 샷에 보이는 것 처럼 출력 하려면 0.959478342 라고 적힌 I5부터 0.058687이라고 적힌 J9까지 

블럭을 씌운 다음 위의 수식 입력줄에 =LINEST(H1:H4,I1:I4,,1)을 입력하고 Ctrl + Shift + Enter를 누르면 됩니다. 

하지만 이렇게 함수를 이용할 경우 한 세트의 y값에 여러 세트의 x 값이 있을 경우 불편합니다.

이를 SUM함수를 이용하여 해결 할 수 있습니다.

먼저 a, b 값만을 얻을 경우 [stats]를 0으로 두고 셀에

=SUM(LINEST(C1:C4,D1:D4,,0)*{1,0})을 입력할 경우 a값

=SUM(LINEST(C1:C4,D1:D4,,0)*{0,1})을 입력할 경우 b값을 출력합니다.

이렇게 사용하면 배열 함수로 적용 되지 않으며

R^2을 구하기 위해 [stats]를 1로 설정한다면 배열 함수의 행이 늘어나기 때문에

위 스크린 샷 처럼 =SUM(LINEST(C1:C4,D1:D4,,1)*{0,0;0,0;1,0;0,0;0,0}) 이라고 입력해 주어야 합니다.

또한 R^2값이 1이 되는경우 (위 스크린 샷에서 E열) #NUM!오류가 발생 하는것으로 보입니다.

x