Excel pivot tablo büyük verileri özetlemeye yarayan bir rapor yardımcı fonksiyonudur.
Excel Pivot Tablo Nedir?
Pivot Tablo, büyük miktarda veriyi hızlı bir şekilde özetlemenin etkileşimli bir yoludur.
Microsoft’un kendi sitesindeki tanıma göre; “PivotTable, verilerinize ilişkin karşılaştırmaları, kalıpları ve eğilimleri görmek amacıyla verileri hesaplamanıza, özetlemenize ve analiz etmenize yönelik güçlü bir araçtır.”
En güzeli de hiç formül bilmenize gerek yoktur. Sadece sürükle bırak ile pivot tablonuzu kullanabilirsiniz.
Verilerinizi satırlar veya sütunlar halinde düzenleyebilir ve bu düzenler arasında geçiş (veya pivot) yapabilirsiniz. Excel sizin için özetleri otomatik olarak hesaplar ve görüntüler.
Verilerinizin toplamını mı, ortalamasını mı yoksa başka bir özet türünü mü görmek istediğinize siz karar verirsiniz. Bu, büyük, dağınık bir yapboz parçaları kutusunu alıp net bir resim görmek için onları sıralamak gibidir.
Temel düzey
Excel Pivot Tablo nasıl oluşturulur?
Kaynak veriyi hazırlayın
Pivot Tablo oluşturmadan önce verilerinizin doğru şekilde ayarlandığından emin olun:
- Verilerin bir tablo formatında olması gerekir; sütunlar kategoriler olarak kullanılacak ve satırlar işlemler olarak kullanılır.
- Boş satır veya sütun olmadığından emin olun.
- Verilerinize ara toplam veya toplam satırlar eklemekten kaçının.
- Her sütunun benzersiz bir başlığa ihtiyacı vardır ve bu başlıklar her biri bir hücrede olmalıdır.
Bu makalede verilen pivot tablo örneklerini çalışmak için aşağıdan örnek excel dosyasını indirebilirsiniz.
Excel pivot tablo örnek çalışma dosyası indir
Başlıksız sütun olmamalı
Pivot tabloya veri hazırladığınızda başlıkların bir alan için kullanılacağını hatırlayın. Bu başlıkların hepsinin bir ismi olmalı. Eğer bir başlık boş ise size pivot tablo uyarı verecektir.
Sütunların Veri Tipleri Aynı Olmalı
Sütunların içinde değer olarak sakladığınız rakamların hepsi aynı olmalı. Yani rakam olmalı. Eğer içinde bir tarih varsa onu da pivot tablo rakam gibi algılar ve size tarih değil rakama çevrilmiş halini gösterir.
Toplam alanını kaldırın
Hazırladığınız verideki toplam satırını pivot tablo yine normal bir satır gibi görür, o satırın toplam satırı olduğunu anlamaz. Toplam satırını da toplayıp alt toplamı x2 katı olarak görmenize sebep olur. Toplam satırınız varsa kaldırın çünkü onu pivot tablo ile hazırlayacağız.
Veri Kaynağını Dinamik Tablo Haline Getirme
Pivot tablonun veri kaynağını aralık olarak ayarlarsanız, yeni satırlar eklediğinizde pivot tablo bu yeni sütunları veya satırları otomatik olarak algılamaz. Veri kaynağını değiştirip güncellemeniz gerekir. Pivot Tablonun yeni veri satırları içermesine izin vermek için, orijinal tablonuzu bir Excel Tablosuna dönüştürmeniz ilerideki kullanımlarınızı kolaylaştırır. Bir veri eklediğinizde tabloyu baz alarak pivot tablo kendini günceller.
PivotTable Oluşturmak için verinizi seçin.
- CTRL + L tuşlarına basarak verileri bir Excel Tablosuna dönüştürün ve Tablo Oluştur iletişim kutusunda Tamam’a tıklayın.
- Tablo Tasarımı -> Özellikler -> öğesini seçerek tabloya uygun bir ad verin ve varsayılan adı (büyük olasılıkla “Tablo1”) “AylıkSatışlar” gibi daha anlamlı bir adla düzenleyin.
NOT: Bu son adım isteğe bağlıdır, ancak tabloları daha kolay takip etmenize yardımcı olacaktır.
Pivot tablo oluşturma
Şimdi Pivot Tablo oluşturmaya gelelim.
Tablo Tasarımı -> Araçlar (grup) -> PivotTable ile Özetleme’yi seçin.
Aşağıdaki gibi bir kutucuk çıkacak.
Var olan çalışma sayfasını seçin ve bir hücre seçin. Pivot tablonuz hazır bile.
Klasik veriden Pivot Tablo oluşturun
Bir Pivot Tablo oluşturmak için aşağıdaki adımları uygulayın:
1 – Veri kümenizin parçası olan bir hücreye tıklayın.
2 – Ekle (sekme) -> Tablolar (grup) -> PivotTable’ı seçin.
3- Pivot tablonuzu bu sayfaya mı yoksa yeni bir sayfaya mı ekleyeceğinizi sorduğunda bu sayfada sağı ve aşağısı boş bir hücre seçebilirsiniz.
4- Ortaya şekillendirmeye açık boş bir pivot tablo çıkacak.
Önerilen Pivot Tabloları Kullanma
Nasıl başlayacağınızdan emin değilseniz veya sadece verilerinize bakmak istiyorsanız Excel’de pivot tablo oluşturmanın başka bir yolu daha vardır.
Verilerinize tıklayın, ardından Ekle sekmesi > Tablolar > Önerilen Özet Tablolar’a gidin.
Excel verilerinize bakacak ve çeşitli tablo tasarımları önerecektir. Bu öneriler, verileriniz hakkında sahip olabileceğiniz yaygın soruları ele almayı amaçlamaktadır.
Bu öneriler size tam olarak ihtiyacınız olan tabloyu verebilir. Eğer değilse, hedefinize doğru daha hızlı bir şekilde başlamanıza yardımcı olabilirler.
Önerilen tablolardan hiçbiri işinize yaramazsa, sol alt köşedeki Boş Özet Tablo’yu seçebilirsiniz. Bu, sıfırdan başlamanızı ve alanları kendiniz eklemenizi sağlar.
Pivot tablo veri kaynağı değiştirme
Bazen excel tablo yerine düz veri kullanabilirsiniz. Ve bazen bu veriye yeni bir sütun eklemek istersiniz.
Eğer en sonuna eklerseniz özet tablonun ilk seçtiğiniz veri kaynağının dışında kalırsa bu sütun özet tabloya dahil olmaz. Veri kaynağı değiştir fonksiyonuyla yeniden veri kaynağını pivot tabloya tanımlamalısınız.
Veri kaynağını değiştir’e tıkladığınızda yeni sütunun veri kaynağında olmadığını göreceksiniz.
Veri kaynağını tekrar belirlediğinizde yeni sütun özel tablonun içine dahil edilecektir.
*Bonus ipucu
Eğer verinizin en dışına yeni bir sütun eklerseniz bu sütunu dahil etmek için tekrar veri kaynağını belirlemek durumundasınız. Fakat verinin içinde araya bir sütun eklerseniz pivot tablo bunu otomatik olarak içeri alır.
Pivot Tablo Nasıl Yenilenir
Temel veriler değiştiğinde (eklemeler, silmeler veya değişiklikler), değişen verileri yansıtmak için raporu “yenilemeniz” gerekecektir.
Pivot Tablo raporunu yenilemenin birçok yolu vardır.
- Pivot Tablo raporuna sağ tıklayın ve Yenile’yi seçin.
- PivotTable Analyze (sekme) -> Data (grup) -> Refresh (Yenile) öğesini seçin.
- Veri (sekme) -> Sorgular ve Bağlantılar (grup) -> Tümünü Yenile’yi seçin.
- Kısayolu kullanın: CTRL + ALT + F5
İki pivot tablo üst üste gelemez
Bir sayfaya alt alta birden çok pivot tablo koyabilir ve saklayabilirsiniz. Pivot tabloda veriler hiyerarşik şekilde saklandığından aşamalardan birini açmak ve alt kırılımına bakmak isteyebilirsiniz. Alt kırılıma bakmak için aşamalardan birini açtığınızda pivot tablo aşağıya doğru uzayacaktır. Eğer altta klasik veri varsa üzerine yazacak ve veriyi silecektir fakat başka bir pivot tablo varsa onun üzerine çıkmayacağı için uyarı verecektir. Böyle durumlarda alttaki pivot tablonun yerini değiştirebilir veya biraz kaydırabilirsiniz.
Otomatik sığdır özelliğini devre dışı bırakmak
Varsayılan olarak, bir pivot tabloyu yenilediğinizde, veri içeren sütunlar verilere en iyi şekilde uyacak şekilde otomatik olarak ayarlanır. Normalde bu iyi bir şeydir, ancak çalışma sayfasında pivot tabloyla birlikte başka şeyler de varsa veya sütun genişliklerini dikkatlice manuel olarak ayarladıysanız ve değiştirilmesini istemiyorsanız sizi çıldırtabilir. Bu özelliği devre dışı bırakmak için pivot tablonun içine sağ tıklayın ve PivotTable Seçenekleri’ni seçin. Seçeneklerin ilk sekmesinde “Güncellemede Sütun Genişliklerini Otomatik Sığdır” seçeneğinin işaretini kaldırın.
Güncelleştirmeyi otomatik ertelemek hızlandırır
Verilerin çok olduğu raporlarda pivot tablo alanlara yenisini eklediğinizde hesaplayarak ekler. Bu da biraz zaman alabilir. Ağır dosyalarda çalıştığınızda ve birden çok alan eklediğinizde hepsini teker teker hesaplaması yerine tüm eklemeleri yaptıktan sonra veri güncellemesini yapmasını isteyebilirsiniz.
Bunun için yan panelde en altta “güncellemeyi ertele” alanına tıklamanız yeterlidir.
Pivot tablo alanları
Pivot Tabloya Alan Ekleme
Sol tarafta boş bir Pivot Tablo ve sağ tarafta raporu doldurmak için kullandığımız Alan Listesi adlı bir sürükle-bırak aracı görüyoruz.
Pivot Tablo Alan Listesi
Alan Listesi, kaynak verilerdeki başlıkları temsil eder.
💡 Pivot Tablo ile ilgili Şerit sekmelerini ve Alan Listesini göstermek için imlecinizin Pivot Tablo alanının içinde olması gerekir. Ayrıca PivotTable Analiz sekmesine tıklayıp Göster’e giderek ve Alan Listesi’ni seçerek Alan Listesi’ni gösterebilir veya gizleyebilirsiniz.
Ürünler hakkındaki sorularımızı yanıtlamaya başlamak için:
Alan Listesindeki Kategoriler girişine tıklayın ve basılı tutun ve Satırların içine sürükleyin.
Alan Listesi’nde Satış Yıllık Toplam girişine tıklayın ve basılı tutun ve Değerler’in içine sürükleyin.
Yukarıdaki rapor oluştu bile.
Varsayılan olarak, Pivot Tablo satışları toplar. Saymayı, ortalamayı veya başka bir şey yapmayı tercih ederseniz, rapordaki herhangi bir satış rakamına sağ tıklayın, “Değerleri Özetleme Ölçütü…” seçeneğini belirleyin ve istediğiniz yöntemi seçin.
Bu seçenekler şu şekilde sonuç veriyor
- Toplam: ayların toplamını verir
- Say: Kategoriden kaç tane veri olduğunu verir
- Ortalama: Kategorideki toplamı veren rakamların ortalamasını verir
- En büyük: Kategori içindeki en büyük değer
- En küçük: Kategori içindeki en küçük değer
Eğer sağ tıkla menüsünden diğer seçenekleri seçerseniz standart sapma, varyans gibi istatistiksel hesaplamalara da ulaşabilirsiniz.
Pivot Tablo Alanlarının kullanımı
Pivot tablonuzun görünümünü değiştirmek için alanlarınızı 4 farklı alana yerleştirebilirsiniz.
Filtreler
Filtreler alanına bir alan eklediğinizde, pivot tablonun sol üst köşesinde bir açılır menü görünür. Bu menü, tabloyu söz konusu alandaki benzersiz kategorilere göre filtrelemenizi sağlar.
Sütunlar
Alanları Sütunlar alanına yerleştirmek dikey sütunlar oluşturur. Bu sütunlar, alandaki benzersiz öğeleri başlıkları olarak kullanır.
Örneğin, kategoriye göre filtreleme yapmadan her kategoriyi kendi sütunu olarak göstermek istiyorsanız, kategori alanını Sütunlar alanına taşıyın. Ardından, her bir kategori için sütun etiketlerini göreceksiniz.
Satırlar
Alanları satırlar alanına yerleştirmek sol taraftaki satırların başlıklarını oluşturur.
Değerler
Değer alanı, sayılar gibi özetlemek istediğiniz alanlar içindir – miktarları, fiyatları, maaşları düşünün. Özetlemenin varsayılan yolu bunları toplamaktır, ancak bunu değiştirebilirsiniz. Ayrıca ne sıklıkta göründüklerini saymak için buraya metin alanları da ekleyebilirsiniz.
Bir Alanı Alan Listesinden Kaldırma
Bir alanı artık Alan Listesi bölgesinde istemediğinize karar verirseniz, alanı ana alan listesine geri sürükleyebilir veya ana listede o alanın adının işaretini kaldırabilirsiniz.
Alan Ayarlarını Özelleştirin
Alan ayarlarını açmak için dişli düğmesine tıklayarak Alan Listesini özelleştirebilirsiniz.
Sürükle ve bırak bölgelerinin düzenini değiştirebilir ve ayrıca alan adlarınızı sıralayabilir ve arayabilirsiniz.
Alanlarınızı genellikle verideki sıralamayla kullanırız. Fakat bazen çok fazla olduklarında alfabetik sıralama sizi daha hızlandırabilir. Alanlarınızı A’dan Z’ye sıralayabilirsiniz.
Yan yana bir düzen kullanmak genellikle varsayılan yığılmış kurulumdan daha fazla alan sağlar.
Pivot tablo temel biçimlendirme
Sonuçları Biçimlendirme
Pivot tablodaki sayıların doğru görünmediğini fark edebilirsiniz. Rakamlarınızı para birimi veya yüzde gibi özel bir şekilde göstermek istiyorsanız, herhangi bir satış rakamına sağ tıklayın ve “Sayı Biçimi…” seçeneğini seçin. Bu sizi doğrudan sayı biçimlendirme seçeneklerine götürür.
❗ Menüden “Hücreleri Biçimlendir” seçeneğini SEÇMEYİN. Bu sadece seçtiğiniz hücreleri değiştirir, raporun tamamını değil. “Sayı Biçimi “ni kullanmak, seçtiğiniz stilin yeni veriler eklendikçe uygulanacağı anlamına gelir; bu, “Hücreleri Biçimlendir” ile gerçekleşmez.
Örneğin yukarıdaki tabloda sadece bir hücredeki rakamın sonuna “,00” ekledim. Diğerlerine eklenmedi.
Yukarıdaki gibi alanı seçip sayı biçimini seçip biçimlendirme kutucuğunu açabilirsiniz.
Biraz daha pratiği var.
O da sağ tıklayınca menüdeki numara biçimine tıklamak
Numaraları alt menülere girmeden “para birimi, yüzdelik, ondalık ve virgül sonrası ayarlaması” için üst menüdeki hızlı butonları kullanabilirsiniz.
Boş Hücrelere Varsayılan Değerler Ekleme
Boş hücreleri 0 $ veya TBD (“belirlenecek”) gibi varsayılan bir değerle doldurmak için bir pivot tabloyu kolayca özelleştirebilirsiniz. Büyük veri tabloları için, birçok kişi aynı sayfayı incelediğinde bu hücreleri hızlı bir şekilde etiketleyebilmek değerli bir özelliktir.
Pivot tablonuzun boş hücrelerini otomatik olarak biçimlendirmek için tablonuza sağ tıklayın ve PivotTable Seçenekleri’ne tıklayın.
Görüntülenen pencerede, “Boş Hücreler İçin Göster” etiketli kutuyu işaretleyin ve bir hücrenin başka bir değeri olmadığında görüntülenmesini istediğiniz değeri girin.
İçeriğe Göre Otomatik Sütun Yeniden Boyutlandırma
Pivot Tablo, hiçbir verinin görsel olarak kesilmediğinden emin olmak için sütunları otomatik olarak yeniden boyutlandıracaktır. Bu, Alan Listesindeki alanları yeniden düzenlediğinizde, verileri sıraladığınızda, yenilediğinizde veya benzer bir işlem gerçekleştirdiğinizde işe yarar.
Ancak, sütunlarınızı seçtiğiniz bir boyuta yeniden boyutlandırdıysanız, bir sonraki güncellemede bu boyutları büyük olasılıkla kaybedersiniz.
Bu otomatik sütun yeniden boyutlandırma davranışını devre dışı bırakmak için PivotTable Analiz (sekme) -> PivotTable (grup) -> Seçenekler.
PivotTable Seçenekleri iletişim kutusunda, “Güncellemede sütun genişliklerini otomatik ayarla” seçeneğinin işaretini kaldırın.
Orta düzey
Pivot tablo görselleştirme
Pivot tablonun stilini değiştirme
Pivot tabloyu tablo olarak biçimlendirebilirsiniz. Bu da menüleri aç – kapa yaparken biçimlendirmenin olduğu gibi kalmasını sağlar.
Eğer her detayı ayrıca biçimlendirmek istiyorsanız en alttaki yeni pivot tablo stilini tıklayın.
Karşınıza bir kutucuk çıkacak.
Pivot tablonun her başlığına ayrıca bir biçimlendirme yapabiliyorsunuz. Biçimlendirmeyi seçtiğinizde tamam diyorsunuz. Ve stil biçimlerinin içine dahil oluyor. Yeni stili tıklayınca pivot tabloya uygulanıyor.
Koşullu biçimlendirme detaylı kılavuz.
Pivot tablo grafik ekleme
Pivot tablolara normal veriler gibi grafik ekleyebilirsiniz.
Pivot tablonun her hangi bir yerine tıklayıp grafiğinizi seçebilirsiniz.
Pivot tablo güncellendikçe grafik de güncellenecektir.
Pivot chartların üzerinde filtrelemeyi grafiğin üzerinden yapmanızı sağlayan alan düğmeleri bulunur.
İsterseniz sunumunuzda bu alan düğmelerini etki bir şekilde kullanabilirsiniz. İsterseniz yukarıdaki butondan seçerek tek tek veya topluca kaldırabilirsiniz.
Pivot grafiklerin özellikleri dilimleyici eklemeye kadar normal veri grafikleriyle aynıdır.
Excel grafikler detaylı kılavuz
Pivot tablo kullanımı
Çok Düzeyli Alanlı Rapor Oluşturma
Bir “üst” öğeyi “alt” öğelere bölünmüş olarak gösteren bir rapor hazırlamak istiyorsanız, gerekli alanı başka bir alanla aynı bölgeye koyun.
💡Bölgedeki alanların sırası, hangisinin “üst” ve hangisinin “alt” öğeler olduğuna karar verir.
Sağ taraftaki alanlara kategoriler ve müşterileri alt alta koyarsanız özet tablonuzda da kategori ve müşteriler aynı sırada alt ve üst kategori olurlar.
Sıralamayı alanlarda değiştirirseniz sıralama pivot tabloda da değişir.
Sıralama değiştirmeyi sürükle bırak yöntemiyle yapabilirsiniz. Veya sol tıklayıp aşağı taşı menüsüne tıklayabilirsiniz.
Daralan ve Genişleyen Hiyerarşiler
Alanlar arasında bir üst/alt ilişkisi oluşturmak, her üst düzey öğenin yanına “artı/eksi” düğmeleri ekler.
Bu düğmeler, “+” işaretine tıklayarak alt öğeler için ayrıntılı bilgileri göstermenizi ve “-” işaretine tıklayarak bunları gizlemenizi ve yalnızca üst düzey öğelerin özetini görünür bırakmanızı sağlar.
PivotTable Analiz > Göster > +/- Düğmeleri’ne giderek bu düğmeleri açabilir veya kapatabilirsiniz.
Eğer çok kategoriniz var ise ve tek tek açıp kapatmak istemiyorsanız sağ tıklayıp “genişlet/daralt” menüsünden “alanın tamamını genişlet /daralt “ menülerini kullanabilirsiniz.
Bonus – Hiyerarşileri Hızlı aç kapa özelliği
Büyük dosyalarla çalışırken hızlı aç – kapa yapmak için shift tuşuyla + mouse tekerleğini ileri ittirin, çok hızlı açıldığını göreceksiniz.
Boş satırları kaldırın
Kurduğunuz boş satırlar pivot tabloya da boş satır olarak yansır. Aşağıdaki örnekte olduğu gibi boş bir satırı veya boş satırları pivot tablo boş diye bir başlık açarak altta toplar.
Varsayılan Başlıkları Güncelleme
Otomatik pivot tablo başlıkları bazen hoş değildir. “Satır Etiketleri” ve “Sütun Etiketleri” hiç bilgilendirici değil ve “Toplamı …” eklemeleri oldukça hantaldır. Sunum amaçları için kesinlikle uygun değildir.
Neyse ki, başlık hücresinin içine tıklayabilir ve daha açıklayıcı bir başlık yazabilirsiniz.
PRO İPUCU: Pivot Tablolar, mevcut bir başlıkla eşleşen bir başlık adlandırmanızdan hoşlanmaz. Çakışmayı önlemek için özel başlıktan önce veya sonra bir boşluk eklemek basit bir hiledir.
Pivot tabloları kopyalama ve taşıma
Kopyalamak veya taşımak istediğinizde de pivot tablonun tamamını seçip kopyala veya kes tuşuna basıp yerini değiştirebilirsiniz. Eğer bir kısmını kopyalamak isterseniz başka bir hücreye veya sayfaya aktarabilir ama tamamını almadığınız için pivot tablo özelliğini kaybeder.
Pivot tablo nasıl silinir
Pivot tablonun üzerine gelip pivot tabloya ait tüm verileri ve başlıkları seçerseniz “sil” düğmesiyle silebilirsiniz. Eğer başlıkların veya verilerin 1 satırını bile seçim dışında bırakırsanız pivot tablo silinemiyor diye hata alırsınız.
Pivot tablo sıralama ve filtreleme
Pivot Tablo Nasıl Sıralanır
Varsayılan sıralama düzeni satır etiketlerine göre alfabetiktir, ancak rapor okuyucuları genellikle “en iyi performans gösterenler” veya “en az vaka” gibi şeylerle ilgilenir. Bir toplama değerine sağ tıklayıp Sırala -> En Küçükten En Büyüğe Sırala veya En Büyükten En Küçüğe Sırala’yı seçerek raporu kolayca sıralayabiliriz.
Elle sıralama
Pivot tablonun verdiği sıralama size göre değilse ve küçük değişiklikler yapmak istiyorsanız satırları elle yerinden oynatıp yer değiştirebilirsiniz.
Pivot tablo filtreleme
Satır etiketleri başlığının filtresine tıkladığınızda menü belirir. Bu menüden sıralama yapabileceğiniz gibi “etiket” veya “değer” filtresi kullanabilirsiniz. Bunun yanında sadece bazı müşterilerin performansına bakmak isteyebilirsiniz. Bazı müşterilerin tıkını kaldırabilirsiniz.
Uzun listelerde birçok kez filtreleme
Eğer isimlere göre bir listeniz varsa ve çok uzunsa, arasından da 2 ailenin sipariş adetlerini toplamak istiyorsanız birden çok filtreleme yapabilirsiniz. Soyismi ile aramak ve topluca filtrelemek istediğiniz aile soyadlarının Aksoy, Yıldırım olduğunu varsayın.
Örneğin birini seçip tamam’a basın. İkinciyi seçtiğinizde seçime ekle tıkına basarsanız ilk seçtiğiniz kaybolmaz.
Şimdi yıldırım soyadlı kişileri seçelim
Bu durumda Aksoy ve yıldırım soyadlı kişiler tabloda süzülmüş olacaktır.
Değer ve etiket filtrelemeleri
Pivot tabloda filtrelemeyi etiket ve değer filtreleri olarak da yapabilirsiniz.
- Etiket filtresi satır etiketlerinin hemen altında yer alan isimlerin içeriğine göre filtreleme yapmak için kullanılır. Örneğin içinde “Mehmet” olanları filtrele diyebilirsiniz.
- Değer filtresi ise “yıllık satış” sütunundaki verilere göre filtreleme yapmak için kullanılır. Örneğin “50.000 adetin üzerindekileri” filtrele diyebilirsiniz.
Dilimleyici ile filtreleme
Filtreleme yerine daha hızlı filtreleme için dilimleyici ekleyebilirsiniz. Pivot tablo analizi menüsünün altında dilimleyiciye tıklayıp dilimleyici kullanabilirsiniz.
Dilimleyici klasik filtreleme ile aynı fonksiyonu görür fakat daha hızlıdır.
Eğer iki kategorinin de filtrelemesini yapmak isterseniz filtrelerden tek tek seçmelisiniz.
Fakat dilimleyici kullandığınızda iki kategoriyi de aynı anda kontrol edebileceğiniz iki ayrı dilimleyici kullanabilirsiniz.
Tek tıkla dilimleyici açma
Pivot tablo alanlarını üst menüden seçip dilimleyici eklemek yerine alan listesinde sağ tıklayarak tek tıkla dilimleyici seçebilirsiniz.
Dilimleyici kullanım detaylı kılavuz.
Verileri gruplama
Rakamları gruplama
Bir Satır alanında sayıları tek tek listelemek yerine, verilerin kısa bir özetini oluşturmak için bunları gruplayabilirsiniz.
Bu örnekte, orijinal pivot tablo kategorilere göre yıllık satışı göstermektedir. Bu satışın içinde de olan ürünleri göstermektedir.
Sıraları özetlemek için yıllık toplamı biner biner gruplayın. Böylece, yıllık satış rakamları ayrı satırlarda görünmek yerine, 1.000 lik gruplama halinde özetlenecektir.
Sayıları gruplamak için
1 – Pivot tablodaki birim numaralarından birine sağ tıklayın.
2 – Açılır menüde Gruplandır tıklayın
3 – Gruplama iletişim kutusunda, Başlangıç Noktası kutusuna 10.000 girin
4 – Bu örnekte, en yüksek birim sayısı 15.000’dir ve gerekirse daha yüksek bir sayı yazabilirsiniz.
5 – Gruplandırmayı uygulamak için Tamam’a tıklayın
Pivot tabloda, Birimler artık gruplar halinde görünür.
Tarihleri gruplamak
Müşteri siparişlerini inceliyorsunuz. Sistemdeki rapor size tarihe göre satış adetlerini veriyor olabilir. Yani elinizde günlere göre satış adetleri listesi var. Hem de 2 yıllık. Yani en az 700 satırlık veri olabilir.
Özet tablo çekerek aylara göre veya çeyrek raporu çekeceksiniz.
Çok kolay!
Pivot tablo bu tür tarih listelerini aylara, çeyreklere ve yıllara göre gruplayıp size veriyor.
Önce verilerinizi ekle – > tablo nesnesi menüsünden tabloya çeviriyorsunuz.
Her hangi bir hücresine tıklayın ve üst menüden pivot tablo ile özetle menüsünden pivot tablo ekleyin.
Boş pivot tablo oluştuğunda yukarıdaki gibi gözükecek.
Tarih alanını satırlar bölümüne alın. Hemen aylar ve çeyreklerin göründüğünü göreceksiniz.
Pivot tablonun alanlarını genişlettiğinizde ise tüm alt verileri görebilirsiniz.
Eğer bu gruplandırma çok fazla alt kolları içeriyor ve bazı alanları kaldırmak isterseniz pivot tabloya sağ tıklayın ve gruplandır alt menüsüne girin.
Örneğin ay detayını görmek istemiyorsanız gruplandırma kutucuğundan ayı kaldırabilirsiniz ve sadece yıl ve çeyrek raporu çekebilirsiniz.
Ortaya aşağıdaki gibi bir tablo çıkacaktır.
Alt alan olarak aylar kalkıyor ve sadece yıl ve çeyrek alanlarını görüyorsunuz.
Zaman çizelgesi kullanımı
Uzun bir tarih listeniz varsa ve aylık bölümlemeler yapmak isterseniz bu zaman çizelgesiyle mümkün olacaktır.
Pivot tablonuzu seçin. Üst menüden zaman çizelgesini seçin. Ortaya çıkan kutucuktan tarihi tıklayın.
Zaman dilimleyici size ay ay verileri süzmenizi sağlar.
İsterseniz shift e basıp bir aralığı seçebilir veya ortadaki mavi butonu kenarından tutup genişletebilirsiniz.
Pivot tablo değer gösterimi ve gösterim düzeni
Pivot Tablo Düzenini ve Diğer Ayarları Özelleştirme
Pivot Tablolar için düzinelerce özelleştirilebilir seçenek mevcuttur. Tasarım sekmesindeki en yaygın kullanılan özelliklerden bazıları şunlardır:
- Alt toplamları açma/kapatma
- Genel toplamları açma/kapatma
- Tablo düzeninin değiştirilmesi
- Üst düzey satırlar arasına boş satır ekleme/kaldırma
- Üst düzey etiketleri alt düzey satırlarda tekrarlama
- Önceden tanımlanmış bir renk şeması uygulama
- Alternatif satırları/sütunları/başlıkları renklendirme (vurgulama)
Görsel olarak ilginç raporlar oluşturmak için farklı kozmetik özellik kombinasyonlarını denemenizi tavsiye ederim.
NOT: Tasarım sekmesi pivot tabloya özgüdür, yani yalnızca pivot tablo içinde bir hücre seçtiğinizde görünür.
Pivot tablo tasarımı
Pivot tablonun görüntüsünü çeşitli şekillerde ayarlayabilirsiniz. Alt toplamlar, genel toplamlar, rapor düzeni ve boş satırların durumunu pivot tablo tasarım menüsünden belirleyebilirsiniz.
Örneğin alt toplamların 3 alt menüsünü de denediğinizde aşağıdaki gibi bir sonuç alıyorsunuz.
Örneğin genel toplamların alt 4 menüsünü denerseniz aşağıdaki görüntü ortaya çıkar.
Örneğin rapor düzeninin alt 3 menüsünü denerseniz aşağıdaki görüntü ortaya çıkar.
Örneğin boş satırların alt 2 menüsünü denerseniz aşağıdaki görüntü ortaya çıkar.
Pivot Tabloda Toplamın Yüzdesi Nasıl Eklenir
Hatırlarsanız, yanıtlanması gereken sorularımızdan biri de her bir müşterinin toplam satışların yüzde kaçını oluşturduğuydu.
Bu yüzdeyi tek bir formül yazmadan birkaç tıklamayla belirleyebiliyoruz.
1 – Yıllık Satış alanını ikinci kez Değerler alanına ekleyin (orijinal girişin altına yerleştirilir).
2 – Yeni eklenen sütundaki değerlerden birine sağ tıklayın ve “Değerleri Farklı Göster” -> “Genel Toplamın %’si” öğesini seçin.
“Değerleri Farklı Göster” listesini incelemek için biraz zaman ayırın. Orada bulunabilecek matematiksel anlamda bir altın madeni var.
3 – Daha sonra sütun başlığını yeniden adlandırmayı unutmayın.
Birkaç örnek inceledim. Aşağıda 5 değer gösteriminin sonuçlarını görebilirsiniz.
Birden çok alt toplam gösterimi
Bir müşterinin satış rakamları var ve sadece toplamları değil farklı alt toplamlar da görmek istiyorsunuz.
Excelde pivot tabloya ek alt toplamlar eklemek mümkün.
Müşteri ismine sağ tıklayın. Alan ayarlarını seçin.
Beliren kutucuktaki toplam, say, ortalama, mak ve min’e tıklayın
Özet tabloya yeni alt toplamlar gelecek.
Müşterinin toplam satışını, kategori sayısını, ortalamasını, en yüksek kategoriyi ve en düşük kategoriyi tabloya getirmiş oluyorsunuz.
Kümülatif toplam gösterimi
Pivot tabloda kümülatif toplam tutar ve yüzdesini gösterebilirsiniz. Örneğin müşteriye göre satışların kümülatif toplamını ve yüzdesini görmek istiyoruz.
Önce yıllık satış alanını değerler bölümüne 2 kez sürükle bırak yapıyoruz. Bu alanların birini toplam birini yüzde olarak değiştireceğiz.
Ardından rakama sağ tıklayıp değer gösterimi -> değişen toplam seçiyoruz. Türkçe’ye değişen toplam olarak çevrilse de aslında anlatılmak istenen kümülatif toplamdır.
Diğer eklediğimiz yıllık satış alanına yine sağ tıklayıp bu sefer değişen % seçiyoruz.
Ortaya çıkan tablo hem toplam hem yüzde olarak kümülatif veri gösteriyor.
İleri düzey
Pivot tablo hesaplanmış alan
Pivot tablo hesaplanmış alan
Pivot tablonun veri aldığı dosyada istediğiniz veri yok ise siz pivot tabloda özet üzerinden o veriyi oluşturabilirsiniz. Kendi formüllerinizi kullanabilirsiniz.
Veri dosyasına ek veri sütunu eklemenize gerek kalmaz.
Pivot tablo hesaplanmış alan eklemek için alanlar bölümünden hesaplanmış alana tıklayın.
Ortaya bir kutucuk açılacak.
Alanınızın ismini üste yazıp alta formülünüzü kurabilirsiniz.
Örneğin;
İlk yarı yıl satış tutarını hesaplamak istiyorsunuz.
Aşağıdaki formülü kurabilirsiniz.
Yeni alan pivot tablo alanlarına eklenecektir.
Pivot tablonuza da eklediğinizde yeni hesaplanmış alanınız hazırdır. Tüm yıl ile yarı yılı yan yana görebilirsiniz.
Diğer örnek
Satış pirimini hesaplamak;
Yıllık satış piriminin 2% olduğunu varsayalım.
Mayıs ayının satış primini hesaplayalım. Mayıs satışı x 2% formülüyle yeni bir hesaplanmış alan oluşturursunuz.
Bu şekilde mayıs satışı ve mayıs primini yan yana görebilirsiniz.
Karışık bir formüllü alan ekleyin.
Örneğin şu şekilde bir formül kurabiliriz.
Sadece satış adeti 5.000 adeti geçmişse 5%lik primi hesaplasın, yoksa prim 2% te kalsın.
Kutucuğa tamam derseniz pivot tablonuza bir sütun daha eklenecek.
Kullandığınız formülleri listelemek
Pivot tablo üzerinde bir çok formül kullanmış olabilirsiniz. Bunların listesini excel ayrı bir sayfada size gösteriyor.
Formül listele menüsüne tıklayın
Ayrı bir sayfada size formül dökümünü verecek.
Hesaplanmış alanı silme
Hesaplanmış alana gelin ve üstteki ok tuşuna basın. Şimdiye kadar oluşturduğunuz tüm hesaplanmış alanları gösterecek. Silmek istediğinizi seçin. Ve sağ taraftaki sil butonuna tıklayın.
Pivot tablo cache silme (hafıza silme)
Pivot tablonun veri aldığı yerde düzenleme yaparsanız ve bir satırı silerseniz veri pivot tablodan silinir ama filtreler bölümünden silinmez. Bu da kafanızı karıştırır ve dosyayı ağırlaştırır.
Örneğin aşağıdaki gibi bir veriniz var.
Tüm satış ekibini listeden çıkaralım
Tabloyu da alttan daraltın.
Pivot tabloyu güncelleyin
Sildiğiniz satış mesleğinin pivot tablodan silindiğini göreceksiniz.
Filtreyi açtığınızda satış mesleğinin hala filtrelerde durduğunu göreceksiniz.
Bunu kaldırmak için pivot tabloya sağ tıklayın ve pivot tablo seçeneklerine tıklayın.
Açılan aşağıdaki kutucukta veri sekmesini seçin.
Alan başına korunacak öğe sayısını otomatikten yoka çevirin.
Pivot tabloyu yenileyin ve filtreye bakın.
Artık satış satırının filtrelerde olmadığını göreceksiniz.
Birden çok tablo ile pivot tablo
İki Tablo, Bir Pivot
Hepimiz tek tablolardan veya veri aralıklarından pivot raporlar oluşturabiliriz. Peki ya iki veya daha fazla tabloda bulunan verilerden bir pivot oluşturmanız gerekiyorsa?
Birden fazla tabloyu birbirine bağlamak ve bunlardan pivotlar oluşturmak için Excel’in “Veri Modeli” özelliğini kullanabilirsiniz. Bu teknik, kolaylıkla gelişmiş veri analizi için kapıları açar.
Birden fazla tablodan pivot raporlar oluşturmak için,
1 – Verilerinizi tablo olarak ayarlayın. Veriler aralıklar halindeyse, CTRL+L veya Ekle > Tablo seçeneğini kullanarak bunlardan tablolar oluşturun.
2 – Veri modeli oluşturmak için ilişkiler kurun: Verilerinizin bir tablosunun Satış bilgileri ve diğer tablonun Müşteri bilgileri olduğunu düşünün. Bunları birbirine bağlayabilirsiniz. İlişkiyi kurmak için Veri şeridine gidin ve ilişki düğmesine tıklayın.
3 – Bir pivot tablo ekleyin: Devam edin ve tablolarınızdan herhangi birinden bir pivot tablo ekleyin. Pivot ekle ekranının altındaki “Veri modeline ekle” seçeneğini işaretlediğinizden emin olun.
4 – Birden fazla tablodan pivotlar oluşturun. Artık karmaşık iş raporlarını kolayca oluşturmak için herhangi bir tablodaki alanları bir araya getirebilirsiniz.
Dosya içinde seçtiğiniz diğer tabloları tümü yazan butona tıkladığınızda görebilirsiniz.
Her tablonun yanındaki oklara tıkladığınızda alt kategoriler açılacaktır.
Bu alt kategorileri de alanlara yerleştirip istediğiniz pivot tabloyu kullanabilirsiniz.
İleri düzey tüyolar
Yanlışlıkla Çift Tıklamayı Önlemek için Ayrıntıları Göster’i Kapatın
Bir pivot tablo sonucunun arkasındaki ayrıntılı verileri gösterme yeteneği ilginizi çekmiyorsa, bu özelliği kapatabilirsiniz. Bu, yanlışlıkla çift tıklamalar nedeniyle içinde veri parçaları olan yeni sayfalar oluşturmaktan kaçınabileceğiniz anlamına gelir.
Seçenekler menüsünü açmak için PivotTable bölümündeki Seçenekler düğmesine basın.
PivotTable Seçenekler menüsünde Veri sekmesine gidin ve bu özelliği devre dışı bırakmak için Ayrıntıları göstermeyi etkinleştir kutusundaki işareti kaldırın.
PivotTable Alanları Penceresinden Alanları Filtreleme
PivotTable Alanları penceresindeki alan listesinden bir alandaki öğeleri filtreleyebilirsiniz. Filtre yalnızca alan filtreler, sütunlar veya satırlar alanına eklendiğinde uygulanır. Filtre menüsünü açmak için istediğiniz alanın üzerine gelin ve alan adının sağındaki küçük aşağı oka tıklayın.
Pivot Tablonun Tamamını Seçme
Çoğu insan gibiyseniz, verilerin aynı anda farklı görünümlerine sahip olmak için muhtemelen bir pivot tablonun birkaç kopyasını oluşturacaksınız. Pivot tablonuz büyükse veya filtre alanında öğeler varsa, kopyalayıp yapıştırmak için tümünü seçmek zor olabilir. İşte bu durumda Tüm pivot table seç kullanışlı hale gelir.
Analiz sekmesine gidin ve Eylemler bölümünün altındaki Seç komutuna basın, ardından Tüm PivotTablo’yu seçin. Bu, tablonun üzerindeki tüm filtre öğeleri dahil olmak üzere pivot tablonun tamamını seçecektir.
Tüm Filtreleri Temizle
Pivot tablonuzda birden fazla filtre varsa, her bir filtre menüsüne girip Filtreden Temizle seçeneğini seçmeden hepsini hızlı bir şekilde temizleyebilirsiniz.
Şeritteki PivotTable Araçları sekmelerini etkinleştirmek için pivot tabloda filtrelerini temizlemek istediğiniz bir hücre seçin.
- Analiz sekmesine gidin.
- Eylemler bölümünden Temizle düğmesine basın.
- Menüden Filtreleri Temizle öğesini seçin.
Pivot tablonuz, tüm kaynak verilere dayalı sonuçları gösteren tamamen filtrelenmemiş bir duruma geri dönecektir.
Seçili Öğeleri Sakla
Seçili öğeleri gizlemeye benzer şekilde, bir filtre ile yalnızca seçili öğeleri tutmayı seçebilirsiniz.
- Filtrenizle saklamak istediğiniz öğeleri seçin. Bitişik olmayan öğeleri seçmek için Ctrl tuşunu kullanabilirsiniz. Ardından seçili öğelere sağ tıklayın.
- Menüden Filtre öğesini seçin.
- Menüden Yalnızca Seçili Öğeleri Tut öğesini seçin.
Seçili Öğeleri Gizle
Seçili öğeleri filtre menüsüne girmeden hızlıca gizleyebilirsiniz.
- Filtrenizle gizlemek istediğiniz öğeleri seçin. Bitişik olmayan öğeleri seçmek için Ctrl tuşunu kullanabilirsiniz. Ardından seçili öğelere sağ tıklayın.
- Menüden Filtre öğesini seçin.
- Alt menüden Seçili Öğeleri Gizle’yi seçin.
Bu, filtre menüsüne girmeden ve uzun bir öğe listesindeki kutuları işaretlemeden veya işaretlerini kaldırmadan öğeleri hızlı bir şekilde filtrelemenize olanak tanır.
Özetverial’ı Kapatın
Varsayılan olarak, bir formülde pivot tablo içindeki bir hücreye başvurmaya çalıştığınızda, Excel başvuru için bir özetverial formülü oluşturur. özetverial mutlak başvuruya benzer şekilde davrandığından, basit bir göreli A1 stili başvuru istediğinizde bunlar can sıkıcı olabilir.
Bu varsayılan seçeneği, pivot tablonuzu seçip şeritteki Analiz sekmesine giderek ve PivotTable bölümünün altındaki Seçenekler düğmesinin yanındaki küçük aşağı oka tıklayarak kapatabilirsiniz. Bu özelliği kapatmak için Özetverial üret seçeneğinin işaretini kaldırın. Ayrıca buradan da tekrar açabilirsiniz!
PivotTable Alanları Listesinde Arama Yapma
Kaynak verilerinizde çok sayıda alan varsa, arama kutusunu kullanmak aradığınızı bulmak için listeyi daraltmanıza yardımcı olabilir.
Alan Başına Birden Fazla Filtreye İzin Ver
Excel’de bir pivot tablo alanı için iki tür filtre kullanılabilir: Etiket Filtresi ve Değer Filtresi. Diyelim ki bu pivot tabloyu soyadı Arslan olan ve (Etiket Filtresi kullanarak) ve Toplam değeri 1.000’dan büyük olan (Değer Filtresi kullanarak) tüm Satılan Ürünlerde filtrelemek istediniz, varsayılan ayarlarla bu iki filtrenin aynı anda kullanılması mümkün değildir. Buna izin vermek için ayarları güncelleyebiliriz.
Pivot tablonuzu seçin ve şeritteki Analiz sekmesine gidin ve PivotTable bölümündeki Seçenekler düğmesine basın.
PivotTable Seçenekleri iletişim kutusunda birden çok filtreyi etkinleştirin.
- Toplamlar ve Filtreler sekmesine gidin.
- Alan başına birden fazla filtreye izin ver kutusunu işaretleyin.
- Tamam düğmesine basın.
Artık tek bir alanda aynı anda hem Etiket Filtresi hem de Değer Filtresi kullanabileceksiniz.
Özet olarak
Excel pivot tablo büyük verileri özetleyerek ve sürükle-bırak yöntemiyle kullanabileceğiniz hem basit hem büyük işler başaran bir excel fonksiyonudur.
Yukarıdaki birçok özelliğini öğrenirseniz pivot tabloyu çok verimli ve hızlı kullanabilirsiniz.
Microsoft’un pivot tablo sayfasından da yararlanabilirsiniz.
Bu yazılar da ilgini çekebilir;
Excel Etopla Formülü ve Çoketopla Formülü Kullanımı [1 şablon içerir]
Excel’de Yerinekoy Formülü ve Değiştir Formülü işe Metin Değiştirmenin 2 Farklı Yolu
Excel Tarih ve Zaman Formülleri [21 Formül 1 şablon içerir]
Excel Dashboard Raporlama Örnekleri [13 şablon içerir]
![Semih Arslan](https://markamuduru.com/wp-content/litespeed/avatar/659d7eb9923c45ff7fa7785e8c874ca0.jpg?ver=1725267143)
2008’den beri pazarlama dalında çalışıyorum. 2014’ten beri markamuduru.com’da yazıyorum. İnanıyorum ki markalaşma adına ülkemizde inanılmaz bir potansiyel var ve markalaşmak ülkemizi fersah fersah ileri götürecek. Kendini yetiştirmiş marka müdürlerine de bu yüzden çokça ihtiyaç var. Ben de öğrendiklerimi, araştırdıklarımı, bildiklerimi burada paylaşıyorum. Daha fazla bilgi için Hakkımda sayfasını inceleyebilirsiniz.