Excel'de alt toplamları hariç tutarak veya dahil etmeden değerler nasıl toplanır?
Excel'de veri setleriyle çalışırken, farklı gruplar veya kategoriler için alt toplamları hesaplayarak sayılarınızı düzenlemeniz ve analiz etmeniz yaygındır. Ancak bu alt toplamları ekledikten sonra, nihai toplamda herhangi bir çift sayımın önlenmesi için bu alt toplam değerlerini hariç tutan bir genel toplam hesaplamak isteyebilirsiniz. Sorun şu ki, tüm sayıları doğrudan topladığınızda, alt toplamlar genel toplama dahil edilir ve sonuçta istenenden daha büyük bir toplam elde edilir. Bunu önlemek için, alt toplam satırlarını hariç tutarak orijinal verileri toplamanız gerekir. Bu makalede, bu sorunu çözmek için birkaç pratik yöntem sunuyoruz, böylece farklı senaryolarda doğru sonuçları verimli bir şekilde hesaplayabilirsiniz.
Excel'de Topla fonksiyonu ile alt toplamları olmadan değerleri toplama
Excel'de Alt Toplam fonksiyonu ile alt toplamları olmadan değerleri toplama
VBA Kodu - Alt toplam satırlarını hariç tutarak değerleri toplama
Filtreleme - Filtre ve ALTTOPLAM kullanarak yalnızca görünür (alt toplam olmayan) satırları toplama
Excel'de Topla fonksiyonu ile alt toplamları olmadan değerleri toplama
Grup alt toplamlarını hesaplamak için yaygın olarak kullanılan bir yaklaşım, her ilgili grup içinde veya altına TOPLA fonksiyonunu eklemektir. Ancak, bu grup alt toplam satırlarını görmezden gelerek tüm listenin genel toplamını hesaplamak istediğinizde, genellikle bir ek adım gereklidir. İşte bunu başarmak için kullanabileceğiniz bir yöntem:
1. Aşağıdaki ekran görüntüsünde gösterildiği gibi, her grupta TOPLA fonksiyonunu kullanarak ilgili alt toplamları oluşturun:
2. Ardından, bu alt toplam satırlarını dahil etmeden genel toplamı bulmak için, sonucun görünmesini istediğiniz boş bir hücreye aşağıdaki formülü girin:
=TOPLA(B2:B21)/2
Doğru toplamı elde etmek için Enter tuşuna basın. Bu formül, ilgili grupların hemen ardından TOPLA fonksiyonu ile alt toplamlar eklediğinizi varsaydığı için işe yarar; bu da ham verilerin toplamını etkili bir şekilde ikiye katlar. 2'ye bölerek yinelenen toplamı ortadan kaldırır. Bu yöntemin, orijinal değerlerin ve alt toplamların toplamının orijinal verilerin toplamının tam olarak iki katı olduğu durumlarda en uygun olduğunu unutmayın.
Veri yapınız daha karmaşık ise veya alt toplam satır sayınız değişiyorsa, aşağıda açıklanan alternatif yöntemler daha doğru ve esnek bir çözüm sunabilir.
Excel'de alt toplamları hariç tutarak değerleri toplama
Excel'deki ALTTOPLAM fonksiyonu, filtrelenmiş veya görünür satırlar üzerinde hesaplama yapmak için gömülü bir yol sağlar. Bu, ALTTOPLAM formüllerinin aralıktaki diğer ALTTOPLAM sonuçlarını görmezden gelmek üzere yapılandırılabilmesi nedeniyle, alt toplamlar içeren listelerle çalışırken özellikle kullanışlıdır; böylece değerlerin çift sayılımasını önler.
Bu bağlamda ALTTOPLAM fonksiyonunu kullanmak için şu adımları izleyin:
1. Her grup için aşağıdaki gibi ALTTOPLAM fonksiyonunu girin:
=ALTTOPLAM(9;B2:B10)
Bu, belirtilen aralığa TOPLA işlemi uygular (fonksiyon numarası 9) ve genellikle Excel’in yerleşik Veri > Alt Toplam özelliğinin bir parçası olarak kullanılır.
2. İç alt toplamları hariç tutan genel toplam için, seçtiğiniz hücreye aşağıdaki formülü girin:
=ALTTOPLAM(9;B2:B21)
Enter tuşuna basarak aralıktaki iç içe geçmiş ALTTOPLAM fonksiyonlarını görmezden gelerek veri değerlerini otomatik olarak toplayın. Bu yaklaşım, grup özetleri için ALTTOPLAM fonksiyonunu kullanan listeler için uygundur ve alt toplamları birden fazla kez toplama riskini ortadan kaldırır.
ALTTOPLAM fonksiyonunun, alt toplam hücreleri TOPLA yerine ALTTOPLAM kullanılarak oluşturulduğunda en iyi şekilde çalıştığını unutmayın. Ayrıca, satırları filtrelerseniz veya gizlerseniz, ALTTOPLAM yalnızca görünür (gizli olmayan) verileri toplamak üzere ayarlanabilir; bu da dinamik raporlama durumlarında yardımcı olur.
VBA Kodu - Alt toplam satırlarını hariç tutarak değerleri toplama
Veri yapınız karmaşıksa veya yalnızca ham veri satırlarını (alt toplamlar hariç) toplamak için otomatikleştirilmiş bir yönteme ihtiyacınız varsa, basit bir VBA makrosu kullanabilirsiniz. Bu yaklaşım, programlı olarak kriterlerinize uyan satırları tanımlamanıza ve toplamanıza olanak tanır; örneğin belirli biçimlendirme, formül içermesi veya diğer ayırt edici özelliklere göre.
Örneğin, alt toplam satırlarınız formüllerle hesaplanıyorsa (örneğin TOPLA veya ALTTOPLAM) ve ham veri satırlarınız yalnızca statik değerler içeriyorsa, VBA kodunu yalnızca formül içermeyen belirli bir sütundaki hücreleri toplamak üzere ayarlayabilirsiniz. İşte bunu nasıl yapabileceğiniz:
1. Tıklayın Geliştirici Araçları > Visual Basic Microsoft Visual Basic for Applications penceresini açmak için.
2. Yeni pencerede tıklayın Ekle > Modül ve aşağıdaki kodu modüle yapıştırın:
Sub SumNonSubtotalRows()
Dim WorkRng As Range
Dim SumResult As Double
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to sum (e.g., B2:B21)", xTitleId, WorkRng.Address, Type:=8)
SumResult = 0
For Each cell In WorkRng
If Not cell.HasFormula Then
SumResult = SumResult + cell.Value
End If
Next
MsgBox "The sum of non-subtotal rows is: " & SumResult, vbInformation, xTitleId
End Sub3. Tıklayın 
Çalıştır düğmesine kodu çalıştırmak için. Seçiminizdeki veri aralığını seçmeniz için bir istem belirecek. Makro, yalnızca formül içermeyen hücreleri toplayarak tipik alt toplam satırlarını atlayacaktır.
Filtreleme - Filtre ve ALTTOPLAM kullanarak yalnızca görünür (alt toplam olmayan) satırları toplama
Veri setiniz, alt toplam satırlarını filtrelemenize izin veriyorsa (örneğin, onları tanımlamak için bir etiket, anahtar kelime veya desen varsa), Excel'in yerleşik Filtre özelliği ile ALTTOPLAM fonksiyonunu birlikte kullanarak yalnızca görünür (filtrelenmemiş) veri satırlarını ekleyebilirsiniz. Bu teknik, alt toplamların tutarlı bir göstergesi veya etiketi olduğunda pratiktir.
1. Veri setinizde herhangi bir yere tıklayın, ardından Veri sekmesine gidin ve Filtre tıklayarak sütunlarınız için filtreleri etkinleştirin.
2. Yardımcı sütununuzdaki açılır menüyü tıklayın, "Alt Toplam" seçeneğini kaldırarak alt toplam satırlarını gizleyin ve yalnızca orijinal verileri görüntüleyin.
3. Görünür (filtrelenmiş) satırları toplamak için ayrı bir hücreye aşağıdaki formülü girin.
=SUBTOTAL(9,B2:B21)ALTTOPLAM fonksiyonu (TOPLA için fonksiyon numarası 9), gizli satırları görmezden gelecek ve yalnızca şu anda görünür olan satırların toplamını (yani, filtrelemeden sonra bıraktığınız alt toplam olmayan satırlar) verecektir. Bu çözüm hızlı ve dinamiktir—filtreleri yeniden uygularsanız veya veri setinizi genişletirseniz, formül buna göre güncellenir.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin