วิธีการคำนวณการคาดการณ์ค่าเฉลี่ยเคลื่อนที่ใน excel

การคาดการณ์ค่าเฉลี่ยเคลื่อนที่ ใช้ค่าเฉลี่ยเคลื่อนที่ของช่วงก่อนหน้าจำนวนหนึ่งเพื่อคาดการณ์มูลค่าของช่วงถัดไป

ตัวอย่างต่อไปนี้แสดงวิธีการคำนวณการคาดการณ์ค่าเฉลี่ยเคลื่อนที่ใน Excel

ตัวอย่าง: วิธีการคำนวณการคาดการณ์ค่าเฉลี่ยเคลื่อนที่ใน Excel

สมมติว่าเรามีชุดข้อมูลต่อไปนี้ที่แสดงยอดขายรวมของธุรกิจเป็นเวลา 20 วันติดต่อกัน:

สมมติว่าเราต้องการคำนวณการคาดการณ์ค่าเฉลี่ยเคลื่อนที่ 5 วัน

ซึ่งหมายความว่าเราจะใช้มูลค่าการขายเฉลี่ยของ 5 วันก่อนหน้าเป็นมูลค่าคาดการณ์สำหรับวันถัดไป

เมื่อต้องการทำเช่นนี้ เราสามารถพิมพ์สูตรต่อไปนี้ในเซลล์ C8 :

 =AVERAGE( B2:B6 )

จากนั้นเราสามารถคลิกและลากสูตรนี้ไปยังแต่ละเซลล์ที่เหลือในคอลัมน์ C:

ค่าในคอลัมน์ C แสดงถึงมูลค่ายอดขายเฉลี่ยเคลื่อนที่ 5 วันที่คาดการณ์ไว้

เช่น มูลค่ายอดขายที่คาดการณ์ในวันที่ 6/1/2023 คือ 11

ในการหาปริมาณว่าค่าที่คาดการณ์ของเราตรงกับมูลค่าการขายจริงได้ดีเพียงใด เราสามารถคำนวณ MAPE (ค่าเฉลี่ยความคลาดเคลื่อนเปอร์เซ็นต์สัมบูรณ์) ซึ่งใช้สูตรต่อไปนี้:

MAPE = (1/n) * Σ(|จริง – คาดการณ์| / |จริง|) * 100

ทอง:

• Σ – สัญลักษณ์ที่หมายถึง “ผลรวม”
• n – ขนาดตัวอย่าง
• real – มูลค่าที่แท้จริงของข้อมูล
• พยากรณ์ – ค่าที่คาดหวังของข้อมูล

MAPE มักใช้เพราะง่ายต่อการตีความและอธิบาย ตัวอย่างเช่น ค่า MAPE 11.5% หมายความว่าความแตกต่างโดยเฉลี่ยระหว่างค่าที่คาดการณ์กับค่าจริงคือ 11.5%

ยิ่งค่า MAPE ต่ำ โมเดลก็ยิ่งสามารถทำนายค่าได้ดีขึ้นเท่านั้น

ในการคำนวณ MAPE สำหรับแบบจำลองการคาดการณ์นี้ เราสามารถพิมพ์สูตรต่อไปนี้ในเซลล์ D7 :

 =ABS( B7 - C7 )/ B7 *100

จากนั้นเราสามารถคลิกและลากสูตรนี้ไปยังแต่ละเซลล์ที่เหลือในคอลัมน์ D:

สุดท้าย เราสามารถพิมพ์สูตรต่อไปนี้ในเซลล์ D22 เพื่อรับ MAPE สำหรับแบบจำลองการคาดการณ์นี้:

 =AVERAGE( D7:D21 )

ภาพหน้าจอต่อไปนี้แสดงวิธีใช้สูตรนี้ในทางปฏิบัติ:

จะเห็นได้ว่า MAPE ของรุ่นนี้อยู่ที่ประมาณ 19.12%

นี่บอกเราว่าความแตกต่างเฉลี่ยระหว่างมูลค่าการขายที่คาดการณ์โดยค่าเฉลี่ยเคลื่อนที่ 5 วันและมูลค่าการขายจริงคือ 19.12%

หากเราต้องการ เราสามารถทำซ้ำขั้นตอนนี้โดยใช้ช่วงเวลาอื่น เช่น การคาดการณ์ค่าเฉลี่ยเคลื่อนที่ 10 วัน เพื่อพิจารณาว่าการคาดการณ์นี้นำไปสู่ค่า MAPE ที่ต่ำกว่าหรือไม่

แหล่งข้อมูลเพิ่มเติม

บทช่วยสอนต่อไปนี้อธิบายวิธีการทำงานทั่วไปอื่นๆ ใน Excel:

วิธีการคำนวณค่าเฉลี่ยเคลื่อนที่แบบถ่วงน้ำหนักใน Excel
วิธีการคำนวณค่าเฉลี่ยสะสมใน Excel
วิธีการคำนวณค่าเฉลี่ยเคลื่อนที่แบบเอ็กซ์โปเนนเชียลใน Excel