Excel'de verilerle çalışırken, genellikle veri kümenizdeki aykırı değerleri işleme sorunları yaşarsınız.
Aykırı değerlere sahip olmak her tür veride oldukça yaygındır ve analizinizin doğru ve daha anlamlı olduğundan emin olmak için bu aykırı değerleri belirlemek ve işlemek önemlidir.
Bu eğitimde, size göstereceğim Excel'de aykırı değerler nasıl bulunurve bu aykırı değerleri ele almak için çalışmamda kullandığım bazı teknikler.
Aykırı Değerler Nedir ve Bunları Bulmak Neden Önemlidir?
Aykırı değer, veri kümesindeki diğer veri noktalarının çok ötesinde olan bir veri noktasıdır. Verilerde bir aykırı değere sahip olduğunuzda, verilerinizi çarpıtabilir ve bu da yanlış çıkarımlara yol açabilir.
Size basit bir örnek vereyim.
Diyelim ki A varış noktasından B varış noktasına bir otobüste 30 kişi seyahat ediyor. Tüm insanlar benzer bir ağırlık ve gelir grubunda. Bu öğreticinin amacı için, ortalama ağırlığın 220 pound ve ortalama yıllık gelirin 70.000 dolar olduğunu düşünelim.
Şimdi rotamızın ortasında bir yerde otobüs duruyor ve Bill Gates biniyor.
Şimdi, bunun otobüsteki insanların ortalama ağırlığına ve ortalama gelirine ne yapacağını düşünüyorsunuz.
Ortalama ağırlık çok fazla değişmeyecek olsa da, otobüsteki insanların ortalama geliri büyük bir hızla artacak.
Bunun nedeni, Bill Gates'in gelirinin grubumuzda aykırı olması ve bu da bize verilerin yanlış yorumlanmasına neden oluyor. Otobüsteki her bir kişi için ortalama gelir, gerçek değerin çok ötesinde, birkaç milyar dolar olacaktır.
Excel'de gerçek veri kümeleriyle çalışırken, herhangi bir yönde aykırı değerlere sahip olabilirsiniz (yani, bir pozitif aykırı veya negatif bir aykırı değer).
Analizinizin doğru olduğundan emin olmak için, bir şekilde bu aykırı değerleri belirlemeniz ve ardından onlara en iyi nasıl davranacağınıza karar vermeniz gerekir.
Şimdi Excel'de aykırı değerleri bulmanın birkaç yolunu görelim.
Verileri Sıralayarak Aykırı Değerleri Bulun
Küçük veri kümeleriyle, aykırı değerleri belirlemenin hızlı bir yolu, verileri basitçe sıralamak ve bu sıralanmış verilerin üstündeki bazı değerleri manuel olarak gözden geçirmektir.
Ve her iki yönde de aykırı değerler olabileceğinden, verileri önce artan düzende, sonra azalan düzende sıraladığınızdan emin olun ve ardından en üst değerleri gözden geçirin.
Size bir örnek göstereyim.
Aşağıda, 15 müşteri hizmetleri araması için arama sürelerinin (saniye cinsinden) olduğu bir veri kümem var.
Aşağıda, veri kümesindeki aykırı değerleri belirleyebilmemiz için bu verileri sıralama adımları verilmiştir:
- Sıralamak istediğiniz sütunun Sütun Başlığını seçin (bu örnekte B1 hücresi)
- Ana Sayfa sekmesine tıklayın
- Düzenleme grubunda, Sırala ve Filtrele simgesine tıklayın.
- Özel Sıralama'ya tıklayın
- Sıralama iletişim kutusunda, Sıralama ölçütü açılır menüsünden "Süre"yi ve Sıra açılır menüsünde "En Büyükten En Küçüğe"yi seçin.
- Tamam'ı tıklayın
Yukarıdaki adımlar, arama süresi sütununu en üstte en yüksek değerlere sahip olacak şekilde sıralar. Artık verileri manuel olarak tarayabilir ve herhangi bir aykırı değer olup olmadığını görebilirsiniz.
Örneğimizde, ilk iki değerin diğer değerlerden çok daha yüksek olduğunu görebiliyorum (ve alttaki iki değer çok daha düşük).
Not: Bu yöntem, verileri manuel olarak tarayabileceğiniz küçük veri kümeleriyle çalışır. Bilimsel bir yöntem değil ama işe yarıyor
Çeyrek İşlevlerini Kullanarak Aykırı Değerleri Bulma
Şimdi herhangi bir aykırı değer olup olmadığını belirlemenize yardımcı olabilecek daha bilimsel bir çözümden bahsedelim.
İstatistikte, bir çeyrek, veri setinin dörtte biridir. Örneğin, 12 veri noktanız varsa, ilk çeyrek en alttaki üç veri noktası, ikinci çeyrek sonraki üç veri noktası olur ve bu böyle devam eder.
Aykırı değerleri bulmak istediğim veri seti aşağıdadır. Bunu yapmak için 1. ve 3. çeyreği hesaplamam ve sonra onu kullanarak üst ve alt limiti hesaplamam gerekecek.
E2 hücresindeki ilk çeyreği hesaplamak için formül aşağıdadır:
=ÇEYREK.DAR($B$2:$B$15,1)
ve işte E3 hücresindeki üçüncü çeyreği hesaplayan:
=ÇEYREK.DAR($B$2:$B$15,3)
Şimdi, Çeyrek Aralığı (1. ve 3. çeyrekteki verilerimizin %50'si olan) elde etmek için yukarıdaki iki hesaplamayı kullanabilirim.
=F3-F2
Şimdi verilerimizin çoğunu içerecek olan alt ve üst limiti bulmak için çeyrekler arası aralığı kullanacağız.
Bu alt ve üst sınırların dışında kalan her şey aykırı değer olarak kabul edilecektir.
Alt limiti hesaplamak için formül aşağıdadır:
=Çeyrek1 - 1.5*(Çeyrekler Arası Aralık)
hangi bizim örneğimizde olur:
=F2-1.5*F4
Ve üst sınırı hesaplamak için formül:
=Çeyrek3 + 1.5*(Çeyrekler Arası Aralık)
hangi bizim örneğimizde olur:
=F3+1.5*F4
Artık veri setimizde üst ve alt limite sahip olduğumuza göre, orijinal verilere geri dönebilir ve bu aralıkta olmayan değerleri hızlı bir şekilde belirleyebiliriz.
Bunu yapmanın hızlı bir yolu, her değeri kontrol etmek ve yeni bir sütunda DOĞRU veya YANLIŞ değerini döndürmek olacaktır.
Aykırı değerler için DOĞRU elde etmek için aşağıdaki VEYA formülünü kullandım.
=VEYA(B2$F$6)
Artık Aykırı Değer sütununu filtreleyebilir ve yalnızca değerin DOĞRU olduğu kayıtları gösterebilirsiniz.
Alternatif olarak, değerin DOĞRU olduğu tüm hücreleri vurgulamak için koşullu biçimlendirmeyi de kullanabilirsiniz.
Not: Bu, istatistiklerde aykırı değerleri bulmak için daha kabul gören bir yöntem olsa da. Bu yöntemi gerçek hayat senaryolarında biraz kullanışsız buluyorum. Yukarıdaki örnekte formül ile hesaplanan alt sınır -103 iken elimizdeki veri seti sadece pozitif olabilir. Dolayısıyla bu yöntem bir yönde (yüksek değerler) aykırı değerleri bulmamıza yardımcı olabilir, diğer yöndeki aykırı değerleri belirlemede işe yaramaz.BÜYÜK/KÜÇÜK işlevlerini kullanarak Aykırı Değerleri Bulma
Çok fazla veriyle (birden çok sütundaki değerler) çalışıyorsanız, en büyük ve en küçük 5 veya 7 değeri çıkarabilir ve içinde herhangi bir aykırı değer olup olmadığını görebilirsiniz.
Herhangi bir aykırı değer varsa, tüm verileri her iki yönde de gözden geçirmek zorunda kalmadan bunları tanımlayabileceksiniz.
Aşağıdaki veri setine sahip olduğumuzu ve herhangi bir aykırı değer olup olmadığını bilmek istediğimizi varsayalım.
Aşağıda size veri kümesindeki en büyük değeri verecek formül yer almaktadır:
=BÜYÜK($B$2:$B$16,1)
Benzer şekilde, ikinci en büyük değer şu şekilde verilecektir:
=BÜYÜK($B$2:$B$16,1)
Dinamik dizilere sahip Microsoft 365 kullanmıyorsanız, aşağıdaki formülü kullanabilirsiniz ve bu size tek bir formülle veri kümesinden en büyük beş değeri verecektir:
=BÜYÜK($B$2:$B$16,SATIR($1:5))
Benzer şekilde, en küçük 5 değeri istiyorsanız, aşağıdaki formülü kullanın:
=KÜÇÜK($B$2:$B$16,SATIR($1:5))
veya dinamik dizileriniz yoksa aşağıdakiler:
=KÜÇÜK($B$2:$B$16,1)
Bu değerlere sahip olduğunuzda, veri setindeki aykırı değerleri bulmak gerçekten çok kolay.
En büyük ve en küçük 5 değeri çıkarmayı seçmiş olsam da, veri kümenizin ne kadar büyük olduğuna bağlı olarak 7 veya 10 almayı seçebilirsiniz.
Excel'de aykırı değerleri bulmak için bunun kabul edilebilir bir yöntem olup olmadığından emin değilim, ancak birkaç yıl önce işimde çok fazla finansal veri ile çalışmak zorunda kaldığımda kullandığım yöntem bu. Bu eğitimde ele alınan diğer tüm yöntemlerle karşılaştırıldığında, bunu en etkili buldum.
Aykırı Değerlerle Nasıl Doğru Şekilde Başa Çıkılır?
Şimdiye kadar veri setimizde aykırı değerleri bulmamıza yardımcı olacak yöntemleri gördük. Ama aykırı değerler olduğunu öğrendikten sonra ne yapmalı.
Veri analizinizin doğru olması için aykırı değerleri işlemek için kullanabileceğiniz birkaç yöntem aşağıda verilmiştir.
Aykırı Değerleri Sil
Veri kümenizden aykırı değerleri kaldırmanın en kolay yolu, onları basitçe silmektir. Bu şekilde analizinizi çarpıtmaz.
Büyük veri kümeleriniz olduğunda ve birkaç aykırı değeri silmenin genel analizi etkilemeyeceği durumlarda bu daha uygun bir çözümdür. Ve elbette, verileri silmeden önce bir kopya oluşturduğunuzdan ve bu aykırı değerlere neyin neden olduğunu araştırdığınızdan emin olun.
Aykırı Değerleri Normalleştirin (Değeri Ayarlayın)
Aykırı değerleri normalleştirmek, tam zamanlı işimdeyken yaptığım şeydi. Tüm aykırı değerler için, onları veri setindeki maksimum değerden biraz daha yüksek bir değere değiştirirdim.
Bu, verileri silmememi sağladı ama aynı zamanda verilerimi çarpıtmasına izin vermiyorum.
Size gerçek hayattan bir örnek vermek gerekirse, şirketlerin çoğunun -%10 ila %30 arasında olduğu ve %100'ün üzerinde olan birkaç değerin olduğu şirketlerin net kar marjını analiz ediyorsanız, bu aykırı değerleri %30 veya %35 olarak değiştirmek yeterlidir.
Yani bunlar, kullanabileceğiniz yöntemlerden bazıları. Aykırı değerleri bulmak için Excel.
Aykırı değerleri belirledikten sonra, verileri araştırabilir ve bunlara neyin neden olduğunu arayabilirsiniz, aynı zamanda bu aykırı değerleri işlemek için tekniklerden birini seçebilirsiniz (bunları kaldırmak veya değeri ayarlayarak normalleştirmek olabilir)
Umarım bu öğreticiyi faydalı bulmuşsunuzdur.
