Excel'de filtrelenmiş hücreleri veya listeyi nasıl ortalarsınız?

Filtrelenmiş bir veri kümesine doğrudan AVERAGE işlevini uyguladığınızda, bu işlev filtreleme nedeniyle gizlenmiş satırlar da dahil olmak üzere belirtilen aralıktaki tüm hücrelerin ortalamasını hesaplar. Ancak yalnızca görünür satırların ortalamasını almak istiyorsanız bu yaklaşım yanıltıcı sonuçlara yol açabilir. Filtrelenmiş (yani yalnızca görünür) verilerin gerçek ortalamasını elde etmek için Excel’in SUBTOTAL işlevi mükemmel bir çözümdür. SUBTOTAL işlevi, hem filtrelemeyi hem de gizlenmiş satırları dikkate alacak şekilde ayarlanabilir; bu özellik onu bu tür senaryolar için ideal kılar.

Sadece filtrelenmiş sonuçların ortalamasını hesaplamak için aşağıdaki adımları izleyin:

• Ortalama almak istediğiniz sütundaki tüm filtrelenmiş verileri içeren aralığı belirleyin (bu örnekte, değerlerin)C12:C24 aralığında ve Tutar sütununda olduğunu varsayalım).
• Boş bir hücreye aşağıdaki formülü girin:

=SUBTOTAL(1,C12:C24)

Bu formül, C12:C24 aralığındaki görünür (filtrelenmiş) hücrelerin ortalamasını hesaplar. 1 parametresi, SUBTOTAL işlevine AVERAGE işlevini kullanmasını söyler; böylece SUBTOTAL, filtrelemeyle gizlenmiş satırları otomatik olarak yok sayar.

Formülü yazdıktan sonra Enter tuşuna basın. Aşağıda gösterildiği gibi, yalnızca görünür satırların ortalama değeri anında görüntülenecektir:

Gizlenmiş veya filtrelenmiş hücreleri/satırları/sütunları yok sayarak yalnızca görünür hücreleri topla/say/ortalama

Excel'deki normal TOPLA, SAY ve ORTALAMA işlevleri, filtreleme ya da manuel gizleme nedeniyle hücreler görünür veya gizli olsa bile belirtilen aralıktaki tüm hücreleri hesaplamaya dahil eder. Bu tür durumlarla daha etkili bir şekilde başa çıkmak için Kutools for Excel'i kullanabilirsiniz. Özel TOPLAGÖRÜNÜR, SAYGÖRÜNÜR ve ORTALAMAGÖRÜNÜR işlevleriyle, herhangi bir aralıkta gerçekten görünür olan hücrelerin toplamını, sayısını ve ortalamasını kolayca hesaplayabilir; hem filtrelenmiş hem de manuel olarak gizlenmiş hücreleri, satırları veya sütunları dışarıda bırakabilirsiniz. Bu işlevsellik, karmaşık çalışma sayfalarında hataları önlemenize yardımcı olur ve karmaşık formüller veya özel kodlar yazmanıza gerek kalmadan zaman kazanmanızı sağlar.

AGGREGATE işleviyle filtrelenmiş verileri/listeyi ortalama

Excel 2010 veya daha yeni bir sürüm kullanıyorsanız, AGGREGATE işlevi, filtrelenmiş verilerde ortalama hesaplamak için SUBTOTAL’dan çok daha fazla esneklik sunar; hatalarla ve gizlenmiş satırlarla başa çıkmak için de ekstra seçenekler sağlar. İşte bunu nasıl uygulayabileceğiniz:

• Boş bir hücreye aşağıdaki formülü girin (filtrelenmiş verilerinizin C12:C24 aralığında olduğunu varsayalım):

=AGGREGATE(1,5, C12:C24)

• İlk bağımsız değişken ()1), AVERAGE işlevini belirtir; tıpkı SUBTOTAL işlevinde olduğu gibi.
• İkinci bağımsız değişken ()5), AGGREGATE işlevine filtreleme sonucunda gizlenmiş satırları ve hataları yok saymasını söyler.

Formülü yazdıktan sonra Enter tuşuna basın; böylece Filtre Aralığı’ndaki görünür satırların ortalaması anında hesaplanıp görüntülenir. Satırları gizlemenin farklı yollarını kullanıyor veya diğer toplulaştırma işlevlerine uyarlamak istiyorsanız, sadece ikinci parametreyi buna göre ayarlamanız yeterli.

Yalnızca gerçekten görünür hücreleri ortalayan VBA makrosu

Daha gelişmiş veya özelleştirilmiş ihtiyaçlar için, yalnızca görünür (gizlenmemiş ve filtrelenmemiş) hücrelerin ortalamasını almak amacıyla bir Aralık Seç içinde basit bir VBA makrosu kullanabilirsiniz. Bu yöntem, verileri gizlemek üzere birden fazla tekniğin kullanıldığı sayfalarda özellikle yararlıdır. İşte bunu nasıl yapacağınız:

1. Excel'de Geliştirici sekmesine gidin ve VBA düzenleyicisini açmak için Visual Basic seçeneğine tıklayın. Düzenleyicide, yeni bir modül oluşturmak için Ekle > Modül yolunu izleyin.
2. Aşağıdaki VBA kodunu modül penceresine kopyalayıp yapıştırın:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. VBA düzenleyicisini kapatın. Çalışma sayfanıza döndüğünüzde Alt+F8 tuşlarına basın, AverageVisibleCells seçeneğini belirleyin ve Çalıştır düğmesine tıklayın. İstendiğinde hedef Veri Aralığı’nızı seçin. Makro, yalnızca şu anda görünür olan (filtrelenmemiş ve gizlenmemiş) sayısal hücrelerin ortalamasını hesaplayıp gösterecektir.

Ağırlıklı ortalama

Ortalama/bileşik yıllık büyüme oranını hesapla

Hareketli/kayan ortalama

Kümülatif toplam / ortalama

Değişim oranının ortalaması