Excel'de birden fazla VLOOKUP sonucunun ortalaması nasıl alınır?

Aynı arama öğesiyle ilişkili birden fazla değeri bulup ortalamak istediğinizde, doğrudan bir formül kullanmak en hızlı ve esnek yollardan biridir. ORTALAMA.EĞER işlevi ya da bir dizi formülü, ekstra sütunlar oluşturmadan bu işlemi 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. Böylece A sütunundaki E2 hücresindeki arama değerinizle eşleşen tüm satırların C sütunundaki değerlerinin ortalaması anında hesaplanır. Aşağıdaki görsel örneğe göz atı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: Eşleşen değerlerin ortalamasını almak istediğiniz aralık.

Alternatif yaklaşım (dizi formüllerine alışkın kullanıcılar için):

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

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

Dizi formülleri, her karşılaştırmayı ayrı ayrı işler; bu da dinamik dizileri desteklemeyen Excel sürümlerinde büyük avantaj sağlar. Hataları önlemek için aralıkların tam olarak aynı boyutta olduğundan emin olun.

Pratik senaryolar ve notlar:
- Filtrelenmemiş ve basit arama gereksinimlerine sahip veri kümeleri için en ideal seçenektir.
- Aralıklardan biri boş hücreler içeriyorsa, bu hücreler ortalama hesaplamasında göz ardı edilir.
- Daha sağlam formüller elde etmek ve veri eklerken esneklik kazanmak için dinamik tablolarda tablo başvurularını kullanmayı düşünün.
- Hücre aralıklarının yanlış eşleştirilmesi, yanlış ortalama sonuçlarına veya hatalara yol açan yaygın bir hatadır—buna dikkat edin.

Yinelenen değerlere göre verileri özetlemek ve toplamak sık ihtiyaç duyulan bir işlemdir. İşte bu noktada,Kutools for Excel, çözüm olarak Gelişmiş Satırları Birleştiryardımcı programını sunar. Bu araç, eşleşen kayıtlar için ortalama, toplam veya sayı gibi değerleri tek adımda hızlıca birleştirebilir ya da hesaplayabilir; bu da büyük veri kümeleri veya düzenli raporlar için son derece uygundur.

1. Arama sütununu ve ortalamasını almak istediğiniz değerleri içeren veri tablosunun aralığını seçin. Ardından Kutools > İçerik > Gelişmiş Satırları Birleştir yolunu izleyin. Ekran görüntüsüne bakın:

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

• Arama değeri aralığınızı içeren sütunu seçin ve Birincil Anahtar seçeneğine tıklayın.
• Hedef değerlerinizi içeren sütunu seçin, ardından Hesapla > Ortalama seçeneğine tıklayın.
• Diğer sütunlar için gerektiğinde metin birleştirme (örneğin virgülle) veya toplam, maksimum ya da minimum gibi hesaplama kurallarını ayarlayın.

3. Ayarları uygulamak için Tamam düğmesine tıklayın.

Yinelenen “Arama değeri aralığı”na sahip satırlar artık birleştirildi ve belirlenen sütundaki değerler, her benzersiz arama değeri için otomatik olarak ortalamalandı. Bu özellik, özellikle özet raporları oluşturmak veya verileri yoğunlaştırmak için son derece kullanışlıdır.

Pratik ipucu: Gelişmiş Satırları Birleştir özelliği, manuel hesaplamaları ve hata riskini en aza indirir. Bu araç, tekrarlayan Arama değeri aralığı içeren verileri düzenli olarak işleyen ve hızlıca eyleme dönüştürülebilir özetler isteyen kullanıcılar için idealdir. Birleştirme işleminden önce, özellikle veri yapısı değiştiyse, doğru sütunların atandığından emin olun.

Kutools for Excel kullanarak birden fazla VLOOKUP sonucunun ortalamasını alma örneği

PivotTable, verileri özetlemek ve analiz etmek için dinamik ve görsel bir yaklaşım sunar. PivotTable kullanarak, girdileri arama değerlerine göre otomatik olarak gruplandırabilir ve her grup için hedef sütunun ortalamasını interaktif bir özet halinde görüntüleyebilirsiniz—bu özet, verileriniz değiştiğinde anında güncellenir.

En etkili senaryolar: Bu yaklaşım, tek bir arama değerine odaklanmak yerine tüm Arama değeri aralığına dair genel bir özet gerektiğinde idealdir. PivotTable ayrıca, hızlı veri keşfi yapmak, rapor oluşturmak ve sonuçlarınızı sıralanabilir, genişletilebilir bir biçimde sunmak istediğinizde mükemmeldir.

Talimatlar:

• Başlıkları da içerecek şekilde tüm veri kümenizi seçin.
• Ekle > Özet Tablo > Tablodan veya Aralıktan yolunu izleyin. PivotTable’yi yeni bir çalışma sayfasına mı yoksa mevcut bir çalışma sayfasına mı yerleştirmek istediğinizi seçin.
• Özet Tablo Alanları bölmesinde, arama değer aralığınızı 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ıp Değer Alan Ayarları seçeneğini belirleyin ve hesaplama türünü Ortalama olarak ayarlayın.

Sonuç olarak, her benzersiz arama değeri ve ilişkili verilerin ortalamasıyla bir özet tablo oluşturulur. İhtiyacınıza göre gruplamayı kolayca değiştirebilir, filtreleyebilir veya ayrıntılara inebilirsiniz.

Avantajlar: Formül gerektirmez, dinamik güncellemeleri destekler ve raporlama ile veri keşfi için idealdir.

Dezavantajlar: Veriler değiştiğinde yenilemek için ek adımlar gerekir, tek bir değeri doğrudan diğer formüllere aktarmak için pek uygun değildir ve ilk kurulum, Özet Tablolar hakkında temel bilgi gerektirir.

Sorun Giderme İpuçları: Değerler ortalama yerine sayı veya toplam olarak görünüyorsa, alan hesaplama ayarlarını kontrol edin. En iyi sonuçlar için sütunların uygun başlıklara sahip olduğundan ve PivotTable oluşturmadan önce yinelenen sütun adlarının kaldırıldığından emin olun.

Gelişmiş kullanıcılar ve düzenli olarak güncellenen verilerle çalışanlar için, VBA makrosu kullanmak arama değerine eşleşen tüm girdiler üzerinde ortalama işlemini otomatikleştirmenize olanak tanır. Bu yöntem, verilerinizdeki her eşleşmeyi bulmak ve ortalamayı hesaplamak üzere döngüsel olarak çalışır; bu da büyük veri kümeleri veya tekrarlanabilir bir iş akışı gerektiğinde uygundur.

Uygulanabilir senaryolar ve notlar: VBA, ortalama hesaplamalarını sık sık yapmanız, raporları otomatikleştirmeniz veya sıra dışı veri düzenlerine uyarlanmış esnek bir yaklaşım istemeniz durumunda idealdir. VBA makroları, çalışma kitabınızda makroları etkinleştirmeye alışkın olduğunuz ve özelleştirilmiş çıktılar gerektirdiğinizde en iyi şekilde çalışır.

1. Geliştirici sekmesine gidin, Visual Basic seçeneğini tıklayın veya VBA düzenleyicisini açmak için Alt + F11 tuşlarına basın. Ardından Ekle > Modül yolunu izleyin ve 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 dönün, F5 tuşuna basın veya Çalıştır seçeneğine tıklayın. İstendiğinde arama sütununu ve ortalaması alınacak değer sütununu seçin, ardından 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 satır sayılarının aynı olduğundan ve seçili alanlarda boş satır bulunmadığından emin olun. Hedef sütundaki sayısal olmayan değerlere sahip girdiler göz ardı edilecektir. En iyi otomasyon için, çalışma sayfası düzeninize uygun şekilde adlandırılmış aralıkları veya makro mantığını gerektiği gibi ayarlayın.

Sorun Giderme: "Eşleşme bulunamadı" hatası alırsanız, arama sütununuzdaki baştaki ve sondaki boşlukları veya veri türü tutarsızlıklarını kontrol edin. Makroların çalıştırılabilmesi 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/kayan ortalamayı hesaplayın

Excel'de PivotTable ile gün/ay/çeyrek/saat başına ortalama alın