วิธีการดำเนินการถดถอยกำลังสองใน excel

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

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

อย่างไรก็ตาม บางครั้งความสัมพันธ์ระหว่างตัวแปรทำนายและตัวแปรตอบสนองไม่เป็น เชิงเส้น ความสัมพันธ์ไม่เชิงเส้นประเภททั่วไปคือ ความสัมพันธ์กำลังสอง ซึ่งอาจมีลักษณะเหมือน U หรือ U กลับหัวบนกราฟ

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

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

เรามาทบทวนตัวอย่างวิธีการถดถอยกำลังสองใน Excel

การถดถอยกำลังสองใน Excel

สมมติว่าเรามีข้อมูลเกี่ยวกับจำนวนชั่วโมงทำงานต่อสัปดาห์และรายงานระดับความสุข (ในระดับ 0 ถึง 100) สำหรับคน 16 คน:

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

ไฮไลต์เซลล์ A2:B17 จากนั้น คลิกแท็บ INSERT ตามแนว Ribbon ด้านบน จากนั้นคลิก กระจาย ในพื้นที่ แผนภูมิ สิ่งนี้จะสร้างแผนภาพกระจายของข้อมูล:

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

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

ขั้นแรก ไฮไลต์ค่าทั้งหมดในคอลัมน์ B แล้วลากไปที่คอลัมน์ C

ถัดไป ให้พิมพ์สูตร =A2^2 ในเซลล์ B2 สิ่งนี้จะสร้างค่า 36 จากนั้นคลิกมุมขวาล่างของเซลล์ B2 แล้วลากสูตรลงเพื่อเติมเซลล์ที่เหลือในคอลัมน์ B

ต่อไป เราจะใส่แบบจำลองการถดถอยกำลังสองพอดี

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

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

จากนั้นกรอกค่าต่อไปนี้ลงในช่อง Regression ที่ปรากฏขึ้น จากนั้นคลิก ตกลง

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

ต่อไปนี้เป็นวิธีตีความตัวเลขต่างๆ ในเอาต์พุต:

R Square: หรือเรียกอีกอย่างว่า สัมประสิทธิ์การกำหนด ซึ่งเป็นสัดส่วนของความแปรปรวนในตัวแปรตอบสนองที่สามารถอธิบายได้ด้วยตัวแปรทำนาย ในตัวอย่างนี้ ค่า R กำลังสองคือ 0.9092 ซึ่งบ่งชี้ว่า 90.92% ของความแปรปรวนในระดับความสุขที่รายงานสามารถอธิบายได้ด้วยจำนวนชั่วโมงทำงานและจำนวนชั่วโมงทำงาน ^2

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

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

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

_หมวก y = b ₀ + b ₁ x ₁ + b ₂ x ₁ ²

ในตัวอย่างนี้ สมการการถดถอยโดยประมาณคือ:

ระดับความสุขที่ประกาศไว้ = -30.252 + 7.173 (ชั่วโมงทำงาน) -0.106 (ชั่วโมงทำงาน) ²

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

ระดับความสุขที่รายงาน = -30.252 + 7.173(30) -0.106(30) ² = 88.649

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

วิธีเพิ่มเส้นแนวโน้มกำลังสองใน Excel
วิธีอ่านและตีความตารางการถดถอย
ค่า R-กำลังสองที่ดีคืออะไร?
ทำความเข้าใจกับข้อผิดพลาดมาตรฐานของการถดถอย
คำแนะนำง่ายๆ เพื่อทำความเข้าใจการทดสอบ F ของความสำคัญโดยรวมในการถดถอย