บทความการใช้ Microsoft Excel ในบทนี้จะขอเสนอการประยุกต์ใช้สูตร excel
สำหรับวิเคราะห์การถดถอยแบบเชิงเส้น
โดยนำผลวิเคราะห์ค่าทางสถิติมาพิจารณาแนวโน้มความเป็นเชิงเส้น โดยสูตร excel
ที่จะนำมาใช้คือสูตร
LINEST ก่อนอื่นมารู้จักสมการเส้นตรงกันก่อนครับ ซึ่งสามารถอธิบายได้ดังสมการ
Y = mX + c
โดยที่ m คือ ความชันของเส้นตรง c คือ จุดตัดของเส้นตรงบนแกน Y เมื่อ X
ตัวแปรอิสระ
การใช้สูตร excel : SLOPE
และ Excel : INTERCEPT สำหรับหาค่าความชันและจุดตัดแกน ได้เคยนำเสนอมาแล้วในบทความ
เรื่อง การใช้ Excel สร้างกราฟเส้นตรง ซึ่งการใช้สูตร Excel ดังกล่าวยังขาดผลวิเคราะห์ทางสถิติที่จะนำมาใช้วัดความแม่นยำของการใช้สมการเส้นตรงในการทำนายแนวโน้มจากข้อมูลที่ได้จากการทดลอง
ดังนั้นสูตร Excel : LINEST ซึ่งใช้การวิเคราะห์การถดถอยเชิงเส้นจึงถูกนำมาใช้วิเคราะห์หาค่าความชันและจุดตัดแกน
Y เนื่องจากสูตร Excel : LINEST สามารถคืนค่าทางสถิติที่ได้จากการวิเคราะห์การถดถอยเชิงเส้นมาให้ผู้ใช้อย่างเราพิจารณาความแม่นยำของแบบจำลองเชิงเส้นที่จะอธิบายแนวโน้มของข้อมูลที่วัดได้จริง
ดังตัวอย่างในภาพที่ 1
ภาพที่ 1 ชุดข้อมูล
รูปแบบการใช้สูตร Linest มีดังนี้
LINEST(known_y's, [known_x's],
[const], [stats]) สูตรนี้จะคืนค่ากลับเป็น
Array
เมื่อ
known_y's
คือข้อมูลตัวแปรตามที่ทราบ
[known_x's] คือ
ข้อมูลอาเรย์ x ตัวแปรอิสระ
หากไม่ใส่ Excel จะกำหนดให้เป็นค่า
1,2,3,… จนเท่ากับจำนวนของตัวแปรตาม
[const] คือ
ตรรกะที่ใช้ระบุให้ Excel คำนวณค่าจุดตัดแกน (c )หรือไม่ หากเป็น
TRUE จะคำนวณตามปกติ หากเป็น FALSE จะกำหนดให้ c = 0
[stats] คือ
ตรรกะที่ใช้ระบุให้ Excel ส่งกลับผลคำนวณค่าทางสถิติหรือไม่ โดย หากเป็น TRUE
จะส่งกลับหากเป็น FALSE จะไม่ส่งกลับ
เนื่องจากสูตร Excel : Linest จะคืนค่าผลการคำนวณในรูปแบบ Array ดังนั้นจะต้องใช้สูตร
Excel:Index ช่วยเพื่อตำแหน่งของตัวแปรที่ต้องการแสดงผล
ให้พิมพ์ข้อความใน E2 E3 และ E4 ดังนี้ m , c , r^2
พิมพ์สูตร excel ใน F2 เป็น =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,1) จากนั้นกดปุ่ม Ctrl +
Shift+ENTER เพื่อเป็นการคำนวณแบบ
Array
พิมพ์สูตร excel ใน F3 เป็น =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),1,2) จากนั้นกดปุ่ม Ctrl +
Shift+ENTER เพื่อเป็นการคำนวณแบบ
Array
พิมพ์สูตร excel ใน F4 เป็น =INDEX(LINEST(C$2:C$9,B$2:B$9,TRUE,TRUE),3,1) จากนั้นกดปุ่ม Ctrl +
Shift+ENTER เพื่อเป็นการคำนวณแบบ
Array
จากผลการใช้สูตร Excel :Linest จะได้แบบจำลองสมการเชิงเส้นที่มี
m = 2.037, c = -0.096, r^2 = 0.99 ค่า r^2 เป็นค่าทางสถิติที่ได้จากการวิเคราะห์การถดถอยของ
Excel โดยพบว่าเข้าใกล้ 1 มากๆ แสดงถึงแนวโน้มความสัมพันธ์ระหว่างตัวแปรตาม y
และตัวแปรอิสระ
x ของข้อมูลชุดนี้มีความเป็นเชิงเส้นมาก ดังแสดงในภาพที่ 2 จากแบบจำลองสมการเชิงเส้นที่วิเคราะห์ได้ทำให้ผู้ใช้สามารถประมาณค่าภายในช่วง
(Interpolation) ของชุดข้อมูลดังกล่าวได้เมื่อทราบตัวแปรอิสระ
ภาพที่ 2 ผลการใช้สูตร Excel
ซึ่งจากผลการประยุกต์ใช้สูตร Excel : Linest จะทำให้ผู้ใช้มีความมั่นใจที่จะเลือกใช้แบบจำลองสมการเชิงเส้นสำหรับอธิบายความสัมพันธ์ของข้อมูลที่มีอยู่
ในขณะเดียวกันหากค่า r^2 ของแบบจำลองสมการเชิงเส้น มีค่าน้อยกว่า 1 มากๆ
จะแสดงให้ทราบว่าความสัมพันธ์ของชุดข้อมูลดังกล่าวไม่เป็นเชิงเส้น อาจจะเป็นแบบพหุนาม
หรือ exponential ก็เป็นได้ ในทำนองเดียวกันเราสามารถประยุกต์ใช้สูตร Excel :
Linest กับแบบจำลองเชิงเส้นแบบหลายเชิง แบบจำลองพหุนาม (โพลิโนเมียล) รวมถึง สมการแบบยกกำลังและแบบ log ได้ดังจะนำเสนอในครั้งต่อๆไป
ไม่มีความคิดเห็น:
แสดงความคิดเห็น