การพยากรณ์ หรือประมาณการด้วย Excel

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

• แน้วโน้มทางการตลาดจะเป็นอย่างไร?
• ยอดขายจะเป็นเท่าไหร่?
• สินค้าคงคลังจะเป็นเท่าไหร่?
• และควรจะต้องมีพนักงานฝ่ายต่าง ๆ จำนวนเท่าไหร่?

หรืองานอะไรก็ตาม ซึ่งผู้บริหารไม่ว่าจะระดับไหนก็ต้องวางแผนทรัพยากรล่างหน้าอยู่เสมอ ไม่ว่าจะเป็นการเพิ่มหรือลดก็ตาม

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

ในตอนนี้เราจะขอใช้สูตร Excel มาช่วยในการประมาณการสิ่งที่จะเกิดขึ้น ซึ่งน่าจะช่วยให้เป็นตัวอย่างให้เรานำไปใช้ในงานของผู้อ่านได้

ตัวอย่างที่เราจะใช้ในตอนนี้คือ ประมาณการยอดผู้ติดเชื้อโควิด 19 ในประเทศไทยของเราเอง ซึ่งข้อมูลเราได้มาจาก รายงาน COVID-19 ประจำวัน ของ data.go.th แล้วนำมาสรุปเป็นยอดผู้ติดเชื้อต่อวัน แล้วก็จะได้ไฟล์ตัวอย่าง ซึ่งกดไปดูหรือดาวน์โหลดได้จากลิงค์นี้

โดยในไฟล์ตัวอย่าง ค่าในคอลัมภ์ต่าง ๆ จะเป็นตามนี้ ส่วนสูตรคำนวณจะอธิบายเพิ่มเติมอีกที

• คอลัมภ์ A คือ Date ซึ่งก็คือวันที่
• คอลัมภ์ B คือ CasePerDay ซึ่งก็คือ ยอดผู้ติดเชื้อต่อวัน ซึ่งเรารวมมาจากข้อมูลที่ได้จาก data.go.th ในลิงค์ที่ให้ไว้ด้านบน โดยเราสามารถนำข้อมูลยอดผู้ติดเชื้อรายวันของแต่ละวันมาป้อนลงในช่องนี้ได้ เพื่อให้การพยากรณ์วันต่อ ๆ ไปมีความแม่นยำขึ้น
• คอลัมภ์ C คือ LowerConfidenceBound พูดง่าย ๆ ก็คือ ยอดต่ำสุดที่เป็นได้จากการพยากรณ์
• คอลัมภ์ D คือ Forecast หรือเป็นค่าพยากรณ์ที่ได้จากข้อมูลย้อนหลัง
• คอลัมภ์ E คือ UpperConfidenceBound พูดง่าย ๆ ก็คือ ยอดสูงสุดที่เป็นได้จากการพยากรณ์
• คอลัมภ์ F คือ Error ซึ่งจะได้จากการเอายอดผู้ติดเชื้อจริงในวันนั้น ๆ เทียบกับยอดที่พยากรณ์ออกมาและทำเป็นเปอร์เซ็นต์

สูตร Excel สำหรับการพยากรณ์จากข้อมูลเดิมที่เรามีอยู่แล้ว

จะขอเริ่มจากคอลัมภ์ D หรือ Forecast(CasePerDay) ก่อน โดยผมเริ่มใช้พยากรณ์ในเดือนสิงหา (2564) ที่เซลล์ D580 เพราะอยากรู้สถานการณ์จะรุนแรงขึ้นหรือทุเลาลง ซึ่งสูตรที่ใช้จะเป็น

=FORECAST.ETS(A580,$B$2:$B579,$A$2:$A579,0,1,1)

โดยฟังก์ชัน FORECAST.ETS() จะใช้ได้กับ Excel 2016 เป็นต้นมา ส่วนใครใช้เวอร์ชันเก่ากว่านี้ให้ใช้ FORECAST() ซึ่งใช้ได้เหมือนกัน เพียงแต่ FORECAST() จะไม่ได้ต้องการอาร์กิวเมนต์เพิ่มเติม 3 ตัวหลัง แต่มันมีความแม่นยำน้อยกว่ามาก

เหตุที่ฟังก์ชัน FORECAST.ETS() มีความแม่นยำกว่านั้นเป็นเพราะว่า ให้อัลกอริทึม (algorithm) ที่แตกต่างออกไป โดยใช้หลัการพยากรณ์แบบ AAA (Additive error, Additive trend, and Additive seasonality) ของ Exponential Triple Smooting (ETS) ถ้าอ่านแล้ว งง! ช่างมันครับ😂 เพราะมันเป็นหลักการพยากรณ์ค่าที่คนที่เขียนฟังก์ชัน (ไมโครซอฟต์) เอามาใช้ ซึ่งจะมีหลักการทำงานประมาณนี้

พยากรณ์จากการนำค่าเฉลี่ยแบบถ่วงน้ำหนัก แต่น้ำหนักจะลดลงเรื่อย ๆ แบบเท่าทวีคูณ หากข้อมูลนั้นเก่ากว่า หรือพูดอีกนัยหนึ่งก็คือ จะให้น้ำหนักกับข้อมูลใหม่มากกว่า

หน้า 171 Forecasting: Principles and Pratice, 2013

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

ไทม์ไลน์ (Timeline)

Timeline ถือว่าเป็นสิ่งสำคัญมากต่อความแม่นยำในการพยากรณ์ของ FORECAST.ETS() และต้องมีขั้นที่เท่ากันเสมอ ไม่ว่าจะเป็นทุกวัน ทุก 3 วัน ทุกสัปดาห์ ทุกเดือน หรืออาจจะเป็นขั้นของจำนวนก็ได้ เป็นต้น ที่สำคัญคือ ขั้นต้องเท่ากันเสมอ ซึ่งในตัวอย่างก็คือ ทุกวัน

ไวยากรณ์ (Syntax)

FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

• target_date [จำเป็นต้องมี] คือ จุดข้อมูลที่คุณต้องการค่าพยากรณ์ โดยอาจเป็น วันที่ เดือน เวลา หรือตัวเลข แต่ถ้าวันที่ เดือน ปี เวลา หรือตัวเลยนั้น เกิดขึ้นก่อนที่จะมีข้อมูล (พยากรณ์ค่าในอดีต) FORECAST.ETS() จะแสดง error เป็น #NUM!
• values [จำเป็นต้องมี] คือ ชุดข้อมูลในอดีตที่เราจะนำมาใช้พยากรณ์
• timeline [จำเป็นต้องมี] คือ ช่วงเวลาของชุดข้อมูลใน values อย่าลืมนะว่า ขั้นต้องเท่ากันและต้องไม่เป็นศูนย์ แต่ไม่มีความจำเป็นต้องเรียงข้อมูลก็ได้ เพราะฟังก์ชันจะนำไปเรียงเอง
  • ถ้าขั้นของไทม์ไลน์ไม่เท่ากัน FORECAST.ETS() จะแสดง error เป็น #NUM
  • ถ้าในไทม์ไลน์มีค่าซ้ำกัน FORECAST.ETS() จะแสดง error เป็น #VALUE
  • ถ้าขนาดของ range ของ values กับ timeline ไม่เท่ากัน FORECAST.ETS() จะแสดง error เป็น #N/A
• seasonality [ไม่ใส่ก็ได้] ค่านี้จะเป็นตัวเลข
  • ปกติจะเป็น 1 ซึ่งหมายถึงให้ฟังก์ชันคำนวณหาให้
  • แต่ถ้าใส่ 0 คือ ข้อมูลที่ใส่ใน values ไม่มีความเป็นฤดูกาล
  • ตัวเลขนี้ต้องเป็นจำนวนเต็มที่ระบุความเป็นฤดูกาลของข้อมูลที่เราใส่ใน values
  • ค่ามากที่สุดที่ใส่ได้คือ 8760 (จำนวนชั่วโมงใน 1 ปี) ถ้าค่ามากกว่านี้ FORECAST.ETS() จะแสดง error เป็น #NUM
• data_completion [ไม่ใส่ก็ได้] จากที่เคยบอกไปก่อนหน้านี้ว่า FORECAST.ETS() ได้ข้อมูลที่เป็นช่วง ๆ หรือขั้นที่เท่ากัน แต่ FORECAST.ETS() ก็ยอมให้ข้อมูลไม่ครบ หรือหายไปได้ 30% นะ โดย
  • ถ้าใส่ 0 คือ ปรับข้อมูลในช่วง หรือขั้นนั้นให้เป็น 0
  • ถ้าใส่ 1 คือ จะนำที่อยู่ใกล้เคียงมาเฉลี่ย
• aggregation [ไม่ใส่ก็ได้] ใช้กำหนดว่าจะให้ฟังก์ชันเอาข้อมูลที่เกิดในช่วงเวลาเดียวกัน (same time stamp) มาทำอย่างไรดี ซึ่งปกติคือ 0 หมายถึง ให้นำมาเฉลี่ย (Average) ส่วนตัวเลือกอื่น ๆ ได้แก่ นำมาบวกัน (SUM) นับจำนวน (COUNT) นับเฉพาะที่มีข้อมูล (COUNTA) เลือกเอาค่าน้อยสุด (MIN) เลือกเอาค่ามากสุด (MAX) และค่ามัธยฐาน (MEDIAN) ซึ่งก็ต้องใส่เป็นตัวเลข 1,2,3,4,5,6 แล้วแต่จะเลือกใช้