วิธีสร้าง roc curve ใน excel (ทีละขั้นตอน)

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

• ความไว: ความน่าจะเป็นที่แบบจำลองคาดการณ์ผลลัพธ์ที่เป็นบวกสำหรับการสังเกต เมื่อผลลัพธ์เป็นบวกจริงๆ สิ่งนี้เรียกอีกอย่างว่า “อัตราบวกที่แท้จริง”
• ความจำเพาะ: ความน่าจะเป็นที่แบบจำลองทำนายผลลัพธ์ที่เป็นลบสำหรับการสังเกต เมื่อผลลัพธ์เป็นลบจริงๆ สิ่งนี้เรียกอีกอย่างว่า “อัตราติดลบที่แท้จริง”

วิธีหนึ่งในการแสดงภาพการวัดทั้งสองนี้คือการสร้าง เส้นโค้ง ROC ซึ่งย่อมาจากเส้นโค้ง “ลักษณะการทำงานของตัวรับ” นี่คือกราฟที่แสดงความไวและความเฉพาะเจาะจงของแบบจำลองการถดถอยลอจิสติก

ตัวอย่างทีละขั้นตอนต่อไปนี้แสดงวิธีสร้างและตีความเส้นโค้ง ROC ใน Excel

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

เริ่มต้นด้วยการป้อนข้อมูลดิบ:

ขั้นตอนที่ 2: คำนวณข้อมูลสะสม

จากนั้นลองใช้สูตรต่อไปนี้เพื่อคำนวณค่าสะสมสำหรับหมวดหมู่ผ่านและไม่ผ่าน:

• ค่าความสำเร็จสะสม: =SUM($B$3:B3)
• ค่าความล้มเหลวสะสม: =SUM($C$3:C3)

จากนั้นเราจะคัดลอกและวางสูตรเหล่านี้ลงในแต่ละเซลล์ในคอลัมน์ D และคอลัมน์ E:

ขั้นตอนที่ 3: คำนวณอัตราผลบวกลวงและอัตราผลบวกจริง

ต่อไป เราจะคำนวณอัตราผลบวกลวง (FPR) อัตราผลบวกจริง (TPR) และพื้นที่ใต้เส้นโค้ง (AUC) โดยใช้สูตรต่อไปนี้:

• FPR: =1-D3/$D$14
• ทีพีอาร์: =1-E3/$E$14
• ASC: =(F3-F4)*G3

จากนั้นเราจะคัดลอกและวางสูตรเหล่านี้ลงในแต่ละเซลล์ในคอลัมน์ F, G และ H:

ขั้นตอนที่ 4: สร้างเส้นโค้ง ROC

ในการสร้างเส้นโค้ง ROC เราจะเน้นแต่ละค่าในช่วง F3:G14

ต่อไป เราจะคลิกแท็บ แทรก บริเวณริบบิ้นด้านบน จากนั้นคลิก แทรกกระจาย (X, Y) เพื่อสร้างเส้นทางต่อไปนี้:

ขั้นตอนที่ 5: คำนวณ AUC

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

ดังที่เราเห็นจากกราฟด้านบน โมเดลการถดถอยโลจิสติกนี้ทำหน้าที่จำแนกข้อมูลออกเป็นหมวดหมู่ได้ดีมาก

ในการหาปริมาณ เราสามารถคำนวณ AUC (พื้นที่ใต้เส้นโค้ง) ซึ่งจะบอกเราว่าพล็อตอยู่ใต้เส้นโค้งมากเพียงใด

ยิ่ง AUC ใกล้ 1 มากเท่าใด โมเดลก็ยิ่งดีเท่านั้น โมเดลที่มี AUC เท่ากับ 0.5 ไม่ได้ดีไปกว่าโมเดลที่มีการจำแนกแบบสุ่ม

ในการคำนวณ AUC ของเส้นโค้ง เราสามารถรวมค่าทั้งหมดในคอลัมน์ H:

AUC กลายเป็น 0.802662 ค่านี้ค่อนข้างสูง บ่งชี้ว่าโมเดลทำงานได้ดีในการจัดประเภทข้อมูลเป็นหมวดหมู่ “ผ่าน” และ “ไม่ผ่าน”

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

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

วิธีการลงจุด CDF ใน Excel
วิธีสร้างเส้นโค้งการเอาชีวิตรอดใน Excel
วิธีสร้างแผนภูมิควบคุมกระบวนการทางสถิติใน Excel