Excel pivot tablosu nasıl eklenir ve kullanılır

Çoğu kez, bir Özet Tablo oluşturduktan sonra analizinizi genişletmeniz ve bir parçası olarak daha fazla veri / hesaplama içermeniz gerekiyor.

Pivot Tablosundaki mevcut veri noktalarını kullanarak elde edilebilen yeni bir veri noktasına ihtiyacınız varsa, geri dönüp kaynak veriye eklemenize gerek yoktur. Bunun yerine, bunu yapmak için Hesaplanmış Bir Pivot Tablosu alanı kullanabilirsiniz .

Bu yazıda, Excel Pivot Tablosu Hesaplanan Alan hakkında her şeyi öğreneceksiniz:

Pivot Tablosu Hesaplanan Alan nedir ve Birine İhtiyacınız Olan Ne Zaman?
Pivot Tablosu Nasıl Hesaplanır Eklenir?
Pivot Tablosu ile İlgili Konular Hesaplanan Alan
Pivot Tablosunu Hesaplanan Alan Nasıl Kaldırır?
Hesaplanan Alanların Bir Listesini Nasıl Oluştururum?

Veri setini indirin ve birlikte takip edin .

Pivot Tablosu Hesaplanan Alan Nedir?

Pivot Tablonun temel bir örneğini görelim.

Perakendecilerin bir veri kümesine sahip olduğunuzu ve aşağıda gösterildiği gibi bir Pivot Tablosu oluşturduğunuzu varsayalım:

Yukarıdaki Özet Tablo, perakendecilerin satış ve kar değerlerini özetlemektedir.

Peki, bu perakendecilerin kâr marjlarının ne olduğunu (kar marjının 'Kar' bölünerek 'Satış' olarak) öğrenmek isterseniz ne olur?

Bunu yapmak için bir çift yol var:

Orijinal veri kümesine dönün ve bu yeni veri noktasını ekleyin. Böylece, kaynak verilerde yeni bir sütun yerleştirebilir ve içinde kar marjı hesaplayabilirsiniz. Bunu yaptıktan sonra, bu yeni sütunu bir parçası olarak almak için Özet Tablo'nun kaynak verilerini güncellemeniz gerekiyor.
- Bu yöntem bir olasılık olsa da, elle veri kümesine dönüp hesaplamaları yapmanız gerekir. Örneğin, birim başına ortalama satış miktarını (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üncellemesi gerekecek .
- Bu yöntem aynı zamanda Pivot Tablosunu yeni veri eklerken şişirir.
Pivot Tablosu dışında hesaplamalar ekleyin. Pivot Tablosu yapınızın değişmesi muhtemel değilse, bu bir seçenek olabilir. Ancak Pivot tablosunu değiştirirseniz, hesaplama buna göre güncelleşmeyebilir ve size yanlış sonuçlar veya hatalar verebilir. Ardından perakendeciler olduğunda aşağıda gösterildiği gibi Kâr Marjı'nı hesapladım. Ancak müşteriden bölgelere değiştirdiğimde formüller bir hata verdi.
Pivot Tablosu Hesaplanmış Alan Kullanma. Bu, mevcut Pivot Tablosu verilerini kullanmak ve istenen metriği hesaplamak için en verimli yoldur. Hesaplanan Alanı, Pivot Tablosundan mevcut sütunları kullanarak eklediğiniz sanal bir sütun olarak düşünün. Bir Pivot Tablosu Hesaplanan Alanı'nı (bir dakika içinde göreceğimiz gibi) kullanmanın bir çok faydası vardır:
- Formülleri ele almayı veya kaynak verisini güncellemenizi gerektirmez.
- Pivot Tablosuna ekleyebileceğiniz yeni verileri otomatik olarak hesaplayacak şekilde ölçeklenebilir. Bir Hesapla Alan ekledikten sonra, onu Pivot Tablosundaki diğer alanlar gibi kullanabilirsiniz.
- Güncellemek ve yönetmek kolaydır. Örneğin, ölçümler değişirse veya hesaplamayı değiştirmeniz gerekiyorsa, Pivot Tablosundan kendiniz yapabilirsiniz.

Pivot Tablosu Hesaplanan Alan Ekleme

Mevcut bir Pivot Tabloda Pivot Tablosu Hesaplanan Alanı nasıl ekleneceğini görelim.

Aşağıda gösterildiği gibi bir Pivot Tablosunuz olduğunu ve her perakende satıcı için kar marjını hesaplamak istediğinizi varsayalım:

Hesaplanan Alan Özet Tablosu ekleme adımları:

Pivot Tablodaki herhangi bir hücreyi seçin.
Pivot Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler bölümüne gidin.
Açılan listeden Hesaplanan Alan'ı seçin.
Hesaplanan Dosyayı 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 birini seçebileceğinizi unutmayın. Bu durumda formülü '= Kâr / Satış'. 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'ı eklediğinizde Özet Tablo Alanları listesindeki alanlardan biri olarak görünür.

Şimdi bu hesaplanan alanı diğer Özet Tablo alanı olarak kullanabilirsiniz (Pivot Tablo Hesaplanan Alanını rapor filtresi veya dilimleyici olarak kullanamayacağınızı unutmayın).

Daha önce de belirttiğim gibi, bir Pivot Tablosu Hesaplanan Alanı kullanmanın yararı, Pivot Tablosunun yapısını değiştirebilmeniz ve otomatik olarak ayarlamanızdır.

Örneğin, satır alanına bölge sürükleyip bırakırsam, aşağıda gösterildiği gibi sonuç alırsınız; burada perakendeciler ve bölge için Kar Marjı değeri raporlanır.

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

Size Pivot Tablosu Hesapla Alanı Oluşturmak için gelişmiş bir formül kullanma örneği göstermeden önce bilmeniz gereken bazı şeyler:

Sen referanslar veya KULLANMAYIN CAN adlandırılmış aralıklar Saha Hesaplanan Bir Özet Tablo oluştururken. Bu, VLOOKUP, INDEX, OFFSET ve benzeri birçok formülü ekarte eder. Bununla birlikte, referanslar olmadan çalışabilen formülleri kullanabilirsiniz (örneğin, SUM, IF, COUNT vb.).
Formülde bir sabit kullanabilirsiniz. Örneğin, öngörülen satışların% 10 büyümesinin beklendiğini bilmek istiyorsanız, = Sales * 1.1 formülünü (burada sabit 1.1 olduğu yerde) kullanabilirsiniz.
Hesaplanan alanı oluşturan formülde öncelik sırası izlenir. En iyi uygulama olarak öncelik sırasını hatırlamak zorunda olmadığınızdan emin olmak için parantez kullanın.

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

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

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

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

İşte bunu yapmak için:

Pivot Tablodaki herhangi bir hücreyi seçin.
Pivot Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler bölümüne gidin.
Açılan listeden Hesaplanan Alan'ı seçin.
Hesaplanan Dosyayı Ekle iletişim kutusunda:
- Ad alanına girerek bir ad verin.
- Formül alanına aşağıdaki formülü kullanın: = IF (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ğeri ile pivot tabloda yeni bir sütun ekler.

Veri kümesini indirin .

Pivot Tablosu ile Hesaplanan Alanlar

Hesaplanan Alan, her ne kadar ölçeklenebilir ve yönetilebilir olmasını sağlarken, Pivot Tablosunun değerini alan hesaplamaları ile gerçekten zenginleştiren şaşırtıcı bir özelliktir.

Bununla birlikte, Pivot Tablosu Hesaplanan Alanlarla kullanmadan önce bilmeniz gereken bir sorun var.

Aşağıda gösterilen bir Pivot Tablosu var, burada tahmini satış sayılarını almak için hesaplanan alanı kullandım.

Alt toplam ve büyük toplamların doğru olmadığını unutmayın.

Bunlar, her bir perakendeci için bireysel satış tahmin değerini eklemeliyse de, aslında, oluşturduğumuz hesaplanan alan formülünü takip ediyor.

Güney Total için, değer 22.824.000 olmalı, Güney Toplam yanlışlıkla 22.287.000 olarak bildiriyor. Bu, değeri elde etmek için 21.225.800 * 1.05 formülünü kullandığı için olur.

Maalesef bunu düzeltmenin bir yolu yoktur.

Bunu yapmanın en iyi yolu, Özet Tablosundan alt toplamları ve Büyük Toplamları kaldırmak olacaktır.

Ayrıca gidebilirsiniz bazı yenilikçi geçici çözümler Debra bu sorunu ele almak göstermiştir.

Pivot Tablolar Nasıl Değiştirilir veya Silinir Hesaplanan Alan

Bir Pivot Tablosu Hesaplanan Alanı oluşturduktan sonra, aşağıdaki adımları izleyerek formülü değiştirebilir veya silebilirsiniz:

Pivot Tablodaki herhangi bir hücreyi seçin.
Pivot Tablo Araçları -> Analiz Et -> Hesaplamalar -> Alanlar, Öğeler ve Kümeler bölümüne gidin.
Açılan listeden Hesaplanan Alan'ı seçin.
Ad alanında, açılır oku tıklayın (alanın sonunda küçük aşağı ok).
Listeden, silmek veya değiştirmek istediğiniz hesaplanan alanı seçin.
Formülü değiştirmek isterseniz formülü değiştirin veya silmek istiyorsanız, Sil'i tıklayın.

Hesaplanan Alan Formüllerinin Bir Listesini Alın

Pivot Tablosu Hesaplanmış alanı çok oluşturursanız, her birinde kullanılan formül hakkında bir parça tutma konusunda endişelenmeyin.

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

Tüm Hesaplanan Alanlar formüllerinin listesini hızlı bir şekilde elde etmek için gereken adımlar şunlardır:

Pivot Tablodaki herhangi bir hücreyi seçin.
Pivot Tablo Araçları -> Analiz -> Alanlar, Öğeler ve Takımlar -> Liste Formüllerine gidin.

Liste Formüllerine tıkladığınızda, Excel Pivot Tablosunda kullandığınız tüm hesaplanan alanların / öğelerin ayrıntılarını taşıyacak yeni bir çalışma sayfası ekleyecektir.