Selamlar,
Bilgi amaçlı olarak, Dinamik Alan ile ilgili, örnek üzerinden
(belki biraz fazla uzun olacak ama) açıklama eklemek isterim.
Dosya ektedir...
Resimde de görüleceği üzere, açıklamayı dosyaya da yazdım.
Örnek olarak, günlük tarih bazında satış elemanlarının yapmış oldukları satış adetleri listesi ele alındı.
İstenilen, açılır listeden isim seçildiğinde seçilen personele ait kayıtların listelenmesi...
Burada,
hem B - C - D kolonlarını içeren liste
önemli ve hem de personel isimlerinin olduğu C kolonu
önemli.
Personel ismine göre listeleme yapılacağından,
C kolonuna göre işlemler yapıp dinamik alanlar belirlenmeli...
** C kolonunda BOŞ hücre - SAYI - METİN ifadeler mevcut.
Not : Gerek Açılır liste referans listesi ve gerekse kayıtların listelenmesi ve gerekse de Açılır liste Excel 365 formülleri ile yapıldı.
Dinamik Alan : Başlangıç hücresinden,
son DOLU satırdaki hücreye kadarki alan olarak tanımlanabilir.
Avantajları
--- Formüllerde kullanıldığında gereksiz hücre (fazladan hesaplanan hücre aralığı) aralıkları hesaplanmaya dahil edilmez ve performanslı hesaplama sağlanır.
---
SATIR ekleme ve çıkarmalarda (Başlangıç hücresinin bulunduğu
SATIR HARİÇ olmak üzere) dinamik alan otomatik olarak hesaplanır. Formüllerde kullanılan
Hücre aralıkları değiştirilmek zorundayken, dinamik alanlarda değişikliğe gerek kalmaz.
Dinamik Alan için
Ad Tanımlamaları yapılmalı... Ad Tanımlamaları için Bak. Formüller >>>
Ad Yöneticisi
1 -
Son DOLU hücreye göre Liste SATIR adedi hesaplanmalı...
Bunun için çeşitli formüller mevcut. Örneğin,
=ARA(2;1/(Sayfa1!$C$7:$C$2000<>"");SATIR(Sayfa1!$C$7:$C$2000))-SATIR(Sayfa1!$C$7)+1
Excel 365 için
=ARA(2;1/(Sayfa1!$C$7:$C$2000<>"");SIRALI(SATIRSAY(Sayfa1!$C$7:$C$2000)))
ya da
=KAÇINCI(2;1/(Sayfa1!$C$7:$C$2000<>""))
ya da
=MAK(EĞER(Sayfa1!$C$7:$C$2000<>"";SIRALI(SATIRSAY(Sayfa1!$C$7:$C$2000))))
MAK(
EĞER ile
KAÇINCI formülleri
DİZİ formüllerdir.
ARA ise standart Normal formüldür (ama DİZİ formül mantığında çalışır)
Fazla miktarda SATIR içeren listelerde kullanılması yavaşlamalara neden olabilir. Yine de, belirtmekte fayda var, bu formüllerde de C : C gibi komple sütunu
değerlendirmemek gerekir.
Ben genelde, eklenen dosyada olduğu gibi
=MAK(EĞERHATA(KAÇINCI(9^99;Sayfa1!$C$7:$C$1000000);1);EĞERHATA(KAÇINCI("zzzzz";Sayfa1!$C$7:$C$1000000);1))
formülünü tercih ediyorum...
*** Standart Normal formüldür. Değerlendirme aralığı olarak C : C gibi komple alan yazılabilir. Ya da örnekte olduğu gibi 1.000.000 satıra kadar rahatlıkla değerlendirilebilir.
Burada
KAÇINCI(9^99;Sayfa1!$C$7:$C$1000000) : formülüyle Sayfa1!$C$7:$C$1000000 aralığında
SAYI olanların en sonuncusunun (9^99 ile en sonuncusuna bakılıyor) kaçıncı olduğu bulunmakta...
EĞER listede SAYI Yok ise HATA verecektir. O nedenle de
EĞERHATA ile 1 hesaplanır.
KAÇINCI("zzzzz";Sayfa1!$C$7:$C$1000000) : formülüyle Sayfa1!$C$7:$C$1000000 aralığında METİN olanların en sonuncusunun (zzzzz ile en sonuncusuna bakılıyor) kaçıncı olduğu bulunmakta... EĞER listede METİN Yok ise HATA verecektir. O nedenle de
EĞERHATA ile 1 hesaplanır.
Ve her ikisinin Maksimumu hesaplanarak
Liste SATIR adedi hesaplanır.
Burada dikkat edilecek noktalar :
a - Liste her zaman 1. satırdan başlamaz. Örnekte olduğu gibi 7. satırdan itibaren kayıtlar başlamakta.
(Genelde, A1 : A10000 gibi aralıkla hesaplama yapılır ve Satır Numarası bulunur)
b - Formüllerle bulunacak değer, Satır Numarası değildir. Listedeki Satır Adedidir.
(Çünkü bu değeri direkt olarak kullanıp Dinamik Alan oluşturacağız)
Örnek dosyada bu son DOLU Satıra göre Liste SATIR adedi
n ad tanımlamasıyla yapıldı.
2 - Dinamik Alan bu
n ad tanımlamasıyla beraber yapılabilir artık.
Örnek dosyada
Alan ad tanımlamasıyla yapıldı.
=KAYDIR(Sayfa1!$B$7;;;n;3)
Başlangıç hücresi :
Sayfa!$B$7
SATIR adedi :
n
Sütun Adedi :
3
Bu durumda dinamik alanı bulacaksak
n = 16 (B7 den itibaren son dolu hücre olan B22 hücresine kadar 16 adet SATIR ve 3 adet KOLON)
Dinamik Alan =
Alan =
Sayfa1!$C$7:$E$22
olacaktır.
Aynı zamanda Satış elemanları kolonu değerlendirilecek kolon olduğundan ikinci bir Dinamik Alan olarak
Pers ad tanımlaması yapıldı.
=KAYDIR(Sayfa1!$C$7;;;n;1)
Başlangıç hücresi :
Sayfa!$C$7
SATIR adedi :
n
Sütun Adedi :
1
Bu durumda dinamik alanı bulacaksak
n = 16 (C7 den itibaren son dolu hücre olan C22 hücresine kadar 16 adet SATIR ve 1 adet KOLON)
Dinamik Alan =
Pers =
Sayfa1!$C$7:$C$22
olacaktır.
*** Listeye satır ekleme / çıkarmalarda, n ile bulunan liste SATIR adedi otomatik olarak değişecek ve Dinamik alanlar da otomatik olarak hesaplanacaktır.
Dinamik alanlar için
KAYDIR işlevini örnek olarak verdim..
İNDİS işlevi ile de yapılabilir...
Dosyada bu
İNDİS tanımlamaları da yaptım ve de formüllerde bu tanımlamaları kullandım.
AlanA =Sayfa1!$B$7:İNDİS(Sayfa1!$D$7:$D$1000000;n)
PersA =Sayfa1!$C$7:İNDİS(Sayfa1!$C$7:$C$1000000;n)
İNDİS işlevinin avantajı,
KAYDIR işlevi gibi
Volatile (Oynak, uçucu) olmayışıdır. Yani, volatile demek, herhangi bir hücrede değişiklik yapıldığında , veri girildiğinde, veri silindiğinde... vs sayfa yeni baştan hesaplanacaktır. demektir. Bu da performans kaybıdır. Ancak yine de kullanıcının seçimidir...
Not :
Formüller >>> Ad Yöneticisinde ad tanımlaması yapmak...
1 - Ad Yöneticisinde Yeni tıklayın
2 - Tanımlanacak Ad yazın
(Bunun için bazı kurallar mevcut)
3 - Başvuru yerine Formülü yazın.
(Burada dikkat edilecek nokta, Hücre Adreslerinin önünde MUTLAKA Sayfa isimleri bulunmalıdır. Dikkat edilirse, formüllerde, $C$7:$C$1000000 yerine daima Sayfa1!$C$7:$C$1000000 şeklinde yazdım)
Formüller >>> Ad Yöneticisine gelindiğinde tüm tanımlanmış adlar listelenir.
Resimde de görüleceği üzere, bu adlardan herhangi bir Dinamik Alan seçtiğinizde ve Başvuru yeri yazan kısımdaki formüle mouse ile tıkladığınızda, sayfadaki tanımlanmış alan kesik çizgilerle belirtilerek gösterilecektir.
ve bu dinamik alan tanımlamalarını formüllerde kullandık.
** Açılır liste Referans Listesi için L6 hücresinde
=
SIRALA(
BENZERSİZ(
FİLTRE(
PersA ; (
PersA<>""))))
**
Veri Doğrulama Açılır Liste için Formül çubuğuna
=
$L$6#
** Seçilen Personele ait kayıtlar için H6 hücresine
=
SIRALA(
FİLTRE(
AlanA ; (I2<>"")*(
PersA =I2);""))
