Dinamik Formüller ile Dinamik Tablolar Oluşturma

Dinamik Formüller ile Dinamik Tablolar Oluşturma isimli dosyamızda, Özet Tabloya gerek duymadan formüller ile dinamik tablo yapılması öğretilmektedir.

Öncelikle belirtmek gerekirki, makale tadında olacak bu dosyadaki formüllerin çalışması için Ofis 365 kullanıcısı olmak gerekir. Hazırsak başlayalım.

Dinamik Formüller ile Dinamik Tablolar Oluşturma

Excel'i çok daha efektif olarak kullanmak için, verilerimizi bir Tablo biçiminde oluşturmanın faydalarından bahsetmiştik. Bu dosyamızda da verilerimizi bir tablo yapısına inşaa ettiğimizi belirtmek isterim.

Excel tablolarının bir özelliğide, başlık olarak belirlenen alanların aşağı doğru inildiğinde otomatik olarak Excel Sütunlarının harflerinin yerini almasıdır.



Dikkat ederseniz 1. satırımız başlık satırı ve arka planı renklendirilmiş ve koyu biçimde yazılmış. Biraz aşağıya doğru indiğimde oluşan görüntü aşağıdaki gibi olacaktır:



Gördüğünüz üzere başlık satırımız sütunlardaki harflerin yerini aldı. Yani Bölmeleri Dondurma işlemi yapmamıza gerek kalmadan ne kadar aşağı gidersek gidelim ilk satırımız hep görünmektedir.


Bu nedenle verilerinizin olduğu aralıkları mutlaka bir tablo biçiminize dönüştürmenizi tavsiye ederim.

Üstteki gibi gördüğünüz bir veri tablomuzdan, farklı bir sayfada dinamik yapıda raporlar almak istiyoruz. Düşündüğümüz yapı, hem Müşteri Adı hem de Ürün Adı şeklinde iki açılır liste ile istediğimiz kriterleri seçtikten sonra, o kriterlere ait verilerin sayfaya formüller ile listelenmesi şeklinde olacaktır. Yani aşağıdaki gibi:



Evet şimdi adımlarımıza geçelim.

Üst kısımda görülen Müşteri Adı ve Ürün Adı kısımlarında isim seçimi yapmak için en ideal yöntem, Veri Doğrulama özelliği ile tablomuzdaki değerleri almak olacaktır. Yeni nesil Excel Dinamik Dizi Formülleri ile Veri Doğrulamanın liste özelliğine veri almakta oldukça kolay bir hale gelmiştir. Biz bu dinamik alanları kolay kullanmak için, bir başka sayfada SIRALA ve BENZERSİZ formüllerini birlikte kullanarak aşağıdaki gibi bir liste yaptık.



Üstteki sayfada da oluşturulan listeler dinamik bir yapıda olduğu için, veri tablomuza yeni bir veri eklense bile otomatik olarak alta eklenecektir. Yani formülü aşağı doğru çoğaltayım, azaltayım gibi bir derdimiz yoktur.

Gerekli tanımlamaları yaptıktan sonra, Veri Doğrulama'nın Ayarlar sekmesindeki Liste özellğine aşağıdaki gibi basit bir tanımlama yapıyoruz.



Liste alanında gördüğünüz =listeler!$B$2#şeklindeki yazı, B2'den itibaren aşağıya doğru ne kadar veri varsa, otomatik olarak açılan kutuya eklemesini sağlamaktadır. Buradaki sihir, # işaretindedir.

Artık verilerimizi de açılan listeye eklediğimize göre, sıra işin raporlama kısmına gelmiş oldu.

Üst resimde kafamızdaki tasarımın nasıl olduğunu aktarmıştık. Her iki açılan kutudan ya da tek tek seçimlerimizi yaptıktan sonra, seçtiğimiz kritere ait verilerin listelenmesini istemiştik.

Normalde eski nesil Excel formülleri ile de aynı işlemler yapılır ama özellikle verilerin otomatik olarak doldurulma kısmı için tüm hücrelere formül yazmak gerekmektedir. Dinamik Dizi formüllerinde ise buna ihtiyaç duymayız.

Oldukça faydalı olduğunu düşündüğümüz bu dosyanın çalışma yöntemine dair görüntüler eşliğinde açıklamalarımıza devam edelim.



Üstte bir müşteri adı seçtim ve gördüğünüz gibi o müşteriye ait satış hareketleri listelendi. Burada dikkat ederseniz, en alt satırda da Toplam satırı yer almaktadır. Bu işlem için herhangi özel bir işlem yapmadığımı belirtmek istiyorum. Yani veriyi ne şekilde listelersem listeleyim, o satır en alt satıra gelecek, yazı koyu olaca ve arka planı sarımsı bir renkte olacaktır. Hemen test edelim:



Burada bir sihir yok, sadece tek bir formülle ve Koşullu Biçimlendirme ile bu sonucu alıyorum. Formülüm ise şu şekilde:


Müşteri Adını silip, sadece Ürün Adını bıraktığımda, sonuç aşağıdaki gibi olacaktır:



Dosyamızı indirdikten sonra bu olasılıkların tamamını deneyebilirsiniz. Son olarak hatalı bir seçim olması halinde oluşacak görüntüyü de gösterelim:



Dikkat ederseniz sonuçların olduğu bir seçimde tüm bilgiler dolmasına rağmen, bir sonuç yer almayan veri seçiminden sonra sadece ilk satırda "Veri Bulunamadı" mesajı çıktı. İşte bu özellik yeni nesil Excel formüllerinin hayatımıza kattığı en büyük yeniliktir. Burada dikkat edilmesi gereken şey, Dökülme Aralığı ya da Taşma Aralığı dediğimiz bu alanda bir başka verinin olmaması olacaktır. Eğer böyle bir durum olursa, Excel Taşma hatası verecektir.



Hataya sebep olan şey, Müşteri başlığının altındaki 1 rakamıdır. Formül yapısı gereği TAŞMA davranışı gösteremediği için hata #TAŞMA! hatası vermektedir.

Hatanın çözümüne dair #TAŞMA! hatası nasıl düzeltilir isimli makalemize bir göz atabilirsiniz.

Bu makale tadındaki dosyamızın, Ofis 365 versiyonuna sahip Excel kullanıcıları tarafından mutlaka incelenmesini öneririz. Dosyamızdaki yöntemleri kendi çalışmalarınıza uygulayarak, kolayca dinamik tablolara sahip olabilirsiniz.