Ana sekmenin son satırında yer alan veriyi neden çekemiyorum?

[A_day]

İndir Eksik Satir

Dosyayı indir Eksik Satir

dosya.co

Eklemiş olduğum dosya, aslında 2000 satırlı olan dosyamın benzeri.

Yan sekmelerde, ana sayfadan veriler çektiğim için dosyam anormal yavaşladı. Bunun üzerine ana sayfama dinamik ad tanımlaması yaparak, yan sekmelerdeki formüllerimi revize etme işine kalkıştığımda, "Kasa - Arama" sekmesinde şöyle bir sorunla karşılaştım;

"Kasa - Arama" sekmesinde [ B ] sütunundaki formüller düzgün çalışıyor ve "Kasa" ana sekmesinden, [B2] hücresindeki kelimenin geçtiği tüm satır nolarını düzgünce getiriyor. (3 adet veri)

Fakat "Aranılan Kelime" yani "Ali" eğer, örnekte olduğu gibi "Kasa" sekmesinde son satırdaysa, [C7:G7] aralığındaki formüller, "Kasa" sekmesinden veri çekemiyorlar.

Örneğin, "Kasa" sekmesindeki 13 nolu satırı, yani "Ali"nin avans aldığı son satırı silersem ve "Ali" "Kasa" sekmesinde son satırda yer almazsa, bu durumda "Kasa - Arama" sekmesindeki formüller doğru çalışıyor ve bana 2 adet "Ali" verisi çekiyor.

"Ali" ana sekmenin son satırında olduğunda [C7:G7] aralığındaki formüllerin neden veri çekemediklerini saatlerdir uğraşıyorum ama bulamadım.

Neyi yanlış yapıyorum acaba?

 

[Ömer]

Merhaba,

=KAYDIR(Kasa!$B$5;1;;MAK(Kasa!$B:$B);7)

kkayit tanımındaki yukarıdaki formüle mak dan sonra + 1 ilave ederek deneyiniz.

=KAYDIR(Kasa!$B$5;1;;MAK(Kasa!$B:$B)+1;7) gibi.

 

[A_day]

Ömer bey harikasınız. İşe yaradı... Teşekkürler ediyorum...

 

[A_day]

Ömer bey, yan sekmelerin formüllerinde yapmış olduğum revizyon sonrası asıl dosyamda, eskiye nazaran bayağı bir hızlanma oldu ama hücreye bilgi girip sağa okla yana ilerlediğimde yine de şöyle 1 - 2 saniyelik bir gecikme olmakta. Formüllerde daha başka ne tip iyileştirmeler yapılabilir, uzman gözüyle bir öneriniz olabilir mi?

 

[Korhan Ayhan]

Merhaba,

Formül yükü olan dosyalarda hızlı çalışmak için FORMÜLLER sekmesinden HESAPLAMA yöntemini MANUEL (ELLE) olacak şekilde ayarlayabilirsiniz.

Böylece işlemlerinizi çok hızlı şekilde gerçekleştirebilirsiniz. Sonuçları görmek istediğinizde ise F9 tuşuna basarak excelin hesaplama işlemini yapmasını sağlayabilirsiniz.

Ayrıca formüllerde genellikle pratik olduğu için A:A şeklinde aralıklar kullanılmaktadır. Bu aralıkları daraltarak excelin hesaplama yükünü azaltabilirsiniz. Gerekiyorsa dinamik alan dediğimiz türde ad tanımlaları kullanarak dosyanızı daha verimli hale getirebilirsiniz.

Linkleri inceleyebilirsiniz. (Altın Üyelik gerekebilir)

Formüllerde yapılan A:A, gibi alan tanım yanlışlığı

. Değerli Forum katılımcıları, İyi akşamlar... Bugüne kadar bazı formül yazımlarında gördüğüm bir hatadan söz etmek istiyorum. Yazım kolaylığı açısından B:B, C:F gibi gösterimlerde bulunuyoruz. Bu yanlış bir gösterimdir. B:B derken Excel 2003’te 65 bin küsür, Excel 2010’da 1 milyon 40 bin...

www.excel.web.tr

https://www.excel.web.tr/threads/dynamic-range-dinamik-alan.26374/

 

[A_day]

Korhan bey teşekkür ediyorum ilginize, sağolun, gözden geçireceğim formülleri..

 

[A_day]

Korhan bey, dosyamı gözden geçirdim ve A:A, C:F tarzı tanımlamaların yapıldığı formüller buldum. Ekli dosyadaki, "Kasa" sekmesinin J ve K sütunu komple bu tarz formüller içeriyor. Sanırım dosyanın 1 - 2 saniye de olsa kasılmasına sebep olan formüller bunlar. Yalnız bu "dinamik ad tanımlama" konusunda çok acemiyim ve

=EĞER(C7<>C8,ETOPLA(C:C,C7,F:F),0)

formulünü bu durumdan nasıl kurtaracağımı tam olarak bilmiyorum. Hem C:C için hem de F:F için 2 ayrı "dinamik ad tanımlaması" mı yapmam lazım?

 

[Korhan Ayhan]

Aslında Kasa isimli sayfanızı Ekle-Tablo menüsünü kullanarak tabloya dönüştürme şansınız varsa bahsettiğiniz formülü yeniden yazdığınızda zaten formül dinamik olarak çalışacaktır.

 

[A_day]

Korhan bey maalesef tablomu bu şekilde kullanmam gerekiyor. Bu durumda;

=EĞER(C7<>C8,ETOPLA(C:C,C7,F:F),0)

formülündeki C:C ve F:F için apayrı, yeni dinamik ad tanımlamalarımı yapmam lazım, yoksa zaten "kkayit" isimli bir dinamik ad tanımım var. Bunu kullanarak da formülleri revize etmek mümkün mü?

Dinamik ad tanımlama konusunun çok acemisiyim, sormuş olduğum şey belki de çok saçma, kusuruma bakmayın...

 

[Ömer]

ETOPLA(C:C,C7,F:F) yerine aşağıdaki gibi kullanabilirsiniz.

ETOPLA(İNDİS(kkayit;;2);C7;İNDİS(kkayit;;5))

İNDİS(kkayit;;2) buradaki 2, B:H aralığındaki 2. sütun yani C sütunudur, 5 ise F sütunudur. Diğer formülleri bu mantıkla düzenleyiniz.

.

 

[Korhan Ayhan]

Saçma olarak düşünmeyin lütfen.

Bizler sizlere en kolay yöntemleri önermeye gayret ediyoruz.

Varolan dinamik alan tanımlamanızda Ömer beyin önerisindeki gibi İNDİS (INDEX) formülü ile sonuca gidebilirsiniz.

 

[A_day]

Ömer bey, Korhan bey hallettim, çok teşekkür ediyorum, sağolun yardım ve ilgileriniz için...

 

[Korhan Ayhan]

Peki bu işlem sonrasında excel çalışmanızın performansı iyileşti mi?

 

[A_day]

Korhan bey, dün yapmış olduğum revizyondan önce, bir hücreye bir şey yazıp, sağ okla yan hücreye geçmek istediğimde, abartısız 7 - 8 saniye bekliyordum. Dün yardımlarınızla yapmış olduğum iyileştirme sonrası bu kasılma 1-2 sn civarına kadar düşmüştü.

Bugün yine yardımlarınızla yapmış olduğum düzeltmelerin onucunu çok hissedemedim ama illaki faydası olmuştur çünkü 2 sütunda C:C, F: F tarzı tanımlamalar vardı, şimdi bunlar ortadan kalktı.

Tekrar, ilginize ve yardımlarınıza teşekkürler ediyorum...

 

[Ömer]

Birkaç salise daha hızlandırmak için.

Kasa-Arama sayası kayıt no sütununda kullandığınız formülü bu veri yapısına göre aşağıdaki gibi kullanabilirsiniz.

Dizi formülüdür.

=EĞERHATA(KÜÇÜK(EĞER(İNDİS(kkayit;;3)=B$2;İNDİS(kkayit;;1));SATIRSAY(B$5:B5));"")

 

[A_day]

Ömer bey, dosyam iş yerinde yarın deneyeceğim, teşekkürler ediyorum ilginize, yardımlarınıza...

 

[A_day]

Ömer bey, "Kayıt No" sütunu için vermiş olduğunuz formülleri dosyama uyarladım ama şöyle bir sorun çıktı;

önceki formülüm, B2 hücresine yazdığım kelime için, içinde o kelime geçen kayıtları getiriyordu.

Sizin yeni formülünüz ise, moda mod aynı sözcükleri içeren hücreleri bulup getiriyor, içinde geçenleri getirmiyor. İçinde geçen kelimeleri bulup getiren halinde revize edebilirseniz, çok makbule geçecek...

 

[Ömer]

Dizi formülüdür.

=EĞERHATA(KÜÇÜK(EĞER(ESAYIYSA(MBUL(B$2;İNDİS(kkayit;;3)));İNDİS(kkayit;;1));SATIRSAY(B$5:B5));"")

 

[A_day]

Ömer bey, eksik olmayın, harikasınız, gönül dolusu teşekkürler...

 

[A_day]

Korhan bey, son haliyle hissedilir derecede hızlandı. Artık bir hücreden, sağ okla yan hücreye geçiş yaparken, sadece şöyle bir anlık takılır gibi yapıyor ama ardından geçiyor. Eski haliyle şimdiki arasında dağlar kadar fark var.

Hepinize yardımlarınız ve sabrınız için teşekkürler ediyorum, eksik olmayın...