Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie die SUMIFS-Funktion und Platzhalterzeichen verwenden, um Daten zu summieren, die Zellen entsprechen, die bestimmten Text in Excel und Google Sheets enthalten.
Summe, wenn Text enthält
Zuerst demonstrieren wir, wie man mit der SUMIFS-Funktion Daten summiert, die sich auf Zellen beziehen, die bestimmten Text enthalten.
Die SUMIFS-Funktion summiert Datenzeilen, die bestimmte Kriterien erfüllen. Seine Syntax lautet:
Dieses Beispiel summiert alles Spielstände mit einem Staatsname die „Dakota“ enthält, unter Verwendung der SUMIFS-Funktion und des Platzhalterzeichens *.
| 1 | =SUMIFS(C3:C9,B3:B9,"*Dakota*") |
Das Zeichen * lässt eine beliebige Anzahl (einschließlich Null) anderer Zeichen zu, die seinen Platz einnehmen.
In diesem Beispiel wird es verwendet, um alle Zellen zu finden, die den Text „Dakota“ enthalten. Bei dieser Suche wird die Groß-/Kleinschreibung nicht beachtet, daher wird „dakota“ mit „Dakota“ oder „DAKOTA“ gleichgesetzt. Norddakota und Süddakota enthalten „Dakota“ und sind daher in der Summe enthalten.
Summe, wenn Text mit beginnt
Das Zeichen * kann auch verwendet werden, um nach Zellen zu suchen, die mit dem angegebenen Text beginnen:
| 1 | =SUMIFS(C3:C9,B3:B9,"Neu*") |
New York, New Jersey, und New-Mexiko beginnen mit „Neu“ und sind somit in der Summe enthalten. Beachten Sie, dass beim Suchbegriff „Neu*“ die Textzelle mit „Neu“ beginnen muss; Es reicht nicht aus, diese Zeichen einfach zu enthalten.
Summe, wenn Text endet mit
Ebenso können wir alle zusammenfassen Spielstände zum Zustände auf „o“ enden mit:
| 1 | =SUMIFS(C3:C9,B3:B9,"*o") |
New-Mexiko und Ohio enden mit „o“ und sind somit in der Summe enthalten.
Verwendung der ? Platzhalterzeichen
Die ? Zeichen kann verwendet werden, um ein beliebiges einzelnes Zeichen in einer Textzeichenfolge darzustellen.
Dieses Beispiel findet alle Staatsnamen beginnend mit „Neu“, gefolgt von genau 7 Zeichen (einschließlich Leerzeichen).
| 1 | =SUMIFS(C3:C9,B3:B9,"Neu???????") |
New Jersey und New-Mexiko erfüllen diese Kriterien, aber New York tut es nicht, da nach „Neu“ in . nur 5 Zeichen folgen New York.
Beachten Sie, dass * und ? Platzhalterzeichen können bei Bedarf kombiniert werden, um sehr spezifische Suchbefehle zu erstellen. Dieses nächste Beispiel findet Staatsnamen die mit „N“ beginnen und ein „o“ vor dem letzten Zeichen des Strings enthalten. Dies schließt aus New-Mexiko; es beginnt mit „N“, hat aber kein „o“ vor dem letzten Zeichen.
| 1 | =SUMIFS(C3:C9,B3:B9,"N*o?*") |
Diese Funktion ist besonders nützlich, wenn Sie Produktcodes, Postleitzahlen oder Seriennummern durchsuchen, bei denen die Position jedes Zeichens eine bestimmte Bedeutung hat.
Verwenden des Zeichens ~ (Tilde)
Das Sonderzeichen ~ (bekannt als a Tilde) erlaubt uns die Behandlung von * oder ? Zeichen, als ob sie einfache Textwerte wären und sich nicht als Platzhalter verhalten.
Im folgenden Beispiel müssen wir die Lagerbestand wenn das Produktname stimmt speziell mit dem Text „Produkt ?“ überein:
| 1 | =SUMIFS(C3:C8,B3:B8,"Produkt ~?") |
Das ~ unmittelbar vor einem * oder einem ? Zeichen verwandelt es wieder in einen Textwert, also der Suchbegriff "Produkt ~?" wird verwendet, um eine genaue Textübereinstimmung mit „Produkt ?“ zu finden.
Kombinieren von SUMIFS-Platzhaltern mit Zellreferenzen
Normalerweise ist es nicht empfehlenswert, Werte in Formeln fest zu codieren. Stattdessen ist es flexibler, separate Zellen zu verwenden, um Werte für unsere Suchbegriffe anzugeben.
Um zu suchen, ob die Zellen den in Zelle E3 enthaltenen Text enthalten, können wir die SUMIFS-Funktion mit einem Zellbezug und * Platzhaltern verwenden:
| 1 | =SUMIFS(C3:C9,B3:B9,"*"&E3&"*") |
Beachten Sie, dass der Text "Dakota" durch den Zellbezug &E3& ersetzt wurde und die *-Zeichen in Anführungszeichen (" ") gesetzt wurden.
Mehrere Zellbezüge und Platzhalterzeichen können auch miteinander kombiniert werden. Finden Staatsnamen die mit dem Text in Zelle E3 beginnen und den Text in Zelle F3 gefolgt von mindestens 1 weiteren Zeichen enthalten, kann die folgende Formel verwendet werden:
| 1 | =SUMIFS(C3:C9,B3:B9,E3&"*"&F3&"?*") |
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir die Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | =SUMIFS(C3:C9,B3:B9,"*"&E3&"*") |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | =SUMIFS($C$3:$C$9,$B$3:$B$9,"*"&E3&"*") |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
Summe, wenn die Zelle einen bestimmten Text enthält, unter Verwendung von Platzhaltern in Google Tabellen
Diese Formeln funktionieren in Google Sheets genauso wie in Excel.
