13.03.2018 tarihinde,VBA konularına Worksheet Olayları sayfası eklenmiştir.

16.02.2018 tarihinde,VBA konularına Workbook Olayları sayfası eklenmiştir.

28.12.2017 tarihinde,VBA konularına Dosya Okuma/Yazma sayfası eklenmiştir.

20.12.2017 tarihinde,sitem mobil uyumlu hale gelmiştir.

ExcelInsert Menüsü1

Pivot Tables(Özet Tablolar)

Genel Bakış

Elimizde liste olarak bulunan bir data kümesinin ç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ına buradan ulaşabilirsiniz)

Ş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.)

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ı bakımı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 diyip ilerleyelim.

OK diyip 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 alanı sürükleyerek oynayabilirsiniz.

Detaylar

Pratik hususlar

  • Pivot 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)
  • 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 Value Fields Settingste de yapabilirsiniz, en üstteki Custom Name yazan yere yeni başlığınızı yazın
  • PivotTabloları 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.

Ertelenmiş Güncelleme(Defer Layout Update)

Özet Tablonuzda değişklik yapmak istediğinizde her değişklik sonrasında tablonuz otomatik güncellenir, ancak bazen tablonuzda birden çok değişklik 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 işiniz 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 çok faydalıdır.

Rakamsal İçerik

Values bölümüne sürüklediğniz 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 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ı

Şimdi bu tarihsel datayı Pivotlayalım

Show Value as diyip Running In ... diyelim ve Ay alanını seçelim. Ben daha okunaklı görünmesi adına Classic Pivot seçneğini de işaretledim ve Repeat Row Labels dedim. Gördüğünüz

Detaya inme(Drilling)

Values alanınıdaki bir hücreye çift tıkladığnızda ilgili kesişim kümesindeki detayalı bilgi yeni bir sayfada açılr. Bu örnekte B4'e tıkladığınızda karşımıza çıkacak görüntü şu olacaktır.

GETPIVOTDATA

Pivotdata 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 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. Resimden görüleceği üzere kesişim rakamını getirdi. Tabi, bu örnek bunu aynı sayfa üzerinde yapmak anlamsız olabilir, ancak daha büyük Özet Tablolarda oldukça kullanışlı bir formüldür.

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

(İlave örnekler ayrıca konacaktır...)

Generate Report

Şimdi diyelim ki, yukardaki örnekte, her bölge için ayrı bir sayfada pivot table 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ölgenini tablosunun oluştuğunu görebilirsiniz.

Options Butonu

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.???????????????????????

Layout & Format

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.

Öncesi şöyle olan bir tabloyu,REİSMLER HATALI

şu değişilikler sonrasında

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. Ancak ideal olanı tabiiki kaynak datada hatalı sonuçların olmamasını sağlamaktır.

Totals & Filters

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.

Display

Burdaki en önemli seçenek, eski bildiğimiz Pivot Table formatına dönmemize izin veren seçenektir. 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.

Data

Data menüsünde iki önemli özellik şunlardır.

  • Sarıyla işaretli seçenek default seçilir 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.

  • Yeşilli seçenek de, dosya açılır açılmaz pivot tablonunu otomatik güncellenmesini sağlar. Bu, özellikle kaynak datanın gece belli bir saatte schedule edilmiş olması duurmunda kullancıların dosyayı açtığında güncel datayı görmesi adına faydalı bir sçeenktir. Ancak otomatik refreshin rahatsız edici olabileceği durumlrda bu seçenek kapatılıp, kullanıcılarıa uygun bir bilgilendirme de yapılabilir.

  • Bu yukardaki sçeeneklerde renksiz gösterilen seçenek işaretli iken DrillDown ypaılabilmektedir, eğer PT üzerinde bir hücree çift tıklandığında drill ypıalmak sitenmiyorsa bunadki iaşret kaldırılırç

Diğer alanlar

Pritinnig ve All text alanlarıyla çok işim olmadı, siz kurcalamak isterseniz krucalıyın

Fields Settings

Field Settings

filed settingsteki iki sekme bulnmaktadır

Burdak iseçenekleere aynı zamanda bir hücreye sağ tıklayarak da ulaşılabilemtekdir.

ilk seçeneği zaten tüm örnekler boyuınca oldukça kullandık. Bura Topam, Adet, Ortalma, Min, Mac gibistandart  hesplamalar var. Aslında çok önemli br eksik vardı, o da Distinct Count, o da Excel 2013 ile eklendi, ancak listede doğrudan göremezsiniz, bunu Data Model konusunda ayrıca göreceğiz.

ikinci seçenekte ise bir değeri Toplam/Adet giib standart hesplama şekilleriyşle deil de, birşeyin yüzdesiz olarak.

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öremek isteyebliriz. bununu için de % of Colum total demek gerkeiyor

Şimdi örnek dosyamızın ikinci pivotu olan liste üzerinden bir pivot yapalım. Aylık bazda ürünler 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çinde belli eşikleri ne zaman göreçmiş görmek istyorum. Ozaman Runnign Total in .. seçeneğini sçeeriz. Base Field olarak da Ay seçeriz. ve talbomuz bu hale gelir. Gördüğünüz gibi Ürün 1 ağustos ayında 80 bandını geçmiş.

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

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ömrenin 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ı sçeerek yapıypruz.

Gördüğünüz üzere Bölge2 adında yeni bir alan eklend ve gruplara otomatik oalrak 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 geğ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 fakrlı kolonda geldi.

Biz bu isimleri ilgili hücrelere gelerek ilave bir şeyyapamdan direkt değiştirebiliyoruz, aşağıdaki gibi.

Tarihleri ve sayılar sözkonsu olduğunda gruplamama özel bir şekli de oluyor. Başlaangıcı ve bitiş belli olan gruplar, tek tek sçeim yapmadan kolaylıkla oluşturulabiliyor

Ben yarıyıllık bir görüntü elde etmek istediğim için aşağıdaki gib seçim ypatım.

yine bölge isimlerndirmesinde olduğu gibi burda da ilgili dönemleri manuel dğeiştirebiliyorum

Ancak burda farkettiiyseniz, Bölgelerde olduğu gibi Collapsa/Expand(+/-) butonları gözükmedi. eğer bunların gözükmesin istiyrosanız yine manuel seçere ilerleyebilrisiniz, yani ilk 6 ayı sçeip 1.YY diyip, temmuz sonrasına ise 2.yy şekliden gruplaaybilrisiniz.

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 oalrak verdiğim excel dosyasına bir access veritabanı içine import edelim(Access bildiğinzii varrsayıyırum, eğer iblmiyorsanız şimdili bu kısmı atlayabilirsniz)

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

Burda önemli bir nokta şu olaiblir. Accesteki talbonuznu boyutu çok büyükse kaynak datayı excel içinde tutmanıza gerek olmayabilir, bunun için PT options'ta Data sekmesine "Save sourca data with file" sçeenğindeki iaşreti kaldırmanız yeterkidir.

Data Model

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

Ancak burada Normal Pivotla ilgili olarak sadece şunu söylemkete fayda var. Özet Tablo hazırlarken bazen bir kolndaki 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.

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.

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 deverey alarak bu sorunu çözmüştür. Ancak yine de bazen Cachelerin ayrı olmasını isteriz, çünkü aynı cacheden beslenen tablolardan birinde refreh 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 cachelerin farklılaştırılması gereki. 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. Güncelleyin(Refresh)
  4. Hafızaya kopyalayın
  5. Orjinal dosyaya yapıştırın
  6. Geçici dosyayı kapatın

Yeni özettablo şimdi kendi cachesini kullanacak ve diğerlkeri refresh olduğunda bu refesh olmayacak, ayrıca yine diğerlerindeki gruplamadan etkilenmeyecek. Bunun yapmanaın bir yolu da uygun bir VBA kodu çalışıtırmaktır. Ancak bunu Özettablolarla 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

Pivotcachelerle ilgili başka neler yapılabilir, bunu VBA sayfalarında inceleyeceğiz.

Menüler

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. Bazıları ise sizin kendinizin kurcalayarak çok kolay keşfedebilecğeiniz detaylar. O yüzden bunlara girmiyorum. Şimdiye kadar bahsetmediğimiz özelliklere bir bakalım.

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

Calculated Field

Bazen elinizdeki kaynak datada eksik bir kolon olduğunu düşünürsünüz, bunun için biz çözüm şekli kaynak tabloya bu kolonu eklemeniz gerekir. İşte, Calculated Fields bu işleme alternatiftir. 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ı az sonra göstereceğim.

Şimdi diyelim ki aşağıdaki gibi bir tablomuz var, bunu Kanal bazında özetleyeceğiz. Ama oransal bir bilgi olan faiz tutarını da özet tablo da görmek istiyoruz. Oransal kalemleri özet tablolara doğrudan almak doğru değildir. Oransal kalemlerin toplanmaycağı aşikardır ancak bu örnekte ortalama aldırmak da doğru değildir, zira 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 Ortalama faiz için ortalama alalım.

Şimdi de öncelikle manuel formül 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 olaiblir, başka zaman 2 olabilir, manuel formül kolonunuda eksik veya fazla satır olabilir. Ayrıca diptoplam için girilen formül SUMIF dğeil, SUM olacak, anz yine burdaki satır sayısı değiştirkçe buna da müdhale etmek gerekfcektir. bu yüzden bu ideal çözüm değildir.

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

sağdaki panelin en altında yerleşir.

adfsadasd

afasdf

Dikkat:fomrüle yazdığını her şeyi ayrı ayrı işleme sokar. Ör:A*B şeklinde bir formülünü varsa, sol taraftaki roe labal bazında A'ları toplar, sonra da B'leri toplar. bu toplamalrı çarpar. yani ilgili row label kalemini oluşturan tüm satırlar için A*B yapıp da bunları toplamaz.

NOTLAR:1-HEsplanmış alanlar, normal alanlardan faklı olarak sadece Value olarak kulalnılır.

2-sadece toplam işlemi ypalır, filed settingsten ortalama, coutn yapsnz bile işe yaramz(umarız, ileri versiyonalrda bu da değiştir)

Caldulated Item

 

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 öncei Excel kullananlar Excelent ile istenilen sonuca ulaşabilirler.

 

 

YORUMLAR