PivotTable'larda, temel alınan kaynak verilerden değerleri birleştirmek için değer alanlarında özet işlevlerini kullanabilirsiniz. Özet işlevlerinden ve özel hesaplamalardan istediğiniz sonuçları alamazsanız, hesaplanan alanlarda ve hesaplanan öğelerde kendi formüllerinizi oluşturabilirsiniz. Örneğin, her bölgede farklı olabilecek satış komisyonu için formülle bir hesaplanan öğe ekleyebilirsiniz. Bundan sonra PivotTable, alt toplamlarda ve genel toplamlarda otomatik olarak bu komisyonu içerebilir.
Hesaplamanın bir diğer yolu da Data Analysis Expressions (DAX) formülü kullanarak oluşturduğunuz Power Pivot'ta Ölçüler'i kullanmaktır. Daha fazla bilgi için bkz. Power Pivot'ta Ölçü Oluşturma.
PivotTable'larda verileri hesaplamak için çeşitli yollar sağlanır. Sağlanan hesaplama yöntemlerini, kaynak verilerin türünün hesaplamaları nasıl etkilediğini ve PivotTable'larla PivotChart'larda formüllerin nasıl kullanıldığını öğrenin.
Sağlanan hesaplama yöntemleri
PivotTable'daki değerleri hesaplamak için, aşağıdaki hesaplama yöntemlerinin herhangi birini veya tümünü kullanabilirsiniz:
Değer alanlarındaki özet işlevleri Değerler alanındaki veriler PivotTable'da temel alınan kaynak verileri özetler. Örneğin, aşağıdaki kaynak veriler:
Aşağıdaki PivotTable ve PivotChart'ları oluşturur. PivotTable'daki verilerden bir PivotChart oluşturuyorsanız, PivotChart'taki değerler ilişkili PivotTable raporundaki hesaplamaları yansıtır.
PivotTable'da, Ay sütun alanı Mart ve Nisan öğelerini sağlar. Bölge satır alanı Kuzey, Güney, Doğu ve Batı öğelerini sağlar. Nisan sütunuyla Kuzey satırının kesişim noktasındaki değer, kaynak verilerde Ay değerleri Nisan ve Bölge değerleri Kuzey olan kayıtlardaki toplam satış geliridir.
PivotChart'ta Bölge alanı, kategori olarak Kuzey, Güney, Doğu ve Batı öğelerini gösteren bir kategori alanı olabilir. Ay alanı, seri olarak göstergede belirtilen Mart, Nisan ve Mayıs öğelerini gösteren bir seri alanı olabilir. Satış Toplamı adlı bir Değerler alanı, her bölgenin her ay elde ettiği toplam gelirini gösteren veri işaretçilerini içerebilir. Örneğin, bir veri işaretçisi dikey eksendeki (değer ekseni) konumuyla Kuzey bölgesinin Nisan ayındaki toplam satışını gösteriyor olabilir.
Değer alanlarını hesaplamak için, aşağıdaki özel işlevleri Çevrimiçi Analitik İşlem (OLAP) kaynak verileri dışında tüm kaynak veri türlerinde kullanılabilir.
İşlev Özetlediği değerler Topla Değerlerin toplamı. Bu, sayısal veriler için varsayılan işlevdir. Say Veri değerlerin sayısı. Say özet işlevi BAĞ_DEĞ_DOLU_SAY işleviyle aynı şekilde çalışır. Say işlevi, sayılar dışındaki veriler için varsayılan işlevdir. Ortalama Değerlerin ortalaması. Mak En büyük değer. Min En küçük değer. Çarpım Değerlerin çarpımı. Sayı Adedi Sayılardan oluşan veri değerlerinin sayısı. Sayı Adedi özet işlevi BAĞ_DEĞ_SAY işleviyle aynı şekilde çalışır. StdSapma Popülasyonun standart sapmasının tahmini; örnek tüm popülasyonun alt kümesidir. StdSapmaS Bir popülasyonun standart sapması, burada popülasyon özetlenecek tüm verilerdir. Var Popülasyonun varyansının tahmini; örnek tüm popülasyonun alt kümesidir. VarS Bir popülasyonun farkı; burada popülasyon özetlenecek tüm verilerdir.
Özel hesaplamalar Özel hesaplama, veri alanındaki diğer öğelere veya hücrelere dayalı olarak değerleri gösterir. Örneğin, Satış Toplamı veri alanındaki değerleri Mart satışlarının yüzdesi olarak veya Ay alanındaki öğelerin değişen toplamı olarak görüntüleyebilirsiniz.
Değer alanlarındaki özel hesaplamalar için aşağıdaki işlevler kullanılabilir.İşlev Sonuç Hesaplama Yok Alana girilen değeri görüntüler. Genel Toplam % Değerleri, rapordaki tüm değerlerin veya veri noktalarının genel toplamının yüzdesi olarak görüntüler. Sütun Toplamı % Her sütun veya serideki tüm değerleri, sütun veya seri için toplamın yüzdesi olarak görüntüler. Satır Toplamı % Her satır veya kategorideki değerleri, satır veya kategori için toplamın yüzdesi olarak görüntüler. % Değerleri, Temel alanı'ndaki Temel öğe değerinin yüzdesi olarak görüntüler. Üst Satır Toplamı % Değerleri şöyle hesaplar:
(öğe için değer) / (satırlardaki üst öğe için değer)Üst Sütun Toplamı % Değerleri şöyle hesaplar:
(öğe için değer) / (sütunlardaki üst öğe için değer)Üst Toplam % Değerleri şöyle hesaplar:
(öğe için değer) / (seçili Temel alanın üst öğesi için değer)Fark Değerleri, Temel alanındakiTemel öğesinin değerinden fark olarak görüntüler. % Farkı Değerleri, Temel alanındakiTemel öğesinin değerinden fark yüzdesi olarak görüntüler. Değişen Toplam Temel alandaki ardışık öğeler için değeri değişen toplam olarak görüntüler. Değişen Toplam % Yüzde cinsinden değişen toplam olarak görüntülenen Temel alanındaki ardışık öğeler için değeri hesaplar. En Küçükten En Büyüğe Sırala Belirli bir alandaki seçili değerlerin sırasını görüntüler; alandaki en küçük öğeyi 1 olarak ve daha büyük olan her değeri daha yüksek bir sıra değeri ile listeler. En Büyükten En Küçüğe Sırala Belirli bir alandaki seçili değerlerin sırasını görüntüler; alandaki en büyük öğeyi 1 olarak ve daha küçük olan her değeri daha yüksek bir sıra değeri ile listeler. Dizin Değerleri şöyle hesaplar:
((hücredeki değer) x (Genel Toplamların Genel Toplamı)) / ((Genel Satır Toplamı) x (Genel Sütun Toplamı))
- Formüller Özet işlevlerinden ve özel hesaplamalardan istediğiniz sonuçları alamazsanız, hesaplanan alanlarda ve hesaplanan öğelerde kendi formüllerinizi oluşturabilirsiniz. Örneğin, her bölgede farklı olabilecek satış komisyonu için formülle bir hesaplanan öğe ekleyebilirsiniz. Bundan sonra rapor, alt toplamlarda ve genel toplamlarda otomatik olarak bu komisyonu içerebilir.
Kaynak verilerin türü hesaplamaları nasıl etkiler?
Raporda hangi hesaplamaların ve seçeneklerin kullanılabildiği, kaynak verilerin bir OLAP veritabanından mı yoksa OLAP olmayan bir veri kaynağından mı geldiğine bağlıdır.
-
OLAP kaynak verilerine dayalı hesaplamalar OLAP küplerinden oluşturulmuş PivotTable'larda, Excel sonuçları görüntülemeden önce özetlenmiş değerler OLAP sunucusunda önceden hesaplanır. PivotTable'da bu önceden hesaplanmış değerlerin hesaplanma biçimini değiştiremezsiniz. Örneğin, veri alanlarını veya alt toplamları hesaplarken kullanılan özet işlevini değiştiremez ya da hesaplanan alanlar veya hesaplanan öğeler ekleyemezsiniz.
Bunun yanı sıra, OLAP sunucusu hesaplanan alanlar sağlıyorsa, bu alanları PivotTable Alan Listesi'nde görürsünüz. Ayrıca, Visual Basic for Applications'da (VBA) yazılmış ve çalışma kitabınızda depolanmış makrolar tarafından oluşturulan tüm hesaplanan alanları ve hesaplanan öğeleri de görürsünüz, ama bu alanları veya öğeleri değiştiremezsiniz. Başka hesaplama türlerine ihtiyacınız varsa, OLAP veritabanı yöneticinize başvurun.
OLAP kaynak verileri için, alt toplamları ve genel toplamları hesaplarken gizli öğelerin değerlerini dahil edebilir veya hariç tutabilirsiniz. - OLAP olmayan kaynak verilere dayalı hesaplamalar Diğer türlerdeki dış verilere veya çalışma sayfası verilerine dayalı PivotTable'larda, Excel sayısal veri içeren değer alanlarını hesaplamak için Topla özet işlevini ve metin içeren veri alanlarını hesaplamak için de Bağ_Değ_Say özet işlevini kullanır. Verilerinizi daha fazla analiz etmek ve özelleştirmek için Ortalama, Mak veya Min gibi farkı özet işlevlerini de seçebilirsiniz. Ayrıca, alanın içinde bir hesaplanan alan veya hesaplanan öğe oluşturmak yoluyla, raporun öğelerini veya diğer çalışma sayfası verileri kullanan kendi formüllerinizi oluşturabilirsiniz.
PivotTable'larda formülleri kullanma
Yalnızca OLAP olmayan kaynak verilere dayalı raporlarda formül oluşturabilirsiniz. OLAP veritabanına dayalı raporlarda formül kullanamazsınız. PivotTable'larda formül kullanırken, aşağıdaki formül söz dizimi kurallarını ve formül davranışını biliyor olmanız gerekir:
PivotTable formül öğeleri Hesaplanan alanlar ve hesaplanan öğeler için oluşturduğunuz formüllerde, aynı diğer çalışma sayfası formüllerinde olduğu gibi işleçler ve ifadeler kullanabilirsiniz. Sabitler kullanabilir ve rapordan verilere başvurabilirsiniz, ancak hücre başvuruları veya tanımlanmış adlar kullanamazsınız. Bağımsız değişken olarak hücre başvuruları veya tanımlanmış adlar gerektiren çalışma sayfası işlevlerini kullanamazsınız ve dizi işlevlerini de kullanamazsınız.
Alan ve öğe adları Excel, formüllerinizde raporun bu öğelerini tanımlamak için alan ve öğe adlarını kullanır. Aşağıdaki örnekte, C3:C9 aralığındaki veriler SütÜrünleri alan adını kullanır. Tür alanında yer alan ve SütÜrünleri satışına dayalı olarak yeni ürünün satış tahminini yapan bir hesaplanan öğe, =SütÜrünleri * %115 gibi bir formül kullanabilir.
Not
PivotChart'ta, alan adları PivotTable alan listesinde görüntülenir ve öğe adları da her alanın açılan listesinde görülebilir. Bu adları grafik ipuçlarında gördüğünüz, seri ve veri noktası adlarını yansıtan değerlerle karıştırmayın.
Formüller tek tek kayıtlar yerine toplamlar üzerinde çalışır Hesaplanan alanlara yönelik formüller, formüldeki tüm alanlar için temel oluşturan verilerin toplamı üzerinde çalışır. Örneğin, =Satış * 1,2 hesaplanan alan formülü her türün ve bölgenin toplam satışını 1,2 ile çarpar; tek tek her satış değerini 1,2 ile çarpıp, sonra çarpım sonucu elde edilen tutarları toplamaz.
Hesaplanan öğelere yönelik formüller tek tek kayıtlar üzerinde çalışır. Örneğin, =SütÜrünleri *%115 hesaplanan öğe formülü SütÜrünleri'nin tek tek her satışını %115 ile çarpar ve bundan sonra çarpım sonucu elde edilen tutarlar Değerler alanında hep birlikte özetlenir.Adlardaki boşluklar, sayılar ve simgeler Birden çok alan içeren bir adda, alanlar herhangi bir sırada olabilir. Yukarıdaki örnekte, C6:D6 hücreleri 'Nisan Kuzey' veya 'Kuzey Nisan' olabilir. Birden çok sözcükten oluşan ya da sayı veya simge içeren adları tek tırnak içine alın.
Toplamlar Formüller toplamlara başvuramaz (örnekteki Mart Toplam, Nisan Toplam ve Genel Toplam gibi).
Öğe başvurularındaki alan adları Bir öğenin başvurusuna alan adını ekleyebilirsiniz. Öğe adı köşeli parantez içinde olmalıdır; örneğin, Bölge[Kuzey]. #NAME önlemek için bu biçimi mi kullanacaksınız? hatalarından kaçınmak için bu biçimi kullanın. Örneğin, raporun Tür alanında Et adlı bir öğe varsa ve Kategori alanında da Et adlı başka bir öğe varsa, #NAME? hatalarına çevirerek hataları alırsınız.
Öğelere konumlarına göre başvurma Öğeye, raporda şu anda sıralandığı ve görüntülendiği konumuna göre başvurabilirsiniz. Tür[1]SütÜrünleri ve Tür[2] de DenizÜrünleri'dir. Öğelerin konumları değiştiğinde veya farklı öğeler görüntülendiğinde ve gizlendiğinde, bu şekilde başvurulan öğe değişir. Gizlenmiş öğeler bu dizinde sayılmaz.
Öğelere başvururken göreli konumlar kullanabilirsiniz. Konumlar, formülü içeren hesaplanan öğeye göre saptanır. Güney geçerli bölgedir ve Bölge[-1] de Kuzey'dir; geçerli bölge Kuzey olduğunda, Bölge[+1] de Güney olur. Örneğin, hesaplanan öğe =Bölge[-1] * %3 formülünü kullanabilir. Verdiğiniz konum alandaki ilk öğeden önceye veya son öğeden sonraya denk geliyorsa, formülde #BAŞV! hatası oluşur.
PivotChart'larda formülleri kullanma
PivotChart'larda formülleri kullanmak için, formülü ilişkili PivotTable'da oluşturursunuz. Burada, verilerinizi oluşturan tek tek değerleri görebilirsiniz ve ardından PivotChart'ta da sonuçları grafik olarak görüntüleyebilirsiniz.
Örneğin, aşağıdaki PivotChart'ta her satış elemanının bölgeye göre satışları gösterilir:
Yüzde 10 artırıldığında satışların nasıl görüneceğine bakmak için, ilişkili PivotTable'da =Satış * %110 gibi bir formül kullanan bir hesaplanan alan oluşturabilirsiniz.
Sonuç, aşağıdaki grafikte de gösterildiği gibi PivotChart'ta hemen görüntülenir:
Kuzey bölgesindeki satışlarda yüzde 8 ulaşım maliyetinin çıkarıldığı ayrı bir veri işaretçisi görmek için, Bölge alanında =Kuzey – (Kuzey * %8) gibi bir formülle bir hesaplanan öğe oluşturabilirsiniz.
Sonuçta şöyle bir grafik elde edilir:
Bununla birlikte, SatışElemanı alanında oluşturulan hesaplanan öğe, göstergede yer alan bir seri olarak ve grafikte her kategoride bir veri noktası olarak görünebilir.
Daha fazla yardım mı gerekiyor?
Dilediğiniz zaman Excel Teknoloji Topluluğundaki uzmanlara sorabilir veya Topluluklar'dan destek alabilirsiniz.