การสร้างกราฟแสดงค่าการพยากรณ์แบบอัตโนมัติด้วย excel

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

1. หลัก B เป็นรายชื่อเดือน

2. หลัก C เป็นราคาสินค้าจริง

3. หลัก D เป็นค่าพยากรณ์ 3 เดือน
4. หลัก E เป็นค่าพยากรณ์ 4 เดือน

5. หลัก F เป็นค่าพยากรณ์ 5 เดือน

       ข้อมูลจะถูกบันทึกตั้งแต่แถวที่ 4 เป็นต้นไป สำหรับสูตร excel ที่ใช้ในการคำนวณค่าเฉลี่ย 3 เดือน 4 เดือน และ 5 เดือน จะเริ่มที่ CELL D7 , E8 และ F9 ตามลำดับ โดยสูตร excel จะเป็นไปตามที่นำเสนอในบทความ การใช้ excel พยากรณ์ด้วยวิธีค่าเฉลี่ยเคลื่อนที่ ดังนี้

CELL D7

IF(ISBLANK(C6),"",AVERAGE(OFFSET($C$4,COUNT($C$4:C6)-3,0,3,1)))

คำอธิบาย

จะตรวจสอบการบันทึกข้อมูลราคาสินค้าเดือน มีค 55 (C6) หากยังไม่มีการบันทึกจะยังไม่คำนวณค่าเฉลี่ย แต่หากมีข้อมูลใน C6 จะคำนวณค่าเฉลี่ยโดยใช้สูตร excel : AVERAGE ร่วมกับการใช้สูตร excel : OFFSET โดยใช้ข้อมูลย้อนหลัง 3 เดือน

CELL E8

IF(ISBLANK(C7),"",AVERAGE(OFFSET($C$4,COUNT($C$4:C7)-4,0,4,1))) 

คำอธิบาย

จะตรวจสอบการบันทึกข้อมูลราคาสินค้าเดือน เมย 55 (C7) หากยังไม่มีการบันทึกจะยังไม่คำนวณค่าเฉลี่ย แต่หากมีข้อมูลใน C7 จะคำนวณค่าเฉลี่ยโดยใช้สูตร excel : AVERAGE ร่วมกับการใช้สูตร excel : OFFSET โดยใช้ข้อมูลย้อนหลัง 4 เดือน

CELL F9

IF(ISBLANK(C8),"",AVERAGE(OFFSET($C$4,COUNT($C$4:C8)-5,0,5,1)))

คำอธิบาย

จะตรวจสอบการบันทึกข้อมูลราคาสินค้าเดือน พค 55 (C8) หากยังไม่มีการบันทึกจะยังไม่คำนวณค่าเฉลี่ย แต่หากมีข้อมูลใน C8 จะคำนวณค่าเฉลี่ยโดยใช้สูตร excel : AVERAGE ร่วมกับการใช้สูตร excel : OFFSET โดยใช้ข้อมูลย้อนหลัง 5 เดือน

ทำการ copy สูตรจาก D7, E8 และ F9  ลงไปเพื่อให้ excel คำนวณค่าเฉลี่ยโดยอัตโนมัติหากมีการบันทึกข้อมูลราคาสินค้าในหลัก C รูปแบบผลการคำนวณค่าเฉลี่ยแสดงได้ดังภาพที่ 1 โดยการใส่ลำดับของข้อมูลในหลัก A สามารถใช้สูตร Excel : IF ได้ ซึ่งได้นำเสนอไว้แล้ว

ภาพที่ 1 ผลการใช้ excel คำนวณค่าเฉลี่ยเคลื่อนที่

การจัดเตรียมข้อมูลก่อนสร้างกราฟเส้น

1. สร้างข้อมูลแบบพลวัตร โดยแบ่งข้อมูลดังนี้

   ข้อมูลแกนนอน (แกน x) ซึ่งแสดงรายชื่อเดือนต่างๆ เราจะกำหนดข้อมูลแบบพลวัตรได้ดังนี้ (สมมุติให้ข้อมูลถูกบันทึกไว้ที่ Sheet ชื่อ MV

ชื่อข้อมูล X_DATA

สูตร excel : OFFSET(MV!$B$4,0,0,COUNT(MV!$C:$C)+1,1)

ชื่อข้อมูล Y_Data เป็นข้อมูลราคาสินค้าจริงในหลัก C

สูตร excel : OFFSET(MV!$C$4,0,0,COUNT(MV!$C:$C)+1,1)

ชื่อข้อมูล Y3_Data เป็นข้อมูลค่าเฉลี่ยเคลื่อนที่แบบ 3 เดือน

สูตร excel : OFFSET(MV!$D$4,0,0,COUNT(MV!$D:$D)+3,1)

ชื่อข้อมูล Y4_Data เป็นข้อมูลค่าเฉลี่ยเคลื่อนที่แบบ 4 เดือน

สูตร excel : OFFSET(MV!$E$4,0,0,COUNT(MV!$E:$E)+4,1)

ชื่อข้อมูล Y5_Data เป็นข้อมูลค่าเฉลี่ยเคลื่อนที่แบบ 5 เดือน

สูตร excel : OFFSET(MV!$F$4,0,0,COUNT(MV!$F:$F)+4,1)

2. สร้างกราฟเส้น โดยเลือก แทรก->แผนภูมิเส้นพร้อมเครื่องหมาย แสดงแนวโน้มตามเวลา และเลือกข้อมูลเพื่อเปรียบเทียบข้อมูลจริงและข้อมูลจากการพยากรณ์ ดังแสดงในภาพที่ 2 , 3 และ 4

ภาพที่ 2 การเลือกแหล่งข้อมูล

ภาพที่ 4 การพิมพ์เลือกค่าข้อมูลพลวัตรสำหรับราคาสินค้า

ภาพที่ 5 การพิมพ์เลือกค่าข้อมูลพลวัตรสำหรับป้ายชื่อแกน

กราฟเส้นที่แสดงแนวโน้มราคาสินค้าที่ได้แสดงดังภาพที่ 6

ภาพที่ 6 กราฟแสดงแนวโน้มราคาสินค้าด้วยวิธีค่าเฉลี่ยเคลื่อนที่

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