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

 

การใช้ excel ช่วยคำนวณดอกเบี้ยเงินฝากประจำ

การใช้ excel ในบทความนี้ขอนำเสนอ การใช้ excel คำนวณดอกเบี้ยเงินฝากประจำ โดยเงินฝากประจำที่จะนำเสนอในบทความนี้จะหมายถึง การฝากเงินเป็นประจำทุกๆเดือนติดต่อกันในระยะเวลาที่กำหนดและดอกเบี้ยที่กำหนด ปัญหาของผู้ที่ต้องการฝากเงินในลักษณะนี้คือ สุดท้ายแล้วจะได้ดอกเบี้ยเท่าไหร่ เงินฝากประจำแบบนี้เป็นการลงทุนการเงินที่มีความเสี่ยงต่ำ โดยสถาบันการเงินในปัจจุบันให้ดอกเบี้ยค่อนข้างสูง(หากเทียบกับเงินฝากลักษณะอื่นๆ) และที่สำคัญดอกเบี้ยของการฝากเงินลักษณะนี้ไม่ถูกหักภาษีเงินได้ร้อยละ 15 ครับ ในบทความนี้จะนำเสนอการสร้างตารางใน excel เพื่อคิดดอกเบี้ยเงินฝาก เริ่มต้นจากแนวคิดการคิดดอกเบี้ยเงินฝากของเงินฝากประจำดังกล่าว โดยมีแนวคิดดังนี้ 

ดอกเบี้ยที่ได้รับในแต่ละเดือน = เงินต้น x อัตราดอกเบี้ย(ร้อยละต่อปี)xจำนวนวันที่คิดดอกเบี้ย/365

โดยส่วนใหญ่เมื่อครบกำหนดฝากธนาคารจะคิดโบนัสดอกเบี้ยอีกร้อยละ 2.5 ของดอกเบี้ยที่ได้รับ 

เงินต้นที่ใช้คำนวณในแต่ละเดือนจะเพิ่มขึ้นตามเงินฝากที่สะสมในแต่ละเดือน ตาราง excel สำหรับคำนวณดอกเบี้ยแสดงได้ดังภาพที่ 1 โดยสมมุติให้เป็นเงินฝากแบบประจำ 12 เดือน อัตราดอกเบี้ยร้อยละ 3.5 โดยเริ่มเปิดบัญชีเงินฝากวันที่ 1 มค 2555

ภาพที่ 1 ตาราง excel คำนวณดอกเบี้ยเงินฝากประจำ

จากภาพที่ 1 จะขออธิบายสูตร excel และการบันทึกข้อมูลในแต่ละเซลล์ดังนี้

• ข้อมูลจำนวนงวดเงินฝากในหลัก A และ วันเดือนปีที่ฝากเงินในแต่ละเดือน ผู้ใช้ excel ต้องบันทึกทุกเดือน
• การคำนวณเงินต้นสะสมในงวดที่ 2 ถึง 12 จะเขียนสูตร excel โดยอ้างอิงเงินต้นสะสมในเซลล์ C2
• สูตร excel เงินต้นสะสม C3 = $C$2*A3 (copy สูตร excel ลงไปในแถวด้านล่างต่อไป)
• ข้อมูลอัตราดอกเบี้ยเงินฝาก ผู้ใช้ excel ต้องบันทึกทุกเดือน (ในกรณีที่ดอกเบี้ยเงินฝากในแต่ละเดือนไม่คงที่)
• จำนวนสำหรับคำนวณดอกเบี้ยในแต่ละเดือน จะคำนวณระยะห่างจากวันฝากเงินเดือนนี้และวันฝากเงินเดือนถัดไป เราสามารถเขียนสูตร excel ได้ดังนี้ E2 = B3-B4
• ดอกเบี้ยแต่ละเดือน เขียนสูตร excel ได้ดังนี้ F2 = C2*(E2/365)*E2
• ในเซลล์ B14 จะเป็นวันเดือนปีที่ครบกำหนดการฝากประจำ
• ในเซลล์ F14 เป็นดอกเบี้ยรวมทั้ง 12 เดือน เขียนสูตร excel ได้เป็น F14=SUM(F2:F13)
• ในเซลล์ F15 คำนวณโบนัสดอกเบี้ยร้อยละ 2.5 เขียนสูตร excel ได้เป็น F15 = F14*0.025
• ในเซลล์ F16 คำนวณดอกเบี้ย  เขียนสูตร excel ได้เป็น F16 = F15+F14
• ในเซลล์ F17 คำนวณเงินต้นและดอกเบี้ยที่ได้จาการฝากประจำ เขียนสูตร excel ได้เป็น F17 = F13+F16

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

การใช้ excel : ประยุกต์การสร้างกราฟเส้นตรงกับกราฟแนวโน้มแบบยกกำลัง (1)

การใช้ excel ในบทความนี้ขอนำคำถามจากคุณ kv ซึ่งฝากไว้ที่บทความเรื่อง การใช้ excel สร้างกราฟเส้นตรง (Linear graph) เกี่ยวกับการวิเคราะห์ข้อมูลที่มีแนวโน้มแบบยกกำลังหรือแบบ exponential โดยผมจะนำเสนอการประยุกต์การวิเคราะห์ข้อมูลแบบกราฟเส้นตรงที่ได้นำเสนอมาแล้วในบทความดังกล่าว โดยเริ่มต้นจากการแนะนำรูปแบบฟังก์ชันยกกำลังที่นิยมนำมาใช้วิเคราะห์แนวโน้มข้อมูล ซึ่งในบทความนี้ขอนำเสนอ 3 ฟังก์ชันดังนี้ครับ

แนวคิด

เราจะเปลี่ยนฟังก์ชันยกกำลังเหล่านี้ให้อยู่ในรูปของฟังก์ชันเชิงเส้น y = mx + c และประยุกต์ใช้สูตร excel ที่เกี่ยวกับกราฟเส้นตรงซึ่งได้อธิบายไปแล้วในบทความ การใช้ excel สร้างกราฟเส้นตรง คำนวณหาความชันและจุดตัดแกน y และย้อนกลับไปคำนวณหาค่าคงที่ a , b ในฟังก์ชันยกกำลังดังกล่าว และสุดท้ายจะประยุกต์ใช้สูตร excel : TREND ซึ่งใช้สำหรับสมการเส้นตรง ช่วยคำนวณค่า y ในฟังก์ชันยกกำลังดังกล่าว

การเปลี่ยนฟังก์ชันยกกำลังให้เป็นพังก์ชันเชิงเส้น

ทำการ Take ค่า ln กับฟังก์ชันยกกำลังทั้งสองข้างและจัดรูปแบบสมการใหม่ได้ดังนี้ครับ

เมื่อพิจารณาฟังก์ชันที่ถูกแปลงใหม่กับรูปแบบของสมการเส้นตรง y = mx + c เราจะพบว่า

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

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

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

การเตรียมข้อมูลใน excel เพื่อคำนวณหาค่าคงที่ในฟังก์ชันยกกำลัง

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

ภาพที่ 1 ลักษณะข้อมูล

จากลักษณะของฟังก์ชันยกกำลังที่เปลี่ยนเป็นฟังก์ชันเส้นตรงของสมการที่ 1 จะต้องแปลงค่า y ให้เป็นค่า ln y โดยเก็บไว้ในหลัก C เขียนสูตร excel ได้เป็น C4= ln(B4) 

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

สูตร excel 

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

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

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

=exp(E2)

=exp(D2)

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

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

ภาพที่ 2 ผลการคำนวณหาเส้นแนวโน้ม

สูตร excel สำหรับคำนวณค่าตัวแปรตาม (y) ในฟังก์ชันยกกำลัง

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

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

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,$C$1))

ในกรณีสมการที่ 3 สูตร excel จะเปลี่ยนเป็น

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,ln($C$1)))

 

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

ภาพที่ 3 ผลการประยุกต์ใช้สูตร excel :TREND

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

โดยปรับสูตร excel ใน G2 จาก G2 = exp(D2) เป็น G2 = D2 และ

ปรับสูตร excel ใน D4-D13 เป็น D4 = $F$2*exp($G$2^$A4) (copy สูตร excel ไปยังแถวถัดไปได้)

ในขณะที่รูปแบบการคำนวณใน excel สำหรับสมการที่ 3 นั้นจะปรับเปลี่ยนการคำนวณดังนี้

คำนวณ ln x ในหลัก C

คำนวณ ln y ในหลัก D

คำนวณค่า slope ใน D2 ด้วยสูตร excel : D2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า Intercept ใน E2 ด้วยสูตร excel : E2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า a ใน F2 ด้วยสูตร excel : F2=exp(E2)

คำนวณค่า a ใน G2 ด้วยสูตร excel : G2=D2

ข้อควรระวังสำหรับการประยุกต์สมการเส้นตรงกับ สมการที่ 1-3 คือค่า y ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ ในขณะที่สมการที่ 3 ค่า x ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ 

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