ÖZETVERİAL

ÖZETVERİAL (GETPIVOTDATA), bir özet tablo raporunda görünen verileri alır. Değeri hücre konumuna göre değil, alan ve öğe adlarına göre bulur. Bu nedenle özet tablodaki bir toplamı, ayrıntı toplamını ya da genel toplamı başka bir formülde kullanmak için tercih edilir.

Söz dizimi ve parametre​

alan1 ve öğe1 çiftleri birlikte kullanılır ve en fazla 14 çift verilebilir; çiftlerin sırası önemli değildir. Özet tablodaki bir hücreye tıklayarak formül oluşturduğunuzda Excel bu işlevi sizin için kendisi yazabilir.

1. Ad ve ay kesişimindeki toplam​

Kaynak veri A1:C6 aralığındadır. Özet tabloyu kurmak için bu aralıkta bir hücre seçip Ekle sekmesindeki Özet Tablo komutunu kullanın ve tabloyu aynı sayfada F1 hücresine yerleştirin. Ad alanını Satırlar, Ay alanını Sütunlar, Satış alanını Değerler bölgesine sürükleyin ve değer alanının adını Satış Toplamı yapın. Ahmet ve Ocak çifti iki ayrı kayıttan oluşur; özet tablo bunları tek hücrede toplar. D2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Her satır kendi ad ve ay değerini alan1 ve öğe1 çifti olarak kullanır.
Sonuçlar sırasıyla 1.100, 750, 1.100, 1.500 ve 850 olur. İşlev kaynak satırdaki tutarı değil, özet tablodaki ad ve ay kesişiminin toplamını getirir. Bu yüzden Ahmet'in Ocak ayındaki iki kaydı için ilk ve üçüncü satırda aynı toplam görünür. Selin'in Ocak ve Şubat satırları ise farklı kesişimlere karşılık geldiği için farklı sonuç verir.

2. Yalnız bir alanla ay toplamı​

Aynı düzende bir özet tablo kurun: kaynak A1:C6 aralığı, hedef F1 hücresi, Ad alanı Satırlar, Ay alanı Sütunlar, Satış alanı Değerler bölgesinde ve değer alanının adı Satış Toplamı olsun. Yalnız ayı sormak için alan1 ve öğe1 çiftinin yalnızca ay kısmını verin. D2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Ad bilgisi verilmediği için işlev bütün personelin o aydaki satışlarını birlikte getirir.
Sonuçlar sırasıyla 1.250, 1.250, 2.350, 2.350 ve 900 olur. Ocak ve Şubat ayları birden fazla personelin satışını içerdiği için sonuç tek satırın tutarından büyüktür. Nisan ayında yalnız Zeynep'in satışı bulunduğundan son satırda kendi tutarı görünür. Aynı aya ait satırlar aynı toplamı getirir.

3. Ad toplamı ve genel toplam​

Kaynak veri A1:C6 aralığındadır; özet tabloyu bu kez G1 hücresine yerleştirin. Ad alanı Satırlar, Ay alanı Sütunlar, Satış alanı Değerler bölgesinde olsun ve değer alanının adı Satış Toplamı olsun. D2 hücresine kişinin tüm aylardaki toplamını getiren aşağıdaki formülü yazıp aşağı kopyalayın:
E2 hücresine ise alan ve öğe çifti vermeden yalnız ilk iki parametreyi kullanan aşağıdaki formülü yazıp aşağı kopyalayın:
İkinci formül özet tablonun genel toplamını getirir.
Ad toplamı sütununda sonuçlar sırasıyla 1.100, 1.600, 1.100, 1.500 ve 1.600 olur. Genel toplam sütununda ilk satırın sonucu 4.200 olur ve her satırda aynıdır. Ad toplamı, kişinin Ocak ve Şubat satışlarını birlikte içerir; Selin'in iki satırı bu yüzden aynı değeri verir. Genel toplam ise özet tablodaki bütün satışların toplamıdır.

4. Verisi bulunmayan birleşimler​

Kaynak veri A1:C6 aralığındadır; özet tabloyu G1 hücresine kurun. Ad alanı Satırlar, Ay alanı Sütunlar, Satış alanı Değerler bölgesinde olsun ve değer alanının adı Satış Toplamı olsun. D sütununda hangi ayın sorgulandığı yazılıdır; bazı kişilerin o ayda satışı yoktur. E2 hücresine aşağıdaki formülü yazıp aşağı kopyalayın:
Formül her satırda ad ve sorgulanan ay çiftini özet tabloda arar.
Sonuçlar sırasıyla 0, 850, 1.500, 0 ve 750 olur. Selin, Mehmet ve Selin'in Ocak satırı için özet tabloda görünür bir kesişim vardır; işlev bu hücrelerdeki değeri getirir. Ahmet'in satışı yalnız Ocak'ta, Zeynep'in satışı yalnız Nisan'dadır; bu iki satırda sorgulanan ayda toplanacak bir kayıt yoktur. Bu satırların sonucunu özet tablodaki ilgili boş hücreyle karşılaştırarak yorumlayın. Özet tabloda hiç bulunmayan bir alan adı yazarsanız durum farklıdır ve #BAŞV! hatası alırsınız.

Kullanırken dikkat edin​

• veri_alanı parametresi: veri_alanı (data_field), verisini almak istediğiniz alanın adıdır ve tırnak içinde metin olarak yazılır. Bu sayfadaki örneklerde özet tablonun Değerler bölgesindeki alana verilen Satış Toplamı adı kullanılmıştır. Hesaplanan alanlar, hesaplanan öğeler ve özel hesaplamalar da işlevin sonucuna dahildir.
• özet_tablo parametresi: özet_tablo (pivot_table), bir hücre başvurusu, adlandırılmış bir aralık ya da özet tablonun üstündeki bir hücrede yazılı etiket olabilir. Verdiğiniz aralık birden fazla özet tablo raporu içeriyorsa veri, aralıkta en son oluşturulan rapordan alınır. özet_tablo bir özet tabloya başvurmuyorsa işlev #DEĞER! hatası verir.
• alan1 ve öğe1 parametreleri: alan1 (field1) verinin süzüleceği alanın adını, öğe1 (item1) bu alandaki değeri belirtir; ikisi de tırnak içinde metin olarak yazılır ya da metin içeren bir hücreye başvurur. Birden fazla satır ve sütun başlığını ilgili öğelerle birlikte vererek belirli bir kesişimi alabilirsiniz. En fazla 14 alan ve öğe çifti kullanılabilir ve çiftler herhangi bir sırada yazılabilir. Yalnız veri_alanı ve özet_tablo verilirse özet tablonun genel toplamı döner.
• Hata durumları: Adı verilen alan özet tabloda görünür bir alan değilse işlev #BAŞV! hatası verir; alan adındaki yazım hatası da aynı sonucu doğurur. özet_tablo özet tablo olmayan bir hücreye başvuruyorsa sonuç #DEĞER! olur. Verilen alan ve öğe tek bir hücreyi tanımlıyorsa, hücrenin değeri metin, sayı ya da hata olsun fark etmeksizin olduğu gibi döner.
• Özet tabloyu güncel tutun: İşlev özet tablonun gösterdiği değeri okur, kaynak veriyi değil. Kaynak veriyi değiştirdiğinizde özet tabloyu yenilemezseniz sonuçlar eski değerlerde kalır. Alan veya öğe düzenini değiştirmeniz, formülün hücre konumuna değil adlara dayandığı için sonucu bozmaz.
• Hesaplama ve sürüm: İşlev her zaman tek iş parçacığıyla çalışır. Excel 1997 sürümünden beri kullanılmaktadır.
• İlgili işlevler: VSEÇAL: Özet tablodan tek bir değer almak için kullanılabilir; ölçütleri ayrı bir ölçüt aralığında vermek istediğinizde tercih edilir. GROUPBY: Veriyi satıra göre gruplayıp topladıktan sonra özet tabloyu formülle döndürmek istediğinizde, özet tablo nesnesi kurmadan kullanılır. PIVOTBY: Veriyi hem satıra hem sütuna göre gruplayıp toplayan özet tabloyu formülle döndürmek istediğinizde kullanılır.

Mini Sayfa içindeki sonuç hücresini seçerek gerçek formülü görebilirsiniz. Formüllü örnekleri indirilebilir Excel dosyasında da kullanabilirsiniz.