Aya ve yıla göre sütun toplamı alırken, USD değerleri EUR'a çevirmek (Topla.Çarpım ?)

[tabari]

Güncelleme (4 Şubat 2012)

Aşağıdaki orijinal sorum çözüldü, şimdi (4 Şubat 2012, saat 10.07) yeni bir mesaj yazdım ve TOPLA.ÇARPIM ile çalışan formülü ÇOKETOPLA ile yazma konusunda ilave sorular sordum.

=============

Merhaba,

Ekteki dosyada yapmak istediğimi açıklayan örnek mevcut.

Tablomda:
- 3 sütun var : "Tarih", "Firma" ve "Tutar"
- Tutarlar USD veya EUR olabiliyor.
- "Tutar" hücre biçimi, "Firma" sütununda yazan değere göre koşullu biçimlendirme ile belirleniyor (EUR veya USD olarak).

Yapmak istediğim :
- Ay ve yıla göre dönemsel olarak (örn: Ocak 2012) tutar toplamlarını almak,
- Bunu yaparken de EUR olanları toplama aynen katmak, USD olanları ise pariteye bölüp (örn: 1,3) EUR'a çevirdikten sonra toplama katmak; yani toplamı EUR olarak elde etmek.
- Para birimi tespitini ise, "Firma" sütünündaki değeri, ParaBirimiEUR ve ParaBirimiUSD olarak ad tanımlaması yaptığım aralıklardan hangisinde mevcut olduğuna bakarak yapmak istiyorum.

Not : Gerçek tabloda çok daha fazla firma adı ve veri girişi var. Dolayısıyla formülde firma isimlerini EUR ve USD için tek tek yazmak yerine, ad tanımlaması yaptığım aralıklardan kontrol etmek istiyorum.

 

[Korhan Ayhan]

Merhaba,

Aşağıdaki formülü denermisiniz.

B24 hücresine uygulayınız.

=TOPLA.ÇARPIM((AY($A$4:$A$15)=AY(A24))*(YIL($A$4:$A$15)=YIL(A24))*((SOLDAN($B$4:$B$15;1)="E")*($C$4:$C$15)+(SOLDAN($B$4:$B$15;1)="U")*($C$4:$C$15/1,3)))

 

[tabari]

Merhaba,
Aşağıdaki formülü denermisiniz.
B24 hücresine uygulayınız.

Cevabınız için teşekkür ederim, formülünüz çalışıyor ve Topla.Çarpım içinde + kullanımını daha net görmüş oldum.

Fakat firma adının ilk harfini kullanmak yerine, firma adının ParaBirimiEUR ve ParaBirimiUSD ad tanımlama aralığında olup olmamasına göre EUR veya USD olduğunu anlayabilir miyiz ? Çünkü benim gerçek tablomda isimler çok farklı.

Bu istediğimi aslında koşullu biçimlendirme yaparken aşağıdaki formülle elde ediyorum. Örnek tablomda da bunu kullandım.

=YADA(B1=ParaBirimiEUR)

Ama aynısını Topla.Çarpım fonksiyonu içinde kullandığımda istediğim sonuç çıkmıyor.

 

[Korhan Ayhan]

Merhaba,

Aşağıdaki formüllerden birisini denermisiniz.

=TOPLA.ÇARPIM((AY($A$4:$A$15)=AY(A24))*(YIL($A$4:$A$15)=YIL(A24))*((EĞERSAY($E$4:$E$5;$B$4:$B$15)*($C$4:$C$15)+(EĞERSAY($F$4:$F$5;$B$4:$B$15)*($C$4:$C$15/1,3)))))

=TOPLA.ÇARPIM((AY($A$4:$A$15)=AY(A24))*(YIL($A$4:$A$15)=YIL(A24))*((EĞERSAY([COLOR=red]ParaBirimiEUR[/COLOR];$B$4:$B$15)*($C$4:$C$15)+(EĞERSAY([COLOR=red]ParaBirimiUSD[/COLOR];$B$4:$B$15)*($C$4:$C$15/1,3)))))

 

[tabari]

Merhaba,
Aşağıdaki formüllerden birisini denermisiniz.

Ben 1.formülü denerken herhalde siz 2.formülü ilave ettiniz

1.formül çalıştı, elinize sağlık.

Tabi benim tablom sürekli genişleyeceği için, hücre aralıklarını absolute reference olarak değil, tablo adı ve sütun adı belirterek aşağıdaki gibi yazdım. Böylece formül içinde sadece A24 relative ve toplam tutar tablosunda aşağı doğru A24, A25, A26 olarak istediğim gibi değişiyor.

=TOPLA.ÇARPIM((AY(DataTablosu[Tarih])=AY(A24))*(YIL(DataTablosu[Tarih])=YIL(A24))*((EĞERSAY(ParaBirimiEUR;DataTablosu[Firma])*(DataTablosu[Tutar])+(EĞERSAY(ParaBirimiUSD;DataTablosu[Firma])*(DataTablosu[Tutar]/1,3)))))

Eğersay fonksiyonunu kullanmak gayet mantıklı. Sonuçta tek bir hücreyi aralık içinde kontrol ediyoruz ve varsa 1, yoksa 0 dönecektir; başka ihtimal yok :bravo:

Excel formüllerini, fonksiyonlarını yeni yeni öğreniyorum ve gördüğüm kadarıyla tamamen esnek düşünmek gerekiyor. Aynı sonucu birçok yoldan elde edebiliyorsunuz. Acaba sizin arşivinizde ve forumda (yabancı dilde olabilir), Excel formüllerini ve fonksiyonlarının alternatif kullanım örneklerini bir liste olarak veren bir kaynak var mıdır ?



Bu arada, ben de dün gece araştırırken istediğimi farklı bir şekilde elde edebileceğimi düşündüğüm, ama henüz üzerine çalışmadığım aşağıdaki formülü gördüm. Bu formül sizin formülünüzde sadece "EĞERSAY" kısmına denk geliyor tabi.

=EĞER(EYOKSA(KAÇINCI(DataTablosu[Firma];ParaBirimiEUR;0)),"1","0"

- "KAÇINCI" fonksiyonu Türkçe'ye biraz tuhaf tercüme edilmiş. İngilizcesi "MATCH" (eşleşme).
-Bu formülün yaptığı, Data Tablosu'nda Firma sütunundaki değer, ParaBirimiEUR içindeki değerlerden biriyle eşleşiyorsa "1" dönüyor, eşleşmiyorsa "0" dönüyor.
-ParaBirimiEUR'dan sonraki 0, fonksiyon argümanlarından biri. 0-Tam eşleşme anlamına geliyor.

Neyse, benim durumumda aslında Sterlin, TL gibi para birimleri de olacağı için bu formül fazla karmaşık olur. Sizin yönteminiz daha basit.

 

[Korhan Ayhan]

Merhaba,

Forumumuzun Excel Dersanesi bölümünü incelemenizi öneririm. Birçok açıklamalı örnek çalışmaya ulaşabilirsiniz.

Excel formüllerini, fonksiyonlarını yeni yeni öğreniyorum ve gördüğüm kadarıyla tamamen esnek düşünmek gerekiyor. Aynı sonucu birçok yoldan elde edebiliyorsunuz. Acaba sizin arşivinizde ve forumda (yabancı dilde olabilir), Excel formüllerini ve fonksiyonlarının alternatif kullanım örneklerini bir liste olarak veren bir kaynak var mıdır ?

 

[tabari]

Tekrar merhaba,

Şu konudaki Excel hesaplama hızı ile ilgili sorumu takiben, TOPLA.ÇARPIM ile sizin yardımınız ile yazdığım aşağıdaki formüldeki AY & YIL kriterleri ve para birimi kriteri ÇOKETOPLA ile yazılabilir mi ?

TOPLA.ÇARPIM ile istediğim gibi çalışan formül :

=TOPLA.ÇARPIM((AY(DataTablosu[Tarih])=AY($A24))*(YIL(DataTablosu[Tarih])=YIL($A24))
*((EĞERSAY(ParaBirimiEUR;DataTablosu[Firma])*(DataTablosu[Tutar])
+(EĞERSAY(ParaBirimiUSD;DataTablosu[Firma])*(DataTablosu[Tutar]/1,3)))))

Benin ÇOKETOPLA ile yazmaya çalıştığım örnek formüller aşağıda, fakat ikisi de çalışmıyor, 0 sonucu veriyorlar.

Not : Bu formülleri ilk mesajın ekindeki dosyada herhangi bir hücreye yazabilirsiniz.


AY ve YIL kriteri için :

=ÇOKETOPLA(DataTablosu[Tutar];
DataTablosu[Tarih];
VE(AY(DataTablosu[Tarih])=AY(A24);(YIL(DataTablosu[Tarih])=YIL(A24))))

Firma adının, bir aralıktaki (örn.ParaBirimiEUR) aralığında bulunması kriteri :

=ÇOKETOPLA(DataTablosu[Tutar];
DataTablosu[Firma];
DataTablosu[Firma]=YADA(ParaBirimiEUR))

* Bu formüldeki YADA fonksiyonu, koşullu biçimlendirmede çalıştığı için burada da kullanmaya çalışıyorum ama olmuyor.

Not : Formüller daha okunaklı olsun diye Alt+Enter ile satır başı yapıyorum. Tüm formülü görmek için formül çubuğunu alt kenarından yakalayıp genişletmeniz gerekebilir.