Wie zählt man / summiert durchgestrichene Zellen in Excel?
In Excel ist es üblich, bestimmte Zellen mit einer Durchstreichung zu formatieren, um Daten als veraltet, irrelevant oder ungültig zu markieren. Diese visuelle Markierung kann das Datenmanagement erheblich verbessern und Ihnen helfen, sich bei der Analyse auf die relevanten Informationen zu konzentrieren. Wenn es jedoch um Berechnungen geht – wie zum Beispiel das Zählen oder Summieren – möchten Sie möglicherweise die Ergebnisse für durchgestrichene Zellen von denen ohne Durchstreichung trennen. Das Verständnis, wie man solche Szenarien handhabt, kann Ihren Workflow optimieren und zu genaueren Erkenntnissen führen. Dieser Artikel bietet mehrere praktische Methoden, um Zellen mit oder ohne Durchstreichungsformatierung in Excel zu zählen und zu summieren, wobei maßgeschneiderte VBA-Funktionen verwendet werden, die Flexibilität für unterschiedliche Datenverwaltungsanforderungen bieten.
Durchgestrichene Zellen mit VBA in Excel zählen
Zählen ohne durchgestrichene Zellen mit VBA in Excel
Summieren ohne durchgestrichene Zellen mit VBA in Excel
Zählen oder Summieren von Zellen mit Durchstreichung mithilfe von Kutools für Excel
Durchgestrichene Zellen in Excel zählen
Wenn Sie die Gesamtzahl der Zellen ermitteln müssen, die in einem bestimmten Bereich mit einer Durchstreichung formatiert wurden, kann dies nicht direkt über Excels integrierte Funktionen erreicht werden, da es keine Formel gibt, die Textformatierungen wie Durchstreichungen erkennt. Daher wird die Verwendung einer benutzerdefinierten Funktion (UDF) in VBA zu einer praktischen Lösung. Dieser Ansatz ist besonders nützlich bei der Verwaltung von Aufgabenlisten, Inventaraktualisierungen oder in jedem Szenario, in dem eine Durchstreichung verwendet wird, um abgeschlossene, ungültige oder veraltete Datenpunkte zu kennzeichnen.
1. Halten Sie die Tasten ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul zu erstellen, und fügen Sie den folgenden Code in das Modulfenster ein.
VBA-Code: Durchgestrichene Zellen zählen
Public Function CountStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountStrike = xOut
End Function
3. Nachdem Sie den Code eingegeben haben, speichern Sie Ihre Arbeitsmappe (um das Makro zu behalten), schließen dann den VBA-Editor und kehren zu Ihrem Arbeitsblatt zurück. Geben Sie in einer beliebigen leeren Zelle, in der das Ergebnis erscheinen soll, die folgende Formel ein (z.B., um durchgestrichene Zellen im Bereich A2:B14 zu überprüfen): =CountStrike(A2:B14)
Tipp: Sie können A2:B14 in einen beliebigen anderen Bereich ändern, den Sie überprüfen möchten.
4. Drücken Sie die Eingabetaste, um die Formel auszuführen, und Excel zeigt sofort die Anzahl aller durchgestrichenen Zellen im angegebenen Bereich an. Dies ermöglicht es Ihnen, den Fortschritt effizient zu verfolgen oder veraltete Einträge bei Bedarf zu entfernen. Siehe Screenshot:
Hinweis: Um sicherzustellen, dass dieser VBA-Code für die zukünftige Verwendung verfügbar bleibt, müssen Sie die Datei im .xlsm-Format (Excel-Makrofähige Arbeitsmappe) speichern.
Zählen ohne durchgestrichene Zellen in Excel
Es gibt Fälle, in denen Sie möglicherweise nur die Anzahl der Zellen benötigen, die in einem bestimmten Bereich keine Durchstreichungsformatierung haben. Zum Beispiel möchten Sie bei Projektverfolgungstabellen oder Verkaufsdaten oft nur die aktiven/gültigen Einträge (d.h. jene ohne Durchstreichung) für weitere Operationen zählen. Die Standardfunktionen ZÄHLEN oder ZÄHLENWENN in Excel ignorieren Textformatierungen, sodass eine angepasste VBA-Lösung präzise Kontrolle über solche Anforderungen bietet.
1. Drücken Sie ALT + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Gehen Sie auf Einfügen > Modul, um ein neues Code-Modul hinzuzufügen. Fügen Sie den folgenden Code in dieses Modulfenster ein.
VBA-Code: Zählen ohne durchgestrichene Zellen
Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountNoStrike = xOut
End Function
3. Speichern Sie Ihre makrofähige Arbeitsmappe, kehren Sie zum Arbeitsblatt zurück und geben Sie in einer leeren Zelle die Formel ein (z.B. für A2:B14): =countnostrike(A2:B14). Drücken Sie dann die Eingabetaste, um das Ergebnis zu erhalten. Excel zählt dabei nur die Zellen innerhalb des ausgewählten Bereichs, die keine Durchstreichungsformatierung haben.
Hinweis: Im obigen Beispiel ist A2:B14 der auszuwertende Zellbereich. Passen Sie die Bereichsreferenz bei Bedarf für Ihr Arbeitsblatt an. Beachten Sie auch, dass zusammengeführte Zellen oder besondere Formatierungen manchmal die Ergebnisse beeinflussen können – überprüfen Sie, dass Ihr Bereich nur die vorgesehenen Zellen enthält.
Summe ohne durchgestrichene Zellen in Excel
Wenn Sie numerische Daten bearbeiten, bei denen bestimmte Einträge nicht mehr aktiv sind oder ignoriert werden sollen (d.h. sie haben eine Durchstreichungsformatierung), ist es oft notwendig, nur die gültigen Zahlen zu summieren. Dies ist häufig bei Budgettabellen, Lagerverwaltung oder Aufgabenverfolgung der Fall, wo durchgestrichene Einträge veraltete oder abgeschlossene Elemente darstellen, die von der Gesamtsumme ausgeschlossen werden müssen. Da die integrierte SUMME-Funktion in Excel nicht zwischen formatierten und nicht formatierten Zellen unterscheidet, ist eine benutzerdefinierte VBA-Funktion für diese Operation erforderlich.
1. Verwenden Sie ALT + F11, um den Editor Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul, um ein Modul hinzuzufügen, und fügen Sie den unten stehenden Code in dieses Modulfenster ein.
VBA-Code: Summe ohne durchgestrichene Zellen
Public Function ExcStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + pRng.Value
End If
Next
ExcStrike = xOut
End Function
3. Speichern und schließen Sie den VBA-Editor. Wählen Sie in Ihrem Zielarbeitsblatt eine leere Zelle aus und geben Sie die folgende Formel ein (z.B. für B2:B14): =excstrike(B2:B14). Drücken Sie die Eingabetaste, und Excel summiert nur die Zellen in Ihrem angegebenen Bereich, die keine Durchstreichungsformatierung haben und liefert sofort eine Summe, die veraltete Werte ausschließt.
Hinweis: Passen Sie B2:B14 an Ihren tatsächlichen Datenbereich an. Diese Funktion ignoriert durchgestrichene Zellen unabhängig von ihrem Wert. Wenn Sie unerwartete Ergebnisse erhalten, stellen Sie sicher, dass alle Zellen numerische Einträge und die richtige Formatierung haben.
Praxistipp: Wenn Sie die Summierung nach dem Ändern des Durchstreichungsstatus einer Zelle aktualisieren müssen, berechnen Sie das Arbeitsblatt neu, indem Sie F9 drücken.
Zählen oder Summieren von Zellen mit Durchstreichung mithilfe von Kutools für Excel
Wenn Sie nur die Zellen zählen oder summieren möchten, die ein Durchstreichungsformat haben, bietet Excel keine integrierte Möglichkeit dafür – aber Kutools für Excel macht es einfach mit seiner Spezialfilter-Funktion.
1. Spalte auswählen
Klicken Sie auf die Spalte, die die Zellen mit Durchstreichungsformatierung enthält.
2. Spezialfilter öffnen
Gehen Sie zur Registerkarte Kutools Plus, klicken Sie dann auf Spezialfilter > Filter Strikethrough.
3. Filter bestätigen
Ein Dialogfeld wird angezeigt, das zeigt, wie viele Zellen der Bedingung entsprechen. Klicken Sie auf OK, um fortzufahren.
4. Ergebnisse anzeigen
Die Zellen mit einer Durchstreichung sind nun gefiltert und sichtbar. Wählen Sie den gefilterten Bereich aus – Sie sehen sofort die Summe, den Durchschnitt und die Anzahl in der Statusleiste von Excel am unteren Rand.
✨ Warum Kutools verwenden?
Kutools für Excel bietet über 300 leistungsstarke und einfach zu verwendende Funktionen wie diese – hilft Ihnen Zeit zu sparen und Produktivität zu steigern, ohne komplexe Formeln oder VBA-Code schreiben zu müssen.
Ob Sie nach Format filtern, Daten bereinigen, Tabellenblätter kombinieren oder Stapel bearbeiten – Kutools vereinfacht Ihre Arbeit in Sekunden.
Testen Sie Kutools für Excel kostenlos für 30 Tage und entdecken Sie, was Sie bisher verpasst haben!
Verwandte Artikel:
Wie summiert/zählt man fettgedruckte Zahlen in einem Zellbereich in Excel?
Wie zählt und summiert man Zellen basierend auf der Hintergrundfarbe in Excel?
Wie zählt/summiert man Zellen basierend auf den Schriftfarben 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