✅ Excel Pivot Tablosu Hesaplanan Alanı Nasıl Eklenir ve Kullanılır

İçindekiler

Özet Tablo Hesaplanan Alanı nedir?
Özet Tabloya Hesaplanmış Alan Ekleme
Pivot Tablo Hesaplanan Alanlarla İlgili Bir Sorun
Bir Özet Tablo Hesaplanan Alanı Nasıl Değiştirilir veya Silinir?
Hesaplanan Tüm Alan Formüllerinin Listesi Nasıl Alınır?

Çoğu zaman, bir Özet tablo oluşturduğunuzda, analizinizi genişletmeniz ve bunun bir parçası olarak daha fazla veri/hesaplama eklemeniz gerekir.

Pivot Tablodaki mevcut veri noktalarını kullanarak elde edilebilecek yeni bir veri noktasına ihtiyacınız varsa, geri dönüp kaynak verilere eklemeniz gerekmez. Bunun yerine, bir Pivot Tablo Hesaplanan Alan Bunu yapmak için.

Veri kümesini indirin ve takip edin.

Özet Tablo Hesaplanan Alanı nedir?

Temel bir Pivot Tablo örneği ile başlayalım.

Bir perakendeci veri kümeniz olduğunu ve aşağıda gösterildiği gibi bir Pivot Tablo oluşturduğunuzu varsayalım:

Yukarıdaki Özet Tablo, perakendeciler için satış ve kar değerlerini özetlemektedir.

Şimdi, bu perakendecilerin kar marjının ne olduğunu da bilmek istiyorsanız (kar marjının "Kar" bölü "Satış" olduğu yerde).

Bunu yapmak için bir çift yol var:

Orijinal veri kümesine geri dönün ve bu yeni veri noktasını ekleyin. Böylece kaynak verilere yeni bir sütun ekleyebilir ve içindeki kar marjını hesaplayabilirsiniz. Bunu yaptıktan sonra, bu yeni sütunu bunun bir parçası olarak almak için Pivot Tablonun kaynak verilerini güncellemeniz gerekir.
- Bu yöntem bir olasılık olsa da, veri kümesine manuel olarak geri dönmeniz ve hesaplamaları yapmanız gerekir. Örneğin, birim başına ortalama satışı (Satış/Miktar) hesaplamak için başka bir sütun eklemeniz gerekebilir. Yine bu sütunu kaynak verilerinize eklemeniz ve ardından pivot tabloyu güncellemeniz gerekecek.
- Bu yöntem, siz ona yeni veriler eklerken Pivot Tablonuzu da şişirir.
Pivot Tablonun dışına hesaplamalar ekleyin. Pivot Tablo yapınızın değişmesi olası değilse bu bir seçenek olabilir. Ancak Pivot tablosunu değiştirirseniz, hesaplama buna göre güncellenmeyebilir ve size yanlış sonuçlar veya hatalar verebilir. Aşağıda gösterildiği gibi, satırda perakendeciler varken Kar Marjını hesapladım. Ama müşteriden bölgelere çevirdiğimde formül hata verdi.
Bir Özet Tablo Hesaplanan Alanı Kullanma. bu en verimli yol mevcut Pivot Tablo verilerini kullanmak ve istenen metriği hesaplamak için. Hesaplanan Alanı, Pivot Tablodaki mevcut sütunları kullanarak eklediğiniz sanal bir sütun olarak düşünün. Bir Özet Tablo Hesaplanmış Alanı kullanmanın birçok faydası vardır (birazdan göreceğimiz gibi):
- Formülleri işlemenizi veya kaynak verileri güncellemenizi gerektirmez.
- Pivot Tablonuza ekleyebileceğiniz tüm yeni verileri otomatik olarak hesaba katacağı için ölçeklenebilir. Bir Hesaplama Alanı eklediğinizde, bunu Pivot Tablonuzdaki diğer herhangi bir alan gibi kullanabilirsiniz.
- Güncellemek ve yönetmek kolaydır. Örneğin, metrikler değişirse veya hesaplamayı değiştirmeniz gerekirse, bunu Pivot Tablonun kendisinden kolayca yapabilirsiniz.

Özet Tabloya Hesaplanmış Alan Ekleme

Mevcut bir Özet Tabloya bir Özet Tablo Hesaplanan Alanının nasıl ekleneceğini görelim.

Aşağıda gösterildiği gibi bir Özet Tablonuz olduğunu ve her perakendeci için kar marjını hesaplamak istediğinizi varsayalım:

Özet Tablo Hesaplanan Alanı ekleme adımları şunlardır:

Pivot Tabloda herhangi bir hücreyi seçin.
Özet Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler'e gidin.
Açılır menüden Hesaplanan Alan'ı seçin.
Hesaplanan Dosyalanmış Ekle iletişim kutusunda:
- Ad alanına girerek bir ad verin.
- Formül alanında, hesaplanan alan için istediğiniz formülü oluşturun. Aşağıda listelenen alan adlarından seçim yapabileceğinizi unutmayın. Bu durumda formül '= Kar/ Satış' şeklindedir. Alan adlarını manuel olarak girebilir veya Alanlar kutusunda listelenen alan adına çift tıklayabilirsiniz.
Ekle'ye tıklayın ve iletişim kutusunu kapatın.

Hesaplanan Alan'ı ekler eklemez, Özet Tablo Alanları listesindeki alanlardan biri olarak görünecektir.

Artık bu hesaplanan alanı herhangi bir Pivot Tablo alanı olarak kullanabilirsiniz (Pivot Tablo Hesaplanan Alanını bir rapor filtresi veya dilimleyici olarak kullanamayacağınızı unutmayın).

Daha önce de belirttiğim gibi, bir Özet Tablo Hesaplanan Alanı kullanmanın yararı, Özet Tablonun yapısını değiştirebilmeniz ve otomatik olarak ayarlanmasıdır.

Örneğin, satırlar alanına bölgeyi sürükleyip bırakırsam, bölge için olduğu kadar perakendeciler için de Kar Marjı değerinin raporlandığı aşağıdaki gibi bir sonuç elde edeceksiniz.

Yukarıdaki örnekte, hesaplanmış bir alan eklemek için basit bir formül (=Kar/Satış) kullandım. Bununla birlikte, bazı gelişmiş formüller de kullanabilirsiniz.

Size bir Özet Tablo Hesaplama Alanı oluşturmak için gelişmiş bir formül kullanma örneğini göstermeden önce, bilmeniz gereken bazı şeyler şunlardır:

Bir Özet Tablo Hesaplanmış Alanı oluştururken referansları veya adlandırılmış aralıkları KULLANAMAZSINIZ. Bu, DÜŞEYARA, INDEX, OFFSET vb. gibi birçok formülü ekarte eder. Ancak referanssız çalışabilen formüller kullanabilirsiniz (SUM, IF, COUNT vb. gibi).
Formülde bir sabit kullanabilirsiniz. Örneğin, %10 oranında artmasının beklendiği tahmini satışları bilmek istiyorsanız, =Satış*1.1 formülünü kullanabilirsiniz (1.1 sabittir).
Hesaplanan alanı oluşturan formülde öncelik sırası takip edilir. En iyi uygulama olarak, öncelik sırasını hatırlamanız gerekmediğinden emin olmak için parantez kullanın.

Şimdi, Hesaplanmış Alan oluşturmak için gelişmiş bir formül kullanma örneğini görelim.

Aşağıda gösterildiği gibi veri kümeniz olduğunu ve Pivot Tablosunda tahmini satış değerini göstermeniz gerektiğini varsayalım.

Tahmin edilen değer için, büyük perakendeciler için %5'lik bir satış artışı (3 milyonun üzerindeki satışlar) ve küçük ve orta ölçekli perakendeciler için %10'luk bir satış artışı (3 milyonun altındaki satışlar) kullanmanız gerekir.

Not: Buradaki satış numaraları sahtedir ve bu eğitimdeki örnekleri göstermek için kullanılmıştır.

Bunu nasıl yapacağınız aşağıda açıklanmıştır:

Pivot Tabloda herhangi bir hücreyi seçin.
Özet Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler'e gidin.
Açılır menüden Hesaplanan Alan'ı seçin.
Hesaplanan Dosyalanmış Ekle iletişim kutusunda:
- Ad alanına girerek bir ad verin.
- Formül alanında aşağıdaki formülü kullanın: =EĞER(Bölge =”Güney”,Satış *1.05,Satış *1.1)
Ekle'ye tıklayın ve iletişim kutusunu kapatın.

Bu, satış tahmini değeriyle birlikte pivot tabloya yeni bir sütun ekler.

Veri setini indirmek için buraya tıklayın.

Pivot Tablo Hesaplanan Alanlarla İlgili Bir Sorun

Hesaplanan Alan, her şeyi ölçeklenebilir ve yönetilebilir tutarken, alan hesaplamalarıyla Pivot Tablonuzun değerini gerçekten artıran harika bir özelliktir.

Ancak, Pivot Tablo Hesaplanan Alanları ile ilgili, kullanmadan önce bilmeniz gereken bir sorun vardır.

Tahmini satış rakamlarını almak için hesaplanan alanı kullandığım aşağıda gösterildiği gibi bir Özet Tablom olduğunu varsayalım.

Ara toplam ve genel toplamların doğru olmadığını unutmayın.

Bunların her perakendeci için bireysel satış tahmini değerini eklemesi gerekirken, gerçekte, oluşturduğumuz aynı hesaplanmış alan formülünü izler.

Yani Güney Toplam için, değer 22.824.000 olması gerekirken, Güney Toplamı yanlış bir şekilde 22.287.000 olarak bildiriyor. Bu, değeri elde etmek için 21,225,800*1,05 formülünü kullandığı için gerçekleşir.

Ne yazık ki, bunu düzeltmenin bir yolu yok.

Bunu ele almanın en iyi yolu, Pivot Tablonuzdan ara toplamları ve Genel Toplamları kaldırmaktır.

Debra'nın bu sorunu çözmek için gösterdiği bazı yenilikçi geçici çözümleri de inceleyebilirsiniz.

Bir Özet Tablo Hesaplanan Alanı Nasıl Değiştirilir veya Silinir?

Bir Özet Tablo Hesaplanan Alanı oluşturduktan sonra, aşağıdaki adımları kullanarak formülü değiştirebilir veya silebilirsiniz:

Pivot Tabloda herhangi bir hücreyi seçin.
Özet Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler'e gidin.
Açılır menüden Hesaplanan Alan'ı seçin.
Ad alanında, açılır oku tıklayın (alanın sonundaki küçük aşağı ok).
Listeden silmek veya değiştirmek istediğiniz hesaplanan alanı seçin.
Formülü değiştirmek isterseniz değiştirin veya silmek isterseniz Sil'e tıklayın.

Hesaplanan Tüm Alan Formüllerinin Listesi Nasıl Alınır?

Çok fazla Özet Tablo Hesaplanan alanı oluşturursanız, her birinde kullanılan formülü takip etme konusunda endişelenmeyin.

Excel, Hesaplanmış Alanlar oluştururken kullanılan tüm formüllerin listesini hızlı bir şekilde oluşturmanıza olanak tanır.

Tüm Hesaplanan Alanlar formüllerinin listesini hızlı bir şekilde alma adımları şunlardır:

Pivot Tabloda herhangi bir hücreyi seçin.
Özet Tablo Araçları -> Analiz Et -> Alanlar, Öğeler ve Kümeler -> Formülleri Listele'ye gidin.

Formülleri Listele'ye tıkladığınız anda Excel, Özet Tabloda kullandığınız tüm hesaplanmış alanların/öğelerin ayrıntılarını içeren yeni bir çalışma sayfasını otomatik olarak ekler.