Tabloda Dağınık Olarak Bulunan Verileri Dinamik Olarak Tek Bir Listede Toplama

[fune1991]

Merhabalar,

Excel sayfasında bulunan bir tabloda verilerim bulunuyor. Ancak tablomun her hücresi dolu değil. Tabloda ki dolu hücreleri tek bir sütunda toplamak istiyorum. Makro kullanmadan bunu yapmam mümkün müdür acaba? Örnek dosya ekliyorum. Yardımlarınız için şimdiden teşekkürler.

Örnek Dosya

 

[çıtır]

Çözümlenmiş dosyanız ektedir.
https://www.dosyaupload.com/qySu

 

[fune1991]

Çözümlenmiş dosyanız ektedir.
https://www.dosyaupload.com/qySu

Çok teşekkür ediyorum. Kullandığınız yapıyı inceleyip direk kendi sistemime entegre edeceğim.

 

[çıtır]

Çok teşekkür ediyorum. Kullandığınız yapıyı inceleyip direk kendi sistemime entegre edeceğim.

Rica ederim.Dönüş yaptığınız için teşekkür ederim.Dizi formülü.Formülden çıkarken ctrl+shift tuşuna basılı iken enter tuşlamalısınız.Formülün başında { işareti sonunda } işareti kendiliğinden oluşmalı.Sonra aşağı çekerek çoğaltınız.Kolay gelsin.

 

[çıtır]

Veya Aşağıdaki formülde aynı işlemi yapar.I2 hücresine kopyalayınız.Aşağı çekerek çoğaltınız.

=EĞERHATA(EĞERHATA(EĞERHATA(EĞERHATA(İNDİS($B$1:$B$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($B$2:$B$7<>"");SATIR(A1)));İNDİS($C$1:$C$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($C$2:$C$7<>"");SATIR(A1)-BAĞ_DEĞ_DOLU_SAY($B$2:$B$7))));İNDİS($D$1:$D$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($D$2:$D$7<>"");SATIR(A1)-BAĞ_DEĞ_DOLU_SAY($B$2:$C$7))));İNDİS($E$1:$E$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($E$2:$E$7<>"");SATIR(A1)-BAĞ_DEĞ_DOLU_SAY($B$2:$D$7))));"")

 

[fune1991]

Gösterdiğiniz yöntemleri kullanmak istediğim excel dosyası verileri SQL Server' da bir tablodan çekiyor. Bu yüzden hücre boş da olsa içerisinde bir formül var. Gösterdiğiniz 2 yöntemde de hücrede veri olup olmadığınını İlgili Hücre <>"" sorgusu ile kontrol ediliyor. Hücrede veri olmasa da hücrede formül olduğundan <>"" sorgusu true olarak geri dönüyor ve listeye boş hücrelerde ekleniyor. Bunu nasıl engelleyebilirim?

Örnek Dosya

 

[çıtır]

Aşağıdaki formülü. I2 hücresine kopyalayınız.Aşağı çekerek çoğaltınız.

=EĞERHATA(EĞERHATA(EĞERHATA(EĞERHATA(İNDİS($B$1:$B$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($B$2:$B$7<>"");SATIR(A1)));İNDİS($C$1:$C$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($C$2:$C$7<>"");SATIR(A1)-TOPLA.ÇARPIM(($B$2:$B$7<>"")*(B$2:$B$7<>0)))));İNDİS($D$1:$D$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($D$2:$D$7<>"");SATIR(A1)-TOPLA.ÇARPIM(($B$2:$C$7<>"")*(B$2:$C$7<>0)))));İNDİS($E$1:$E$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($E$2:$E$7<>"");SATIR(A1)-TOPLA.ÇARPIM(($B$2:$D$7<>"")*(B$2:$D$7<>0)))));"")

 

[fune1991]

Örnek dosyada 4 sütun olduğundan formülü de 4 sütun için yazmışsınız. Benim kullanacağım dosyada 20 sütün var. Formülünüzü 20 sütun için tekrardan yazdım. Sıkıntısız çalıştı. Ama formülde anlayamadığım noktalar var ve onları sormak istiyorum. İlk eğer hata fonksiyonun kontrol ettiği indis formülü. Bu formülde dizi kısmında kontrol edeceğimiz ilk sütun var. Formülün ikinci kısmında satır sayısı alanına Toplama formülü yazmışsınız. Bu formül içerisinde verdiğiniz 15 ve 6 değerlerinin manasını anladım. Formülün sonunda kullandığınız Satır(A1) formülünün manasını da dolu olan kaçıncı satırı alacağına yordum ve bütün formülü yukarıdan aşağı sürüklediğimde alınacak dolu hücre sayısını birer birer artırmak için bu fonksiyonunu kullandığınızı anladım. Ancak arada kullandığınız SATIR($B$2:$B$7)/($B$2:$B$7<>"") ifadesinin ne manaya geldiğini çıkartamadım. İkinci olarak da eğer hata fonksiyonu hata verince kontrol edeceği ikinci formülde anlamadığım bir nokta var. Burada da Toplama fonksiyonunun kaçıncı dolu satırı alacağını belirttiğiniz yerdeki ifade olan SATIR(A1)-TOPLA.ÇARPIM(($B$2:$D$7<>"")*(B$2:$D$7<>0)) ifadesi. Bu noktaları açıklayabilirseniz çok sevinirim.

 

[çıtır]

=İNDİS($C$1:$C$7;TOPLAMA(15;6;SATIR($B$2:$B$7)/($C$2:$C$7<>"");SATIR(A1)-TOPLA.ÇARPIM(($B$2:$B$7<>"")*(B$2:$B$7<>0))))

Yukardaki formül üzerinden açıklmak gerekirse.
1)İndis(C1:C7):Bilginin çekileçeği sütun.
2)Toplama(15;6 Toplama(15 Küçük fonksiyonu karşılığı,6 Hata değerlerini yoksay).Bazı değerler şarta uymadığından hata verecek.
3)SATIR(B2:B7)/(C2:C7<>"").C2:C7 aralığındaki dolu satırlarda satır numarasını almak için.(satır(B2:B7 yerine,Satır(A2:A7) de olabilir.
4)SATIR(A1) ilk satırdan itibaren alabilmek için:Aşağı çekildiğinde 1.Küçük,2.küçük ... şeklinde gitmek için.
5)TOPLA.ÇARPIM(($B$2:$B$7<>"")*(B$2:$B$7<>0)):Bu soruya ilişkin birden fazla sütun kullanıldığı için kullanılma zorunluluğu nedeniyle ilave edilmiş fonksiyon.
1.Sütunda değerleri alındıktan sonra satır numaraları tekrar küçüldüğü için. yeniden 1.Küçüğe dönme gereği ortaya çıkmış ve bu sorunda önçe bağdeğdolusay ile çözülmüş.Hücrelerde formül olması bu fonksiyonu işlemsiz kılmış.Bunun yerine hücrelerdeki formülü saymayan bu formül kullanılmıştır.Eğerhata fonsiyonları.Hata kontrolleri için
Umarım yeterinçe açıklayabilmişimdir.Kolay gelsin.

 

[fune1991]

Çok açıklayıcı olmuş. Ellerinize sağlık.

 

[sezengin]

Örnek dosyada 4 sütun olduğundan formülü de 4 sütun için yazmışsınız. Benim kullanacağım dosyada 20 sütün var. Formülünüzü 20 sütun için tekrardan yazdım. Sıkıntısız çalıştı. Ama formülde anlayamadığım noktalar var ve onları sormak istiyorum. İlk eğer hata fonksiyonun kontrol ettiği indis formülü. Bu formülde dizi kısmında kontrol edeceğimiz ilk sütun var. Formülün ikinci kısmında satır sayısı alanına Toplama formülü yazmışsınız. Bu formül içerisinde verdiğiniz 15 ve 6 değerlerinin manasını anladım. Formülün sonunda kullandığınız Satır(A1) formülünün manasını da dolu olan kaçıncı satırı alacağına yordum ve bütün formülü yukarıdan aşağı sürüklediğimde alınacak dolu hücre sayısını birer birer artırmak için bu fonksiyonunu kullandığınızı anladım. Ancak arada kullandığınız SATIR($B$2:$B$7)/($B$2:$B$7<>"") ifadesinin ne manaya geldiğini çıkartamadım. İkinci olarak da eğer hata fonksiyonu hata verince kontrol edeceği ikinci formülde anlamadığım bir nokta var. Burada da Toplama fonksiyonunun kaçıncı dolu satırı alacağını belirttiğiniz yerdeki ifade olan SATIR(A1)-TOPLA.ÇARPIM(($B$2:$D$7<>"")*(B$2:$D$7<>0)) ifadesi. Bu noktaları açıklayabilirseniz çok sevinirim.

20 sütun için yazdığınız formülü paylaşmanız mümkün mü? Bana da benzer bir formül lazım da. teşekkür ederim.