Excel에서 rmse(제곱 평균 제곱근 오차)를 계산하는 방법

통계에서 회귀 분석은 예측 변수 x와 응답 변수 y 사이의 관계를 이해하는 데 사용하는 기술입니다.

회귀 분석을 수행하면 예측 변수의 값을 기반으로 응답 변수의 예측 값을 알려주는 모델을 얻습니다.

모델이 특정 데이터 세트에 얼마나 잘 맞는지 평가하는 한 가지 방법은 평균 제곱 오차를 계산하는 것입니다. 이는 예측 값이 관찰 값과 평균적으로 얼마나 떨어져 있는지 알려주는 측정항목입니다.

더 일반적으로 RMSE 로 알려진 평균 제곱 오차를 찾는 공식은 다음과 같습니다.

RMSE = √[ Σ(P _i – O _i ) ² / n ]

금:

• Σ는 ‘합’을 의미하는 화려한 기호입니다.
• _Pi는 데이터 세트의 ^i번째 관측치에 대한 예측 값입니다.
• O _i 는 데이터세트의 ^i번째 관측치에 대한 관측값입니다.
• n은 표본 크기입니다.

기술 노트 :

• 평균 제곱 오차는 예측 값을 생성하는 모든 유형의 모델에 대해 계산할 수 있으며, 이를 데이터 세트의 관측 값과 비교할 수 있습니다.
• 평균 제곱 오차는 평균 제곱 편차라고도 하며 종종 RMSD로 약칭됩니다.

다음으로 Excel에서 평균 제곱 오차를 계산하는 방법의 예를 살펴보겠습니다.

Excel에서 평균 제곱 오류를 계산하는 방법

Excel에는 RMSE를 계산하는 기본 제공 함수가 없지만 단일 수식으로 매우 쉽게 계산할 수 있습니다. 두 가지 시나리오에 대해 RMSE를 계산하는 방법을 보여 드리겠습니다.

시나리오 1

시나리오에서는 모델의 예측 값을 포함하는 하나의 열과 관찰된 값을 포함하는 다른 열을 가질 수 있습니다. 아래 이미지는 이 시나리오의 예를 보여줍니다.

그렇다면 셀에 다음 수식을 입력한 다음 CTRL+SHIFT+ENTER를 클릭하여 RMSE를 계산할 수 있습니다.

=SQRT(SUMSQ(A2:A21-B2:B21) / 개수(A2:A21))

이는 평균 제곱 오차가 2.6646 임을 알려줍니다.

공식은 약간 까다로워 보일 수 있지만, 일단 분석해 보면 이해가 됩니다.

= SQRT( SUMSQ(A2:A21-B2:B21) / 개수(A2:A21) )

• 먼저 SUMSQ() 함수를 사용하여 예측값과 관측값 간의 차이의 제곱합을 계산합니다.
• 다음으로, 비어 있지 않은 범위의 셀 수를 계산하는 COUNTA() 를 사용하여 데이터세트의 샘플 크기로 나눕니다.
• 마지막으로 SQRT() 함수를 사용하여 전체 계산의 제곱근을 구합니다.

시나리오 2

다른 시나리오에서는 예측된 값과 관찰된 값 간의 차이를 이미 계산했을 수도 있습니다. 이 경우 차이점을 표시하는 열이 하나만 표시됩니다.

아래 이미지는 이 시나리오의 예를 보여줍니다. 예측값은 A열에, 관측값은 B열에, 예측값과 관측값의 차이는 D열에 표시됩니다.

그렇다면 셀에 다음 수식을 입력한 다음 CTRL+SHIFT+ENTER를 클릭하여 RMSE를 계산할 수 있습니다.

=SQRT(SUMSQ(D2:D21) / 개수(D2:D21))

이는 평균 제곱 오차가 2.6646 임을 말하며 이는 첫 번째 시나리오에서 얻은 결과에 해당합니다. 이는 RMSE를 계산하는 두 가지 접근 방식이 동일하다는 것을 확인합니다.

이 시나리오에서 사용한 공식은 이전 시나리오에서 사용한 공식과 약간 다릅니다.

= SQRT( SUMSQ(D2:D21) / 개수(D2:D21) )

• D열의 예측값과 관측값의 차이를 이미 계산했으므로 SUMSQ() 함수를 사용하여 차이 제곱의 합을 계산할 수 있습니다 .   D열의 값만 가지고 함수를 실행합니다.
• 다음으로, 비어 있지 않은 범위의 셀 수를 계산하는 COUNTA() 를 사용하여 데이터세트의 샘플 크기로 나눕니다 .
• 마지막으로 SQRT() 함수 를 사용하여 전체 계산의 제곱근을 구합니다 .

RMSE를 해석하는 방법

앞서 언급했듯이 RMSE는 회귀 모델(또는 예측 값을 생성하는 모든 모델)이 데이터 세트에 얼마나 잘 맞는지 확인하는 유용한 방법입니다.

RMSE가 클수록 예측값과 관측값 간의 차이가 커집니다. 즉, 회귀 모델이 데이터에 더 잘 맞는다는 의미입니다. 반대로, RMSE가 작을수록 모델이 데이터에 더 잘 적합할 수 있습니다.

두 가지 모델의 RMSE를 비교하여 어떤 모델이 데이터에 가장 적합한지 확인하는 것이 특히 유용할 수 있습니다.

Excel에 대한 더 많은 자습서를 보려면 통계에 대한 모든 Excel 자습서가 나열된 Excel 가이드 페이지를 확인하세요.