Excel'de kriterlere göre yalnızca görünür hücreleri nasıl toplarsınız?
Excel’de kullanıcılar genellikle SUMIFS işlevini kullanarak belirli kriterlere göre hücreleri toplayabilir. Ancak filtrelenmiş veriler üzerinde çalışırken SUMIFS işlevi, hem görünür hem de gizli hücreleri hesaplamaya dahil eder. Bu durum — özellikle aşağıda ekran görüntüsünde gösterildiği gibi — yalnızca belirli kriterlere uyan görünür (yani süzülmüş) hücrelerin toplanması gerektiği zaman yanlış sonuçlara yol açar.
Yalnızca görünür hücreleri bir veya daha fazla kritere göre yardımcı sütun kullanarak toplayın
Yalnızca görünür hücreleri bir veya daha fazla kritere göre formül kullanarak toplayın
Yalnızca görünür hücreleri kriterlere göre VBA kodu kullanarak toplayın
Günlük raporlama ve veri analizi iş akışlarında, örneğin belirli filtreler uygulandıktan sonra bir ürün ya da kategoriye ilişkin satış tutarlarını hesaplarken, süzülmüş tablolardaki verileri doğru şekilde toplamak büyük önem taşır. Yanlış yapmanız, istemediğiniz verileri de içeren yanıltıcı toplamlara neden olabilir; bu yüzden yalnızca ekranda gördüğünüz görünür verileri toplayan teknikleri kullanmanız kritiktir.
Bu makale, farklı senaryolar ve yetkinlik düzeyleri için uygun çeşitli pratik yöntemleri sunmaktadır; her yöntemin kendine özgü avantajları ve olası sınırlamaları vardır. Çalışma sayfanızın boyutuna, veri yapısına ve operasyonel alışkanlıklarınıza en iyi şekilde uygun olan çözümü kolayca seçebilirsiniz. Aşağıda, her çözüm için adım adım uygulama yönergeleri, karşılaşılabilen hataların açıklamaları ve daha güvenilir sonuçlar elde etmek adına hesaplama sürecini optimize etme yolları yer almaktadır.
Yalnızca görünür hücreleri bir veya daha fazla kritere göre yardımcı sütun kullanarak toplayın
Belirli kriterlere göre görünür hücreleri toplamanın en sezgisel ve güvenilir yollarından biri, yalnızca görünür satırlar için bir değer döndüren yardımcı bir sütun kullanmak ve ardından bu sütunu istediğiniz koşullarla birlikte SUMIFS işlevinde değerlendirmektir. Bu yöntem, veri kümenizin sık sık farklı şekillerde süzülmesi durumunda ya da meslektaşlarınızın kolayca anlayabileceği ve üzerinde değişiklik yapabileceği hesaplamalar oluşturmanız gerektiğinde özellikle etkilidir.
Avantajlar: Kurulumu son derece kolaydır; tüm mantık ve hesaplamalar çalışma sayfasında şeffaf bir şekilde görünür kalır; küçük ila orta ölçekli tablolar için idealdir; formüllerinizi ayarlamanız veya denetlemeniz gerektiğinde ise son derece sağlam bir yapı sunar.
Sınırlamalar: Ekstra sütunlar oluşturur; satır düzeni değişirse formüllerin güncellenmesi gerekir; çok büyük veri kümelerinde aşırı kullanımı zahmetli hale gelebilir.
Örneğin, bir Filtre Aralığı içinde "Hoodie" ürününe ait sipariş değerlerini yalnızca toplamak için:
1. Veri kümenizin yanındaki boş bir sütuna (örneğin, D değer sütunu varsayılarak E2 hücresine) aşağıdaki formülü girin veya kopyalayın:
Formülü Veri Aralığı’ndaki tüm satırlara uygulamak için doldurma tutamacını aşağı doğru sürükleyin. Bu formül, satır görünür durumdaysa D sütunundaki değeri; satır süzmeyle gizlenmişse 0 değerini döndürür.
2. E sütununda yardımcı değerleri oluşturduktan sonra, yalnızca kriterlerinize göre görünür hücreleri toplamak için SUMIFS işlevini kullanın. Örneğin, A sütununda "Hoodie" için:
SUMIFS bağımsız değişkenlerini =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], …) biçiminde genişleterek daha fazla kriter ekleyebilirsiniz. Her zaman aralıklarınızın doğru hizalandığından ve beklenen sonuçları alabilmek için bunları kontrol ettiğinizden emin olun.
Dikkat: Formüllerinizi ayarladıktan sonra satırları yeniden düzenler, ekler veya silerseniz, tüm başvuruların hâlâ veri yapınıza uygun olduğundan emin olun. Hizalanmamış aralıklar ya da kriter hücrelerinin güncellenmemesi bazen hatalara yol açabilir.
Kriterlere göre yalnızca görünür hücreleri formülle toplayın
Formül tabanlı, yardımcı sütun gerektirmeyen bir çözüm arıyorsanız, görünür hücreleri belirli kriterlere göre toplamak için SUMPRODUCT, SUBTOTAL, OFFSET, ROW ve MIN işlevlerini birlikte kullanabilirsiniz. Bu yöntem, dizi formüllerine aşina deneyimli Excel kullanıcıları için idealdir ve ekstra sütunlar eklemeksizin sayfanızın düzenli kalmasını sağlar.
Avantajlar: Ekstra çalışma sayfası sütunlarına gerek yoktur; esnektir ve dinamiktir; filtrelediğinizde veya kriterleri değiştirdiğinizde formül anında güncellenir.
Sınırlamalar: Özellikle dizi formüllerine aşina olmayan kullanıcılar için bu formüllerin okunması veya hata ayıklanması zor olabilir; ayrıca çok büyük tablolarda performans düşüşü yaşanabilir.
Boş bir hücreye (örneğin, A2:A12 içinde "Hoodie" için görünür hücreleri toplamak üzere, Gerçek Değer D2:D12 içinde ve kriter A17'de olacak şekilde) aşağıdaki formülü kopyalayın veya girin:
Formülü girdikten sonra, aşağıdaki gibi istenen sonucu almak için Entertuşuna basın:
Dikkat: Bu yaklaşım belirtilen aralıklara son derece duyarlıdır—uyumsuz veya çakışan aralıklar hatalara ya da beklenmeyen sonuçlara yol açabilir. Özellikle süzme işlemi görünür satırların sayısını veya konumunu değiştirdiğinde uç durumları mutlaka test edin.
Yalnızca görünür hücreleri kriterlere göre VBA kodu kullanarak toplayın
Gelişmiş kullanıcılar için VBA kullanımı, standart formüllerin performans darboğazlarına uğradığı, çoklu koşullu mantığın tek bir formülle ifade edilmesinin zor olduğu karmaşık senaryolarda veya büyük veri kümelerinde belirli kriterlere göre yalnızca görünür hücreleri toplamak gerektiğinde esnek bir çözüm sunar. VBA, her görünür satırı sırayla inceleyebilir, tanımladığınız koşulları değerlendirebilir ve toplamı verimli şekilde hesaplayabilir. Bu yaklaşım, özellikle tekrarlayan raporlama görevlerinde veya özet hesaplamaların otomatikleştirilmesinde idealdir.
Avantajlar: Büyük veri kümelerini, çoklu ya da dinamik kriterleri ve karmaşık mantığı zahmetsizce işler; binlerce satır içeren verilerde bile işlemler hızla tamamlanır; manuel formül değişikliklerinden kaynaklanan hata riskini en aza indirir.
Sınırlamalar: Makroların etkinleştirilmesini gerektirir; bazı kullanıcılar VBA ile tanışık olmayabilir veya gerekli izinlere sahip olmayabilir. Değişiklikler, Makro Düzenleyici’ye erişim gerektirir. Önemli veri kümelerinde VBA çalıştırmadan önce her zaman bir yedek alın.
1. Başlamak için VBA Düzenleyici penceresini açın; bunun için Geliştirici Araçları > Visual Basic seçeneğine tıklayın. Açılan pencerede Ekle > Modül yolunu izleyin ve aşağıdaki kodu yeni modüle yapıştırın:
Sub SumVisibleByCriteria()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim criteriaColumn As Range
Dim sumColumn As Range
Dim criteriaValue As Variant
Dim total As Double
Dim lastRow As Long
Dim criteriaColNum As Integer
Dim sumColNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Prompt user for criteria column and sum column
Set criteriaColumn = Application.InputBox("Select the criteria range (e.g., A2:A100):", xTitleId, Type:=8)
Set sumColumn = Application.InputBox("Select the values range to sum (e.g., D2:D100):", xTitleId, Type:=8)
criteriaValue = Application.InputBox("Enter the criteria value to match:", xTitleId, Type:=2)
If criteriaColumn Is Nothing Or sumColumn Is Nothing Or criteriaValue = "" Then
MsgBox "Operation cancelled.", vbInformation, xTitleId
Exit Sub
End If
If criteriaColumn.Rows.Count <> sumColumn.Rows.Count Then
MsgBox "Criteria and sum ranges must be the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
total = 0
For Each cell In criteriaColumn
If Not cell.EntireRow.Hidden Then
If cell.Value = criteriaValue Then
total = total + sumColumn.Cells(cell.Row - criteriaColumn.Cells(1).Row + 1).Value
End If
End If
Next cell
MsgBox "The sum of visible cells matching the criteria is: " & total, vbInformation, xTitleId
End Sub2. Kodu çalıştırmak için 
"Çalıştır" düğmesine tıklayın (veya)F5 tuşuna basın). Bir iletişim kutusu, kriter aralığınızı (örneğin ürün adlarınızı), toplanacak değer aralığınızı ve filtre olarak istediğiniz değeri (örneğin "Hoodie") seçmenizi isteyecektir. Makro, kriterinize uyan yalnızca görünür satırları toplayacak ve sonucu bir açılır iletiyle gösterecektir.
Pratik ipuçları: Verilerinizi veya filtrelerinizi değiştirdikten sonra toplamlarınızı sıkça yeniden hesaplamanız gerekiyorsa, bu VBA kodunu kullanın. Birden fazla kriter için çalışacak şekilde VBA kodunu daha da genişletebilirsiniz; bunun için ek giriş istemleri veya mantıksal koşullar ekleyin.
Sorun Giderme: Kriter ve değerler için seçtiğiniz aralıkların aynı sayıda satıra sahip olduğundan ve süzülmüş verilerinizle aynı sütunlarda yer aldığundan her zaman emin olun. Kod bir hata bildirirse ya da beklediğiniz toplamı döndürmüyorsa, filtre ayarlarınızı ve etkin seçiminizi tekrar gözden geçirin.
Özet öneriler: Yalnızca görünür hücreler üzerinde tekrarlanan hesaplamalar gerektiren Veri Analizi iş akışlarınız için bu makroyu Kişisel Makro Çalışma Kitabınızda saklamak, günlük raporlama süreçlerinizi önemli ölçüde hızlandırabilir. İletişim kutusu görünmüyorsa, lütfen makro ayarlarınızı ve güvenlik izinlerinizi kontrol edin.
En İyi Office Üretkenlik Araçları
Kutools for Excel ile Excel becerilerinizi üst seviyeye taşıyın ve hiç yaşamadığınız bir verimlilik deneyimi yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli arayüz getirir ve işlerinizi çok daha kolay hale getirir
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Birden fazla belgeyi yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açın ve oluşturun.
- Günlük üretkenliğinizi %50 artırır ve size her gün yüzlerce fare tıklamasından tasarruf sağlar!
Tüm Kutools eklentileri — tek bir yükleyiciyle!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint için eklentileri ve ayrıca Office Tab Pro’yu içerir; bu da Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek yükleyici, tek lisans— dakikalar içinde kurulum (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— tek tek eklenti satın alımına göre tasarruf edin