ALTTOPLAM İçinde Belirli Sayıları Toplamak

[welqmx]

Merhaba

ALTTOPLAM yaparken gizli olan sutunlardaki sayıları toplamıyoruz fakat gorunenlerinde içinden bazılarını toplamak istemiyorum Mesela yardımcı hücreyle yan tarafına usd ve TL yerleştirsem görünen gizli olmayan TL veya USD leri nasıl toplatabilirim.
Yardımcı olabilecek arkadaşlara şimdiden Teşekkür ederim.

 

[Milady Meriç]

Dosyanız Ekte.

İnceleyebilirsiniz.

Kolay gelsin.

 

[welqmx]

ilginize teşekkür ederim

Fakat sadece etopla yapınca gizli olan yani süz ile yapılan filitrelemeden dolayı gorunmeyen hücreleride topluyor bu formul
benim asıl aradığım etopla ile birlikte alttoplam ı nasıl çalıştırırım.

 

[Mahir64]

Merhaba

ALTTOPLAM yaparken gizli olan sutunlardaki sayıları toplamıyoruz fakat gorunenlerinde içinden bazılarını toplamak istemiyorum Mesela yardımcı hücreyle yan tarafına usd ve TL yerleştirsem görünen gizli olmayan TL veya USD leri nasıl toplatabilirim.
Yardımcı olabilecek arkadaşlara şimdiden Teşekkür ederim.

Merhaba,

Sorunuz net değil. Biraz daha açarmısınız.

Olmasını istediğiniz sonuçları elle girerek örnek verirmisiniz.

 

[Milady Meriç]

=SUBTOTAL(9,TOPLANACAK ALAN)

Dolar ve TL için ayrı ayrı uyarlanabilir.

 

[welqmx]

merhaba,

sorunuz net değil. Biraz daha açarmısınız.

Olmasını istediğiniz sonuçları elle girerek örnek verirmisiniz.

merhaba mahir

daha net anlaşılır olması için ekteki dosyaya örnek vererek acıklama yaptım. Umarım çözüm için yeterlidir.

Asıl sorun bir sutun düşünün içinde türklirası ve usd olan sutun ve bu sutunu süzüp belirli aralıklarla toplam almak istiyorum mesela süzerken kasım ayı görünür sekılde suzuyorum. Sadece kasım ayına ait toplam usd ve tl leri ayrı ayrı toplamak istiyorum.

Ilginiz için teşekkür ederim

 

[welqmx]

=SUBTOTAL(9,TOPLANACAK ALAN)

Dolar ve TL için ayrı ayrı uyarlanabilir.

Bu şekilde denedim ama beceremedim alttoplam (subtotal) içinde baska fonksiyon yazdırmıyor. mesela "toplanacak alan" yazmışsın o alana etopla mı yapmak lazım yoksa baska bir fonksiyonmu ?

 

[zafer]

Merhaba

Kod ile isterseniz yapabiliriz.

 

[Korhan Ayhan]

Merhaba,

E20 hücresine;

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1));--($F$5:$F$19="$");--($E$5:$E$19))

E21 hücresine;

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1));--($F$5:$F$19="TL");--($E$5:$E$19))

Formüllerini uygulayıp denermisiniz.

 

[katib2007]

bu formül nasıl yazılıyor adım adım mantığının anlatırmısınız

 

[assenucler]

Sayın Korhan Ayhan;

İyi akşamlar.

Katkınız için teşekkürler.

Sevgi ve saygılar.

 

[zafer]

Merhaba

Sn.Korhan formül için diyecek birşey yok.Çok güzel.

Teşekkürler.

 

[Mahir64]

Merhaba,

E20 hücresine;

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1));--($F$5:$F$19="$");--($E$5:$E$19))

E21 hücresine;

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1));--($F$5:$F$19="TL");--($E$5:$E$19))

Formüllerini uygulayıp denermisiniz.

Ne denebilir ki! Harika çalışıyor.

"-MİN(SATIR($F$5:$F$19));;1))" formüle katkısı nedir?

 

[Korhan Ayhan]

Merhaba,

Arkadaşlar formülü ben tasarlamadım. Eğer yanılmıyorsam bu formül, excel formül profesörü Sn. Aladin AKYUREK tarafından hazırlanmış. Yabancı kaynaklı forumlarda bu formül yayınlanmış.

Bizim forumda da daha önce Sn. Ali bey kullanmıştı. Bende çok beğendiğim için arşivime almıştım.

Sanıyorum formülün çözümleme mantığını bizlere en iyi Sn. Ömer bey anlatabilir.

 

[mustafaine1]

Merhabalar, ornegi anlatmak gibi bir saygisizlik etmeyecegim. Ama hazir vaktim varken ve formulu incelemisken SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19)) formulunu genelde kullanmadigimi fakat bu formulun sadece 0,1,3,4,5.. seklinde bir dizi olusturdugunu ve bu sekilde kaydirma dizisi yarattigini soylemek isterim.

Ama ben genelde bu tarz islemler icin match kullanirim..

Ornegin; MATCH(ROW($F$5:$F$19),ROW($F$5:$F$19)) seklinde kullansaydik 1,2,3,4,5.. seklinde calisirdi fakat burada f5`ten baslamak istedigimiz icin 0,1,2,3,4... seklinde olusturmaliyiz yani match formulumuze -1 ekleyecegiz, yani SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19)) yerine MATCH(ROW($F$5:$F$19),ROW($F$5:$F$19))-1 seklinde de formulumuzu kullansak olurdu..

 

[uzmanamele]

Merhaba

Aynı Topla.Çarpım fonksiyon mantığıyla alternatif formül.

=TOPLA.ÇARPIM(ALTTOPLAM(9;KAYDIR($E$5:$E$19;SATIR($E$5:$E$19)-SATIR($E$5);;1));--($F$5:$F$19="$"))

=TOPLA.ÇARPIM(ALTTOPLAM(9;KAYDIR($E$5:$E$19;SATIR($E$5:$E$19)-SATIR($E$5);;1));--($F$5:$F$19="TL"))

Bir formülün mantığını anlamak için basit yüntem şudur.
Formül, birkaç fonksiyondan ibaretse (örnekde 4 fonksiyon var) formülü parçalara ayırarak her fonksiyonun ne sonuç ürettiğine bakmaktır.
Sonra bunları bir araya getirdiğinizde büyük resmi çizmek kolaydır.
Giğer bir yöntem ise, Formül Değerlendirmeden hesap adımlarını incelemektir.
Bu şekilde adım adım ne hesapladığı görüldüğü gibi formül mantığı da çok kolay anlaşılır.

 

[Ömer]

bu formül nasıl yazılıyor adım adım mantığının anlatırmısınız

Merhaba Arkadaşlar,

Formül yapılarının hareketlerini görmek için formül değerlendirme menüsünü kullanmanızı tavsiye ederim.

Önce formülde kullanılan fonksiyonları açıklamaya çalışayım, daha sonra formülün son halini açıklarım.
-------------------------------------------------------------------

Bilindiği üzere süzülen verilerde hesaplama yapmak için basit alttoplam formülü kullanılır.

=ALTTOPLAM(9;E5:E19) toplatabilir.
=ALTTOPLAM(3;E5:E19) sayabilirsiniz. vs..

Yalnız formülde, süzme dışında ikinci bir toplama kriteri olan para birimi olduğu için bir dizi oluşturup formülü bu şekilde sonuçlandırmak gerekir. Burada dizi oluşturacak formül topla.çarpım seçilmiştir.
-------------------------------------------------------------------

Topla.Çarpım formülü adında da anlaşılacağı gibi dizi oluşturup dizileri çarparak toplar. Tercihinize göre matematik işlemine alarak farklı sonuçlara da ulaşabilirsiniz.

=topla.çarpım((a1:a5="excel")*(b1:b5)) formülünde a1:a10 arasında excel olan verilerin b sütunundaki karışıklarını toplar. Basit şekilde gibi mantığı vardır. Derinleştirmek size bağlıdır.

Formülün dizi oluşturması şu şekilde olur.

a1:a5 arasında 2. ve 4. satırlarda excel olduğunu düşünelim, b sütununda ise verilerin 1 ile 5 arasında sayılar olduğunu düşünelim.

{yanlış\doğru\yanlış\doğru\yanlış}*{1\2\3\4\5}

ilk dizi a1:a5 arasında excel olanlara doğru olmayanlara yanlış mantıksal değerini üretir. Excel dilinde doğru - 1 , yanlış ise 0 olarak değer alır. Bu açıklamaya göre dizi aşağıdaki hali alır.

{0\1\0\1\0}*{1\2\3\4\5} bu iki dizi bire bir karşılıkları ile çarpılır ve aşağıdaki sonuca varılır.

{0\2\0\4\0} sonuç dizisin inde her elamanı toplanarak formül bize 6 değerini verir.
-------------------------------------------------------------------

Kaydır ise bir başvuru formülüdür. Belirlediğiniz kriterlere göre alan oluşturur.

=kaydır(başvuru;satır;sütun;yükseklik;genişlik)

formülde yükseklik veya genişlik atlanırsa, başvurudaki yükseklik veya genişlikle aynı olduğu varsayılarak hesaplama yapılır.

Örnek, =kaydır(a1;1;2) a1 den bir satır aşağı ve 2 sütun yana giderek c2 alanını verir.

=kaydır(a1;1;2;3;2) a1den bir satır aşağıda 2 sütuna yan gider c2 yi bulur, daha sonra 3 yükseklik ve 2 genişlik oluşturarak c2:d4 alanını oluşturur.

Formüller üzerinde denemeler yaparak mantığını daha kolay anlayabilirsiniz.
-------------------------------------------------------------------

Formüldeki satır elemanı ise adının da açıklık getirdiği gibi aralıktaki satır indislerini verir. Tabi indislerin tamamını alabilmek için kullandığınız formülün dizi özelliğinin olması gerekir.

örneğin satır(b2:b6) formülü dizide {2\3\4\5\6} elemanlarını oluşturur.
-------------------------------------------------------------------

Formüldeki son elamanda min fonksiyonudur. Bu fonksiyon da belli aralıktaki minimum değeri verir.

=min(a2:a8) aralığındaki minimum değer -5 ise formül -5 değeri üretir.
-------------------------------------------------------------------
-------------------------------------------------------------------
-------------------------------------------------------------------


Formülde kullanılan fonksiyonlardan sonra formülün yapısını dizi oluşturarak açıklamaya çalışayım.

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1));--($F$5:$F$19="$");--($E$5:$E$19))

Formül 3 dizili bir topla.çarpım fonksiyonudur. Yukarıdaki formülde verdiğim örnekte ;-- yerine * kullanılmıştır. Bu kafa karıştırmasın bu kullanım bazen kaçınılmaz bazen tercihe göre bazen de formülün hızı için tercih edilir. Bu detayı tecrübe ile daha iyi anlayabilirsiniz.

($F$5:$F$19="$");--($E$5:$E$19) son iki dizi yukarıda topla.çarpım da verdiğim örnek gibi ölçüte karşılık toplam almak içindir. Bu formülün basit tarafı.
-------------------------------------------------------------------

Formüldeki ilk diziyi ele alalım.

ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1))

Burada alttoplam için belirlenen aralık F5:F19 dur mesele bu aralığı alttoplam içinde bütün olarak değil de dizi olarak yerleştirip tek tek sonuçlandırarak sayfama yada toplama işlemi yapmaktır. Alttoplamda işlev sayısı 3 kullanıldığı için aralığı sayma yoluna gidilmiştir.

alttopllam içinde kullanılan kaydır fonksiyonunda

KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));;1)

satır için aşağıdaki dizi oluşturulmuştur.

SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19))

Dizide , SATIR($F$5:$F$19) bu eleman

{5\6\7\8\9\10\...........\19} dizisini oluşturur.

MİN(SATIR($F$5:$F$19) bu eleman da aynı şekilde.

Min({5\6\7\8\9\10\...........\19}) dizisini oluşturarak dizideki min değeri bulur. Bu değer 5 dir.

İki dizi birbirinden çıkartıldığını için sonuç;

{5\6\7\8\9\10\...........\19}-5

{0\1\2\3\4\5\.............\14} dizisi oluşuru. Bu işlemi neden yaptık derseniz, kaydır fonksiyonunda satır elamanı,

0 başvurulan satırdır.
1 başvurulan satırın bir altındakidir.
2 başvurulan satırın iki altındakidir.

Bu şekilde tüm başvuru alanını başından sonuna kadar dizi içine tek tek sokmuş oluruz.
-------------------------------------------------------------------

SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19)) bu diziyi oluştururken neden tek seferde satır(f1:f15)-1 kullanılmadı diye sorulabilir.

Buradaki maksat formülün yazım şekliyle ilgilidir. Eğer formülü, satır(f1:f15)-1 bu şekilde diziye sokarsanız sonuç olarak bir şey değiştirmek fakat;

KAYDIR($F$5:$F$19;SATIR($F$1:$F$15)-1;;1)

F5:F19 - F1:F15 gibi aralıklardaki farklı yazılımlar hem görsel olarak kafa karıştırabilir, hem formülü yazarken iki kere düşünmek zorunda kalırsınız, hem de daha önemlisi bu aralıkta satır ekleme yapıldığında formül aynı anda hareket etmez ve bozulup yanlış değerler almanıza neden olur.

SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19))

yerine,

SATIR($F$5:$F$19)-SATIR($F$5) de yazılabilirdi.

Bu açıklamadan sonra formüle geri dönersek,

kaydır($F$5:$F$19;{0\1\2\3\4\5\.............\14};;1)

burada başvuru f5:f19 seçildiği için yükseklik 1 seçilmiştir. Boş bırakıldığında başvuru ile aynı yükseklikte kabul edileceği için seçim yapılmıştır. Sütunda ; atlanarak 0 olarak belirlenmiştir. Yani kaydır aralığı bozulmadan F5:F19 arasında tek tek işleme girer.

kaydır($F$5;{0\1\2\3\4\5\.............\14};; ) şeklinde yazılsa idi, yükseklik boş bırakılabilirdi. Çünkü başvuru tek olarak seçilmiştir. Yüksekliği seçildiği gibidir.

kaydır fonksiyonunun dizideki kullanım amacı budur.
-------------------------------------------------------------------

Şimdi alttolam ile kaydırın alanını birleştirirse,

=allttoplam(3,kaydır dizin alanı şeklide olur.

Buradaki 3 değeri sayım yapmak için kullanıldığı için, F5:F19 aralığına tek tek bakarak dizi içinde eğer süzülen (gizli) değer varsa 0 yoksa 1 değeri verir,

{1\1\1\0\0\0\1\1\1\0....} gibi.

Formülün ilk dizisini bu şekilde bitirmiş olduk.

son iki elaman ($F$5:$F$19="$");--($E$5:$E$19) da aynı şekilde hesaplamaya girer. Bunu daha önce açıklamıştım.

{1\1\1\0\0\0\1\1\1\0....};--($F$5:$F$19="$");--($E$5:$E$19)

Tahmini yazıyorum.

{1\1\1\0\0\0\1\1\1\0....};--{doğru\yanlış\yanlış\doğru\....};--($E$5:$E$19)

doğrular 1 yanlışlar 0 olur.

{1\1\1\0\0\0\1\1\1\0....};--{1\0\0\1\....};--($E$5:$E$19)

son dizide de e5:e19 arası değerler diziye girer.

{1\1\1\0\0\0\1\1\1\0....};--{1\0\0\1\....};--(14\659\154\698\....)

son olarak bu dizi birbiriyle işleme girere 1 olanların karşılığı sonucu verir.

Ek olarak formülde alttolamda 3 kullanıp da ayrı ayrı dizi oluşturmak yerine 9 kullanıp toplam alanını iki defa yazmaktan kurtulabiliriz.
-------------------------------------------------------------------

=TOPLA.ÇARPIM(ALTTOPLAM(3;KAYDIR($F$5:$F$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));0;1));--($F$5:$F$19="$");--($E$5:$E$19))

yerine;

=TOPLA.ÇARPIM(ALTTOPLAM(9;KAYDIR($E$5:$E$19;SATIR($F$5:$F$19)-MİN(SATIR($F$5:$F$19));0;1));--($F$5:$F$19="$"))

yazılabilirdi.
-------------------------------------------------------------------

Hızlı yazımdan dolayı mana bozukluğu, anlatım ve yazım hatası varsa kusuruma bakmayın.

Foruma kazandırdıkları formül çözümlerinden dolayı, Sn. Aladin AKYUREK, Sn. Ali bey'e ve tüm arkadaşlarımıza bu vesile ile tekrar teşekkür ederim.

.

 

[Mahir64]

Öğrenilecek çok şey var daha.

Teşekkürler Ömer bey, güzel anlatımınız için.


-MİN(SATIR($F$5:$F$19)) bunun yerine

SATIR(F5) denildiğinde de aynı çocunu üretecek , anladığım kadarıyla.

Tekrar teşekkürler.

 

[Ömer]

Rica ederim Mahir bey.

Evet, SATIR($F$5:$F$19)-SATIR($F$5) de yazılabilirdi.

Buradaki maksat, f5:f19 arasındaki dizi elamanı kadar 0 dan başlayan satır sayısı (dizi elamanı) üretmekti.

 

[mustafaine1]

Omer bey aciklamaniz icin tesekkur ederim;

Ayrica uyelerimiz icin tavsiye olarak; SATIR($F$5:$F$19)-SATIR($F$5) yerine ayni satirin kendi ile karsilastirilmasi ile yani match(row(..),row(..)) seklinde 1,2,3,4, seklinde bir dizi olusturulmasi daha akilda kalici olabilir diye dusunuyorum.