1. Amaç
Dinamik grafik hazırlarken farklı miktarda veri içeren grupların grafik olarak gösterilmesinde sorun yaşanabiliyor. Örnek olarak farklı projeleri gösteren bir grafik hazırladığınızı varsayalım. Projelerin toplam ay adeti farklı ise hazırladığınız grafik aralığı en uzun projeye göre ayarlanmanız gerek. Eğer ayarlamazsanız fazla olan aylar grafikte gösterilmez. Ancak bu durumda uzunluğu kısa olan proje için fazladan boş değerlerin grafikte gösterilmesine yol açarsınız.
Bunu engellemek için değişken aralıkların grafik içinde kullanılmasını sağlamanız gerek. Bu nedenle öncelikle değişken aralıklar yazımı okumanızı tavsiye ediyorum.
Uygulama esnasında kullandığım dosyayı da buradan indirebilirsiniz. Dosyayı açtığınızda “Planlama”, “Gerçekleşen” ve “Grafik Değerleri” adlı üç tane sayfa göreceksiniz. İlk sayfada projeler için aylık planlanan saatleri içeren “Planlama” adlı bir tablo, ikinci sayfada ise buna karşılık gerçekleşen saatleri içeren “Gerçekleşen” adlı bir tablo yer alıyor. Uygulama esnasında tüm formülleri 3. sayfada oluşturacağız.
Not: Burada anlatacağım yöntemler elimdeki veri setine bağlı olarak oluşturuldu. Örnek olarak tarihe dayalı bir raporlama ve grafik oluşturma var. Burada anlatılan yöntem sadece bu örnek için oluşturulmuş olup uygulama yapacağınız veri setine göre farklı formüller ve yöntemler kullanmanız gerekecektir.
2. Uygulama
Burada yapmak istediğim her iki tabloda yer alan saatleri tek bir tablo altında birleştirmek. Bu tablodaki değerlerin de açılır bir listeden seçilen proje adına göre çekilmesini sağlamak. Bu sayede kullanıcı proje adını seçtiğinde otomatik olarak tablo ve bu tabloya bağlı olarak oluşturulacak grafik güncellenecek.
Dosyayı açtığınızda aşağıdaki gibi bir yapıya sahip 2 tane tablo göreceksiniz. Biz bu tabloyu önce istediğimiz şekle getireceğiz.
a. Ay ve Yıl Değerlerini Tarihe Çevirme
Tabloları incelediğimizde proje süreleri ay ve yıl olarak girilmiş. Ancak grafikte yatay eksenin daha anlaşılabilir olması için ben bunları tarihe dönüştüreceğim. Bunun için her iki tabloda da “Yıl” sütunundan sonra “Tarih” adında yeni bir sütun oluşturacağım ve aşağıdaki formülü gireceğim:
=TARİH([@Yıl]; [@Ay]+1;0)
Bu formül ile her ay için ayın son günü bulunarak sütun doldurulacaktır. Değerlerin rakam değil tarih olarak gelmesi sonraki aşamalarda işimizi çok kolaylaştıracak. Sonucunda aşağıdaki gibi ay ve yıldan tarih elde ettiğimiz bir tablo oluşmuş olacak. Bu işlemi hem Planlanan hem de Gerçekleşen tablolarına uygulamanız gerek.
b. Proje İsimlerini Çekme ve Veri Doğrulama Listesine Atama
Dinamik grafikte kullanmak üzere bana proje isimlerinin yer aldığı bir veri doğrulama listesine ihtiyacım var. Bunu yapabilmenin en kolay yöntemi Excel 365 için BENZERSİZ kullanmak. Ben A2 hücresine aşağıdaki formülü yazıyorum
=BENZERSİZ(Planlama[Proje Adı])
Eski Excel sürümleri için buradaki anlattığım yöntemi kullanarak aşağıdaki formülü oluşturarak 10 satıra kadar uzatarak çoğaltabilirsiniz.
=EĞERHATA(İNDİS(Planlama[Proje Adı];KAÇINCI(0;İNDİS(EĞERSAY($A$1:$A1;Planlama[Proje Adı]);;);0););””)
İki formül arasındaki temel fark ilk formül siz planlama tablosunu ne kadar proje eklerseniz ekleyin otomatik olarak kendini güncelleyecektir. İkinci formülde ise 10 satırı aşan adette projeniz var ise bu durumda formülün uygulandığı aralığını tekrar el ile büyütmeniz gerek.
Sonrasında C1 hücresine Proje yazacağım ve C2 hücresine bir veri doğrulama listesi oluşturarak aşağıdaki formülü gireceğim:
=KAYDIR($A$2;;;EĞERSAY($A:$A;”*?”)-1;1)
Excel 365 kullanıyorsanız bu uzun formül yerine $A$2# yazabilirisiniz. Artık C2 hücresinde proje seçebileceğim bir açılır listem oldu. Bu aşamada C2 hücresine tıklayarak bir proje seçin çünkü bundan sonraki adımlar bu seçili projeye göre ilerleyecek.
Bu işlemleri yaptığınızda sayfa görüntüsü aşağıdaki gibi oluşacaktır.
c. Proje Tarih ve Toplam Ay Adedini Bulma
Not: Burada kullanacağınız yöntem tamamen verilerinizin dağılımı ile ilgili. Bu değerler farklı bir tabloda yer alabilir veya planlama ve gerçekleşen tablolarından çekilebilir, ben burada planlama ve gerçekleşen tablolarındaki verileri kullanarak bu değerleri elde edeceğim.
Burada kullanacağım yöntem her iki tabloda o proje için tanımlanmış başlangıç ve bitiş tarihlerini bulmak ve sırası ile iki tarih arasındaki en küçük ve en büyük tarihi almak ve bunlar arasındaki toplam ay sayısını bulmak.
Bunun için Excel 2019 ve 365’te yeni bir işlev olan ÇOKEĞERMAK ve ÇOKEĞERMİN işlevlerini kullanabilirim:
=ÇOKEĞERMAK(Planlama[Tarih];Planlama[Proje Adı];$C$2)
=ÇOKEĞERMİN(Planlama[Tarih];Planlama[Proje Adı];$C$2)
=ÇOKEĞERMAK(Gerçekleşen[Tarih];Gerçekleşen[Proje Adı];$C$2)
=ÇOKEĞERMİN(Gerçekleşen[Tarih];Gerçekleşen[Proje Adı];$C$2)
Bu formüller ile Gerçekleşen ve Planlama tablolarındaki seçili proje için en büyük ve en küçük tarihleri bulduk. Bunlarda sırası ile en küçüğünü ve en büyüğünü alacağız. B3 Hücresine Proje Başlangıç Tarihi yazıyorum ve C3 hücresine de yukarıdaki aşağıdaki formülü yazıyorum:
=MİN(ÇOKEĞERMİN(Planlama[Tarih];Planlama[Proje Adı];C2);ÇOKEĞERMİN(Gerçekleşen[Tarih]; Gerçekleşen[Proje Adı];C2))
Aynı şekilde B4 hücresine “Proje Bitişi” yazıyorum ve C4 hücresine de aşağıdaki formülü giriyorum:
=MAK(MİN(ÇOKEĞERMAK(Planlama[Tarih];Planlama[Proje Adı];C2);ÇOKEĞERMAK(Gerçekleşen[Tarih]; Gerçekleşen[Proje Adı];C2))
Eğer elinizde daha eski Excel sürümleri var ise bu durumda ÇOKEĞERMAK-ÇOKEĞERMİN yerine aşağıdaki formülleri kullanabilirsiniz:
=MAK(EĞER((Planlama[Proje Adı]=’Grafik Değerleri’!$C$2);Planlama[Tarih]))
=MİN(EĞER((Planlama[Proje Adı]=’Grafik Değerleri’!$C$2);Planlama[Tarih]))
=MAK(EĞER((Gerçekleşen[Proje Adı]=’Grafik Değerleri’!$C$2);Gerçekleşen[Tarih]))
=MİN(EĞER((Gerçekleşen[Proje Adı]=’Grafik Değerleri’!$C$2);Gerçekleşen[Tarih]))
Sonrasında iki tarih arasındaki toplam ay sayısını bulacağım:
=ETARİHLİ(F1;F2;”M”)+1
Artık elimde seçilen projenin toplam kaç ay olduğunu gösteren bir rakam var. Bu rakamlar toplam satır sayısını belirleyecek. Her iki adımdan sonra elimizde aşağıdaki gibi değerlerin yer aldığı bir aralık oluşacaktır.
d. Her İki Tablodan Verileri Çekme
Bundan sonra yapacağım işlem her iki tablonun verilerini tek bir tabloda birleştirmek. Bunun için önce projenin aylarını satırlara yazmam gerekiyor. E6 hücresine “Tarih” yazyıyorum ve E7 hücresine aşağıdaki formülü giriyorum:
=EĞER(SATIRSAY($E$7:$E7)<=$F$3;SERİAY($F$1;SATIRSAY($E$7:$E7)-1);””)
Bir projenin en fazla 5 yıl-60 ay süreceğini ön görerek bu formülü E67 hücresine kadar çoğaltıyorum. Ben burada artımlı bir grafik oluşturmak istediğim için satırdaki tarihten önceki proje saatlerinin toplamını almak istiyorum. Bu nedenle F6 hücresine “Planlanan” yazarak altına aşağıdaki formülü yazıyorum:
=ÇOKETOPLA(Planlama[Plan];Planlama[Tarih];”<=”&$E7;Planlama[Proje Adı];$C$2)
Bu formülü de F67 hücresine kadar çoğaltıyorum. Aynı formülü Gerçekeleşen değerleri çekmek için de kullanacağım:
=ÇOKETOPLA(Gerçekleşen[Gerçekleşen];Gerçekleşen[Tarih];”<=”&$E7;Gerçekleşen[Proje Adı];$C$2)
Yine bu formülü de G67 hücresine kadar çoğalttığımda artık elimde istediğim değerlerin yer aldığı aşağıdaki tablo oluşmuş olacaktır.
e. Değişken Aralık Tanımlama
Tablom seçilen proje ismine göre değişken sayıda satırdan oluşacak. Bu nedenle ben de bir değişken aralık tanımlamalıyım. Değişken aralık tanımlama için buradaki yazıyı okumanızı tavsiye ediyorum. Değişken aralık için kullanacağım formül:
=KAYDIR(‘Grafik Değerleri’!$E$7;;;BAĞ_DEĞ_SAY(‘Grafik Değerleri’!$E$7:$E$67);3)
Bu formülü “ProjeDegerleri” adlı yeni bir isim oluşturarak Hedef kısmına yazıyorum. Sonrasında ayrı ayrı isimler ile bu tablonun istediğim alanlarını almam gerek. Yine isim tanımlama kısmında “ProjeTarih” adlı bir isim tanımlayarak aşağıdaki formülü hedef kısmına yazıyorum:
=İNDİS(ProjeDegerleri;;1)
Sonrasında ProjePlanlanan diye bir isim tanımlayacağım ve aşağıdaki formülü kullanacağım:
=İNDİS(ProjeDegerleri;;4)
Ve en son olarak ProjeGerçekleşen ismini oluşturacağım ve tablonun son sütununu alacağım:
=İNDİS(ProjeDegerleri;;5)
f. Çubuk Grafiği Oluşturma
Son aşamada grafik oluşturarak tanımladığımız isimleri bu grafikte kullanacağız. Burada kullanacağım grafik oldukça temel bir grafik olacak amaç isimleri grafikte nasıl kullanabileceğimizi göstermek.
Hemen boş bir çizgi grafik ekleyeceğim. Grafiğin yatay ekseninde aylar yer alacak, dikey eksende ise değerler. Sonrasında daha önce oluşturduğum değişken aralığa sahip isimleri bu grafiğe eklemeye çalışacağım, seri değerlerini eklerken F3 tuşuna basarsanız isimlerin listesi geleceğini bir kez daha hatırlatayım:
Gördüğünüz gibi bunu yaparken bir hata alıyoruz. Zaten hata da almamız gerekiyordu. Şimdi biz ufak bir hile ile Excel’i kandıracağız. Grafiğe isim eklerken dosya ismini de kullanacağız:
=GrafiklerdeDegiskenAralik.xlsx!ProjeGerçekleşen
=GrafiklerdeDegiskenAralik.xlsx!ProjePlanlanan
=GrafiklerdeDegiskenAralik.xlsx!ProjeAylar
Ben burada kendi dosyamın adını, tanımladığım ad ile aralarında “!” işareti kullanarak bağladım. Şimdi bu yeni isimleri grafiğe ekleyelim. Seri kısımlarına iki tane seri ekleyeceğim ve bunlar Planlanan ve gerçekleşen alanları olacak. Yatay eksen kısmını da ProjeAYlar ismini atayacağım. Nasıl yapıldığını aşağıdaki videoda izleyebilirsiniz:
Graifiğimizi hazırladık, dikkat ederseniz proje değiştiğinde sadece değerler değil, yatay düzlemde yer alan tarih değerleri de değişiyor. Siz isterseniz değerleri farklı bir sayfaya çekip grafiği farklı bir sayfada oluşturabilirsiniz.