Excel–Leerschritte stören beim ZÄHLENWENN

Eine von mehreren Personen gepflegte Liste wird mit ZÄHLENWENN und SUMMEWENN ausgewertet. Dabei fällt auf, dass immer wieder Einträge nicht mitgezählt werden. Des Rätsels Lösung: führende oder folgende Leerschritte.

Die Formel in Spalte F lautet:
=ZÄHLENWENN(A:A;E2)

Die Formel in Spalte G wird sein:
=SUMMENWENN(A:A;E2;B:B)

08.03.2021-0001

In dieser Liste wird Ulm 12 mal, München 14 mal gezählt – aber beide kommen 15 mal vor. Bei den Einträge von A5 und A8 ahnt man schon das Problem: ein führender Leerschritt. In A2 wurde dann noch ein nachfolgender Leerschritt entdeckt.

Dabei kann es vorkommen, dass nur ein Leerschritt gemacht wurde, es kommen aber auch doppelte Leerschritte vor.

Es gibt zwei Ansätze, das zu lösen. Im ersten Fall wird die Funktion GLÄTTEN verwendet. GLÄTTEN hat nur ein Argument: die Angabe der Zelle, die den Eintrag enthält. Mit GLÄTTEN werden alle führenden und schließenden Leerschritte entfernt, aber nicht die Leerschritte innerhalb des Textes in der Zelle. Im zweiten Fall wird nach allen Einträgen gesucht, die alles mögliche davor und danach haben können.

Lösung 1 mit einer Hilfsspalte

In Spalte C wird eine Hilfsberechnung mit der Funktion GLÄTTEN eingebaut. ZÄHLENWENN und SUMMEWENN werden dann auf C und nicht auf A geleitet. Die Spalte trägt bei mir die Überschrift “Bereinigt”.

08.03.2021-0002

Die Formel in G2 lautet:

=GLÄTTEN(A2)

Diese Formel wird nach unten ausgefüllt. Die beiden Formeln in F2 und G2 lauten jetzt:

=ZÄHLENWENN(C:C;E2)
=SUMMEWENN(C:C;E2;B:B)

08.03.2021-0003

Beide Formeln werden nach unten ausgefüllt.

Alternativ können Sie die Spalte C berechnen lassen, dann kopieren Sie den Inhalt und fügen ihn mit der Option “Werte” in Spalte A wieder ein. Damit überschreiben Sie die fehlerhaften Einträge mit den korrekt errechneten.

Lösung 2 ohne eine Hilfsspalte

Bei der zweiten Lösung ergänzen Sie den Suchbegriff von ZÄHLENWENN und SUMMEWENN um alle möglichen Zeichen vor und hinter dem Wort. Der Suchbegriff wird in Spalte E angegeben: E2 bis E4. Wenn “Ulm” sowohl mit führenden als auch mit schließenden Leerschritte gefunden werden soll, ergänze ich den Suchbegriff um das Merkmal *. Das * (Sternchen, Asterisk) steht für jedes beliebige Zeichen.

figure_trips_custom_text_13907 (1)Vorsicht! Dieses Vorgehen ist nicht geeignet, wenn beispielsweise auch die Städte “Neu-Ulm” oder “Münchenbernsdorf” vorkommen.
Das * findet alle Ortsnamen, die irgendwie beginnen, dann kommt Ulm, dann kommt wieder irgendwas. Es werden nicht nur Leerschritte gesucht!

Aber gesetzt den Fall, die Liste ist wie im Beispiel eindeutig und es gibt kein Neu-Ulm, kein Ulmet und kein Ulmen, dann sieht die Formel für ZÄHLENWENN und SUMMEWENN so aus:

=ZÄHLENWENN(A:A;“*“&E2&“*“)
=SUMMEWENN(A:A;“*“&E2&“*“;B:B)

Dabei ist E2 wieder der Suchbegriff, also der Ortsname. Davor und dahinter das * eingefasst in Anführungszeichen, weil es Text ist. Das & ist zuständig für das Anhängen des Zeichens an den Zellbezug: irgendwelche Zeichen plus E2 plus irgendwelche Zeichen.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s