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