การใช้ excel คำนวณค่าเฉลี่ยเคลื่อนที่ (Moving average) เพื่อใช้ในการพยากรณ์

ค่าเฉลี่ยเคลื่อนที่เป็นเทคนิคการพยากรณ์ที่นิยมใช้กันมากในการพยากรณ์ปริมาณต่างๆที่สนใจ เช่น แนวโน้มการตลาดของยอดขายสินค้าหรือราคาวัตถุดิบ ในช่วงสั้นๆ เช่น อาจจะเป็นสัปดาห์หรือ เดือน เป็นต้น โดยวิธีการพยากรณ์ด้วยค่าเฉลี่ยเคลื่อนที่จะไม่ถือว่าเป็นวิธีการทางสถิติ แต่จะเป็นการกำหนดขึ้นโดยอาศัยหลักเกณฑ์แบบง่าย คือ การนำค่าข้อมูลที่เกิดขึ้นจริงในช่วงเวลาที่ผ่านมาคำนวณค่าเฉลี่ยและนำค่าเฉลี่ยที่คำนวณได้ไปเป็นค่าพยากรณ์ในช่วงเวลาถัดไป โดยตัวแปรสำคัญของ ค่าเฉลี่ยเคลื่อนที่คือ จำนวนข้อมูลที่เกิดขึ้นจริงที่นำมาคำนวณ (N) โดยเราจะเรียกค่าเฉลี่ยเคลื่อนที่ตามจำนวนข้อมูลที่นำมาคำนวณเช่น ค่าเฉลี่ยเคลื่อนที่แบบ 3 เดือน จะหมายถึงค่าพยากรณ์แบบที่ใช้ข้อมูลจริงจำนวน 3 เดือนย้อนหลังมาคำนวณ เป็นต้น ดังนั้นจากที่กล่าวมาขอสรุปลักษณะของการพยากรณ์ค่าเฉลี่ยเคลื่อนที่ไว้เป็น 2 ข้อคือ

1. ก่อนจะพยากรณ์ ผู้คำนวณต้องเตรียมข้อมูลในช่วงเวลาที่ผ่านมาให้มีจำนวนเท่ากับที่ต้องการก่อน เช่น หากต้องการพยากรณ์ด้วยค่าเฉลี่ยแบบ 3 เดือน เราจะทำการพยากรณ์ข้อมูลได้ตั้งแต่เดือนที่ 4 เป็นต้นไป
2. ค่าการพยากรณ์จะมีความเรียบมากขึ้นหากใช้จำนวนข้อมูลมากขึ้น

ในการคำนวณค่าเฉลี่ยเคลื่อนที่ เราจะใช้ excel ช่วยในการคำนวณเพื่อความสะดวกและถูกต้องของค่าการพยากรณ์ โดยในบทความนี้ขอยกตัวอย่างข้อมูลราคาสินค้าชนิดหนึ่งที่มีการสังเกตการไว้ สมมุติให้ข้อมูลที่เกิดขึ้นจริง 4 เดือนแรกอยู่ในหลัก B โดยเริ่มต้นที่ B3 ดังแสดงในภาพที่ 1 เราต้องการคำนวณค่าพยากรณ์ราคาสินค้าในเวลาถัดไปทุกครั้งที่มีการเพิ่มข้อมูลที่เกิดขึ้นจริงในหลัก B ซึ่งจากข้อกำหนดดังกล่าวมันเป็นการคำนวณแบบไดนามิกส์ สมมุติรูปแบบการจัดใน worksheet แสดงดังภาพที่ 1

โดยกำหนดสูตร excel : COUNT เพื่อช่วยนับจำนวนข้อมูลจริงในหลัก B และแสดงผลในเซลล์ C3 ในขณะที่ผู้ใช้จะกำหนดจำนวนเดือนเพื่อใช้คำนวณค่าเฉลี่ยเคลื่อนที่ได้ในเซลล์ที่ C2 

ภาพที่ 1 รูปแบบการแสดงผลข้อมูลใน excel 

สูตร excel สำหรับคำนวณค่าเฉลี่ยเคลื่อนที่

วิธีคำนวณ

• ใช้สูตร excel : AVERAGE เพื่อคำนวณค่าเฉลี่ย
• ใช้สูตร excel : OFFSET เพื่อนำข้อมูลส่งไปคำนวณค่าเฉลี่ย โดยจะใช้ข้อมูลย้อนหลังไปเป็นจำนวนเดือนที่กำหนดไว้ในเซลล์ C2 (การกำหนดข้อมูลแบบไดนามิกส์ผู้อ่านสามารถศึกษาในบทความ Excel tips การกำหนดข้อมูลแบบพลวัตรได้)

ดังนั้นสูตร excel ในเซลล์ D7 คือ =AVERAGE(OFFSET($B$6,C3-C2,0,C2,1))

ผลการคำนวณด้วย excel แสดงได้ดังภาพที่ 2 โดยค่าพยากรณ์ที่ได้จะเป็นค่าพยากรณ์ของเดือนเมษายน 55

ภาพที่ 2 ผลการคำนวณด้วย excel

หากท่านผู้อ่านเพิ่มราคาสินค้าจริงของเดือนเมษาลงไป ค่าพยากรณ์ใน D7 ก็จะเปลี่ยนไป โดยจะนำเอาราคาสินค้าเดือน กุมภาพันธ์- เมษายน มาคำนวณ โดยค่าพยากรณ์ที่ได้จะเป็นของเดือน พฤษภาคม ดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการคำนวณหลังเพิ่มข้อมูล

ท้ายที่สุดของบทความท่านผู้อ่านจะเห็นว่าเราสามารถคำนวณค่าพยากรณ์ด้วยค่าเฉลี่ยเคลื่อนที่ได้มากยากโดยใช้ excel ช่วยคำนวณ แต่จากตัวอย่างที่นำเสนอมานี้หากเรานำไปสร้างกราฟเส้นเพื่อเปรียบเทียบราคาสินค้าที่ได้พยากรณ์และราคาสินค้าที่เกิดขึ้นจริงได้แล้วจะช่วยให้การวิเคราะห์ข้อมูลของเราง่ายขึ้นกว่าเดิม ในบทความถัดไปผมจะนำเสนอการใช้ excel สร้างกราฟแสดงค่าเฉลี่ยเคลื่อนที่ ซึ่งจะนำความรู้จากบทความนี้และบทความการใช้ excel สร้างกราฟแบบไดนามิกส์ มาประยุกต์ต่อไปครับ