ETOPLA Formül kısaltma..

[pNouma]

Kıymetli Hocalarım merhaba

Bir dosyada Ocak ayından Kasım Ayına kadar aynı formatta sayfalar mevcut,
Aşağıdaki ETOPLA formül ile her bir sayfadaki verileri ayrı-ayrı alarak toplama yaptırıyorum;

bu formülün daha kısa yazılışı için bir yöntem olabilir mi?


=ETOPLA(Ocak!$B:$B;$A5;Ocak!$R:$R)+ETOPLA(Şubat!$B:$B;$A5;Şubat!$R:$R)+ETOPLA(Mart!$B:$B;$A5;Mart!$R:$R)+ETOPLA(Nisan!$B:$B;$A5;Nisan!$R:$R)+ETOPLA(Mayıs!$B:$B;$A5;Mayıs!$R:$R)+ETOPLA(Haziran!$B:$B;$A5;Haziran!$R:$R)+ETOPLA(Temmuz!$B:$B;$A5;Temmuz!$R:$R)+ETOPLA(Ağustos!$B:$B;$A5;Ağustos!$R:$R)+ETOPLA(Eylül!$B:$B;$A5;Eylül!$R:$R)+ETOPLA(Ekim!$B:$B;$A5;Ekim!$R:$R)+ETOPLA(Kasım!$B:$B;$A5;Kasım!$R:$R)


teşekkürler,
iyi Çalışmalar.

 

[tahsinanarat]

Bunun için belki bir fonksiyon yazılabilir. Örnek dosa eklerseniz daha çabuk sonuç alırsınız.

 

[tugkan]

aylık verileri otomatik olarak toplamak için bir VBA makrosu yazabiliriz. Bu makro, belirli bir hücredeki değeri tüm aylık sayfalarda arar ve eşleşen hücrelerdeki verileri toplar. Aşağıdaki VBA kodu, Ocak-Kasım arasındaki sayfaları dolaşarak bu işlemi gerçekleştirir.

VBA Makro Kodu:

1. Excel’de Alt + F11 tuşlarına basarak VBA düzenleyicisini açın.
2. Ekle > Modül yoluyla yeni bir modül ekleyin.
3. Aşağıdaki kodu kopyalayıp yapıştırın.

Function AylikToplam(KriterHücre As Range) As Double
    Dim SayfaIsimleri As Variant
    Dim Sayfa As Worksheet
    Dim Toplam As Double
    Dim Kriter As Variant
    
    ' Aylık sayfa isimlerini tanımlayın
    SayfaIsimleri = Array("Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım")
    Kriter = KriterHücre.Value
    Toplam = 0

    ' Her sayfayı dolaşarak kriteri arayın ve toplayın
    For Each Sayfa Is In ThisWorkbook.Sheets(SayfaIsimleri)
        Dim SonSatir As Long
        Dim i As Long

        SonSatir = Sayfa.Cells(Sayfa.Rows.Count, 2).End(xlUp).Row ' B sütunundaki son satırı bul
        For i = 1 To SonSatir
            If Sayfa.Cells(i, 2).Value = Kriter Then ' Eğer B sütunundaki değer kriterle eşleşiyorsa
                Toplam = Toplam + Sayfa.Cells(i, 18).Value ' R sütunundaki değeri topla
            End If
        Next i
    Next Sayfa

    AylikToplam = Toplam
End Function

Kullanım:

1. Makroyu kaydedin.
2. Çalışma sayfasına geri dönün ve formül kısmına şunu yazın:

=AylikToplam(A5)

Bu makro, A5 hücresindeki kriter değerini alarak, Ocak-Kasım arasındaki sayfalarda B sütununda bu kriteri arar ve R sütunundaki ilgili verileri toplar.

 

[pNouma]

aylık verileri otomatik olarak toplamak için bir VBA makrosu yazabiliriz. Bu makro, belirli bir hücredeki değeri tüm aylık sayfalarda arar ve eşleşen hücrelerdeki verileri toplar. Aşağıdaki VBA kodu, Ocak-Kasım arasındaki sayfaları dolaşarak bu işlemi gerçekleştirir.

VBA Makro Kodu:

1. Excel’de Alt + F11 tuşlarına basarak VBA düzenleyicisini açın.
2. Ekle > Modül yoluyla yeni bir modül ekleyin.
3. Aşağıdaki kodu kopyalayıp yapıştırın.

Function AylikToplam(KriterHücre As Range) As Double
    Dim SayfaIsimleri As Variant
    Dim Sayfa As Worksheet
    Dim Toplam As Double
    Dim Kriter As Variant
   
    ' Aylık sayfa isimlerini tanımlayın
    SayfaIsimleri = Array("Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım")
    Kriter = KriterHücre.Value
    Toplam = 0

    ' Her sayfayı dolaşarak kriteri arayın ve toplayın
    For Each Sayfa Is In ThisWorkbook.Sheets(SayfaIsimleri)
        Dim SonSatir As Long
        Dim i As Long

        SonSatir = Sayfa.Cells(Sayfa.Rows.Count, 2).End(xlUp).Row ' B sütunundaki son satırı bul
        For i = 1 To SonSatir
            If Sayfa.Cells(i, 2).Value = Kriter Then ' Eğer B sütunundaki değer kriterle eşleşiyorsa
                Toplam = Toplam + Sayfa.Cells(i, 18).Value ' R sütunundaki değeri topla
            End If
        Next i
    Next Sayfa

    AylikToplam = Toplam
End Function

Kullanım:

1. Makroyu kaydedin.
2. Çalışma sayfasına geri dönün ve formül kısmına şunu yazın:

=AylikToplam(A5)

Bu makro, A5 hücresindeki kriter değerini alarak, Ocak-Kasım arasındaki sayfalarda B sütununda bu kriteri arar ve R sütunundaki ilgili verileri toplar.

Hocam teşekkürler, bunu formül ile çözmem lazım, kod yazma işinize girince sayfanın uzantısını falan değiştirmek gerekecek.

sanki aşağıdaki şekile benzer bir yöntem vardı, şimdi tam çıkaramadım, bir yerde görmüştüm diye hatırlıyorum.

ETOPLA{"Ocak","Şubat","Mart","Nisan","Mayıs","Haziran","Temmuz","Ağustos","Eylül","Ekim",Kasım"}(!$B:$B;$A5;Ocak!$R:$R)

tekrar teşekkürler,
iyi Çalışmalar dilerim.

 

[walabi]

Sanırım formül oluştururken sürekli sayfalara gitmek istemiyorsunuz. Formül oluşturduğunuz sayfada sayfa adlarını Dolaylı fonksiyonu ile hücreden aldırıp formülü buna göre çoğaltabilirsiniz.

 

[pNouma]

Sanırım formül oluştururken sürekli sayfalara gitmek istemiyorsunuz. Formül oluşturduğunuz sayfada sayfa adlarını Dolaylı fonksiyonu ile hücreden aldırıp formülü buna göre çoğaltabilirsiniz.

Hocam tüm sayfalardan veri çekme kriteleri aynı olduğu için;

formülde tekrar eden bölümleri (!$B:$B;$A5;Ocak!$R:$R) her seferde yazmak yerine bir defa yazarak formül kısaltılabilir mi diye düşünüyorum.

ilgi ve alakanız için teşekkürler,
iyi Çalışmalar.

 

[Korhan Ayhan]

Formülü aşağıdaki gibi kısaltabilirsiniz.

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&{"Ocak";"Şubat";"Mart";"Nisan";"Mayıs";"Haziran";"Temmuz";"Ağustos";"Eylül";"Ekim";"Kasım"}&"'!B:B");$A5;DOLAYLI("'"&{"Ocak";"Şubat";"Mart";"Nisan";"Mayıs";"Haziran";"Temmuz";"Ağustos";"Eylül";"Ekim";"Kasım"}&"'!R:R")))

Ama mantıklı olanı ay isimlerini uygun bir aralığıa yazarak formülde kullanmak olur... Ben ay isimlerini Z1:Z11 hücrelerine yazarak kurguladım..

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&$Z$1:$Z$11&"'!B:B");$A5;DOLAYLI("'"&$Z$1:$Z$11&"'!R:R")))

 

[Erdem Akdemir]

Alternatif ,

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&METNEÇEVİR(TARİH(1;SATIR(1:12);1);"aaaa")&"'!B:B");$A5;DOLAYLI("'"&METNEÇEVİR(TARİH(1;SATIR(1:12);1);"aaaa")&"'!R:R")))

 

[pNouma]

Formülü aşağıdaki gibi kısaltabilirsiniz.

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&{"Ocak";"Şubat";"Mart";"Nisan";"Mayıs";"Haziran";"Temmuz";"Ağustos";"Eylül";"Ekim";"Kasım"}&"'!B:B");$A5;DOLAYLI("'"&{"Ocak";"Şubat";"Mart";"Nisan";"Mayıs";"Haziran";"Temmuz";"Ağustos";"Eylül";"Ekim";"Kasım"}&"'!R:R")))

Ama mantıklı olanı ay isimlerini uygun bir aralığıa yazarak formülde kullanmak olur... Ben ay isimlerini Z1:Z11 hücrelerine yazarak kurguladım..

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&$Z$1:$Z$11&"'!B:B");$A5;DOLAYLI("'"&$Z$1:$Z$11&"'!R:R")))

Çok teşekkür ederim Korhan Hocam
iyi çalışmalar.

 

[pNouma]

Korhan Hocam tekrar merhaba,
benzeri yöntemle; aşağıdaki formülü de kısaltmamız mümkün müdür?

Aylar' sayfasının "T" sütununda ortalama değerler yazılı, bu değerlerin personel bazlı ("B" sütunu) aylık bazda ortalamasını almak;

sonrasında da tüm aylara göre yılın ortalamasını almak...

=(EĞERHATA(EĞERORTALAMA(Ocak!$B$3:$B$1000;$A5;Ocak!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Şubat!$B$3:$B$1000;$A5;Şubat!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Mart!$B$3:$B$1000;$A5;Mart!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Nisan!$B$3:$B$1000;$A5;Nisan!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Mayıs!$B$3:$B$1000;$A5;Mayıs!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Haziran!$B$3:$B$1000;$A5;Haziran!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Temmuz!$B$3:$B$1000;$A5;Temmuz!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Ağustos!$B$3:$B$1000;$A5;Ağustos!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Eylül!$B$3:$B$1000;$A5;Eylül!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Ekim!$B$3:$B$1000;$A5;Ekim!$T$3:$T$1000);0)+EĞERHATA(EĞERORTALAMA(Kasım!$B$3:$B$1000;$A5;Kasım!$T$3:$T$1000);0))/11/100

desteğiniz için şimdiden teşekkür eder, iyi Çalışmalar dilerim.

 

[Korhan Ayhan]

Deneyiniz..

=TOPLA.ÇARPIM(ETOPLA(DOLAYLI("'"&Z1:Z11&"'!B:B");$A5;DOLAYLI("'"&Z1:Z11&"'!T:T")))/TOPLA.ÇARPIM(ÇOKEĞERSAY(DOLAYLI("'"&Z1:Z11&"'!B:B");$A5;DOLAYLI("'"&Z1:Z11&"'!T:T");"<>"))