วิธีการคำนวณ Mean Squared Error (MSE) ใน Microsoft Excel

ในทางสถิติ ค่าคลาดเคลื่อนกำลังสองเฉลี่ย (MSE) เป็นตัวชี้วัดที่สำคัญที่ใช้วัดจำนวนข้อผิดพลาดในแบบจำลองทางสถิติ มักใช้เพื่อประเมินความแม่นยำของแบบจำลองที่ใช้ในการทำนายค่าในอนาคต

ในบทช่วยสอนนี้ เราจะอธิบายว่า MSE คืออะไรโดยละเอียด จากนั้นจึงแสดงวิธีคำนวณ MSE ใน Excel

ข้อผิดพลาด Mean Squared คืออะไร

ค่าคลาดเคลื่อนกำลังสองเฉลี่ย (MSE) คือการคำนวณที่วัดผลต่างกำลังสองเฉลี่ยระหว่างค่าประมาณและค่าจริงในชุดข้อมูล กล่าวอีกนัยหนึ่ง ระบบจะประมาณจำนวนข้อผิดพลาดในแบบจำลองทางสถิติ สำหรับสถิติโดยละเอียด ระบบจะคำนวณว่าเส้นการถดถอยพอดีกับชุดจุดข้อมูลมากน้อยเพียงใด

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

MSE คำนวณโดยการหาค่าเฉลี่ยของผลต่างกำลังสองระหว่างค่าที่คาดการณ์ไว้กับค่าจริงของตัวแปรเป้าหมาย

สูตร MSE มีลักษณะดังนี้:

ที่ไหน:

Σหมายถึงผลรวมของค่า

nคือขนาดตัวอย่างหรือจำนวนการสังเกต

Yiคือค่าที่สังเกตได้ และ;

Ŷiคือค่าที่ทำนายไว้

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

สิ่งนี้ดูน่าสับสน แต่ก็ไม่ได้ยากเกินไปที่จะปฏิบัติตามเมื่อเราแจกแจงรายละเอียดในส่วนต่อไปนี้

วิธีคำนวณข้อผิดพลาด Mean Squared ใน Microsoft Excel

มีสองวิธีหลักที่คุณสามารถคำนวณ MSE ใน Microsoft Excel: ฟังก์ชัน SUMSQ, ฟังก์ชันค่าเฉลี่ย และสูตร MSE เราจะใช้ตัวอย่างด้านล่างเพื่อแสดงวิธีคำนวณ MSE โดยใช้แต่ละฟังก์ชันเหล่านี้:

ในตัวอย่างที่เรียบง่ายนี้ เราจะดูมูลค่ายอดขายสมมติสำหรับแต่ละเดือน (คอลัมน์ A) ค่าประมาณจะแสดงอยู่ในคอลัมน์ B และค่าจริงในคอลัมน์ C.

คำนวณ MSE โดยใช้ฟังก์ชัน SUMSQ

ฟังก์ชัน SUMSQ ใน Excel ใช้ในการคำนวณผลรวมของกำลังสองของตัวเลขในช่วง วิธีใช้เพื่อคำนวณ MSE:

สร้างคอลัมน์ใหม่ในคอลัมน์ D ชื่อ “ความแตกต่าง” นี่จะแสดงความแตกต่างระหว่างค่าที่คาดหวังและค่าจริง

ใช้สูตร “C2-B2” เพื่อคำนวณความแตกต่างของจุดข้อมูลในแต่ละแถว

เลือกเซลล์ใหม่และพิมพ์ไวยากรณ์ต่อไปนี้: “=SUMSQ(D2:D13/COUNT(D2:D13)” แทนที่เซลล์ในสูตรด้วยเซลล์ที่ถูกต้องจากสเปรดชีต Excel ของคุณ ผลลัพธ์สุดท้ายในเซลล์นี้ คือ MSE ของคุณ

คำนวณ MSE โดยใช้ฟังก์ชัน AVERAGE

ฟังก์ชัน AVERAGE ใน MS Excel ใช้ในการคำนวณค่าเฉลี่ยของช่วงตัวเลข เมธอดนี้ส่งคืนผลลัพธ์เหมือนกับฟังก์ชัน SUMSQ แต่แต่ละขั้นตอนจะดำเนินการด้วยตนเอง

ดำเนินการขั้นตอนที่ 1 และ 2 ที่อธิบายไว้ในวิธี SUMSQ ซึ่งจะส่งคืนความแตกต่างระหว่างค่าที่คาดการณ์และข้อมูลจริง

ตั้งชื่อคอลัมน์ E “กำลังสองของความแตกต่าง” จากนั้นใช้สูตร “=D2^2” เพื่อคำนวณกำลังสองของผลต่างของแต่ละค่าในคอลัมน์ D

เลือกเซลล์ใหม่และคำนวณค่าเฉลี่ยของคอลัมน์ E ซึ่งจะส่งกลับค่าเฉลี่ยของค่ากำลังสองที่คุณคำนวณไว้ก่อนหน้านี้ โดยพิมพ์ “=AVERAGE(E2:E12)” แล้วกด Enterตรวจสอบให้แน่ใจว่าได้แทนที่เซลล์ด้วยเซลล์ที่คุณใช้ใน แผ่นงาน ค่าข้อมูลผลลัพธ์คือ MSE ของคุณ

การวิเคราะห์ทางสถิติใน Microsoft Excel

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

.

กระทู้ที่เกี่ยวข้อง: