Wie summiert man in Excel nur sichtbare Zellen basierend auf Kriterien?
In Excel können Benutzer normalerweise Zellen basierend auf bestimmten Kriterien mit der SUMMEWENN-Funktion summieren. Wenn jedoch mit gefilterten Daten gearbeitet wird, führt die einfache Anwendung von SUMMEWENN dazu, dass sowohl sichtbare als auch ausgeblendete Zellen in die Berechnung einbezogen werden. Dies führt oft zu falschen Ergebnissen, wenn Sie nur die sichtbaren (d.h. ungefilterten) Zellen summieren müssen, die bestimmte Kriterien erfüllen, wie im folgenden Screenshot gezeigt.
Nur sichtbare Zellen basierend auf einem oder mehreren Kriterien mit einer Hilfsspalte summieren
Nur sichtbare Zellen basierend auf einem oder mehreren Kriterien mit einer Formel summieren
Nur sichtbare Zellen basierend auf Kriterien mit VBA-Code summieren
Es ist eine häufige Anforderung in täglichen Berichts- und Datenanalyse-Workflows, Daten in gefilterten Tabellen genau zu aggregieren, z.B. beim Berechnen von Verkaufsbeträgen für ein bestimmtes Produkt oder eine bestimmte Kategorie nach Anwendung einiger Filter. Eine falsche Ausführung kann dazu führen, dass Gesamtbeträge Daten enthalten, die nicht beabsichtigt waren. Daher ist es wichtig, Techniken zu verwenden, die nur die sichtbaren Daten summieren, die Sie auf Ihrem Bildschirm sehen.
Dieser Artikel stellt mehrere praktische Methoden vor, die für verschiedene Szenarien und Kenntnisstufen geeignet sind, jede mit ihren Vorteilen und möglichen Einschränkungen. Sie können eine Lösung auswählen, die am besten zu Ihrer Arbeitsblattgröße, Datenstruktur und Ihren Arbeitsgewohnheiten passt. Detaillierte Schritte für jede Lösung werden unten bereitgestellt, zusammen mit Erklärungen potenzieller Fehler und Möglichkeiten zur Optimierung des Berechnungsprozesses für zuverlässigere Ergebnisse.
Nur sichtbare Zellen basierend auf einem oder mehreren Kriterien mit einer Hilfsspalte summieren
Einer der intuitivsten und stabilsten Ansätze, um sichtbare Zellen basierend auf bestimmten Kriterien zu summieren, besteht darin, eine Hilfsspalte zu verwenden, die Werte nur für sichtbare Zeilen zurückgibt, und dann die SUMMEWENN-Funktion mit Ihren gewünschten Bedingungen zu nutzen. Dies ist besonders effektiv, wenn Ihr Datensatz häufig auf verschiedene Weisen gefiltert wird oder wenn Sie Berechnungen einrichten müssen, die Kollegen leicht verstehen oder ändern können.
Vorteile: Einfach einzurichten; gesamte Logik und Berechnungen bleiben im Arbeitsblatt sichtbar; ideal für kleine bis mittlere Tabellen; robust bei der Anpassung oder Prüfung von Formeln.
Einschränkungen: Erzeugt zusätzliche Spalten; möglicherweise müssen Formeln aktualisiert werden, wenn sich das Zeilenlayout ändert; umfangreiche Nutzung könnte bei sehr großen Datensätzen unhandlich werden.
Zum Beispiel, um nur die Werte von Bestellungen für das Produkt "Hoodie" in einem gefilterten Bereich zu summieren:
1. Geben Sie die folgende Formel in eine leere Spalte neben Ihrem Datensatz ein (z.B. in Zelle E2, unter der Annahme, dass D Ihre Wertespalte ist):
Ziehen Sie den Ausfüllkursor nach unten, um diese Formel durch alle Zeilen in Ihrem Datenbereich zu füllen. Diese Formel gibt den Wert aus Spalte D zurück, wenn die Zeile sichtbar ist, und 0, wenn die Zeile durch Filtern ausgeblendet ist.
2. Nachdem Sie die Hilfswerte in Spalte E generiert haben, verwenden Sie eine SUMMEWENN-Funktion, um nur die sichtbaren Werte basierend auf Ihren Kriterien zu summieren. Zum Beispiel, um die Summe für "Hoodie" in Spalte A zu bilden:
Sie können weitere Kriterien hinzufügen, indem Sie die SUMMEWENN-Argumente im Format =SUMMEWENN(summen_bereich, kriterien_bereich1, kriterium1, [kriterien_bereich2, kriterium2], [kriterien_bereich3, kriterium3], ...) erweitern. Überprüfen Sie immer Ihre Bereiche, um korrekte Ausrichtung und erwartete Ergebnisse sicherzustellen.
Beachten Sie: Wenn Sie Zeilen nach dem Einrichten Ihrer Formeln neu anordnen, einfügen oder löschen, überprüfen Sie doppelt, ob alle Referenzen noch Ihrer Datenstruktur entsprechen. Manchmal können Fehler durch falsch ausgerichtete Bereiche oder vergessene Aktualisierungen Ihrer Kriterienzellen auftreten.
Nur sichtbare Zellen basierend auf Kriterien mit einer Formel summieren
Wenn Sie eine formelbasierte Lösung bevorzugen, die keine Hilfsspalten benötigt, können Sie eine Kombination aus SUMMENPRODUKT, TEILERGEBNIS, VERSCHIEBUNG, ZEILE und MIN-Funktionen verwenden, um sichtbare Zellen entsprechend bestimmter Kriterien zu summieren. Dieser Ansatz eignet sich am besten für erfahrene Excel-Benutzer, die mit Array-Formeln vertraut sind, und ist besonders nützlich, wenn Sie Ihr Blatt ohne zusätzliche Spalten ordentlich halten möchten.
Vorteile: Keine Notwendigkeit für zusätzliche Arbeitsblattspalten; flexibel und dynamisch; Formeln aktualisieren sich sofort, wenn Sie filtern oder Kriterien ändern.
Einschränkungen: Formeln können schwer zu lesen oder zu debuggen sein, insbesondere für diejenigen, die nicht mit Array-Funktionen vertraut sind; Leistung kann bei sehr großen Tabellen langsam werden.
Kopieren oder geben Sie die folgende Formel in eine leere Zelle ein (zum Beispiel, um sichtbare Zellen für "Hoodie" in A2:A12 zu summieren, mit tatsächlichen Werten in D2:D12 und den Kriterien in A17):
Nachdem Sie die Formel eingegeben haben, drücken Sie Enter, um das gewünschte Ergebnis zu erhalten, wie unten gezeigt:
Achten Sie darauf: Dieser Ansatz ist empfindlich gegenüber den angegebenen Bereichen – nicht übereinstimmende oder überlappende Bereiche können Fehler oder unerwartete Ergebnisse auslösen. Testen Sie Grenzfälle, insbesondere wenn Filter die Anzahl oder Position der sichtbaren Zeilen ändern.
Nur sichtbare Zellen basierend auf Kriterien mit VBA-Code summieren
Für fortgeschrittene Benutzer bietet die Verwendung von VBA eine flexible Möglichkeit, nur sichtbare Zellen pro spezifische Kriterien zu summieren, insbesondere bei der Handhabung komplexer Szenarien oder großer Datensätze, wo Standardformeln unter Leistungsengpässen leiden könnten oder wo die Kriterienzählung Multi-Konditionslogik umfasst, die schwer in einer einzigen Formel auszudrücken ist. VBA kann jede sichtbare Zeile durchlaufen, Ihre Bedingungen testen und die Summe effizient berechnen. Dies ist besonders geeignet für wiederholte Berichtsaufgaben oder bei der Automatisierung von Zusammenfassungsberechnungen.
Vorteile: Kann große Datensätze, mehrere oder dynamische Kriterien und komplexe Logik leicht handhaben; Prozess wird selbst bei Tausenden von Zeilen schnell ausgeführt; reduziert das Risiko von Fehlern durch manuelle Formeländerungen.
Einschränkungen: Erfordert das Aktivieren von Makros; einige Benutzer sind möglicherweise nicht mit VBA vertraut oder haben keine ausreichenden Berechtigungen; Änderungen erfordern Zugriff auf den Makroeditor. Machen Sie immer eine Sicherungskopie, bevor Sie VBA auf wichtige Datensätze anwenden.
1. Um loszulegen, öffnen Sie den VBA-Editor, indem Sie auf Entwicklertools > Visual Basic klicken. Im erscheinenden Fenster gehen Sie zu Einfügen > Modul und fügen den folgenden Code in das neue Modul ein:
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. Klicken Sie auf die 
„Ausführen“-Schaltfläche (oder drücken Sie F5), um den Code auszuführen. Ein Dialogfeld fordert Sie auf, sowohl den Kriterienbereich (z.B. Ihre Produktnamen), den Wertebereich zur Summierung und den Wert, den Sie als Filter wünschen (z.B. „Hoodie“), auszuwählen. Das Makro wird nur diejenigen sichtbaren Zeilen summieren, in denen Ihre Kriterien erfüllt sind, und das Ergebnis in einer Pop-up-Nachricht anzeigen.
Praktische Tipps: Verwenden Sie diesen VBA-Code, wenn Sie Ihre Summen oft nach dem Ändern Ihrer Daten oder Filter neu berechnen müssen. Sie können den VBA-Code weiter erweitern, um für mehrere Kriterien zu funktionieren, indem Sie weitere Eingabeaufforderungen oder logische Bedingungen hinzufügen.
Problembehebung: Stellen Sie immer sicher, dass die von Ihnen ausgewählten Bereiche für Kriterien und Werte die gleiche Anzahl von Zeilen haben und zu denselben Spalten gehören wie Ihre gefilterten Daten. Wenn der Code einen Fehler meldet oder Ihre erwartete Summe nicht zurückgibt, überprüfen Sie Ihre Filtereinstellungen und aktive Auswahl.
Zusammenfassende Empfehlungen: Für die Datenanalyse, die wiederholte Berechnungen nur für sichtbare Zellen erfordert, kann das Speichern dieses Makros in Ihrem persönlichen Makro-Arbeitsbuch die tägliche Berichterstattung beschleunigen. Wenn kein Dialogfeld erscheint, überprüfen Sie Ihre Makroeinstellungen und Sicherheitsberechtigungen.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins