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

การสร้างกราฟหรือแผนภูมิแบบเรดาร์ด้วย excel (How to make radar chart)

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

ภาพที่ 1 ข้อมูลใน Sheet Excel

ขั้นตอนการสร้างกราฟเรดาห์โดยใช้ excel ทำได้ดังนี้

1.เลือกข้อมูล cell A1:B6

2.คลิกเลือกเมนู insert

3.เลือกทูลบาร์ Other charts

4.เลือก chart type เป็น radar ชนิด Radar with Markers

จะได้กราฟหรือแผนภูมิแบบเรดาร์ดังแสดงในภาพที่ 2

ภาพที่ 2 กราฟแบบเรดาร์จากโปรแกรม excel

ในกรณีที่คะแนนสูงสุดของการประเมินในทุกหัวข้อเท่ากันท่านผู้อ่านสามารถปรับสเกลของกราฟได้ สมมุติให้ทุกหัวข้อการประเมินมีคะแนนเต็ม 6 คะแนน ให้ท่านผู้อ่านเลือกแกนกราฟและคลิกเมาส์ปุ่มขวา เลือก Format axis... โปรแกรม excel จะแสดงหน้าต่าง Format axis ให้ท่านผู้อ่านเลือกหัวข้อ Axis Options ด้านขวา ในพื้นที่ด้านซ้าย หัวข้อ Maximum คลิกเลือก  Fixed ป้อนค่าคะแนนเต็ม 6 และกดปุ่ม Close จะได้กราฟแบบเรดาร์ดังแสดงในภาพที่ 3

ภาพที่ 3 กราฟแบบเรดาร์หลังปรับคะแนนเต็มของหัวข้อประเมิน

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

ภาพที่ 4 ข้อมูลการประเมินผลแบบสอบถาม

ให้ท่านผู้อ่านดำเนินการตามขั้นตอน 1-4 ของวิธีการสร้างกราฟด้วย excel โดยในขั้นตอนที่ 1 ให้เลือกข้อมูลจาก cell A1:C6

ภาพที่ 5 แผนภูมิเรดาร์หลังการปรับปรุง

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