Excel PivotTable'da ağırlıklı ortalama nasıl hesaplanır?
Excel'de verilerinizin sonuca eşit katkı sağlamadığı durumlarda ağırlıklı ortalama hesaplamak sık karşılaşılan bir gereksinimdir. Basit Aralıklar için, SUMPRODUCT ve SUM fonksiyonları ile hızlıca sonuç alabilirsiniz. Ancak PivotTable kullanırken, hesaplanmış alanlarda bu fonksiyonlar doğrudan desteklenmez. Dolayısıyla, PivotTable içinde ağırlıklı ortalama hesaplamak istediğinizde işler karmaşıklaşabilir. Bu sınırlamaları anlamak ve alternatif yöntemler öğrenmek, farklı senaryolarda verilerinizi etkili şekilde özetlemenizi sağlar. Bu makalede, PivotTable'da ağırlıklı ortalama hesaplamanın farklı yolları, klasik çözümler ve Excel'in yeni sunduğu özellikler ele alınacaktır.
Excel PivotTable'da ağırlıklı ortalama hesaplama
VBA Kodu - PivotTable'da Ağırlıklı Ortalama Hesabını Otomatikleştirme
Power Pivot (Veri Modeli) - DAX Kullanarak PivotTable'da Ağırlıklı Ortalama Hesaplama
Excel PivotTable'da ağırlıklı ortalama hesaplama
Çeşitli meyvelere ait satış verilerini gösteren, şu gibi sütunları olan bir tablonuz olduğunu düşünelim: Meyve, Ağırlık, ve Birim başına fiyat, ve bu değerleri özetleyen bir PivotTable oluşturduysanız, aşağıdaki gibi görünür.
Her meyve için ağırlıklı ortalama fiyatı hesaplamanız gerektiğinde—yani, her bir veri noktasının ağırlığına göre doğru katkısını yansıtmak istediğinizde—PivotTable'lar, Hesaplanmış Alan'da SUMPRODUCT veya benzeri gelişmiş fonksiyonların doğrudan kullanımına izin vermez. Aşağıdaki manuel yöntem, kaynak verinize yardımcı bir sütun ekleyerek ve PivotTable'ın yerleşik seçenekleriyle ağırlıklı ortalamayı türeterek bu sınırlamanın üstesinden gelir.
1. İlk olarak, kaynak verinize ' Tutar isimli yardımcı bir sütun ekleyin.
Yeni bir boş sütun oluşturun, başlığını Tutarolarak belirleyin ve ilk satıra (ör. C2) şu formülü yazın: =D2*E2 (burada D2 ağırlık sütununu, E2 birim fiyat sütununu ifade eder—başlıklarınıza uygun şekilde uyarlayın). Sonrasında, dolgu tutamacını aşağı sürükleyerek formülü tüm satırlara uygulayın. Bu adım, her ürünün ağırlığını birim fiyatla çarparak o ürün için toplam ağırlıklı fiyatı elde etmenizi sağlar. Ekran görüntüsüne bakınız:
İpuçları:
- Kaynak tablonuzda birleştirilmiş hücreler olmamasına dikkat edin; bu, formül hatalarına neden olabilir.
- Geniş veri setlerinde, formülün ilgili tüm satırlara uygulandığından emin olun.
- Sütun atamaları değişirse, formülü buna göre güncelleyin.
2. Son olarak, PivotTable'ı yeni eklenen yardımcı sütunu yansıtacak şekilde güncelleyin. PivotTable içinde herhangi bir hücreyi seçin, ardından açılan PivotTable Araçları bağlamsal sekmesine gelin. Burada Çözümle (veya Seçenekler, Excel sürümünüze bağlı olarak) > Yenileseçeneğini tıklayın. Bu adım, yeni Tutar alanının PivotTable Alan Listesinde görünmesini sağlar.
3. Hesaplanmış ağırlıklı ortalama alanı eklemek için, Çözümle > Alanlar, Öğeler ve Kümeler > Hesaplanmış Alan yolunu izleyin. Açılan Hesaplanmış Alan Ekle iletişim kutusunda özel hesaplamanızı ayarlayın.
Not: Hesaplanmış Alan, verinizde zaten tanımlanmış alanları kullanır. Bu adımdan önce gerekli tüm sütunların eklendiğinden ve güncellenmiş olduğundan emin olun.
4. Hesaplanmış Alan Ekle iletişim kutusunda, Ağırlık Ortalama (veya farklı bir isim) Ad kutusuna girin. Formül alanına şunu yazın: =Tutar/Ağırlık. Kaynak verinizdeki alan adlarını tam ve büyük/küçük harf duyarlı olarak kullanmaya dikkat edin. Ardından, Tamam diyerek hesaplanmış ağırlık alanını ekleyin.
Sorun giderme:
- Eğer #BÖL/0! hataları görüyorsanız, ağırlık değerlerinizde sıfır olmadığını kontrol edin.
- Hesaplanmış alan görünmüyorsa, alan adlarının yazımı ve büyük/küçük harf uyumunu kontrol edin.
Her meyve türü için ağırlıklı ortalama fiyatı artık PivotTable'ın ara toplam satırlarında görünecektir. Sonuç, ortalama fiyat hesabının her girdinin ağırlığına olan etkisini doğru biçimde yansıtmasını sağlar.
Avantajları: Eski Excel sürümleriyle uyumludur; eklenti veya gelişmiş bir özellik gerektirmez.
Dezavantajları: Kaynak veriye yardımcı sütun eklenmesini gerektirir; veri güncellendiğinde hesaplamalar daha az dinamik olabilir.
Pratik ipucu: Düzenli raporlama ihtiyacınız varsa, yardımcı sütun formülünü dinamik tutmayı veya makro ile otomatik güncellemeyi değerlendirin.
Power Pivot (Veri Modeli) - DAX Kullanarak PivotTable'da Ağırlıklı Ortalama Hesaplama
Excel'in modern sürümlerinde, Power Pivot eklentisi (diğer adıyla Veri Modeli) sayesinde DAX (Veri Analizi İfadeleri) ile yeni hesaplama seçenekleri mümkün olur. Böylece, asıl tablonuza ek sütunlar eklemeden doğrudan PivotTable içinde ağırlıklı ortalama hesaplayabilirsiniz.
Uygulama senaryosu: Özellikle büyük veri kümeleri veya ilişkili tablolarla çalışırken ve hesaplamaların veri güncellendiğinde otomatik yenilenmesini istediğinizde idealdir. Bu yöntem, kaynak tablonuzu temiz tutmak istediğiniz iş analizleri ve paneller için de çok uygundur.
Talimatlar:
- Power Pivot eklentisini etkinleştirin Dosya > Seçenekler > Eklentiler yoluna gidin. Yönet açılır menüsünden COM Eklentileri'ni seçin, Git düğmesine tıklayın ve Power Pivot'u işaretleyin.
- Power Pivot'a veri ekleyin Çalışma sayfasında tablonuzu seçin, ardından Power Pivot > Yönet yolunu izleyerek Power Pivot penceresini açın.
- Power Pivot'tan PivotTable oluşturun Power Pivot penceresinde, Giriş > PivotTable yolunu izleyin.
Daha sonra yerleştirme konumunu (ör. Mevcut çalışma sayfası) seçin ve Tamam'a tıklayın.
- PivotTable'ı oluşturun ve bir ölçü ekleyin Yeni oluşturulan PivotTable alan listesinde, alanları uygun bölümlere sürükleyin. Ardından tablo adına sağ tıklayıp Ölçü Ekle'yi seçin.
- Ölçüyü tanımlayın Ölçü iletişim kutusunda:
- Ölçüye bir ad verin (örnek: Ağırlıklı Ortalama Fiyat).
- Ağırlıklı ortalama için aşağıdaki DAX ifadesini girin.
=SUMX(Table1, Table1[Weight] * Table1[Price]) / SUM(Table1[Weight])(Table1, [Ağırlık] ve [Fiyat] değerlerini kendi tablonuz ve alan adlarınız ile değiştirin.) - Ekle'yi tıklayarak işlemi tamamlayın.
- Ölçüyü PivotTable'da kullanın Eklenen ölçü alan listesinde görünecek ve diğer alanlar gibi Değerler bölümüne sürüklenebilir.
İpuçları ve sorun giderme:
- DAX formülleri büyük/küçük harf duyarlı değildir ancak alan/tablo adları modelinizdekiyle aynı olmalıdır.
- Alt veri değiştirildiğinde ölçü PivotTable'da otomatik olarak yenilenir.
- Boş veya beklenmeyen sonuçlar alıyorsanız, ağırlık değerlerinde sıfır veya eksik olup olmadığını ve veri modelinin uygun şekilde yenilendiğini kontrol edin.
Avantajları: Kaynak veride değişiklik gerektirmez; hesaplamalar veri değiştikçe anında güncellenir ve ileri düzey özetlemeleri destekler.
Dezavantajları: Power Pivot tüm Excel sürümlerinde mevcut değildir ve başlangıçta ek kurulum gerekebilir; DAX konusunda deneyimi olmayan kullanıcılar için öğrenme eğrisi oluşturabilir.
Kutools AI ile Excel Sihirini Keşfedin
- Akıllı Yürütme: Hücre işlemleri gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla sürülür.
- Özel Formüller: İş akışlarınızı hızlandırmak için özel formüller oluşturun.
- VBA Kodlama: VBA kodunu kolayca yazın ve uygulayın.
- Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
- Metin Çevirisi: Elektronik tablolarınız içindeki dil engellerini aşın.
İlgili makaleler:
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin