การสร้างกราฟแสดงค่าการพยากรณ์แบบอัตโนมัติด้วย 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 : Frequency (Method to create histogram chart with excel formula)

การใช้ excel ขอนำเสนอวิธีการสร้างแผนภูมิฮิสโตแกรมโดยใช้สูตร Excel : Frequency จากที่ได้นำเสนอการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram ใน Add -Ins Data Analysis ไปแล้ว ซึ่งพบว่าวิธีการดังกล่าวอาจจะไม่เหมาะสมหากข้อมูลที่ต้องการวิเคราะห์มีการเปลี่ยนแปลงจำนวนอยู่ตลอดเวลา โดยวิธีการเดิมนั้นจะต้องทำการเรียกคำสั่ง  Histogram ทุกครั้งเมื่อมีการปรับเพิ่มหรือลดลงของข้อมูล ดังนั้นเพื่อเพิ่มประสิทธิภาพในการวิเคราะห์ข้อมูล จึงขอนำเสนอ วิธีการสร้างแผนภูมิฮิสโตแกรมโดยใช้สูตร Excel : Frequency ซึ่งจะทำให้แผนภูมิฮิสโตแกรมที่สร้างด้วยวิธีนี้มีการอัพเดต รูปร่างอย่างอัตโนมัติเมื่อมีการอัพเดตข้อมูล โดยข้อมูลสำหรับสร้างฮิสโตแกรมผมจะขออ้างอิงตัวอย่างเดิมจากบทความการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram มานำเสนอ เริ่มจาก

1. ปรับปรุงการเก็บข้อมูลโดยย้ายข้อมูลทั้งหมดถูกเก็บอยู่ในหลัก A เพียงหลักเดียว

2. ใช้สูตร excel แสดงค่า max. ค่า min. จำนวนข้อมูล  Range of data จำนวนชั้นข้อมูล และความกว้างของชั้นข้อมูล ตามที่ได้นำเสนอในบทความ การสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram 

3. สร้างข้อมูลขอบเขตข้อมูล โดยใช้สูตร excel ตามที่นำเสนอในบทความการสร้างแผนภูมิฮิสโตแกรมโดยใช้คำสั่ง Histogram 

4. หลังปรับรูปแบบการเก็บและแสดงข้อมูล จะได้รูปแบบใน excel ก่อนหาความถี่ของข้อมูลแต่ละชั้นดังภาพที่ 1

5. ขั้นตอนการคำนวณความถี่ของข้อมูลแต่ละชั้นด้วยสูตร excel : FREQUENCY ทำได้ดังนี้
    5.1 พิมพ์สูตร excel ใน D11 ดังนี้ D11 = FREQUENCY(A:A,C11:C18)
    5.2 เลือก CELL D11:D18
    5.3 กดปุ่ม F2
    5.4 กดปุ่ม Shift + Ctrl + Enter เพื่อคำนวณความถี่ของชั้นข้อมูลที่เหลือ (เป็นการบอกให้ excel คำนวณสูตร excel แบบเวกเตอร์)
6. สร้างแผนภูมิฮิสโตรแกรมโดยใช้แผนภูมิแบบ Scatter ดังที่ได้นำเสนอในบทความ การใช้ excel ปรับแต่งกราฟแท่งในแผนภูมิฮิสโตแกรม
7. แผนภูมิฮิสโตแกรมแสดงได้ดังภาพที่ 2

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

ภาพที่ 2 แผนภูมิฮิสโตแกรม

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

หัวข้อที่น่าสนใจ

การใช้ excel พยากรณ์ยอดขายด้วยเทคนิค Monte Carlo Simulation