การใช้ excel : นำเสนอข้อมูลด้วยกราฟ Log ตอนที่ 2 (How to present data by logarithm graph by excel-2)

จากบทความการใช้ excel นำเสนอข้อมูลแบบ log scale ตอนที่ 1 ซึ่งได้นำเสนอหลักการของ linear scale และตัวอย่างการใช้ linear scale ในกราฟของ MS excel  สำหรับการสร้างงานนำเสนอข้อมูล และท้ายที่สุดได้ทิ้งท้ายปัญหาการนำเสนอช่วงข้อมูลที่มีค่าแตกต่างกันมากๆ ซึ่งพบว่าข้อมูลที่มีค่าน้อยๆไม่มีความโดดเด่นเลยใน linear scale ซึ่งผิดหลักของการนำเสนอข้อมูลด้วยแผนภาพ ที่ต้องมีความชัดเจน ดังนั้น ในบทความนี้จึงขอนำเสนอ การนำเสนอข้อมูลใน  logarithm scale กัน

 ก่อนอื่นขอเริ่มที่ นิยามของ Logarithm ก่อน logarithm เป็นระเบียบวิธีทางคณิตศาสตร์ที่นำมาใช้ในการแก้ปัญหาที่มีความซับซ้อนให้อยู่ภายใต้การดำเนินการทางคณิตศาสตร์แบบง่ายๆ เช่น เปลี่ยนจากการคูณเป็นการบวก การหารเป็นการลบ การยกกำลังเป็นการคูณเป็นต้น  โดยจะขอยกนิยามของ Logarithm  ให้เข้าใจได้ง่ายดังนี้

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

 

 

หลังจากรู้จักนิยามและสมบัติของ  logarithm มาแล้ว เราจะเริ่มเข้าสู่การประยุกต์ใช้ logarithm scale กัน โดยในบทความนี้ขอกล่าวถึง logarithm ฐาน 10 เท่านั้น โดยทั่วไป logarithm ฐานสิบจะนิยมละ เลขฐาน ไว้ และเมื่อกล่าวถึง logarithm ฐาน 10 ก็ต้องนึกถึงเลขยกกำลังฐานสิบคู่กันไปด้วย

สมมุติเลขจำนวนจริงชุดหนึ่งสามารถเขียนความสัมพันธ์ในรูปเลขยกกำลังฐาน 10 ได้ดังสมการ 1

จะพบว่าสมการที่ 2 มีลักษณะเป็นสมการเชิงเส้นที่มีความชันเท่ากับ 1 (กล่าวได้ว่าสมการยกกำลังสามารถเปลี่ยนเป็นสมการเชิงเส้นได้เมื่ออยู่ใน logarithm scale ) เราจะเขียนช่วงของตัวเลขในรูปของเลขยกกำลังฐานสิบที่มีเลขยกกำลังต่างๆกันในสมการที่ 1 และคำนวณค่า  logarithm  ของจำนวนดังกล่าวตามสมการที่ 2 ได้ดังตารางที่ 1 

 

จากตารางที่ 1 เมื่อพิจารณาระยะห่างใน linear scale เมื่อ  N = 1 และ N = 100,000 จะพบว่ามีระยะห่างกันมากถึง 100,000 ช่อง แต่เมื่อพิจารณาในlogarithm  scale พบว่ามีระยะห่างกันเพียง 5ช่อง ซึ่งทำให้สามารถแสดงข้อมูลทั้งสองได้อย่างชัดเจน

พึงสังเกตว่าระยะห่าง 5 ช่องใน logarithm scale (ฐานสิบ) จะมีค่าต่างกันถึง 10⁵ ใน linear scale

เราสามารถสรุปได้ว่า หากข้อมูลตัวเลขที่เรามีอยู่มีความแตกต่างกันอย่างมากแล้ว แต่หากปรับให้อยู่ใน logarithm scale แล้ว ตัวเลขจะถูกบีบอัดให้มีความแตกต่างกันลดลงทำให้ข้อมูลที่มีอยู่มีค่าใกล้เคียงกันจนสามารถวิเคราะห์หรือนำเสนอข้อมูลนั้นได้อย่างละเอียดและชัดเจน

เอาหล่ะครับในขณะนี้ผมจะขอนำเสนอวิธีการอ่านข้อมูลใน log scale และแสดงให้เห็นถึงการบีบอัดข้อมูลกันครับ ดังแสดงในภาพ

เปรียบเทียบ Linear & logarithm scale

คราวนี้เราจะนำปัญหาของการนำเสนอยอดขายของคุณสมชายจากบทความตอนที่ 1 มาวิเคราะห์กันอีกครั้งหนึ่งครับ โดยคราวนี้เราจะปรับสเกลแกนตั้งซึ่งแสดงยอดขายให้เป็น log scale ฐาน 10 โดยสามารถทำได้ดังนี้

1.                ให้นำเมาส์ไปคลิก เลือกเส้นสเกลในแนวดิ่งของกราฟยอดขาย

2.                คลิกเมาส์ขวา และเลือกเมนู จัดรูปแบบแกน... excel จะแสดงไดอะล็อก จัดรูปแบบแกน ดังแสดงในภาพ

3.                ใน list ด้านซ้ายให้เลือก ตัวเลือกแกน

4.                ในตัวเลือกด้านขวาให้คลิกเลือก มาตราส่วนลอการิทึม โดยกำหนด ฐาน เป็น 10 ดังแสดงในภาพ

5.                ในหัวข้อ แกนนอนตัดที่ ให้เลือก ค่าแกน และป้อนค่า  0.01 ( ต้องป้อนค่าที่มากกว่า 0 เนื่องจาก log 0 ไม่นิยาม)

6.                คลิกตกลง จะได้กราฟแสดงยอดขายใน scale log

 

 

ขั้นตอนที่ 2

 

 

ขั้นตอนที่ 4

 

 

เปรียบเทียบกราฟ Linear & Logarithm Scale จาก Excel

 

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

 

บทความที่เกี่ยวข้อง

 

ประยุกต์การวิเคราะห์กราฟแนวโน้มแบบยกกำลังด้วยกราฟเส้นตรง

ประยุกต์การสร้างกราฟเส้นตรงกับกับกราฟแนวโน้มการเพิ่มสู่จุดอิ่มตัว



 

 

 

 

 

 

การใช้ excel : นำเสนอข้อมูลด้วยกราฟ Log ตอนที่ 1 (How to present data by logarithm scale by excel-1)

ในบทความนี้ขอนำเสนอเทคนิคการนำเสนอข้อมูลด้วย Excel  โดยใช้เทคนิคของ logarithm graph ซึ่งเป็นเทคนิคที่ใช้แก้ปัญหาข้อมูลที่นำเสนอมีค่าที่แตกต่างกันอย่างมาก เช่น มีค่าตั้งแต่ 100 – 1,000,000 ในกราฟเดียวกัน หรือยอดขายของเซลล์แต่ละคน ยอดขายสินค้า by item ในแต่ละเดือนเป็นต้น  ก่อนอื่นขอเริ่มต้นให้ท่านผู้อ่านรู้จัก scaling ก่อนครับ Scaling เป็นการเพิ่มหรือลดขนาดของตัวเลขในกลุ่มข้อมูลด้วยค่าคงที่ค่าหนึ่งๆ โดยส่วนใหญ่เราจะรู้จัก scaling แบบ linear (linear scale) ซึ่งสามารถเขียนสมการแสดงความสัมพันธ์ได้ดังนี้

y = ax  

เมื่อ y คือค่าหลังทำการ scale

x คือค่าก่อนการ scale

a คือค่าคงที่ในการ scale  มีค่ามากกว่า 0

จากสมการจะพบว่าความสัมพันธ์ระหว่าง x และ y เป็นแบบ linear  โดยหาก a มีค่ามากกว่า 1 จะเป็นการขยายค่า หรือ scale up และหาก x น้อยกว่า 1 จะเป็นการ scale down ตัวอย่างการทำ linear scale มีมากมายที่จะประยุกต์ใช้ ยกตัวอย่างเช่น คุณสมชายมีข้อมูลยอดขายสินค้า 5 item ซึ่งมีข้อมูลดังนี้

จากข้อมูลหากนำเสนอเป็นตัวเลขดังกล่าวคงสร้างความลำบากในการอ่านหรือนำเสนอเป็นอย่างมาก และในความเป็นจริงผู้บริหารอาจต้องการทราบตัวเลขโดยประมาณก็เป็นได้ ดังนั้น เราจะทำการปรับ scale จากมูลค่า บาท เป็นมูลค่า ล้านบาท ซึ่งทำได้โดยการหารยอดขายด้วยค่า 1,000,000 ดังนั้นจากสมการ  linear scale เราสามารถเขียนได้ดังนี้

Y = (1/1,000,000)*x

ซึ่ง y เป็นยอดขายหลังทำการ scale แล้ว หน่วยเป็น ล้านบาท

x เป็นยอดขายที่แท้จริง หน่วยเป็นล้านบาท

a = 1/1,000,000

จากสมการ linear scale  ท่านผู้อ่านจะเห็นว่า a น้อยกว่า 1 ดังนั้นจึงเป็นการ scale down โดยหลังการ scaling แล้วจะได้ ยอดขายใหม่ในหน่วย ล้านบาท และเมื่อใช้สูตร excel ปรับรูปแบบตัวเลขเป็นทศนิยม 2 ตำแหน่ง ข้อมูลการนำเสนอยอดขายจะแสดงได้ดังตาราง

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

เอาหล่ะครับมาถึงขณะนี้สมมุติว่า คุณสมชายมีสินค้าใหม่เพิ่มขึ้นมาอีก 1 item ยอดขาย 80,000 บาท และต้องการนำเสนอผู้บริหารด้วยเนื่องจากเป็นสินค้าที่กำลังมาแรงหรือมี potential ในตลาดขณะนี้  เรามาดูกราฟหลังการเพิ่มสินค้า item นี้กันครับ

จากกราฟเป็นยังบ้างครับ ข้อมูล item 6 ไม่มีความโดดเด่นและไม่น่าสนใจเลย ซึ่งขัดแย้งกับเทคนิคการนำเสนอที่ต้องมีความน่าสนใจและชัดเจน ครับ เราจะแก้ปัญหานี้ได้ โดยการใช้ scaling แบบ logarithm ติดตามกันในบทความถัดไปครับ ว่า scaling แบบ logarithm คืออะไร

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

การใช้ excel : ประยุกต์การสร้างกราฟเส้นตรงกับกราฟแน้วโน้มการเพิ่มสู่จุดอิ่มตัว

การใช้ excel ในบทความนี้ขอเพิ่มเติมการประยุกต์การสร้างกราฟเส้นตรงเข้ากับข้อมูลที่มีลักษณะการกระจายที่มีอัตราการเพิ่มเข้าสู่จุดอิ่มตัว (Saturation-growth rate) การนำไปใช้ประโยชน์ เช่นการวิเคราะห์หาจุดอิ่มตัวของปริมาณใดๆที่เราสนใจจะวิเคราะห์เพื่อกำหนดแผนการดำเนินการต่อไปเช่น จุดอิ่มตัวหรืออัตราการเติบโตของตลาด (Market Saturate)  จุดอิ่มตัวและอัตราการเติบโตของผลิตภัณฑ์ จุดอิ่มตัวและอัตราการเติบโตทางเศรษฐกิจ (economic growth rate) เป็นต้น

ภาพที่ 1 เส้นกราฟสีน้ำเงินแสดงขนาดของตลาดสินค้าชนิดหนึ่งที่มีลักษณะเข้าสู่จุดอิ่มตัว

Ref. http://en.wikipedia.org/wiki/Market_saturation

หลังจากที่ได้อธิบาย การประยุกต์กราฟเส้นตรงกับข้อมูลที่กระจายแบบกำลังไปแล้ว แนวคิดการแก้ปัญหายังเหมือนเดิม เราจะเริ่มจาก สมการที่ใช้อธิบาย ลักษณะการกระจายข้อมูลแบบที่มีอัตราการเพิ่มเข้าสู่จุดอิ่มตัว เราจะเรียกสมการนี้ว่า Saturation-growth rate Equation โดยมีรูปแบบดังนี้



จากรูปแบบสมการหากทำการคูณไขว้ และจัดรูปสมการใหม่จะได้ดังสมการที่ 1

หากพิจารณาสมการที่ 1 เทียบกับรูปแบบของสมการเชิงเส้น y = mx + c เราจะได้ว่า

สมการแรก หากเราพล็อตกราฟ แกนตั้งเป็นค่า 1/y แกนนอนเป็นค่า 1/x ความชันของชุดข้อมูล m = b/a จุดตัดแกนตั้ง c = 1/a ดังนั้น a = 1/c  ขณะที่ b = m*a

 

การเตรียมข้อมูลใน excel เพื่อคำนวณหาค่าคงที่ a และ b ในสมการการเพิ่มเข้าสุ่จุดอิ่มตัว

สมมุติว่าเรามีข้อมูล x และ y จากข้อมูลจริงและได้บันทึกข้อมูลลง excel ในหลัก A และ B ดังแสดงในภาพที่ 2

 

ภาพที่ 2 ตัวอย่างข้อมูลที่กระจายแบบเพิ่มเข้าสู่จุดอิ่มตัว

 

จากลักษณะของสมการเพิ่มเข้าสุ่จุดอิ่มตัวที่เปลี่ยนเป็นสมการเส้นตรงตามสมการที่ 1 จะต้องแปลงค่า y ให้เป็นค่า 1/y และเก็บค่าไว้ในหลัก D เขียนสูตร excel ได้เป็น D4= 1/B4 และแปลงค่า x ให้เป็นค่า 1/x และเก็บค่าไว้ในหลัก C  เขียนสูตร excel ได้เป็น C4= 1/A4

 

ใช้สูตร excel : SLOPE เพื่อหาความชันและจุดตัดของข้อมูล (1/x , 1/y) ใน D2 และ E2 ได้ดังนี้

สูตร excel

=SLOPE($D$4:$D$13,$C$4:$C$13)

=INTERCEPT($D$4:$D$13,$C$4:$C$13)

เราสามารถคำนวณค่าคงที่ a และ b ใน F2 และ G2 โดยเขียนสูตร excel ได้ดังนี้

= 1/E2

= F2*D2

เราสามารถคำนวณค่า y ของฟังก์ชันยกกำลังได้โดยใช้ค่าคงที่ a และ b ที่คำนวณได้ โดยคำนวณและเก็บไว้ในหลัก D ดังนี้

สูตร excel : D4 = $F$2*$A4/($G$2+$A4) (copy สูตร excel ไปยังแถวถัดไปได้)

 

 

ภาพที่ 3 เปรียบเทียบเส้นแนวโน้มและข้อมูลจริง

 

สูตร excel สำหรับคำนวณค่าตัวแปรตาม (y) ในสมการการเพิ่มเข้าสู่จุดอิ่มตัว

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

 

เมื่อพิจารณาสมการการเพิ่มเข้าสู่จุดอิ่มตัวซึ่งถูกเปลี่ยนอยู่ในรูปสมการเชิงเส้นไปแล้วนั้นจะพบว่า หากใช้สูตร excel : TREND กับข้อมูลดังกล่าว สูตร excel : TREND จะคืนค่า 1/y กลับมา ดังนั้นหากต้องการทราบค่า y ในสมการการเพิ่มเข้าสู่จุดอิ่มตัวจะต้องเขียนสูตร excel ดังนี้ สมมุติให้ เซลล์ C1 เป็นจุด x ที่ต้องการทราบค่า y และค่า y จะถูกคำนวณและแสดงในเซลล์ C2 เราจะเขียนสูตร excel ได้ดังนี้

C2 = 1/(TREND($D$4:$D$13,$C$4:$C$13,1/C1))

ผลคำนวณแสดงได้ดังภาพที่ 4

 

 

ภาพที่ 4 การใช้สูตร excel : TREND

 

ข้อควรระวังสำหรับการประยุกต์สมการเส้นตรงกับ สมการการเพิ่มเข้าสู่จุดอิ่มตัว คือค่า x และ y ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า 1/0 ได้ และจากสมการที่ 1 เราจะพบว่าหากป้อนค่า x = 0 เข้าไป เราจะคำนวณค่า y ได้เท่ากับ 0 ฉะนั้นจึงควรเขียนสูตร excel เพื่อป้องกันการแสดงความผิดพลาดในการคำนวณสูตร excel :TREND ในเซลล์ C2 โดยปรับสูตรใหม่ได้ดังนี้

C2 = IF(C1=0,0,1/(TREND($D$4:$D$13,$C$4:$C$13,1/$C$1))

 

เพิ่มเติม

เราสามารถคำนวณอัตราการเพิ่มหรือเติบโต (growth-rate) ได้โดยการ differential สมการที่ 1 เทียบกับ x จะได้สมการคำนวณอัตราการเติบโตได้ดังสมการ

 

สมมุติกำหนดให้ growth-rate ถูกคำนวณไว้ในเซลล์ H2 โดยสามารถเขียนสูตร excel ได้ดังนี้

 

H2 = $F$2*$G$2/($G$2+$C$1)^2

 

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

การใช้ excel สร้างกราฟแบบไดนามิกส์ (How to create dynamics chart by excel)

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

ภาพที่ 1 ตัวอย่างข้อมูลยอดขาย

ขั้นตอนการสร้างกราฟแบบไดนามิกส์จะเริ่มดังนี้ โดยสมมุติให้มีผลกับ Worksheet ชื่อ Sheet1 เท่านั้น

1. กำหนดชื่อข้อมูลแบบพลวัตรให้กับข้อมูลแกน x โดยใช้ชื่อ xValue  (ใช้เมนู สูตร เลือก กำหนดชื่อและเลือกขอบเขตเป็น Sheet1 )

สูตร excel : OFFSET($B$3,1,0,COUNTA($B:$B)-1,1) 

2. กำหนดชื่อข้อมูลแบบพลวัตรให้กับข้อมูลแกน y โดยใช้ชื่อ yValue  (ใช้เมนู สูตร เลือก กำหนดชื่อและเลือกขอบเขตเป็น Sheet1 )

สูตร excel : OFFSET($B$3,1,1,COUNTA($B:$B)-1,1) 

3. สร้างกราฟแท่ง โดยไปที่เมนู แทรก เลือกแผนภูมิคอลัมน์แบบกลุ่ม จะปรากฎพื้นที่พล๊อตกราฟใน Sheet1 

4. ทำการคลิกขวาในพื้นทีีแผนภูมิและเลือก เลือกข้อมูล... จะปรากฎหน้าต่างเลือกแหล่งข้อมูล

5. คลิกปุ่มเพิ่มในหน้าต่างดังกล่าวเพื่อ เพิ่มข้อมูลแกน y จะปรากฎหน้าต่าง แก้ไขชุดข้อมูล ให้พิมพ์สูตร excel ลงในช่องของค่าชุดข้อมูล ดังแสดงในภาพที่ 2 และคลิกปุ่ม ตกลง เพื่อปิดหน้าต่างดังกล่าว

6. คลิกปุ่ม แก้ไข ในช่องรายการ ป้ายชื่อแกน (ประเภท) แนวนอน จะปรากฎหน้าต่าง ป้ายชื่อแกน ให้พิมพ์สูตร excel ลงในช่อง ช่วงป้ายชื่อแกน ดังแสดงในภาพที่ 3 และคลิกปุ่ม ตกลง เพื่อปิดหน้าต่าง

7. คลิกปุ่ม ตกลง เพื่อปิดหน้าต่าง เลือกแหล่งข้อมูล จะได้กราฟแท่ง แสดงยอดขาย 3 เดือนแรก ดังภาพที่ 4

ภาพที่ 2 การกำหนดสูตร excel แสดงค่า y

ภาพที่ 3 การกำหนดสูตร excel แสดงป้ายชื่อแกน x

ภาพที่ 4 กราฟแท่งแสดงยอดขาย 3 เดือน

หากไม่มีข้อผิดพลาดอะไร ขณะนี้กราฟที่สร้างใน excel ตามขั้นตอนดังกล่าวเป็นกราฟแบบไดนามิกส์แล้ว ให้ท่านผู้อ่านทดลองเพิ่มข้อมูลยอดขายลงไปในเซลล์ B7 C7 จะพบว่ากราฟของเราจะทำการเพิ่มข้อมูลให้โดยอัตโนมัติ ดังแสดงในภาพที่ 5

ภาพที่ 5 กราฟหลังจากมีการเพิ่มเติมข้อมูล

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