Excel: วิธีคำนวณค่าเฉลี่ยไม่รวมค่าผิดปกติ

มีสองวิธีในการคำนวณค่าเฉลี่ยโดยไม่รวมค่าผิดปกติใน Excel:

1. คำนวณค่าเฉลี่ยและใช้ TRIMMEAN เพื่อแยกค่าผิดปกติออก

2. คำนวณค่าเฉลี่ยและใช้ช่วงระหว่างควอไทล์เพื่อแยกค่าผิดปกติออก

เราจะใช้ชุดข้อมูลต่อไปนี้ใน Excel เพื่อแสดงวิธีใช้ทั้งสองวิธี:

วิธีที่ 1: คำนวณค่าเฉลี่ยและใช้ TRIMMEAN เพื่อแยกค่าผิดปกติออก

ฟังก์ชัน TRIMMEAN ใน Excel สามารถใช้คำนวณค่าเฉลี่ยของช่วงค่าต่างๆ โดยไม่รวมเปอร์เซ็นต์ของการสังเกตจากด้านบนและด้านล่างของชุดข้อมูล

ตัวอย่างเช่น เราสามารถใช้สูตรต่อไปนี้เพื่อคำนวณค่าเฉลี่ยในคอลัมน์ A โดยไม่รวมการสังเกตทั้งหมด 20% (10% บนสุดและล่าง 10%):

 =TRIMMEAN( A2:A16 , 20%)

เนื่องจากเรามี 15 ค่าในชุดข้อมูลของเรา 10% เท่ากับ 1.5 ซึ่งปัดเศษเป็น 1 ดังนั้นสูตรนี้จะคำนวณค่าเฉลี่ยของค่าในช่วงโดยไม่รวมค่าที่น้อยที่สุดและค่าที่ใหญ่ที่สุด:

ค่าเฉลี่ยไม่รวมค่าผิดปกติ กลายเป็น 58.30769

วิธีที่ 2: คำนวณค่าเฉลี่ยและใช้ช่วงระหว่างควอไทล์เพื่อแยกค่าผิดปกติออก

ช่วงระหว่างควอไทล์ (IQR) คือความแตกต่างระหว่างเปอร์เซ็นไทล์ที่ 75 (Q3) และเปอร์เซ็นไทล์ที่ 25 (Q1) ในชุดข้อมูล โดยจะวัดการกระจายของค่าเฉลี่ย 50% ของค่า

เราสามารถกำหนดให้การสังเกตเป็นค่าผิดปกติได้หากเป็น 1.5 เท่าของช่วงระหว่างควอไทล์เหนือควอไทล์ที่ 3 (Q3) หรือ 1.5 เท่าของช่วงระหว่างควอไทล์ที่ต่ำกว่าควอร์ไทล์ที่ 1 (Q1)

เราสามารถใช้สูตรต่อไปนี้เพื่อคำนวณช่วงระหว่างควอไทล์ของชุดข้อมูลของเราใน Excel:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

ภาพหน้าจอต่อไปนี้แสดงวิธีใช้สูตรนี้:

จากนั้นเราสามารถใช้สูตรต่อไปนี้เพื่อใช้ IQR เพื่อระบุค่าผิดปกติและกำหนด “1” ให้กับค่าใดๆ ที่เป็นค่าผิดปกติในชุดข้อมูล:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

ภาพหน้าจอต่อไปนี้แสดงวิธีใช้สูตรนี้:

เราพบว่ามีเพียงค่าเดียวเท่านั้นคือ 164 ซึ่งกลายเป็นค่าผิดปกติในชุดข้อมูลนี้

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

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

ภาพหน้าจอต่อไปนี้แสดงวิธีใช้สูตรนี้:

ค่าเฉลี่ยไม่รวมค่าผิดปกติ กลายเป็น 55.42857

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

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

วิธีการคำนวณช่วงระหว่างควอไทล์ (IQR) ใน Excel
วิธีการคำนวณค่าเฉลี่ยตามกลุ่มใน Excel
วิธีการคำนวณค่าเฉลี่ยเคลื่อนที่ถ่วงน้ำหนักใน Excel