Bu yazımda dinamik olarak değişen bir grafik uygulaması yapmak istiyorum. Dinamik grafik derken seçtiğimiz değere göre hazırladığımız grafiğin kendini güncellemesini kastediyorum. Uygulama olarak uzun zaman önce bir arkadaşta gördüğüm ve nasıl yapabileceğimi uzun uzun düşündüğüm seçilen verinin farklı renkte gösterilerek vurgulanması uygulamasını seçtim. Nasıl bir sonuç elde edeceğimi aşağıdaki videoda gösterdim. Etkileyici değil mi? Hadi başlayalım.
1. Hazırlık
Bu örneği yapabilmek için öncelikle veri doğrulama, temel grafik işlemlerini ve ikincil eksen oluşturmayı bildiğinizi varsayıyorum. Eğer bu konularda bilginiz yoksa lütfen önce bu yazılarımı okuyun.
Şimdi isterseniz bu uygulamada kullandığım tabloyu vereyim. Bu tablo bizim ana tablomuz. Daha sonra bir de yardımcı tablo oluşturacağım ve bu yardımcı tablo verilerini buradan çekecek.
2. Uygulama
a. Ana Tablonun Hazırlanması
Bu uygulama için 1. eksende tabloda yer alan değerleri kullanıyorum. Bu değerler arka planda dikkat çekmeyecek şekilde gözükmesi için hepsini soğuk, açık bir renge boyayacağım. Hücre etkisi oluşturmak için de sınırlarını beyaz renk 3nk olarak ayarlıyorum. Aşağıdaki videoda bu işlem adımlarını görebilirsiniz.
b. Yöntemin Açıklaması
İstediğimiz etkiyi elde edebilmek adına vurgulamak istediğimiz veriyi çekerek kullanacağımız ikinci bir tablo hazırlamamız gerek. Ben kendi uygulamamda Aşağıda bu tablonun nasıl olduğunu görebilirsiniz. Formülleri de bu tablodaki adreslere göre şekillendireceğim. Siz farklı bu tabloları farklı hücrelerde oluşturursanız formüllerdeki hücre adreslerini kendi tablonuzdaki adreslere göre güncelleyin.
Burada yapmak istediğim ilk tablodaki değerleri seri olarak bir yığılı sütun grafiğine eklemek. Renk olarak biraz daha soluk bir renk kullanmak, sonra ikinci tablodaki serileri de aynı grafiğe eklemek. Ancak ikinci tablodaki Ürün Alt ve Ürün Üst serilerini gözükmeyecek şekilde biçimlendireceğim. Ürün Alt serisi, seçtiğim ürünün altında kalan tüm ürünlerin toplamlarını içerecek ve seçtiğim ürünün daha yukarıda gözükmesini sağlayacak. Ürün Üst serisi ise ikinci eksen ile ilk eksenin üst limit değerlerini eşit yapmaya yarayacak.
İlk önce veri doğrulama ile ön plana çıkarmak istediğim seriyi seçeceğim hücre içinde bir listeyi oluşturalım Bunu veri doğrulama ile yapacağım. Kendi örneğimde B11 hücresinde “VERİ”, “Veri Doğrulama” seçeneğini kullanarak B4:B7 aralığını liste olarak tanımladım.
c. Ek Verilerin Çekilmesi
Sonrasında seçtiğim ürünün değerlerini çağıracak formülü yazmam gerek. Burada DÜŞEYARA formülü benim işimi görecektir. Ben C11 hücresine aşağıdaki formülü yazdım ve daha sonra G11 hücresine kadar bu formülü çoğalttım. Bu sayede B11 hücresindeki listeden seçilen ürünün satış değerlerini ilgili hücrelere getirmiş oldum.
=DÜŞEYARA($B$11;$B$3:$G$7;KAÇINCI(C$10;$B$3:$G$3;0);0)
Sonrasında seçtiğim seriyi istediğim konuma taşıyacak olan Ürün Alt serisini oluşturacağım. Bunun için seçtiğim alanın altında kalan değerleri içerecek bir aralık elde edecek formülümü tanımlıyorum:
=KAYDIR($B$3;1;KAÇINCI(C$10;$C$3:$G$3;0);KAÇINCI($B$11;$B$4:$B$7;0)-1;1));0)
Bu formül sayesinde önce Bölge 1 ismini C3:G3 aralığında aratıyorum, 1 değeri geliyor. Sonra B11’deki ürün adını B4:B17 aralığında aratıp bulduğum sayıdan 1 çıkarıyorum ve resimdeki örnek için 2 değerini buluyorum. Hemen ardından KAYDIR formülü ile B3 hücresinden başlayarak 1 aşağı, 1 yan hücre olan C4 hücresinden başlayarak toplam 2 satır ve 1 sütundan oluşan bir aralık geri döndürüyorum. Sonuç olarak elimde C4:C5 aralığı oluyor.
Sonrasında bu aralıktaki tüm değerleri toplayacağım. Bunun için TOPLA formülünü kullanıyorum:
=TOPLA(KAYDIR($B$3;1;KAÇINCI(C$10;$C$3:$G$3;0);KAÇINCI($B$11;$B$4:$B$7;0)-1;1));0))
Eğer seçtiğim ürün ilk satırda ise KAYDIR formülü hata döndürecek, dolayısı ile TOPLA formülüm de. Bunu engellemek için EĞERHATA formülü ile bu formülü birleştireceğim:
=EĞERHATA(TOPLA(KAYDIR($B$3;1;KAÇINCI(C$10;$C$3:$G$3;0);KAÇINCI($B$11;$B$4:$B$7;0)-1;1));0));0)
Formülü C12 hücresine yazıp yana doğru uzattığımda tüm bölgeler için istediğim değerleri elde etmiş olacağım. Sonrasında Ürün Üst serisini oluşturacağım. Bu seri belli bir bölgedeki tüm ürünlerin toplamı ile yardımcı tablomda seçtiğim ürün ve ürün alt değerlerinin toplamının farkı. Yani:
=TOPLA(C$4:C$7)-TOPLA(C$11:C$12)
Bu formülü de C13 hücresine yazıp yana doğru çektiğimde gerekli olan tüm verileri elde etmiş oluyorum. Aşağıda yardımcı tablomun son halini görebilirsiniz.
d. İkinci Eksenin Eklenmesi ve Grafiğin Tamamlanması
Bu tabloda B11 hücresinde yer alan ürünü değiştirdiğinizde tablodaki değerlerin de değiştiğini görebilirsiniz. Artık dinamik tablomuzu oluşturmak için elimizde her türlü veri var. Hemen bu verileri de grafiğimize ekleyelim. Bunu yaparken dikkat etmeniz gereken husus, serilerin sıralaması. Önce Ürün Alt, sonra Ürün en sonunda Ürün Üst serisini eklemeniz gerek. Eğer bu sıralamaya dikkat etmezseniz daha sonra serilerin sıralamasını grafik veri seçme penceresinde de yapabilirsiniz.
Aşağıdaki videoda işlem adımlarını gösterdim.
İlk başta oldukça zor gözüken bir uygulama olabilir. Özellikle Excel’e yeni başlayan kullanıcılar için ancak gördüğünüz üzere korkacak bir durum yok. İşlemi adımlara böldüğümüzde hızlı ve kolayca istediğimiz sonucu alabiliyoruz.
3. Neler Yapılabilir
Ben bu uygulamada veri etiketleri eklemedim. Siz isterseniz 2. tabloda vurgu için kullandığım seriye veri etiketi ekleyerek istediğiniz gibi biçimlendirebilirsiniz. Seçim değiştiğinde veri etiketlerindeki değerler de kendini güncelleyecektir.
Dinamik grafikler her ne kadar güzel gözükseler ve vurgulanmak istenen veriyi ön plana çıkarsalar da sunumlarda kullanılması veya tüm verilerin özet olarak gösterilmesi istenen yerlerde kullanılmasında sıkıntı yaratır. Bu tarz grafikler genelde dinamik gösterge raporlarında kullanılır. Bunu da ek bilgi olarak eklemekte fayda var.
İlginizi Çekebilecek Diğer Yazılar
Dinamik Formüller İle Farklı Kıstaslara Göre Veri Toplama
İki Listeyi Birleştirme
Formülle Özet Tablo Ters Çevirme
Metin İçinden Aranan Birden Fazla Degeri Cekme
Arama Yapılabilen Açılır Liste
Veri Doğrulama İle Tek Seçim
You may also like this
