วิธีดำเนินการถดถอยเชิงเส้นอย่างง่ายใน excel

การถดถอยเชิงเส้นอย่างง่าย เป็นวิธีการที่เราสามารถใช้เพื่อทำความเข้าใจความสัมพันธ์ระหว่างตัวแปรอธิบาย x และตัวแปรตอบสนอง y

บทช่วยสอนนี้จะอธิบายวิธีการถดถอยเชิงเส้นอย่างง่ายใน Excel

ตัวอย่าง: การถดถอยเชิงเส้นอย่างง่ายใน Excel

สมมติว่าเราต้องการเข้าใจความสัมพันธ์ระหว่างจำนวนชั่วโมงที่นักเรียนเรียนเพื่อสอบกับเกรดที่ได้รับจากการสอบ

ในการสำรวจความสัมพันธ์นี้ เราสามารถทำการถดถอยเชิงเส้นอย่างง่ายโดยใช้ ชั่วโมงที่ศึกษา เป็นตัวแปรอธิบาย และ ใช้คะแนนสอบ เป็นตัวแปรตอบสนอง

ทำตามขั้นตอนต่อไปนี้ใน Excel เพื่อดำเนินการถดถอยเชิงเส้นอย่างง่าย

ขั้นตอนที่ 1: ป้อนข้อมูล

ป้อนข้อมูลต่อไปนี้สำหรับจำนวนชั่วโมงเรียนและคะแนนสอบที่ได้รับสำหรับนักเรียน 20 คน:

ขั้นตอนที่ 2: แสดงภาพข้อมูล

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

ไฮไลต์ข้อมูลในคอลัมน์ A และ B ตาม Ribbon ด้านบนของ Excel ให้ไปที่แท็บ แทรก ในกลุ่ม กราฟิก คลิก แทรกกระจาย (X, Y) และคลิกตัวเลือกแรกที่มี ข้อความกระจาย สิ่งนี้จะสร้างพอยต์คลาวด์ต่อไปนี้โดยอัตโนมัติ:

จำนวนชั่วโมงที่เรียนจะแสดงบนแกน x และผลการสอบจะแสดงบนแกน y เราจะเห็นได้ว่ามีความสัมพันธ์เชิงเส้นตรงระหว่างตัวแปรทั้งสอง: จำนวนชั่วโมงเรียนที่มากขึ้นสัมพันธ์กับคะแนนสอบที่สูงขึ้น

ในการหาปริมาณความสัมพันธ์ระหว่างตัวแปรทั้งสองนี้ เราสามารถทำการถดถอยเชิงเส้นอย่างง่ายได้

ขั้นตอนที่ 3: ทำการถดถอยเชิงเส้นอย่างง่าย

บน Ribbon ด้านบนของ Excel ให้ไปที่แท็บ Data แล้วคลิก Data Analysis หากคุณไม่เห็นตัวเลือกนี้ คุณต้อง ติดตั้งซอฟต์แวร์ Analysis ToolPak ฟรี ก่อน

เมื่อคุณคลิกที่ การวิเคราะห์ข้อมูล หน้าต่างใหม่จะปรากฏขึ้น เลือก การถดถอย แล้วคลิกตกลง

สำหรับ Input Y Range ให้กรอกอาร์เรย์ของค่าสำหรับตัวแปรตอบสนอง สำหรับ Input X Range ให้กรอกอาร์เรย์ของค่าสำหรับตัวแปรอธิบาย

ทำเครื่องหมายที่ช่องถัดจาก ป้ายกำกับ เพื่อแจ้งให้ Excel ทราบว่าเราได้รวมชื่อตัวแปรไว้ในช่วงอินพุตแล้ว

สำหรับ Output Range ให้เลือกเซลล์ที่คุณต้องการให้เอาต์พุตการถดถอยปรากฏ

จากนั้นคลิก ตกลง

ผลลัพธ์ต่อไปนี้จะปรากฏขึ้นโดยอัตโนมัติ:

ขั้นตอนที่ 4: ตีความผลลัพธ์

ต่อไปนี้คือวิธีตีความตัวเลขที่เกี่ยวข้องมากที่สุดในผลลัพธ์:

อาร์ สแควร์: 0.7273 . นี่เรียกว่าสัมประสิทธิ์การตัดสินใจ เป็นสัดส่วนของความแปรปรวนในตัวแปรตอบสนองที่สามารถอธิบายได้ด้วยตัวแปรอธิบาย ในตัวอย่างนี้ 72.73% ของความแปรผันของคะแนนสอบอธิบายได้ด้วยจำนวนชั่วโมงที่เรียน

ข้อผิดพลาดมาตรฐาน: 5.2805 นี่คือระยะห่างเฉลี่ยระหว่างค่าที่สังเกตได้กับเส้นถดถอย ในตัวอย่างนี้ ค่าที่สังเกตได้เบี่ยงเบนโดยเฉลี่ย 5.2805 หน่วยจากเส้นถดถอย

ฉ: 47.9952 . นี่คือสถิติ F โดยรวมสำหรับแบบจำลองการถดถอย ซึ่งคำนวณเป็น MS การถดถอย/MS คงเหลือ

ความหมาย F: 0.0000 นี่คือค่า p ที่เกี่ยวข้องกับสถิติ F โดยรวม ข้อมูลนี้บอกเราว่าแบบจำลองการถดถอยมีนัยสำคัญทางสถิติหรือไม่ กล่าวอีกนัยหนึ่ง มันบอกเราว่าตัวแปรอธิบายมีความสัมพันธ์ที่มีนัยสำคัญทางสถิติกับตัวแปรตอบสนองหรือไม่ ในกรณีนี้ ค่า p น้อยกว่า 0.05 แสดงว่ามีความสัมพันธ์ที่มีนัยสำคัญทางสถิติระหว่างชั่วโมงเรียนกับเกรดที่ได้รับจากการสอบ

ค่าสัมประสิทธิ์: ค่าสัมประสิทธิ์ให้ตัวเลขที่จำเป็นในการเขียนสมการการถดถอยโดยประมาณ ในตัวอย่างนี้ สมการการถดถอยโดยประมาณคือ:

คะแนนสอบ = 67.16 + 5.2503*(ชั่วโมง)

เราตีความค่าสัมประสิทธิ์ชั่วโมงว่า ในแต่ละชั่วโมงที่เรียนเพิ่มเติม คะแนนสอบควรเพิ่มขึ้นโดยเฉลี่ย 5.2503 เรา ตีความค่าสัมประสิทธิ์ของการสกัดกั้นว่าคะแนนสอบที่คาดหวังสำหรับนักเรียนที่เรียนเป็นศูนย์ชั่วโมงคือ 67.16

เราสามารถใช้สมการการถดถอยโดยประมาณนี้เพื่อคำนวณคะแนนสอบที่คาดหวังสำหรับนักเรียน โดยพิจารณาจากจำนวนชั่วโมงเรียน

เช่น นักเรียนที่เรียน 3 ชั่วโมง ควรได้คะแนนสอบ 82.91 :

คะแนนสอบ = 67.16 + 5.2503*(3) = 82.91

แหล่งข้อมูลเพิ่มเติม

บทช่วยสอนต่อไปนี้อธิบายวิธีการทำงานทั่วไปอื่นๆ ใน Excel:

วิธีการสร้างพล็อตที่เหลือใน Excel
วิธีสร้างช่วงการทำนายใน Excel
วิธีสร้างพล็อต QQ ใน Excel