Excel เป็นเครื่องมือยอดนิยมสำหรับจัดการข้อมูลขนาดใหญ่ สามารถทำงานหลากหลายมิติได้ ในบทความนี้ จะอธิบายวิธีใช้ Holt-Winters Exponential Smoothing ใน Excel วิธีนี้เหมาะสำหรับ การทำนาย (Forecasting)
ดาวน์โหลดสมุดงาน
ดาวน์โหลดไฟล์นี้มาเปิดควบคู่กับการอ่าน
บทนำ: Holt-Winters Exponential Smoothing
Holt-Winters เป็นวิธีขั้นสูงสำหรับทำนายค่า พิจารณา ฤดูกาล (Seasonality) และ แนวโน้ม (Trend) ร่วมกัน ทำให้ค่าทำนายใกล้เคียงค่าจริง มากกว่าวิธีอื่นๆ
สูตรคำนวณค่าทำนาย (Forecast) ด้วย Holt-Winters ใน Excel:
Ft+k = (Lt + k×Tt) × St-m+k
โดยที่ F = ค่าทำนาย (Forecasted Value), L = ระดับ (Level), T = แนวโน้ม (Trend), M = 4 สำหรับรายไตรมาส, 12 สำหรับรายเดือน, S = ดัชนีฤดูกาล (Seasonality Index)
11 ขั้นตอน ทำ Holt-Winters Exponential Smoothing ใน Excel
ชุดข้อมูลวันนี้: ยอดขายรายไตรมาส จนถึงปี 2022 เราจะทำนายปี 2023
ขั้นตอนที่ 1: กำหนดค่า Alpha, Beta, Gamma เริ่มต้น (สุ่ม)
กำหนดค่าคงที่ alpha (α), beta (β), gamma (γ) เริ่มต้นแบบสุ่ม จะปรับค่าเหล่านี้ให้เหมาะสมในภายหลัง
ขั้นตอนที่ 2: คำนวณ Seasonal Index เริ่มต้น (Initial Seasonal Index)
หา Seasonal Index เริ่มต้นสำหรับ 4 ไตรมาสแรก โดย นำยอดขายแต่ละไตรมาส หารด้วย ค่าเฉลี่ยยอดขาย ของ 4 ไตรมาสแรก ใช้ AVERAGE
- ในเซลล์ F11 ใส่สูตร:
=C11/AVERAGE($C$11:$C$14) - กด ENTER ลาก Fill Handle ถึง F14
ขั้นตอนที่ 3: หา Level และ Trend เริ่มต้น (Initial Level and Trend)
Initial Level (L5) คือ Level ของไตรมาสที่ 5 (เพราะมี 4 ไตรมาสต่อปี) สูตร: L5 = Y5 / S1 (Y5 = ยอดขายไตรมาสที่ 5, S1 = Seasonal Index ไตรมาสที่ 1)
- เซลล์ D15 ใส่:
=C15/F11> ENTER
Initial Trend (T5) สูตร: T5 = L5 - Y4/S4 (L5 = Level ไตรมาส 5, Y4 = ยอดขายไตรมาส 4, S4 = Seasonal Index ไตรมาส 4)
- เซลล์ E15 ใส่:
=D15-C14/F14> ENTER
ขั้นตอนที่ 4: คำนวณ Seasonal Index ถัดไป (Next Seasonal Indexes)
สูตรทั่วไป: St = γ(Yt/Lt) + (1-γ)St-m
- เซลล์ F15 ใส่:
=$C$6*(C15/D15)+(1-$C$6)*F11($C$6 = gamma) - กด ENTER ลาก AutoFill ถึง F22 (ข้าม error ไปก่อน จะแก้ตัวเองเมื่อคำนวณ Level, Trend ถัดไป)
ขั้นตอนที่ 5: หา Level ถัดไป (Next Levels)
สูตร: Lt = α(Yt/St-m) + (1-α)(Lt-1 + Tt-1)
- เซลล์ D16 ใส่:
=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)($C$4 = alpha) - กด ENTER ลาก AutoFill ถึง D22
ขั้นตอนที่ 6: วัด Trend ถัดไป (Next Trends)
สูตร: Tt = β(Lt - Lt-1) + (1-β)Tt-1
- เซลล์ E16 ใส่:
=$C$5*(D16-D15)+(1-$C$5)*E15($C$5 = beta) - กด ENTER ลาก AutoFill ถึง E22
ขั้นตอนที่ 7: หาค่าทำนาย (Forecasted Values) เพื่อเปรียบเทียบกับยอดขายจริง
สูตรทำนาย (เพื่อเปรียบเทียบ): Ft = (Lt-1 + Tt-1) × St-M (เริ่มจากไตรมาสที่ 6)
- เซลล์ G16 ใส่:
=(D16+E16)*F12> ENTER ลาก AutoFill ถึง G22
ขั้นตอนที่ 8: คำนวณ Forecasting Errors (ข้อผิดพลาดการทำนาย)
ลบค่าทำนายออกจากยอดขายจริง: Error = Actual - Forecast
- เซลล์ H16 ใส่:
=C16-G16> ENTER ลาก AutoFill ถึง H22
ขั้นตอนที่ 9: กำหนดค่า K สำหรับไตรมาสที่จะทำนาย
K แทนช่วงเวลาอนาคตที่จะทำนาย เราจะทำนาย 4 ไตรมาสของปี 2023 ข้อมูลมีจนถึง 2022 ดังนั้น ไตรมาสแรกของ 2023 → k=1, ไตรมาสที่ 2 → k=2, ...
ขั้นตอนที่ 10: คำนวณค่าทำนายจริง (Forecasted Value)
ใช้ Level, Trend, Seasonality ล่าสุด คำนวณทำนาย 4 ไตรมาสถัดไป
- เซลล์ G23 ใส่:
=($D$22+F23*$E$22)*F19> ENTER ลาก AutoFill ถึง G25
ขั้นตอนที่ 11: ปรับค่า Alpha, Beta, Gamma ให้เหมาะสม (Optimize)
เพื่อลดข้อผิดพลาด ใช้ Excel Solver หา α, β, γ ที่ทำให้ RMSE น้อยที่สุด
- คำนวณ RMSE ใน C7:
=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) - ไป Data > Solver > Set Objective: C7 (Min), By Changing Variable Cells: C4:C6 (α, β, γ)
- เพิ่ม Constraints: 0 ≤ α,β,γ ≤ 1 > Solve
- Solver จะปรับค่า α, β, γ ให้ RMSE ต่ำที่สุด
สิ่งที่ควรจำ
- ต้องเปิดใช้งาน Solver Add-in ก่อน
- ไม่ต้องกังวลค่า K สำหรับการทำนายที่เปรียบเทียบกับข้อมูลจริง (In-sample)
สรุป
บทความนี้อธิบายวิธีใช้ Holt-Winters Exponential Smoothing ใน Excel แบบทีละขั้นตอน หวังว่าจะเป็นประโยชน์ หากมีข้อสงสัย กรุณาคอมเมนต์
