Grafik ve Dinamik Tabloları Etkileşimli Tasarlama

Etkileşimli grafik ve dinamik tablo (interactive chart and PivotTable), verinin kullanıcı tıkladıkça ya da seçim yaptıkça kendini yeniden özetlediği Excel raporlarıdır. Bunun için veriyi Tablo olarak biçimlendirir, özet tablo ve özet grafik oluşturur, dilimleyici, zaman çizelgesi ya da açılır liste ekleyerek raporu canlı hale getirirsiniz. Bu sayfada her yöntemin nasıl kurulduğunu, hangi durumda hangisini seçeceğinizi ve üç uygulamalı örneği bulacaksınız.

Etkileşimli grafik nasıl oluşturulur​

Excel'de etkileşimli grafiğin en pratik yolu özet grafiktir. Verinizin içinde bir hücre seçin ve Ekle > Grafikler > Özet Grafik (Insert > Charts > PivotChart) yolunu izleyin. Excel hem özet tabloyu hem ona bağlı grafiği oluşturur; tablodaki alanları değiştirdiğinizde grafik de değişir.

Grafiği tıklanabilir hale getiren başlıca araçlar şunlardır:

• Alan düğmeleri: Özet grafiğin üzerindeki filtre düğmeleriyle ürün, bölge ya da ay gibi kategorileri açıp kapatırsınız.
• Dilimleyici: Özet tablonun içine tıklayıp Özet Tablo Çözümle > Filtre > Dilimleyici Ekle (PivotTable Analyze > Filter > Insert Slicer) yolunu izleyin. Dilimleyici, filtre seçeneklerini düğme olarak gösterir; birden fazla öğeyi Ctrl tuşuyla seçebilirsiniz.
• Zaman Çizelgesi: Aynı gruptaki Zaman Çizelgesi Ekle (Insert Timeline) düğmesiyle tarih alanını ay, çeyrek ya da yıl düzeyinde süzersiniz. Tarih alanı gerçek tarih değerlerinden oluşmalıdır.
• Rapor Bağlantıları: Dilimleyiciye sağ tıklayıp Rapor Bağlantıları (Report Connections) seçeneğini kullanırsanız tek bir dilimleyici birden fazla özet tabloyu ve grafiği aynı anda süzer.

Özet grafikte bazı grafik türleri desteklenmez; örneğin dağılım, kabarcık ve hisse senedi grafikleri özet grafik olarak oluşturulamaz. Bu türler için formül tabanlı yöntemi kullanabilirsiniz.

Dinamik tablo nasıl hazırlanır​

Özet tablonun güvenilir çalışması için kaynak veriyi önce Excel Tablosu yapın. Veri içinde bir hücre seçip Ekle > Tablolar > Tablo (Insert > Tables > Table) komutunu kullanın ya da Ctrl+T tuşlarına basın. Tabloya eklenen yeni satırlar tablonun içine otomatik dahil olur. Ardından Ekle > Tablolar > Özet Tablo (Insert > Tables > PivotTable) komutunda kaynak olarak tablo adını seçerseniz kaynak aralığını elle genişletmeniz gerekmez.

Özet tablonun ayarlarını şöyle yönetin:

• Alan yerleşimi: Alan Listesi'nde alanları Satırlar, Sütunlar, Değerler ve Filtreler kutularına sürükleyip bırakarak düzeni anında değiştirirsiniz.
• Gruplama: Tarih ya da sayı alanındaki bir öğeye sağ tıklayıp Grupla (Group) seçeneğini kullanarak günleri aylara, ayları çeyreklere dönüştürürsünüz.
• Hesaplanan alan: Özet Tablo Çözümle > Hesaplamalar > Alanlar, Öğeler ve Kümeler > Hesaplanan Alan (Fields, Items & Sets > Calculated Field) ile mevcut alanlardan yeni bir ölçü üretirsiniz.
• Yenileme: Özet tablo kaynak veri değişince kendiliğinden güncellenmez. Özet Tablo Çözümle > Veri > Yenile (Refresh) komutunu ya da Alt+F5 tuşlarını kullanın; tüm özet tabloları yenilemek için Ctrl+Alt+F5 yeterlidir.

Kaynak veride başlık satırı eksiksiz olmalı, sütunlarda boş başlık ve birleştirilmiş hücre bulunmamalıdır.

Açılır listeyle formül tabanlı etkileşimli grafik​

Özet tablo kullanmak istemiyorsanız, seçime göre değişen bir grafik verisi hazırlayabilirsiniz. Önce seçim hücresine bir açılır liste ekleyin: hücreyi seçin, Veri > Veri Araçları > Veri Doğrulama (Data > Data Tools > Data Validation) penceresinde İzin Verilen olarak Liste'yi seçin ve kaynak olarak ürün başlıklarını gösterin. Ardından grafiğin kullanacağı sütunda seçilen ürünün değerini formülle getirin. Aşağıdaki formül, seçim hücresindeki ürün adını başlık satırında arar ve aynı satırdaki değeri döndürür:
Grafiği ay sütunu ile bu formül sütununa bağlarsanız, listeden ürün değiştirdiğinizde grafik hemen yeni ürüne göre çizilir. Aynı yöntemi geliştirici sekmesindeki form denetimleriyle (açılır kutu, kaydırma çubuğu) de kurabilirsiniz.

Filtre uygulanmış Excel Tablosu da grafik için bir seçenektir: tabloya dilimleyici ekleyip grafiği tablodan oluşturursanız, varsayılan ayarlarda gizlenen satırlar grafikte çizilmez ve grafik süzmeye göre değişir.

1. Seçime göre değişen grafik verisi​

Aylık satış adetleri üç ürün için tutuluyor. F2 hücresindeki ürün adına göre E sütunu seçilen ürünün değerlerini getiriyor. F2 hücresine açılır liste ekleyip grafiği A ve E sütunlarından oluşturabilirsiniz.
F2 hücresinde Telefon seçili olduğu için E2 hücresi Ocak ayı için 95, E7 hücresi Haziran ayı için 130 değerini getirir. F2 hücresini Laptop ya da Tablet olarak değiştirdiğinizde E sütunu ve ona bağlı grafik hemen yeni ürüne göre güncellenir.

2. Özet tablo için kaynak veri hazırlama​

Özet tablo ve zaman çizelgesi için kaynak veri satır satır tutulur. Tarihler gerçek tarih değeri olarak girilir, Tutar sütunu ise adet ile birim fiyatın çarpımıyla hesaplanır. Bu aralığı Ctrl+T ile Tablo yapıp özet tablo ve özet grafik oluşturabilirsiniz.
F2 hücresi ilk satırın tutarını 100.000,00, F9 hücresi son satırın tutarını 126.000,00 olarak hesaplar. Aralığı Tablo yaptıktan sonra özet tabloda Bölge alanını satırlara, Tutar alanını değerlere yerleştirin; Tarih alanı için Zaman Çizelgesi Ekle komutuyla dönem süzmesi ekleyin. Yeni satır eklediğinizde özet tabloyu yenilemeniz yeterlidir.

3. Hedef takibinde koşullu biçimlendirme​

Şube hedefleri ve gerçekleşen satışlar yan yana tutuluyor. Oran sütunu gerçekleşmeyi hedefe böler, Durum sütunu ise hedefe ulaşılıp ulaşılmadığını yazar. Oran sütununa veri çubukları eklediğinizde tablo grafik gibi okunur.
İstanbul şubesinin oranı D2 hücresinde %108, durumu E2 hücresinde Hedefte olarak görünür. Ankara için sonuçlar %94 ve Hedef altı biçimindedir. D2:D7 aralığını seçip Giriş > Stiller > Koşullu Biçimlendirme > Veri Çubukları (Home > Styles > Conditional Formatting > Data Bars) komutunu uygularsanız hedefin altında kalan şubeler çubuk uzunluğundan hemen anlaşılır.

Sık sorulan sorular​

Özet tablo neden otomatik güncellenmiyor? Özet tablo formül gibi anında hesaplanmaz. Kaynak veriyi değiştirdikten sonra Yenile komutunu ya da Alt+F5 tuşlarını kullanın.

Dilimleyici ile zaman çizelgesi arasındaki fark nedir? Dilimleyici her türlü alanda öğe seçtirir. Zaman çizelgesi yalnızca tarih alanlarında çalışır ve dönem aralığını kaydırarak seçmenizi sağlar.

Özet tablo olmadan grafiği dilimleyiciyle süzebilir miyim? Evet. Veriyi Excel Tablosu yapıp tabloya dilimleyici ekleyebilirsiniz; grafik tablodan oluşturulmuşsa süzülen satırlara göre güncellenir. Tablo dilimleyicisi Excel 2013 ve sonraki sürümlerde bulunur.

Yeni eklediğim satırlar özet tabloya neden gelmiyor? Kaynak sıradan bir aralıksa yeni satırlar aralığın dışında kalır. Kaynağı Tablo yapın, ardından özet tabloyu yenileyin.

Kullanırken dikkat edin​

• Yenilemeyi unutmayın: Özet tablo ve özet grafik, kaynak veri değişince kendiliğinden güncellenmez. Yenile komutunu kullanın ya da Özet Tablo Seçenekleri'nden dosya açılırken yenilemeyi etkinleştirin.
• Tarih alanı gerçek tarih olmalı: Zaman çizelgesi yalnızca tarih olarak tanınan hücrelerde çalışır. Metin olarak girilmiş tarihler varsa önce bunları gerçek tarih değerine dönüştürün.
• Özet grafikte tür sınırı: Dağılım, kabarcık ve hisse senedi grafikleri özet grafik olarak kullanılamaz. Bu türlerde formül tabanlı yöntemi ya da süzülmüş Tablo kaynağını tercih edin.
• Kaynak veriyi düzenli tutun: Başlık satırında boş hücre ya da sütunlarda birleştirilmiş hücre bulunması özet tablo oluşturmayı engelleyebilir ya da alan adlarını bozabilir. Kaynağı Tablo yapmak bu sorunların çoğunu önler.
• Dilimleyicileri düzenli yerleştirin: Çok sayıda dilimleyici ekranı kaplar. Dilimleyici sekmesinden sütun sayısını ve boyutu ayarlayın, gereksiz dilimleyicileri silin ve raporu paylaşmadan önce tüm süzmeleri temizleyin.
• İlgili konular:
  • Özet Tablo ve Özet Grafik: Ekle sekmesinden oluşturulan etkileşimli raporlar
  • Dilimleyici ve Zaman Çizelgesi: Özet tablo ve Tablo süzme araçları
  • Veri Doğrulama: Açılır liste ile seçim hücresi hazırlama
  • Koşullu Biçimlendirme: Veri çubukları ve simge kümeleri
  • İşlevler: İNDİS, KAÇINCI, EĞER, ÇOKETOPLA, ÖZETVERİAL

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.