예측값과 실측값으로 평균제곱오차를 계산하려면?
예측 오차의 방향이 달라도 서로 상쇄되지 않도록 오차를 제곱해 평균을 구하세요. 실측값과 예측값이 모두 있는 내역으로 모형별 평균제곱오차를 계산할 수 있습니다.
예측 오차 +2와 −2를 그대로 평균 내면 0이지만, 두 예측 모두 실제 값에서 벗어났습니다. JOIN SHEET에서 각 오차를 자기 자신과 곱한 뒤 평균을 구하세요. 양수와 음수가 서로 상쇄되지 않는 평균제곱오차(MSE)를 계산할 수 있습니다.
같은 대상·기간의 예측과 실측을 한 줄에 놓기
아래 가상 표에서는 같은 대상·기간의 예측량과 실측량을 한 줄에 놓았습니다. 둘 중 하나가 없으면 이번 평가에서 제외하고, 실제 계산에 사용한 건수도 함께 표시하겠습니다. A에서 비어 있는 예측량을 0으로 간주하면 안 됩니다.
| 모형 | 실측량 | 예측량 |
|---|---|---|
| A | 10 | 12 |
| A | 20 | 18 |
| A | 30 | 빈 값 |
| B | 10 | 11 |
| B | 20 | 23 |
오차 계산 뒤 제곱, 마지막에 평균
- 자료를 불러와 실측량과 예측량이 모두 숫자로 읽혔는지 확인합니다. 시트를 클릭하고 ‘결과 정리’를 엽니다. 두 항목에 각각 ‘비어 있지 않음’ 필터를 추가해 실측값과 예측값이 모두 있는 행만 남깁니다.
- 그 아래에 ‘계산’을 추가해 예측량 − 실측량을 구하고 이름을 ‘예측 오차’로 지정합니다.
- 다음 ‘계산’에서는 ‘곱하기’를 선택하고 ‘첫 번째 값 (A)’과 ‘두 번째 값 (B)’에 모두 예측 오차를 고릅니다. 같은 오차를 곱해 제곱하는 단계입니다. ‘결과 항목 이름’에는 ‘오차 제곱’을 입력하세요.
- ‘요약’을 추가하고 ‘그룹화 기준’에서 모형을 선택합니다. 기본 집계의 ‘집계 방식’을 ‘평균’으로 바꾸고 ‘항목’은 오차 제곱, ‘결과 항목 이름’은 ‘평균제곱오차’로 설정하세요. 같은 요약에서 ‘집계 추가’를 눌러 ‘개수’를 유지하고 이름을 ‘평가 건수’로 바꾼 뒤 ‘적용’을 누릅니다.
분모는 평가에 남은 쌍의 개수입니다
A의 오차를 제곱하면 4와 4이므로 평균은 4입니다. B는 1과 9의 평균인 5가 나옵니다. 두 모형 모두 평가에 사용한 내역이 두 건이므로 나누는 수 n은 2입니다. MSE의 단위는 원래 수량 단위를 제곱한 것이며 결과 4는 4%를 뜻하지 않습니다. MSE에 제곱근을 취한 RMSE와도 구분해야 합니다.
| 모형 | 평균제곱오차 | 평가 건수 |
|---|---|---|
| A | 4 | 2 |
| B | 5 | 2 |
원문 질문은 이 계산의 분모를 Excel 회귀분석 ANOVA의 분모와 혼동한 경우입니다. 여기서는 이미 구한 예측값의 오차를 제곱해 단순 평균하며, 회귀 잔차 분산에서 쓰는 자유도 보정 n−p−1은 적용하지 않습니다. 모형을 비교할 때는 평가 대상과 단위를 같게 맞추세요. 어느 내역이 빠졌는지가 모형마다 다르면 MSE 수치만으로 성능을 단정하기 어렵습니다.
내 파일로 시작해 보세요
JOIN SHEET에서 시작하기 ↗Excel·CSV 작업은 PC에서 이용해 주세요.
새 창에서 열리며 작업 중인 데이터를 바꾸지 않습니다.
예제 확인일:
출처
MSE denominator의 질문을 참고해 별도의 가상 데이터로 구성했습니다.
