Pivot Table(Özet Tablo)

Elimizde liste olarak bulunan bir data kümesini çeşitli seviyelerde özet hale getirmek için PivotTable aracını kullanırız.

Örneğin, elimizde bir bankanın Bölge-Şube-Ürün (veya bir market zinciri için Bölge-Mağaza-Reyon) detayında rakamları var ve biz bunu Bölge-Ürün bazında görmek istiyoruz diyelim. Datamız şöyle olsun(Tüm liste aşağı doğru uzuyor. İlgili excel dosyasını buradan indirebilirsiniz)

Şimdi ilgili data kümesi içinde herhangi bir hücredeyken Insert menüsünden PivotTable butonuna basalım. Burada önemli bir nokta var, o da ilgili kümenin başlıklarında hiç boş hücre olmaması lazım, aksi halde hata alırsınız. (Bu arada bazı arkadaşlarımın tüm data kümesini seçtikten sonra Pivot düğmesine bastığını görüyorum, buna gerek yok, küme içinde herhangi bir hücrede olmanız yeterlidir.)

Data Kaynağı

Akabinde aşağıdaki dialog kutusu çıkacaktır. Burada Select a table or range kutusuna otomatikman ilgili data kümesinin alanı gelecektir. Excel sayfanızda bir ListeliTablo(Table) varsa buraya bunun adı çıkacaktır, ki Özet Tablonuzun kaynak hücre grubunun dinamik olması açısından Table olarak almanız çok daha iyi olur. Böylece kaynak dataya yeni data eklendikçe Change Data Source yapmak zorunda kalmazsınız. Aşağıdaki örnekte Table7 isminde bir tablodan veri sağlanacağını görüyoruz.(Hemen bir alttaki external data source kısmını şimdilik görmezden gelin, bununla ilgili bir örneği ayrıca yapacağız.)

Özet tablomuzu nereye koyacağımızı bir alttaki seçeneklerde belirtiyoruz. Ayrı bir sayfada görmek istiyorsak New Worksheet, mevcut sayfa üzerinde bir yerde istiyorsak Existing Worksheet diyip tam olarak başlangıç konumunu belirtiyoruz. Biz aşağıdaki örnek için New Workseheet deyip ilerleyelim.

Özet tabloyu düzenleme

OK deyip devam ettiğimizde yeni açılan sayfada karşımıza şöyle bir görüntü çıkacaktır.

Sağ taraftaki PivotTable Fields panelinde ROWS bölümüne Bölge'yi, COLUMNS Bölümüne Ürün Adını, ve VALUES bölümüne de Aylık Gerç alanını sürükleyelim. Sonuç şöyle olacaktır:
 
Eğer en sağdaki Grand Total kolonunu gereksiz buluyorsanız (çünkü bazı durumlarda ürünlerin toplamı anlamsız olabilir) bu başlığa sağ tıklanıp Remove GrandTotal denilerek, otomatik gelen bu kolon silinebilir, ancak en alttaki GrandTotal bu örnek için kalmalıdır, zira bu bize banka toplamını verecektir, ve anlamlı bir bilgidir, tabiki isterseniz yine de sağ tıklayıp aynı kaldırma işlemini yapabilirsiniz. Sonrasında istediğiniz bölgeye istediğiniz bilgiyi sürükleyerek oynayabilirsiniz. (Not:Diptoplamları menülerden de kaldırıp ekleyebiliyorsunuz)

Pratik hususlar

• Özet Tablonuzu yeni data geldikçe güncellemek için tablo üzerinde herhangi bir yerdeyken sağ tıklayıp Refresh butonuna basmanız yeterlidir. (Ancak, Tablonuzun kaynak datası bir Table değil de bir hücre alanı ise DataSource kısmını da güncellemeniz gerekir. Siz en iyisi datanızı hep Table şeklinde muhafaza edin)
• Özet tablonuzun kaynağı bir Table olsa bile, Table manuel veya makro ile refresh olduğunda Özet tablonuz da eş zamanlı olarak refresh olmaz. Bunun için sizin özet tablonuzu ayrıca refresh etmeniz gerekir.
• Sum of Rakam, Row Label gibi başlıkları değiştirmek çok kolay, sadece bu başlığa gelin ve yeni isim yazın, veya bunu sağ tık>Value Fields Settings'te de yapabilirsiniz, en üstteki Custom Name yazan yere yeni başlığınızı yazın.
• Özet Tabloları hızlı bir şekilde Benzersiz(Uniqe) değerleri almak için de kullanabilrisiniz.(Bir kolonu tümden seçip başka bir yere kopyalayıp RemoveDuplicates yapmak yerine)
• Güzel görünümlü Özet Tablolar için bir öneri: Solda boş bir kolon olsun, gridleri kaldırın, PivotTabloların kendi gridi var zaten. Ayrıca PivotTable Options'ta aşağıdaki işaretli kısmın işaretini kaldırın. Böylece kolonlarınızın genişliği sürekli genişleyip durmaz.
• Başlıklar:Bir Özet Tablo içindeyken Design>Layout'ta Compact yerine Outline veya Tabular yaparsanız başlıkların manalı hale geldiğini görürsünüz.(Default format neden bunlardan biri değildir bilmiyorum)

Ertelenmiş Güncelleme(Defer Layout Update)

Özet Tablonuzda değişiklik yapmak istediğinizde her değişklik sonrasında tablonuz otomatik güncellenir, ancak bazen tablonuzda birden çok değişiklik yapmak isteyeceksiniz ve eğer bu güncellemeler de çok vakit alıyorsa PivotTable Fields panelinin en altında bulunan Defer Layout Update seçeneğini işaretlemeniz yeterlidir, böylece değişikleriniz sonrasında hemen güncelleme olmaz, tüm değişiklik işleriniz bitince bu seçeneğin hemen sağındaki UPDATE düğmesine tıkladığınızda güncelleme gerçekleşir. Çok göze çarpmayan bir özelliktir ama yeri geldiğinde çok faydalıdır.

Rakamsal İçerik

Values bölümüne sürüklediğiniz alana bakarak Excel otomatik olarak rakam içeriğini tespit etmeye çalışır. Rakamsal alanlar için varsayılan değer Toplam aldırmakken, Metinsel alanlar için Adet saydırmak olacaktır.

Otomatik gelen bu bilgiyi değiştirmek için PivotTable Fields'tan ilgili alana tıklayıp Value Field Settings kutusunu açarak veya doğrudan ilgili kolondaki bir hücreye sağ tıklayıp Summarize Values By diyerek istediğimiz değişikliği yapabiliriz.

Bu arada aynı alanı ikinci bir kez daha sürükleyip, bu sefer bu alanın başka bir içeriğini, mesela Sum varken bir de Count'ını aldırabiliriz. Veya yukardaki kutunun ikinci sekmesinde bulunan yüzdesel gösterim şekli gibi farklı gösterim şekillerini de gösterebiliriz. Bu işlemi yine ilgili kolonda herhangi bir hücreye tıkalyıp Show Values As seçeneği ile de yapabiliriz. Mesela biz % of Column Total seçeneğini seçerek ilgili bölgenin toplam içinde ne kadar pay aldığını gösterelim.

Sonuç aşağıdak gibi olacaktır.

Keza, bir de tarihsel derinlikteki datamız var ve bu data aylık bazda. Bizden bunun her ay için Yıllık(Kümüle/YTD) versiyonunu da hazırlamamız istendi diyelim.(Tabi bu ürünlerin toplanabilen Yeni Satış veya Gelir Tablosu kalemleri gibi Flow(Dönem içi) tipli ürünler olduğunu varsayıyorum. Ör:Yeni Kredi Kart Adedi, Ücret Komisyon Tahsilatı. Veya bir mağaza için aylık işlem adedi, sipariş adedi gibi.

Şimdi bu tarihsel datayı Pivotlayalım.

Show Value as'e tıklayıp sonra da Running In ... diyelim ve Ay alanını seçelim. Ben daha okunaklı görünmesi adına Tabular görünüm şeklini seçtim ve Repeat Row Labels dedim. Sonuç aşağıdaki gibi olacak. Gördüğünüz gibi bu yöntem kümülatif toplam almanın muhteşem kolay bir yolunu sunmaktadır.

Detaya inme(Drilling)

Values alanındaki bir hücreye çift tıkladığnızda ilgili kesişim kümesindeki detaylı bilgi yeni bir sayfada açılır. Bu örnekte B4'e(Başkent1 bölgesinin rakamına) tıkladığınızda karşımıza çıkacak görüntü şu olacaktır.

Bu yöntem özellikle kaynak data ile özet tabloyu birbirinden ayırdığımızda kaynak datayı tekrar elde etme yolu olarak pratik bir imkan sunmaktadır.

Belki gözünüzden kaçmış olabilir, tekrar edelim. Sadece Values alanındaki bir hücreye çift tıkladığımızda detaylı dataya(başka bir sayfada) ulaşmış oluyoruz. Label'lara çift tıkladığımızda ise bize, hangi alanın alt kalem olarak tabloda gösterilmesini istediğimiz sorulur. İstenirse seviye seviye aşağı doğru inilir. Mesela biz önce Başkent2'ye çift tıklayalım ve Şubeyi seçelim.

Gördüğünüz gibi + işaretleri otomatik eklendi. Hem de tüm bölgelere, sadece Başkent2'ye değil. Şimdi de Şube10'a çift tıklayalım ve Ürünü seçelim. Sonuç aşağıdaki gibidir:

GetPivotData

Özet tablo içinden, belli kesişim noktasına ait bilgiyi çıkarmak için GETPIVOTDATA fonksiyonunu kullanırız. Bunu kullanabilmek için Excel Options'ında aşağıdaki seçeneğin işaretli olması gerekir.

Bunu ayrıca Pivot Table sekmesindeki Options butonunun yanındaki oka tıklayarak, Generate GetPivotData seçeneğine işaret koyarak da yapabilirsiniz.

Şimdi bu seçenek işaretli değilken aşağıdaki tablomuz üzerinden bu formülü nasıl kullanacağımıza bakalım. A21 ve B21 hücrelerine DataValidation ile bir combobox oluşturdum. C21 hücresine de şu formül yazdım.

=GETPIVOTDATA("Aylık Gerç";$A$3;"Bölge";$A$21;"Ürün Adı";$B$21)

Resimden görüleceği üzere, formülümüz kesişim rakamını getirdi. Tabi, bunu aynı sayfa üzerinde yapmak anlamsız olabilir, ancak daha büyük Özet Tablolarda, veya özet tablonuz gizlenmiş kolonlarda yer alıyorsa veya başka bir sayfadaysa oldukça kullanışlı bir formüldür. Gerçi başkaları çok faydalı bir formül olduğunu söylese de ben bu formülü pek kullanmam. Kesişimler için kullandığım başka formüller var ve özet tabloları da kaspayacak daha genel formüller. Onları bu sayfada ele alıyor olacağız.

NOT:Yukardaki formülü uzun uzun elle yazmak yerine geçici olarak "Generate Getpivotdata" seçeneğini aktive edip, arkasından Values bölümünden herhangi bir hücre seçtiğinizde, Excel size otomatik olarak formülü üretir. Siz sadece bölge ve ürün adını parametrik yapacak değişklik için müdahale edersiniz.

Generate Report

Şimdi diyelim ki, yukardaki örnekte, her bölge için ayrı bir sayfada özet tablo oluşturmak istiyorsunuz. Tablomuzu şu hale getirelim, yani ROWS alanına Şube Adını, ROWS'un hemen üstündeki FILTERS alanına Bölge'yi koyalım.

Şimdi bu haldeyken, PivotTable alt menüsünde Options'ın yanındaki küçük oka ve akabinde Show Report Filter Pages... butonuna tıklayalım

Buna tıkladıktan sonra aşağıdaki kutu çıkacak, Bölgeyi seçip OK diyelim..

İşlem tamamdır, şimdi aşağıdaki gibi her sayfada ayrı bir bölgenin tablosunun oluştuğunu görebilirsiniz.

Dışardaki bir datayı kaynak olarak kullanmak

Bazen kaynak datamız excelde olmayabilir, bunu Access gibi bir veritabanından almamız gerekebilir. Şimdi de böyle bir örnek gösterelim. Kaynak olarak verdiğim excel dosyasına bir access veritabanı içine import edelim(Access bildiğinizi varsayıyorum, eğer bilmiyorsanız şimdilik bu kısmı atlayabilirsiniz)

Insert PivotTable dediğimizde karşımıza çıkan kutuda, işaretli yere bastığımızda database ve sonrasında tablo seçimini yaparız. bu tablodaki alanlar Özet Tablonun Fieldları olarak sağ panelde yerini alır. Sonra rutin işlemleri uygulayabiirsniz.

Burda altı çizilecek bir nokta şu olabilir. Accesteki tablonuzun boyutu çok büyükse kaynak datayı excel içinde tutmanıza gerek olmayabilir, bunun için PivotTable Options'ta Data sekmesine "Save sourca data with file" seçeneğindeki işareti kaldırmanız yeterlidir.

Pivot Cache ve birden fazla görünüm

Bir özet tablo yarattığımız zaman, Excel arka planda bizim göremediğimiz bir kopya data üretir ve bu data üzerinden özet tabloları manipüle eder. Buna Pivot Cache denir. Pivot Cache sayesinde, yukarda gördüğümüz gibi kaynak datayı dosyadan ayırdığımızda bile Özet tablomuz çalışmaya devam eder. Böylece dosya boyutumuz da önemli ölçüde küçülmüş olur. Peki kaynak datayı görmek istersek ne yapıcaz? Grand Total'e çift tıklayarak drill-up yaparız ve işte size kaynak data! Tabi PivotTable Options>Data>Enable Show details seçeneğinin işaretli olması lazım.

NOT:2007 öncesinde PivotCahe ile ilgili önemli bir sorun vardı. Siz aynı özet tablodan farklı görünümler elde etmek için kopya aldığınızda her bir özet tablonunu ayrı bir PivotCahesi oluyordu, bu da dosya boyutunu artırmaktaydı. 2007 verisyonuyla birlikte Excel, paylaşılan PivotCahce yöntemini devreye alarak bu sorunu çözmüştür. Ancak yine de bazen Cachelerin ayrı olmasını isteriz, çünkü aynı cacheden beslenen tablolardan birinde refresh yapıldığında hepsi birden refresh olur veya birinde bir gruplama yaptığınızda tüm diğer özet tablolarda da aynı gruplamanın olduğunu görürsünüz. İşte, böyle birşeyin olmasını istemiyorsanız, ki bazen istemeyeceksiniz, cachelerin farklılaştırılması gerekir. Bunun için şu adımları uygulayın:

1. İkinci özet tabloyu kesin(Cut)
2. Bunu sıfır bir dosyaya yapıştırın
3. Sıfır dosyadaki bu özet tabloyu refreshleyin
4. Copy ile hafızaya kopyalayın
5. Orjinal dosyaya yapıştırın
6. Geçici dosyayı kapatın

Yeni özettablo şimdi kendi cache'sini kullanacak ve diğerleri refresh olduğunda bu refresh olmayacak, ayrıca yine diğerlerindeki gruplamadan da etkilenmeyecektir. Bunu yapmanın bir yolu da uygun bir VBA kodu çalıştırmaktır. Ancak bunu ve pivotcachelerle ilgili başka neler yapılabilir bilgisini konuyla ilgili VBA sayfalarına gelince göreceğiz.

İşlem olduktan sonra PivotCacheleri görmenin tek yolu ise aşağıdaki gibi bir VBA kodu çalıştırmaktır.

Sub cacheleri_gor()
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    Debug.Print pt.Parent.Name, pt.Name, pt.CacheIndex
  Next pt
Next ws

End Sub

Options butonunda 6 sekme bulunur. Bunlara tek tek bakalım. Burada önemli bir nokta var, o da şu: Buradaki yapılacak ayarlamaların her Özet Table için ayrı ayrı olmasıdır. Yani genel bir Pivot Table ayarlaması yapılmamaktadır.

Options>Layout & Format sekmesi

Burda boş değerlerin(Yani ana datada verisi olmayan) ve hatalı(#N/A, # DIV/0 gibi) değerlerin nasıl gösterileceğine ait seçenekler var. Boş dataların boş görünmesi bazen sıkıntılara sebep olabilmektedir, o yüzden bunların 0 görünmesini isteyebilirsiniz. Hatalı değerler de yine bazı durumlarda sıkıntı yaratır hatta bir de diptoplamın da hatalı görünmesine neden olmaktadır. Bunu da 0 olarak ayarlayabilirsiniz.

Örneğin Başkent1 bölgesinin Ürün3 ve Ürün4ü boş geliyor olsa ve biz bu ayarlamayı yapsaydık tablomuz şöyle görünürdü:

Tabi yine diptoplamın düzelmediğini görüyoruz ancak, tabloyu Pivottan çıkarıp normal Range haline getirdikten sonra basit bir toplam alma formülüyle istediğimiz sonuca ulaşırız. İdeal çözüm tabiki kaynak datada hatalı sonuçların olmamasını sağlamaktır.

Options>Totals & Filters sekmesi

Burada sütun ve satırlarda diptoplam gösterilecek mi gösterilmeyecek mi bunun ayarlaması yapılır. Bunu başka yerlerde de yapabildiğimiz için burada detaya girmiyorum.

Ayrıca, bir alan üzerinde çoklu filtreleme yapma imkanı da verir. Default olarak bu ayar seçimsizdir. Şimdi diyelimk ki İstanbul bölgerinden 110 mio üzerinde hacmi olan bölgeleri filtrelemek isityoruz. Önce İstanbulları filtreleyelim. Label Filters>Begins With=İst yazalım

Şimdi bi de Value Filter alanına greater than 110.000.000 yapalım

Gördüğünüz gibi ikinci filtre ilkini ezdi. İkisini aynı anda yapmak için aşağıdaki sarı renkli Allow multiple filters per field kutusunu işaretleylim.

Şimdi hem Label Filters'ı hem Value Filtersi tekrar uygulayalım, sonuç aşağıdaki gibidir. Bu arada Row Labels'taki filtre işaretine tıkladığınızda her iki Filtrede de Tick işareti olduğunu görebilirsiniz.

Options>Display sekmesi

Burda çok kritik bir seçenek yok. Belki eski bildiğimiz Pivot Table formatına dönmemize izin veren seçenek ilginizi çekebilir. Aşağıdaki renkli kutuya tıklayarak bu isteğinize kavuşabilirsiniz.

Diğer seçenekler de zaten yeterince açıklayıcı olduğu için detaya girmiyorum.

Options>Data sekmesi

Data menüsündeki önemli özellikler şunlardır.

• Sarıyla işaretli seçenek(Save source data) default seçili gelir. Bunu seçmezseniz, pivot tabloya kaynaklık eden PivotCacheyi farklı bir dosyada kaydeder, bu da dosyanın boyutunu küçültür. Çok büyük kaynaklı dosyalarda bu işlemi yapabilirsiniz. Aşağıdaki aynı dosyanın kaynak datasını dosyayla birlikte kaydedilip kaydedilmeme durumundaki boyut farkı görünmektedir.
   
  Bu seçeneğin işaretlenmesiyle dosya boyutu artar ama dosya açılışı daha hızlı olur, seçenek işaretlenmezse boyut küçülür ama açılış hızı süresi uzar, zira o sırada pivotcache yeniden yaratılmaktadır.
• Yeşilli seçenek de(Refresh data ...), dosya açılır açılmaz pivot tablonun otomatik güncellenmesini sağlar. Bu, özellikle kaynak datanın gece belli bir saatte schedule edilmiş olması durumunda kullanıcıların dosyayı açtığında güncel datayı görmesi adına faydalı bir seçenektir. Ancak otomatik refreshin rahatsız edici olabileceği durumlarda bu seçenek kapatılıp, kullanıcılara uygun bir bilgilendirme de yapılabilir.
  
  Eğer, kaynağı dosya içinde kaydetmemeyi tercih ettiyseniz, bu yeşilli seçeneği seçmenizde de fayda var. Aksi halde, bu tablo üzerinde çeşitli filtre v.s işlemi yapmaya çalıştığınızda(veya mail göndermek amacıyla boyut küçülttüyseniz, dosyayı alan kişi işlem yapmaya çalışırsa) şöyle bir uyarı mesajıyla karşılaşır: “The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report.”. Bu bir hata değildir ama tecrübeyle sabittir ki insanlar bunu hata sanıp hemen siz arıyorlar, "dosya bozulmuş" diye. O yüzden dosya açılır açılmaz refresh olsun ki, bu tür şikayetlerle uğraşmayın. Olur da unutursanız, yapılması gereken ilgili pivot tabloyu manuel refreshlemektir.
• Bu yukardaki seçeneklerde renksiz gösterilen seçenek işaretli iken DrillDown yapılabilmektedir, eğer Özet tablo üzerinde bir hücre çift tıklandığında drill yapılmak istenmiyorsa bu işaret kaldırılır.
• Pivot tabloların kaynağı olan alanlarda bazı datalar silinse bile normal ayarlara göre bu data gerek özet tablonun kendisinde gerek bu özettablo üzerine eklenmiş Slicerlarda görünmeye devam eder. Bunların görünmemesi için Data sekmesindeki Retain items deleted from the data source seçeneğini None yapmanız gerekir.
  

Sağ tık>(Value)Field Settings

Özet tablonun Satır/Sütun alanlarında mı yoksa ortadaki data alanında olup olmadığınıza bağlı olarak sağ tıkladığınızda iki farklı Field Settings kutusu çıkar.

Önce orta alanda sağ tıkladığımızda çıkan Value Field settingse bakalım. Burada iki sekme bulunmaktadır.

Burdaki seçeneklere aynı zamanda bir hücreye sağ tıklayarak da ulaşılabilmektedir.

İlk sekmedeki seçenekleri zaten tüm örnekler boyunca oldukça kullandık. Burada Toplam, Adet, Ortalma, Min, Max gibi standart  hesaplamalar var. Aslında çok önemli bir eksik vardı, Distinct Count, o da Excel 2013 ile eklendi, ancak listede doğrudan göremezsiniz. Bunu Data Model'e ekleyerek görebiliyoruz.

Data modele ekleme konusu PowerPivotla alakalı olduğu ve daha geniş bir yer vermek gerektiği için bunu bu sayfada ayrıca ele alıyoruz.

Ancak burada Normal Pivotla ilgili olarak Distinc Count konusuna deineceğiz. Özet Tablo hazırlarken bazen bir kolondaki tekil(uniqe) adetleri saydırmak istersiniz. SQL diliyle söyleyecek olursak "Distinct count" almak istersiniz. Excel 2013le birlikte artık bunu Data Modele ekleyerek yapabiliyoruz. Önceki versiyonlarda böyle bir özellik malesef yok. Aşağıdaki göresellerde işlemin nasıl yapılacağı görülmektedir.

Gördüğünüz gibi normal Count ilk bölge için 132 sonucnu veriyor çünkü, 4 ayrı ürün için çoklama yapıyor. Halbuki bu bölgede 33 şube var, işte bunu da Distinct Count veriyor.

Value Field Settings'in ikinci sekmesinde ise bir değeri Toplam/Adet gibi standart hesaplama şekilleriyle değil de, birşeyin yüzdesiz olarak gösterme imkanı buluyoruz.

Aşağıdaki örnekte % of Row Total yaptım:

Tabi burda ürünlerin tutarları orantsız olduğu için daha çok son iki ürünün ağırlığı yüksek çıktı. Böyle bir analizden ziyade bir ürün hangi bölgede ne kadar paya sahip, bunu görmek isteyebiliriz. Bunun için de % of Column Total demek gerekiyor.

Şimdi örnek dosyamızın ikinci datası olan liste üzerinden bir pivot yapalım. Aylık bazda ürünlerin hacmi ne olmuş, onu görelim. Tablomzu şöyledir.

Peki bu tabloda mesela Ürün1 toplamda ne zaman mesela 80 milyonu geçmiş, onu görmek istiyorum, diğer ürünler için de belli eşikleri ne zaman geçmiş görmek istyorum. O zaman daha önce gördüğümüz Running Total in .. seçeneğini seçeriz. Base Field olarak da Ay seçeriz ve tabomuz bu hale gelir. Gördüğünüz gibi Ürün1 ağustos ayında 80 bandını geçmiş.

Rank, Difference gibi diğer seçenekler üzerinden de siz alıştırma yapabilirsiniz.

Diğer alanlar

Printing ve All text alanlarıyla çok işim olmadı, sizin de çok olacağını düşünmüyorum ancak kurcalamak isterseniz de kurcalayabilirsiniz.

Bir özet tablodaki herhangi bir hücreyi seçtiğinizde Ribbon'da Analyze ve Design isimli iki menü ortaya çıkar. Bunların içindeki bir çok alt menüyü zaten yukarda kısım kısım gördük. Nedendir bilmem, Microsofttaki abiler bazı araçları sadece bir yere değil birden fazla yere koyuyor. Mesela Özet tabloların altında veya sağında diptoplam görünsün mü görünmesin mi kararını hem Options butonundan hem de Design menüsünden verebiliyorsunuz.

Bazı özellikler ise kurcalayarak çok kolay keşfedebileceğiniz detayda. O yüzden bunlara da girmiyorum. Şimdiye kadar bahsetmediğimiz bir iki özellik var, onlara bakalım.

ANALYZE>Gruplama

Şimdi diyelim ki Özet tablonuzun satır sayısı çok fazla ve burada gruplanabilecek bazı kayıtlar var. İlk etapta sadece bu grubu görmenin yeterli olduğunu, isterseniz detaya daha sonra inebileceğinizi düşünüyorsunuz. Bunun için verileri gruplama toolunu kullanacağız. Bu örnekte, bölgeleri İstanbul ve İstanbul dışı olarak gruplayalım.

Gruplayacağım bölgeleri seçiyoruz, sağ tıklayarak Group diyoruz(bunu Analyze menüsünde de yapabilirdik)

Şimdi aynısını bi de İstanbulları seçerek yapıypruz.

Gördüğünüz üzere Bölge2 adında yeni bir alan eklendi ve gruplara otomatik olarak isim verildi. Yeni eklenen alanın aynı kolonda mı yoksa yeni açılan bir kolonda mı geleceği, Özet Tablonun formatına bağlı olarak değişir. Compact Form'daysa aynı kolonda gelir, Outline veya Tabular formda ise farklı kolonda. Aşağıdaki örnekte Outlime formda olduğu için farklı kolonda geldi.

Biz bu isimleri ilgili hücrelere gelerek ilave bir şey yapmadan direkt değiştirebiliyoruz, aşağıdaki gibi. Group1'i İstanbul Dışı, Group2'yi İstanbul olarak değiştirdim.

Tarihleri ve sayılar sözkonsu olduğunda gruplamanın özel bir şekli de oluyor. Başlangıcı ve bitişi belli olan gruplar, tek tek seçim yapmadan kolaylıkla oluşturulabiliyor.

Ben yarıyıllık bir görüntü elde etmek istediğim için aşağıdaki gibi seçim yaptım.(1 Ocaktan başlamadığı için 182 gün demedik, 152 dedik)

Yine bölge isimlendirmesinde olduğu gibi burda da ilgili dönemleri manuel değiştirebiliyorum.

Ancak burda farkettiyseniz, Bölgelerde olduğu gibi Collapse/Expand(+/-) butonları gözükmedi. Eğer bunların gözükmesini istiyorsanız yine manuel seçerek ilerleyebilrisiniz, yani ilk 6 ayı seçip 1.YY diyip, temmuz sonrasına ise 2.yy şeklinde gruplayabilirsiniz. Böyle yapınca +/- butonları çıkar.

ANALYZE>Fields, Items & Sets(Calculated Fields&Items)

Calculated Field

Bazen elinizdeki kaynak datada eksik bir kolon olduğunu görürsünüz. Bunun için bir çözüm şekli, kaynak tabloya bu kolonu eklemek olabilir. Bir diğer çözüm ise Özet Tablo işlemi uyguladıktan sonra manuel formül yazmaktır, ama bunun neden ideal çözüm olmadığını da az sonra göstereceğim. İşte, Calculated Fields bu iki yönteme bir alternatiftir.

Şimdi diyelim ki aşağıdaki gibi bir tablomuz var, bunu Kanal bazında özetleyeceğiz. Ama oransal bir bilgi olan faiz oranını da özet tabloda görmek istiyoruz. Oransal kalemleri özet tablolara doğrudan almak doğru değildir, zira bunların toplanmaycağı aşikardır, bu örnekte ortalama aldırmak da doğru değildir, çünkü her kredinin tutarı farklıdır. Bu yüzden bunların ağırlıklı ortalamasını almak gerekir. Bunun için de pay ve paydayı ayrı ayrı toplayıp, bu toplamlar üzerinden işlem yapmak gerekir. Bütün bu işlemi datayı çektiğmiz SQL'de de yapabilirsiniz ancak bu datanın daha uzun sürede gelmesine neden olur.

Şimdi ilk olarak, hatalı sonuca bakalım. Yani doğrudan, faiz oranının ortalamasını alalım.

Şimdi de öncelikle manuel formül(SUMIF) yazarak sonuca ulaşalım, ama bu çok sağlıklı bir yöntem değildir, zira satır ekleme/eksilme durumlarında, mesela belli bi anda kanal sayısı 4 olabilir, başka zaman 2 olabilir, böylece manuel formül kolonunuda eksik veya fazla satır olabilir. Ayrıca diptoplam için girilen formül SUMIF değil, SUM olacak, yine burdaki satır sayısı değiştirdikçe buna da müdahale etmek gerekecektir. Bu yüzden rakamı doğru vermekle birlikte bu yöntem ideal çözüm değildir.

Şimdi ideal çözüm olarak Calculation Field yaratmaya bakalım.

Yeni alanımız sağdaki panelin en altında yerleşir.

Sonuç da aşağıdaki gibi olur.

Dikkat:Calculated Fieldlarda formüle yazdığınız her şey ayrı ayrı işleme girer. Ör:Calculated Field'daki formülünüz A*B şeklinde ise, sol taraftaki row label bazında A'ları toplar, sonra da B'leri toplar. Bu toplamları çarpar, yani ilgili row label kalemini oluşturan tüm satırlar için A*B yapıp da bunları toplamaz. O yüzden biz Tutar*Faiz yapmadık, Faizgeliri/Tutar yaptık. İhtiyacınız böyle birşeyse Table içine A*B şeklinde bir kolon hazırlayın, sonra da bunu Özet tablo içine normal bir alan olarak alın.

NOT:Hesaplanmış alanlar, normal alanlardan faklı olarak sadece Value olarak kullanılır, yani Row veya Column'a gelemezler. Bunlar için Calculated Item kullanıyoruz.

Bir diğer husus da Calculated fieldlarda sadece Toplama işlemi yaplır. Field settingsten ortalama veya adet seçseniz bile işe yaramaz.

Caldulated Item

Bazen de Rows veya Columns'ta yer alan alanlardan yeni bir alan türetmek istersiniz. Mesela Başkent1 ve Başkent2 diye iki bölge var diyelim, bunlardan Başkent diye bir Ana bölge türetmek isteyebilirsiniz. Bunu da Calculated Item ile yapıyoruz. Bu biraz da yukarda bahsettiğimiz Gruplamaya benziyor, ancak gruplamadan bir farkı, burda +/- düğmeleriyle açıp daraltacağımız bir formatın oluşmaması, özet tablomuzun adeta yeni bir kayıt eklenmiş gibi görünmesidir. Daha büyük farkı ise burada iki veya daha fazla şeyi toplamaktan daha karışık formüller de yazabiliyor olmamız.

Mesela aşağıdaki işlemi Gruplama ile de yapabiliriz. Görüntü açısından fark dışında pek de bir fark yok gibi görünüyor.

Ancak bazen Gruplama yetersiz kalır, işte böyle durumlarda mecburen Calculated Item yaratmak gerekir.

Bu arada yukarda, elemanlara doğrudan isimleriyle ulaştık, bunlara index numarası ile de ulaşılabilir. Bu index numarası da mutlak veya göreceli olabilir. Mutlak index, ilgili alanın hangi sütun veya satırının seçileceğini belirtirken, göreceli index ise yeni hesapladığımız Calculated itema olan uzaklığı belirtir. Yeri her defasında sabit olan alanlar için mutlak index kullanılması gerekirken yeri sürekli değişebilen durumlarda göreceli index kullanmak gerekir. Bunlarla ne demek istediğimi az sonraki örnekle daha iyi anlayacaksınz. Göreceli başvurularda + ve - işaretlerini kullanırız.

Biz şimdi iki yöntemi de kullanacağımız bir örnek yapalım. Mesela tarihsel datamızda Ay alanı için bir Calculated Item yaratalım. İhtiyacımız olan şey de hep son ay ile ilk ay rakamlarının oranını yani yıllık büyümeyi gösteren bir alan. Bunu yeni bir Ay elemanı gibi düşündüğümüz için Calculated Item yapıyoruz. Datamızın ilk hali aşağıdaki gibi ve yeni alanımız en sona eklensin istiyoruz.

Bu örnekte ilk ay datasının yeri sabittir ve hep birinci kolondur, o yüzden buna Ay[1] ile ulaşacağım, yeni alanımızı en sona koyacağız, yani son ay kolonu da hep bundan bir önceki kolon olacak, o yüzden buna da Ay[-1] göreceli başvurusu ile ulaşacağım. Bu durumda yeni yaratacağımız Item'ın formülü aşağıdaki gibi olacaktır.

Şimdi diyeceksiniz ki, neden Ay[-2] yazdık, az önce Ay[-1] demiştik. Çünkü bir de hep son ay bir önceki aya göre ne kadar büyümüşüz buna bakmak istiyorum, bunun için de hep son 2 ayın farkına bakacak formülü yazarım, ki bu formül sadece göreceli başvuruları içeriyor olacak.

Şimdi bu da yeni bir kolon olarak geleceği ve ilk yarattığımız Calcuated Item'ın bundan sonra görünmesini istediğim için, bundaki göreceli indexi -1 yerine -2 yaptım.

Sonuç aşağıdaki gibi olacaktır.(Başkent2'nin Ocak rakamı eksik olduğu için DIV/0 hatası çıkmış ve bu hata diptoplama da sirayet etmiş, buna şimdilik takılmayın, gerekirse IFERROR ile hataları sıfır getirtebilirsiniz)

Bir de Solve Order diye bir şey var, oluşturduğunuz Calculated Itemların sırasını belilersiniz. Bu özellikle, önce A hesaplansın, B'nin hesabında A kullanılacak tarzı durumlar varsa önem arzetmektedir. Ben burda bu detay girmiyorum, böyle bir ihtiyaç olursa bu ekrandan o işi kolayca halledebilirsiniz.

DESIGN>Layout

Diyelim ki "Bölge Kodu - Ürün - Ay - Aylık Gerç" formatındaki (örnek dosyada tarihsel data sayfasında) tablonuzu bölge detayı olmadan yani bölge toplamında görmek istiyorsunuz. Çünkü ya nihai tablo üzerinde vlookup işlemi kullanacaksınız, veya başkalarına liste olarak göndereceksiniz, bu kişiler filtreleme v.s yapmak istediklerinde yapamayacak. Artık bunu Excelde yapmak çok kolay. Hemen bakalım:

Aşağıdaki gibi Pivot alma işlemini yapın,

Sonra, Design>Report Layout>Show in Tabular Form seçeneğini seçin, rapor aşağıdaki gibi görünecektir.(Bir diğer seçenek de Outline Form gösterim şeklide veya Classic Pivot Table gösterimi olabilir)

Son olarak yine Design>Report Layout>Repeat all Item labels seçeneğini seçin. Bu işlemi Field settingsi açıp(tabloda herhangi bir yere sağ tıklayarak veya Analyze menüsünden)  Layout&Print sekmesinden de yapabilirsiniz.

Ve işte yeni tablomuz!

Gördüğünüz gibi vlookup yapmak için ideal bir liste haline geldi. Sanki ana tablodan bölge kolonu çıkarılmış  ve rakamlar otomatik olarak ürün-ay seviyesinde yeniden hesaplanmış gibi. Bu şahane özellik Excel 2010 ile aramıza katıldı. Bundan önce ben bu işi Excelent'ta bulunan "pivotta oto doldur" makrosu ile hallediyordum, böylece uzun yıllar kullandığım ve birçok kişinin de kullandığı bu makrom da çöp olmuş oldu :) Ancal Hala 2010 öncesi Excel kullananlar Excelent ile istenilen sonuca ulaşabilirler.

Slicer ve Timeline

Excelin 2010 versiyonu ile Özet Tablolara Slicerla kolay filtre uygulama imkanı gelmiştir. Slicer özelliği Excel 2013 ile birlikte Listeli Tablolara da uygulanabilir hale geldiği için bu konuyu başka bir sayfaya aldım. Buradan bakabilrsiniz.

TimeLine ise, yine Özet tablolara uygulanabilen bir zaman filtreleme yöntemidir. Geniş bir zaman aralığına ait bir datayı özet tablo haline getirdiniz diyelim, ancak belli dönemlerde sadece belirli bir yıla, aya, çeyreğe veya günlere ait veriyi görmek ve hatta bunu bir grafik eşliğinde incelemek isteyebilirsiniz. Aşağıda farklı bir data setine ait bir özet tabloya hem Slicer hem TimeLine filtresi uygulandığnı görebilrsiniz. Slicer'dan sadece TL seçilmiş, Timeline'dan da Kasım ayı seçilmiş. Tabi Kasım'ı seçebilmem için Zaman frekansının Ay olarak seçilmiş olması gerekmektedir, bunu da görselde görebilirsiniz.