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