Überprüfen auf einen von zwei Textwerten (Microsoft Excel)
Chris möchte Zellen, die den Textwert A oder den Textwert B enthalten, an einer beliebigen Stelle im Text der Zelle zählen. Wenn die Zelle sowohl A als auch B enthält, möchte sie sie zählen, aber nur einmal. Zum Beispiel hat Chris drei Zellen, die „Apfelsamen“, „Apfelbaum“ und „Pfirsichsamen“ enthalten, und sie möchte wissen, wie viele Zellen entweder „Apfel“ oder „Samen“ enthalten. (Die richtige Antwort, die zurückgegeben werden sollte, ist 3.)
Es gibt viele Möglichkeiten, wie dies angegangen werden kann. Bei der Prüfung von Lösungen habe ich nur die Lösungen untersucht, bei denen Zwischenantworten vermieden werden und die zusätzliche Spalten belegen. Die erste Lösung besteht darin, die COUNTIF-Funktion folgendermaßen zu verwenden:
=COUNTIF(A1:A9,"apple")+COUNTIF(A1:A9,"seed") -COUNTIF(A1:A9,"seedapple")-COUNTIF(A1:A9,"appleseed")
Die Formel zählt alle Zellen, die entweder „Apfel“ oder „Samen“ enthalten
und subtrahiert dann alle Zellen, die „Samen“ enthalten, gefolgt von „Apfel“
(beide Wörter befinden sich in der Zelle) oder „Apfel“, gefolgt von „Samen“ (dieselben Wörter in umgekehrter Reihenfolge).
Eine andere, etwas kürzere Lösung basiert auf den hier gezeigten Funktionen COUNTA und FIND:
=COUNTA(A1:A9)-SUMPRODUCT(--(ISERROR(FIND("apple",A1:A9)))
* --ISERROR(FIND("seed",A1:A9)))
Die Formel zählt die Zellen, die Werte enthalten, und subtrahiert dann alle Zellen, die weder „Apfel“ noch „Samen“ enthalten. Beachten Sie, dass bei der FIND-Funktion zwischen Groß- und Kleinschreibung unterschieden wird. Dies bedeutet, dass nur „Apple“ gefunden wird und nicht „Apple“ oder „APPLE“. Wenn Sie etwas benötigen, bei dem die Groß- und Kleinschreibung nicht berücksichtigt wird, ersetzen Sie FIND durch SEARCH.
Wenn Sie möchten, können Sie auch eine der Datenbankfunktionen von Excel verwenden.
Vorausgesetzt, Sie haben eine Spaltenüberschrift für Ihre ursprünglichen Phrasen, ist dies nicht so schwierig und führt zu der kürzesten Formel. Sie müssen lediglich eine entsprechende Kriterientabelle einrichten. Angenommen, Ihre Daten befinden sich in A1: A9, und die erste Zelle in der Spalte enthält eine Überschrift wie „Meine Sätze“. In einer anderen Spalte sollten Sie dieselbe Überschrift einfügen und dann in den beiden Zellen direkt darunter die beiden folgenden Formeln platzieren:
apple seed
Die Kriterien geben an, dass Sie mit allen Zellen übereinstimmen möchten, die „Apfel“ oder „Samen“ in der Zelle enthalten. Mit dieser Einstellung (ich gehe davon aus, dass Sie die Kriterientabelle in D1: D3 platziert haben) können Sie die folgende Formel verwenden:
=DCOUNTA(A1:A9,1,D1:D3)
Natürlich können Sie auch eine Array-Formel verwenden (eingegeben durch Drücken von Strg + Umschalt + Eingabetaste), um Ihre Antwort zu erhalten. Das Folgende ist eine solche Formel, die sich wiederum darauf stützt, dass die Phrasen in A1 überprüft werden: A9:
=SUM(--((ISNUMBER(FIND("apple",A1:A9))+ISNUMBER(FIND("seed",A1:A9)))>0))
Wenn Sie sich mehr für die Arbeit mit Makros interessieren, können Sie eine benutzerdefinierte Funktion erstellen, die die Anzahl für Sie zurückgibt. Das Folgende ist ein Beispiel für eines, das funktionieren wird:
Function FindTwoStrings(rng As Range, s1 As String, _ s2 As String) As Integer Application.Volatile If TypeName(rng) <> "Range" Then Exit Function Dim cell As Range For Each cell In rng.Cells If (InStr(1, UCase(cell.Value), UCase(s1), _ vbTextCompare) > 0) Or (InStr(1, UCase(cell.Value), _ UCase(s2), vbTextCompare) > 0) Then _ FindTwoStrings = FindTwoStrings + 1 Next cell End Function
Um die Funktion zu verwenden, können Sie diese Formel in einer Zelle verwenden:
=FindTwoStrings(A1:A9,"apple","seed")
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (9325) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier: