Excel'de Pivot Tablo Nasıl Yapılır?

Pivot tablo ne işe yarar?

Pivot tablo, uzun bir veri listesini formül yazmadan özetleyen etkileşimli bir tablodur. Microsoft'un Türkçe arayüzünde adı PivotTable olarak geçer. Yüzlerce satırlık bir satış listesinde bölge başına toplam, ürün başına adet ya da ay başına ciro görmek istediğinizde birkaç sürükle bırak hareketi yeter.

Aynı sonucu ETOPLA veya ÇOKETOPLA ile de alabilirsiniz. Fark şu: formülde her bölge için ayrı bir hücre yazarsınız, pivot tablo ise listedeki bütün bölgeleri kendisi bulur ve yeni bir bölge eklendiğinde yenilemeyle tabloya ekler.

Veriyi pivot tabloya hazırlamak

İyi bir pivot tablonun ilk şartı düzgün bir kaynak listedir. İlk satırda her sütunun tek ve benzersiz bir başlığı olmalı, araya boş satır ya da birleştirilmiş hücre girmemelidir. Tarih sütununda yalnız gerçek tarihler, tutar sütununda yalnız sayılar bulunmalıdır.

Örnek olarak mart ayına ait altı satırlık bir satış listesi düşünün: Tarih, Bölge, Ürün ve Tutar sütunları var. Bu listeyi önce Ctrl+T ile Excel tablosuna çevirmenizi öneririz. Tabloya eklenen yeni satırlar pivot tablonun kaynağına kendiliğinden dahil olur ve sonradan veri aralığını elle genişletmeniz gerekmez.

PivotTable ekleme adımları

Listenin içinde herhangi bir hücreyi seçin ve Ekle sekmesinde PivotTable düğmesine tıklayın. Açılan PivotTable Oluştur iletişim kutusunda Excel tabloyu ya da aralığı kendisi önerir. Yerleşim için Yeni Çalışma Sayfası seçeneğini bırakmak çoğu durumda en temiz sonuçtur.

Tamam'a bastığınızda boş bir pivot alanı ve sağda PivotTable Alanları listesi açılır. Listede kaynak tablonuzdaki her sütun başlığı bir alan olarak görünür. Hangi düzeni kuracağınızdan emin değilseniz Önerilen PivotTable'lar seçeneği verinize uygun birkaç hazır düzen gösterir.

Alanları doğru bölgeye yerleştirmek

Alanlar dört bölgeye sürüklenir: Filtreler, Sütunlar, Satırlar ve Değerler. Bölge alanını Satırlar'a, Tutar alanını Değerler'e bıraktığınızda her bölgenin toplam satışı alt alta sıralanır. Ürün alanını Sütunlar'a eklerseniz aynı tablo bölge ve ürün kırılımlı bir çapraz tabloya dönüşür.

Sayı içeren alanlar Değerler'e bırakıldığında Excel varsayılan olarak toplam alır. Metin içeren bir alanı Değerler'e taşırsanız sayım yapılır. Toplam yerine ortalama ya da en büyük değer gerekiyorsa değer alanına tıklayıp Değer Alanı Ayarları penceresinden özetleme türünü değiştirin.

Sonucu formülle doğrulamak

Pivot tablonun doğru veriyi okuduğunu görmek için bir iki değeri formülle kontrol etmek iyi bir alışkanlıktır. Ege bölgesinin toplamı pivot tabloda 1950 görünüyorsa aynı rakamı ETOPLA da vermelidir.

=ETOPLA(B2:B7;"Ege";D2:D7)

=SUMIF(B2:B7,"Ege",D2:D7)

İki koşullu bir hücreyi ÇOKETOPLA ile denetleyebilirsiniz. Marmara satırıyla Defter sütununun kesiştiği hücre bu formülle aynı olmalıdır.

=ÇOKETOPLA(D2:D7;B2:B7;"Marmara";C2:C7;"Defter")

=SUMIFS(D2:D7,B2:B7,"Marmara",C2:C7,"Defter")

Rakamlar tutmuyorsa sebep çoğunlukla kaynaktadır. Tutar sütununda metin olarak girilmiş sayılar toplamaya katılmaz, bölge adlarının sonundaki fazla boşluk ise aynı bölgeyi iki ayrı satır olarak gösterir.

Düzeni okunur hâle getirmek

Pivot tablo ilk oluştuğunda sıkışık bir görünümle gelir. Tasarım sekmesindeki Rapor Düzeni seçenekleriyle tabloyu klasik satır ve sütun düzenine çevirebilir, Alt Toplamlar ile ara toplamları gösterip gizleyebilirsiniz. Satır etiketi yerine Bölge yazması için başlık hücresine tıklayıp yeni adı yazmanız yeterli.

Tutarların binlik ayraçla görünmesi için Değer Alanı Ayarları penceresindeki Sayı Biçimi düğmesini kullanın. Sayfadaki hücreleri seçip biçimlendirmek de işe yarar, ama alanın kendi biçimi yenilemelerde daha güvenilir şekilde korunur. Büyükten küçüğe sıralamak için değer sütununda bir hücreye sağ tıklayıp Sırala menüsünü açın; Marmara 2140 ile en üste çıkar.

Filtreler bölgesine Ürün alanını bırakırsanız tablonun üstünde bir açılır liste belirir. Yalnız Kalem'i seçtiğinizde bütün bölge toplamları kalem satışına göre yeniden hesaplanır. Aynı raporu farklı ürünler için ayrı ayrı kurmanıza gerek kalmaz.

Sık karşılaşılan sorunlar

En yaygın sorun, kaynak veriye satır eklendikten sonra pivot tablonun eski rakamları göstermesidir. Pivot tablo formül gibi anında hesaplanmaz; PivotTable Çözümle sekmesindeki Yenile düğmesine basmanız gerekir. Kaynak bir Excel tablosu değilse yeni satırlar aralığın dışında kalabilir.

Başlığı boş bir sütun da sorun çıkarır. Excel bu durumda pivot tabloyu oluşturmaz ve alan adının geçerli olmadığını bildirir. Boş başlığa bir ad yazdığınızda sorun çözülür.

Sırada ne var?

Kendi verinizden ilk pivot tabloyu kurduktan sonra Tutar alanını Değerler bölgesine ikinci kez sürükleyin. İkinci kopyayı toplamın yüzdesi olarak gösterdiğinizde aynı tabloda hem tutarı hem payı görürsünüz. Tarih alanını Satırlar'a ekleyip ay bazında gruplamak da bir sonraki doğal adımdır.