Wie schließt man Zellen in einer Spalte von der Summe in Excel aus?
Beim Arbeiten mit Daten in Excel müssen Sie oft die Summe eines Bereichs berechnen, während Sie bestimmte Zellen absichtlich überspringen – zum Beispiel, wenn diese Zellen Ausreißer, Fehler, irrelevante Daten oder spezifische Werte enthalten, die Sie aus der Gesamtsumme herauslassen möchten. Wie im folgenden Screenshot zu sehen ist, stellen Sie sich vor, Sie haben eine Spalte mit Zahlen, möchten aber alle außer den Werten in Zelle A3 und A4 summieren. Dieser Artikel wird umfassend verschiedene praktische Methoden demonstrieren, um einen Bereich in Excel zu summieren, während eine oder mehrere bestimmte Zellen ausgeschlossen werden, was Ihnen dabei hilft, genau die Ergebnisse zu erhalten, die Sie für Finanzanalysen, Berichte oder Bestandsverwaltungsaufgaben benötigen, bei denen selektive Berechnungen erforderlich sind.
- Zellen in einer Spalte von der Summe mit Formel ausschließen
- VBA-Code – Programmgesteuertes Summieren eines Bereichs, wobei bestimmte Zellen übersprungen/ausgeschlossen werden
- Excel-Formel – Verwenden von SUMMEWENN/SUMMEWENNS, um nur Werte einzuschließen, die nicht den Ausschlusskriterien entsprechen
- Excel-Formel – Verwenden der FILTER-Funktion in neueren Excel-Versionen, um ausgeschlossene Zellen vor dem Summieren zu filtern
Zellen in einer Spalte von der Summe mit Formel ausschließen
Durch die Verwendung einfacher Arithmetik innerhalb der SUMME-Formel können Sie unerwünschte Zellen direkt in Ihrer Berechnung ausschließen. Dieser Ansatz eignet sich für schnelle Berechnungen, wenn Sie nur wenige Ausnahmen zu behandeln haben. Folgen Sie diesen Schritten:
1. Wählen Sie eine leere Zelle aus, um das Summenergebnis anzuzeigen, und geben Sie die folgende Formel in der Formelleiste ein, dann drücken Sie die Eingabetaste, um die Summe zu berechnen, während bestimmte Zellen ausgeschlossen werden. Zum Beispiel:
=SUMME(A2:A7)-SUMME(A3:A4)
Erklärung & Tipps:
- Die SUMME(A2:A7) berechnet den gesamten Bereich, während SUMME(A3:A4) die Werte der ausgeschlossenen Zellen subtrahiert. Dies funktioniert am besten, wenn die auszuschließenden Zellen zusammenhängend sind.
- Sie können problemlos mehrere ausgeschlossene Zellen mischen und subtrahieren, wenn sie nicht benachbart sind. Wenn Sie beispielsweise A3 und A6 aus dem Bereich ausschließen möchten, passen Sie die Formel wie folgt an:
=SUMME(A2:A7)-A3-A6
- Wenn die Ausnahmen verstreut oder zahlreich sind, kann das manuelle Auflisten jeder ausgeschlossenen Zelle die Formeln länger und schwerer zu verwalten machen.
- Seien Sie vorsichtig bei Zellbezügen: Wenn sich Ihre Daten oder der Bereich ändern, aktualisieren Sie die Formel entsprechend, um Fehler zu vermeiden.
VBA-Code – Programmgesteuertes Summieren eines Bereichs, wobei bestimmte Zellen übersprungen/ausgeschlossen werden
Für Szenarien, in denen Sie viele Ausnahmen haben oder den Prozess oft wiederholen müssen, bietet die Verwendung eines VBA-Makros Flexibilität und Automatisierung. Mit VBA können Sie einen bestimmten Bereich summieren und beliebig viele Zellen, ob zusammenhängend oder nicht, programmatisch definieren und ausschließen. Diese Methode eignet sich für Benutzer, die mit der VBA-Umgebung vertraut sind und komplexere Ausschlusslogik optimieren möchten.
Vorsichtsmaßnahmen: VBA-Makros können Ihr Arbeitsblatt ändern. Speichern Sie Ihre Arbeit immer, bevor Sie neuen Code ausführen. Makros müssen aktiviert sein, damit das oben genannte funktioniert.
1. Gehen Sie zu Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im Projekt-Fenster mit der rechten Maustaste auf Ihre Arbeitsmappe, wählen Sie Einfügen > Modul und fügen Sie den folgenden Code in das Modul ein:
Sub SumWithExclusions()
Dim sumRange As Range
Dim excludeCells As Range
Dim cell As Range
Dim result As Double
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
result = 0
If Not sumRange Is Nothing Then
For Each cell In sumRange
If Not Application.Intersect(cell, excludeCells) Is Nothing Then
' Skip excluded cells
Else
result = result + cell.Value
End If
Next
MsgBox "The sum excluding specified cells is: " & result, vbInformation
Else
MsgBox "No range selected.", vbExclamation
End If
End Sub2. Klicken 
Ausführen im VBA-Fenster oder drücken Sie F5 um das Makro auszuführen. Ein Dialogfenster fordert Sie auf, den vollständigen Bereich zur Summierung auszuwählen, dann wählen Sie die Zellen aus, die Sie ausschließen möchten (halten Sie Strg gedrückt, um mehrere Zellen auszuwählen). Das Makro zeigt das Ergebnis in einem Meldungsfeld an.
- Wenn Sie versehentlich Zellen ausgewählt haben, führen Sie das Makro erneut aus. Der Prozess ist interaktiv und flexibel.
- Makros eignen sich am besten für Routineaufgaben oder komplexe kriterienbasierte Ausschlüsse.
Excel-Formel – Verwenden von SUMMEWENN oder SUMMEWENNS, um nur Werte einzuschließen, die nicht den Ausschlusskriterien entsprechen
Für fortgeschrittene oder logikbasierte Ausschlüsse können Sie die Funktionen SUMMEWENN oder SUMMEWENNS verwenden. Diese Formeln funktionieren gut, wenn Ihre Ausschlüsse auf Werten, Kriterien oder einer Liste von Werten basieren, die Sie vermeiden möchten.
Beispiel – Ausschluss basierend auf einem bestimmten Wert
1. Wenn Sie A2:A7 summieren möchten, aber den Wert '16' ausschließen wollen, geben Sie die folgende Formel in Ihre Zielzelle ein (z.B. in Zelle B1):
=SUMIF(A2:A7,"<>16")Diese Formel addiert alle Werte im Bereich A2:A7, außer denen, die gleich 16 sind.
2. Nachdem Sie die Formel eingegeben haben, drücken Sie die Eingabetaste. Sie können den Bereich/Zellbezüge nach Bedarf kopieren oder anpassen.
Beispiel – Alle Zellen ausschließen, die einem Zellenwert entsprechen
Angenommen, Zelle C1 enthält den Wert, den Sie von der Summe ausschließen möchten:
=SUMIF(A2:A7,"<>"&A3)Aktualisieren Sie C1 bei Bedarf, und die Formel schließt dynamisch alle übereinstimmenden Werte aus.
- Für mehrere Ausschlusskriterien oder komplexere Regeln sollten Sie SUMMEWENNS in Kombination mit Hilfsspalten oder Arrays in Betracht ziehen. SUMMEWENN/SUMMEWENNS funktioniert jedoch am besten, wenn Ausschlüsse auf bestimmten und konsistenten Kriterien basieren, nicht auf willkürlichen Zellenpositionen.
- Wenn Ihr Bereich Text oder leere Zellen enthält, ignoriert SUMMEWENN sie automatisch; stellen Sie sicher, dass dies das gewünschte Verhalten ist.
Excel-Formel – Verwenden der FILTER-Funktion (neue Excel-Versionen), um ausgeschlossene Zellen vor dem Summieren zu filtern
Wenn Sie Excel für Microsoft 365 oder Excel 2021 und höher verwenden, ermöglicht die FILTER-Funktion das dynamische und flexible Ausschließen von Zellen, bevor SUMME angewendet wird. Dies ist besonders nützlich für große Datensätze oder variierende Ausschlusskriterien.
Beispiel – Ausschluss spezifischer Werte (z.B. 16 und 13)
1. Geben Sie die folgende Formel in Ihre Zielzelle ein (z.B. B1):
=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))Dies summiert alle Werte in A2:A7, außer denen, die gleich 16 und 13 sind. Die FILTER-Funktion erstellt ein Array, das nur Zellen einschließt, die nicht diesen Werten entsprechen, und dann addiert SUMME sie.
2. Drücken Sie die Eingabetaste. Die Berechnung wird dynamisch aktualisiert, wenn sich die Ausschlüsse oder die Quelldaten ändern.
- Um Werte dynamisch basierend auf einer Liste auszuschließen (z.B., die Ausschlussliste befindet sich in C2:C4):
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))Diese Formel schließt jeden Wert in A2:A7 aus, der mit einem Wert in C2:C4 übereinstimmt. Aktualisieren Sie einfach Ihre Ausschlussliste in Spalte C, und das Formelergebnis wird automatisch aktualisiert.
- Der FILTER-basierte Ansatz wird Nutzern empfohlen, die mit den neuesten Excel-Versionen arbeiten und nach dynamischer, skalierbarer Ausschlusslogik suchen.
- Wenn Sie einen #CALC!-Fehler erhalten, überprüfen Sie, ob nach allen Ausschlüssen mindestens ein Wert im Bereich verbleibt; andernfalls gibt FILTER einen Fehler zurück.
Zusammenfassend bietet Excel mehrere praktische Lösungen, um einen Bereich zu summieren, während bestimmte Zellen oder Werte ausgeschlossen werden. Einfache Formeln eignen sich für schnelle, kleine Ausschlüsse, während SUMMEWENN/SUMMEWENNS und FILTER flexiblere, bedingungsgetriebene Szenarien unterstützen. VBA ist ideal, wenn Ausschlüsse viele, unterschiedliche oder automatisierte Anforderungen haben. Überprüfen Sie immer Zellbezüge und Formelanpassungen, wenn Sie Ihre Quelldaten ändern. Wenn Sie auf Fehler stoßen, überprüfen Sie Bereiche oder Ausschlusslisten und versuchen Sie, Ihre Formeln erneut anzuwenden oder das Makro erneut auszuführen.
Verwandte Artikel:
- Wie schließt man bestimmte Zellen oder Bereiche vom Drucken in Excel aus?
- Wie schließt man Werte in einer Liste von einer anderen in Excel aus?
- Wie findet man den Minimalwert in einem Bereich unter Ausschluss des Nullwerts in Excel?
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