Çözüldü İç içe eğer formülü yerine uygulanacak bir formül

[Ahmet Bey]

Merhabalar değerli hocalarım;
Altta paylaştığım 8.976 karakterden oluşan formül, maksimum formül karakter sınırı olan 8.192'yi geçtiği için, excel hücresine yazılamıyor. Bu uzun formülü, makro kullanmadan, nasıl kısaltıp, doğru sonuca ulaşabiliriz değerli üstadlarım? Acilen yardımınıza muhtacız efendim.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[Miraga]

Merhabalar
8.976 karakterli formül ile ne yapılmaya çalışılmış onu çözmeye çalışmak hem vakit alacaktır hem de hataya sebep olabilecektir. o yüzden yapmak istediğiniz çalışmayı örnek excel dosyası ile açıklarsanız sonuç almanız daha hızlı olacaktır

formülün tamamına bakamadım ancak 1-2 sıra gidince anladığım 16. satır sayıysa ve 6.satır 3 satırdan büyük ise toplasın bu bd sütunu il uj sütunu arasında yapıyor) eğer doğru anlamışsam aşağıdaki dizi formülü deneye bilirsiniz (ctrl + shift + enter)
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[Ahmet Bey]

Merhabalar
8.976 karakterli formül ile ne yapılmaya çalışılmış onu çözmeye çalışmak hem vakit alacaktır hem de hataya sebep olabilecektir. o yüzden yapmak istediğiniz çalışmayı örnek excel dosyası ile açıklarsanız sonuç almanız daha hızlı olacaktır

formülün tamamına bakamadım ancak 1-2 sıra gidince anladığım 16. satır sayıysa ve 6.satır 3 satırdan büyük ise toplasın bu bd sütunu il uj sütunu arasında yapıyor) eğer doğru anlamışsam aşağıdaki dizi formülü deneye bilirsiniz (ctrl + shift + enter)
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Hocam, paylaştığım örnek excel dosyasında olduğu gibi, amacımız tam olarak şöyle:
Sarıyla işaretli olan örnek ilk 5 sütunda olduğu gibi; BD16 sayı ise ve BD16 büyük ise BD$6'dan, bu hücre için sonuç BD$3'deki değer olsun, değilse sonuç 0(sıfır) olsun. Aynı koşullar, 252 adet olan BD16:UJ16 arasına uygulansın ve tek tek hesaplanan bu hücre değeri sonuçlarının hepsi toplansın.

Mesela ilk örnekte 0,1342 değerindeki BD16, bir sayı olduğu için ve BD$6'daki 0,1339'dan büyük olduğu için sonuç: BD$3'deki değer olan 1 olsun.

Fakat burda hesaplama yapılacak olan BD16:UJ16 sütun aralığı, boşluksuz olarak yan yana devam etmiyor yani mesela hesaplama yapılacak olan BD ile BF arasında, hesabı bozmasını istemediğimiz BE sütunu var. Ve aynı şekilde, daha sonra da BG, BI, BK, BM, BO... diye UJ sütununa kadar gidiyor. Yani yazacağımız formülü, bunu da göz önüne alarak yazmamız gerekir ki, hesaplanmaması gereken sütunları da hesaplayıp, yanlış sonuç elde etmeyelim hocam.

Ayrıca ilave olarak, hesaplamak istediğimiz BD16:UJ16 aralığının başına; AJ16 sayı ise ve AJ16 büyük ise AJ$6'dan, bu hücre için sonuç AJ$3'deki değer olsun, değilse sonuç 0(sıfır) olsun ilavesini, mümkünse FİLTRE fonksiyonu ile AH16:AH27 arasındaki yeşil alana yazacağımız yeni formüle ilave edebilirmiyiz değerli hocam. Tüm üstadlarımıza saygılarımı sunarım.

 

[Miraga]

Merhabalar
8.976 karakterli formül ile ne yapılmaya çalışılmış onu çözmeye çalışmak hem vakit alacaktır hem de hataya sebep olabilecektir. o yüzden yapmak istediğiniz çalışmayı örnek excel dosyası ile açıklarsanız sonuç almanız daha hızlı olacaktır

formülün tamamına bakamadım ancak 1-2 sıra gidince anladığım 16. satır sayıysa ve 6.satır 3 satırdan büyük ise toplasın bu bd sütunu il uj sütunu arasında yapıyor) eğer doğru anlamışsam aşağıdaki dizi formülü deneye bilirsiniz (ctrl + shift + enter)
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

formülüğ denedinizmi

 

[Ahmet Bey]

formülüğ denedinizmi

Denedim ve formülde ilave edilmesi gereken eksikleri yazdım hocam, son yazdığım, örnek excel dosyalı, #3 nolu mesajı okudunuzmu?

 

[kralım]

Ben Sayın Ahmet Bey Bey'in yazdığı son paragraf üzerinde çalışmadım, zira ben 365 bilmiyorum. Ancak benim yaptığım çalışmada değerler Sayın Miraga'nın yaptığı çalışmadaki değerlerden farklı çıktı. Doğrusunu isterseniz hangisi doğru bilemedim. Deneyiniz ve dönüş yapınız.

 

[kralım]

Ahmet Bey mesela MP16'dan sonuna kadar ve bazı satırlarda da aynı şekilde sona kadar boş hücreler var. Bu hücreler eğer boş ise yine değer olarak boş mu kalacak 3 ncü hücredeki değer mi gelecek ? Ayrıca UL sütununu hesaba katmamışsınız. Unuttunuz mu ? Bilerek mi hesaba katmadınız ?

 

[atoykan]

Merhabalar değerli hocalarım;
Altta paylaştığım 8.976 karakterden oluşan formül, maksimum formül karakter sınırı olan 8.192'yi geçtiği için, excel hücresine yazılamıyor. Bu uzun formülü, makro kullanmadan, nasıl kısaltıp, doğru sonuca ulaşabiliriz değerli üstadlarım? Acilen yardımınıza muhtacız efendim.

Excelde bırakın 8976 karakterden oluşan bir formül 1500 karakterlik bir formül -üstelik örneğinizdeki kadar çok içiçe formül- teknik olarak yazılabilecek olsa da pratikte yazılmaz. Çalışmanızda 10 satır varken bu kadar karmaşık formülasyon ile sonuçlara ulaşmanız örneğin 1 dakika sürüyor ise 1000 satıra çıktığınızda sonuç alamazsınız zira excel hesaplama motorunun çalışma mantığına göre işlem süresi örneğiniz özelinde yaklaşık 3-4 satırda bir geometrik olarak artacağından 32bit sürümde RAM kapasitesi dolduğundan, 64bit sürümde ise cache belleği yetersizliğinden excel yanıt vermiyor hatası ile kilitlenir. Dizi formülleri, yardımcı sütunlar vb algoritma mantığında izleyici ve tetikleyici alanlar belirlenebilmesi, VBA üzerinden kodlama ile çözümler geliştirilebilmesi gibi yardımcı silahlarınız ağırlıklı olarak bu tip senaryolar ve otomasyon amacı ile geliştirilmiştir.

 

[SNNAY]

Hocam, paylaştığım örnek excel dosyasında olduğu gibi, amacımız tam olarak şöyle:
Sarıyla işaretli olan örnek ilk 5 sütunda olduğu gibi; BD16 sayı ise ve BD16 büyük ise BD$6'dan, bu hücre için sonuç BD$3'deki değer olsun, değilse sonuç 0(sıfır) olsun. Aynı koşullar, 252 adet olan BD16:UJ16 arasına uygulansın ve tek tek hesaplanan bu hücre değeri sonuçlarının hepsi toplansın.

Mesela ilk örnekte 0,1342 değerindeki BD16, bir sayı olduğu için ve BD$6'daki 0,1339'dan büyük olduğu için sonuç: BD$3'deki değer olan 1 olsun.

Fakat burda hesaplama yapılacak olan BD16:UJ16 sütun aralığı, boşluksuz olarak yan yana devam etmiyor yani mesela hesaplama yapılacak olan BD ile BF arasında, hesabı bozmasını istemediğimiz BE sütunu var. Ve aynı şekilde, daha sonra da BG, BI, BK, BM, BO... diye UJ sütununa kadar gidiyor. Yani yazacağımız formülü, bunu da göz önüne alarak yazmamız gerekir ki, hesaplanmaması gereken sütunları da hesaplayıp, yanlış sonuç elde etmeyelim hocam.

Ayrıca ilave olarak, hesaplamak istediğimiz BD16:UJ16 aralığının başına; AJ16 sayı ise ve AJ16 büyük ise AJ$6'dan, bu hücre için sonuç AJ$3'deki değer olsun, değilse sonuç 0(sıfır) olsun ilavesini, mümkünse FİLTRE fonksiyonu ile AH16:AH27 arasındaki yeşil alana yazacağımız yeni formüle ilave edebilirmiyiz değerli hocam. Tüm üstadlarımıza saygılarımı sunarım.

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[Ahmet Bey]

Ahmet Bey mesela MP16'dan sonuna kadar ve bazı satırlarda da aynı şekilde sona kadar boş hücreler var. Bu hücreler eğer boş ise yine değer olarak boş mu kalacak 3 ncü hücredeki değer mi gelecek ? Ayrıca UL sütununu hesaba katmamışsınız. Unuttunuz mu ? Bilerek mi hesaba katmadınız ?

MP16'dan sonraki gibi, tüm boş hücreler, ilk koşulumuz olan; hücre içinde herhangi bir sayının bulunup bulunmaması olduğu için, ve boş olan bu tip hücrelerin hesaplanan sonuçları, kesinlikle hep 0(sıfır) olacak hocam. Ayrıca hesaplama, en son UJ sütununda bittiği için, UL sütunu artık hesaba katılmıyor. Hesaplanmadığı halde, UL sütununun tek varlık sebebi ise, UJ sütunundaki değerlerin, kendisinden 1 önceki işgünü tarih bilgilerine sahip olan, UL sütunundaki bilgilerle hesaplanmasıdır. Yani her sütunun değerleri hesaplanırken, kendinden bir önceki sütuna ihtiyaç olduğu için UL sütunu var yani hocam.

 

[Ahmet Bey]

Ben Sayın Ahmet Bey Bey'in yazdığı son paragraf üzerinde çalışmadım, zira ben 365 bilmiyorum. Ancak benim yaptığım çalışmada değerler Sayın Miraga'nın yaptığı çalışmadaki değerlerden farklı çıktı. Doğrusunu isterseniz hangisi doğru bilemedim. Deneyiniz ve dönüş yapınız.

ayrıca sizin yaptığınız çalışmayı indirip baktığımda, hesaplamayı, her satırın bir alt satırındaki yardımcı satırlar aracılığı ile yapılmaya çalışıldığını gördümki, zaten orjinal dosyadaki, belli bir parça örnek alınarak site gönderdiğimiz ve aşağıya doğru binlerce satır devam eden bu dosyada, böyle yardımcı satırlarla hesaplama yapma gibi bir lüksümüz, maalesef zaten yok hocam. Ayrıca yardımcı alt satırlardaki formüllerde hesaplanan hücreler, $ ile sabitlenmediği için kaymalar olmuş ve doğru sonuçlara da ulaşılamamış, ama sabitleme işlemi yapılırsa, sizin bu çalışmanızla, diğer hocalarımızın çalışmalarının sağlaması yapılabilir diye düşünüyorum şuan yani hocam, Saygılarımla.

 

[kralım]

Ayrıca ilave olarak diye başlayan son paragrafınız için ayrıca çalışmak lazım.
Aşağıdaki şekliyle lütfen Deneyiniz.

 

[Ahmet Bey]

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Hocam elinize sağlık, denedim çalışıyor.

Aynı bu formül formatında yazılmak üzere, bir de şunu yapmak istiyoruz hocam:

Sadece tek koşul olarak; BD16 sayı ise(yani boşluktan ve metinden farklı ise), bu hücre için sonuç BD$3'deki değer olsun, değilse sonuç 0(sıfır) olsun. Ve bu koşullar tabiki, BD16:UJ16 aralığına ve + ilave olarak AJ16 sütununa da uygulanıp, tek tek hesaplanan bu hücre değerlerinin hepsi toplansın.

yani koşulumuzu sadece BD sütunu üzerinden örneklendirirsek böyle hocam: =EĞER(ESAYIYSA(BD16);BD$3;0)

Bundan önce yaptığınız formülün sonucunu, bu yapacağınız formül sonucuna bölerek, oran orantı kurmak için, bu 2 formüle ihtiyaç duyduk değerli hocam. Size ve tüm üstadlarımıza, emekleri için sonsuz teşekkürlerimi sunarım, Saygılarımla.

 

[SNNAY]

Hocam elinize sağlık, denedim çalışıyor.

Aynı bu formül formatında yazılmak üzere, bir de şunu yapmak istiyoruz hocam:

Sadece tek koşul olarak; BD16 sayı ise(yani boşluktan ve metinden farklı ise), bu hücre için sonuç BD$3'deki değer olsun, değilse sonuç 0(sıfır) olsun. Ve bu koşullar tabiki, BD16:UJ16 aralığına ve + ilave olarak AJ16 sütununa da uygulanıp, tek tek hesaplanan bu hücre değerlerinin hepsi toplansın.

yani koşulumuzu sadece BD sütunu üzerinden örneklendirirsek böyle hocam: =EĞER(ESAYIYSA(BD16);BD$3;0)

Bundan önce yaptığınız formülün sonucunu, bu yapacağınız formül sonucuna bölerek, oran orantı kurmak için, bu 2 formüle ihtiyaç duyduk değerli hocam. Size ve tüm üstadlarımıza, emekleri için sonsuz teşekkürlerimi sunarım, Saygılarımla.

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

 

[Ahmet Bey]

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Hocam 2. formüldeki:
=TOPLA(BYCOL(BD16:UJ16;LAMBDA(hücre;EĞER(ESAYIYSA(hücre);İNDİS(BD$3:UJ$3;1;SÜTUN(hücre)-SÜTUN(BD$16)+1);0))))+EĞER(ESAYIYSA(AJ16);AJ$3;0)

$ ile sabitlediğiniz BD'nin 16.satırını, farkında olmadan, yanlışlıklamı sabitlediniz? Yoksa öyle olması gerektiği için, bilinçli olarakmı sabitlediniz? Ben bu fonksiyonları, daha önce hiç kullanmadığım için, formüle çok hakim olamadığımdan, emin olmak için sordum hocam af buyrun.

 

[SNNAY]

BD$16 Satır sayısı sabit sütun adını farklı olmasını sağlar. sağa doğru çoğalttığınız zaman sütun ismi değişir ama satır sayısı sabit kalır. bd ul aralığında BD16 olarak çoğalttığınızda kriterler bd16 be17 bf18 olmasın diye $ ile satır sayısını sabitliyoruz

Hocam 2. formüldeki:
=TOPLA(BYCOL(BD16:UJ16;LAMBDA(hücre;EĞER(ESAYIYSA(hücre);İNDİS(BD$3:UJ$3;1;SÜTUN(hücre)-SÜTUN(BD$16)+1);0))))+EĞER(ESAYIYSA(AJ16);AJ$3;0)

$ ile sabitlediğiniz BD'nin 16.satırını, farkında olmadan, yanlışlıklamı sabitlediniz? Yoksa öyle olması gerektiği için, bilinçli olarakmı sabitlediniz? Ben bu fonksiyonları, daha önce hiç kullanmadığım için, formüle çok hakim olamadığımdan, emin olmak için sordum hocam af buyrun.

 

[Ahmet Bey]

BD$16 Satır sayısı sabit sütun adını farklı olmasını sağlar. sağa doğru çoğalttığınız zaman sütun ismi değişir ama satır sayısı sabit kalır. bd ul aralığında BD16 olarak çoğalttığınızda kriterler bd16 be17 bf18 olmasın diye $ ile satır sayısını sabitliyoruz

hocam ben bu formülü, sağa sola doğru değil, sadece 16.satırdan aşağıya doğru binlerce satıra çoğaltacağım için sormuştum bunu, yani aşağıya doğru kopyalayarak kullandığımızda da, 16.satır sabit olarak kalmalımı diyorsunuz yani hocam?

 

[SNNAY]

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Şeklinde olmalı

 

[Ahmet Bey]

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Şeklinde olmalı

1. Formülde:
=TOPLA(BYCOL(BD16:UJ16;LAMBDA(hücre;EĞER(ESAYIYSA(hücre)*(hücre>İNDİS(BD?6:UJ?6;1;SÜTUN(hücre)-SÜTUN(BD6)+1));İNDİS(BD$3:UJ$3;1;SÜTUN(hücre)-SÜTUN(BD$3)+1);0))))+EĞER(VE(ESAYIYSA(AJ16);AJ16>AJ6);AJ$3;0)
? koyduğum yerler, $ ile sabitlenmeyecekmi hocam? Bunu da yine, bu formülü aşağıya doğru kopyalayacağım için sordum hocam.

 

[Ahmet Bey]

Deneyiniz
HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

HTML:

İçeriği görebilmek için Giriş yap ya da Üye ol.

Şeklinde olmalı

Şimdi şunu da farketttim hocam, 1. Formülde ayrıca:
=TOPLA(BYCOL(BD16:UJ16;LAMBDA(hücre;EĞER(ESAYIYSA(hücre)*(hücre>İNDİS(BD6:UJ6;1;SÜTUN(hücre)-SÜTUN(BD?6)+1));İNDİS(BD$3:UJ$3;1;SÜTUN(hücre)-SÜTUN(BD$3)+1);0))))+EĞER(VE(ESAYIYSA(AJ16);AJ16>AJ6);AJ$3;0)
? koyduğum yer de, $ ile sabitlenmeyecekmi hocam? Dediğim gibi yani bunu da yine, bu formülü aşağıya doğru kopyalayacağım için sordum.