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

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

1. ใช้ TRIMMEAN เพื่อแยกค่าผิดปกติ

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

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

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

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

ตัวอย่างเช่น เราสามารถใช้สูตรต่อไปนี้เพื่อคำนวณค่าเฉลี่ยในคอลัมน์ 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)

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

 =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

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

บทแนะนำต่อไปนี้จะอธิบายวิธีทำงานทั่วไปอื่นๆ ใน Google ชีต

วิธีคำนวณค่าเฉลี่ยตามกลุ่มใน Google ชีต
วิธีการคำนวณเปอร์เซ็นต์เฉลี่ยใน Google ชีต
วิธีการคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Google ชีต