Formülü Son Dolu Hücre ile Sınırlandırmak

[altanson]

Arkadaşlar şöyle bir formülüm var;

=AİÇVERİMORANI('sayfa1'!K4:K104;'sayfa1'!C4:C104)

fakat verileri aldığım tabloda daha az veya daha fazla veri olabiliyor. Eğer veriler 50. satırda bitiyorsa formülü otomatik olarak nasıl değiştirebilirim?

yani son veri 50. satırdaysa formül;

=AİÇVERİMORANI('sayfa1'!K4:K50;'sayfa1'!C4:C50)

olarak otomatik değişmeli.

Teşekkürler.

 

[muygun]

Slm.
Bunu birkaç yolla yapabilirsiniz.
1-C sütunundaki son veri satır sayısı için D sütununa
=EĞER(C1="";"";SATIR())
formülünü uygulayıp aşağı doğru çoğaltın
=BÜYÜK(D : D;1) formülü ile en son satır no'sunu alabilirsiniz. Bunuda formül içinde sabit sayı yerine kullanırsınız. (not:formül arasındaki boşluğu yok sayın)
veya;
="sayfa1'!K4:K"&BAĞ_DEĞ_DOLU_SAY(K:K)&";sayfa1'!C4:C"&BAĞ_DEĞ_DOLU_SAY(C:C)
formülü ile
=AİÇVERİMORANI(DOLAYLI(üstteki formül)) yazılımı ile oluşturulan dolaylı adresi kullanabilirsiniz.

NOT:Ekleyeceğiniz örnek uygulama; sizin soruyu tam anlatmanıza, diğer kişilerin de soru ve cevaptan faydalanmasına yardımcı olacaktır.

 

[altanson]

dediklerinizi dosyama uyguladım fakat sanırım bir yerde hata yapıyorum.

Örnek dosyam ekte, yardımcı olacak arkadaşlara şimdiden teşekkürler.

 

[mehmett]

Sn Altanson,

Aşağıdaki gibi deneyin.

=AİÇVERİMORANI(DOLAYLI("I3:I"&BAĞ_DEĞ_DOLU_SAY(I:I)+2);DOLAYLI("C3:C"&BAĞ_DEĞ_DOLU_SAY(I:I)+2))

 

[Merhum İdris SERDAR]

dediklerinizi dosyama uyguladım fakat sanırım bir yerde hata yapıyorum.

Örnek dosyam ekte, yardımcı olacak arkadaşlara şimdiden teşekkürler.

1. Data/List yöntemini

2. Dinamik alan yöntemini

Kullanabilirsiniz.

Bu konular için Excel Dersanesi'ne bakın.

Data/List yöntemi ile yapılmış dosyanız ekte.

.

 

[altanson]

formülünüzü dosyama uyguladım fakat hata veriyor;

formülüm şu şekilde

=AİÇVERİMORANI(K4:K28;C4:C28)

sizin verdiğiniz formülü şu şekilde değiştirdim;

=AİÇVERİMORANI(DOLAYLI("k4:k"&BAĞ_DEĞ_DOLU_SAY(K:K)+3);DOLAYLI("C4:C"&BAĞ_DEĞ_DOLU_SAY(C:C)+3))

fakat değer hatası veriyor, nasıl düzeltebilirim?

 

[mehmett]

Sn altanson,

Örnek dosyanızda K sütunu boş olduğundan I sütuna göre fonksiyon yazmıştım.

Şimdi örnek dosyanızda K sütununun dolu olması için araya iki boş sütun ekleyerek yeniden gönderiyorum.

 

[altanson]

hatanın nedenini anladım;

son yolladığınız örnekte (ve dolayısıyla benim yolladığım ilk örnek dosyada) "C" ve "K" sutünlarında son veriden sonraki hücreler boş

fakat esas uygulamak istediğim dosyada "K" sütununun tamanında

=EĞER(B10="";"";EĞER(EHATALIYSA(E10-J10);-J10;E10-J10))

gibi bir formülüm var bu yüzden formül esas uygulamak istediğim dosyada hata veriyor.

bu şekilde nasıl düzeltebiliriz? Örnek dosyam Ekte;

 

[mehmett]

Dosyanız ekte.

Bu sefer K sütunundaki formül de değişmek zorunda kaldı.

 

[altanson]

sorumu parça parça soruyorum farkındayım ama esas çalıştığım dosyanın boyutu büyük onun için sorum ile ilgili bölümü ekledim;

"K" sütünundaki formülü dediğiniz gibi düzelttim fakat "C10" hücresinde de

=EĞER(B11="";"";EĞER('Veri Giriş'!$E$12=1;C10+365;EĞER('Veri Giriş'!$E$12=2;C10+184;EĞER('Veri Giriş'!$E$12=4;C10+92))))

formülü var ve "K" sütunundaki gibi aşağıya doğru devam ediyor. Bu formülü nasıl düzeltmeliyim?

 

[Merhum İdris SERDAR]

Sayın altanson,

Eklediğim dosyanızda, ilk başlangıç hücresi ile son bitiş hücresi baz alınarak hesaplama yapıyor.

Bu şekilde bir şey mi istiyorsunuz?

.

 

[altanson]

ilgilenen tüm arkadaşlara teşekkürler fakat yine olmadı, dosyadaki ilgili sayfanın tamamını ekliyorum bu sefer;

 

[Merhum İdris SERDAR]

Arkadaşlar şöyle bir formülüm var;

=AİÇVERİMORANI('sayfa1'!K4:K104;'sayfa1'!C4:C104)

fakat verileri aldığım tabloda daha az veya daha fazla veri olabiliyor. Eğer veriler 50. satırda bitiyorsa formülü otomatik olarak nasıl değiştirebilirim?

yani son veri 50. satırdaysa formül;

=AİÇVERİMORANI('sayfa1'!K4:K50;'sayfa1'!C4:C50)

olarak otomatik değişmeli.

Teşekkürler.

İstediğiniz bu değil mi?

Bir soru soruyorsunuz. Yanıt veriyoruz. Olmadı deyip herhangi bir açıklama yapmıyorsunuz.

Bakın son eklediğim dosya ve aşağıda eklediğim dosya istediğinizi yapıyor.

Neresi olmuyor biraz açarmısınız?

Ben yine açıklamasını yazayım:

=XIRR(K4:K28;C4:C28)

Bu aralığa göre AİÇVERİMORANI'ın hesaplanmasın istiyorsunuz. Ancak formülleriniz K sütununda K204'e kadar gidiyor. Ancak veriler bundan önce bitiyor ve formülünüzün bittiğiyere kadar hesaplama yapmasını istiyorsunuz. YaniC28 ve K28 değişiyor. Ben K4 ve C4'ün de değiştiğini varsayıyorum ve formülü oluşturmaya başlıyorum.

1. Veri K'da nerede ba&#351;l&#305;yor bulmak i&#231;in =CELL("address";INDEX(K1:K204;MATCH(TRUE;K1:K204<>"";0))) form&#252;l&#252;n&#252; CTRL+SHIFT+ENTER ile girersem, $K$4 elde ederim.

2. Verinin K'da nerede bitti&#287;ini bulmak i&#231;in =ADDRESS(MATCH(9,99999999999999E+307;K1:K100);COLUMN(K1)) form&#252;l&#252; ile $K$28 bulunur.

3 . Ayn&#305; y&#246;ntemi C s&#252;tunu i&#231;in de uyguluyorum.

4. Sonu&#231;ta =XIRR(INDIRECT(CELL("address";INDEX(K1:K204;MATCH(TRUE;K1:K204<>"";0)))&":"&ADDRESS(MATCH(9,99999999999999E+307;K1:K100);COLUMN(K1)));INDIRECT(CELL("address";INDEX(C1:C204;MATCH(TRUE;C1:C204<>"";0)))&":"&ADDRESS(MATCH(9,99999999999999E+307;C1:C204);COLUMN(C1)))) form&#252;l&#252;n&#252; CTRI+SHIFT+ENTER girdi&#287;imde;

=XIRR(K4:K28;C4:C28) form&#252;l&#252;n&#252;n&#252;n =XIRR(K'n&#305;n ba&#351;lang&#305;c h&#252;cresi:K'n&#305;n Biti&#351; h&#252;cresi;C'nin ba&#351;lang&#305;&#231; h&#252;cresi:C'nin biti&#351; h&#252;cresi) formunda bir form&#252;l elde edilmi&#351; olur.

Bana g&#246;re olduk&#231;a &#246;nemli bir form&#252;l ortalama, standart sapma gibi. ba&#351;&#305; ve sonu belli olayan aral&#305;klar&#305;n hesaplanmas&#305;nda &#246;nemlilik arzediyor.

&#304;&#351;inize yaramazsa, birinin i&#351;ine yarayaca&#287;&#305; d&#252;&#351;&#252;ncesi ile bu a&#231;&#305;klamalar&#305; yapt&#305;m.

..





.

 

[altanson]

Sn. yurttas,

Ayrıntılı cevabınız için teşekkürler, hepinizi uğraştırdım fakat bu sefer formül yapmak istediğimiz gibi, sorun dosyanın tamamını ekleyemediğimden kaynaklandı.

Şimdi yine ufak bir sorun var fakat. Verilerin başladığı satırdan önceki herhangi bir satıra (3. satıra) eğer yazı ile bir değer girersek ("Tarih" "Toplam" vb.) XIIR formülü metin kabul etmediği için formül hata veriyor. Formülü istediğimiz bir satırdan başlatabilmek için (4. satır mesela) Formülünüzü şu şekilde değiştirdim.

=XIRR(DOLAYLI(HÜCRE("address";İNDİS(K4:K204;KAÇINCI(DOĞRU;K4:K204<>"";0)))&":"&ADRES(KAÇINCI(9,99999999999999E+307;K1:K100);SÜTUN(K1)));DOLAYLI(HÜCRE("address";İNDİS(C4:C204;KAÇINCI(DOĞRU;C4:C204<>"";0)))&":"&ADRES(KAÇINCI(9,99999999999999E+307;C1:C204);SÜTUN(C1))))

Bu şekilde tam istediğim gibi oldu, yardımcı olan herkese çok teşekkürler.