Veel mensen proberen Excel als database te gebruiken. Hoewel het kan werken als een database, zijn sommige van de taken die heel gemakkelijk zouden zijn in een databaseprogramma vrij complex in Excel. Een van deze taken is het matchen van twee lijsten op basis van een gemeenschappelijk veld; dit kan eenvoudig worden bereikt met Excel VERT.ZOEKEN. U zult de functie VERT.ZOEKEN bijzonder nuttig vinden, dus bekijk hieronder een voorbeeld van wanneer en hoe u deze functie kunt gebruiken.
Stel dat uw reisbureau u een maandrapport stuurt met alle plaatsen die uw medewerkers hebben bezocht. Het rapport gebruikt luchthavencodes in plaats van stadsnamen. Het zou handig zijn als u gemakkelijk de echte plaatsnaam zou kunnen invoeren in plaats van alleen de code.
Op internet vindt en importeert u een lijst met de plaatsnaam voor elke luchthavencode.
Maar hoe krijgt u deze informatie over elk record in het rapport?
- Gebruik de functie VERT.ZOEKEN. VLOOKUP staat voor "Vertical Lookup". Het kan op elk moment worden gebruikt dat u een lijst met gegevens heeft met het sleutelveld in de meest linkse kolom.
- Begin met het typen van de functie
=VLOOKUP(,. Typ Ctrl + A om hulp bij de functie te krijgen. - VERT.ZOEKEN heeft vier parameters nodig. De eerste is de stadscode in het oorspronkelijke rapport. In dit voorbeeld zou dat cel D4 zijn
- De volgende parameter is het bereik met uw opzoektabel. Markeer het bereik. Zorg ervoor dat u F4 gebruikt om het bereik absoluut te maken. (Een absolute verwijzing heeft een dollarteken voor zowel het kolomnummer als het rijnummer. Wanneer de formule wordt gekopieerd, blijft de verwijzing naar I3: J351 wijzen.
- De derde parameter vertelt Excel in welke kolom de stadsnaam voorkomt. In het bereik van I3: J351 staat de stadsnaam in kolom 2. Voer een 2 in voor deze parameter.
- De 4e parameter vertelt Excel of een "close" match OK is. In dit geval is dit niet het geval, dus voer False in.
- Klik op OK om de formule te voltooien. Sleep de vulgreep om de formule naar beneden te kopiëren.
- Omdat u zorgvuldig absolute formules heeft ingevoerd, kunt u kolom E naar kolom D kopiëren om de stad van bestemming te krijgen. In dit geval vertrekken alle vluchten vanaf Pearson International Airport in Toronto.
Hoewel dit voorbeeld perfect werkte, betekent dit dat kijkers die VERT.ZOEKEN gebruiken meestal lijsten zoeken die uit verschillende bronnen kwamen. Als lijsten uit verschillende bronnen komen, kunnen er altijd subtiele verschillen zijn waardoor de lijsten moeilijk te matchen zijn. Hier zijn drie voorbeelden van wat er mis kan gaan en hoe u deze kunt corrigeren.
-
De ene lijst heeft streepjes en de andere niet. Gebruik de
=SUBSTITUTE()functie om de streepjes te verwijderen. De eerste keer dat u VERT.ZOEKEN probeert, krijgt u N / A-fouten.Gebruik de formule SUBSTITUTE om de streepjes met een formule te verwijderen. Gebruik 3 argumenten. Het eerste argument is de cel met de waarde. Het volgende argument is de tekst die u wilt wijzigen. Het laatste argument is de vervangende tekst. In dit geval wilt u streepjes in niets veranderen, dus de formule is
=SUBSTITUTE(A4,"-","").U kunt die functie in VERT.ZOEKEN plaatsen om de beschrijving te krijgen.
-
Deze is subtiel, maar heel gebruikelijk. Een lijst heeft een lege spatie achter de invoer. Gebruik = TRIM () om overtollige spaties te verwijderen. Wanneer u de formule voor het eerst invoert, ziet u dat alle antwoorden N / A-fouten zijn. U weet zeker dat de waarden in de lijst staan en dat alles er goed uitziet met de formule.
Een standaard ding om te controleren is om naar de cel met de opzoekwaarde te gaan. Druk op F2 om de cel in de bewerkingsmodus te zetten. In de bewerkingsmodus kunt u zien dat de cursor één spatie verwijderd is van de laatste letter. Dit geeft aan dat er een spatie achter het item staat.
Gebruik de TRIM-functie om het probleem op te lossen.
=TRIM(D4)verwijdert voorloopspaties, volgspaties en vervangt interne dubbele spaties door een enkele spatie. In dit geval werkt TRIM perfect om de volgspatie te verwijderen.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)is de formule. -
Ik noemde een bonustip in de shownotities: hoe het # N / A-resultaat voor ontbrekende waarden te vervangen door een spatie. Als uw opzoekwaarde niet in de opzoektabel staat, retourneert VERT.ZOEKEN een N / A-fout.
Deze formule gebruikt de
=ISNA()functie om te detecteren of het resultaat van de formule een N / A-fout is. Als u de fout krijgt, zal het tweede argument in de ALS-functie Excel vertellen om elke gewenste tekst in te voeren.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
Met VERT.ZOEKEN kunt u tijd besparen bij het matchen van lijsten met gegevens. Neem de tijd om het basisgebruik te leren en u zult veel krachtigere taken in Excel kunnen uitvoeren.
