Excel'de sıfır değerleri veya hata içeren hücreler göz ardı edilerek medyan nasıl hesaplanır?
Excel'de birçok veri analizi görevinde, veri kümenizin merkez eğilimini doğru şekilde değerlendirmek için medyanın hassas bir biçimde hesaplanması kritik öneme sahiptir. Ancak veri kümeniz bazen, medyan hesaplamasını bozabilecek sıfır değerler veya ()#BÖL/0!, #YOK vb. gibi) hata içeren hücreler barındırabilir. Örneğin, standart =MEDIAN(range) formülünü kullanmak sıfırları da hesaba katar ve aralıkta geçersiz hücreler olduğunda bir hata döndürür; bu durum aşağıda gösterildiği gibi yanıltıcı sonuçlara ya da hesaplama sorunlarına neden olabilir.
Bunu çözmek için, sıfırları veya hataları dışlayarak medyanı hesaplamanıza yardımcı olacak çeşitli çözümler sunuyoruz; böylece analiziniz hem doğru hem de sağlam hale gelir. Bu çözümler, anket verileri, finansal raporlar ya da bilimsel ölçümler gibi sıfır veya hataların anlamlı sonuçlar elde etmek adına çıkarılması gereken pek çok senaryoya uygundur. Aşağıda, doğrudan formüllerden gelişmiş otomasyon tekniklerine kadar Excel’de kullanabileceğiniz her yöntemin pratik, adım adım kılavuzlarını bulacaksınız.
VBA: Sıfırları ve hataları yok sayarak medyan (KDF)
Power Query: Sıfırları/hataları filtreledikten sonra medyan
Sıfırları yok sayarak medyan
Medyan hesaplamasında göz önünde bulundurmak istemediğiniz sıfırlar varsa—örneğin eksik değerlerin 0 olarak temsil edildiği durumlarda—sıfırları dışlamak için bir dizi formülü kullanabilirsiniz. Bu, sıfırların gerçek ölçümler değil de kullanılamayan veriler için yer tutucu olduğu veri kümelerinde özellikle yararlıdır.
Medyanın görüntüleneceği bir hücre seçin (örneğin C2) ve aşağıdaki formülü girin:
=MEDIAN(IF(A2:A17<,>,0,A2:A17))Formülü girdikten sonra yalnızca Enter tuşuna basmak yerine, formülü bir dizi formülü haline getirmek için Ctrl + Shift + Enter tuşlarına basın (Formül Çubuğu’nda formülün etrafında süslü parantezler görünecektir). Böylece A2:A17 aralığındaki sıfır olmayan değerler, medyan hesaplamasında dikkate alınır. Ekran görüntüsüne bakın:
İpuçları:
- Excel 365 veya Excel 2021 ve sonraki sürümleri kullanıyorsanız, dinamik dizi desteğine sahip olduğunuz için yalnızca Enter tuşuna basmanız yeterlidir.
- Aralıkta en az bir sıfırdan farklı sayısal değer olduğundan emin olun; aksi takdirde formül #SAYI! hatası döndürür.
- Bu çözüm, anket yanıtları, gider raporları veya satış verileri gibi sıfırların analizden çıkarılması gereken verileri temizlemek için idealdir.
Hataları yok sayarak medyan
#YOK, #BÖL/0! veya #DEĞER! gibi hata değerleri, standart MEDYAN işlevinin hata vermesine ve Veri Analizi işleminizin durmasına neden olabilir. Bu hataları dışlayarak medyanı güvenle hesaplamak için aşağıdaki dizi formülünü kullanabilirsiniz.
Sonucunuzu görüntülemek istediğiniz herhangi bir hücre seçin ve aşağıdaki formülü girin:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Formülü girdikten sonra Ctrl + Shift + Enter tuşlarına basın (Excel 365/Excel 2021 veya üstünü kullanmıyorsanız; bu sürümler dinamik dizilere izin verir). Bu formül, F2:F17 aralığında yalnızca gerçek sayıları dikkate alır—hata içeren hücreleri tamamen yok sayar.
İpuçları ve Uyarılar:
- Tüm hücreler hata değerleriyse, sonuç bir #SAYI! hatası döndürür—verilerinizde en az bir geçerli sayının olduğundan emin olun.
- Dışlama kriterlerini (örneğin sıfırları ve hataları aynı anda hariç tutma gibi) koşulları iç içe yerleştirerek birleştirebilirsiniz.
- Bu formül, içe aktarılmış verilerle, anket sonuçlarıyla veya finansal tablolarla çalışırken—özellikle bunlar kısmen tamamlanmış ya da başarısız hesaplamalar içerebiliyorsa—son derece yararlıdır.
VBA: Sıfırları ve hataları yok sayarak medyan (KDF)
Sıfırları ve hataları yok sayarak sık sık medyan hesaplamanız gerekiyorsa ya da dizi formüllerini elle girmekten kaçınmak istiyorsanız, özel bir VBA işlevinden (Kullanıcı Tanımlı İşlev – KDF) yararlanabilirsiniz. Bu yaklaşım, tüm dışlama kriterlerini içerebilen ve yerleşik bir formül gibi kullanılabilen ekstra esneklik sunar; böylece büyük veya sık güncellenen veri kümeleri için idealdir.
KDF'yi ayarlama:
- Excel'de Geliştirici sekmesine tıklayın. Mevcut değilse, etkinleştirmek için Dosya > Seçenekler > Şerit Özelleştir yolunu izleyin.
- Visual Basic öğesine tıklayarak VBA düzenleyicisini açın.VBA düzenleyicisi.
- VBA düzenleyicide yeni bir modül oluşturmak için Ekle > Modül öğesine tıklayın.
- 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 FunctionKDF kullanımı:
Excel'e döndükten sonra herhangi bir hücreye =MedianIgnoreZeroError(A2:A17)formülünü girin (hedef aralığınızla)A2:A17 ifadesini değiştirin). Dizi formüllerinin aksine yalnızca Enter tuşuna basmanız yeterlidir—Ctrl + Shift + Enter tuşlarına basmanıza gerek yoktur.
- Bu yöntem çok büyük veri kümeleri için iyi çalışır, dizi formülüyle ilgili sorunlardan kaçınır ve kodda daha fazla düzenleme yapılarak diğer istenmeyen değerleri yok sayacak şekilde uyarlanabilir.
- Aralık yalnızca sıfır veya hatalardan oluşuyorsa sonuç #SAYI!
- Bir #AD? hatası alırsanız, VBA makrosunun doğru şekilde yüklendiğinden ve Excel ayarlarınızda makroların etkinleştirildiğinden emin olun.
Power Query: Sıfırları/hataları filtreledikten sonra medyan
Power Query, Excel’de veri içe aktarma, dönüştürme ve analiz için özellikle medyan gibi hesaplamalardan önce verileri temizlemek ve ön işlemek istediğinizde güçlü bir araçtır. Power Query ile sıfırları ve hataları kolayca filtreleyebilir, böylece hesaplamalarda yalnızca geçerli sayıların yer almasını sağlayabilirsiniz. Bu yaklaşım, Kaynak Veri düzenli olarak güncelleniyor ya da harici sistemlerden içe aktarılıyorsa özellikle faydalıdır.
Sıfırları ve hataları yok sayarak medyan hesaplamak için Power Query kullanım adımları:
- Veri Aralığı içinde herhangi bir hücre seçin, ardından Veri sekmesine gidip Tablodan/Araklıktan öğesine tıklayın. Verileriniz zaten tablo biçiminde değilse, Excel bir tablo oluşturmanızı isteyecektir—Tamam’a tıklayın.
- Power Query Düzenleyici penceresi açılacaktır. İlgili sütunun açılır okuna tıklayıp 0öğesinin işaretini kaldırarak sıfır değerleri filtreleyin. (Hataları filtrelemek için sütun başlığına sağ tıklayın ve)Hataları Kaldır seçeneğini belirleyin.)
- Filtreleme tamamlandıktan sonra temizlenmiş verileri çalışma sayfanıza geri göndermek için Giriş > Kapat ve Yükle öğesine tıklayın.
- Veriler artık istenmeyen tüm öğelerden arındırıldığı için, yalnızca filtrelenmiş değerlere sahip sütuna standart
=MEDIAN()formülünü uygulayabilirsiniz.
Bu yöntem orijinal verilerinizin değişmeden kalmasını sağlar, yeni veya güncellenmiş verilerle güçlü tekrarlanabilirlik sunar ve tekrarlayan raporlama görevleri veya büyük/harici veri kümeleriyle çalışırken özellikle etkilidir. Power Query iş akışları, Kaynak Veri değiştiğinde tek bir tıklamayla yenilenebilir; böylece manuel müdahale ve hata riski en aza indirilir.
- Power Query, Excel 2016 ve sonraki sürümlerde yerleşik olarak; Excel 2010 ve 2013 içinse eklenti olarak mevcuttur.
- Dönüştürmeden sonra, sonuç verileri üzerinde hesaplamalar yapılabildiğinden, aşağı akıştaki analizler daha yüksek güvenilirlik kazanır.
Beklenmeyen sonuçlarla karşılaşırsanız, Power Query’deki filtreleme adımlarınızı gözden geçirin ve temizlenmiş verilerinizde geçerli sayısal değerlerin olduğundan emin olun.
Özetle, doğrudan dizi formüllerini mi, otomasyon için özel bir VBA çözümünü mü yoksa daha kapsamlı iş akışı otomasyonu için Power Query’yi mi seçtiğinizin önemi yok—Excel, sıfırları ve hataları yok sayarak medyan hesaplamak için size birden çok pratik seçenek sunar. Güvenilir ve doğru sonuçlar almak için veri kümenizin boyutuna, güncelleme sıklığına ve iş akışınıza en uygun yöntemi tercih edin.
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar