Excel Platzhalter: *, ? und ~ in ZÄHLENWENN und SVERWEIS

Excel kennt drei Platzhalterzeichen für Kriterien und Verweise: * steht für beliebig viele Zeichen (auch keines), ? steht für genau ein Zeichen, und ~ macht aus dem nächsten * oder ? wieder ein normales Zeichen. =ZÄHLENWENN(A2:A7;"*apple*") zählt die Zellen, die irgendwo apple enthalten. Die Tabelle zeigt die englische Schreibweise, ZÄHLENWENN heißt dort COUNTIF. Du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons: =ZÄHLENWENN($A$2:$A$7;C2).

FormelnZurücksetzen

D2fx

=COUNTIF($A$2:$A$7,C2)

	A	B	C	D
1	Product		Pattern	Count
2	Apple juice		*apple*	4
3	Green apple		apple*	2
4	Pineapple		*juice	2
5	Orange juice		?????	0
6	Pear		*e	4
7	Apples

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =ZÄHLENWENN($A$2:$A$7;C2)

• *apple* enthält apple: 4 Treffer, weil auch Pineapple zählt.
• apple* beginnt mit apple: nur Apple juice und Apples. ZÄHLENWENN ignoriert die Groß- und Kleinschreibung.
• *juice endet auf juice.
• ????? ist genau fünf Zeichen lang. Keines dieser Produkte hat fünf Zeichen, also 0; tippe Peach in A6, und es zählt 1.
• *e endet auf e.

Tippe in Spalte C ein eigenes Muster, etwa *an* oder P*, und die Zahl passt sich an.

Teilübereinstimmung in SVERWEIS und XVERWEIS

SVERWEIS (englisch VLOOKUP) nimmt Platzhalter im Modus für genaue Übereinstimmung an (FALSCH als letztes Argument). Häng den Platzhalter in der Formel an den Wert, damit in D2 nur die ersten Buchstaben stehen:

FormelnZurücksetzen

E2fx

=VLOOKUP(D2&"*",A2:B6,2,FALSE)

	A	B	C	D	E
1	Product	Price		Starts with	Price
2	Apple juice	3.5		Pin	4
3	Green apple	1.2			4
4	Pineapple	4
5	Orange juice	3.2
6	Pear	0.9

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SVERWEIS(D2&"*";A2:B6;2;FALSCH)

Beide Formeln finden Pineapple. In deutschem Excel lauten sie =SVERWEIS(D2&"*";A2:B6;2;FALSCH) und =XVERWEIS("*"&D2&"*";A2:A6;B2:B6;"none";2). Ändere D2 in juice: Das Muster juice* von SVERWEIS verlangt, dass der Text mit juice beginnt, und liefert #NV (englisch #N/A; die Tabelle zeigt die englischen Fehlernamen), während das Muster *juice* von XVERWEIS das erste Produkt findet, das es enthält, Apple juice. Wie jede genaue Übereinstimmung liefert ein Verweis mit Platzhalter die erste passende Zeile, also mach das Muster genau genug.

XVERWEIS (englisch XLOOKUP) behandelt * und ? nur dann als Platzhalter, wenn sein fünftes Argument, match_mode (Vergleichsmodus), 2 ist. Ohne das sucht es nach dem Sternchen selbst. VERGLEICH nimmt Platzhalter mit dem Vergleichstyp 0 an, und XVERGLEICH mit dem Vergleichsmodus 2, genau wie XVERWEIS. Die übrigen Argumente stehen unter SVERWEIS.

FormelnZurücksetzen

D2fx

	A	B	C	D
1	Product			Phone products
2	Smartphone
3	Headphones
4	Phone stand
5	Laptop bag
6	Charger

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Zähle in D2 die Produkte, deren Name irgendwo phone enthält.

PrüfenLösung zeigen

Summe und Mittelwert mit Platzhalter

Jede Funktion, die Kriterien annimmt, liest sie auf dieselbe Weise, also funktionieren dieselben Muster in SUMMEWENN, SUMMEWENNS, MITTELWERTWENN, MITTELWERTWENNS, MAXWENNS und MINWENNS:

FormelnZurücksetzen

E2fx

=SUMIF(A2:A7,D2,B2:B7)

	A	B	C	D	E
1	Region	Sales		Pattern	Total
2	North-East	120		North*	285
3	North-West	95		*West	155
4	South	80		?????	150
5	South-West	60
6	East	110
7	North	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMEWENN(A2:A7;D2;B2:B7)

North* addiert jede Region, die mit North beginnt, auch North selbst, weil * auch auf nichts passt. ????? addiert die Regionen mit genau fünf Zeichen: South und North.

FormelnZurücksetzen

E2fx

	A	B	C	D	E
1	Region	Sales			West total
2	North-East	120
3	North-West	95
4	South	80
5	South-West	60
6	East	110
7	North	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Bilde in E2 die Summe der Umsätze aller Regionen, deren Name auf West endet.

PrüfenLösung zeigen

Ein echtes Sternchen oder Fragezeichen mit ~ finden

Um Text zu zählen, der wirklich ein * oder ? enthält, setzt du eine Tilde davor. Die Tilde selbst schreibst du als ~~.

FormelnZurücksetzen

C2fx

=COUNTIF(A2:A5,"*~**")

	A	B	C
1	Note		Count
2	Rated 5*		1
3	Why?		1
4	Done		4
5	5 stars

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =ZÄHLENWENN(A2:A5;"*~**")

C2 zählt die Zellen mit einem echten Sternchen (nur A2), C3 die mit einem Fragezeichen. C4 zeigt die andere Seite: "*" allein passt auf jeden Text, also zählt es jede Textzelle, hier 4. Zahlen und leere Zellen überspringt es, deshalb ist ZÄHLENWENN(Bereich;"*") der übliche Weg, Textzellen zu zählen.

Welche Funktionen Platzhalter annehmen

SUCHEN nimmt Platzhalter innerhalb einer Formel an: =SUCHEN("b?d";"a bad day") liefert in Excel 3 (FINDEN und SUCHEN erklärt die Einzelheiten). Für FILTER nimmst du statt eines Musters ISTZAHL(SUCHEN(...)) als Bedingung.

Häufiger Fehler: ein Platzhalter nach =

Der Operator = liest nie Platzhalter: =A2="*apple*" fragt, ob in A2 die sieben Zeichen *apple* stehen. In Excel, mit Green apple in A2:

=A2="*apple*"                    FALSE
=IF(A2="*apple*","yes","no")     no

In deutschem Excel: =WENN(A2="*apple*";"yes";"no") liefert ebenfalls no. Setz die Prüfung stattdessen in ZÄHLENWENN, das für eine einzelne Zelle 1 oder 0 liefert, oder nimm SUCHEN:

FormelnZurücksetzen

B2fx

=IF(COUNTIF(A2,"*apple*"),"contains apple","no")

	A	B	C
1	Product	COUNTIF test	SEARCH test
2	Green apple	contains apple	contains apple
3	Pear	no	no

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =WENN(ZÄHLENWENN(A2;"*apple*");"contains apple";"no")

WENN behandelt die 1 von ZÄHLENWENN als WAHR und die 0 als FALSCH. Die Variante mit SUCHEN braucht gar keine Platzhalter, weil SUCHEN den Text ohnehin an jeder Stelle der Zelle sucht.