Excel PivotTable Hesaplanan Alanları Ekleme ve Kullanma

Tipik olarak, bir pivot tablo oluşturduğunuzda, analizinizi genişletmeniz ve ona daha fazla veri/hesaplama eklemeniz gerekir.

Pivot tablodaki mevcut bir veri noktası kullanılarak elde edilebilecek yeni bir veri noktasına ihtiyacınız varsa, geri dönüp kaynak verilere eklemenize gerek yoktur.Bunun yerine, kullanabilirsinizPivotTable Hesaplanan AlanlarıBunu yapmak için.

PivotTable Hesaplanan Alanı nedir?

Temel bir pivot tablo örneği ile başlayalım.

Bir perakendeci veri kümeniz olduğunu ve bunun gibi bir pivot tablo oluşturduğunuzu varsayalım:

Yukarıdaki pivot tablo, perakendecinin satış ve kar değerlerini özetlemektedir.

Şimdi, bu perakendecilerin kar marjlarının ne olduğunu da bilmek istiyorsanız (kar marjı "kâr" bölü "satışlar"dır)?

Bunu yapmanın birkaç yolu vardır:

1. 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.Bu yapıldıktan sonra, bu yeni sütunu bunun bir parçası olarak almak için pivot tablonun kaynak verilerini güncellemeniz gerekir.
   • Bu yaklaşım işe yararken, veri kümesine manuel olarak geri dönmeniz ve hesaplamaları yapmanız gerekir.Örneğin, birim başına ortalama satışları (satış/miktar) hesaplamak için başka bir sütun eklemek isteyebilirsiniz.Yine, bu sütunu kaynak verilere eklemeniz ve ardından pivot tabloyu güncellemeniz gerekir.
   • Bu yöntem, siz ona yeni veriler ekledikçe pivot tablonuzu da şişirir.
2. Pivot tabloların dışına hesaplamalar ekleyin.Pivot tablo yapınızın değişmesi olası değilse bu seçeneği seçin.Ancak, pivot tabloyu değiştirirseniz, hesaplamalar buna göre güncellenmeyebilir ve size yanlış sonuçlar veya hatalar verebilir.Aşağıda gösterildiği gibi, sırada perakendeciler varken kar marjını hesapladım.Ama bunu müşteriden bölgeye değiştirdiğimde formül yanlış gidiyor.
3. Pivot tabloları kullanarak alanları hesaplayın.buMevcut PivotTable verilerini kullanın veen verimli yol.Hesaplanan alanları, pivot tablonuzdaki mevcut sütunları kullanarak eklediğiniz sanal sütunlar olarak düşünün.Pivot tablo hesaplanan alanları kullanmanın birçok faydası vardır (daha sonra göreceğimiz gibi):
   • Formüllerle çalışmanızı veya kaynak verileri güncellemenizi gerektirmez.
   • Genişletilebilir, çünkü pivot tabloya ekleyebileceğiniz tüm yeni verileri otomatik olarak hesaba katar.Hesaplanan bir alan ekledikten sonra, onu pivot tablodaki diğer herhangi bir alan gibi kullanabilirsiniz.
   • Güncellemek ve yönetmek kolaydır.Örneğin, bir metrik değişirse veya bir hesaplamayı değiştirmeniz gerekirse, bunu pivot tablonun kendisinden kolayca yapabilirsiniz.

Özet tabloya hesaplanan alanlar ekleyin

Mevcut bir pivot tabloya bir pivot tablo hesaplanan alanının nasıl ekleneceğini görelim.

Şuna benzeyen bir pivot tablonuz olduğunu ve her perakendeci için kar marjını hesaplamak istediğinizi varsayalım:

PivotTable hesaplanmış alanı ekleme adımları şunlardır:

Hesaplanan alanı ekledikten sonra Özet Tablo Alanları listesindeki alanlardan biri olarak görünecektir.

Artık bu hesaplanan alanı başka herhangi bir pivot tablo alanı olarak kullanabilirsiniz (bir pivot tablo hesaplanan alanını rapor filtresi veya dilimleyici olarak kullanamayacağınızı unutmayın).

Daha önce de belirttiğim gibi, pivot tablo hesaplanan alanı kullanmanın faydası, pivot tablonun yapısını değiştirebilmeniz ve otomatik olarak ayarlayacaktır.

Örneğin bir bölgeyi satır alanına sürükleyip bırakırsam, perakendeci ve bölge için kar marjı değerlerinin raporlandığı aşağıdaki gibi sonuçlar elde edeceksiniz.

 

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

Size bir PivotTable hesaplanmış alanı oluşturmak için gelişmiş bir formül kullanma örneğini göstermeden önce, bilmeniz gereken birkaç şey var:

• PivotTable hesaplanmış alanları oluştururken referansları veya adlandırılmış aralıkları kullanamazsınız.Bu, DÜŞEYARA, INDEX, OFFSET vb. gibi birçok formülü hariç tutacaktır.Ancak referanssız çalışan formüller kullanabilirsiniz (örn. SUM, IF, COUNT, vb.).
• Formüllerde sabitleri kullanabilirsiniz.Örneğin, %10 artması beklenen tahmini satış büyümesini bilmek istiyorsanız, =Satış*1.1 formülünü kullanırsınız (burada 1.1 sabittir).
• Hesaplanan alanlar oluşturan formüllerde öncelik sırasını izleyin.En iyi uygulama olarak, önceliği hatırlamanız gerekmediğinden emin olmak için parantez kullanın.

Şimdi gelişmiş bir formül kullanarak hesaplanmış alan oluşturmaya ilişkin bir örneğe bakalım.

Aşağıda gösterilen veri kümesine sahip olduğunuzu ve tahmini satışları bir pivot tabloda görüntülemeniz gerektiğini varsayalım.

Tahmin değerleri için, büyük perakendeciler için %300'lik bir satış artışı (5 milyonun üzerinde satış) ve küçük ve orta ölçekli perakendeciler için %300'luk bir satış artışı (10 milyondan az satış) kullanırsınız.

Not: Buradaki satış rakamları 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:

Bu, satış tahmini değerleriyle birlikte PivotTable'a yeni bir sütun ekleyecektir.

Pivot tablo hesaplanan alan sorunu

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

Ancak, PivotTable hesaplanmış alanlarıyla ilgili, kullanmadan önce anlamanız gereken bir sorun vardır.

Diyelim ki aşağıdaki gibi bir pivot tablom var ve tahmini satış rakamlarını almak için hesaplanmış bir alan kullanıyorum.

Ara toplamların ve toplamların yanlış olduğunu unutmayın.

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

Yani Güney Toplam için, değer 22,824,000 olması gerekirken, Güney Toplamı yanlış olarak 22,287,000 olarak bildiriyor.Bunun nedeni, değeri almak için 21,225,800*1.05 formülünü kullanmasıdır.

Ne yazık ki, bu sorunu düzeltemezsiniz.

Bunu halletmenin en iyi yolu, ara toplamları ve genel toplamları pivot tablodan çıkarmaktır.

sen de geçebilirsin Debra tarafından sunulduBu sorunla başa çıkmak için bazı yenilikçi geçici çözümler.

Bir pivot tablo hesaplanan alanı nasıl değiştirilir veya silinir?

Bir PivotTable hesaplanan alanı oluşturduktan sonra, aşağıdaki adımları kullanarak formülü değiştirebilir veya silebilirsiniz:

Hesaplanan tüm alan formüllerinin bir listesi nasıl alınır?

Çok sayıda PivotTable hesaplanmış alanı oluşturursanız, her alanda kullanılan formülleri takip etme konusunda endişelenmenize gerek yoktur.

Excel, hesaplanmış alanlar oluşturmak için kullanılan tüm formüllerin listesini hızlı bir şekilde oluşturmanıza olanak tanır.

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

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

Liste formülüne tıkladığınızda, Excel, pivot tabloda kullandığınız tüm hesaplanmış alanların/öğelerin ayrıntılarını içeren yeni bir çalışma sayfası otomatik olarak ekleyecektir.

Bir müşteriye iş göndermeniz veya ekibinizle paylaşmanız gerekiyorsa, bu çok yararlı bir araç olabilir.