Birden çok koşula dayalı olarak değerleri toplamak istiyorum.

[ismailavci76]

İyi günler,

Elimdeki bir tabloda yer alan TARİH ve TANIM koşulunu sağlayan satırlardaki belirli hücrelerin toplamının bir başka hücrede yazılmasını istiyorum. Yani B (tarih sütunu) sütunundaki tarih 01.01.2021-31.01.2021 aralığında ise F sütunundaki (TANIM sütunu) tanımların karşısında C ve D sütunlarında yer alan verilerin OCAK AYI'na ait O ve P sütunlarındaki ilgili hücrelere yazılmasını istiyorum. Bu şekilde tarihe göre tanımların karşısındaki rakamlar ilgili Aylara ait O ve P sütunlarındaki hücrelere yazılmalı.

Ekte gönderdiğim tabloda ayrıntılı bir şekilde sorumu yazdım. Şimdiden desteğiniz için teşekkür ederim,

Saygılarımla
İsmail Avcı

 

[Korhan Ayhan]

Deneyiniz.

Formüllerin içinde yıl olarak 2021 yılını elle yazdım. Dilerseniz bir hücreden aldırarak dinamik hale getirebilirsiniz.

O6;

=SUMIFS($C$4:$C$103;$B$4:$B$103;">="&DATEVALUE("1."&LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&EDATE(DATEVALUE("1."&LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

P6;

=SUMIFS($D$4:$D$103;$B$4:$B$103;">="&DATEVALUE("1."&LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&EDATE(DATEVALUE("1."&LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

SUMIFS = ÇOKETOPLA
DATEVALUE = TARİHSAYISI
LOOKUP = ARA

 

[ismailavci76]

Korhan bey çok teşekkür ederim.

Bir başka konuda bir arkadaşın sorusuna bu şekilde cevap verilince arkadaş demiş ki, "eğer meslek sırrı değilse formülün açıklamasını yazabilir misiniz?". Şimdi ben de soruyorum, ezbere yapmış olmak istemediğim için. Öğrenmek amacıyla, formülün açıklamasını yazabilir misiniz müsait olduğunuz bir zaman, eğer meslek sırrı falan değilse tabi

Bunun dışında sorum çözülmüştür, tekrar teşekkür ederim, iyi çalışmalar.

 

[Korhan Ayhan]

Bu büyük sırrı en sonunda elimden geldiğince açıklamaya çalışacağım.

Birden çok koşul deyince hemen aklımıza TOPLA.ÇARPIM geliyor. Fakat bu fonksiyon büyük veri aralıklarında dosya boyutuna etki ediyor ve dosyanın yavaşlamasına sebep oluyor. Eğer eski sürüm kullansaydınız bunu önerecektim.

Siz yeni excel sürümü kullandığınız için ÇOKETOPLA fonksiyonuna yöneldim. Bu fonksiyonda çok koşullu toplama işlemleri için kullanılıyor. Hem de daha performanslı sonuç vermektedir.

Aslında ben her zaman formülleri sadeleştirme taraftarıyım. Performans için exceli yormamak lazım.

Mesela siz M6 hücresine 01.01.2021 yazsaydınız formül daha kısa yazılacaktı. Hücre biçimini de AY (OCAK) gösterecek şekilde düzenlerseniz yine görsel olarak aynı görüntüyü elde edersiniz.

Bu durumda oluşacak formül aşağıdaki gibi olacaktı;

EN;

=SUMIFS($C$4:$C$103;$B$4:$B$103;">="&LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6);$B$4:$B$103;"<="&EDATE(LOOKUP(2;1/($M$6:$M6<>"");$M$6:$M6);1)-1;$F$4:$F$103;$N6)

TR;

=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">="&ARA(2;1/($M$6:$M6<>"");$M$6:$M6);$B$4:$B$103;"<="&SERİTARİH(ARA(2;1/($M$6:$M6<>"");$M$6:$M6);1)-1;$F$4:$F$103;$N6)

İlk mesajımda ki O6 hücresi için kullandığım formülü adımlayarak gidersek;

=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">="&TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&SERİTARİH(TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

1. koyu renkli bölüm toplanacak alanı ifade etmektedir.


=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">="&TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&SERİTARİH(TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

2. koyu renkli bölümde ise M6 hücresine yazdığınız ay adından bir tarih serisi üretmeye çalışıyoruz. TARİHSAYISI("1."&OCAK&".2021") ilgili alanı değerlendirdiğimizde bize 01.01.2021 tarihini döndürecektir. Yani ayın ilk gününü verecektir. Bu değerin soluna "büyük eşit" koşulunu ekleyerek ilk ölçütümüzü oluşturmuş oluyoruz. Formülün içinde kullanılan ARA fonksiyonu ise M7-M8-M9-M10 hücreleri boş olduğu için ve bize o kısımda da OCAK ayı bilgisi lazım olduğu için kullandık. Formülün ARA(2;1/($M$6:$M6<>"");$M$6:$M6) kısmını başına eşittir ekleyerek boş bir hücreye yazıp aşağı doğru sürüklerseniz ne demek istediğimi anlayabilirsiniz.


=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">="&TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&SERİTARİH(TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

3. koyu renkli bölümde ise bir önceki koşulun aynısı oluyor. Fakat burada bize ayın son günü olan 31.01.2021 tarihini döndürüyor. Bu değer içinde yine sol tarafında olacak şekilde "küçük eşit" koşulunu ekliyoruz.

Böylece tarih sütununda ay aralığını tanımlayarak istediğimiz değerleri dikkate almasını sağlamış oluyoruz.


=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">="&TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");$B$4:$B$103;"<="&SERİTARİH(TARİHSAYISI("1."&ARA(2;1/($M$6:$M6<>"");$M$6:$M6)&".2021");1)-1;$F$4:$F$103;$N6)

4. koyu bölümde ise TANIMI koşulunu ekleyerek fonksiyon yazımını tamamlıyoruz.


Formülü değerlendirip son halini incelediğimizde ise aşağıdaki şekli alarak sonuç vermektedir.

=ÇOKETOPLA($C$4:$C$103;$B$4:$B$103;">=01.01.2021";$B$4:$B$103;"<=31.01.2021";$F$4:$F$103;"1.kalite blok")

İlk başta yazmış olduğumuz o karmaşık formül yapısı en nihayetinde bu hali alarak bize istediğimi sonucu vermektedir...

 

[ismailavci76]

Saygılar üstadım, elinize sağlık, daha iyi anladım şimdi.