Excel: cara menghitung rata-rata tidak termasuk outlier

Ada dua cara untuk menghitung rata-rata sambil mengecualikan outlier di Excel:

1. Hitung rata-rata dan gunakan TRIMMEAN untuk mengecualikan outlier

2. Hitung mean dan gunakan rentang interkuartil untuk mengecualikan outlier

Kami akan menggunakan kumpulan data berikut di Excel untuk mengilustrasikan cara menggunakan kedua metode:

Metode 1: Hitung rata-rata dan gunakan TRIMMEAN untuk mengecualikan outlier

Fungsi TRIMMEAN di Excel dapat digunakan untuk menghitung rata-rata suatu rentang nilai sambil mengecualikan persentase pengamatan tertentu dari atas dan bawah kumpulan data.

Misalnya, kita dapat menggunakan rumus berikut untuk menghitung nilai rata-rata di kolom A tidak termasuk total 20% observasi (10% teratas dan 10% terbawah):

 =TRIMMEAN( A2:A16 , 20%)

Karena kita memiliki 15 nilai dalam kumpulan data kita, 10% sama dengan 1,5, yang dibulatkan menjadi 1. Jadi rumus ini akan menghitung rata-rata nilai dalam rentang tersebut sambil mengecualikan nilai terkecil dan nilai terbesar:

Rata-rata, tidak termasuk outlier, adalah 58.30769 .

Metode 2: Hitung mean dan gunakan rentang interkuartil untuk mengecualikan outlier

Rentang interkuartil (IQR) adalah selisih antara persentil ke-75 (Q3) dan persentil ke-25 (Q1) dalam suatu kumpulan data. Ini mengukur distribusi rata-rata 50% nilai.

Kita dapat mendefinisikan suatu observasi sebagai outlier jika observasi tersebut 1,5 kali rentang antarkuartil di atas kuartil ketiga (Q3) atau 1,5 kali rentang antarkuartil di bawah kuartil pertama (Q1).

Kita bisa menggunakan rumus berikut untuk menghitung rentang interkuartil kumpulan data kita di Excel:

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

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini:

Kemudian kita dapat menggunakan rumus berikut untuk menggunakan IQR untuk mengidentifikasi outlier dan menetapkan “1” untuk nilai apa pun yang merupakan outlier dalam kumpulan data:

 =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)

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini:

Kami melihat bahwa hanya satu nilai – 164 – yang merupakan outlier dalam kumpulan data ini.

Terakhir, kita bisa menggunakan rumus berikut untuk menghitung rata-rata seluruh nilai dalam dataset yang bukan outlier:

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

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini:

Rata-ratanya, tidak termasuk outlier, adalah 55.42857 .

Sumber daya tambahan

Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di Excel:

Cara Menghitung Rentang Interkuartil (IQR) di Excel
Cara menghitung rata-rata berdasarkan kelompok di Excel
Cara Menghitung Rata-Rata Pergerakan Tertimbang di Excel