Excel'de veriyle birlikte büyüyen dinamik grafik nasıl kurulur?

Dinamik grafik ne demek, hangi sorunu çözer?

Sıradan bir grafik, oluşturulduğu anda seçili olan hücre aralığını gösterir. Yeni bir ay eklediğinizde grafik o aya kendiliğinden uzanmaz; veri kaynağını elle genişletmeniz gerekir. Aylık rapor hazırlayan biri için bu, her dönem başında tekrarlanan sıkıcı ve unutulabilen bir iştir.

Dinamik grafik bu işi ortadan kaldırır. Burada farklı iki ihtiyaç ortaya çıkar: veri eklendikçe kendiliğinden büyüyen grafik ve kullanıcının bir açılır listeden seçtiği kategoriye göre değişen etkileşimli grafik. Birincisi tablo ya da tanımlı adla, ikincisi veri doğrulama listesi ve yardımcı alanla çözülür.

Temel mantık hepsinde aynıdır: grafik sabit hücre adresine değil, değişebilen bir kaynağa bağlanır.

Yöntem bir: Excel tablosundan grafik kurmak

En basit yol veriyi bir Excel tablosuna dönüştürmektir. Verinin içinde bir hücre seçin ve Giriş sekmesindeki tablo olarak biçimlendirme komutunu kullanın; açılan pencerede başlık satırı kutusunu işaretleyin. Microsoft'un anlatımına göre tablonun hemen altındaki boş hücreye yazarsanız tablo yeni satırı içine alır; yapıştırma durumunda da tablo yapıştırılan veriyi kapsar. Yapıştırılan verinin sütun sayısı tablonunkinden fazlaysa fazla sütunlar tabloya katılmaz ve tabloyu elle yeniden boyutlandırmanız gerekir.

Sonra grafiği tablodan oluşturun: tablonun içinden seçim yapıp grafik ekleyin. Grafiğin kaynağı tablo olduğunda, tablo genişledikçe grafiğin yeni satırı da göstermesi beklenir. Yine de bunu kendi dosyanızda deneyin: bir satır ekleyip grafiğin yeni noktayı gösterdiğini doğrulayın. Göstermiyorsa veri kaynağı tabloyu değil sabit bir aralığı gösteriyor demektir.

Bu yöntemin avantajı bakım gerektirmemesi, dezavantajı yalnızca ekleme senaryosunu çözmesidir: listeden seçime göre değişen davranış için ayrı bir kurulum gerekir.

Yöntem iki: tanımlı ad ve OFFSET ile

Tablo kullanamıyorsanız ya da daha fazla denetim istiyorsanız tanımlı ad yöntemi vardır. Microsoft'un yöntem anlatımı şöyle işler: Formüller sekmesindeki ad tanımlama komutuyla iki ad oluşturulur, biri dönem etiketleri, diğeri değerler için. Her adın başvuru formülü OFFSET ile ilk veri hücresinden başlar, kaydırma sıfırdır ve yükseklik, sütundaki dolu hücre sayısından başlık satırı çıkarılarak bulunur. Böylece aralık, sütuna veri girildikçe uzar.

Sonra grafik normal şekilde eklenir ve grafiğin veri kaynağını düzenleme iletişim kutusunda seri değerleri ile yatay eksen etiketleri aralık adresi yerine ad ile değiştirilir; biçim, sayfa adı, ünlem ve tanımlı addır. Dolu hücre sayımında metin etiketler için COUNTA, yalnızca sayısal değerler için COUNT kullanılır.

Bu yöntemde dikkat edilecek noktalar vardır. Sayım tüm sütun üzerinden yapıldığı için sütunda başka veri olmamalıdır. Arada boş hücre kalırsa dolu hücre sayısı gerçek satır sayısından küçük çıkar ve son nokta grafikte görünmez. Adları ve formülleri dosyada bir açıklama sayfasında belgelemek, bir başkası dosyayı devraldığında mantığın kaybolmasını önler.

Örnek senaryo: bir mağaza zincirinde haftalık ciro tablosu var. Dönem etiketleri bir sütunda, ciro değerleri yanındaki sütunda duruyor. Ekip dönem ve ciro için birer ad tanımlıyor, grafiği ciro sütunundan kuruyor ve serileri bu adlara bağlıyor. Her hafta tablonun altına yeni satır eklendikçe grafik yeni haftayı gösteriyor; raporu hazırlayan kişi grafiğe hiç dokunmuyor.

Yöntem üç: açılır listeyle değişen grafik

Etkileşimli grafik için şu parçalar gerekir: bir seçim hücresi, bir yardımcı alan ve yardımcı alandan beslenen grafik. Seçim hücresine veri doğrulama listesi eklenir; kullanıcı buradan bir ürün, bölge ya da kategori seçer. Yardımcı alan, seçilen değere göre kaynak tablodan ilgili değerleri çeken formüllerden oluşur. Grafik ham tabloya değil yardımcı alana bağlanır.

Seçim değişince yardımcı alan yeniden hesaplanır, grafik de yeni değerleri gösterir. Bu kurulumda ilk adım veri doğrulama listesidir; bağımlı listeler gerekiyorsa bir listenin seçimi ikincisinin içeriğini belirler.

Örnek senaryo: bir satış yöneticisi bölge bazında aylık satışları ayrı ayrı grafiklemek istemiyor. Tek bir grafik ve bir bölge listesi kuruyor. Toplantıda bölge adı seçildikçe grafik o bölgenin serisini gösteriyor. Bölge sayısı kadar grafik yerine tek bir kontrol kullanılıyor.

Yardımcı alanı grafik kaynağıyla birlikte ayrı bir sayfada tutmak, ana tablonun okunurluğunu bozmaz. Seçim listesinin kaynağı da dinamik olursa yeni bir bölge ya da ürün eklendiğinde listeye de kendiliğinden girer; bu, tablo yöntemiyle birleştirilebilecek en pratik tamamlayıcıdır.

Sık yapılan hatalar ve doğruları

Dinamik grafiklerde sorun çoğunlukla kaynağın gerçekten dinamik olmamasından doğar.

• Veri kaynağını elle genişletmeye devam etmek; her yeni dönemde aynı işi tekrarlamak ve unutmak.
• Tablodan grafik kurmuş saymak, ama grafiğin sabit bir aralığa bağlı kalması; satır ekleyerek test edilmelidir.
• Tanımlı ad kullanıp sütunda başka veri bırakmak; sayım bozulur.
• Veri arasında boş hücre bırakmak; son nokta kaybolabilir.
• Açılır liste kurup grafiği yardımcı alana değil ham tabloya bağlamak.

Bu beceriyi Mindset programlarında nerede çalışırsınız?

Dinamik grafik, veri içeren grafikler ile raporlama otomasyonunun kesiştiği bir beceridir. Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı), veri içeren grafikler ve grafik formatlama ile veri tabloları ve PivotTabloları başlıklarını birlikte işlediği için tekrarlayan raporlarını otomatikleştirmek isteyen analistler ve yöneticiler için doğrudan ilgili bir zemin sunar.

Excel Orta Düzey ise grafik ve görsel analiz ile veri doğrulama ve koşullu biçimlendirme başlıklarını içerir; açılır listeli, okunur raporlar hazırlamak isteyen ofis çalışanları için tamamlayıcıdır.