-
DİKKAT DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
Altın Üyelik Hakkında Bilgi
Çoketopla ile koşula bağlı toplam aldırma
- Konbuyu başlatan rota59
- Başlangıç tarihi
Ömer BARAN
Uzman
- Katılım
- 8 Mart 2011
- Mesajlar
- 12,986
- Excel Vers. ve Dili
- Office 2013 ( 32 bit ) TÜRKÇE
Merhaba, foruma hoşgeldiniz.
B3 hücresine aşağıdaki formüllerden birini uygulayıp, sağa doğru kopyalayın.
.
B3 hücresine aşağıdaki formüllerden birini uygulayıp, sağa doğru kopyalayın.
.
Kod:
=TOPLA.ÇARPIM((AY($H$2:$H$10)=SÜTUN()-1)*($I$2:$I$10))
[COLOR="Red"]veya[/COLOR]
=TOPLA(EĞER(AY($H$2:$H$10)=SÜTUN()-1;$I$2:$I$10))
[COLOR="Blue"]İkinci formül dizi formülü olup, hücreye [COLOR="Red"]CTRL+SHIFT+ENTER[/COLOR] ile girilmelidir.[/COLOR]
Son düzenleme:
Merhaba,
İlgili kodları çalışma kitabım da, uygulama yaptığım da,
sorumu yanlış sorduğumu fark ettim. Cevaplarınız doğru ve cevabınız ile yeni bir şey öğrendim.
Ancak benim ihtiyacım, bir makro sanırım.
Örnek dosyayı ekleyip, içinde açıklamaları yaptım.
Yardımınızı rica ederim.
İlgili kodları çalışma kitabım da, uygulama yaptığım da,
sorumu yanlış sorduğumu fark ettim. Cevaplarınız doğru ve cevabınız ile yeni bir şey öğrendim.
Ancak benim ihtiyacım, bir makro sanırım.
Örnek dosyayı ekleyip, içinde açıklamaları yaptım.
Yardımınızı rica ederim.
- Katılım
- 1 Mart 2005
- Mesajlar
- 22,239
- Excel Vers. ve Dili
- Win7 Home Basic TR 64 Bit
Ofis-2010-TR 32 Bit
Merhaba,
Çalıştığım işten ötürü yeni inceleme fırsatım oldu müthiş olmuş. Geç cevap verdiğim için özür dilerim.
formül' e ait bir kaç soru sormak istiyorum.
1 _)
Formülün için de geçen kod ile Excel'e ne komutu vermiş olduk.
Çalıştığım işten ötürü yeni inceleme fırsatım oldu müthiş olmuş. Geç cevap verdiğim için özür dilerim.
formül' e ait bir kaç soru sormak istiyorum.
1 _)
Kod:
KAÇINCI(9,9999E+307Selam,
Formüllerin açıklaması
B kolonunda SayfaAdedine göre sıra listesi yapılmaktadır. B5 hücresindeki formül
=EĞER(SATIRSAY($1:1)<=SyfAdet-1;SATIRSAY($1:1);"")
Burada SyfAdet Makro4 kodlarından GET.WORKBOOK (Türkçe olarak AL.ÇALIŞMAKİTABI) kullanılarak yapıldı
(SyfAdet-1 olarak yazılmasının sebebi Toplam sayfa adedi nden 1 eksik olarak (Örnekte 5 , ama listede 4 olması lazım. Çünkü AnaSayfa işlem sayfası olduğundan adetlerde yazdırılması istenmedi) yazdırılması için )
=SAYIYAÇEVİR(AL.ÇALIŞMAKİTABI(4)&DOLAYLI("IV65000")) --- SyfAdet Ad Tanımlaması
C kolonunda sayfa adlarını yazdırmak için
=EĞERHATA(İNDİS(Sayfa;B5+1);"")
Burada Sayfa Makro4 kodlarından GET.WORKBOOK (Türkçe olarak AL.ÇALIŞMAKİTABI) kullanılarak yapıldı
(B5+1 olarak yazılmasının sebebi ilk sayfa işlem sayfası olan AnaSayfa ve bunun yazılmasını istemiyoruz. Çünkü diğer sayfalardan toplam alıp bu sayfada işlem yapacağız)
=PARÇAAL(AL.ÇALIŞMAKİTABI(1);BUL("]";AL.ÇALIŞMAKİTABI(1))+1;99)&DOLAYLI("IV65000") ---- Sayfa Ad Tanımlaması
Makro4 kodları sayfalarda direkt fonksiyon olarak kullanılamaz. Bunun için gerek Sayfa ve gerekse SyfAdet ad tanımlamaları yapılarak formüllerde kullanıldı. Bununiçin Bak Formüller > Ad Yöneticisi.... Detay için GET.WORKBOOK internette arayarak incelemeniz i öneririm. Şunu söylemek isterim formüllerdeki &DOLAYLI("IV65000") ifadesi "" değer üretecek olup metne çevirmek için kullanılmaktadır.
Aylık toplamların alınmasında , örneğin E5 hücresi (Ocak ve A sayfası)
=EĞERHATA(TOPLA.ÇARPIM((DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))>=TARİH($E$2;AY(E$3&1);1))*(DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))<=TARİH($E$2;AY(E$3&1)+1;0))*(DOLAYLI("'"&$C5&"'!k6:k"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))));"")
Sanıyorum , Bu formülün
DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))
bu kısmını incelemek yeterli olacaktır.
EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6)
ile KAÇINCI(9,9999e307 .... ifadesi Excelin tanıdığı en büyük sayı olan 9,99999999 e 307 nin kullanılması ile en son dolu hücrenin kaçıncı satırda olduğunu bulmak için kullanıldı. DOLAYLI("'"&$C5&"'!B:B")) burada C5 de yazan (ki, A sayfasıdır. ) sayfa adının B kolonunun dikkate alınmasıdır. Yani kaçıncı(......;dolaylı(..... ifadesiyle de , C5 de adı yazan sayfanın B kolonundaki içerisinde değer ( değer mutlaka sayı olmalı çünkü 9,9999 ile sayıları arattırıyoruz) bulunan satır numarasını bulmak .... Dolayısıyla bunu kullanarak Sayfalar DİNAMİK olarak değerlendirildi. Diğer bir deyişle A , B , C, .... sayfalarına aşağıya doğru istediğiniz sayıda veri girebilirsiniz.. Formüllerde değişiklik gerekmeyecektir. Eğerhata ile de sayfada B kolonunda sayı yoksa, ya da sayfa var ama henüz değer girilmemişse, koşulunuz olan 6 satıra bağlama işlemi yapıldı. Yani hata olursa 6 sayısı gelsin istedik.
Eğerhatanın önündeki DOLAYLI("'"&$C5&"'!B6:B"& kısmına baktığımızda ise, C5 te adı yazan sayfanın B6:B... ... kısmına da eğerhata ile başlayan formülde bulduğumuz sayı gelecek... (Örnek olarak bakalım A sayfası için , Eğerhata(.....) formülü bize B kolonundaki son değer olan 10. satır olduğundan , 10 değerini üretecektir. DOLAYLI("'"&$C5&"'!B6:B"& ... ifadesinde .... yerine 10 geleceğinden DOLAYLI("'"&$C5&"'!B6:B"& 10 şeklinde olacak ve bu da A!B6:B10 şeklinde hücre aralığı değerini üretecektir.
Sonradan ilave Not : Sayfada herhangi bir VBA kodu olmamasına rağmen, Makro4 kodlarından yararlanılarak yapılan ad tanımlamalarından dolayı sayfa .xlsm (yani makro içeren dosya) olarak kaydedilmelidir.
Umarım faydalı olur...
Formüllerin açıklaması
B kolonunda SayfaAdedine göre sıra listesi yapılmaktadır. B5 hücresindeki formül
=EĞER(SATIRSAY($1:1)<=SyfAdet-1;SATIRSAY($1:1);"")
Burada SyfAdet Makro4 kodlarından GET.WORKBOOK (Türkçe olarak AL.ÇALIŞMAKİTABI) kullanılarak yapıldı
(SyfAdet-1 olarak yazılmasının sebebi Toplam sayfa adedi nden 1 eksik olarak (Örnekte 5 , ama listede 4 olması lazım. Çünkü AnaSayfa işlem sayfası olduğundan adetlerde yazdırılması istenmedi) yazdırılması için )
=SAYIYAÇEVİR(AL.ÇALIŞMAKİTABI(4)&DOLAYLI("IV65000")) --- SyfAdet Ad Tanımlaması
C kolonunda sayfa adlarını yazdırmak için
=EĞERHATA(İNDİS(Sayfa;B5+1);"")
Burada Sayfa Makro4 kodlarından GET.WORKBOOK (Türkçe olarak AL.ÇALIŞMAKİTABI) kullanılarak yapıldı
(B5+1 olarak yazılmasının sebebi ilk sayfa işlem sayfası olan AnaSayfa ve bunun yazılmasını istemiyoruz. Çünkü diğer sayfalardan toplam alıp bu sayfada işlem yapacağız)
=PARÇAAL(AL.ÇALIŞMAKİTABI(1);BUL("]";AL.ÇALIŞMAKİTABI(1))+1;99)&DOLAYLI("IV65000") ---- Sayfa Ad Tanımlaması
Makro4 kodları sayfalarda direkt fonksiyon olarak kullanılamaz. Bunun için gerek Sayfa ve gerekse SyfAdet ad tanımlamaları yapılarak formüllerde kullanıldı. Bununiçin Bak Formüller > Ad Yöneticisi.... Detay için GET.WORKBOOK internette arayarak incelemeniz i öneririm. Şunu söylemek isterim formüllerdeki &DOLAYLI("IV65000") ifadesi "" değer üretecek olup metne çevirmek için kullanılmaktadır.
Aylık toplamların alınmasında , örneğin E5 hücresi (Ocak ve A sayfası)
=EĞERHATA(TOPLA.ÇARPIM((DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))>=TARİH($E$2;AY(E$3&1);1))*(DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))<=TARİH($E$2;AY(E$3&1)+1;0))*(DOLAYLI("'"&$C5&"'!k6:k"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))));"")
Sanıyorum , Bu formülün
DOLAYLI("'"&$C5&"'!B6:B"&EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6))
bu kısmını incelemek yeterli olacaktır.
EĞERHATA(KAÇINCI(9,9999E+307;DOLAYLI("'"&$C5&"'!B:B"));6)
ile KAÇINCI(9,9999e307 .... ifadesi Excelin tanıdığı en büyük sayı olan 9,99999999 e 307 nin kullanılması ile en son dolu hücrenin kaçıncı satırda olduğunu bulmak için kullanıldı. DOLAYLI("'"&$C5&"'!B:B")) burada C5 de yazan (ki, A sayfasıdır. ) sayfa adının B kolonunun dikkate alınmasıdır. Yani kaçıncı(......;dolaylı(..... ifadesiyle de , C5 de adı yazan sayfanın B kolonundaki içerisinde değer ( değer mutlaka sayı olmalı çünkü 9,9999 ile sayıları arattırıyoruz) bulunan satır numarasını bulmak .... Dolayısıyla bunu kullanarak Sayfalar DİNAMİK olarak değerlendirildi. Diğer bir deyişle A , B , C, .... sayfalarına aşağıya doğru istediğiniz sayıda veri girebilirsiniz.. Formüllerde değişiklik gerekmeyecektir. Eğerhata ile de sayfada B kolonunda sayı yoksa, ya da sayfa var ama henüz değer girilmemişse, koşulunuz olan 6 satıra bağlama işlemi yapıldı. Yani hata olursa 6 sayısı gelsin istedik.
Eğerhatanın önündeki DOLAYLI("'"&$C5&"'!B6:B"& kısmına baktığımızda ise, C5 te adı yazan sayfanın B6:B... ... kısmına da eğerhata ile başlayan formülde bulduğumuz sayı gelecek... (Örnek olarak bakalım A sayfası için , Eğerhata(.....) formülü bize B kolonundaki son değer olan 10. satır olduğundan , 10 değerini üretecektir. DOLAYLI("'"&$C5&"'!B6:B"& ... ifadesinde .... yerine 10 geleceğinden DOLAYLI("'"&$C5&"'!B6:B"& 10 şeklinde olacak ve bu da A!B6:B10 şeklinde hücre aralığı değerini üretecektir.
Sonradan ilave Not : Sayfada herhangi bir VBA kodu olmamasına rağmen, Makro4 kodlarından yararlanılarak yapılan ad tanımlamalarından dolayı sayfa .xlsm (yani makro içeren dosya) olarak kaydedilmelidir.
Umarım faydalı olur...
Son düzenleme:
Vermiş olduğunuz cevap beni mutlu etti.
Şahsım adına şunu söylemek istiyorum;
çözüm formülünüz sadece sorunun cevabını yansıtmıyor.
Ayrıca bir eğitim gibi olmuş.
Samimiyetimle söylüyorum, burada geçen formülleri bilen bilmeyen herkesin örnek tablo ile birlikte incelemesi gerekir.
Mesleği finans ve muhasebe olan herkesin bu tabloyu açıklamaları ile birlikte arşivlemesi gerekir.
Kodların içinde anlamadığım
bu kodun görevi nedir. K6 hücresi boş, dolması yani, yeni verinin gelmesi de muhtemel bir durum.
Şahsım adına şunu söylemek istiyorum;
çözüm formülünüz sadece sorunun cevabını yansıtmıyor.
Ayrıca bir eğitim gibi olmuş.
Samimiyetimle söylüyorum, burada geçen formülleri bilen bilmeyen herkesin örnek tablo ile birlikte incelemesi gerekir.
Mesleği finans ve muhasebe olan herkesin bu tabloyu açıklamaları ile birlikte arşivlemesi gerekir.
Kodların içinde anlamadığım
Kod:
(DOLAYLI("'"&$B35&"'!k6:k"&bu kodun görevi nedir. K6 hücresi boş, dolması yani, yeni verinin gelmesi de muhtemel bir durum.
selam,
#11 mesajımda DOLAYLI("'"&$C5&"'!B6:B"& örneğini açıklamıştım.
her ne kadar DOLAYLI("'"&$B35&"'!k6:k"& formüllerin içerisinde yer almasa da ( yazım hatası olabilir) mantık açıkladığım örnekle aynı...
Yine de DOLAYLI("'"&$B35&"'!k6:k"& açıklamaya çalışacağım...
ilk Sorunuza baktığımızda her sayfanın B6 dan başlayıp aşağıya doğru olan tarih aralığında K6 dan başlayan ilgili (karşılık gelen) satırlarının toplamını almak... olduğundan formüller de "B6:B"&.... veya "K6:K"& şeklinde oluşturuldu.. Yine #11 mesajda B6:B"& ya da K6:K"& ifadelerindeki & dan sonra gelecek B kolonundaki son Tarihin bulunduğu satır numarası gelecektir. Örnek dosyada bu değer 10 no lu satır olduğundan "B6:B"&10 (ya da "K6:K"&10) bize B6:B10 (ya da k6:K10) olarak değer alacaktır.
DOLAYLI("'"&$B35&"'!k6:k"& dediğimizde ise (örnekle anlatırsak B35 de Z sayfası olsun. & dan sonra 10 geliyor olsun .... DOLAYLI("'"&$B35&"'!k6:k"& = 'Z'!K6:K10 şeklinde olacaktır. Yani Z sayfasının K kolonundaki 6. satırdan 10.satıra kadarki olan Aralık..)
K6 hücresi boş olsa da , içindeki veri değişse de, yeni veri yazılsa da (yeter ki SAYI olsun) sonuçta formül gereği toplama işlemine tabi tutulacağından toplam sonucu ne ise sonuç o olacaktır.
Umarım anlatabilmişimdir..
#11 mesajımda DOLAYLI("'"&$C5&"'!B6:B"& örneğini açıklamıştım.
her ne kadar DOLAYLI("'"&$B35&"'!k6:k"& formüllerin içerisinde yer almasa da ( yazım hatası olabilir) mantık açıkladığım örnekle aynı...
Yine de DOLAYLI("'"&$B35&"'!k6:k"& açıklamaya çalışacağım...
ilk Sorunuza baktığımızda her sayfanın B6 dan başlayıp aşağıya doğru olan tarih aralığında K6 dan başlayan ilgili (karşılık gelen) satırlarının toplamını almak... olduğundan formüller de "B6:B"&.... veya "K6:K"& şeklinde oluşturuldu.. Yine #11 mesajda B6:B"& ya da K6:K"& ifadelerindeki & dan sonra gelecek B kolonundaki son Tarihin bulunduğu satır numarası gelecektir. Örnek dosyada bu değer 10 no lu satır olduğundan "B6:B"&10 (ya da "K6:K"&10) bize B6:B10 (ya da k6:K10) olarak değer alacaktır.
DOLAYLI("'"&$B35&"'!k6:k"& dediğimizde ise (örnekle anlatırsak B35 de Z sayfası olsun. & dan sonra 10 geliyor olsun .... DOLAYLI("'"&$B35&"'!k6:k"& = 'Z'!K6:K10 şeklinde olacaktır. Yani Z sayfasının K kolonundaki 6. satırdan 10.satıra kadarki olan Aralık..)
K6 hücresi boş olsa da , içindeki veri değişse de, yeni veri yazılsa da (yeter ki SAYI olsun) sonuçta formül gereği toplama işlemine tabi tutulacağından toplam sonucu ne ise sonuç o olacaktır.
Umarım anlatabilmişimdir..