วิธีการดำเนินการถดถอยโลจิสติกใน excel

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

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

ตัวอย่าง: การถดถอยโลจิสติกใน Excel

ใช้ขั้นตอนต่อไปนี้เพื่อดำเนินการถดถอยโลจิสติกใน Excel สำหรับชุดข้อมูลที่ระบุว่าผู้เล่นบาสเกตบอลระดับวิทยาลัยถูกร่างเข้าสู่ NBA หรือไม่ (ร่าง: 0 = ไม่, 1 = ใช่) โดยอิงตามเกรดเฉลี่ยของพวกเขา แต้ม รีบาวด์ และแอสซิสต์ในอดีต ฤดูกาล.

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

ขั้นแรก ให้ป้อนข้อมูลต่อไปนี้:

ขั้นตอนที่ 2: ป้อนเซลล์สำหรับค่าสัมประสิทธิ์การถดถอย

เนื่องจากเรามีตัวแปรอธิบายสามตัวในโมเดล (pts, rebs, ast) เราจะสร้างเซลล์สำหรับค่าสัมประสิทธิ์การถดถอยสามค่าบวกหนึ่งค่าสำหรับต้นกำเนิดในโมเดล เราจะตั้งค่าสำหรับแต่ละรายการเป็น 0.001 แต่เราจะปรับให้เหมาะสมในภายหลัง

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

ขั้นตอนที่ 3: สร้างค่าสำหรับบันทึก

ต่อไป เราจะสร้างคอลัมน์ logit โดยใช้สูตรต่อไปนี้:

ขั้นตอนที่ 4: สร้างค่าสำหรับ e ^logit

ต่อไป เราจะสร้างค่าสำหรับ e ^logit โดยใช้สูตรต่อไปนี้:

ขั้นตอนที่ 5: สร้างค่าความน่าจะเป็น

ต่อไปเราจะสร้างค่าความน่าจะเป็นโดยใช้สูตรต่อไปนี้:

ขั้นตอนที่ 6: สร้างค่าสำหรับบันทึกความน่าจะเป็น

ต่อไปเราจะสร้างค่าสำหรับบันทึกความน่าจะเป็นโดยใช้สูตรต่อไปนี้:

ความน่าจะเป็นของบันทึก = LN (ความน่าจะเป็น)

ขั้นตอนที่ 7: ค้นหาผลรวมของความน่าจะเป็นของบันทึก

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

ขั้นตอนที่ 8: ใช้ตัวแก้ปัญหาเพื่อแก้ค่าสัมประสิทธิ์การถดถอย

หากคุณยังไม่ได้ติดตั้งตัวแก้ปัญหาใน Excel ให้ทำตามขั้นตอนเหล่านี้:

• คลิก ไฟล์
• คลิก ตัวเลือก
• คลิก Add-in
• คลิก Solver Add-in แล้วคลิก ไป
• ในหน้าต่างใหม่ที่ปรากฏขึ้น ให้ทำเครื่องหมายในช่องถัดจาก Solver Add-In จากนั้นคลิก Go

เมื่อติดตั้งตัวแก้ปัญหาแล้ว ให้ไปที่กลุ่ม การวิเคราะห์ บนแท็บ ข้อมูล แล้วคลิก ตัวแก้ปัญหา ป้อนข้อมูลต่อไปนี้:

• ตั้งเป้าหมาย: เลือกเซลล์ H14 ซึ่งมีผลรวมของความน่าจะเป็นของบันทึก
• โดยการแก้ไขเซลล์ตัวแปร: เลือกช่วงเซลล์ B15:B18 ซึ่งมีค่าสัมประสิทธิ์การถดถอย
• ทำให้ตัวแปรที่ไม่มีข้อจำกัดไม่เป็นค่าลบ: ยกเลิกการเลือกช่องนี้
• เลือกวิธีการแก้ปัญหา: เลือก GRG แบบไม่เชิงเส้น

จากนั้นคลิก แก้ไข

Solver จะคำนวณค่าประมาณสัมประสิทธิ์การถดถอยโดยอัตโนมัติ:

ตามค่าเริ่มต้น สามารถใช้สัมประสิทธิ์การถดถอยเพื่อค้นหาความน่าจะเป็นที่โปรเจ็กต์ = 0

อย่างไรก็ตาม โดยทั่วไปในการถดถอยโลจิสติก เราสนใจความน่าจะเป็นที่ตัวแปรตอบสนอง = 1

ดังนั้น เราสามารถย้อนกลับเครื่องหมายบนค่าสัมประสิทธิ์การถดถอยแต่ละตัวได้:

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

ตัวอย่างเช่น สมมติว่าผู้เล่นเฉลี่ย 14 แต้มต่อเกม 4 รีบาวด์ต่อเกม และ 5 แอสซิสต์ต่อเกม ความน่าจะเป็นที่ผู้เล่นรายนี้จะถูกร่างเข้าสู่ NBA สามารถคำนวณได้ดังนี้:

P(โครงการ = 1) = อี ^{3.681193 + 0.112827*(14) -0.39568*(4) – 0.67954*(5)} / (1+e ^{3.681193 + 0.112827*(14 ) -0.39568*(4) – 0.67954*(5 )} ) = 0.57 .

เนื่องจากความน่าจะเป็นนี้มากกว่า 0.5 เราจึงทำนายผู้เล่นรายนี้   กำลังถูกร่างเข้าสู่ NBA

ที่เกี่ยวข้อง: วิธีสร้าง ROC Curve ใน Excel (ทีละขั้นตอน)