Excel'de birden fazla vlookup sonucunun ortalamasını nasıl alırsınız?

Aynı arama öğesiyle ilişkilendirilmiş birkaç değeri bulmanız ve ortalamasını almanız gerektiğinde, doğrudan bir formül kullanmak en hızlı ve en esnek yollardan biridir. AVERAGEIF veya bir dizi formülü, ekstra sütunlar oluşturmadan bunu kolayca gerçekleştirir.

Boş bir hücreye (örneğin F2) aşağıdaki formülü girin:

=AVERAGEIF(A1:A24,E2,C1:C24)

Formülü yazdıktan sonra Enter tuşuna basın. Bu, size E2 hücresindeki arama değerinizle eşleşen A sütunundaki ilgili değerlerin C sütunundaki tüm değerlerin ortalamasını hemen verecektir. Aşağıdaki görsel açıklamaya bakın:

Parametre açıklaması ve ipuçları:

• A1:A24: Arama değerlerinizi içeren aralık.
• E2: Aramak istediğiniz özel değer.
• C1:C24: Ortalamasını almak istediğiniz eşleşen değerlerin bulunduğu aralık.

Alternatif yaklaşım (dizi formülleri konusunda rahat olan kullanıcılar için):

Boş bir hücreye aşağıdaki formülü girin ve onaylamak için Ctrl + Shift + Enter tuşlarını kullanın:

=AVERAGE(IF(A1:A24=E2,C1:C24))

Dizi formülleri, her karşılaştırmayı tek tek işler, bu da dinamik dizileri desteklemeyen Excel sürümlerinde yardımcı olur. Hataları önlemek için aralıkların tam olarak aynı boyutta olduğundan emin olun.

Pratik senaryolar ve notlar:
- Filtrelenmemiş ve basit arama ihtiyaçlarına sahip veri setleri için en uygunudur.
- Herhangi bir aralık boş hücre içeriyorsa, bu hücreler ortalama hesaplamasında göz ardı edilir.
- Dinamik tablolarda veya veri ekleme sırasında, daha sağlam formüller için tablo referanslarını kullanmayı düşünün.
- Yanlış ortalamalar veya hataların yaygın bir kaynağı olan hücre aralıklarının yanlış eşleşmelerine dikkat edin.

Eğer tekrarlayan verilere dayalı olarak veri toplamak ve birleştirmek zorunda kalıyorsanız, Kutools for Excel'in Gelişmiş Satırları Birleştir yardımcı programı aracılığıyla pratik bir çözüm sunar. Bu araç, eşleşen kayıtlar için ortalama, toplam veya sayma gibi değerleri tek bir adımda birleştirebilir veya hesaplayabilir, bu da büyük veri setleri veya düzenli raporlar için oldukça uygundur.

1. Veri tablonuzun aralığını, hem arama sütununu hem de ortalamasını almak istediğiniz değerleri vurgulayın. Ardından Kutools > Metin > Gelişmiş Satırları Birleştir'e gidin. Ekran görüntüsüne bakın:

2. Görüntülenen iletişim kutusunda:

• Arama değerlerinizin bulunduğu sütunu seçin ve Anahtar olarak ayarlayın.
• Hedef değerlerinizin bulunduğu sütunu seçin, ardından Hesapla > Ortalama'ya tıklayın.
• Diğer sütunlar için birleştirme veya hesaplama kurallarını gerektiği gibi ayarlayın - örneğin, virgülle metin birleştirme veya toplam, maksimum veya minimum uygulama.

3. Ayarları uygulamak için Tamam'a tıklayın.

Şimdi, arama değerleriyle eşleşen satırlar birleştirilmiştir ve belirlenen sütundaki değerler, her benzersiz arama değeri için otomatik olarak ortalanmıştır. Bu, özellikle özet raporlar hazırlarken veya verileri sıkıştırırken faydalıdır.

Pratik ipucu: Gelişmiş Satırları Birleştirme aracı, manuel hesaplama ihtiyacını ve hata yapma potansiyelini azaltır. Araç, tekrarlayan arama değerleriyle düzenli olarak veri işleme ihtiyacı duyan ve hızlı bir şekilde eyleme dönüştürülebilir özetler isteyen kullanıcılar için en uygundur. Birleştirme işleminden önce doğru sütunların atanmış olduğundan emin olun, özellikle veri yapısı değiştiğinde.

Demo: Kutools for Excel ile birden fazla vlookup sonucunun ortalamasını alın

Pivot Tablolar, verileri özetlemek ve analiz etmek için dinamik ve görsel bir yaklaşım sunar. Pivot Tablo kullanarak, girişleri arama değerlerine göre otomatik olarak gruplayabilir ve her grup için hedef sütunun ortalamasını gösterebilirsiniz; bu da verileriniz değiştiğinde güncellenen etkileşimli bir özet sağlar.

En etkili senaryolar: Bu yaklaşım, tek bir arama değerine odaklanmak yerine tüm arama değerleri için genel bir özet gerektiğinde uygundur. Pivot Tablolar, hızlı veri keşfi, rapor oluşturma ve sonuçlarınızı sıralanabilir, genişletilebilir bir formatta sunmak istediğinizde harikadır.

Talimatlar:

• Başlıklar dahil olmak üzere tüm veri setinizi seçin.
• Ekle > PivotTable > Tablodan veya Aralık’tan’a gidin. Pivot Tabloyu yeni bir çalışma sayfasına veya var olan birine yerleştirmek için gereken şekilde seçin.
• PivotTable Alanlar panelinde, arama değerlerini içeren sütunu Satırlar alanına sürükleyin.
• Ortalamasını almak istediğiniz sütunu Değerler alanına sürükleyin. Değer alanına tıklayın, Değer Alanı Ayarları'nı seçin, ardından hesaplama türünü Ortalama olarak ayarlayın.

Bu, ilişkili veriler için her benzersiz arama değerinin ortalamasıyla birlikte bir özet tablo oluşturur. Gruplamayı değiştirebilir, filtreleyebilir veya detaylara inebilirsiniz.

Artılar: Formül gerekmez, dinamik güncellemeleri destekler, raporlama ve veri keşfi için uygundur.

Eksiler: Veri değişikliklerinden sonra yenilemek için ekstra adımlar gereklidir, diğer formüllere doğrudan tek bir değer çıkarmak için daha az uygundur ve ilk kurulum PivotTables hakkında temel bilgi gerektirir.

Sorun giderme ipuçları: Değerler ortalama yerine sayılar veya toplamlar olarak görünüyorsa, alan hesaplama ayarını kontrol edin. En iyi sonuçlar için, sütunların uygun başlıklara sahip olduğundan emin olun ve Pivot Tablo oluşturmadan önce yinelenen sütun isimlerini netleştirin.

İleri düzey kullanıcılar ve düzenli olarak güncellenen verilerle uğraşanlar için VBA makrosu kullanmak, bir arama değerine uyan tüm girişler arasında ortalama alma işlemini otomatikleştirmenize olanak tanır. Bu yöntem, verilerinizdeki her eşleşmeyi bulmak için döngü yapar ve ortalamayı hesaplar; bu da büyük veri setleri veya tekrarlanabilir bir iş akışı gerektiğinde uygundur.

Uygulanabilir senaryolar ve notlar: VBA, ortalama hesaplamasını sık sık yapmanız gereken, raporları otomatikleştirmek istediğiniz veya sıra dışı veri düzenlerine uyarlanabilen esnek bir yaklaşım gerektirdiğinizde idealdir. VBA makroları, çalışma kitabınızda makroları etkinleştirmekten rahat olduğunuzda ve özel çıktılar gerektirdiğinizde en iyi şekilde çalışır.

1. Geliştirici sekmesine gidin, Visual Basic'i seçin veya Alt + F11 tuşlarına basın VBA düzenleyicisini açmak için, ardından Ekle > Modül'e tıklayın. Aşağıdaki kodu yeni modüle kopyalayıp yapıştırın:

Sub AverageVlookupMatches()
    Dim lookupCol As Range
    Dim avgCol As Range
    Dim lookupValue As Variant
    Dim total As Double
    Dim count As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
    Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
    lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
    
    Application.ScreenUpdating = False
    total = 0
    count = 0
    
    For i = 1 To lookupCol.Rows.Count
        If lookupCol.Cells(i, 1).Value = lookupValue Then
            If IsNumeric(avgCol.Cells(i, 1).Value) Then
                total = total + avgCol.Cells(i, 1).Value
                count = count + 1
            End If
        End If
    Next i
    
    If count > 0 Then
        MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
    Else
        MsgBox "No matches found.", vbExclamation, "Result"
    End If
    
    Application.ScreenUpdating = True
End Sub

2. Kodu yapıştırdıktan sonra VBA düzenleyicisini kapatın. Makroyu çalıştırmak için Excel’e geri dönün, F5 tuşuna basın veya Çalıştır'a tıklayın. İstendiğinde, arama sütununu, ortalaması alınacak değer sütununu seçin ve arama değerini girin. Makro, hesaplanan ortalamayı bir ileti kutusunda gösterecektir.

Pratik ipuçları ve önlemler: Arama ve değer sütunlarınızın aynı sayıda satıra sahip olduğundan ve seçili alanlarda boş satır olmadığından emin olun. Hedef sütundaki sayısal olmayan değerler içeren girişler göz ardı edilecektir. En iyi otomasyon için, çalışma sayfanızın düzenine göre adlandırılmış aralıkları veya makro mantığını gerektiği gibi ayarlayın.

Sorun giderme: "Eşleşme bulunamadı" hatasıyla karşılaşırsanız, arama sütununuzdaki baştaki/sondaki boşlukları veya veri türü tutarsızlıklarını kontrol edin. Makroların çalıştırılması için etkinleştirildiğinden emin olun.

Excel'de ortalama/bileşik yıllık büyüme oranını hesaplayın

Excel'de hareketli/otomatik ortalama hesaplayın

Excel'de Pivot Tablo ile günlük/aylık/çeyrek saatlik ortalama alın