Excel에서 Holt-Winters 지수 평활화 수행(간단한 단계 사용)

엑셀 거대한 데이터 세트를 다룰 때 가장 널리 사용되는 도구입니다. Excel에서 다양한 차원의 수많은 작업을 수행할 수 있습니다. . 이 기사에서는 Holt-Winters 지수 평활화를 적용하는 방법을 설명합니다. 엑셀에서 . 이 방법은 예측에 유용합니다.

통합 문서 다운로드

이 워크북을 다운로드하고 이 문서를 진행하면서 연습하세요.

Holt-Winters 지수 평활화 소개

홀트-겨울 방법은 값을 예측하는 고급 방법입니다. 예측을 예측하면서 계절성과 추세 효과를 고려합니다. 그렇기 때문에 값은 임의성을 제외하고 실제 값을 나타냅니다.

Holt-Winters 지수 평활화를 사용하여 예측 값을 계산하는 공식 엑셀 이다

Ft+k =(Lt+k*Tt)*St-m+k

어디에, F =예측 값
엘 =레벨
티 =트렌드
남 =4 분기 기간 동안 12 월간 기간 동안
에 =계절성 지수

Excel에서 Holt Winters 지수 평활을 수행하는 11가지 쉬운 단계

이것은 오늘 기사의 데이터 세트입니다. 분기별 매출이 있습니다. 2022년까지.

2023년의 예측값을 계산합니다.

1단계:무작위 알파, 베타 및 감마 값 할당

첫 번째 작업은 상수 alpha에 임의의 값을 할당하는 것입니다. , 베타, 및 감마 .

나중에 이 값을 최적화할 것입니다.

2단계:초기 계절 지수 계산

그 후 초기 계절 지수를 결정합니다. 처음 4 병사. 각 분기의 매출을 평균 매출로 나누어 초기 계절 지수를 결정합니다. 처음 4 중 분기 . AVERAGE 함수를 사용하겠습니다. 그렇게 하십시오.

• F11으로 이동 다음 공식을 적으세요.

• 이제 Enter 키를 누릅니다. . 엑셀 출력을 반환합니다.

• 그런 다음 채우기 핸들을 사용합니다. 자동완성 최대 F14 .

3단계:초기 수준 및 추세 결정

데이터 세트의 초기 수준과 추세를 계산할 시간입니다.

초기 수준 수준입니다. 5분기 4가 있기 때문에 1년에 분기입니다.

초기 레벨의 공식은,

L5 =Y5/S1

Y5 =5분기 매출.

S1 =1분기 시즌별 지수.

확인하려면

• D15로 이동 공식을 적으세요

• 이제 Enter 키를 누릅니다. . 엑셀 출력을 반환합니다.

이번에는 초기 추세(5번째 4분의 1). 초기 추세에 대한 공식은 다음과 같습니다.

T5 =L5-Y4/S4

어디, L5 =5분기 수준입니다.

Y4 =4분기 매출.

S4 =4분기 시즌별 지수.

계산하려면

• E15로 이동 수식을 적어 보세요.

• Enter 키를 누릅니다. 출력을 얻을 수 있습니다.

4단계:다음 계절 지수 계산

이제 일반 공식을 사용하여 다음 계절 지수를 계산합니다. 계절 지수를 계산하는 일반 공식은 다음과 같습니다.

St =ɣ(Yt/Lt)+(1-ɣ)St-m

어디에

엘 =레벨.
티 =추세.
남 =4 분기 기간 동안 12 월간 기간 동안.
에 =계절성 지수.
Ɣ = 계수.
계절 지수를 계산하려면

• F15로 이동 다음 공식을 작성하십시오.

• Enter 키를 누릅니다. .

• 자동완성 최대 F22 .

참고: 잠시 동안 오류를 무시하십시오. 다음 레벨을 측정하면 괜찮을 것입니다. 및 다음 트렌드 .

5단계:다음 단계 결정

이제 공식을 사용하여 다음 레벨을 결정하는 방법을 보여 드리겠습니다.

Lt =α(Yt/St-m)+(1-α)(Lt-1+Tt-1)

어디에

엘 =레벨
티 =트렌드
남 =4 분기별 기간 동안 12 월간
에 =계절성 지수
α = 계수

• D16으로 이동 다음 공식을 작성하십시오.

• Enter 키를 누릅니다. .

• 자동완성 최대 D22 .

6단계:다음 트렌드 측정

이제 추세 효과에 대한 아이디어를 얻으십시오. 공식은

Tt=β(Lt-Lt-1)+(1-β)Tt-1

어디에

엘 =레벨
티 =트렌드
남 =4 분기별 기간 동안 12 월간
에 =계절성 지수
β= 계수

추세 효과를 계산하려면

• E6으로 이동 다음 공식을 작성하십시오.

• Enter  키를 누릅니다. 계속합니다.

• 자동완성 최대 E22 .

7단계:실제 판매와 비교할 예측 값 찾기

이제 실제 판매와 비교하기 위해 예측 값을 계산합니다. 첫 번째는 6분기에 대한 것입니다. 예측 값(비교용)을 계산하는 공식은 다음과 같습니다.

F 그 =(L t-1 + T t-1 )* S 티엠

그럼 해봅시다.

• G16으로 이동 다음 공식을 작성하십시오.

• Enter  키를 누릅니다. 간다.

• 자동완성 최대 G22 .

8단계:예측 오류 계산

이제 실제 판매액에서 예측값을 빼서 예측 오차를 계산해 보겠습니다.

• H16으로 이동 공식을 적으세요

• 그런 다음 Enter 키를 누릅니다. .

• 자동완성 최대 H22 .

9단계:예측할 분기에 K 값 할당

예측을 계산할 시간입니다. 그러나 그 전에 계수 k를 이해해야 합니다. . 예측할 미래의 시간을 나타냅니다. 우리의 경우 4 그리고 2022년 데이터가 있습니다.

따라서 2023년 1분기의 경우 k 값은 1이 되고 2분기의 경우 2가 되는 식입니다.

10단계:예측값 계산

이제 예측 값을 계산할 준비가 되었습니다. 마지막으로 사용 가능한 수준, 추세 및 계절성을 사용하여 계산합니다.

• G23으로 이동 다음 공식을 작성하십시오.

• 그런 다음 Enter 키를 누릅니다. 출력을 얻을 수 있습니다.

• 이제 자동완성 최대 G25 .

11단계:알파, 베타 및 감마 최적화

이제 오류를 최소화하기 위해 alpha 값을 최적화합니다. , 베타 및 감마 . Excel 솔버의 도움을 받을 것입니다. 그렇게 하십시오.

• 먼저, 제곱 평균 제곱근 오차를 계산해야 합니다. 이렇게 하려면 C7으로 이동하세요. 다음 공식을 작성하십시오.

공식 분석:

• COUNT(H15:H21) → 셀의 개수를 센다.
  • 출력 → 7
• SUMSQ(H15:H21) → H5:H11의 제곱합 계산 .
  • 출력 → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE 계산
• =SQRT(992.463493653301/7)
• =SQRT(141.780499093329)
  • 출력 → 9072

• 그런 다음 Enter 키를 누릅니다. .

• 이제 데이터로 이동합니다. 탭>> 해 찾기 선택 .

• 솔버 매개변수 창이 나타납니다. 오류를 최소화하기 위해 우리의 목표는 RMSE를 설정하는 것입니다. 계수 값을 변경하여 최소값으로 .
• 그런 다음 제약 조건을 추가하려면 추가를 클릭합니다. .

<강한>

• 제약조건 추가 창이 나타납니다. 제약 조건은 0<=α,४,ß<=1입니다. . 따라서 첫 번째 제약 조건을 추가하려면 셀 참조를 설정하세요. 및 가치 . (이미지 참조)

<강한>

• 마찬가지로 두 번째 제약 조건을 추가한 후 출력은 다음과 같습니다. 그런 다음 해결을 클릭합니다. .

• 엑셀 알파를 최적화하여 오류를 최소화합니다. , 베타, 및 감마 .

기억해야 할 사항

• 해석 추가 기능을 활성화해야 합니다. .
• 우리는 k 값에 대해 걱정하지 않습니다. 사용 가능한 실제 판매와 비교할 예측을 계산합니다.

결론

이 문서에서는 Holt-Winters 지수 평활화를 적용하는 방법을 설명했습니다. 엑셀에서 . 모두에게 도움이 되기를 바랍니다. 문의사항이 있으시면 댓글을 남겨주세요.