การใช้ 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 คำนวณดอกเบี้ยทบต้น (Compound interest)

สวัสดีครับ blog การใช้ excel ขอนำเสนอวิธีการคำนวณดอกเบี้ยทบต้น หรือที่เรียกว่า Compound interest เพื่อให้ท่านผู้อ่านที่กำลังเป็นนักลงทุนหรือกำลังจะกู้เงินได้เป็นแนวทางในการตัดสินใจครับ ก่อนอื่นมารู้จัก ดอกเบี้ยทบต้นก่อน ดอกเบี้ยทบต้นคือ การคำนวณดอกเบี้ย โดยคิดจากจำนวนเงินต้นที่สูงขึ้นเรื่อยๆ จากการนำดอกเบี้ยในแต่ละงวดที่คิดดอกเบี้ยมารวมเป็นเงินต้นในงวดต่อๆไป ดังนั้นดอกเบี้ยในงวดถัดๆไปจะเพิ่มขึ้นเรื่อยๆเนื่องจาก เงินต้นถูกเพิ่มขึ้นเรื่อยๆจะดอกเบี้ยสะสมในแต่ละงวด เรามาเปรียบเทียบการคิดดอกเบี้ยในเงื่อนไขคาบเวลาการคิดดอกเบี้ยที่แตกต่างกัน โดยสมมุติให้ เงินกู้จำนวนหนึ่งคิดดอกเบี้ยเป็นรายปีในอัตราร้อยละ 6 ในขณะที่เงินกู้จำนวนเดียวกันคิดดอกเบี้ยทุกๆ 4 เดือนในอัตราร้อยละ 6 ต่อปี เราจะสร้างตารางคำนวณใน excel   โดยกำหนดให้ หลัก B เป็นอัตราดอกเบี้ยต่อปี ในหลัก C ของเงินกู้ 2 กำหนดรายการคาบเวลาการคิดดอกเบี้ยให้เลือก มีรายการดังนี้ รายปี ทุกครึ่งปี ทุก 4 เดือน ทุก 3 เดือน ทุก 2 เดือน และรายเดือน โดยใช้สูตร excel if เพื่อแสดงจำนวนครั้งในการคิดดอกเบี้ยต่อปีให้เป็น 1, 2, 3, 4,6 และ 12 เดือนในเซลล์ D3 ตามลำดับ ในเซลล์ E3 คำนวณดอกเบี้ยที่แท้จริงต่อปี โดยใช้สูตร excel เป็น E3 =(1+B3/D3)^D3 - 1 ดังแสดงในภาพที่ 1

 

 

ภาพที่ 1 ตารางคำนวณดอกเบี้ยทบต้นใน excel

 

ให้ท่านทดลองปรับเปลี่ยน อัตราดอกเบี้ยหรือ ลักษณะการคิดดอกเบี้ยในตาราง excel ท่านจะพบว่า การลงทุนหรือการกู้เงินที่มีการคิดดอกเบี้ยถี่ๆ อัตราดอกเบี้ยที่แท้จริงต่อปีจะสูงกว่า ซึ่งเราคงต้องการวิธีคิดดอกเบี้ยถี่ๆสำหรับการลงทุน แต่คงไม่ต้องการสำหรับการกู้เงินหรือการเป็นหนี้ ตัวอย่างที่ชัดเจนที่สุดคือ ดอกเบี้ยบัตรเครดิต ซึ่งคิดเป็นรายวันนั่นเอง หากเพิ่มความถี่ในการคิดดอกเบี้ยมากขึ้นเรื่อยๆ ดอกเบี้ยที่แท้จริงจะเข้าใกล้ค่าๆหนึ่ง เราจะเรียกการคิดดอกเบี้ยลักษณะนี้ว่า ดอกเบี้ยทบต้นต่อเนื่อง จากตัวอย่างอัตราดอกเบี้ยร้อยละ 6 ต่อปี หากกำหนดการคิดดอกเบี้ยเป็นรายวันจะพบว่า อัตราดอกเบี้ยที่แท้จริงจะเท่ากับร้อยละ 6.18 หากเพิ่มความถี่เป็น 10,000 จะพบว่า อัตราดอกเบี้ยที่แท้จริงจะเท่ากับร้อยละ 6.18 เช่นกัน ท่านสามารถเพิ่มรายการคิดดอกเบี้ยรายวันลงในรายการได้ โดยกำหนดจำนวนครั้งในการคิดต่อปีเท่ากับ 365 ดังนั้นจึงกล่าวได้ว่าดอกเบี้ยทบต้นต่อเนื่องมีค่าเท่ากับร้อยละ 6.18 กรณีดอกเบี้ยบัตรเครดิต ซึ่งตามกฏหมายกำหนดให้ไม่เกินร้อยละ 28 ต่อปี จะพบว่าอัตราดอกเบี้ยที่แท้จริงต่อปีจะเท่ากับ ร้อยละ 32.30 ครับ น่าเป็นห่วงคนเป็นหนี้บัตรเครดิต หรือคนคิดจะกู้เงินบัตรเครดิตจริงๆ ก็หวังว่าตาราง excel และสูตร excel ที่พัมนาขึ้นคงช่วยท่านผู้อ่านประกอบการตัดสินใจในการลงทุนหรือกู้เงินได้ครับ