Excel'de dinamik aralığın ortalamasını nasıl hesaplanır?

Formüller, özellikle aylık satışlar veya devam eden toplamlar gibi durumlarda, aralığınızın başlangıç veya bitiş noktasının sıkça değiştiği durumlarda dinamik aralığın ortalamasını hesaplamak için çok yönlü bir yaklaşımdır. Bir giriş hücresinin dinamik aralığın sınırını belirlemesine izin vererek, formülü yeniden yazmadan güncellenmiş verilere hızlıca adapte olabilirsiniz.

Bunu ayarlamak için boş bir hücre seçin, örneğin Hücre C4, ve aşağıdaki formülü girin:

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

Sonra çıkan ortalamayı görmek için Enter tuşuna basın.

Bu formül, otomatik olarak A2'den C2 tarafından belirtilen satıra kadar olan tüm hücreleri içerecek şekilde aralığı ayarlar, böylece C2'nin değeri değiştiğinde ortalama alınan aralık da değişir. Bu, yeni veriler geldiğinde veya belirli bir alt küme üzerinde analiz yapmak istediğinizde, ortalama aralığını dinamik olarak genişletmek veya daraltmak için esneklik sağlar.

Notlar:

(1) Bu formülde =EĞER(C2=0,"NA",ORTALAMA(A2:İNDEKS(A:A,C2))): A2, ortalaması alınacak aralığın ilk hücresini temsil eder ve C2, hedef aralığın son hücresinin satır numarasını tutan hücreye atıfta bulunur. Kendi veri yapınıza göre bu referansları gerektiği gibi değiştirin. C2 hücresinin geçerli bir satıra işaret ettiğinden emin olun, aksi takdirde beklenmedik sonuçlar veya "NA" alabilirsiniz.

(2) Alternatif olarak şunu kullanabilirsiniz:

=AVERAGE(INDIRECT("A2:A"&C2))

Bu yöntem de aynı derecede etkilidir çünkü INDIRECT'in dinamik olarak yorumladığı bir metin referansı oluşturur. Ancak, kapalı çalışma kitaplarıyla veya büyük veri setleriyle INDIRECT'i kullanırken dikkatli olun, çünkü bu, uçucu veriler için INDEX kadar verimli değildir ve hesaplama hızını etkileyebilir.

Pratik İpucu: Verileriniz sürekli büyüyor ise (örneğin, her gün yeni satırlar ekleniyor), üst sınır hücre referansını otomatik olarak ayarlamak için COUNTA veya COUNT fonksiyonunu kullanabilirsiniz — bu, dinamik aralığınızın her zaman güncel girişleri kapsadığından emin olmanızı sağlar.

Uygulanabilir senaryolar: Günlük veri kayıtları, zaman serisi girişleri veya aralığın başlangıcı veya bitişi kullanıcı girdisi veya bir özet hücresi tarafından yönlendirilen herhangi bir analiz. Avantajlar: Doğrudan, ek araç gerektirmez. Sınırlama: Satır konumları büyük ölçüde değişirse manuel formül ayarlaması gerekir.

Dinamik aralığınız pozisyonla değil, belirli kriterlerle (örneğin bölge, kategori veya kullanıcı tanımlı etiket) tanımlandığı durumlarda, dinamik adlandırılmış aralıkları ve INDIRECT gibi fonksiyonları birleştirerek hesaplamalarınızı uyarlayabilirsiniz. Bu özellikle kullanıcıların açılır menüden seçim yaparak ve ilgili ortalamaları anında gördükleri pano durumlarında faydalıdır.

Öncelikle veri setinizi başlık satırlarına veya sütunlarına göre gruplandırın. İşte nasıl yapılacağı:

1. Tüm alanı seçin (örneğin A1:D11) ve Seçimden Oluştur düğmesine tıklayın in the İsimler bölmesinde. Açılan diyalog penceresinde hem En Üst Satır hem de En Sol Sütun seçeneklerini işaretleyin ve ardından Tamam'a tıklayın. Bu adım, formüllerdeki referansları basitleştirmek için satırlardaki ve sütunlardaki verilere adlandırılmış aralıklar atar.

2. Seçtiğiniz boş hücreye şu formülü girin:

=AVERAGE(INDIRECT(G2))

Burada G2, kullanıcıların satır veya sütun başlığı adını yazdığı veya seçtiği ölçüt hücresidir. G2 değiştiğinde (örneğin "Region1" den "Region2" ye) formül, ilgili aralık için dinamik olarak ortalamayı hesaplar. #BAŞV! hatalarını önlemek için G2'deki girişlerin tanımlanan isimlerle tam olarak eşleştiğinden (büyük/küçük harf duyarlılığını da içerecek şekilde) emin olun.

En iyi kullanım alanları: Raporlama panoları, kriter tabanlı analitik. Avantajlar: Kullanıcı etkileşimi ile çok esnek dinamik raporlama veya tek hücre analizi sağlar. Sınırlama: Doğru isim yönetimi ve tutarlı giriş değerlerine dayanır.

Excel'de dolgu rengine göre hücreleri otomatik olarak say/sum/average hesaplayın

Bazen hücreleri dolgu rengiyle işaretlersiniz ve daha sonra bu hücreleri sayarsınız/toplarsınız veya bu hücrelerin ortalamasını hesaplarsınız. Kutools for Excel'in Renk ile Sayma araçları bu sorunu kolayca çözebilir.

Son N satırın ortalamasını alma, birden fazla dinamik kritere göre ortalama alma veya hatta birden fazla sayfadan veri birleştirme gibi gelişmiş dinamik davranışlar için özel bir VBA makrosu oluşturabilirsiniz. Bu yöntem, özellikle yerleşik formüller senaryonuz için çok karmaşık hale geldiğinde veya sık sık değişen yapıya uyum sağlayan otomasyon gerektiğinde çok faydalıdır.

Örneğin, kullanıcı tarafından belirlenen N sayısıyla sütun A'daki son N satırın ortalamasını hesaplamak isteyebilirsiniz veya bitişik olmayan, kullanıcı tarafından belirlenen aralıklardan değerlerin ortalamasını alabilirsiniz.

1. Geliştirici Araçları > Visual Basic'e giderek Microsoft Visual Basic for Applications düzenleyicisini açın. Ardından Ekle > Modül seçip aşağıdaki VBA kodunu yapıştırın:

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

2. Makroyu çalıştırmak için düğmesine tıklayın. Açılan diyalog penceresinde, ortalamak istediğiniz son satır sayısını girin (örneğin 5,10 vb.) ve Tamam'a basın. Sonuç bir ileti kutusunda görünecektir.

Daha karmaşık koşullara göre ortalama almak için (örneğin, kritere göre veya birden fazla sayfadan) VBA kodunu buna göre uyarlayabilirsiniz — örneğin, bir kriter değeri için InputBox ekleyerek veya birkaç çalışma sayfasında dolaşarak aralıkları birleştirmeden önce ortalamayı alabilirsiniz.

Bu yaklaşım maksimum esneklik sunar ve karmaşık veya tekrarlayan dinamik ortalama hesaplamalarını otomatikleştirebilir. Ancak, güvenlik risklerini önlemek için makroları etkinleştirdiğinizden ve bu yöntemi güvenilir bir çalışma kitabında kullandığınızdan emin olun. Yeni makroları çalıştırmadan önce işinizi kaydedin ve otomatik değişiklikler yaptığınızda yedekler oluşturmayı düşünün.

Avantajlar: Otomasyon sağlar, karmaşık veya büyük veri senaryolarını ele alır, çok özel iş mantığı için uyarlanabilir. Dezavantajlar: VBA'nın temel bir anlayışını gerektirir ve yapı değişirse prosedürlerin bakımının yapılması gerekir.