Excel: Platzhalter in VLOOKUP und SUMIFS
Auf einem Power Excel-Seminar, das kürzlich für ein IMA®-Kapitel abgehalten wurde, fragte eine Teilnehmerin, ob es eine Möglichkeit gäbe, VLOOKUP zu verwenden, um eine Zahl abzugleichen, die in eine längere Zelle eingebettet ist. Sie versuchte, Schecks aus einem Scheckregister mit einer Liste von verrechneten Transaktionen ihrer Bank abzugleichen. Die Schwierigkeit: Die Schecknummer war in ein Feld für den Transaktionscode eingebettet.
Das Feld, das sie durchsuchen musste, enthielt eine variable Anzahl von Zeichen als Präfix, gefolgt von der Schecknummer und endete dann mit noch mehr Zeichen. Die meisten Leute wissen nicht, dass VLOOKUP Wildcards unterstützt. Das Excel-Team von Microsoft geht sogar davon aus, dass Wildcards häufiger versehentlich in VLOOKUPs vorkommen, als dass sie von den Benutzern absichtlich verwendet werden. Da die meisten Benutzer nicht wissen, dass VLOOKUP Platzhalter unterstützt, ist es wahrscheinlicher, dass sie ein VLOOKUP durchführen, bei dem der Suchwert zufällig ein Zeichen enthält, das nicht als Platzhalter gedacht war.
VERWENDUNG VON Platzhaltern
Die Standardplatzhalter sind ein Sternchen (*) für eine beliebige Anzahl von Zeichen und ein Fragezeichen (?) für ein beliebiges Zeichen. Was aber, wenn Sie tatsächlich nach einem Sternchen oder einem Fragezeichen suchen müssen? Verwenden Sie ~* für die Suche nach einem Sternchen oder ~? für die Suche nach einem Fragezeichen. Verwenden Sie ~~, um nach einer Tilde zu suchen.
Die Verwendung dieser drei Zeichen ist besonders schwierig, wenn Sie Hunderte von VLOOKUPs haben und die Werte bereits Platzhalterzeichen enthalten. Der Versuch, die Werte mit verschachtelten SUBSTITUTE-Funktionen zu konvertieren, wird unhandlich. Stellen Sie sich vor =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,“~“,“~~“), „*“,“~*“),“?“,“~?“). Es wäre tatsächlich besser, wenn Microsoft eine Option anbieten würde, die das Platzhalterverhalten für ein bestimmtes Arbeitsblatt oder eine Arbeitsmappe ausschaltet.
Zurück zum ursprünglichen Problem. Sie müssten nach *Apfel* suchen, um das Wort „Apfel“ in einem beliebigen Text in der ersten Spalte der Nachschlagetabelle zu finden. Da aber die Zelle A2 in Abbildung 1 bereits „Apfel“ enthält, beginnt die Formel mit =VLOOKUP(„*“&A2&“*“. In diesem Fall verwenden Sie das Verkettungszeichen (&), um den Wert in A2 mit einem Sternchen auf jeder Seite zu umschließen. Alternativ könnten Sie =VLOOKUP(CONCATENATE(„*“,A2, „*“) verwenden, um das Wort „Apfel“ in Sternchen zu verpacken.
Die Formel =VLOOKUP(„*“&A2&“*“,$D$2:$E$6,2,FALSE) findet korrekt die $956 aus Zelle E5.
Seien Sie besonders vorsichtig bei der Überprüfung der Ergebnisse der Platzhalter-Suche. Was wäre, wenn Sie sowohl nach „apple“ als auch nach „crabapple“ suchen würden? Die Suche nach „crabapple“ wäre korrekt, aber da VLOOKUP die erste Übereinstimmung in der Nachschlagetabelle zurückgibt, würde das VLOOKUP für *apple* den Wert für „crabapple“ zurückgeben, wenn es in der Nachschlagetabelle vor „apple“ gefunden wird.
FINDEN UND SUMMEN ALLER ÜBEREINSTIMMUNGEN
Als ich das Beispiel im Excel-Seminar durchging, fragte jemand nach der Situation, in der es mehrere Übereinstimmungen geben könnte und man alle übereinstimmenden Ergebnisse finden und summieren möchte.
Da der Platzhalter *Apfel* so gut mit VLOOKUP funktionierte, habe ich einen Datensatz erstellt, mit dem wir testen konnten, ob SUMIFS auch Platzhalter zulässt. In der unten dargestellten SUMIFS-Funktion besagt die Formel, dass die Werte in Spalte E addiert werden sollen, wenn der entsprechende Wert in Spalte D *Apfel* enthält. Diese Formel funktioniert ebenfalls.
IMPROVING WILDCARD HANDLING
Sind Sie überrascht, dass VLOOKUP mit Platzhaltern umgehen kann? Haben Sie schon einmal ein VLOOKUP durchgeführt, bei dem der Suchwert ein Sternchen oder ein Fragezeichen als Teil des normalen Textes enthält und nicht als Platzhalter verwendet werden soll?
Wenn die Antwort auf eine dieser Fragen „Ja“ lautet, wäre es für Sie wahrscheinlich sinnvoll, wenn Microsoft eine neue, verbesserte Version von VLOOKUP anbieten würde, die Platzhalter standardmäßig ignoriert. Es könnte ein zusätzliches optionales Argument am Ende dieser neuen Funktion geben, mit dem Sie Excel die Verwendung von Platzhaltern vorschreiben können. Wenn Sie dieses Argument weglassen, würde Excel keine Platzhalter verwenden. Sie könnten problemlos nach „142*154“ oder „Warum?“ oder „e*Trade“ suchen, ohne sich Sorgen machen zu müssen, dass ein Wert wie „Elektrohandel“ als falsche Übereinstimmung angezeigt wird.
FASTER VLOOKUPS ARE COMING
Microsoft hat in den letzten Monaten eine neue, schnellere Version von VLOOKUP im Insider Monthly-Kanal von Office 365-Builds getestet. Die Funktion ist oft 10-mal so schnell wie das alte VLOOKUP, insbesondere bei Tausenden von Suchvorgängen in derselben Tabelle.
Die neue Logik sollte bis Ende 2019 für alle Office 365-Abonnenten eingeführt werden. Wenn Ihre VLOOKUPs derzeit weniger als eine Sekunde benötigen, werden Sie die Änderung vielleicht nicht bemerken. Wenn Sie jedoch ein umfangreiches Arbeitsblatt haben, dessen Berechnung 40 Sekunden dauert, können Sie davon ausgehen, dass die Neuberechnungszeit auf etwa vier Sekunden oder weniger sinkt.