Örnek çalışma kitabını indirin
Bu eğitici, Excel ve Google E-Tablolar'daki birden çok sayfada belirli ölçütleri karşılayan verileri toplamak için SUMPRODUCT ve SUMIFS İşlevlerinin nasıl kullanılacağını gösterecektir..
Birden Çok Sayfada Normal Toplam
Bazen verileriniz bir Excel dosyasındaki birkaç çalışma sayfasına yayılabilir. Bu, periyodik olarak toplanan veriler için yaygındır. Çalışma kitabındaki her sayfa, belirli bir zaman aralığı için veri içerebilir. İki veya daha fazla sayfada bulunan verileri toplayan bir formül istiyoruz.
SUM İşlevi, birden çok sayfadaki verileri kolayca toplamanıza olanak tanır. 3D Referans:
| 1 | =TOPLA(Sayfa1:Sayfa2!A1) |
Ancak, SUMIF işlevi ile bu mümkün değildir. Bunun yerine daha karmaşık bir formül kullanmalıyız.
Birden Çok Sayfa Arasındaysa Toplamı
Bu örnek özetleyecek Planlanan Teslimat Sayısı her biri için Müşteri SUMIFS, SUMPRODUCT ve DOLAYLI İşlevlerini kullanarak, her biri farklı bir aya ilişkin verileri tutan birden çok çalışma sayfasında:
| 1 | =TOPLAÇA(TOPLA(DOLAYLI("'"&F3:F6&"'!"&"D3:D7")),DOLAYLI("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Bu formülün üzerinden geçelim.
Adım 1: Yalnızca 1 Giriş Sayfası için bir SÜMIFS Formülü oluşturun:
Toplama için SUMIFS İşlevini kullanırız. Planlanan Teslimat Sayısı tarafından Müşteri tek bir giriş veri sayfası için:
| 1 | =TOPLA(D3:D7;C3:C7;H3) |
Adım 2: Formüle Sayfa Referansı Ekleyin
Formül sonucunu aynı tutuyoruz, ancak giriş verilerinin adlı sayfada olduğunu belirtiyoruz. 'Adım 2'
| 1 | =TOPLA('Adım 2'!D3:D7,'Adım 2'!C3:C7,H3) |
3. Adım : Bir SUMPRODUCT İşlevinin İçine Yuvalayın
Birden çok sayfa üzerinde SUMIFS hesaplamaları yapmak üzere formülü hazırlamak ve ardından sonuçları bir araya toplamak için formülün etrafına bir SUMPRODUCT Fonksiyonu ekleriz.
| 1 | =TOPLAÇARI(TOPLA('Adım 3'!D3:D7,'Adım 3'!C3:C7,H3)) |
SUMIFS İşlevini bir sayfada kullanmak, tek bir değer verir. Birden çok sayfada, SUMIF işlevi bir dizi değer verir (her çalışma sayfası için bir tane). Bu dizideki değerleri toplamak için SUMPRODUCT İşlevini kullanırız.
Adım 4: Sayfa Referansını Sayfa Adları Listesi ile Değiştirin
değiştirmek istiyoruz Sayfa Adı değerleri içeren bir veri listesi içeren formülün bir parçası: Ocak, Şubat, Mart, ve Nisan. Bu liste F3:F6 hücrelerinde saklanır.
DOLAYLI İşlev, gösterilen metin listesinin Sayfa Adları SUMIFS İşlevinde geçerli bir hücre başvurusunun parçası olarak değerlendirilir.
| 1 | =TOPLAÇA(TOPLA(DOLAYLI("'"&F3:F6&"'!"&"D3:D7")),DOLAYLI("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Bu formülde, önceden yazılmış aralık referansı:
| 1 | 'Adım 3'!D3:D7 |
Şununla değiştirilir:
| 1 | DOLAYLI("'"&F3:F6&"'!"&"D3:D7") |
Tırnak işaretleri, formülün okunmasını zorlaştırır, bu nedenle burada boşluklar eklenerek gösterilmiştir:
| 1 | DOLAYLI (" ' " & F3:F6 & " ' ! " & "D3:D7") |
Bir hücre listesine başvurmanın bu yolunu kullanmak, sayısal bir liste stilini takip etmeyen birden çok sayfadaki verileri özetlememize de olanak tanır. Standart bir 3B referans, sayfa adlarının şu tarzda olmasını gerektirir: Girdi1, Girdi2, Girdi3, vb., ancak yukarıdaki örnek, herhangi bir Sayfa Adları ve ayrı bir hücrede başvuruda bulunmalarını sağlamak.
Hücre Referanslarını Kilitleme
Formüllerimizin okunmasını kolaylaştırmak için formülleri kilitli hücre referansları olmadan gösterdik:
| 1 | =TOPLAÇA(TOPLA(DOLAYLI("'"&F3:F6&"'!"&"D3:D7")),DOLAYLI("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Ancak bu formüller, dosyanızın başka bir yerine kopyalayıp yapıştırdığınızda düzgün çalışmayacaktır. Bunun yerine, aşağıdaki gibi kilitli hücre referansları kullanmalısınız:
| 1 | =TOPLA(TOPLA(TOPLA)(DOLAYLI("'"&$F$3:$F$6&"'!"&"D3:D7")),DOLAYLI("'"&$F$3:$F$6&"'!"& "C3:C7"),H3)) |
Daha fazla bilgi için Hücre Referanslarını Kilitleme hakkındaki makalemizi okuyun.
Google E-Tablolar'da Birden Çok Sayfada Olursa Toplamı
Bir SUMPRODUCT ve SUMIFS İşlevinde bir sayfa listesine başvuruda bulunmak için DOLAYLI İşlevini kullanmak şu anda Google E-Tablolar'da mümkün değildir.
Bunun yerine, her bir giriş sayfası ve birlikte eklenen sonuçlar için ayrı SUMIFS hesaplamaları yapılabilir:
| 1234 | =TOPLA(Ocak!D3:D7,Oca!C3:C7,H3)+TOPLA(Şubat!D3:D7,Şubat!C3:C7,H3)+TOPLA(Mar!D3:D7,Mar!C3:C7,H3)+TOPLA(Nis!D3:D7,Nis!C3:C7,H3) |
