Excel'de sıfır değerleri göz ardı ederek yüzdelik dilim veya çeyreklik nasıl hızlıca hesaplanır?
Excel'de YÜZDELİK veya ÇEYREKLİK işlevlerini kullanırken kullanıcılar genellikle veri aralıklarında sıfır değerlerle karşılaşıyor. Bu işlevler, varsayılan olarak sıfırları hesaplamalara dahil eder; ancak sıfırın bağlamda anlamlı bir veri temsil etmediği durumlarda bu, yüzdelik dilim ve çeyreklik sonuçlarını önemli ölçüde düşürebilir. Daha doğru bir istatistiksel analiz için yüzdelik dilim veya çeyreklik hesaplarken sıfır değerleri tamamen göz ardı etmek isteyebilirsiniz. Bu eğitim, Excel’de bu sorunu verimli şekilde aşmak amacıyla yerel formül yaklaşımları, VBA çözümleri ve ihtiyaçlarınıza en uygun yöntemi seçmenize yardımcı olacak senaryo analizlerini içeren çeşitli pratik teknikleri adım adım gösterecektir.
Sıfırları yok sayarak YÜZDELİK veya ÇEYREKLİK
Sıfırları yok sayarak YÜZDELİK (Dizi Formülü)
Sıfırları yok sayarak yüzdelik dilimi hesaplamak için yalnızca sıfırdan büyük değerleri dikkate alan bir dizi formülü kullanabilirsiniz.
Sonucu görüntülemek istediğiniz boş bir hücre seçin ve aşağıdaki formülü girin:
=PERCENTILE(IF(A1:A13>,0,A1:A13),0.3)Formülü yazdıktan sonra yalnızca Enter tuşuna basmak yerine Ctrl + Shift + Enter tuşlarına basmalısınız çünkü bu bir dizi formülüdür. Excel, formülün doğru şekilde girildiğini göstermek için onu süslü parantezlerle { } çevreleyecektir. Bu formülde:
- A1:A13, sizin Veri Aralığınız—kendi sayfanıza göre gerektiği gibi ayarlayın.
- 0,3 değeri, 30 uncuyüzdelik dilimi belirtir. Bu değeri, hesaplamak istediğiniz herhangi bir yüzdelik dilim için değiştirebilirsiniz (örneğin, 0,75 değeri 75)inci yüzdelik dilim içindir).
Bu yöntem, sıfırların —özellikle eksik veya geçersiz ölçümler gibi— istatistiksel çıktıları etkilemesini önlemek istediğinizde son derece yararlıdır.
Yalnızca Enter tuşuna basmanın düzgün çalışmayacağını unutmayın; mutlaka Ctrl + Shift + Enter tuşlarına basmalısınız. Ayrıca, toplama işlevlerinin içinde IF(...) içeren formüller büyük veri kümelerinde daha az verimli olabilir.
Sıfırları yok sayarak ÇEYREKLİK (Dizi Formülü)
Bu yaklaşım çeyreklikler için de benzer şekilde geçerlidir. Sonucu görmek istediğiniz bir hücre seçin ve aşağıdakini girin:
=QUARTILE(IF(A1:A18>,0,A1:A18),1)Formülü girdikten sonra dizi formülü olarak onaylamak için Ctrl + Shift + Enter tuşlarına basın.
- A1:A18, örnek veri aralığıdır (gerektiğinde değiştirin).
- 1, ilk çeyrekliği (25)inci yüzdelik dilim) istediğinizi gösterir. Medyan için 2'yi, üçüncü çeyreklik (75 inci yüzdelik dilim) için ise 3'ü kullanabilirsiniz.
Veri Aralığınızın metin veya hata içeren hücreler barındırmadığından emin olun; çünkü formül yalnızca sayısal değerlerle çalışır. Bu çözüm, VBA veya eklentilere gerek kalmadan hızlı hesaplamalar yapılması gereken orta büyüklükteki veri kümeleri için en ideal seçenektir.
Sıfırları Hariç Tutarak Yüzdelik Dilim/Çeyreklik Hesaplamak İçin VBA Makrosu
Sıfır değerleri filtreleyip kalan veriler üzerinde yüzdelik dilim veya çeyreklik hesaplamak için VBA (Visual Basic for Applications) kullanabilirsiniz. Bu yöntem, özellikle büyük veri kümeleriyle çalışırken ya da formülleri elle girmeden bu işlemi sık sık tekrarlamak istediğinizde son derece pratiktir.
Uygulanabilir senaryolar: İleri düzey kullanıcılar, tekrarlayan görevler veya karmaşık aralıklar için idealdir. Kodu özelleştirerek herhangi bir yüzdelik dilimi, çeyreklik indeksi ve veri alanını işleyebilirsiniz.
1. Excel'de Geliştirici Araçları sekmesine gidin. Görünür değilse, Şerit'e sağ tıklayın, Şerit'i Özelleştir seçeneğini seçin ve Geliştirici kutusunu işaretleyin. Ardından Geliştirici Araçları > Visual Basic'e tıklayın.
2. Microsoft Visual Basic for Applications penceresinde, Ekle > Modül'e tıklayın.
3. Aşağıdaki VBA kodunu modüle kopyalayıp yapıştırın:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Makroyu çalıştırmak için VBA penceresindeki 
düğmesine tıklayın veya F5tuşuna basın. Önce yalnızca sayılardan oluşan Veri Aralığınızı seçmeniz, ardından istenen yüzdelik dilimi belirtmeniz (örneğin, 30)üncü yüzdelik dilim için 0,3) istenecektir. Ardından makro, sıfır değerleri otomatik olarak filtreleyecek ve sonuçları bir ileti kutusunda gösterecektir.
Avantajlar: Büyük veya düzensiz veri kümelerini hızla işler, sıfır değerleri tamamen dışlar ve manuel formül girişi gerektirmez. Tekrar kullanılabilir ve özelleştirilebilir.
Dezavantajlar: Makroların etkinleştirilmesini ve VBA konusunda temel düzeyde bilgiyi gerektirir. Kullanıcı tanımlı işlev (UDF) olarak dönüştürülmediği sürece çalışma sayfası formüllerinde kullanılamaz.
Yaygın sorunlar ve sorun giderme: Sayısal olmayan değerler veya hatalar içeren hücreleri seçerseniz, makro atlayabilir ya da bir hata mesajı gösterebilir. Veri Aralığı’nın yalnızca sıfır ve pozitif sayılardan oluştuğundan emin olun. Sıfır dışı veri bulunamazsa, size uygun bir uyarı görüntülenir.
İpuçları: VBA kodunu, çıktıyı belirli bir çalışma sayfası hücresine kopyalamak, hesaplama işlevlerini özelleştirmek veya birden fazla aralıkta otomatikleştirme yapmak için daha da geliştirebilirsiniz. Makroları çalıştırırken ya da düzenlerken kazara veri kaybını önlemek için mutlaka çalışma kitabınızı kaydedin.
Bu çözümü birden fazla sütunda yüzdelik dilim veya çeyreklik hesaplamalarına genişletmeniz gerekiyorsa, makroyu sütunlar ya da aralıklar arasında döngü kuracak şekilde özelleştirmeyi düşünün.
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