Excel'de sıfırları veya hataları görmezden gelerek medyan değerler nasıl hesaplanır?
Excel’de veri analizi yaparken medyanı doğru şekilde hesaplamak, veri setinizin merkezi eğilimini anlamak için çok önemlidir. Ancak bazen veri setinizde sıfırlar veya hata değerleri (örneğin #BÖL/0!, #YOKvb.) bulunabilir ve bunlar doğrudan medyan hesaplamanızı engelleyebilir. Örneğin, standart formülü kullanmak =MEDIAN(range) sıfırları hesaba katacak ve aralıkta geçersiz hücreler varsa hata verecektir; bu, yanıltıcı sonuçlara veya hesaplama hatalarına yol açabilir. Aşağıda illüstrasyon olarak görebilirsiniz.
Bunu aşmak için sıfırları veya hataları hariç tutarak medyanı doğru ve sağlam şekilde hesaplamanızı sağlayacak çeşitli çözümler mevcuttur. Bu çözümler; anket verilerini temizleme, finansal raporlar veya sıfır ya da hataların anlamlı sonuçlar elde etmek için kaldırılması gereken bilimsel ölçümler gibi farklı senaryolar için uygundur. Aşağıda, Excel’de kullanılabilen yöntemler için pratik adım adım kılavuzlar bulacaksınız—doğrudan formüllerden gelişmiş otomasyon tekniklerine kadar.
Sıfırları hariç tutarak medyan hesaplama
Hataları hariç tutarak medyan hesaplama
VBA: Sıfırları ve hataları hariç tutarak medyan bulma (Kullanıcı tanımlı fonksiyon)
Power Query: Sıfırları/hataları filtreleyerek medyan hesaplama
Sıfırları hariç tutarak medyan hesaplama
Aralığınızda medyan hesaplamasına dahil etmek istemediğiniz sıfırlar varsa—örneğin0 olarak temsil edilen eksik değerler—sıfırları hariç tutmak için dizi formülünü kullanabilirsiniz. Özellikle sıfırların gerçek ölçüm yerine ulaşılmayan veriler için yer tutucu olduğu veri setlerinde bu yöntem oldukça faydalıdır.
Medyanı göstermek istediğiniz bir hücreyi seçin (örneğin, C2) ve aşağıdaki formülü girin:
=MEDIAN(IF(A2:A17<>0,A2:A17))Formülü girdikten sonra, sadece Enter tuşuna basmak yerine Ctrl + Shift + Enter tuşlarına basarak bir dizi formülü oluşturun (formül çubuğunda formülün etrafında süslü parantezler belirecektir). Böylece sadece A2:A17 aralığındaki sıfır olmayan değerler medyan hesaplamasında dikkate alınacaktır. Ekran görüntüsüne bakınız:
İpuçları:
- Excel365 veya Excel2021 ve sonrası sürümleri kullanıyorsanız, dinamik dizi desteği sayesinde sadece Enter tuşuna basmak yeterlidir.
- Aralıkta en az bir tane sıfır olmayan sayısal değer olduğundan emin olun, aksi takdirde formül #SAYI! hatası verecektir.
- Bu çözüm; sıfırların analizden çıkarılması gereken anket yanıtları, harcama raporları veya satış verilerini düzenlemek için idealdir.
Hataları hariç tutarak medyan hesaplama
#YOK, #BÖL/0! veya #DEĞER! gibi hata değerleri; standart medyan fonksiyonunun hata vermesine ve veri analizinizin durmasına neden olabilir. Bu hataları göz ardı ederek güvenli şekilde medyan hesaplamak için aşağıdaki dizi formülünü kullanabilirsiniz.
Sonucunu göstermek istediğiniz herhangi bir hücreyi seçin ve aşağıdaki formülü girin:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Formülü girdikten sonra Ctrl + Shift + Enter (Excel365/Excel2021 veya üzeri sürümler kullanıyorsanız, dinamik diziler sayesinde sadece Enter yeterli olur). Formül, yalnızca F2:F17 aralığındaki gerçek sayı değerlerini içerir—tüm hata hücrelerini tamamen görmezden gelir.
İpuçları ve Dikkat Edilecekler:
- Eğer tüm hücreler hata değeriyse, sonuç #SAYI! olarak döner—verilerinizde en az bir geçerli sayı olduğundan emin olun.
- Hariç tutma kriterlerini birleştirebilirsiniz (örneğin, hem sıfırları hem de hataları hariç tutmak) için koşulları iç içe yerleştirin.
- Bu formül; ithal edilen veriler, anket sonuçları veya kısmi ya da başarısız hesaplamalar içerebilen finansal tablolarla çalışırken özellikle faydalıdır.
VBA: Sıfırları ve hataları hariç tutarak medyan bulma (Kullanıcı Tanımlı Fonksiyon)
Sıkça hem sıfırları hem de hataları hariç tutarak medyan hesaplamanız gerekiyorsa veya dizili formülleri elle girmek istemiyorsanız, özel bir VBA fonksiyonu (Kullanıcı Tanımlı Fonksiyon, UDF) kullanabilirsiniz. Bu yaklaşım; özel fonksiyonun tüm hariç tutma kriterlerini kapsayabilmesini ve yerleşik bir formül gibi kullanılabilmesini sağlayarak geniş veya sık güncellenen veri setleri için ekstra esneklik sunar.
UDF nasıl kurulur:
- Excel'de Geliştirici sekmesine tıklayın. Eğer görünmüyorsa, Dosya > Seçenekler > Şeridi Özelleştir yoluyla etkinleştirin.
- VBA editörünü açmak için Visual Basic’e tıklayın.
- VBA editöründe Ekle > Modül seçeneğine tıklayarak yeni bir modül oluşturun.
- Aşağıdaki kodu modüle kopyalayıp yapıştırın:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionUDF nasıl kullanılır:
Excel’e geri döndükten sonra, herhangi bir hücreye formülü girin =MedianIgnoreZeroError(A2:A17) (hedef aralığınız ile değiştirin). Dizili formüllerin aksine, sadece Enter tuşuna basmanız yeterlidir— A2:A17 Ctrl + Shift + Enter tuşlarına gerek yoktur..
- Bu yöntem; çok büyük veri kümelerinde iyi çalışır, dizili formül problemlerinden kaçınır ve kodu düzenleyerek başka gereksiz değerleri de hariç tutacak şekilde uyarlanabilir.
- Eğer aralıkta sadece sıfırlar veya hatalar varsa, sonuç #SAYI! olarak görünecek.
- Eğer #AD? hatası alırsanız, VBA makrosunun doğru şekilde kurulduğundan ve Excel ayarlarında makroların etkin olduğundan emin olun.
Power Query: Sıfırları/hataları filtreleyerek medyan hesaplama
Power Query, Excel'de veri içe aktarma, dönüştürme ve analizinde oldukça güçlü bir araçtır—özellikle hedefiniz büyük veri kümelerini medyan gibi hesaplamalar öncesinde temizlemek ve ön işleme tabi tutmaksa. Power Query ile sıfırları ve hataları kolayca filtreleyerek hesaplamanızda yalnızca geçerli sayıların yer almasını sağlayabilirsiniz. Bu yöntem; özellikle kaynak verileriniz düzenli olarak güncelleniyor veya dış sistemlerden ithal ediliyorsa oldukça avantajlıdır.
Power Query ile sıfırları ve hataları hariç tutarak medyan hesaplamak için adımlar:
- Veri aralığınızda herhangi bir hücreyi seçin, ardından Veri sekmesine gidip Tablo/Aralıktan seçeneğine tıklayın. Eğer veriniz zaten tablo formatında değilse, Excel tablo oluşturmanızı ister—Tamam'a tıklayın.
- Power Query Düzenleyicisi penceresi açılacaktır. İlgili sütun için aşağı ok simgesine tıklayın ve0 seçeneğinin işaretini kaldırarak sıfırları filtreleyin. (Hataları filtrelemek için kolon başlığına sağ tıklayın, Hataları Kaldır seçeneğini seçin.)
- Filtrelendikten sonra, Ana Sayfa > Kapat & Yükle seçeneğine tıklayarak temizlenen veriyi çalışma sayfanıza geri aktarın.
- Artık sadece filtrelenmiş değerler olan sütuna standart
=MEDIAN()formülünü uygulayabilirsiniz; çünkü veriler artık istenmeyen tüm öğeleri içermez.
Bu yöntem; orijinal verinizin değişmeden kalmasını sağlar, yeni veya güncellenen verilerde güçlü tekrarlanabilirlik sunar ve düzenli raporlama işleri ya da büyük/dış veri kümeleriyle çalışırken özellikle etkilidir. Power Query işlemleri; kaynak veriniz değiştiğinde tek tıkla yenilenebilir, böylece manuel müdahale ve hata riski en aza iner.
- Power Query, Excel2016 ve sonrasında yerleşiktir (Excel2010 ve2013 için eklenti olarak mevcuttur).
- Dönüştürme işlemi tamamlandıktan sonra, elde edilen temiz veri üzerinde hesaplamalar yapabilir ve sonraki analizler için daha güvenilir sonuçlar elde edebilirsiniz.
Beklenmedik sonuçlarla karşılaşırsanız, Power Query’deki filtreleme adımlarınızı kontrol edin ve temizlenen verinizde geçerli sayısal değerler kaldığından emin olun.
Özetle, ister doğrudan dizili formüllerle çalışmayı, ister otomasyon için özel bir VBA çözümü oluşturmayı, ister büyük iş akışları için Power Query’yi kullanmayı tercih edin; Excel’de sıfırları veya hataları görmezden gelerek medyan hesaplamak için birçok pratik seçenek sunulmaktadır. Veri setinizin büyüklüğüne, güncelleme sıklığına ve iş akışı tercihlerinize göre en uygun yöntemi seçerek güvenilir ve doğru sonuçlar elde edin.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin