5.1 PivotTable ile Raporlama ve PivotChart ile Grafik Oluşturma

PivotTable, binlerce veriden oluşan tablolarla çalışırken verileri özetlemek için kullanılır. PivotTable’lar, Excel tablolarındaki bilgilerin analiz edilmesine yarayan oldukça güçlü araçlardır. Tablo verilerini hesaplar, özetler, karşılaştırır, veri eğilimlerini analiz eder. Özetle, tabloları özetlemek ve bir veri analizi yapmak istediğinizde PivotTable’lar çok yardımcı olur.

Tablo verilerini bir PivotTable’a çektiğinizde dört ana alan vardır: Bunlar “Satırlar”, “Sütunlar”, “Değerler” ve “Filtreler” dir.
Satır ve Sütun alanları, verileri farklı şekillerde özetlemek veya sorulan soru veya kriterlere göre yeni raporlar çalıştırmak için hızlı bir şekilde değiştirilebilir. Değer alanı, tablodaki hesaplanabilen veya PivotTable’ın özetleyeceği değerleri içeren verilerdir. Değerler alanı, verileri nasıl hesaplamak istediğinizi seçmek için birden fazla ayara sahiptir; TOPLA, SAY, ORTALAMA, MİN, MAK ve hatta görüntülenen değerleri toplamın yüzdesi, sütun toplamı, genel toplam vb. olarak gösterebilir. Son olarak PivotTable’ı yalnızca belirtilen kriterlerle eşleşen değerleri gösterecek şekilde kısıtlayan Filtreler alanıdır.

Şekil 5.1.1:

Örnek: Aşağıda bir firmada yer alan A, B, C, D ve E departmanlarının aylara göre gider tutarlarının yer aldığı 180 satırlık bir tablonun ilk 10 satırı görülmektedir. Ancak bu tabloya baktığımızda, her bir departmanın ayrı ayrı kaçar TL gideri olduğunu, her ay için ayrı ayrı toplam kaç TL gider olduğunu göremiyoruz. Normalde bu soruların cevaplarına filtre yöntemiyle ulaşabiliriz. Fakat çok sayıda filtre yapmak gerekir. PivotTable sayesinde bu soruların cevabına kolayca ulaşabilirsiniz.
1. Ekle menüsünden Önerilen PivotTable’lar düğmesine tıklarsanız Excel size farklı özet tablo modelleri önerir. Ya da doğrudan PivotTable düğmesine tıklayabilirsiniz.
2. Veri tablosundaki herhangi bir hücre seçiliyken Ekle menüsünden PivotTable düğmesine tıklayın. Böylece aşağıdaki gibi bir pencere açılır, Tablo veya aralık seçin kutucuğu otomatik olarak dolu gelir. Özet tablonuzu mevcut tablonun olduğu sayfada oluşturmak isterseniz “Varolan Çalışma Sayfası” seçeneğine tıklayın. Biz yeni bir çalışma sayfasında oluşturacağımız için Tamam düğmesine tıklayın. Üzerinde “Rapor oluşturmak için PivotTable Alan Listesi’nden alanları seçin” cümlesinin bulunduğu yeni bir çalışma sayfası açılır.

Şekil 5.1.2:

3. Açılan sayfanın sağ tarafındaki PivotTable Alanlarını işaretlerseniz Departman adı ve Gider tutarı Satırlar bölmesine, değerleri ifade eden Gider tutarları ise Değerler bölmesine otomatik olarak sürüklenir. Ancak bu durumda her bir departman için ayrı ayrı tablolar oluşur. Daha özet bir tablo oluşturmak için, Satırlar bölmesindeki Tarih (ay) verisini Sütunlar kısmına sürükleyin. Böylece departmanların satırlarda, ayların sütunlarda olduğu aşağıdaki özet tablo oluşur.

Şekil 5.1.3:

Eğer Departman adını Sütunlar bölmesine, Tarih (Ay) değişkenini ise Satırlar bölmesine sürüklerseniz özet tablonuz aşağıdaki gibi olacaktır.

Şekil 5.1.4:

Biz yine Departman adı değişkenini Satırlar bölmesine, Tarih (ay) değişkenini ise Sütunlar bölmesine sürükleyelim.

4. Değerler bölmesindeki Toplam Gider Tutarı açılır listesinden Değer Alanı Ayarları seçeneğine tıklayın, aşağıdaki pencere açılacaktır.
Bu pencereden Toplam yerine Say seçeneğine tıklarsanız özet tabloda departmanların gider tutarları yerine gider adetleri görünür. Ancak burada Toplam seçeneğinin seçili olması daha anlamlıdır.

Şekil 5.1.5:

5. Özet tablo üzerindeki bir hücreyi seçin, bu durumda üstteki Menü kısmında PivotTable Analizi adlı yeni bir menü açılır. Bu menüye tıklayın.  Açılan menüden Dilimleyici Ekle düğmesine tıklayın. Açılan Dilimleyici Ekle penceresinden Departman adı ve Tarih (ay) isimli dilimleyicileri seçip Tamam’a tıklayın. Sayfa üzerinde taşınabilen aşağıdaki gibi iki dilimleyici penceresi açılır. Departman adı dilimleyicisinden Departman A’yı ve Tarih (ay) dilimleyicisinden Mayıs ayını seçerseniz özet tabloda sadece Departman A’nın Mayıs ayı gideri görünür.

Şekil 5.1.6:

6. Bu dilimleyicilerden CTRL tuşuna basılı tutarak dilediğiniz sayıda Departman ve ay seçebilirsiniz. Örneğin B, C ve D departmanları ile Ocak, Haziran, Eylül ve Aralık aylarını seçin. Özet tablonun görünümü aşağıdaki gibi olacaktır.

Şekil 5.1.7:

7. Dilimleyicilerin sağ üst köşelerindeki “Filtre temizle” işaretine tıklayın, böylece Excel özet tablodaki filtrelemeyi kaldırır ve özet tablonun tamamı görünür.
8. Dilimleyiciler seçili durumdayken üst kısımda Dilimleyici menüsü açılır. Buradan dilimleyicinin stilini değiştirebilirsiniz.
9. Dilimleyicileri seçin ve klavyeden Delete tuşuna tıklayıp dilimleyicileri silin.
10. Başlangıçtaki 180 satırlık verinin yer aldığı ana tablodaki herhangi bir veriyi değiştirdiğinizde Özet tablodaki veri değişmez. Örneğin ilk satırda Departman A’nın Ocak ayındaki gideri olan 1500 yerine 3000 yazın. Diğer sayfadaki Özet tabloda Departman A’nın Ocak ayı toplamı değişmez. Bu değişikliğin özet tabloda da aktif hâle gelmesi için, özet tabloda bir hücre seçiliyken PivotTable Analizi menüsüne tıklayın. Veri komut grubundan Yenile tuşuna tıklayın. Ya da kısa yoldan fareye sağ tıklayıp Yenile seçeneğine tıklayabilirsiniz. Böylece, Departman A’nın 9200 TL olan Ocak ayı toplamının 10700 TL olduğunu göreceksiniz.
11. Ana tablodaki son veri 181.satırdaydı. Bu tablonun 182.satırına yeni bir veri eklediğinizde özet tablonun güncellenmesi için Yenile seçeneği işe yaramaz.  Çünkü pivot tablo oluşturulurken Excel veri kaynağını “Sayfa1!$A$1:$C$181” adresine göre almıştı. Örneğin 182.satıra Departman A’ya Ocak ayı için 2000 TL’lik bir gider ekleyin. Bu durumda özet tablonun yenilenebilmesi için PivotTable Analizi menüsünden Veri Kaynağını Değiştir düğmesine tıklayın. Açılan penceredeki adresi, sayfadaki A, B, C sütunlarını seçerek Sayfa1!$A:$C olarak değiştirin. Böylece eklediğiniz gider özet tablodaki verilere eklenmiş olur. Bundan sonra ana sayfaya yeni veriler eklediğinizde özet tablonun yenilenmesi için “Yenile” seçeneğine tıklamanız yeterli olacaktır.
12. Özet tablodaki verileri grafik üzerinde görmek için PivotTable Analizi menüsünden PivotChart düğmesine tıklayın. Önerilen Kümelenmiş Sütun grafiği için Tamam’a tıklayın. Aşağıdaki gibi bir Pivotchart (özet tablo grafiği) oluşur. PivotChart’ı özet tablonun genişliği kadar genişletin.

Şekil 5.1.8:

Bu grafiğin üzerinde yine filtreleme düğmeleri bulunuyor. Departman adı düğmesine tıklayıp grafikte istediğiniz departmanların görüntülenmesini kaldırabilirsiniz. Aynı şekilde Tarih (ay) düğmesine tıklayıp grafikte istediğiniz ayların görüntülenmesini kaldırabilirsiniz.13. Örneğin, departmanlardan D ve E’yi, aylardan ise Eylül, Kasım ve Aralığı grafikten kaldırın. Bu durumda PivotChart aşağıdaki gibi olacaktır.

Şekil 5.1.9:

14. Yine diğer grafiklerde olduğu gibi grafiğin sağ üst kısmındaki + işaretine tıklayıp grafik üzerinde bazı düzenlemeler yapabilirsiniz. Yine grafik seçiliyken Biçim menüsüne tıklarsanız, Şekil Stilleri’nden yeni bir grafik stili ekleyebileceğininiz gibi Şekil Dolgusu açılır listesinden bir dolgu ekleyebilir, Şekil Ana Hattı açılır listesinden grafiğin çerçeve rengini değiştirebilirsiniz. Örneğin grafiğiniz aşağıdaki gibi olabilir.

Şekil 5.1.10:

15. PivotChart Analizi menüsünden Alan Listesi düğmesine bir kez tıkladığınızda Alan Listesi kaybolur, tekrar tıkladığınızda Alan Listesi geri gelir. Alan Düğmeleri açılır listesinden grafik üzerindeki düğmelerin kapatılıp açılması sağlanabilir.
16. PivotChart üzerindeyken sağ tıkladığınızda açılan listeden PivotTable Seçenekleri seçeneğine tıklayın. Aşağıdaki pencere açılacaktır.

Şekil 5.1.11:

Buradan PivotTable için bir isim verebilirsiniz. Örneğin Veri sekmesinin altından “Dosyayı açarken verileri yenile” seçeneğine tıklarsanız dosya her açıldığında özet tablodaki veriler yenilenecektir.

Örnek 2: Aşağıda bir firmaya ait A, B, C ve D ürünlerinin memnuniyet düzeylerine ölçmek için yapılmış bir anketin sonuçlarını içeren bir Excel çalışma sayfasının ilk 10 satırındaki veriler verilmiştir.

Şekil 5.1.12:

Tüm verileri özetleyen bir PivotTable ve bu tabloya ait verileri görselleştiren bir Pivotchart oluşturalım.
1. Önce anket sonuçlarını içeren veri dosyasını bilgisayarınıza indirin.
2. İndirdiğiniz dosyayı açın ve veri tablosundaki bir hücreye tıklayın.
3. Ekle menüsünden PivotTable düğmesine tıklayın. Açılan pencerede Tablo veya aralık seçin kutucuğunun içeriğini değiştirmeden bırakabilirsiniz ya da veri sayfasında ilk 7 sütunu seçebilirsiniz. PivotTable’ın yerleştirileceği yer olarak Yeni çalışma sayfası seçin. Yeni bir PivotTable sayfası açılır.
4. Anlaşılır bir özet tablo oluşturmak için satırlar için bir değişken, sütunlar için bir değişken ve değerler kısmına sayısal veri içeren bir sütun başlığı seçilmelidir. Diğer PivotTable alanlarını Filtre bölümüne sürükleyebilirsiniz. PivotTable alanları kısmına Yanıt Tarihi alanına ek olarak Ay (Yanıt Tarihi), Üç aylık dönem (Yanıt Tarihi) ve Yıl (Yanıt Tarihi) şeklinde tarihi parçalara ayrıldığı üç alan daha geldi.
5. PivotTable alanlarından Memnuniyet düzeyi, Tercih Edilen Ürün, Yaş grubu ve Ay (Yanıt tarihi) alanlarını seçin. Excel sayısal veriler içeren Memnuniyet düzeyi alanını Değerler bölmesine, diğer alanları ise Satırlar bölmesine alacaktır. Satırlar bölmesindeki Ay (Yanıt Tarihi) alanını Sütunlar bölmesine ve Yaş grubu alanını Filtreler bölmesine sürükleyin. Özet tablonun durumu ve verilerin PivorChart’ı aşağıdaki gibi olacaktır.

Şekil 5.1.13:

Şekil 5.1.14:

Bu tabloda her bir ürünün aylara göre toplam kaç memnuniyet puanı aldığı, ürünlere göre genel toplamlar ve aylara göre genel toplamlar görülmektedir. Ayrıca Yaş grubu filtresini açıp farklı yaş gruplarını filtrelediğinizde, PivotTable üzerinde sadece aktif olan yaş gruplarının memnuniyet puanlarının toplamları görünecektir.

6. PivotTable alanlarından Cinsiyet alanını aktifleştirin. (Otomatik olarak satırlar bölmesine gelecektir.) Bu durumda memnuniyet puanlarını tabloda Erkek ve Kadınlar için aşağıdaki gibi ayrı ayrı görebilirsiniz. Ancak grafikten de anlaşılacağı gibi bu özet tabloda veriler parçalandığı için grafiğin okunurluğu azalmış oldu.

Şekil 5.1.15:

Şekil 5.1.16:

7. Cinsiyet alanını da Filtre bölmesine taşıyın. Ardından özet tablodaki Yaş Grubu filtre açılır listesinden 18-25 yaş aralığını, Cinsiyet filtre açılır listesinden Erkek seçeneğini seçin. Bu durumda özet tablo ve tabloya ait grafik aşağıdaki gibi olacaktır.

Şekil 5.1.17:

Şekil 5.1.18:

8. Özet tablodaki bir sayısal veriye çift tıkladığınızda o toplamı oluşturan detay veriler ana veri tabanından çekilir ve bir ayrıntı sayfası oluşur.
9. PivotTable alanlarından Şehir alanını Filtre bölmesine sürüklerseniz, şehirlere göre filtreleme yapıp her bir şehirdeki memnuniyet düzeyini ayrı ayrı değerlendirebilirsiniz.