Excel: Jokertekens in VLOOKUP en SUMIFS
Tijdens een recent Power Excel-seminar voor een IMA® chapter vroeg een deelnemer of er een manier was om VLOOKUP te gebruiken om een getal te vergelijken dat is ingesloten in een langere cel. Ze probeerde cheques uit een chequeregister te vergelijken met een lijst met goedgekeurde transacties van haar bank. De moeilijkheid: Het chequenummer was ingebed in een veld Transactiecode.
Het veld waarin ze moest zoeken bevatte een variabel aantal tekens als voorvoegsel, gevolgd door het chequenummer, en eindigend met nog meer tekens. De meeste mensen realiseren zich niet dat VLOOKUP jokertekens ondersteunt. In feite denkt het Excel team van Microsoft dat jokertekens vaker per ongeluk in VLOOKUPs terecht komen dan dat mensen ze opzettelijk gebruiken. Aangezien de meeste mensen niet weten dat VLOOKUP jokertekens ondersteunt, lijkt het waarschijnlijker dat mensen een VLOOKUP uitvoeren waarbij hun opzoekwaarde toevallig een teken bevat dat niet bedoeld was als jokerteken.
WILDCARDS GEBRUIKEN
De standaard jokertekens zijn een sterretje (*) voor een willekeurig aantal tekens, en een vraagteken (?) voor een willekeurig teken. Wat als u eigenlijk moet zoeken naar een sterretje of een vraagteken? Gebruik ~* om te zoeken naar een sterretje of ~? om te zoeken naar een vraagteken. Gebruik ~~ om te zoeken naar een tilde.
Het gebruik van deze drie tekens is vooral moeilijk als je honderden VLOOKUPs hebt en de waarden al wildcard tekens bevatten. Proberen om de waarden tijdens het vliegen om te zetten met behulp van geneste SUBSTITUTE functies wordt onhandelbaar. Stel je voor =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, “~”,”~~”), “*”,”~*”),”?”,”~?”). Het zou eigenlijk beter zijn als Microsoft een optie zou aanbieden die het jokerteken gedrag uitschakelt voor een bepaald werkblad of werkmap.
Terug naar het oorspronkelijke probleem. U zou moeten zoeken naar *appel* om het woord “appel” te vinden in elke tekst in de eerste kolom van de opzoektabel. Maar omdat cel A2 van Figuur 1 al “appel” bevat, begint de formule met =VLOOKUP(“*”&A2&”*”. In dit geval gebruikt u het aaneenschakelingsteken (&) om de waarde in A2 te omhullen met een sterretje aan elke kant. Als alternatief zou je =VLOOKUP(CONCATENATE(“*”,A2, “*”) kunnen gebruiken om het woord “appel” in sterretjes te wikkelen.
De formule =VLOOKUP(“*”&A2&”*”,$D$2:$E$6,2,FALSE) vindt correct de $956 uit cel E5.
Gebruik extra zorg bij het bekijken van de resultaten van de wildcard lookup. Wat als u zoekt op zowel “apple” als “crabapple”? Het zoeken naar “crabapple” zou correct zijn, maar omdat VLOOKUP de eerste overeenkomst in de opzoektabel teruggeeft, zou de VLOOKUP voor *apple* de waarde voor “crabapple” teruggeven als deze voor apple in de opzoektabel wordt gevonden.
VINDING EN OPTELLING VAN ALLE OVEREENKOMSTEN
Toen ik het voorbeeld op het Excel-seminar doorwerkte, vroeg iemand anders naar de situatie waarin er meerdere overeenkomsten kunnen zijn en u alle overeenkomende resultaten wilt vinden en optellen.
Omdat het jokerteken *appel* zo goed werkte met VLOOKUP, heb ik een gegevensverzameling gemaakt waarin we konden testen of SUMIFS ook jokertekens zou toestaan. In de SUMIFS functie hieronder zegt de formule om de waarden in kolom E op te tellen als de overeenkomstige waarde in kolom D *Apple* bevat. Deze formule werkt ook.
IMPROVING WILDCARD HANDLING
Ben je verbaasd te horen dat VLOOKUP met jokertekens overweg kan? Hebt u ooit een VLOOKUP uitgevoerd waarbij de opzoekwaarde een sterretje of vraagteken kan bevatten als onderdeel van normale tekst en niet bedoeld om als jokerteken te worden gebruikt?
Als het antwoord op een van deze vragen ja is, dan zou het waarschijnlijk zinvol voor u zijn als Microsoft een nieuwe, verbeterde versie van VLOOKUP zou aanbieden die jokertekens standaard negeert. Er zou een extra optioneel argument aan het eind van deze nieuwe functie kunnen worden toegevoegd, waarmee u Excel kunt vertellen jokertekens te gebruiken. Als je dat argument weglaat, dan zou Excel geen jokertekens gebruiken. U zou gemakkelijk kunnen zoeken naar “142*154” of “Waarom?” of “e*Trade” zonder dat u zich zorgen hoeft te maken dat een waarde als “Electrical Trade” als een valse overeenkomst wordt weergegeven.
Snellere VLOOKUPS KOMEN
Microsoft heeft de afgelopen maanden een nieuwe, snellere versie van VLOOKUP getest in het Insider Monthly-kanaal van Office 365-builds. De functie is vaak 10 keer zo snel als de oude VLOOKUP, vooral wanneer er duizenden lookups zijn tegen dezelfde tabel.
De nieuwe logica moet tegen het einde van 2019 worden uitgerold naar alle Office 365-abonnees. Als uw VLOOKUPs momenteel minder dan een seconde duren, merkt u misschien niets van de verandering. Maar als je een enorm werkblad hebt dat 40 seconden kost om te berekenen, verwacht dan dat die herberekeningstijd daalt tot ongeveer vier seconden of minder.