Excel: aykırı değerler hariç ortalama nasıl hesaplanır?

Excel’de aykırı değerleri hariç tutarak ortalamayı hesaplamanın iki yolu vardır:

1. Ortalamayı hesaplayın ve aykırı değerleri hariç tutmak için TRIMMEAN’ı kullanın

2. Ortalamayı hesaplayın ve aykırı değerleri hariç tutmak için çeyrekler arası aralığı kullanın

Her iki yöntemin de nasıl kullanılacağını göstermek için Excel’de aşağıdaki veri kümesini kullanacağız:

Yöntem 1: Ortalamayı hesaplayın ve aykırı değerleri hariç tutmak için TRIMMEAN’ı kullanın

Excel’deki TRIMMEAN işlevi, veri kümesinin üst ve alt kısmındaki gözlemlerin belirli bir yüzdesini hariç tutarak bir değer aralığının ortalamasını hesaplamak için kullanılabilir.

Örneğin, gözlemlerin toplam %20’sini (üst %10 ve alt %10) hariç tutarak A sütunundaki ortalama değeri hesaplamak için aşağıdaki formülü kullanabiliriz:

 =TRIMMEAN( A2:A16 , 20%)

Veri setimizde 15 değer bulunduğundan %10, 1,5’e eşittir ve bu da 1’e yuvarlanır. Yani bu formül, en küçük değeri ve en büyük değeri hariç tutarak aralıktaki değerlerin ortalamasını hesaplayacaktır:

Aykırı değerler hariç ortalama 58,30769 olarak çıkıyor.

Yöntem 2: Ortalamayı hesaplayın ve aykırı değerleri hariç tutmak için çeyrekler arası aralığı kullanın

Çeyrekler arası aralık (IQR), bir veri setindeki 75. yüzdelik dilim (Q3) ile 25. yüzdelik dilim (Q1) arasındaki farktır. Değerlerin ortalama %50’sinin dağılımını ölçer.

Bir gözlemi, üçüncü çeyreğin (Q3) çeyrekler arası aralığın 1,5 katı üzerinde veya birinci çeyreğin (Q1) altındaki çeyrekler arası aralığın 1,5 katı ise aykırı değer olarak tanımlayabiliriz.

Veri setimizin çeyrekler arası aralığını Excel’de hesaplamak için aşağıdaki formülü kullanabiliriz:

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

Aşağıdaki ekran görüntüsü bu formülün nasıl kullanılacağını gösterir:

Daha sonra aykırı değerleri tanımlamak ve veri kümesinde aykırı değer olan herhangi bir değere “1” atamak amacıyla IQR’yi kullanmak için aşağıdaki formülü kullanabiliriz:

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

Aşağıdaki ekran görüntüsü bu formülün nasıl kullanılacağını gösterir:

Bu veri kümesinde yalnızca bir değerin ( 164 ) aykırı değer olduğunu görüyoruz.

Son olarak veri setinde aykırı olmayan tüm değerlerin ortalamasını hesaplamak için aşağıdaki formülü kullanabiliriz:

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

Aşağıdaki ekran görüntüsü bu formülün nasıl kullanılacağını göstermektedir:

Aykırı değerler hariç ortalama 55,42857 olarak çıkıyor.

Ek kaynaklar

Aşağıdaki eğitimlerde Excel’deki diğer ortak görevlerin nasıl gerçekleştirileceği açıklanmaktadır:

Excel’de Çeyrekler Arası Aralık (IQR) Nasıl Hesaplanır?
Excel’de gruba göre ortalama nasıl hesaplanır
Excel’de Ağırlıklı Hareketli Ortalamalar Nasıl Hesaplanır