Wie kann man durchgestrichene Zellen in Excel zählen oder summieren?
In Excel ist es gängige Praxis, bestimmten Zellen eine Durchstreichformatierung zuzuweisen, um Daten als veraltet, irrelevant oder ungültig zu kennzeichnen. Dieses visuelle Signal verbessert das Datenmanagement erheblich und hilft Ihnen, sich bei der Analyse auf die wirklich relevanten Informationen zu konzentrieren. Bei Berechnungen – etwa beim Zählen oder Summieren – möchten Sie jedoch möglicherweise Ergebnisse für durchgestrichene Zellen separat von denen ohne Durchstreichung erfassen. Ein fundiertes Verständnis, wie solche Szenarien effizient bewältigt werden, optimiert Ihren Arbeitsablauf und führt zu präziseren Erkenntnissen. Dieser Artikel stellt mehrere praktische Methoden vor, um Zellen mit oder ohne Durchstreichformatierung in Excel zu zählen und zu summieren – mithilfe anpassbarer VBA-Funktionen, die maximale Flexibilität für unterschiedlichste Anforderungen im Datenmanagement bieten.
Durchgestrichene Zellen mit VBA in Excel zählen
Zellen ohne Durchstreichung mit VBA in Excel zählen
Summe ohne durchgestrichene Zellen mit VBA in Excel
Zellen mit Durchstreichung zählen oder summieren mit Kutools für Excel
Durchgestrichene Zellen in Excel zählen
Wenn Sie die Gesamtanzahl der Zellen ermitteln möchten, die in einem bestimmten Bereich mit Durchstreichformatierung versehen sind, stoßen Sie an eine Grenze der integrierten Excel-Funktionen: Es gibt keine Standardformel, die Textformatierungen wie Durchstreichung erkennen kann. In solchen Fällen bietet sich eine benutzerdefinierte Funktion (UDF) in VBA als effiziente Lösung an – besonders wertvoll bei der Verwaltung von To-do-Listen, Inventaraktualisierungen oder anderen Szenarien, in denen Durchstreichungen genutzt werden, um abgeschlossene, ungültige oder veraltete Datensätze zu kennzeichnen.
1. Halten Sie die ALT + F11-Tasten 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 anschließend 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. Geben Sie nach der Code-Eingabe Ihre Arbeitsmappe zum Speichern (um das Makro zu behalten), schließen den VBA-Editor und kehren zu Ihrem Arbeitsblatt zurück. Geben Sie in einer leeren Zelle, in der das Ergebnis erscheinen soll, die folgende Formel ein (z. B. zur Prüfung durchgestrichener Zellen im Bereich)A2:B14):=CountStrike(A2:B14)
Tipp: Sie können A2:B14 durch jeden anderen Bereich ersetzen, den Sie prüfen möchten.
4. Drücken Sie die Enter-Taste, um die Formel auszuführen – Excel zeigt sofort die Anzahl aller durchgestrichenen Zellen im begrenzten Bereich an. So verfolgen Sie Ihren Fortschritt effizient oder entfernen bei Bedarf veraltete Einträge. Siehe Screenshot:
Hinweis: Damit dieser VBA-Code auch zukünftig verfügbar bleibt, speichern Sie die Datei im Format .xlsm (Excel-Arbeitsmappe mit Makros).
Zellen ohne Durchstreichung in Excel zählen
Es gibt Situationen, in denen Sie nur die Anzahl der Zellen zählen möchten, die in einem bestimmten Bereich keineDurchstreichformatierung aufweisen. Gerade in Projektverfolgungstabellen oder bei Verkaufsdaten benötigen Sie oft ausschließlich die aktiven bzw. gültigen Einträge – also jene ohne Durchstreichung – für weitere Auswertungen. Da die Standardfunktionen ZÄHLENWENN und ANZAHL2 von Excel Textformatierungen ignorieren, bietet eine maßgeschneiderte VBA-Lösung hier die nötige Präzision.
1. Drücken Sie ALT + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Gehen Sie zu Einfügen > Modul, um ein neues Code-Modul hinzuzufügen. Fügen Sie den folgenden Code in dieses Modulfenster ein.
VBA-Code: Zellen ohne Durchstreichung zählen
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 folgende Formel ein (z. B. für)A2:B14):=countnostrike(A2:B14) Drücken Sie anschließend Enter, um das Ergebnis zu erhalten. Excel zählt dann nur jene Zellen innerhalb des ausgewählten Bereichs, die keine Durchstreichformatierung besitzen.
Hinweis: Im obigen Beispiel ist A2:B14 der auszuwertende Zellbereich. Passen Sie die Bereichsangabe entsprechend Ihrem Arbeitsblatt an. Beachten Sie außerdem, dass zusammengeführte Zellen oder spezielle Formatierungen die Ergebnisse manchmal beeinflussen können – stellen Sie daher sicher, dass Ihr Bereich nur die gewünschten Zellen enthält.
Summe ohne durchgestrichene Zellen in Excel
Bei numerischen Daten, bei denen bestimmte Einträge nicht mehr aktiv sind oder ignoriert werden sollen – also durchgestrichen formatiert wurden –, ist es oft unerlässlich, ausschließlich die gültigen Zahlen zu summieren. Dies kommt häufig in Budgettabellen, im Bestandsmanagement oder bei der Aufgabenverfolgung vor, wo durchgestrichene Einträge veraltete oder abgeschlossene Elemente kennzeichnen, die aus den Summen ausgeschlossen werden müssen. Da die integrierte SUMME-Funktion von Excel keinen Unterschied zwischen durchgestrichenen und regulären Zellen macht, ist hierfür eine benutzerdefinierte VBA-Funktion 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. Schließen Sie den VBA-Editor. Wählen Sie in Ihrem Zielarbeitsblatt eine leere Zelle und geben Sie die folgende Formel ein (z. B. für)B2:B14):=excstrike(B2:B14) Drücken Sie Enter, und Excel summiert nur die Zellen in Ihrem angegebenen Bereich, die keine Durchstreichformatierung aufweisen – so erhalten Sie sofort eine Summe, die veraltete Werte ausschließt.
Hinweis: Passen Sie den Bereich B2:B14 an Ihren tatsächlichen Datenbereich an. Diese Funktion ignoriert durchgestrichene Zellen – unabhängig von ihrem Wert. Sollten unerwartete Ergebnisse auftreten, stellen Sie sicher, dass alle Zellen numerische Werte und die korrekte Formatierung aufweisen.
Praktischer Tipp: Wenn Sie die Summierung aktualisieren müssen, nachdem Sie den Durchstreichstatus einer Zelle geändert haben, drücken Sie F9, um das Arbeitsblatt neu zu berechnen.
Zellen mit Durchstreichung zählen oder summieren mit Kutools für Excel
Wenn Sie nur die Zellen zählen oder summieren möchten, die eine Durchstreichformatierung aufweisen, bietet Excel hierfür keine integrierte Möglichkeit – doch Kutools für Excel vereinfacht dies mithilfe seiner Funktion Spezialfilter.
1. Spalte auswählen
Klicken Sie auf die Spalte, die Zellen mit Durchstreichformatierung enthält.
2. Öffnen Sie den Spezialfilter
Wechseln Sie zur Registerkarte KUTOOLS PLUS, und klicken Sie dann auf Spezialfilter > Zellen mit Durchstreichungen filtern.
3. Filter bestätigen
Ein Dialogfeld erscheint und zeigt an, wie viele Zellen der Bedingung entsprechen. Klicken Sie auf OK, um fortzufahren.
4. Ergebnisse anzeigen
Die durchgestrichenen Zellen sind nun gefiltert und sichtbar. Markieren Sie den gefilterten Bereich – sofort erscheinen Summe, Mittelwert und Anzahl in der Statusleiste am unteren Rand von Excel.
✨Warum Kutools verwenden?
Kutools für Excel bietet über 300 leistungsstarke und einfach zu bedienende Funktionen wie diese – und spart Ihnen wertvolle Zeit, steigert Ihre Produktivität und das ganz ohne komplexe Formeln oder VBA-Code.
Egal, ob Sie nach Format filtern, Daten bereinigen, Tabellen zusammenführen oder Massenbearbeitungen durchführen – mit Kutools erledigen Sie all das im Handumdrehen.
Verwandte Artikel:
Wie summiert oder zählt man fett formatierte Zahlen in einem Zellbereich in Excel?
Wie zählt und summiert man Zellen in Excel basierend auf ihrer Hintergrundfarbe?
Wie zählt oder summiert man Zellen in Excel basierend auf ihrer Schriftfarbe?
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