Excel เป็นเครื่องมือที่ใช้กันอย่างแพร่หลายมากที่สุดเมื่อต้องจัดการกับชุดข้อมูลขนาดใหญ่ เราสามารถทำงานมากมายในหลายมิติใน Excel . ในบทความนี้ ผมจะอธิบายวิธีการใช้Holt-Winters Exponential Smoothing ใน Excel . วิธีนี้มีประโยชน์สำหรับการคาดการณ์
ดาวน์โหลดสมุดงาน
ดาวน์โหลดสมุดงานและแบบฝึกหัดนี้ขณะอ่านบทความนี้
รู้เบื้องต้นเกี่ยวกับ Holt-Winters Exponential Smoothing
โฮลท์-วินเทอร์ method เป็นวิธีการขั้นสูงในการคาดการณ์ค่า โดยจะพิจารณาตามฤดูกาลและผลกระทบของแนวโน้มขณะคาดการณ์การคาดการณ์ นั่นคือเหตุผลที่ค่าแสดงถึงค่าจริง ยกเว้นบางค่าสุ่ม
สูตรคำนวณค่าพยากรณ์โดยใช้ Holt-Winters Exponential Smoothing ใน Excel คือ
Ft+k =(Lt+k*Tt)*St-m+k
ที่ไหน F =มูลค่าที่คาดการณ์
ล =ระดับ
ท =เทรนด์
ม =4 สำหรับงวดรายไตรมาส 12 สำหรับรอบเดือน
ส =ดัชนีฤดูกาล
11 ขั้นตอนง่ายๆ ในการทำ Holt Winters Exponential Smoothing ใน Excel
นี่คือชุดข้อมูลสำหรับบทความของวันนี้ เรามี ยอดขายรายไตรมาส จนถึงปี 2022
เราจะคำนวณค่าที่คาดการณ์ไว้สำหรับปี 2023
ขั้นตอนที่ 1:กำหนดค่าอัลฟ่า เบต้า และแกมมาแบบสุ่ม
งานแรกคือการกำหนดค่าสุ่มสำหรับค่าคงที่ อัลฟ่า , เบต้า และ แกมมา .
เราจะปรับค่าเหล่านี้ให้เหมาะสมในภายหลัง
ขั้นตอนที่ 2:คำนวณดัชนีฤดูกาลเริ่มต้น
หลังจากนั้นเราจะกำหนดเริ่มต้น ดัชนีฤดูกาล สำหรับครั้งแรก 4 ไตรมาส เราจะกำหนดดัชนีฤดูกาลเริ่มต้นโดย หารยอดขายของแต่ละไตรมาสด้วย ยอดขายเฉลี่ย ของ 4ตัวแรก ไตรมาส . เราจะใช้ฟังก์ชัน AVERAGE ให้ทำเช่นนั้น
- ไปที่ F11 แล้วเขียนสูตรต่อไปนี้
=C11/AVERAGE($C$11:$C$14)
- ตอนนี้ กด ENTER . Excel จะส่งคืนผลลัพธ์
- หลังจากนั้น ให้ใช้ Fill Handle เพื่อป้อนอัตโนมัติ มากถึง F14 .
ขั้นตอนที่ 3:กำหนดระดับเริ่มต้นและแนวโน้ม
ได้เวลาคำนวณระดับเริ่มต้นและแนวโน้มสำหรับชุดข้อมูลแล้ว
ระดับเริ่มต้น คือ ระดับ ระดับ สำหรับ ไตรมาสที่ 5 เนื่องจากมี 4 ไตรมาสในหนึ่งปี
สูตรสำหรับระดับเริ่มต้นคือ
L5 =Y5/S1
ที่ไหน Y5 =ยอดขายสำหรับไตรมาสที่ 5
S1 =ดัชนีฤดูกาลสำหรับไตรมาสที่ 1
เพื่อตรวจสอบมัน
- ไปที่ D15 แล้วเขียนสูตร
=C15/F11
- ตอนนี้ กด ENTER . Excel จะส่งคืนผลลัพธ์
คราวนี้ฉันจะคำนวณแนวโน้มเริ่มต้น (ซึ่งก็สำหรับ 5 . ด้วย หนึ่งในสี่). สูตรสำหรับแนวโน้มเริ่มต้นคือ
T5 =L5-Y4/S4
ที่ไหน L5 =ระดับสำหรับไตรมาสที่ 5
Y4 =ยอดขายสำหรับไตรมาสที่ 4
S4 =ดัชนีฤดูกาลสำหรับไตรมาสที่ 4
ในการคำนวณ
- ไปที่ E15 แล้วเขียนสูตร
=D15-C14/F14
- กด ENTER เพื่อให้ได้ผลลัพธ์
ขั้นตอนที่ 4:คำนวณดัชนีฤดูกาลถัดไป
ตอนนี้ เราจะคำนวณดัชนีตามฤดูกาลถัดไปโดยใช้สูตรทั่วไปของเรา สูตรทั่วไปในการคำนวณดัชนีตามฤดูกาลคือ
St =ɣ(Yt/Lt)+(1-ɣ)St-m
ที่ไหน
ล =ระดับ
ท =เทรนด์
ม =4 สำหรับงวดรายไตรมาส 12 เป็นระยะเวลาหนึ่งเดือน
ส =ดัชนีฤดูกาล
Ɣ = ค่าสัมประสิทธิ์
ในการคำนวณดัชนีตามฤดูกาล
- ไปที่ F15 และเขียนสูตรต่อไปนี้
=$C$6*(C15/D15)+(1-$C$6)*F11
- กด ENTER .
- ป้อนอัตโนมัติ มากถึง F22 .
หมายเหตุ: ละเว้นข้อผิดพลาดสักครู่ มันจะดีเมื่อคุณวัดระดับถัดไประดับ และเทรนด์ต่อไป .
ขั้นตอนที่ 5:กำหนดระดับถัดไป
ตอนนี้ฉันจะแสดงวิธีกำหนดระดับถัดไปโดยใช้สูตร
Lt =α(Yt/St-m)+(1-α)(Lt-1+Tt-1)
ที่ไหน
ล =ระดับ
ท =เทรนด์
ม =4 สำหรับรายไตรมาส 12 เป็นประจำเดือน
ส =ดัชนีฤดูกาล
α = ค่าสัมประสิทธิ์
- ไปที่ D16 และเขียนสูตรต่อไปนี้
=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)
- กด ENTER .
- ป้อนอัตโนมัติ มากถึง D22 .
ขั้นตอนที่ 6:วัดแนวโน้มถัดไป
มาดูแนวคิดของเอฟเฟกต์เทรนด์กันเถอะ สูตรคือ
Tt=β(Lt-Lt-1)+(1-β)Tt-1
ที่ไหน
ล =ระดับ
ท =เทรนด์
ม =4 สำหรับรายไตรมาส 12 เป็นประจำเดือน
ส =ดัชนีฤดูกาล
β= ค่าสัมประสิทธิ์
ในการคำนวณผลกระทบของแนวโน้ม
- ไปที่ E6 และเขียนสูตรต่อไปนี้
=$C$5*(D16-D15)+(1-$C$5)*E15
- กด ENTER เพื่อดำเนินการต่อ
- ป้อนอัตโนมัติ มากถึง E22 .
ขั้นตอนที่ 7:ค้นหามูลค่าที่คาดการณ์ไว้เพื่อเปรียบเทียบกับยอดขายจริง
ตอนนี้ เราจะคำนวณมูลค่าที่คาดการณ์ไว้เพื่อเปรียบเทียบกับยอดขายจริง อันแรกจะเป็นสำหรับไตรมาสที่ 6 สูตรคำนวณค่าที่คาดการณ์ไว้ (สำหรับการเปรียบเทียบ) คือ
ฟ ท =(L t-1 + ท t-1 )* ส t-M
ลงมือทำกันเถอะ
- ไปที่ G16 และเขียนสูตรต่อไปนี้
=(D16+E16)*F12
- กด ENTER ไป
- ป้อนอัตโนมัติ มากถึง G22 .
ขั้นตอนที่ 8:คำนวณข้อผิดพลาดในการคาดการณ์
ตอนนี้ เราจะคำนวณข้อผิดพลาดในการคาดการณ์โดยลบมูลค่าที่คาดการณ์ไว้ออกจากยอดขายจริง
- ไปที่ H16 แล้วเขียนสูตร
=C16-G16
- จากนั้นกด ENTER .
- ป้อนอัตโนมัติ มากถึง H22 .
ขั้นตอนที่ 9:กำหนดค่า K สำหรับไตรมาสที่จะคาดการณ์
ได้เวลาคำนวณการคาดการณ์ แต่ก่อนหน้านั้นเราต้องเข้าใจ สัมประสิทธิ์ k . แสดงถึงเวลาในอนาคตสำหรับการคาดการณ์ ในกรณีของเรา เราจะคำนวณการคาดการณ์สำหรับ 4 ไตรมาส 2023 และเรามีข้อมูลสำหรับปี 2022
ดังนั้น สำหรับไตรมาสแรกของปี 2023 ค่าของ k จะเท่ากับ 1 สำหรับไตรมาสที่ 2 จะเท่ากับ 2 เป็นต้น
ขั้นตอนที่ 10:คำนวณมูลค่าที่คาดการณ์ไว้
ตอนนี้ เราพร้อมที่จะคำนวณค่าที่คาดการณ์ไว้ของเราแล้ว เราจะใช้ระดับ เทรนด์ และฤดูกาลล่าสุดที่มีในการคำนวณ
- ไปที่ G23 และเขียนสูตรต่อไปนี้
=($D$22+F23*$E$22)*F19
- จากนั้นกด ENTER เพื่อให้ได้ผลลัพธ์
- ตอนนี้ ป้อนอัตโนมัติ มากถึง G25 .
ขั้นตอนที่ 11:เพิ่มประสิทธิภาพอัลฟ่า เบต้า และแกมมา
ตอนนี้ เพื่อลดข้อผิดพลาด เราจะเพิ่มประสิทธิภาพค่าของ อัลฟ่า , เบต้า และ แกมม่า . เราจะรับความช่วยเหลือจาก โปรแกรมแก้ปัญหา Excel ให้ทำเช่นนั้น
- ขั้นแรก เราต้องคำนวณค่าคลาดเคลื่อนกำลังสองของค่าเฉลี่ยรูท โดยไปที่ C7 และเขียนสูตรต่อไปนี้
=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))
รายละเอียดสูตร:
- 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 . ดังนั้นหากต้องการเพิ่มข้อจำกัดแรก ให้ตั้งค่าการอ้างอิงเซลล์ และ คุณค่า . (ดูภาพ)
- ในทำนองเดียวกัน หลังจากเพิ่มข้อจำกัดที่สอง ผลลัพธ์ของคุณจะเป็นเช่นนี้ จากนั้นคลิก แก้ไข .
- Excel จะลดข้อผิดพลาดด้วยการเพิ่มประสิทธิภาพ อัลฟ่า , เบต้า และ แกมมา .
สิ่งที่ควรจำ
- คุณต้องเปิดใช้งาน โปรแกรมแก้ไขเพิ่มเติม .
- เราไม่กังวลเกี่ยวกับ ค่า k สำหรับการคำนวณการคาดการณ์ที่เราจะเปรียบเทียบกับยอดขายจริงที่มีอยู่
บทสรุป
ในบทความนี้ ฉันได้อธิบายวิธีการใช้Holt-Winters Exponential Smoothing ใน Excel . ฉันหวังว่ามันจะช่วยให้ทุกคน กรุณาแสดงความคิดเห็นหากคุณมีคำถามใด ๆ
