การใช้ excel คำนวณค่าพยากรณ์โดยใช้ค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้ง(Double Moving Average)

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

หลักการคำนวณ

จะเริ่มต้นคำนวณค่าเฉลี่ยเคลื่อนที่แบบธรรมดาของข้อมูล N ชุดแรก ตามที่ได้นำเสนอไปแล้ว จากนั้นจึงนำค่าเฉลี่ยที่ได้ในช่วงเวลาที่ผ่านมาแล้วคำนวณหาค่าเฉลี่ยอีกครั้ง ดังนั้นค่าพยากรณ์แบบค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้งจะต้องมีข้อมูลก่อนหน้าเท่ากับ 2N เมื่อ N คือจำนวนข้อมูลที่ต้องใช้คำนวณค่าพยากรณ์แบบค่าเฉลี่ยเคลื่อนที่ธรรมดา เช่น ค่าเฉลี่ยเคลื่อนที่แบบ 3 เดือน จะต้องใช้ข้อมูลย้อนหลัง 6 เดือนเพื่อให้สามารถพยากรณ์ได้ ดังแสดงในภาพที่ 1

ภาพที่ 1 สมการการคำนวณค่าพยากรณ์

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

คำนวณค่าเฉลี่ยเคลื่อนที่แบบธรรมดาในหลัก C ตั้งแต่เซลล์ C8 (สัปดาห์ที่ 4 เป็นต้นไป) โดยใช้สูตร excel : AVERAGE(OFFSET($B$5,COUNT($B$5:B8)-$D$1,0,$D$1,1))

คำนวณค่าเฉลี่ยเคลื่อนที่ซ้ำครั้งที่สองในหลัก D ตั้งแต่เซลล์ D11 (สัปดาห์ที่ 7 เป็นต้นไป) โดยใช้สูตร excel : AVERAGE(C8:C11)

คำนวณค่าคงที่ a และ b ในหลัก E และ F ตั้งแต่แถวที่ 11 (สัปดาห์ที่ 7 เป็นต้นไป) โดยใช้สูตร excel :

E11 = 2*C11 - D11

F11 = 2*(C11 - D11)/($D$1)

สามารถคำนวณค่าพยากรณ์ได้ตั้งแต่สัปดาห์ที่ 8  โดยใช้สูตร excel : 

G12 = E11 + F11     (ให้ m = 1 หมายถึงช่วงเวลาที่นับจากเวลาปัจจุบันไป 1 สัปดาห์  )

หากต้องการค่าพยากรณ์ในสัปดาห์ที่ 13 โดยใช้ข้อมูลการคำนวณในสัปดาห์ที่ 10 ( m = 3) จะเขียนสูตร excel ได้เป็น E14 +3*F14

จากสูตร excel ที่คำนวณได้เราสามารถแสดงค่าพยากรณ์ยอดขายเดือนที่ 11 ได้ดังภาพที่ 2 

ภาพที่ 2 ผลการคำนวณค่าพยากรณ์ยอดขาย

เราสามารถ copy สูตร excel ในหลัก C14 , D14 , E14 ,F14 และ G15 เพื่อคำนวณค่าพยากรณ์ยอดขายในสัปดาห์ที่ 12 ได้ โดยวางสูตร excel ที่ copy มาลงในแถวถัดไป 

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

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

การใช้ 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 สร้างกราฟแบบไดนามิกส์ มาประยุกต์ต่อไปครับ