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.

Excel Tablosu

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:

Excel Tablo Yapısı

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.

Bölmeleri Dondurma isimli videomuzdan bu özelliğin kullanımını da izleyebilirsiniz.

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:

Dinamik Formüller ile Dinamik Tablolar Oluşturma

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.

Liste Sayfası

Ü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.

Veri Doğrulama

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.

Dinamik Formüller ile Dinamik Tablolar Oluşturma

Ü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:

Dinamik Formüller ile Dinamik Tablolar Oluşturma

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:

=SIRALAÖLÇÜT(H6#:H5;G6#:G5;1)

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

Dinamik Formüller ile Dinamik Tablolar Oluşturma

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:

Veri Bulunamadı

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.

Taşma Hatası

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.

Dosyada sonuçların elde edilmesi için Yardımcı Sütun kullanılmıştır. Dosyanın istediğimiz şekilde çalışmasını sağlayan tüm Excel özelliklerini de aşağıdaki listede bulabilirsiniz.
Sayın ziyaretçi,

Dinamik Formüller ile Dinamik Tablolar Oluşturma isimli dosyayı indirebilmek için, önce Kayıt olmanız ya da kayıtlı üyemiz iseniz Giriş yapmanız, daha sonra ise VIP Üyelik satın almanız gerekmektedir. VIP Üyelik ile sitemizdeki dosyaları tek tıklamayla indirebilirsiniz.

Dinamik Formüller ile Dinamik Tablolar Oluşturma isimli dosyayı üye olmadan indirmek isterseniz, aşağıdaki butona tıklayıp, 1-TL ödemeniz yeterli olacaktır. Online olarak ödemenin alınmasına istinaden, 8 saat geçerli indirme linkine yönlendirileceksiniz.   ÜYE OLMADAN SATIN AL
1   )
Üstteki alanda, Google Reklamları yerine sizin reklamınızın yayınlanmasını ister misiniz?
Reklam vermek istiyorum

Daha önce yorum yapılmamış!

Excel'de bir liste oluşturduğumuz zaman, verilerin bulunduğu en sağdaki hücreye gitmenin en kolay yolu; Ctrl + › (Sağ Ok) tuşudur.

Fareyi kullanarak en sağdaki hücreye gitmek için, listenizde herhangi bir hücre seçin.

Seçmiş olduğunuz hücrenin sağ sınır çizgisine çift tıklayın.

Bu şekilde, listenin en sağına hızlı bir şekilde gitmiş olursunuz.

Tekrar en sola dönmek isterseniz, Ctrl + ‹ (Sol Ok) tuşlarına basmanız, aynı işlemi fare ile yapmak isterseniz ise, seçili hücrenin sol sınır çizgisine çift tıklamanız yeterli olacaktır.

ipucu18
Yükleniyor...