วิธีสร้างตัวแปรจำลองใน excel (ทีละขั้นตอน)

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

ตัวอย่างเช่น สมมติว่าเรามีชุดข้อมูลต่อไปนี้และต้องการใช้ อายุ และ สถานภาพสมรส ในการทำนาย รายได้ :

หากต้องการใช้ สถานภาพการสมรส เป็นตัวแปรทำนายในแบบจำลองการถดถอย เราจำเป็นต้องแปลงให้เป็นตัวแปรจำลอง

เนื่องจากปัจจุบันนี้เป็นตัวแปรเด็ดขาดที่สามารถรับค่าที่แตกต่างกันได้สามค่า (“โสด”, “แต่งงานแล้ว” หรือ “หย่าร้าง”) เราจึงต้องสร้างตัวแปรจำลอง k -1 = 3-1 = 2 ตัว

ในการสร้างตัวแปรจำลองนี้ เราสามารถปล่อยให้ “เดี่ยว” เป็นค่าฐานได้เนื่องจากปรากฏบ่อยที่สุด ต่อไปนี้คือวิธีที่เราจะแปลง สถานภาพการสมรส เป็นตัวแปรจำลอง:

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

ขั้นตอนที่ 1: สร้างข้อมูล

ขั้นแรก มาสร้างชุดข้อมูลใน Excel กัน:

ขั้นตอนที่ 2: สร้างตัวแปรจำลอง

ต่อไป เราสามารถคัดลอกค่าจากคอลัมน์ A และ B ลงในคอลัมน์ E และ F จากนั้นใช้ฟังก์ชัน IF() ใน Excel เพื่อกำหนดตัวแปรจำลองใหม่สองตัว: แต่งงานแล้ว และ หย่าร้าง

นี่คือสูตรที่เราใช้ในเซลล์ G2 ซึ่งเราคัดลอกไปยังเซลล์ที่เหลือในคอลัมน์ G:

 = IF (C2 = "Married", 1, 0)

และนี่คือสูตรที่เราใช้ในเซลล์ H2 ซึ่งเราคัดลอกไปยังเซลล์ที่เหลือในคอลัมน์ H:

 = IF (C2 = "Divorced", 1, 0)

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

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

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

หากไม่มีตัวเลือกนี้ คุณต้องโหลด Analysis Toolpak ก่อน

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

จากนั้นกรอกข้อมูลต่อไปนี้แล้วคลิก ตกลง

สิ่งนี้ให้ผลลัพธ์ดังต่อไปนี้:

จากผลลัพธ์ เราจะเห็นได้ว่าเส้นถดถอยที่พอดีคือ:

รายได้ = 14,276.12 + 1,471.67*(อายุ) + 2,479.75*(แต่งงานแล้ว) – 8,397.40*(หย่าร้าง)

เราสามารถใช้สมการนี้เพื่อค้นหารายได้โดยประมาณของแต่ละบุคคลโดยพิจารณาจากอายุและสถานภาพการสมรส ตัวอย่างเช่น บุคคลที่อายุ 35 ปีและแต่งงานแล้วจะมีรายได้ประมาณ $68,264 :

รายได้ = 14,276.12 + 1,471.67*(35) + 2,479.75*(1) – 8,397.40*(0) = 68,264 ดอลลาร์

ต่อไปนี้เป็นวิธีการตีความค่าสัมประสิทธิ์การถดถอยในตาราง:

• การสกัดกั้น: การสกัดกั้นแสดงถึงรายได้เฉลี่ยของบุคคลคนเดียวที่มีอายุเป็นศูนย์ เนื่องจากบุคคลหนึ่งๆ ไม่สามารถมีอายุเป็น 0 ปีได้ จึงไม่สมเหตุสมผลที่จะตีความการสกัดกั้นด้วยตัวมันเองในแบบจำลองการถดถอยโดยเฉพาะนี้
• อายุ: อายุที่เพิ่มขึ้นในแต่ละปีสัมพันธ์กับรายได้ที่เพิ่มขึ้นโดยเฉลี่ย 1,471.67 ดอลลาร์ เนื่องจากค่า p (0.004) น้อยกว่า 0.05 อายุจึงเป็นตัวทำนายรายได้ที่มีนัยสำคัญทางสถิติ
• แต่งงานแล้ว: คนที่แต่งงานแล้วมีรายได้เฉลี่ยมากกว่า 2,479.75 ดอลลาร์สหรัฐฯ มากกว่าคนเดียว เนื่องจากค่า p (0.800) ไม่น้อยกว่า 0.05 ความแตกต่างนี้จึงไม่มีนัยสำคัญทางสถิติ
• หย่าร้าง: บุคคลที่หย่าร้างมีรายได้โดยเฉลี่ย 8,397.40 ดอลลาร์น้อยกว่าบุคคลเดียว เนื่องจากค่า p (0.532) ไม่น้อยกว่า 0.05 ความแตกต่างนี้จึงไม่มีนัยสำคัญทางสถิติ

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

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

วิธีดำเนินการถดถอยเชิงเส้นอย่างง่ายใน Excel
วิธีการคำนวณผลรวมที่เหลือของกำลังสองใน Excel
วิธีการดำเนินการถดถอยพหุนามใน Excel
วิธีการสร้างพล็อตที่เหลือใน Excel