Inhalte
- Bei der SVERWEIS-Funktion wird zwischen Groß- und Kleinschreibung unterschieden
- LOOKUP-Funktion für die Suche nach Groß- und Kleinschreibung
- SUMMENPRODUKT – findet Textwerte, unterscheidet zwischen Groß- und Kleinschreibung, gibt aber nur Zahlen zurück
- INDEX + MATCH – Suche nach Groß- und Kleinschreibung nach jedem Datentyp
Dieses kleine Tutorial erklärt, wie man eine Funktion erstellt VPR (SVERWEIS) unterscheidet zwischen Groß- und Kleinschreibung, zeigt mehrere andere Formeln, die Excel unter Berücksichtigung der Groß- und Kleinschreibung durchsuchen kann, und weist auf die Stärken und Schwächen jeder Funktion hin.
Ich denke, jeder Excel-Benutzer weiß, welche Funktion die vertikale Suche durchführt. Das ist richtig, es ist eine Funktion VPR. Das wissen aber nur wenige VPR ist nicht case sensitive, dh Groß- und Kleinschreibung sind dabei identisch.
Hier ist ein kurzes Beispiel, das die Unfähigkeit demonstriert VPR Register erkennen. Angenommen in einer Zelle A1 enthält den Wert „Rechnung“ und die Zelle A2 – „Rechnung“, Formel:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… beendet die Suche nach „Rechnung“, da dieser Wert in der Liste an erster Stelle steht, und extrahiert den Wert aus der Zelle B1.
Später in diesem Artikel zeige ich Ihnen, wie das geht VPR Groß- und Kleinschreibung beachten. Darüber hinaus lernen wir einige weitere Funktionen kennen, die Groß- und Kleinschreibung in Excel durchführen können.
Wir beginnen mit dem Einfachsten – ANZEIGEN (NACHSCHLAGEN) und SUMMENPRODUKT (SUMPRODUCT), die leider mehrere erhebliche Einschränkungen haben. Als nächstes schauen wir uns die etwas komplexere Formel genauer an INDEX+VERGLEICH (INDEX+MATCH), die in jeder Situation und mit jedem Datensatz einwandfrei funktioniert.
Bei der SVERWEIS-Funktion wird zwischen Groß- und Kleinschreibung unterschieden
Wie Sie bereits wissen, die übliche Funktion VPR Groß- und Kleinschreibung wird nicht beachtet. Es gibt jedoch eine Möglichkeit, Groß- und Kleinschreibung zu beachten. Dazu müssen Sie der Tabelle eine Hilfsspalte hinzufügen, wie im folgenden Beispiel gezeigt.
Angenommen in einer Spalte B Es gibt Produktkennungen (Artikel) und Sie möchten den Preis des Produkts und den entsprechenden Kommentar aus den Spalten extrahieren C и D. Das Problem besteht darin, dass Bezeichner sowohl Klein- als auch Großbuchstaben enthalten. Zum Beispiel Zellenwerte B4 (001Tvci3u) und B5 (001Tvci3U) unterscheiden sich nur im letzten Zeichen, u и U beziehungsweise.
Wie Sie sich vorstellen können, die übliche Suchformel
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
wird zurückkehren 90 €, da der Wert 001Tvci3u ist im Suchbereich früher als 001Tvci3U. Aber das ist nicht das, was wir brauchen, oder?
Mit einer Funktion suchen VPR In Excel muss die Groß-/Kleinschreibung beachtet werden, Sie müssen eine Hilfsspalte hinzufügen und ihre Zellen mit der folgenden Formel füllen (wobei B die Nachschlagespalte ist):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Diese Formel zerlegt den gewünschten Wert in einzelne Zeichen, ersetzt jedes Zeichen durch seinen Code (z. B. statt A stattdessen mit 65 a Code 97) und kombiniert diese Codes dann zu einer eindeutigen Zahlenfolge.
Danach verwenden wir eine einfache Funktion VPR für Groß-/Kleinschreibung suchen:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Korrekter Betrieb der Funktion VPR Groß-/Kleinschreibung hängt von zwei Faktoren ab:
- Die Hilfsspalte muss die Spalte ganz links im sichtbaren Bereich sein.
- Der gesuchte Wert muss anstelle des realen Wertes einen Zeichencode enthalten.
So verwenden Sie die CODE-Funktion richtig
Die in die Zellen der Hilfsspalte eingefügte Formel geht davon aus, dass alle Ihre Suchwerte die gleiche Anzahl von Zeichen haben. Wenn nicht, müssen Sie die kleinsten und größten Zahlen kennen und so viele Merkmale hinzufügen IFERROR (IFERROR) wie viele Zeichen sind die Differenz zwischen dem kürzesten und längsten gesuchten Wert.
Wenn der kürzeste Suchwert beispielsweise 3 Zeichen und der längste 5 Zeichen beträgt, verwenden Sie diese Formel:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Für Funktion PSTR (MID) Sie geben die folgenden Argumente an:
- 1. Argument – Text (Text) ist der Text oder Zellbezug, der die zu extrahierenden Zeichen enthält (in unserem Fall ist es B2)
- 2. Argument – start_num (start_position) ist die Position des ersten dieser zu extrahierenden Zeichen. du betrittst 1 in der ersten Funktion PSTR, 2 – in der zweiten Funktion PSTR usw.
- 3. Argument – Anzahl_Zeichen (number_of_characters) – Gibt die Anzahl der Zeichen an, die aus dem Text extrahiert werden sollen. Da wir immer nur 1 Zeichen benötigen, schreiben wir in allen Funktionen 1.
GRENZEN: Funktion VPR ist nicht die beste Lösung für Suchen mit Berücksichtigung der Groß- und Kleinschreibung in Excel. Zunächst ist der Anbau einer Hilfskolonne erforderlich. Zweitens leistet die Formel nur dann gute Arbeit, wenn die Daten homogen sind, bzw. die genaue Anzahl der Zeichen in den gesuchten Werten bekannt ist. Wenn dies nicht der Fall ist, ist es besser, eine der Lösungen zu verwenden, die wir unten zeigen.
LOOKUP-Funktion für die Suche nach Groß- und Kleinschreibung
Funktion ANZEIGEN (LOOKUP) verwandt VPR, seine Syntax ermöglicht jedoch Suchen mit Berücksichtigung der Groß- und Kleinschreibung, ohne eine Hilfsspalte hinzuzufügen. Verwenden Sie dazu ANZEIGEN kombiniert mit der Funktion GENAU (GENAU).
Wenn wir die Daten aus dem vorherigen Beispiel (ohne Hilfsspalte) nehmen, wird die folgende Formel die Aufgabe bewältigen:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formel sucht im Bereich A2: A7 exakte Übereinstimmung mit Zellenwert F2 unterscheidet zwischen Groß- und Kleinschreibung und gibt den Wert aus Spalte B derselben Zeile zurück.
Like VPRFunktion ANZEIGEN funktioniert gleichermaßen mit Text und numerischen Werten, wie Sie im folgenden Screenshot sehen können:
Wichtig! Damit die Funktion ANZEIGEN korrekt funktioniert haben, sollten die Werte in der Lookup-Spalte aufsteigend sortiert werden, also vom kleinsten zum größten.
Lassen Sie mich kurz erklären, wie die Funktion funktioniert GENAU in der oben gezeigten Formel, da dies der entscheidende Punkt ist.
Funktion GENAU vergleicht die beiden Textwerte im 1. und 2. Argument und gibt TRUE zurück, wenn sie genau gleich sind, oder FALSE, wenn sie es nicht sind. Wichtig ist uns, dass die Funktion GENAU Groß-/Kleinschreibung beachten.
Mal sehen, wie unsere Formel funktioniert ANSICHT+GENAU:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funktion GENAU vergleicht den Zellwert F2 mit allen Elementen in einer Spalte A (A2:A7). Gibt TRUE zurück, wenn eine exakte Übereinstimmung gefunden wird, andernfalls FALSE.
- Da geben Sie das erste Funktionsargument an ANZEIGEN Wert TRUE, extrahiert den entsprechenden Wert aus der angegebenen Spalte (in unserem Fall Spalte B) nur, wenn eine genaue Übereinstimmung gefunden wird, Groß- und Kleinschreibung beachten.
Ich hoffe, diese Erklärung war klar und Sie verstehen jetzt die Hauptidee. Wenn ja, dann werden Sie mit anderen Funktionen, die wir weiter analysieren werden, keine Schwierigkeiten haben, denn. Sie arbeiten alle nach dem gleichen Prinzip.
GRENZEN: Die Daten in der Nachschlagespalte müssen in aufsteigender Reihenfolge sortiert werden.
SUMMENPRODUKT – findet Textwerte, unterscheidet zwischen Groß- und Kleinschreibung, gibt aber nur Zahlen zurück
Wie Sie bereits aus der Überschrift verstanden haben, SUMMENPRODUKT (SUMPRODUCT) ist eine weitere Excel-Funktion, die Ihnen bei der Suche nach Groß- und Kleinschreibung hilft, aber nur numerische Werte zurückgibt. Wenn diese Option nicht zu Ihnen passt, können Sie sofort mit dem Bundle fortfahren INDEX+VERGLEICH, die für jeden Fall und für alle Datentypen eine Lösung bietet.
Lassen Sie mich zunächst kurz die Syntax dieser Funktion erklären, damit Sie die folgende Groß-/Kleinschreibung besser verstehen.
Funktion SUMMENPRODUKT multipliziert die Elemente der angegebenen Arrays und gibt die Summe der Ergebnisse zurück. Die Syntax sieht so aus:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Da wir eine case-sensitive Suche benötigen, verwenden wir die Funktion GENAU (EXACT) aus dem vorherigen Beispiel als einer der Multiplikatoren:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Wie du dich erinnerst, GENAU vergleicht den Zellwert F2 mit allen Elementen in einer Spalte A. Gibt TRUE zurück, wenn eine exakte Übereinstimmung gefunden wird, andernfalls FALSE. Bei mathematischen Operationen nimmt Excel TRUE als an 1, und FALSE für 0Des Weiteren SUMMENPRODUKT multipliziert diese Zahlen und summiert die Ergebnisse.
Nullen werden nicht gezählt, weil sie beim Multiplizieren immer ergeben 0. Schauen wir uns genauer an, was passiert, wenn eine exakte Übereinstimmung in einer Spalte vorliegt A gefunden und zurückgegeben 1… Funktion SUMMENPRODUKT multipliziert die Zahl in der Spalte B on 1 und gibt das Ergebnis zurück – genau die gleiche Zahl! Dies liegt daran, dass die Ergebnisse der anderen Produkte Null sind und die resultierende Summe nicht beeinflussen.
Leider die Funktion SUMMENPRODUKT kann nicht mit Textwerten und Daten arbeiten, da sie nicht multipliziert werden können. In diesem Fall erhalten Sie eine Fehlermeldung #WERT! (#WERT!) wie in einer Zelle F4 im Bild unten:
GRENZEN: Gibt nur numerische Werte zurück.
INDEX + MATCH – Suche nach Groß- und Kleinschreibung nach jedem Datentyp
Schließlich stehen wir kurz vor einer unbegrenzten und zwischen Groß- und Kleinschreibung unterscheidenden Suchformel, die mit jedem Datensatz funktioniert.
Dieses Beispiel kommt zum Schluss, nicht weil das Beste zum Nachtisch übrig bleibt, sondern weil Ihnen das Wissen aus den vorherigen Beispielen dabei helfen wird, die Groß-/Kleinschreibung besser und schneller zu verstehen. INDEX+VERGLEICH (INDEX+VERGLEICH).
Wie Sie wahrscheinlich erraten haben, die Kombination von Funktionen AUSGESETZTER и INDEX Wird in Excel als flexiblere und leistungsfähigere Alternative für verwendet VPR. Der Artikel INDEX und MATCH statt SVERWEIS verwenden erklärt perfekt, wie diese Funktionen zusammenarbeiten.
Ich fasse nur die wichtigsten Punkte zusammen:
- Funktion AUSGESETZTER (MATCH) sucht nach einem Wert in einem gegebenen Bereich und gibt seine relative Position zurück, d. h. die Zeilen- und/oder Spaltennummer;
- Als nächstes die Funktion INDEX (INDEX) gibt einen Wert aus einer angegebenen Spalte und/oder Zeile zurück.
Zur Formel INDEX+VERGLEICH Groß-/Kleinschreibung beachten könnte, müssen Sie nur eine Funktion hinzufügen. Es ist nicht schwer zu erraten, was es wieder ist GENAU (GENAU):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
In dieser Formel GENAU funktioniert wie in Verbindung mit der Funktion ANZEIGEN, und liefert das gleiche Ergebnis:
Beachten Sie, dass die Formel INDEX+VERGLEICH In geschweifte Klammern eingeschlossen ist eine Matrixformel, die Sie durch Drücken von vervollständigen müssen Strg + Umschalt + Enter.
Warum ist INDEX+MATCH die beste Lösung für die Suche nach Groß- und Kleinschreibung?
Die wichtigsten Vorteile des Bundles INDEX и AUSGESETZTER:
- Erfordert im Gegensatz zu nicht das Hinzufügen einer Hilfsspalte VPR.
- Erfordert keine Sortierung der Suchspalte, im Gegensatz zu ANZEIGEN.
- Funktioniert mit allen Arten von Daten – Zahlen, Text und Daten.
Diese Formel scheint perfekt, nicht wahr? Eigentlich ist es nicht. Und deshalb.
Angenommen, die Zelle in der Rückgabewertspalte, die dem Nachschlagewert zugeordnet ist, ist leer. Welches Ergebnis liefert die Formel? Nein? Mal sehen, was die Formel tatsächlich zurückgibt:
Hoppla, die Formel gibt Null zurück! Dies ist möglicherweise kein großes Problem, wenn Sie mit reinen Textwerten arbeiten. Wenn die Tabelle jedoch Zahlen enthält, darunter auch „echte“ Nullen, wird dies zu einem Problem.
Tatsächlich verhalten sich alle anderen Suchformeln (VLOOKUP, LOOKUP und SUMPRODUCT), die wir zuvor besprochen haben, auf die gleiche Weise. Aber Sie wollen die perfekte Formel, richtig?
Um eine Formel zwischen Groß- und Kleinschreibung zu unterscheiden INDEX+VERGLEICH perfekt, setzen Sie es in eine Funktion IF (IF), das eine Zelle mit einem Rückgabewert testet und ein leeres Ergebnis zurückgibt, wenn es leer ist:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
In dieser Formel:
- B ist eine Spalte mit Rückgabewerten
- 1+ ist eine Zahl, die die relative Position der Zelle dreht, die von der Funktion zurückgegeben wird AUSGESETZTER, an die reale Adresse der Zelle. Zum Beispiel in unserer Funktion AUSGESETZTER Sucharray angegeben A2: A7, also die relative Position der Zelle A2 werden wir 1, weil es das erste im Array ist. Aber die tatsächliche Position der Zelle A2 in der Spalte ist 2, also fügen wir hinzu 1um den Unterschied auszugleichen und die Funktion zu haben INDIREKT (INDIREKT) hat den Wert aus der gewünschten Zelle abgerufen.
Die Bilder unten zeigen die korrigierte Groß-/Kleinschreibung-Formel INDEX+VERGLEICH In Aktion. Es gibt ein leeres Ergebnis zurück, wenn die zurückgegebene Zelle leer ist.
Ich habe die Formel in Spalten umgeschrieben B: Dum die Formelleiste auf den Screenshot anzupassen.
Die Formel kehrt zurück 0wenn die zurückgegebene Zelle Null enthält.
Wenn du den Link willst INDEX и AUSGESETZTER eine Meldung angezeigt wird, wenn der Rückgabewert leer ist, können Sie ihn in die letzten Anführungszeichen ("") der Formel schreiben, zum Beispiel so:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
