Orta Ders 10: Pivot Tabloya Giriş

Pivot tablo ne işe yarar?

Pivot tablo, uzun bir kayıt listesini tek formül yazmadan özetleyen bir araçtır. Sekiz satırlık bir satış listesini ya da seksen bin satırlık bir sipariş dökümünü birkaç sürükle bırak hareketiyle bölge, ürün grubu ya da ay kırılımında toplarsınız. Önceki derslerde ETOPLA ve ÇOKETOPLA ile kurduğunuz özetlerin çoğunu pivot tablo birkaç saniyede üretir.

Formülle kurulan özete göre farkı esnekliğidir. Bölge kırılımından ürün grubu kırılımına geçmek için yeni formül yazmazsınız, yalnız alanın yerini değiştirirsiniz.

Veriyi hazırlamak

Pivot tablonun doğru çalışması için verinin düzgün bir liste olması gerekir. İlk satırda her sütunun benzersiz bir başlığı olmalı, her satır tek bir kaydı anlatmalıdır. Birleştirilmiş hücreler, ara toplam satırları ve tamamen boş satırlar sorun çıkarır.

Aynı sütunda sayı ile metnin karışması da sık görülen bir hatadır. Tutar sütununda "yok" yazan tek bir hücre, Excel'in o alanı toplamak yerine saymasına neden olabilir. Veriyi önce Ctrl+T ile tabloya çevirmeniz önerilir. Böylece yeni satırlar eklendiğinde pivot tablonun kaynağı da kendiliğinden genişler.

İlk pivot tabloyu oluşturmak

Veri içinde bir hücre seçip Ekle sekmesinden PivotTable'ı seçtiğinizde Excel kaynak aralığı ve pivot tablonun konumunu sorar. Yeni Çalışma Sayfası seçeneği, özeti ham veriden ayrı tutmak için iyi bir başlangıçtır. Onayladığınızda sağda PivotTable Alanları bölmesi açılır.

Bölmenin üst kısmında kaynak tablonun sütun başlıkları, alt kısmında dört bölge bulunur: Filtreler, Sütunlar, Satırlar ve Değerler. Bölge alanını Satırlar'a, Tutar alanını Değerler'e sürüklediğinizde her bölgenin toplam satışı listelenir.

Sonucu formülle doğrulamak

Pivot tablo bir kez kurulduktan sonra rakamlara güvenmeden önce bir iki hücreyi formülle kontrol etmek iyi bir alışkanlıktır. Böylece kaynak aralığın eksik seçilmesi ya da metin olarak saklanan tutarlar gibi sorunları erkenden yakalarsınız.

=ETOPLA(A2:A9;"Marmara";C2:C9)

=SUMIF(A2:A9,"Marmara",C2:C9)

Marmara satırında pivot tablonun göstermesi gereken toplam 18.000 + 4.200 + 7.400 = 29.600'dür. Pivot farklı bir değer gösteriyorsa kaynak aralığı ya da kaynak verideki yazım farklarını inceleyin.

Değer Alanı Ayarları

Değerler bölgesine bir sayı alanı koyduğunuzda Excel varsayılan olarak toplar, metin alanı koyduğunuzda sayar. Bunu değiştirmek için alanın üzerindeki açılır menüden Değer Alanı Ayarları'nı açarsınız. Topla, Say, Ortalama, Maksimum ve Minimum gibi seçenekler burada durur.

Aynı pencerenin Değerleri Farklı Göster sekmesi, toplamı genel toplamın yüzdesi ya da bir önceki aya göre fark gibi biçimlerde gösterir. Ürün grubunu Sütunlar'a, Tutar'ı ikinci kez Değerler'e ekleyip sayıma çevirdiğinizde her bölge ve grup için kaç satış yapıldığını görürsünüz.

=ÇOKEĞERSAY(A2:A9;"Ege";B2:B9;"Mobilya")

=COUNTIFS(A2:A9,"Ege",B2:B9,"Mobilya")

Ege bölgesinde iki mobilya satışı var, pivot tablonun sayım hücresi de 2 göstermelidir.

Yenilemeyi unutmayın

Pivot tablo, kaynak veriyi otomatik olarak takip etmez. Yeni satır eklediğinizde ya da bir tutarı değiştirdiğinizde pivot tablonun üzerine sağ tıklayıp Yenile'yi seçmeniz gerekir. Formüllerle kurulan özetlerden en önemli farkı budur ve raporlarda eski rakam göstermenin en sık nedenidir.

Kaynak veriniz tablo değil de sabit bir aralıksa yeni satırlar aralığın dışında kalır. Bu durumda PivotTable Çözümle sekmesindeki Veri Kaynağını Değiştir ile aralığı genişletmeniz gerekir.

Aylık satış toplantısına hazırlık

Her ay satış toplantısı öncesinde bölge ve ürün grubu kırılımında bir özet hazırladığınızı düşünün. Ham veri ERP sisteminden her ay aynı sütunlarla dışa aktarılıyorsa pivot tabloyu bir kez kurarsınız. Sonraki aylarda yalnız yeni veriyi kaynak tabloya yapıştırıp Yenile'ye basarsınız.

Toplantıda "yalnız Ege'yi görelim" dendiğinde Bölge alanını Filtreler bölgesine taşıyıp Ege'yi seçmeniz yeterlidir. Ürün grubunu satırlardan sütunlara almak da tek bir sürükleme ister. Aynı soruları formülle cevaplamak her seferinde yeni bir ÇOKETOPLA yazmayı gerektirirdi.

Pivot tablonun hazırladığı özeti başka bir rapora taşıyacaksanız değer olarak yapıştırmayı tercih edin. Aksi halde kopyalanan alan da bir pivot tablo olarak kalır ve kaynak dosyaya bağımlı olur.

Ham veriyi her ay aynı sütun sırasıyla dışa aktarmak da önemlidir. Bir sütunun adı değişirse pivot tablo o alanı tanımaz ve yenilemeden sonra ilgili kısmı boş bırakır.

Sıradaki adım

Alıştırmada her bölgenin genel toplam içindeki payını ETOPLA ile hesaplayacaksınız. Ardından aynı veriden bir pivot tablo kurup Değerleri Farklı Göster ile genel toplamın yüzdesini seçin ve iki sonucu karşılaştırın.