Wie filtert man Daten in Excel, ohne die unterste Summenzeile auszublenden?
Bei der täglichen Datenverarbeitung mit Excel ist es üblich, am Ende des Datensatzes eine Summenzeile zur Zusammenfassung wichtiger Kennzahlen wie Summen, Durchschnitte, Anzahlen und anderer Aggregationen zu berechnen. Wenn Sie jedoch die Filterfunktion von Excel verwenden, unterliegt die Summenzeile oft denselben Filtern wie der Rest Ihrer Daten, sodass sie ausgeblendet wird, sobald Filter angewendet werden. In vielen Szenarien – insbesondere bei zusammenfassenden Berichten, Finanzberichten oder fortlaufenden Datenanalysen – benötigen Sie möglicherweise eine stets sichtbare Summenzeile am unteren Rand Ihrer Daten, unabhängig von den auf das Arbeitsblatt angewendeten Filtern, wie in der folgenden Abbildung dargestellt.
Dieser Artikel führt Sie durch mehrere praktische Methoden, um sicherzustellen, dass die untere Summenzeile beim Filtern stets sichtbar bleibt. Jede Methode eignet sich für unterschiedliche Anwendungsszenarien und Benutzerpräferenzen. Der Artikel behandelt nicht nur integrierte Excel-Funktionen, sondern bietet auch Formelansätze sowie programmgesteuerte VBA-Lösungen und liefert so eine Auswahl an Optionen je nach Ihren Anforderungen.
➤ Daten filtern, ohne die untere Summenzeile auszuschließen, indem eine Tabelle erstellt wird
➤ Daten filtern, ohne die untere Summenzeile auszuschließen, indem eine Formel eingefügt wird
➤ Daten filtern, ohne die untere Summenzeile auszuschließen, mithilfe von VBA-Code
Daten filtern, ohne die untere Summenzeile auszuschließen, indem eine Tabelle erstellt wird
Der einfachste Weg, Ihre Summenzeile beim Filtern sichtbar zu halten, ist die Umwandlung Ihrer Daten in eine offizielle Excel-Tabelle. Excel-Tabellen verfügen über eine native Summenzeile-Funktion, die intelligent so gestaltet ist, dass sie auch beim Filtern anderer Zeilen stets am unteren Rand der Daten sichtbar bleibt. Im Folgenden finden Sie detaillierte Schritte, typische Anwendungsfälle und hilfreiche Hinweise zu dieser Methode:
1. Falls Ihre Daten bereits eine manuell berechnete Summenzeile enthalten (d. h. eine Zeile mit Summenformeln, die nicht in eine Tabelle integriert sind), entfernen Sie diese vorübergehend, um Dopplungen zu vermeiden.
2. Wählen Sie den Bereich Ihrer eigentlichen Daten aus (ohne die manuell erstellte Summenzeile). Gehen Sie dann zu Einfügen > Tabelle. Stellen Sie im Dialogfeld Tabelle erstellen sicher, dass Meine Tabelle hat Überschriften aktiviert ist, falls Ihre Daten Spaltenüberschriften enthalten. Überprüfen Sie den Datenbereich, um sicherzustellen, dass alle Ihre Zeilen enthalten sind.
3. Nachdem Sie auf OK geklickt haben, werden Ihre Daten als Excel-Tabelle formatiert. Solange die Tabelle ausgewählt ist, wechseln Sie zur Registerkarte Entwurf(oder)Tabellenentwurf in einigen Versionen). Aktivieren Sie in der Gruppe Tabellenformatvorlagen das Kontrollkästchen Summenzeile. Excel fügt automatisch eine neue Summenzeile am unteren Rand hinzu.
4. Sie können jetzt die Filtersymbole in beliebigen Spalten nutzen, um Ihre Daten ganz nach Bedarf zu filtern. Die Summenzeile bleibt stets als letzte Zeile der Tabelle sichtbar – unabhängig von den angewendeten Filtern.
Anwendungsszenarien: Diese Methode eignet sich ideal für Datensätze, die häufig aktualisiert, teamübergreifend geteilt oder in wiederkehrenden Berichten verwendet werden – insbesondere dann, wenn Sie von integrierten Tabellenfunktionen wie abwechselnd farbigen Zeilen, strukturierten Bezügen und automatischer Formatierung profitieren möchten.
Vorteile:
- Nahtlose Integration mit den integrierten Funktionen von Excel
- Formeln müssen nicht aktualisiert werden, wenn sich der Datenbereich vergrößert oder verkleinert
- Die Summenzeile bleibt dynamisch und wird automatisch aktualisiert
Einschränkungen:
- benutzerdefinierte Formeln in der Summenzeile müssen nach der Umwandlung in eine Tabelle neu konfiguriert werden
- Große Datensätze können aufgrund der Echtzeitformatierung geringfügige Leistungseinbußen aufweisen
- Stellen Sie sicher, dass Sie alle manuell erstellten Summenzeilen löschen, bevor Sie die Daten in eine Tabelle umwandeln – andernfalls könnten doppelte Summen angezeigt werden.
- Entfernen Sie Filter, bevor Sie Daten hinzufügen oder löschen, um die Konsistenz der Tabellenstruktur zu gewährleisten.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Daten filtern, ohne die untere Summenzeile auszuschließen, indem eine Formel eingefügt wird
Wenn Sie keine Excel-Tabelle verwenden möchten, können Sie am unteren Rand Ihrer Daten eine manuelle Summenzeile beibehalten, indem Sie die Formel TEILERGEBNIS (SUBTOTAL) anwenden. Die Funktion TEILERGEBNIS ist speziell dafür ausgelegt, dynamisch mit Excel-Filtern zu arbeiten – ihre Berechnung berücksichtigt nur sichtbare (gefilterte) Zeilen, während die Formel selbst beim Filtern nicht ausgeblendet oder ausgeschlossen wird. Dieser Ansatz ist flexibel und funktioniert hervorragend mit klassischen Bereichen.
1. Geben Sie in die erste Zelle Ihrer geplanten Summenzeile – direkt unterhalb Ihrer Datentabelle – folgende Formel ein (z. B., wenn sich Ihre Werte in Spalte B von Zeile 2 bis 13 befinden):
=SUBTOTAL(9, B2:B13)Ersetzen Sie B2:B13 durch den Bereich, der Ihren tatsächlichen Daten in Spalte und Zeilen entspricht. Die Zahl 9in der Formel steht für die SUMME-Funktion; bei Bedarf können Sie auch andere Funktionsnummern verwenden (z. B.)1 für MITTELWERT, 2 für ANZAHL usw.).
2. Drücken Sie Enter, um die Eingabe zu bestätigen. Die Summenzeile zeigt sofort die berechnete Summe basierend auf den sichtbaren (ungefilterten) Zeilen an. Sobald Sie Filter über die integrierten Filterschaltflächen von Excel anwenden, bleibt die Summenzeile am unteren Rand sichtbar und aktualisiert sich automatisch – sie zeigt stets das aktuelle Zwischenergebnis ausschließlich für die sichtbaren Zeilen an.
TEILERGEBNIS-Formel an, wenn sich Ihre Daten vergrößern oder verkleinern. Diese Formel erweitert sich nicht automatisch, es sei denn, Sie verwenden eine dynamische Bereichsfunktion wie BEREICH.VERSCHIEBENoder INDEX.Zusammenfassung: Die Formel TEILERGEBNIS unterstützt verschiedene Aggregatfunktionen (wie MITTELWERT, ANZAHL usw.) und eignet sich daher ideal für Zusammenfassungszeilen in Finanz-, Lager- und Berichtsabläufen.
Daten filtern, ohne die untere Summenzeile auszuschließen, mithilfe von VBA-Code
Für Benutzer, die mit größeren Datensätzen arbeiten oder einen automatisierten Ansatz benötigen, um sicherzustellen, dass die untere Summenzeile stets sichtbar bleibt – unabhängig von den Filtereinstellungen –, bietet sich der Einsatz von VBA (Visual Basic for Applications) an, um das Filtern programmgesteuert zu steuern. Diese Lösung eignet sich besonders für Arbeitsmappen, in denen sich der Datenbereich oder die Position der Summenzeile dynamisch ändert, oder wenn Sie für alle Benutzer eine einheitliche Darstellung garantieren möchten.
Dieses VBA-Beispiel funktioniert, indem es die Letzte Zeile in Ihrer Datenbereich überprüft und erzwingt, dass sie sichtbar bleibt, selbst nachdem Filter angewendet wurden. Dadurch wird sichergestellt, dass die Summenzeile stets angezeigt wird und durch Zuweisen des Makros zu einer Schaltfläche wiederverwendet werden kann.
Schritte zur Verwendung dieser VBA-Lösung:
1. Wechseln Sie in Excel zur Registerkarte Entwicklertools. Falls diese Registerkarte nicht sichtbar ist, aktivieren Sie sie einfach in den Excel-Optionen: Gehen Sie dazu im Abschnitt „Menüband anpassen“ auf „Entwicklertools“ und aktivieren Sie die Option.
2. Klicken Sie auf Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im neuen Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. Schließen Sie nach der Codeeingabe den VBA-Editor. Drücken Sie in Excel Alt + F8, wählen Sie AlwaysShowTotalRow aus und klicken Sie auf Ausführen. Das Makro stellt sicher, dass die letzte Zeile (Ihre Summenzeile) auch nach dem Anwenden von Filtern sichtbar bleibt.
• Das Makro geht davon aus, dass die Summenzeile die letzte genutzte Zeile in Spalte A ist. Passen Sie die Logik gegebenenfalls an, falls Ihre Daten Leere Zeilen oder mehrere Zusammenfassungen enthalten.
• Erwägen Sie, dieses Makro einer Schaltfläche zuzuweisen, um es bei häufigen Datenänderungen einfach wiederverwenden zu können.
• Falls Ihre Arbeitsmappe mehrere Bereiche enthält, verwenden Sie einen benannten Bereich oder eine Spalte angeben-Markierung, um die korrekte Summenzeile zuverlässiger zu lokalisieren.
Vorteile: Automatisiert die Sichtbarkeit der Summenzeile – unabhängig von Benutzeraktionen oder Filtern. Ideal für Dashboards, Berichte oder gemeinsam genutzte Arbeitsmappen.
Einschränkungen: Erfordert eine makrofähige Arbeitsmappe sowie entsprechende Makroberechtigungen. Für komplexere Blattlayouts sind möglicherweise Anpassungen erforderlich.
Zusammenfassung: Dieser VBA-Ansatz wird für automatisierte Umgebungen empfohlen, in denen die manuelle Steuerung der Summenzeile unzuverlässig oder unpraktisch ist. Er stellt sicher, dass zusammenfassende Daten in allen Anwendungsfällen sichtbar bleiben.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins