İçerik
2021 yılında farklı ülkelere göre araba satış değerleri ile aşağıdaki tablodaki verileri görselleştirmemiz gerektiğini varsayalım (bu arada gerçek veriler buradan alınmıştır):
Veri serilerinin (ülkelerin) sayısı çok olduğu için hepsini tek bir grafiğe sıkıştırmaya çalışmak ya korkunç bir “spagetti tablosu”na ya da her seri için ayrı grafikler oluşturmaya yol açacaktır ki bu çok zahmetlidir.
Bu soruna zarif bir çözüm, yalnızca geçerli satırdaki, yani etkin hücrenin bulunduğu satırdaki veriler üzerinde bir grafik çizmek olabilir:
Bunu uygulamak çok kolay – 3 satırda sadece iki formüle ve bir küçük makroya ihtiyacınız var.
Adım 1. Geçerli satır numarası
İhtiyacımız olan ilk şey, aktif hücremizin şimdi bulunduğu sayfadaki satır numarasını hesaplayan adlandırılmış bir aralıktır. Bir sekmede açma Formüller – İsim Yöneticisi (Formüller — İsim yöneticisi), düğmeye tıklayın oluşturmak (Oluşturmak) ve aşağıdaki yapıyı oraya girin:
- İsim – değişkenimiz için uygun herhangi bir isim (bizim durumumuzda bu TekString'dir)
- Semt – bundan sonra, oluşturulan adların yerel olması için geçerli sayfayı seçmeniz gerekir
- Aralık – burada işlevi kullanıyoruz HÜCRE (HÜCRE), ihtiyacımız olan satır numarası da dahil olmak üzere belirli bir hücre için bir dizi farklı parametre yayınlayabilen - bundan “satır” argümanı sorumludur.
Adım 2. Başlığa bağlantı
Seçilen ülkeyi grafiğin başlığında ve açıklamasında görüntülemek için, ilk sütundan (ülke) adıyla hücreye bir referans almamız gerekir. Bunu yapmak için başka bir yerel (yani Semt = geçerli sayfa, Kitap değil!) aşağıdaki formüle sahip adlandırılmış bir aralık:
Burada, INDEX işlevi belirli bir aralıktan (imzalayan ülkelerimizin bulunduğu A sütunu) önceden belirlediğimiz satır numarasına sahip bir hücre seçer.
3. Adım. Verilere bağlantı
Şimdi benzer bir şekilde, aktif hücrenin şimdi bulunduğu mevcut satırdaki tüm satış verilerinin bulunduğu bir aralığa bağlantı alalım. Aşağıdaki formülle başka bir adlandırılmış aralık oluşturun:
Burada, sıfır olan üçüncü argüman, INDEX'in tek bir değer değil, sonuç olarak tüm satırı döndürmesine neden olur.
Adım 4. Bağlantıları Grafikte Değiştirme
Şimdi tablo başlığını ve veri (aralık) içeren ilk satırı seçin ve bunları kullanarak bir grafik oluşturun. Ekle - Grafikler (Ekle - Grafikler). Grafikte veri içeren bir satır seçerseniz, fonksiyon formül çubuğunda görüntülenecektir. SIRA (DİZİLER) orijinal verilere ve etiketlere atıfta bulunmak için herhangi bir grafik oluştururken Excel'in otomatik olarak kullandığı özel bir işlevdir:
Bu fonksiyondaki birinci (imza) ve üçüncü (veri) argümanları dikkatli bir şekilde 2. ve 3. adımdaki aralıklarımızın adlarıyla değiştirelim:
Grafik, geçerli satırdan satış verilerini görüntülemeye başlayacaktır.
Adım 5. Yeniden Hesaplama Makrosu
Son dokunuş kalır. Microsoft Excel, formülleri yalnızca sayfadaki veriler değiştiğinde veya bir tuşa basıldığında yeniden hesaplar F9, ve seçim değiştiğinde, yani aktif hücre sayfa boyunca hareket ettiğinde yeniden hesaplamanın gerçekleşmesini istiyoruz. Bunun için çalışma kitabımıza basit bir makro eklememiz gerekiyor.
Veri sayfası sekmesine sağ tıklayın ve komutu seçin Kaynak (Kaynak kodu). Açılan pencerede, seçim değişikliği olayı için makro işleyicinin kodunu girin:
Kolayca tahmin edebileceğiniz gibi, tek yaptığı, aktif hücrenin konumu değiştiğinde bir sayfa yeniden hesaplamayı tetiklemektir.
Adım 6. Mevcut Satırı Vurgulama
Anlaşılır olması için, grafikte o anda görüntülenen ülkeyi vurgulamak için koşullu bir biçimlendirme kuralı da ekleyebilirsiniz. Bunu yapmak için tabloyu seçin ve Ana Sayfa — Koşullu Formatlama — Kural Oluştur — Formatlanacak Hücreleri Belirlemek İçin Formülü Kullanın (Ana Sayfa — Koşullu biçimlendirme — Yeni kural — Hangi hücrelerin biçimlendirileceğini belirlemek için bir formül kullanın):
Burada formül, tablodaki her hücre için satır numarasının TekRow değişkeninde saklanan sayıyla eşleşip eşleşmediğini kontrol eder ve eğer bir eşleşme varsa, seçilen renkle doldurma tetiklenir.
İşte bu – basit ve güzel, değil mi?
notlar
- Büyük tablolarda tüm bu güzellikler yavaşlayabilir - koşullu biçimlendirme kaynak yoğun bir şeydir ve her seçim için yeniden hesaplama yapmak da ağır olabilir.
- Tablonun üstünde veya altında bir hücre yanlışlıkla seçildiğinde verilerin grafikte kaybolmasını önlemek için, formun iç içe EĞER işlevlerini kullanarak TekRow adına ek bir kontrol ekleyebilirsiniz:
=IF(HÜCRE(“satır”)<4,IF(HÜCRE("satır")>4,HÜCRE(“satır”)))
- Bir grafikte belirtilen sütunları vurgulama
- Excel'de etkileşimli bir grafik nasıl oluşturulur
- Koordinat Seçimi