Excel'de bir sütundaki değerlere göre başka bir sütuna dayalı ortalama nasıl hesaplanır?
Pratik Excel senaryolarının çoğunda, bir sütundaki değerlerin ortalamasını, başka bir sütundaki eşleşen girdilere göre gruplayarak veya filtreleyerek hesaplamanız gerekir. Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi her satış temsilcisi veya bölge için ortalama satışları belirlemek isteyebilirsiniz. Bu tür hesaplamalar, özet raporlar, performans analizleri ve veri incelemelerinde sıkça kullanılır. Bu makalede, ihtiyaçlarınıza ve beceri seviyenize en uygun yaklaşımı seçmenizi sağlayacak çeşitli etkili yöntemi sizinle paylaşacağız.
Başka bir sütundaki aynı değere göre sütun ortalamasını formüllerle hesaplayın
Başka bir sütundaki aynı değere göre sütun ortalamasını Kutools for Excel ile hesaplayın
PivotTable kullanarak gruba göre ortalama hesaplayın
VBA makrosuyla gruplanmış ortalama hesaplamasını otomatikleştirin
Başka bir sütundaki aynı değere göre sütun ortalamasını formüllerle hesaplayın
Excel'de başka bir sütuna göre bir grubun ortalamasını hesaplamanın en basit yollarından biri, AVERAGEIF veya AVERAGEIFS gibi koşullu formüller kullanmaktır. Bu yöntem, özellikle «Owenton» gibi belirli bir şehir ya da satış temsilcisi için ortalama satışları hızlıca elde etmek istediğinizde oldukça işe yarar.
1. Sonucu görüntülemek istediğiniz boş bir hücre seçin, ardından aşağıdaki formülü girin ve Enter tuşuna basın:
=AVERAGEIF(B2:B13,E2,C2:C13)
Parametre açıklaması: Yukarıdaki formülde, B2:B13, kontrol edilecek kriterleri içeren aralıktır (örneğin şehir veya satış temsilcisi), E2, karşılaştırma yapılacak spesifik değerdir (örneğin «Owenton») ve C2:C13, ortalama hesaplamak istediğiniz sayısal değerleri içeren aralıktır.
Enter tuşuna bastıktan sonra, E2 hücresinde belirtilen grup (örneğin «Owenton») için anında ortalama satış değerini alırsınız.
Kriter sütunundaki her benzersiz değer için ortalama hesaplamak istiyorsanız, kriter hücresindeki (E2) değeri ilgili benzersiz değere göre güncelleyin veya benzersiz girdilerinizin bir listesi varsa formülü aşağı doğru kopyalayın.
Pratik ipucu: Daha büyük veri kümeleri veya çok sayıda benzersiz grup söz konusu olduğunda, bu formülü «Yinelenenleri Kaldır» aracı ya da Office 365 ve Excel 2021’deki UNIQUE işlevi gibi araçlarla oluşturulan benzersiz değerler listesiyle birleştirerek tüm grup ortalamalarını tek seferde hesaplama sürecini hızlandırabilirsiniz. Formülünüzdeki aralıkların tüm hedeflenen verileri kapsadığından ve formülü kopyalarken hizalı kalmasından emin olun.
Yaygın hatalar ve sorun giderme:
- #DIV/0! hatası alırsanız, kriter değerinizin gerçekten Aralık seç içinde görünüp görmediğini kontrol edin.
- Sayısal aralığınızın yalnızca geçerli sayılardan oluştuğundan emin olun; metin veya boş hücreler hesaplamayı etkileyebilir.
Başka bir sütundaki aynı değere göre sütun ortalamasını Kutools for Excel ile hesaplayın
Bir sütundaki tüm benzersiz değerler için formül girmek ya da manuel filtreleme yapmak zorunda kalmadan otomatik olarak ortalama hesaplamak istiyorsanız, Kutools for Excel bu görevi yerine getirmek üzere akıcı bir çözüm sunar. Bu özellik, özellikle büyük listelerle veya karmaşık veri kümeleriyle çalışırken son derece kullanışlıdır.
1. Ortalamasını almak istediğiniz grup sütunu ile sayısal sütunu içeren tüm veri aralığını seçin. Ardından Kutools > Birleştir ve Ayır > Gelişmiş Satırları Birleştir seçeneğine gidin.
2. Satırları Birleştir Sütuna Göreiletişim kutusunda şu adımları izleyin:
- Gruplamak istediğiniz sütunu seçin (örneğin Şehir veya Satış Temsilcisi) ve bunu gruplama alanı olarak tanımlamak için Birincil Anahtar düğmesine tıklayın.
- Ortalamasını almak istediğiniz sayısal sütunu seçin, ardından Hesapla > Ortalama seçeneğine tıklayın.
İpucu: Diğer sütunlar için (örneğin tarihler), değerleri nasıl birleştireceğinizi belirleyebilirsiniz (örneğin virgülle birleştirme). - İşlemi gerçekleştirmek için Tamam düğmesine tıklayın.
Kutools, seçtiğiniz anahtara göre verileri anında gruplandırır ve sayısal sütundaki her bir grup için ortalamayı hesaplar.
Kutools’un toplu işlem özelliği, aylık raporlar, departman özetleri veya diğer çoklu grup hesaplamaları gibi gruplanmış istatistikleri düzenli olarak analiz eden kullanıcılar için idealdir. Kutools, orijinal veri yapınızı bozmadan bir eklenti olarak çalışır ve önizleme işlevi sayesinde değişiklikleri uygulamadan önce gruplamaları inceleme imkanı sunar.
Not ve ipuçları:
- Araç çalıştırılmadan önce Aralık Seç içinde boş satır olmadığından emin olun.
- Gruplanmış ortalamaları başka bir yere taşımak istiyorsanız, hesaplama tamamlandıktan sonra sonuçları kopyalayıp yapıştırarak kolayca aktarabilirsiniz.
- Çok büyük veri kümelerinde karışıklığı önlemek için, gruplama ve sayısal sütunların doğru şekilde atandığından emin olun.
Kutools for Excel– Excel'i, işinizi daha hızlı ve kolay hale getiren 300 temel araçla güçlendirin ve daha akıllı veri işleme ile üretkenlik için yapay zeka özelliklerinden yararlanın.Hemen Edinin
PivotTable kullanarak gruba göre ortalama hesaplayın
PivotTable, formüllere veya üçüncü taraf eklentilere gerek kalmadan verileri özetlemek, gruplamak ve analiz etmek—gruplara göre ortalama dahil—için güçlü ve yerleşik bir çözüm sunar. Bu yöntem, farklı kategorilere göre ortalamaların ve toplamların etkileşimli bir görünümünü istediğinizde, hem küçük hem de çok büyük veri kümeleriyle çalışırken idealdir.
Grup ortalamalarını hesaplamak için PivotTable'nü nasıl yapılandırırsınız:
- Veri kümenizdeki herhangi bir hücreyi seçin, ardından Ekle > PivotTablo seçeneğine gidin. İletişim kutusunda PivotTable'nin nerede görünmesini istediğinizi (yeni çalışma sayfası veya mevcut çalışma sayfası) seçin ve Tamam düğmesine tıklayın.
- PivotTablo Alanları bölmesinde, gruplamak istediğiniz sütunu Satırlar alanına sürükleyin (örneğin «Şehir» veya «Satış Temsilcisi»).
- Ortalamasını almak istediğiniz sayısal sütunu (örneğin "Sales") Değerler alanına sürükleyin. Varsayılan olarak Excel toplamı hesaplar; bunu değiştirmek için değer alanına tıklayın, Değer Alan Ayarları seçeneğini seçin ve Ortalama’yı belirleyin.
PivotTable, her grup için ortalama değeri anında gösterir. Raporu ihtiyacınıza göre hızla filtreleyebilir, sıralayabilir ve biçimlendirebilirsiniz. Bu yöntem kullanıcı dostudur ve formül hataları riskini tamamen ortadan kaldırır.
Avantajlar: Etkileşimlidir, büyük veri hacimlerini zarifçe yönetir ve birden fazla istatistiği bir arada özetleyebilir.
Dezavantajlar: Sonuç, basit bir liste değil, PivotTable formatında gösterilir; kaynak veri değişirse ara sıra yenileme gerektirir.
İpucu: PivotTable’daki herhangi bir özet hücreye çift tıkladığınızda, o grup için alttaki verileri içeren yeni bir çalışma sayfası açılır; böylece ayrıntıları incelemek veya tutarsızlıkları gidermek çok daha kolay hale gelir.
Yaygın sorunlar:
- Ortalama alamıyorsanız, Değer Alanı Ayarları bölümünde değer alanının «Ortalama» olarak ayarlandığından emin olun.
- Kaynak verinizi, PivotTable’de düzen sorunlarına yol açabilecek fazladan boş satır veya sütunlar açısından gözden geçirin.
VBA makrosuyla gruplanmış ortalama hesaplamasını otomatikleştirin
Sık sık çok sayıda grup için ortalama hesaplamak veya özeti otomatik olarak oluşturmak isteyen kullanıcılar için bir VBA makrosu yazmak, manuel çabayı önemli ölçüde azaltabilir. VBA, veri yapınızın tutarlı kalmasını beklediğinizde ya da tek bir düğmeye tıklayarak tekrarlanan özet raporlar üretmek istediğinizde özellikle değerlidir.
Başlamadan önce çalışma kitabınızı kaydettiğinizden ve makroların etkin olduğundan emin olun. İşte başlamanın yolu:
1. VBA düzenleyicisini açmak için Geliştirici Araçları > Visual Basic seçeneğine tıklayın. Düzenleyicide, yeni bir kod modülü oluşturmak için Ekle > Modül seçeneğine tıklayın. Aşağıdaki kodu bu modüle yapıştırın:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Kodu ekledikten sonra VBA düzenleyicisini kapatın ve Excel’e dönün. Alt+F8 tuşlarına basın, listeden GroupAverageSummary makrosunu seçin ve Çalıştır düğmesine tıklayın. Makro, gruplama (kriter) sütununuzu ve sayısal değer sütununuzu seçmenizi isteyecektir. Seçimlerinizi yaptıktan sonra, her benzersiz grubun ve ilgili ortalama değerlerinin yer aldığı «Group Average Summary» adlı yeni bir çalışma sayfası otomatik olarak oluşturulacaktır.
Parametre ve işlem notları:
- Grup ve değer sütunlarınızın aynı uzunlukta olduğundan ve geçerli veri içerdiğinden emin olun (kısmi seçimlerden kaçının).
- Bu makro, gerektiğinde daha gelişmiş gruplamalar veya ek özet istatistikler elde etmek için özelleştirilebilir.
- Çalışma sayfanızda zaten «Grup Ortalama Özeti» adında bir çalışma sayfası varsa, makro varsayılan çalışma sayfası adını kullanarak yeni bir çalışma sayfası oluşturur.
Sorun giderme:
- «Dizin aralık dışında» veya benzeri bir hata mesajıyla karşılaşırsanız, seçim aralıklarınızın doğru şekilde hizalandığından ve aynı çalışma sayfasında bulunduğundan emin olun.
- En iyi sonuçlar için, değer sütununun yalnızca sayısal veri içerdiğinden emin olun—sayısal aralıkta yer alan metin veya boş hücreler makro tarafından atlanacaktır.
Bu makro, toplu işlemlerinizde, otomatik raporlama süreçlerinizde veya yeni/güncellenmiş veri kümelerini sık sık özetlemeniz gerektiğinde idealdir.
Demo: Başka bir sütundaki aynı değerlere göre bir sütundaki ortalamayı Kutools for Excel ile hesaplayı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