Excel'de belirli kriterlere göre yalnızca görünür hücreleri toplamak nasıl yapılır?
Excel'de kullanıcılar genellikle SUMIFS fonksiyonunu kullanarak belirli kriterlere göre hücreleri toplayabilirler. Ancak, filtrelenmiş verilerle uğraşırken, sadece SUMIFS uygulamak hem görünür hem de gizli hücreleri hesaplamaya dahil edecektir. Özellikle yalnızca belirli kriterlere uyan görünür (yani, filtrelenmemiş) hücreleri toplamanız gerekiyorsa bu durum yanlış sonuçlara yol açabilir, aşağıda gösterilen ekran görüntüsünde olduğu gibi.
Yardımcı bir sütun kullanarak bir veya daha fazla kritere göre yalnızca görünür hücreleri toplama
Bir formül kullanarak bir veya daha fazla kritere göre yalnızca görünür hücreleri toplama
VBA kodu kullanarak kritere göre yalnızca görünür hücreleri toplama
Günlük raporlama ve veri analizi iş akışlarında, filtrelenmiş tablolarda verileri doğru bir şekilde toplamak yaygın bir ihtiyaçtır; örneğin, bazı filtreler uyguladıktan sonra belirli bir ürün veya kategori için satış tutarlarını hesaplarken. Bunu yanlış yapmak, istemediğiniz verileri içeren toplamlara yol açabilir, bu nedenle ekranda gördüğünüz yalnızca görünür verileri toplayan teknikleri kullanmak önemlidir.
Bu makale, farklı senaryolar ve yetkinlik seviyeleri için uygun olan çeşitli pratik yöntemleri tanıtmaktadır; her birinin avantajları ve olası sınırlamaları bulunmaktadır. Çalışma sayfanızın boyutu, veri yapısı ve operasyonel alışkanlıklarınıza en uygun çözümü seçebilirsiniz. Her bir çözüm için ayrıntılı adımlar, potansiyel hataların açıklamaları ve hesaplama sürecini daha güvenilir sonuçlar elde etmek için optimize etme yolları aşağıda verilmiştir.
Yardımcı bir sütun kullanarak bir veya daha fazla kritere göre yalnızca görünür hücreleri toplama
Belirli kriterlere göre yalnızca görünür hücreleri toplamanın en sezgisel ve kararlı yaklaşımlarından biri, yalnızca görünür satırlar için değer döndüren bir yardımcı sütun kullanmak ve ardından istediğiniz koşullarla SUMIFS fonksiyonundan yararlanmaktır. Bu, özellikle veri setiniz sık sık çeşitli şekillerde filtreleniyorsa veya kolayca anlaşılabilir veya değiştirilebilir hesaplamalar kurmanız gerekiyorsa oldukça etkilidir.
Avantajlar: Kurulumu kolay; tüm mantık ve hesaplamalar çalışma sayfasında görünür kalır; küçük ve orta büyüklükteki tablolar için idealdir; formülleri ayarlamak veya denetlemek gerektiğinde sağlamdır.
Sınırlamalar: Ekstra sütunlar oluşturur; satır düzeni değişirse formüllerin güncellenmesi gerekebilir; çok büyük veri setlerinde kapsamlı kullanımı ağırlaşabilir.
Örneğin, filtrelenmiş bir aralıktaki "Hoodie" ürününün sipariş değerlerini yalnızca toplamak için:
1. Veri setinizin yanındaki boş bir sütuna (örneğin, D değer sütunu ise, E2 hücresine) aşağıdaki formülü girin veya kopyalayın:
Formülü sürükleyerek tüm veri aralığında aşağıya kadar doldurun. Bu formül, eğer satır görünüyorsa D sütunundaki değeri döndürecek, eğer satır filtreleme ile gizlenmişse 0 döndürecektir.
2. E sütununda yardımcı değerleri oluşturduktan sonra, yalnızca görünür değerleri kriterlere göre toplamak için SUMIFS fonksiyonunu kullanın. Örneğin, A sütunundaki "Hoodie" için toplamak için:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], ...) biçiminde SUMIFS argümanlarını genişleterek daha fazla kriter ekleyebilirsiniz. Doğru hizalama ve beklenen sonuçlar için her zaman aralıklarınızı kontrol edin.
Dikkat: Formüllerinizi kurduktan sonra satırları yeniden düzenlerseniz, eklerseniz veya silerseniz, tüm referansların veri yapınızla eşleştiğinden emin olmak için tekrar kontrol edin. Hatalar, hizalanmamış aralıklardan veya kriter hücrelerini güncellemeyi unutmaktan kaynaklanabilir.
Kritere göre yalnızca görünür hücreleri formülle toplama
Ekstra yardımcı sütun eklemek istemiyorsanız, belirli kriterlere göre yalnızca görünür hücreleri toplamak için SUMPRODUCT, SUBTOTAL, OFFSET, ROW ve MIN fonksiyonlarının kombinasyonunu kullanabilirsiniz. Bu yaklaşım, özellikle dizin formülleri konusunda rahat olan deneyimli Excel kullanıcıları için en uygunudur ve ayrıca çalışma sayfanızı temiz tutmak istediğinizde faydalıdır.
Avantajlar: Ekstra çalışma sayfası sütunlarına gerek yoktur; esnek ve dinamiktir; formül, filtreleme veya kriter değişikliklerinde anında güncellenir.
Sınırlamalar: Formüller, özellikle dizi fonksiyonlarına aşina olmayanlar için okunması veya hata ayıklaması zor olabilir; çok büyük tablolarda performans yavaşlayabilir.
Boş bir hücreye (örneğin, A2:A12 aralığında "Hoodie" için görünür hücreleri toplamak üzere, gerçek değerler D2:D12'de ve kriter A17'de ise) aşağıdaki formülü kopyalayın veya girin:
Formülü girdikten sonra, aşağıdaki gibi görünen istenen sonucu almak için Enter tuşuna basın:
Dikkat: Bu yaklaşım, belirtilen aralıklara duyarlıdır—eşleşmeyen veya çakışan aralıklar hatalara veya beklenmedik sonuçlara neden olabilir. Özellikle filtrelemenin görünür satırların sayısı veya konumunu değiştirdiği durumlarda sınır durumlarını test edin.
Kritere göre yalnızca görünür hücreleri VBA kodu ile toplama
Gelişmiş kullanıcılar için, VBA, standart formüllerin performans darboğazlarıyla karşılaştığı veya kriter sayımı çoklu koşullu mantığı içerdiği karmaşık senaryolar veya büyük veri setlerinde yalnızca görünür hücreleri toplamanın esnek bir yolunu sunar. VBA, her görünür satırı yineleyebilir, koşullarınızı test edebilir ve toplamayı verimli bir şekilde hesaplayabilir. Bu özellikle tekrarlı raporlama görevleri veya otomatikleştirilmiş özet hesaplamalar için uygundur.
Avantajlar: Büyük veri setlerini, birden fazla veya dinamik kriterleri ve karmaşık mantığı kolayca işleyebilir; binlerce satır olması durumunda bile işlem hızlı çalışır; manuel formül değişikliklerinden kaynaklanan hata riskini azaltır.
Sınırlamalar: Makroların etkinleştirilmesini gerektirir; bazı kullanıcılar VBA'ya aşina olmayabilir veya yeterli izinlere sahip olmayabilir; değişiklikler için Makro Düzenleyici'ye erişim gereklidir. Önemli veri setlerinde VBA çalıştırmadan önce her zaman yedek alın.
1. Başlamak için, Geliştirici Araçları > Visual Basic'a tıklayarak VBA Editörünü açın. Açılan pencerede, Ekle > Modül'e gidin 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 Sub2Tıklayın 
"Çalıştır" butonuna (veya F5) basarak kodu çalıştırın. Bir iletişim kutusu, hem kriter aralığını (örneğin ürün adlarınızı), hem de toplamak istediğiniz değer aralığını ve filtre olarak ne istediğinizi (örneğin "Hoodie") seçmenizi isteyecektir. Makro, kriterlerinizin karşılandığı görünür satırları yalnızca toplayacak ve sonucu bir pop-up mesajda gösterecektir.
Pratik ipuçları: Verilerinizi veya filtrelerinizi değiştirdikten sonra toplamlarınızı yeniden hesaplamanız gereken durumlarda bu VBA kodunu kullanın. VBA kodunu daha fazla giriş istemi veya mantıksal koşul ekleyerek birden fazla kriter için çalışacak şekilde daha da genişletebilirsiniz.
Sorun giderme: Kriterler ve değerler için seçtiğiniz aralıkların aynı sayıda satıra sahip olduğundan ve filtrelenmiş verilerinizle aynı sütunlarda bulunduğundan her zaman emin olun. Eğer kod bir hata bildirirse veya beklediğiniz toplamı döndürmezse, filtre ayarlarınızı ve aktif seçimlerinizi tekrar kontrol edin.
Özet öneriler: Tekrarlanan görünür-yalnızca hesaplamalar gerektiren veri analizi için, bu makroyu Kişisel Makro Çalışma Kitabınızda kaydetmek günlük raporlama işlemlerinizi hızlandırabilir. Eğer bir iletişim kutusu görünmüyorsa, makro ayarlarınızı ve güvenlik izinlerinizi kontrol edin.
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