Wie summiert man Werte ohne oder schließt Teilergebnisse in Excel aus?
Beim Arbeiten mit Datensätzen in Excel ist es üblich, Ihre Zahlen zu organisieren und zu analysieren, indem Sie Teilergebnisse für verschiedene Gruppen oder Kategorien berechnen. Nachdem Sie diese Teilergebnisse hinzugefügt haben, möchten Sie jedoch möglicherweise eine Gesamtsumme berechnen, die diese Teilergebniswerte ausschließt, um eine Doppelzählung im Endergebnis zu verhindern. Die Herausforderung besteht darin, dass bei der direkten Addition aller Zahlen die Teilergebnisse in der Gesamtsumme enthalten sind, was zu einer Gesamtsumme führt, die größer als beabsichtigt ist. Um dies zu vermeiden, müssen Sie eine Möglichkeit finden, die ursprünglichen Daten zu summieren, während Sie die Teilergebniszeilen ausschließen. In diesem Artikel stellen wir mehrere praktische Methoden zur Lösung dieses Problems vor, damit Sie in verschiedenen Szenarien effizient genaue Ergebnisse berechnen können.
Summieren von Werten ohne Teilergebnisse mit der Summenfunktion in Excel
Summieren von Werten ohne Teilergebnisse mit der Teilergebnisfunktion in Excel
VBA-Code - Summieren von Werten unter Ausschluss von Teilergebniszeilen
Filtern - Verwenden Sie Filter und TEILERGEBNIS, um nur sichtbare (nicht-Teilergebnis) Zeilen zu summieren
Summieren von Werten ohne Teilergebnisse mit der Summenfunktion in Excel
Ein weit verbreiteter Ansatz zur Berechnung von Gruppenteilergebnissen besteht darin, die SUMME-Funktion innerhalb oder unterhalb jeder relevanten Gruppe einzufügen. Wenn Sie jedoch die Gesamtsumme für die gesamte Liste berechnen möchten, wobei diese Gruppenteilergebniszeilen ignoriert werden, ist oft ein zusätzlicher Schritt erforderlich. Hier ist eine Methode, die Sie verwenden können, um dies zu erreichen:
1. Verwenden Sie die SUMME-Funktion innerhalb jeder Gruppe, um die jeweiligen Teilergebnisse zu generieren, wie in diesem Screenshot gezeigt:
2. Geben Sie als Nächstes die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis anzeigen möchten, um die Gesamtsumme ohne die Teilergebniszeilen zu ermitteln:
=SUMME(B2:B21)/2
Drücken Sie die Eingabetaste, um die richtige Gesamtsumme zu erhalten. Diese Formel funktioniert, weil davon ausgegangen wird, dass Sie Teilergebnisse mit der SUMME-Funktion unmittelbar nach den relevanten Gruppen eingefügt haben, wodurch die Summe der Rohdaten effektiv verdoppelt wurde. Durch die Division durch 2 wird die doppelte Gesamtsumme entfernt. Beachten Sie, dass diese Methode am besten geeignet ist, wenn der Datensatz so strukturiert ist, dass die Summe der Originalwerte und Teilergebnisse zusammen genau das Doppelte der Summe der Originaldaten ergibt.
Wenn Ihre Datenstruktur komplexer ist oder die Anzahl der Teilergebniszeilen variiert, können alternative Methoden, die unten beschrieben werden, eine genauere und flexiblere Lösung bieten.
Werte ohne Teilergebnisse mit der Teilergebnisfunktion in Excel summieren
Die TEILERGEBNIS-Funktion in Excel bietet eine integrierte Möglichkeit, Berechnungen nur auf gefilterte oder sichtbare Zeilen durchzuführen. Dies macht sie besonders nützlich, wenn Sie mit Listen arbeiten, die Teilergebnisse enthalten, da TEILERGEBNIS-Formeln so konfiguriert werden können, dass andere TEILERGEBNIS-Ergebnisse innerhalb des Bereichs ignoriert werden, wodurch eine Doppelzählung von Werten verhindert wird.
Um die TEILERGEBNIS-Funktion in diesem Kontext zu verwenden, folgen Sie diesen Schritten:
1. Geben Sie die TEILERGEBNIS-Funktion für jede Gruppe wie folgt ein:
=TEILERGEBNIS(9;B2:B10)
Dies wendet die SUMMEN-Operation (Funktionsnummer 9) auf den angegebenen Bereich an und wird oft als Teil der integrierten Funktion Daten > Teilergebnis von Excel verwendet.
2. Geben Sie für die Gesamtsumme, die interne Teilergebnisse ausschließt, die folgende Formel in Ihre gewählte Zelle ein:
=TEILERGEBNIS(9;B2:B21)
Drücken Sie die Eingabetaste, um die Datenwerte automatisch zu summieren, wobei alle verschachtelten TEILERGEBNIS-Funktionen innerhalb des Bereichs ignoriert werden. Dieser Ansatz eignet sich für Listen, die die TEILERGEBNIS-Funktion für Gruppenzusammenfassungen verwenden, und eliminiert das Risiko, Teilergebnisse mehrfach zu summieren.
Beachten Sie, dass die TEILERGEBNIS-Funktion am besten funktioniert, wenn Teilergebniszellen mit TEILERGEBNIS statt mit SUMME generiert wurden. Außerdem kann TEILERGEBNIS so eingestellt werden, dass nur sichtbare (nicht ausgeblendete) Daten summiert werden, wenn Sie Zeilen filtern oder ausblenden, was in dynamischen Berichtssituationen hilfreich ist.
VBA-Code - Summieren von Werten unter Ausschluss von Teilergebniszeilen
Wenn Ihre Datenstruktur komplex ist oder Sie eine automatisierte Methode zum Summieren nur der Rohdatenzeilen (ohne Teilergebnisse) wünschen, können Sie ein einfaches VBA-Makro verwenden. Dieser Ansatz ermöglicht es Ihnen, programmgesteuert nur diejenigen Zeilen zu identifizieren und zu summieren, die Ihren Kriterien entsprechen, wie z.B. basierend auf einer spezifischen Formatierung, dem Vorhandensein von Formeln oder anderen unterscheidenden Attributen.
Zum Beispiel, wenn Ihre Teilergebniszeilen mit Formeln (wie SUMME oder TEILERGEBNIS) berechnet werden und Ihre Rohdatenzeilen nur statische Werte enthalten, können Sie den VBA-Code so einstellen, dass nur die Zellen in einer bestimmten Spalte summiert werden, die keine Formeln enthalten. So können Sie es tun:
1. Klicken Sie auf Entwicklertools > Visual Basic um das Microsoft Visual Basic for Applications-Fenster zu öffnen.
2. Klicken Sie im neuen Fenster auf Einfügen > Modul und fügen Sie den folgenden Code in das Modul ein:
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. Klicken Sie auf die 
Ausführen Schaltfläche, um den Code auszuführen. Es erscheint eine Eingabeaufforderung, in der Sie den Bereich mit Ihren Daten auswählen sollen. Das Makro summiert dann nur die Zellen in Ihrer Auswahl, die keine Formeln enthalten, und überspringt effektiv typische Teilergebniszeilen.
Filtern - Verwenden Sie Filter und TEILERGEBNIS, um nur sichtbare (nicht-Teilergebnis) Zeilen zu summieren
Wenn Ihr Datensatz es Ihnen ermöglicht, Teilergebniszeilen herauszufiltern (z.B., wenn es ein Label, ein Schlüsselwort oder ein Muster gibt, um sie zu identifizieren), können Sie die integrierte Filterfunktion von Excel zusammen mit der TEILERGEBNIS-Funktion verwenden, um nur die sichtbaren (nicht gefilterten) Datenzeilen zu addieren. Diese Technik ist praktisch für Fälle, in denen Teilergebnisse ein konsistentes Indikator oder Label haben.
1. Klicken Sie irgendwo in Ihrem Datensatz, gehen Sie dann zum Daten Reiter und klicken Sie auf Filter um Filter für Ihre Spalten zu aktivieren.
2. Klicken Sie auf das Dropdown-Menü in Ihrer Hilfsspalte, deaktivieren Sie "Teilergebnis", um Teilergebniszeilen auszublenden und nur die Originaldaten anzuzeigen.
3. Geben Sie in einer separaten Zelle die folgende Formel ein, um nur sichtbare (gefilterte) Zeilen zu summieren.
=SUBTOTAL(9,B2:B21)Die TEILERGEBNIS-Funktion (mit Funktionsnummer 9 für SUMME) ignoriert alle ausgeblendeten Zeilen und gibt die Summe nur der aktuell sichtbaren Zeilen zurück (das heißt, die nicht-Teilergebniszeilen, die Sie nach dem Filtern behalten haben). Diese Lösung ist schnell und dynamisch – wenn Sie Filter neu anwenden oder Ihren Datensatz erweitern, wird die Formel entsprechend aktualisiert.
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