Excel Wildcard: Finden und ersetzen, filtern, in Formeln mit Text und Zahlen verwenden

Alles, was Sie über Wildcards auf einer Seite wissen müssen: Was sie sind, wie man sie am besten in Excel benutzt und warum Wildcards nicht mit Zahlen arbeiten.

Wenn Sie nach etwas suchen, aber nicht genau genau das sind, sind Wildcards eine perfekte Lösung. Sie können sich eine Wildcard als einen Joker vorstellen, der jeden Wert annehmen kann. Es gibt nur 3 Wildcard -Charaktere in Excel (Sternchen, Fragezeichen und Tilde), aber sie können so viele nützliche Dinge tun!

• Excel Wildcard -Charaktere
• So finden und ersetzen Sie Wildcards in Excel
• So filtern Sie Daten mit Wildcards
• So verwenden Sie Wildcard in Excel -Formeln
  • Countif -Formel mit Wildcard
  • Vlookup -Formel mit Wildcard
• Excel Wildcard mit Zahlen

Excel Wildcard -Charaktere

In Microsoft Excel ist eine Wildcard eine besondere Art von Charakter, die einen anderen Charakter ersetzen kann. Mit anderen Worten, wenn Sie keinen genauen Charakter kennen, können Sie an diesem Ort eine Wildcard verwenden.

Die beiden gängigen Wildcard -Charaktere, die Excel erkennt, sind ein Sternchen (*) und ein Fragezeichen (?). Ein Tilde (~) macht sich hervorragend, um diese regulären Charaktere zu behandeln, nicht als Platzhalter.

Wildkarten werden in jeder Situation nützlich, wenn Sie ein teilweise Match benötigen. Sie können sie als Vergleichskriterien zum Filtern von Daten verwenden, Einträge mit einem gemeinsamen Teil oder zur Durchführung von Fuzzy -Matching in Formeln finden.

Sternchen als Platzhalter

Das Sternchen (*) ist der allgemeinste Wildcard -Charakter, der eine beliebige Anzahl von Zeichen darstellen kann. Zum Beispiel:

• CH* - Übereinstimmt jedem Wort, das mit "Ch" wie Charles , Check , Chess usw. beginnt.
• *CH - Ersetzen Sie eine Textzeichenfolge, die mit "CH" endet, z. B. März , Zoll , Fetch usw.
• * CH* - repräsentiert jedes Wort, das "ch" in einer beliebigen Position enthält, z. B. Tschad , Kopfschmerzen , Bogen usw.

Fragezeichen als Wildcard

Das Fragezeichen (?) Steht für ein einzelnes Zeichen . Es kann Ihnen helfen, genauer zu werden, wenn Sie nach einer teilweisen Übereinstimmung suchen. Zum Beispiel:

• ? - Übereinstimmung mit jedem Eintrag, der einen Charakter enthält, z. "A", "1", "-" usw.
• ? - Ersetzen Sie zwei Zeichen, zB "ab", "11", "a*" usw.
• ???-??? -repräsentiert jede Zeichenfolge, die 2 Gruppen von 3 Zeichen enthält, die mit einem Bindestrich wie ABC-Def , ABC-123 , 111-222 usw. getrennt sind.
• Pri? E - Matchprice , Stolz , Preis und dergleichen.

Tilde als Wildcard -Nullifikator

Die Tilde (~) wird vor einem Wildcard -Charakter die Wirkung einer Wildcard abbricht und verwandelt sie in einen wörtlichen Sternchen (~*), ein wörtliches Fragezeichen (~?) Oder eine wörtliche Tilde (~~). Zum Beispiel:

• *~? - Findet einen Eintrag, der mit Fragezeichen endet, z. B. was? , Jemand da? , usw.
• *~ **-Findet alle Daten, die ein Sternchen enthalten, z. *1 , *11* , 1-mar-2020* usw. In diesem Fall sind die Sternchen der 1. ^{und 3.} und ^3. Stern das zweite Sternchen.

Finden und ersetzen Sie Wildcards in Excel

Die Verwendung von Wildcard -Charakteren mit Excels Fund- und Ersatzfunktion ist sehr vielseitig. In den folgenden Beispielen werden einige gemeinsame Szenarien erörtert und Sie vor ein paar Einschränkungen warnen.

So suchen Sie mit Wildcard

Standardmäßig ist das Dialogfeld Find and Ersatz so konfiguriert, dass sie nach den angegebenen Kriterien überall in einer Zelle suchen, um nicht den gesamten Zellinhalt zu übereinstimmen. Wenn Sie beispielsweise "AA" als Suchkriterien verwenden, gibt Excel alle Einträge wie AA-01 , 01-AA , 01-AA-02 usw. zurück. Das funktioniert in den meisten Situationen großartig, aber unter bestimmten Umständen kann eine Komplikation sein.

Angenommen, Sie möchten im folgenden Datensatz die IDs finden, die aus 4 Zeichen bestehen, die mit einem Bindestrich getrennt sind. Also öffnen Sie den Dialogfeld Find and Taste und ersetzen Sie F) , geben Sie ??-?? In der Suche nach welcher Box und drücken Sie alles . Das Ergebnis sieht ein bisschen verwirrend aus, nicht wahr?

Technisch gesehen passen Saiten wie AAB-01 oder BB-002 auch die Kriterien an, weil sie ein ??-?? Substring. Klicken Sie, um diese aus den Ergebnissen auszuschließen, auf die Schaltfläche Optionen und aktivieren Sie das Feld "Ganze Zellinhalte) . Nun, Excel wird die Ergebnisse nur auf das ??-?? Saiten:

Wie man durch Wildcard ersetzt

Falls Ihre Daten einige Fuzzy -Übereinstimmungen enthalten, können Sie Wildcards Ihnen dabei helfen, sie schnell zu lokalisieren und zu vereinen.

Im folgenden Screenshot sehen Sie zwei Rechtschreibvariationen desselben Stadtheims und Gomel . Wir möchten beide durch eine andere Version ersetzen - Homyel . (Und ja, alle drei Schreibweisen meiner Heimatstadt sind korrekt und allgemein akzeptiert :)

Um teilweise Übereinstimmungen zu ersetzen, müssen Sie dies tun:

1. Drücken Sie Strg H, um die Registerkarte "Ersetzen" des Dialogfelds Suchen und Ersetzen zu öffnen.
2. Geben Sie im Find What Box den Wildcard -Ausdruck ein:? Omel
3. Geben Sie im Ersetzen mit Box den Ersatztext ein: Homyel
4. Klicken Sie auf die Schaltfläche Alle Taste.

Und beobachten Sie die Ergebnisse:

So finden und ersetzen Sie Wildcard -Charaktere

Um einen Charakter zu finden, den Excel als Wildcard erkennt, dh ein buchstäbliches Sternchen oder ein Fragezeichen, enthält eine Tilde (~) in Ihre Suchkriterien. Um beispielsweise alle Einträge mit Sternchen zu finden, geben Sie ~* in das Feld Finden Sie ein:

Wenn Sie die Sternchen durch etwas anderes ersetzen möchten, wechseln Sie in die Registerkarte Ersetzen und geben Sie das Interesse des Interesses für das Ersetzen durch Box ein. Lassen Sie alle gefundenen Sternchen mit dem Kästchen leer ersetzen und klicken Sie auf alle .

Filterdaten mit Wildcards in Excel

Excel Wildcards sind ebenfalls sehr nützlich, wenn Sie eine große Datensäule haben und diese Daten basierend auf dem Zustand filtern möchten.

In unserem Beispieldatensatz möchten Sie die IDs filtern, die mit "B" beginnen. Dafür machen Sie Folgendes:

1. Filter zu den Header -Zellen hinzufügen. Am schnellsten ist es, die STRL Shift L -Verknüpfung zu drücken.
2. Klicken Sie in der Spalte Zielspalte auf den Dropdown-Pfeil des Filters.
3. Geben Sie im Suchfeld Ihre Kriterien ein, b* in unserem Fall.
4. Klicken Sie auf OK .

Dadurch wird die Daten sofort auf den Wildcard -Kriterien basierend gefiltert, wie unten:

Wildcards können auch mit Advanced Filter verwendet werden, was es zu einer schönen Alternative zu regulären Ausdrücken (auch von Tech Gurus genannt) machen kann, die Excel nicht unterstützt. Weitere Informationen finden Sie in Excel Advanced Filter mit Platzhaltern.

Excel -Formeln mit Wildcard

Zunächst sollte angemerkt werden, dass eine ziemlich begrenzte Anzahl von Excel -Funktionen Wildcards unterstützen. Hier finden Sie eine Liste der beliebtesten Funktionen, die mit Formel -Beispielen ausmachen:

ADIVEFIF mit Wildcards - findet den durchschnittlichen (arithmetischen Mittelwert) der Zellen, die den angegebenen Zustand erfüllen.

ADIVEIFIFS - Gibt den Durchschnitt der Zellen zurück, die mehrere Kriterien erfüllen. Wie ADIVEFIF im obigen Beispiel erlaubt Platzhalter.

Countif mit Wildcard -Zeichen - zählt die Anzahl der Zellen basierend auf einem Kriterium.

Countifs mit Wildcards - zählt die Anzahl der Zellen basierend auf mehreren Kriterien.

Sumif mit Wildcard-Summenzellen mit Bedingung.

Sumifs - Fügt Zellen mit mehreren Kriterien hinzu. Wie Sumif im obigen Beispiel akzeptiert Wildcard -Charaktere.

Vlookup mit Wildcards - führt eine vertikale Suche mit teilweise Übereinstimmung durch.

Hlookup mit Wildcard - führt eine horizontale Suche mit teilweise Übereinstimmung durch.

Xlookup mit Wildcard -Charakteren - führt eine teilweise Übereinstimmung sowohl in einer Spalte als auch in einer Zeile durch.

Match -Formel mit Wildcards - findet ein teilweise Match und gibt seine relative Position zurück.

XMatch mit Wildcards - Ein moderner Nachfolger der Spielfunktion, der auch die Wildcard -Matching unterstützt.

Suche mit Wildcards-Im Gegensatz zur fälschlichen Fundfunktion versteht die unempfindliche Suche Wildcard-Charaktere.

Wenn Sie teilweise mit anderen Funktionen übereinstimmen müssen, die keine Platzhalter unterstützen, müssen Sie eine Problemumgehung wie Excel herausfinden, wenn Wildcard -Formel.

Die folgenden Beispiele zeigen einige allgemeine Ansätze zur Verwendung von Platzhaltern in Excel -Formeln.

Excel Countif Wildcard -Formel

Angenommen, Sie möchten die Anzahl der Zellen zählen, die den Text "AA" im Bereich A2: A12 enthalten. Es gibt drei Möglichkeiten, dies zu erreichen.

Am einfachsten ist es, Wildcard -Charaktere direkt in das Kriterienargument aufzunehmen:

=COUNTIF(A2:A12, "*AA*")

In der Praxis ist ein solches "Hardcoding" nicht die beste Lösung. Wenn sich die Kriterien zu einem späteren Zeitpunkt ändert, müssen Sie Ihre Formel jedes Mal bearbeiten.

Anstatt die Kriterien in der Formel einzugeben, können Sie sie in eine Zelle eingeben, z. B. E1, und die Zellreferenz mit den Wildcard -Zeichen verkettet. Ihre vollständige Formel wäre:

=COUNTIF(A2:A12,"*"&E1&"*")

Alternativ können Sie eine Wildcard -Zeichenfolge (* aa* in unserem Beispiel) in die Kriterienzelle (E1) eingeben und nur die Zellreferenz in die Formel einbeziehen:

=COUNTIF(A2:A12, E1)

Alle drei Formeln ergeben das gleiche Ergebnis, sodass man eine Frage Ihrer persönlichen Präferenz ist.

Notiz. Die Wildcard-Suche ist nicht sensibel , sodass die Formel sowohl den oberen Gehäuse als auch die Kleinbuchstaben wie AA-01 und AA-01 zählt.

Excel Wildcard Vlookup -Formel

Wenn Sie nach einem Wert suchen müssen, der in den Quelldaten keine genaue Übereinstimmung enthält, können Sie Wildcard -Zeichen verwenden, um eine teilweise Übereinstimmung zu finden.

In diesem Beispiel werden wir die IDs nachschlagen, die mit bestimmten Zeichen beginnen, und ihre Preise aus Spalte B zurückgeben. Um dies zu erledigen, geben Sie die eindeutigen Teile der Ziele in den Zellen D2, D3 und D4 ein und verwenden Sie diese Formel, um die Ergebnisse zu erhalten:

=VLOOKUP(D2&"*", $A$2:$B$12, 2, FALSE)

Die obige Formel geht an E1 und aufgrund der cleveren Verwendung von relativen und absoluten Zellen referenziert sie korrekt auf die folgenden Zellen.

Notiz. Da die Excel Vlookup -Funktion das erste gefundene Match zurückgibt, sollten Sie bei der Suche mit Wildcards sehr vorsichtig sein. Wenn Ihr Lookup -Wert mehr als einen Wert im Suchbereich entspricht, erhalten Sie möglicherweise irreführende Ergebnisse.

Excel Wildcard für Zahlen

Manchmal wird angegeben, dass Wildcards in Excel nur für Textwerte und nicht für Zahlen funktionieren. Dies ist jedoch nicht genau richtig. Mit der Find- und Ersetzungsfunktion sowie Filter funktionieren die Platzhalter sowohl für Text als auch für Zahlen gut.

Finden und ersetzen Sie durch Wildcard -Nummer

Im folgenden Screenshot verwenden wir * 4 * für die Suchkriterien, um nach den Zellen zu suchen, die die Ziffer 4 enthalten, und Excel findet sowohl Textzeichenfolgen als auch Zahlen:

Filter mit Wildcard -Nummer

Ebenso hat Excels Auto-Filter kein Problem damit, dass "4" Filternummern enthalten:

Warum Excel Wildcard nicht mit Zahlen in Formeln arbeitet

Wildcards mit Zahlen in Formeln sind eine andere Geschichte. Verwenden von Wildcard -Zeichen zusammen mit Zahlen (unabhängig davon, ob Sie die Zahl mit Wildcards umgeben oder eine Zellreferenz verkettet) einen numerischen Wert in eine Textzeichenfolge umwandelt. Infolgedessen erkennt Excel eine Zeichenfolge in einer Reihe von Zahlen nicht.

Zum Beispiel zählen beide folgenden Formeln die Anzahl der Zeichen, die "4" perfekt gut enthalten:

=COUNTIF(A2:A12, "*4*" )

=COUNTIF(A2:A12, "*"&E1&"*" )

Aber auch nicht digit 4 innerhalb einer Zahl identifizieren:

Wie man Wildcards für Zahlen funktioniert

Die einfachste Lösung besteht darin, Zahlen in Text umzuwandeln (z. B. durch die Verwendung des Textes zu Spalten) und dann ein normales Vlookup, Countif, Übereinstimmung usw. durchführen.

Um beispielsweise die Anzahl von Zellen zu erhalten, die mit der Zahl in E1 beginnen , ist die Formel:

=COUNTIF(B2:B12, E1&"*" )

In einer Situation, in der dieser Ansatz nicht praktisch akzeptabel ist, müssen Sie für jeden bestimmten Fall Ihre eigene Formel erarbeiten. Leider gibt es keine generische Lösung :( Unten finden Sie einige Beispiele.

Beispiel 1. Excel Wildcard -Formel für Zahlen

Dieses Beispiel zeigt, wie Zahlen Zahlen zählen, die eine bestimmte Ziffer enthalten. Nehmen wir in der folgenden Stichprobentabelle an, dass Sie berechnen möchten, wie viele Zahlen im Bereich B2: B12 "4" enthalten. Hier ist die Formel, die Sie verwenden sollten:

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))

Wie diese Formel funktioniert

Wenn Sie von innen nach außen arbeiten, ist dies die Formel:

Die Suchfunktion sucht nach der angegebenen Ziffer in jeder Zelle des Bereichs und gibt ihre Position zurück. Die Ausgabe ist das folgende Array:

{#VALUE!;1;#VALUE!;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}

Die IsNumber -Funktion nimmt es von dort aus und ändert eine beliebige Zahl in wahr und fehlerhaft in Falsch:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Ein doppelter unärer Operator (-) zwingt wahr und falsch auf 1 bzw. 0:

{0;1;0;0;1;0;0;1;0;0;0}

Schließlich fügt die Sumproduktfunktion die 1 hinzu und gibt die Anzahl zurück.

Notiz. Wenn Sie eine ähnliche Formel in Ihren Arbeitsblättern verwenden, sollten Sie in keinem Fall "$" oder ein anderes Währungssymbol in die Suchfunktion aufnehmen. Bitte denken Sie daran, dass dies nur ein "visuelles" Währungsformat ist, das auf die Zellen angewendet wird. Die zugrunde liegenden Werte sind nur Zahlen.

Beispiel 2. Wildcard -Formel für Daten

Die oben diskutierte Sumproduktformel funktioniert für Zahlen wunderbar, wird jedoch für Daten fehlschlagen. Warum? Weil intern Exceldates als Seriennummern speichert und die Formel diese Zahlen verarbeiten würde, nicht die in Zellen angezeigten Daten.

Um dieses Hindernis zu überwinden, verwenden Sie die Textfunktion, um Daten in Textzeichenfolgen zu konvertieren, und füttern Sie dann die Zeichenfolgen der Suchfunktion.

Abhängig davon, was Sie zählen möchten, können die Textformate variieren.

Um alle Daten in C2: C12 zu zählen, die in Tag, Monat oder Jahr "4" haben, verwenden Sie " mmddyyyy" :

=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "mmddyyyy")))))

Um nur die Tage zu zählen, die "4" Monate und Jahre ignorieren, verwenden Sie das Textformat " DD" :

=SUMPRODUCT(--(ISNUMBER(SEARCH("4",TEXT(C2:C12, "dd")))))

So verwenden Sie Wildcards in Excel. Ich hoffe, diese Informationen werden sich in Ihrer Arbeit als nützlich erweisen. Wie auch immer, ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Wildcards in Excel -Formeln (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel Wildcard: Finden und ersetzen, filtern, in Formeln mit Text und Zahlen verwenden. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!