Soru Düşeyara ile birden çok sütundaki verileri toplayıp getirmek

[klop01]

Arkadaşlar,
Aşağıdaki gibi bir formül kurulabilir mi?
ANA SAYFA'daki E8 hücresini VERİ1 sayfasında B5:V14 aralığında ara. Bulduğun satırdaki 5-14 ve 18-22'nci sütunlarında yazan verilerin toplamını getir.
+
ANA SAYFA'daki E8 hücresini VERİ2 sayfasında B5:V14 aralığında ara. Bulduğun satırdaki 5-14 ve 18-22'nci sütunlarında yazan verilerin toplamını getir.
Yukarıdaki iki işlem tek bir formül ile olmalıdır.
TC Kimlik no.lar sabit satırlarda değildir.

 

[S.Yiğit]

Merhaba,

Aşağıdaki formülü deneyin lütfen.

=TOPLA.ÇARPIM((VERİ1!$C$5:$C$14='ANA SAYFA'!E8)*(VERİ1!$E$5:$V$14<>"")*(VERİ1!$E$5:$V$14))+TOPLA.ÇARPIM((VERİ2!$C$5:$C$14='ANA SAYFA'!E8)*(VERİ2!$E$5:$V$14<>"")*(VERİ2!$E$5:$V$14))

 

[klop01]

Sayın S.Yiğit,
Teşekkür ederim.
Tüm satırlarda denedim. Hatasız çalıştı.
Şöyle bir durumu gözden kaçırmışım soruyu sorarken.
Eğer aranan kişiye veri yazılmamışsa sonuç boş gözüksün.

Mevcut formül kişinin verileri tamamen boşa da 0 sonucuna ulaşıyor, kişinin verileri 0 ise de 0 sonucuna ulaşıyor.
Bunu iki durumu ayırmazsam hata olacak.
Bu konuda bir şey yapılabilir mi?

 

[S.Yiğit]

Sıfırlar için G8:G17 aralığını seçerek hücre biçimlendirmeden isteğe uyarlanmışı seçin ve tür kısmına 0;-0;;@ yazarak deneyin.

 

[klop01]

Sayın S.Yiğit,
Teşekkürler.
Önerdiğiniz işlem sıfırları göstermiyor. Yapmak istediğim boş olanlarda boş gözükmesi sıfır olanlarda sıfırın gözükmesi şeklinde.
Düşeyara kullanılarak Veri1 ve Veri2 sayfalarında yardımcı sütun kullanarak sorunu çözebiliyorum, orijinal belgemde satır sayısı fazla olduğu için bunu kullanmak istememiştim.
Yardımcı sütun olmadan zor görünüyor. Böyle yapayım artık.

 

[Korhan Ayhan]

Alternatif;

1 yardımcı alan kullanılmıştır. İşlem yapılacak sayfa sayısı artacaksa ad tanımlama bölümünü genişleterek kullanabilirsiniz.

 

[klop01]

Sayın Korhan Ayhan,
Formül işlemi yaptı, sağ olunuz.

Yardımcı sütun kullanmadan yapmak için araştırma yaparken yabancı bir sitede bir formül gördüm. Onu dosyaya uyarladım. Denemelerimde doğru sonuç verdi. Bir eksiği var. O da hücreler tamamen boşsa sıfır değerini göstermesi. Bunu formüle yapacağımız bir ekleme ile boş gösterebilir miyiz?

Dizi formülüdür.
G8= =EĞERHATA(TOPLA(DÜŞEYARA($E8;VERİ1!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0);DÜŞEYARA($E8;VERİ2!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0));"")

 

[Korhan Ayhan]

İşin içinde TOPLAMA olduğu için sıfır değeri döndürür. Benim aklıma şuan için gelen bir çözüm yolu yoktur.

Belki diğer arkadaşlar mantıklı bir çözüm bulabilirler.

 

[S.Yiğit]

Merhaba,

Aşağıdaki gibi formüle eğer eklesek mantıksız mı olur? Yine dizi formül olacak şekilde?

=EĞERHATA(EĞER(TOPLA(DÜŞEYARA($E8;VERİ1!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0);DÜŞEYARA($E8;VERİ2!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0))=0;"";TOPLA(DÜŞEYARA($E8;VERİ1!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0);DÜŞEYARA($E8;VERİ2!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0)));"")

 

[Korhan Ayhan]

@S.Yiğit,

Sıfır olan hücreler içinde boş bırakıyor. Bunu da eklemek gerekiyor.

 

[klop01]

Boş hücrelerin getirdiği 0 değerini yakalamak için aşağıdaki şekilde çalıştım.
ETOPLA formülü ile boş olmayan hücreleri toplatmak için bayağı uğraştım. ETOPLA galiba sadece bir sütunda şart arıyormuş, dosyaya uymadı.
BAĞ_DEĞ_SAY sadece sayı topladığı için uygun değildi.
BOŞLUKSAY, EĞERSAY gibi işlevlere de baktım, ya olmuyor ya da yanlış yapıp sonuç alamadım.

BAĞ_DEĞ_DOLU_SAY ile dolu hücrelerin sayısını bulup ona göre 0'ı boş göstermek istedim, bir türlü doğru sonuç vermedi.
=BAĞ_DEĞ_DOLU_SAY(DÜŞEYARA($E8;VERİ1!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0);BAĞ_DEĞ_DOLU_SAY(DÜŞEYARA($E8;VERİ2!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0)))
Yukarıdaki formül bana göre sonucu göstermesi lazım ama göstermiyor.

 

[Korhan Ayhan]

Deneyiniz.

=EĞER(BAĞ_DEĞ_SAY(--DÜŞEYARA($E8&"";VERİ1!$C$5:$V$14&"";{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0))+
BAĞ_DEĞ_SAY(--DÜŞEYARA($E8&"";VERİ2!$C$5:$V$14&"";{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0))=0;"";
TOPLA(DÜŞEYARA($E8;VERİ1!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0);DÜŞEYARA($E8;VERİ2!$C$5:$V$14;{3;4;5;6;7;8;9;10;11;12;16;17;18;19;20};0)))

 

[Ömer]

Merhaba,

Alternatif:

Korhan Bey'in #6 numaralı mesajındaki eklediği dosyada yardımcı sütun olmadan aşağıdaki formülü kullanabilirsiniz.

=EĞER(TOPLA.ÇARPIM(ÇOKEĞERSAY(DOLAYLI("'"&Sayfalar&"'!"&"C4:C20");E8;KAYDIR(DOLAYLI("'"&Sayfalar&"'!"&"E4:E20");;SATIR($1:$18)-1);"<>"))>0
;TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&Sayfalar&"'!"&"C4:C20");E8;KAYDIR(DOLAYLI("'"&Sayfalar&"'!"&"E4:E20");;SATIR($1:$18)-1)));"")

.

 

[İdris SERDAR]

.

Değişik bir bakış açısı ile dosyanız ekte. (Çalışma, Korhan beyin dosyası üzerinde yapılmıştır.)

Sayfalar Data > Consolidate ile konsolide edilerek sadece bir Düşeyara işlevi le sonuçlar elde edilmiştir.


.

 

[klop01]

Sayın Korhan Ayhan ve Ömer,
Her iki formül de sorunsuz olarak çalışıyor.
Çok teşekkür ederim. Sorun ortadan kalktı.

Sayın İdris SERDAR,
Dosya için teşekkürler.
Eklediğiniz dosya satırlar boşken 0 değerini getiriyor. Bunun olmaması gerekiyor.

 

[İdris SERDAR]

.

Onu atlamışım.

Buna bakın.

.

 

[klop01]

Sayın SERDAR,
Teşekkür ederim, formül işlemi hatasız yapıyor.

 

[Haluk]

Google Sheets ile alternatif çözüm:




.

 

[klop01]

Sayın Korhan Ayhan,
Formülünüzde birkaç yerde geçen &"" ne anlama geliyor?

 

[Haluk]

Yukarıda 18. mesajdaki Google Sheets'e aşağıdaki linkten ulaşılabilir...

Sayfalarda düşeyara.xlsx

drive.google.com

Not: Sıfır (0) değerler tabloda boş olarak gösterilmiştir.

.