Genel Bilgiler 27 Ocak 2020

1. Özet Tablo Nedir

Elimizde yüzlerce, binlerce satırdan oluşan bir satış verisi olduğunu varsayalım. Bu verilerden istediğimiz değerleri formüllerle süzerek, örnek olarak bölgelere göre satış, bir tablo hazırlayabiliriz. Bunun için bize öncelikle bir bölgeler tablosu gerekir ki bu tabloyu da ana tablodan almamız gereklidir. Biraz uzun sürse de oldukça yapılabilir bir uygulama. Peki sadece bölgelere göre değil, ürüne göre de bir rapor çıkarmanız gerekse, üstüne bölge ve ürün için bir tane, bir tane de toplam ciro, kâr vs. için. Hepsi için ayrı ayrı tablolar yapmanız gerekse. Üstüne tam da sunumdan önce yöneticiden yeni bir talep gelse ve tüm bu analizler birden boşa çıksa… Kabus gibi değil mi? Ancak bütün bunları tek bir formül yazmadan hızlıca bir kaç dakika içinde çıkarabileceğinizi söylesem? Hem zamandan tasarruf, hem de formül kısmını Excel kendi içinde hallettiğinden hata olasılığı da oldukça düşük. Özet tablo işte tam da bu işi yapıyor. Bir kaç fare tıklaması ile istediğiniz analizi hızlıca yapmanızı sağlıyor. Bunu da sizin hazırladığınız sabit tablo yapısından çıkarak daha etkileşimli dinamik bir arayüz aracılığı ile yapıyor.

2. Örnek Veri ve Çalışma Dosyası

Ben örnek veriyi bu web sitesinden 1000 Sales Records dosyasını CSV olarak aldım ve sonrasında Excel’e aktardım. Ufak tefek bazı düzenlemeler yaptım. Siz isterseniz bu şekilde yapabileceğiniz gibi benim kullandığım dosyayı da buradan indirebilirsiniz.

3. Özet Tablo Ekleme

Özet tablo oluşturmak için verileri içeren hücrelerden herhangi birine tıklayarak ekle menüsünden “Pivot Table” seçeneğine tıklamamız yeterlidir.

Excel veri içeren tüm bitişik hücreleri seçerek bir Özet Tablo oluşturma ekranı gösterecektir.

Bu ekrandaki ayarlar ile farklı kaynaklardan da veri çekebilirsiniz. Özellikle eğer sayfanızda dış kaynaklardan veri almak için kullandığınız bağlantılar var ise “Choose Connection” ile bu bağlantıların gösterildiği bir listeden seçim yaparak o verileri doğrudan kullanabilirsiniz.

Yine bu pencerede özet tablonuzu nerede oluşturacağınızı ayarlayabilirsiniz. Eğer “New Worksheet” seçeneğini seçerseniz Excel yeni bir çalışma sayfası oluşturur ve özet tabloyu buraya atar. Biz bu uygulamada “Existing Worksheet” seçeneği ile özet tablomuzu mevcut sayfada oluşturacağız. Bu seçeneğ tıkladığımızda “Location” kısmı etkinleşecektir, ben J4 hücresini seçtim. Sonrasında Excel sayfaya boş bir özet tablo oluşturacak ve Özet Tablo Alanları adlı bir pencere görüntüleyecektir

Excel bomboş hiç bir şeye benzemeyen bir şekil oluşturdu değil mi? Merak etmeyin, o şekil sizin istediğiniz verileri gösterecek bir alan ve tamamen sizin ihtiyacınıza göre sizin tarafınızdan oluşturulacak. Bir nevi tuval diyebiliriz, istediğiniz resmi sizin çizmeniz gerekecek.

NOT 1: Özet tablo oluşturmadan önce verilerimizi içeren aralığı tablo haline getirmemiz veya değişken aralık ile isim tanımlamanız daha uygun olacaktır. Neden? Çünkü sayfaya yeni veriler eklediğinizde özet tablonuzun veri çektiği aralık ta otomatik olarak güncellenir.

4. Özet Tablo Bölümleri ve Hazırlama

Özet tablo alanları penceresi iki ana kısımdan oluşmakta. Üst kısımda özet tablo oluşturmakta kullandığımız aralığın sütun başlıkları yer alıyor. Alt kısımda ise Özet Tabloyu oluşturacak ana kısımlar. Aşağıdaki resimde hangi tablo bölümlerinin özet tablo tuvali üzerinde hangi bölümlere geldiğini göstermeye çalıştım.

Filtre alanına sürüklediğiniz her başlık, tablonun üst kısmına bir açılır kutu olarak yerleştirilir ve buradan ilgili alana sahip tüm verileri süzerek sadece seçtiğiniz seçeneğe uygun değerlerin özet tabloda dikkate alınmasını sağlar.

Satır alanına sürüklediğiniz her başlık, özet tablonuzun satırlarında gösterilecek ana başlıkları belirler. Yine sütun alanına eklediğiniz her başlıkta özet tablonuzun sütunlarını oluşturur.

Toplam değerler ise tablonuzun satır ve sütunlarının kesiştiği hücrelerde görmek istediğiniz değerleri ifade etmektedir.

Özet tablo hazırlamak için üst kısımda yer alan sütun başlıklarını aşağıdaki alanlara sürükleyerek bırakmanız yeterli. Her alana birden fazla sütun başlığı koymanız mümkün. Aşağıdaki videoda nasıl yapıldığını gösterdim.

Videoda gördüğünüz gibi özet tablonun filtre, satır ve sütun başlıklarında süzme yapmanızı sağlayacak düğmeler mevcut. Bu düğmelerden herhangi birine bastığınızda aşağıdakine benzer bir süzme penceresi gösterilecektir. Bu pencerede normalde tek bir madde seçebilirsiniz ancak en altta yer alan “Birden Çok Öğe Seç” kutusuna tıklarsanız Excel sizin birden fazla madde seçmenize imkan verecektir.

5. Önerilen Özet Tablo Ekleme

Excel 2013 ile beraber özet tablo oluşturmak için bir seçenek daha sunuldu. Bu da “Önerilen Pivot Table” sçeneği ve hemen özet tablo seçeneğinin yanında yer alıyor.

Bu simgeye tıkladığınızda Excel karşınıza tablonuzda yer alan başlıklara göre farklı değerleri analiz edebileceğiniz bir kaç seçenek gösterir. Bunlardan herhangi birini seçerseniz Excel sizin yerinize başlıkları ayarlayarak bir özet tablo oluşturur. İsterseniz alt köşede yer alan “Blank Pivot Table” seçeneği ile boş bir özet tablo oluşturabilirsiniz.

6. Özet Tablo Düzenleme

Özet tablonun toplam alanında yer alan rakamların biçimlendirmesini değiştirebiliriz. Bunu yapabilmek için toplam alanındaki herhangi bir hücreye sağ tıklayarak gelen menüden “Hücreleri Biçimlendir” seçeneğini seçmemiz gerek. Sonrasında Excel’in standart hücre biçimlendirme penceresi gösterilecektir

Bu pencereden istediğiniz sayı biçimlendirmesini seçerek “Tamam” düğmesine bastığınızda toplam kısmındaki tüm hücrelerin değerleri istediğiniz gibi biçimlendirilecektir. Sadece tek bir hücrenin veya belli bir aralığın biçimlendirmesini değiştirmek isterseniz hücreye sağ tıkladığınızda gelen menüde “Hücreleri Biçimlendir” seçeneğini seçmeniz gerekmektedir.

Sağ tık menüsünden yapabileceğiniz bir diğer ayar ise sütunları sıralamaktır. Sağtıklayarak açtığınız menüden bu sefer “Sırala” seçeneğine gelerek küçükten büyüğe veya büyükten küçüğe sıralama yapabilirsiniz. Hatta bu işlemi satırlara da uygulayabilirsiniz.

Siz özet tabloyu oluşturduğunuzda Excel en alta en en sağa Genel Toplam adlı satır ve sütunu otomatik olarak oluşturur. Eğer siz Genel Toplam Sütununu görmek istemiyorsanız tabloya sağ tıklayarak “Pivot Table Seçenekleri” kısmına gelerek ardından açılan pencerede “Toplamlar ve Filtreler” sekmesine gelerek “Satırlar için genel toplamı göster”, “Sütunlar için genel toplamı göster” adlı iki tane kutunun üzerindeki onayı kaldırmanız gereklidir.

7. Değerleri Toplam, Yüzde Olarak Gösterme

Özet tabloda toplam yerine kaç adet ürün olduğunu bulmak için izleyebileceğimiz iki yöntem var.Ben bu örnek için özet tablomu biraz sadeleştireceğim. Satırlar kısmı sadece “Region” başlığından sütunlar kısmında ise sadece “Units Sold” başlığından oluşan bir özet tablo oluşturacağım. Sonrasında “Units Sold” başlığını bir kez daha toplam alanına ekleyeceğim. Böylece toplam alanında 2 tane “Units Sold” başlığı olacak.

Şimdi bir bölgede kaç tane farklı ürün girdisi olduğunu görmek için “Units Sold” sütununda yer alan herhangi bir hücreye sağ tıklayarak gelen menüden “Değerleri Özetleme Ölçüsü” seçeneğininin üzerine gelerek “Say” seçeneğini seçeceğiz.

Bunu yapmanın bir yöntemi de ilgili alana yandaki özet tablo kısmından tıklamak ve açılan menüden “Değer Alanı Ayarları” seçeneğini seçtikten sonra gelen pencereden “Değer alanı özetle” kısmında “Say” seçeneğini seçmektir. Gördüğünüz gibi bu iki menü birbirinin aynısı ve farklı diğer seçenekler de sunmakta. Dikkat ederseniz 2. pencerenin en üstünde “Özel Ad:” adında bir metin kutusu yer alıyor. Buradaki değeri değiştirerek ilgili sütuna daha anlaşılır isim verebilirsiniz. 

Değerleri toplam olarak değil yüzde olarak göstermek için ise yine ilgili sütundaki herhangi bir hücreye sağ tıkladıktan sonra gelen menüden “Değer Gösterimi” seçeneğinin üzerine gelerek açılan ek menüden istediğimiz seçeneklerden birini seçebiliriz. Ben burada “Sütun değerlerinin %” seçeneğini seçiyorum. Bu seçenek ile aynı sütunda yer alan tüm değelerin % toplamları 100’e eşit olacak şekilde Excel gerekli değeri hesaplayarak hücrede gösteriyor.

8. Verileri Yenileme

Özet tablo oluşturduğunuz anda Excel göstermiş olduğunuz aralıktaki verilerin bir görüntüsünü oluşturarak çalışma kitabına kaydeder. Bu şu anlama gelir; özet tablo üzerinde yapılan her işlem verilerin çekildiği aralık üzerinde değil bu görünümü alınmış veri üzerinde gerçekleştirilir. Bunun en temel faydası oldukça büyük veriler ile çalışırken sürekli ana verileri okuma gereğinin ortadan kaldırılması, yani daha hızlı hesap yapabilmesidir. Bu aynı zamanda ana tablonuza yeni veriler eklediğinizde bu görünümün yenilenmemesi, yani yeni verilerin özet tabloda yer almamasıdır.

Siz tablonuza veri ekledikten sonra, özet tablo önbelleğini de güncellemek için yapmanız gereken özet tabloya sağ tıkladığınızda gelen menüden “Yenile” seçeneğini seçmektir. Bunun bir başka yöntemi de özet tablo seçili iken gösterilen “PivotTable Analizi” araç çubuğu üzerindeki “Veri” alanında yer alan “Yenile” simgesine tıklamaktır.

Eğer sayfanızda farklı önbellekler kullanan birden fazla özet tablo var ise aynı yerdeki “Tümünü yenile” komutu ile kitaptaki tüm özet tablolarının kullandığı önbellekler güncellenir. Ancak kullandığınız verinin miktarına göre bu işlem oldukça fazla zaman alabilir.

Özet tablo önbelleğinin sebep olduğu en büyük ikinci sorun ise siz ana tablonuzdan bazı başlıkları silseniz dahi bu değerlerin özet tablo filtre alanlarında hala gözüküyor olmasıdır. Örnek olarak ana tablomuzda “Cosmetics” başlıklı tüm ürünleri seçip sildiğimizi varsayalım. Özet tablomuzu yenilediğimizde satırlarda artık “Cosmetics” başlığı olmayacaktır ancak süzme düğmesine bastığımızda bu başlığın hala seçenekler içinde olduğunu göreceksiniz. Bunu engellemek için yapmanız gereken özet tablo üzerinde sağ tıkladığınızda gelen “Özet Tablo Seçenekleri” seçeneğine tıklamak ve ardından gelen pencerede “Veri” sekmesinde “Alan Başı korunacak öğe sayısı” kutucuğundaki işareti kaldırmanız gerekmekte.

9. Özet Tablo Silme

Bir özet tabloyu silmek istiyorsanız, öncelikle o özet tablonun tüm hücrelerini, filtre alanı da dahil olmak üzere, seçmeniz ve sonrasında klavyedeki “Sil” tuşuna basmanız gerek. Büyük tablolarda bunu yapmanın en kolay yolu,  özet tablonun herhangi bir hücresine tıkladığınızda gelen “PivotTable Araçları” araç çubuğunun “Analiz” sekmesinde “Eylemler” kısmında yer alan “Seç” komutuna tıklayıp açılan menüden “Tüm Özet Tablo” seçeneğini seçmektir.

10. Özet Tabloların Avantajları

Özet tabloları kullanmanın ilk avantajı oldukça hızlı olmasıdır. Hiç bir formül bilmenize veya kullananıza gerek kalmaz sadece sürükle ve bırak ile hızlıca verilerinizi analiz edebilirsiniz. Eğer ana tablonuzdaki verileriniz doğru ise Özet Tabloda yer alan değerlerin de doğru olduğuna emin olabilirsiniz.

Özet tablolar oldukça esnektir. Farklı değerleri analiz etmek için farklı özet tablolar hazırlayabilirsiniz. Burada Excel size bir avantaj daha sunar, aynı veri aralığını kullanan birden fazla özet tablo tek bir önbellek verisi kullanır.

Özet tablolar anlattığım gibi hem satırlarda hem sütunlarda yer alan filtreleme özelliklerine sahip olduğu gibi ek olarak bir de “Filtre” adında ek bir alan sunar. Bu sayede çok büyük verileri içeren tablolarınızı çok etkili bir şekilde farklı değerleri gösterecek biçimde değiştirebilirsiniz.

Özet tablolar hızlıca biçimlendirilebilir. Hatta burada Excel Tablolarında gördüğümüz biçimlendirmelere benzer bir yöntem de kullanabiliriz.

Özet tablolar yeni değerler eklendiğinde içeriğini güncelleyecek şekilde geliştirilmişlerdir. Sadece bir kaç tıklama ile verilerinizi güncelleyebilirsiniz. Tabii burada Excel Tablolarını kullanmak size büyük bir avantaj sağlayacaktır.

Ve tabii olmazsa olmaz, özet tabloları kullanarak hızlıca grafikler oluşturabilirsiniz. Burada Excel’in bir faydalı özelliği daha geliyor, hazırladığınız grafik üzerinde özet tablonuzda kullandığınız alanlardaki filtreler de geliyor. Hatta özet tablonuzdaki alanları değiştirdiğinizde grafiğiniz de bu değişikliği yansıtacak şekilde güncellenecektir. Bu sayede hiç bir ön bilgiye ihtiyaç duymadan dinamik grafikler hazırlayabilirsiniz.

İlginizi Çekebilecek Diğer Yazılar
4 Comments
Etiketler:

You may also like this

  • Grafiklerde Değişken Aralık Kullanma

    • 4 Comments…

    1. Merhaba,

      Özet tabloda 11 sütun kullanıyoruz, 12. sütunu eklemeye çalıştığımızda hedef hücrenin içeriğini değiştirmek istiyormusunuz diye uyarıyor ve evet dersek ilgili sütunu değiştiriyor. Yani 12. Sütunu ekleyemiyoruz. Acaba böyle bir sınır mı var ?

      Cevapla

    2. Merhabalar, bir okulda müdür yardımcısı olarak görevliyim. öğrenciler 9 ay aidat ödüyorlar . ben bu aidatları aylık olarak kayıt altına alıp tarihleri ve ödeme miktarlarıyla bir veri olarak alabilir miyim? ödeme sayfasını oluşturuyorum fakat veri süzme gibi şeyleri yapamadım bir türlü sürekli hata veriyor. yardımcı olabilir misiniz acaba.

      Cevapla

    Leave a Reply