Excel'de Aykırı Değerler İşlevi Nasıl (ve Neden) Kullanılır?

Aykırı değer, verilerinizdeki değerlerin çoğundan önemli ölçüde yüksek veya düşük olan bir değerdir. Verileri analiz etmek için Excel kullanırken, aykırı değerler sonuçları çarpıtabilir. Örneğin, bir veri kümesinin ortalama ortalaması, değerlerinizi gerçekten yansıtabilir. Excel, aykırı değerlerinizi yönetmenize yardımcı olacak birkaç yararlı işlev sağlar, o yüzden bir göz atalım.

Hızlı Bir Örnek

Aşağıdaki resimde, aykırı değerleri tespit etmek oldukça kolaydır - Eric'e atanan ikisinin değeri ve Ryan'a atanan 173'ün değeri. Bunun gibi bir veri kümesinde, bu aykırı değerleri manuel olarak tespit etmek ve bunlarla başa çıkmak yeterince kolaydır.

Daha büyük bir veri kümesinde durum böyle olmayacaktır. Aykırı değerleri belirleyebilmek ve bunları istatistiksel hesaplamalardan çıkarabilmek önemlidir ve bu makalede nasıl yapılacağına bakacağız.

Verilerinizde Aykırı Değerler Nasıl Bulunur?

Bir veri kümesindeki aykırı değerleri bulmak için aşağıdaki adımları kullanırız:

  1. 1. ve 3. çeyrekleri hesaplayın (biraz sonra bunların ne olduğundan bahsedeceğiz).
  2. Çeyrekler arası aralığı değerlendirin (bunları biraz daha aşağıda açıklayacağız).
  3. Veri aralığımızın üst ve alt sınırlarını döndürün.
  4. Uzak veri noktalarını belirlemek için bu sınırları kullanın.

Aşağıdaki resimde görülen veri setinin sağındaki hücre aralığı bu değerleri saklamak için kullanılacaktır.

Başlayalım.

Birinci Adım: Çeyrekleri Hesaplayın

Verilerinizi çeyreklere bölerseniz, bu kümelerin her birine çeyrek adı verilir. Aralıktaki sayıların en düşük% 25'i 1. çeyreği, sonraki% 25'i 2. çeyreği oluşturur ve bu böyle devam eder. İlk önce bu adımı atıyoruz çünkü bir aykırı değerin en yaygın olarak kullanılan tanımı, 1. çeyreğin altında 1.5 çeyrekler arası aralıklardan (IQR) ve 3. çeyreğin üzerinde 1.5 çeyrekler arası aralık olan bir veri noktasıdır. Bu değerleri belirlemek için önce çeyreklerin ne olduğunu bulmalıyız.

Excel, çeyrekleri hesaplamak için DÖRTTEBİRLİK işlevi sağlar. İki parça bilgi gerektirir: dizi ve kuart.

= DÖRTTEBİRLİK (dizi, dörttebir)

Dizi sen değerlendirdiklerini değerlerin aralığıdır. Ve litre Eğer (örneğin 1 2 çeyrek dilimde 1 quartile, 2, vb) geri dönüşü isteyen kuartilini temsil eden bir sayıdır.

Not: Excel 2010'da Microsoft, DÖRTTEBİRLİK işlevinde iyileştirmeler olarak DÖRTTEBİRLİK.DHL ve DÖRTTEBİRLİK.HRC işlevlerini yayımladı. QUARTILE, Excel'in birden çok sürümünde çalışırken geriye dönük olarak daha uyumludur.

Örnek tablomuza dönelim.

1. Çeyrek'i hesaplamak için F2 hücresinde aşağıdaki formülü kullanabiliriz.

= DÖRTTEBİRLİK (B2: B14,1)

Formülü girerken, Excel, dörtlü bağımsız değişken için bir seçenek listesi sağlar.

3. çeyreği hesaplamak için, F3 hücresine öncekine benzer bir formül girebiliriz, ancak bir yerine üç kullanabiliriz.

= DÖRTTEBİRLİK (B2: B14,3)

Şimdi, hücrelerde görüntülenen çeyrek veri noktalarına sahibiz.

İkinci Adım: Çeyrekler Aralığını Değerlendirin

Çeyrekler arası aralık (veya IQR), verilerinizdeki değerlerin ortadaki% 50'sidir. 1. çeyrek değeri ile 3. çeyrek değeri arasındaki fark olarak hesaplanır.

1. çeyreği 3. çeyrekten çıkaran F4 hücresine basit bir formül kullanacağız:

= F3-F2

Şimdi çeyrekler arası aralığımızın görüntülendiğini görebiliriz.

Üçüncü Adım: Alt ve Üst Sınırları Geri Döndürün

Alt ve üst sınırlar, kullanmak istediğimiz veri aralığının en küçük ve en büyük değerleridir. Bu sınır değerlerden daha küçük veya daha büyük değerler, aykırı değerlerdir.

IQR değerini 1.5 ile çarpıp ardından Q1 veri noktasından çıkararak F5 hücresindeki alt sınır sınırını hesaplayacağız:

= F2- (1,5 * F4)

Not: Bu formüldeki parantezler, çarpma kısmı çıkarma kısmından önce hesaplama yapacağından gerekli değildir, ancak formülün okunmasını kolaylaştırır.

F6 hücresindeki üst sınırı hesaplamak için, IQR'yi tekrar 1.5 ile çarpacağız, ancak bu sefer Q3 veri noktasına ekleyeceğiz :

= F3 + (1,5 * F4)

Dördüncü Adım: Aykırı Değerleri Belirleyin

Artık tüm temel verilerimizi oluşturduğumuza göre, alt sınır değerinden düşük veya üst sınır değerden yüksek olan dış veri noktalarımızı belirleme zamanı.

Bu mantıksal testi gerçekleştirmek için OR işlevini kullanacağız ve aşağıdaki formülü C2 hücresine girerek bu kriterleri karşılayan değerleri göstereceğiz:

= VEYA (B2 $ F $ 6)

Daha sonra bu değeri C3-C14 hücrelerimize kopyalayacağız. DOĞRU bir değer aykırı bir değeri gösterir ve görebileceğiniz gibi verilerimizde iki tane var.

Ortalama Ortalamayı Hesaplarken Aykırı Değerleri Yoksaymak

DÖRTTEBİRLİK işlevini kullanarak, IQR'yi hesaplayalım ve en yaygın kullanılan aykırı değer tanımıyla çalışalım. Bununla birlikte, bir dizi değer için ortalama ortalamayı hesaplarken ve aykırı değerleri göz ardı ederken, kullanımı daha hızlı ve daha kolay bir işlev vardır. Bu teknik, daha önce olduğu gibi bir aykırı değeri tanımlamayacak, ancak aykırı değer olarak değerlendirebileceğimiz şey konusunda esnek olmamızı sağlayacaktır.

İhtiyacımız olan işleve TRIMMEAN adı verilir ve bunun sözdizimini aşağıda görebilirsiniz:

= TRIMMEAN (dizi, yüzde)

Dizi ortalama istediğiniz değerlerin aralığıdır. Yüzde (eğer bir yüzdesi veya ondalık değer olarak girebilirsiniz) veri setinin alt ve üst dışında tutulacak veri noktalarının yüzdesidir.

Ortalamayı hesaplamak ve aykırı değerlerin% 20'sini hariç tutmak için örneğimizde aşağıdaki formülü D3 hücresine girdik.

= TRIMMEAN (B2: B14,% 20)

Aykırı değerleri ele almak için iki farklı işleve sahipsiniz. Bunları bazı raporlama ihtiyaçları için belirlemek veya ortalamalar gibi hesaplamalardan çıkarmak isteyip istemediğinizi, Excel'in ihtiyaçlarınızı karşılayacak bir işlevi vardır.