4 Excel Lookup-functies om efficiënt spreadsheets te zoeken

Meestal is het vinden van een cel in een Excel-spreadsheet vrij eenvoudig; als u niet alleen de rijen en kolommen daarvoor kunt doorzoeken, kunt u CTRL + F gebruiken om ernaar te zoeken. Maar als u met een hele grote spreadsheet werkt Hoe u een groot CSV Excel-spreadsheet in afzonderlijke bestanden kunt splitsen Een enorme spreadsheet van CSV Excel opsplitsen in afzonderlijke bestanden Een van de tekortkomingen van Microsoft Excel is de beperkte omvang van een spreadsheet. Als u uw Excel-bestand kleiner wilt maken of een groot CSV-bestand wilt splitsen, lees dan verder! Meer lezen, het kan veel tijd besparen om een ​​van deze vier opzoekfuncties te gebruiken. Ongeacht de grootte van uw Microsoft Excel-document, ze zullen veel efficiënter zijn.

De VLOOKUP-functie

Met deze functie kunt u een kolom en een waarde opgeven en een waarde retourneren uit de corresponderende rij van een andere kolom (als dat niet logisch is, wordt dit in een oogwenk duidelijk). Twee voorbeelden waarbij u dit kunt doen, zijn de achternaam van een werknemer opzoeken op het nummer van de werknemer of een telefoonnummer vinden door een achternaam op te geven. Dit is de syntaxis van de functie:

= VERT.ZOEKEN ([zoekwaarde,] [tabelmatrix], [kolom_index_num], [bereik_weergave])

De [lookup_value] is de informatie die u al hebt; Als u bijvoorbeeld wilt weten in welke staat een stad zich bevindt, is dit de naam van de stad. Met [table_array] kunt u de cellen opgeven waarin de functie zoekt naar de waarden voor opzoeken en retourneren. Zorg er bij het selecteren van uw bereik voor dat de eerste kolom in uw array degene is die uw opzoekwaarde zal omvatten! Dit is cruciaal. [col_index_num] is het nummer van de kolom die de geretourneerde waarde bevat.

[range_lookup] is een optioneel argument en neemt 1 of 0. Als u 1 invoert of dit argument weglaat, zoekt de functie naar de waarde die u hebt ingevoerd of naar het eerstvolgende laagste getal. Dus in de afbeelding hieronder, zal een VLOOKUP die op zoek is naar een SAT score van 652 646 terugkrijgen, omdat dit het dichtstbijzijnde getal in de lijst is dat kleiner is dan 652, en [range_lookup] standaard 1.

Laten we eens kijken hoe we dit kunnen gebruiken. Zoals ik heb in mijn artikelen over booleaanse operatoren in Microsoft Excel Mini Excel zelfstudie: Gebruik Boolean Logic om complexe gegevens te verwerken Mini Excel-zelfstudie: Gebruik Boolean Logic om complexe gegevenslogische operators te verwerken IF, NOT, AND en OR kunnen u helpen van Excel-beginner tot hoofdgebruiker. We leggen de basisprincipes van elke functie uit en laten zien hoe u ze kunt gebruiken voor maximale resultaten. Meer lezen en geavanceerd tellen en toevoegen van Mini Excel-zelfstudie: geavanceerde functies voor tellen en toevoegen gebruiken in Excel Mini Excel-zelfstudie: geavanceerde functies voor tellen en toevoegen van functies in Excel Het tellen en toevoegen van formules kan mondiaal lijken in vergelijking met meer geavanceerde Excel-formules. Ze kunnen u echter helpen veel tijd te besparen wanneer u informatie over de gegevens in uw spreadsheet moet verzamelen. Meer lezen, ik gebruik een spreadsheet die ik heb gegenereerd met generateata.com. Het bevat ID-nummers, voor- en achternaam, stad, staat en SAT-scores. Laten we zeggen dat ik de GEZETEN score van een persoon met de achternaam wil vinden “winters.” VLOOKUP maakt het gemakkelijk. Dit is de formule die we zouden gebruiken:

= VLOOKUP ("Winters", C2: F101, 4, 0)

Omdat de SAT-scores de vierde kolom zijn ten opzichte van de kolom met de achternaam, heb ik er 4 gebruikt voor het kolomindexargument. Houd er rekening mee dat wanneer u naar tekst zoekt, het instellen van [range_lookup] op 0 een goed idee is; zonder dit kunt u slechte resultaten behalen. Dit is wat Microsoft Excel ons biedt:

Het leverde 651 op, de GEZETEN score behorende bij de student genaamd Kennedy Winters, die in rij 92 staat (getoond in de inzet hierboven). Het zou veel langer hebben geduurd om door de naam te zoeken dan dat het deed om snel de syntaxis te typen!

VLOOKUP kan ook heel handig zijn als u Microsoft Excel gebruikt om uw belastingen te betalen. 5 Excel-formules die u moet weten Belastingen doen? 5 Excel-formules die u moet weten Het is twee dagen voordat uw belasting verschuldigd is en u geen nieuwe late indieningskosten wilt betalen. Dit is het moment om gebruik te maken van de kracht van Excel om alles op orde te krijgen. Lees verder .

Opmerkingen over VLOOKUP

Een paar dingen zijn goed om te onthouden wanneer u VERT.ZOEKEN gebruikt. Ten eerste, zoals ik al eerder heb vermeld, moet u ervoor zorgen dat de eerste kolom in uw bereik degene is die uw opzoekwaarde bevat. Als het niet in de eerste kolom staat, retourneert de functie onjuiste resultaten. Als uw kolommen goed zijn georganiseerd, zou dit geen probleem moeten zijn.

Het tweede ding om in gedachten te houden is dat VLOOKUP slechts één waarde zal teruggeven. Als we het hadden gebruikt “Georgië” als de opzoekwaarde, zou het de score van de eerste student uit Georgië hebben teruggegeven en geen aanwijzingen hebben gegeven dat er in feite twee studenten uit Georgië zijn.

De HLOOKUP-functie

Wanneer VLOOKUP overeenkomstige waarden in een andere kolom vindt, zoekt HLOOKUP overeenkomstige waarden in een andere rij. Omdat het meestal het gemakkelijkst is om door kolomkoppen te bladeren totdat u de juiste kolomtitel hebt gevonden en een filter gebruikt om te vinden waarnaar u zoekt, kunt u HORIZ.ZOEKEN het beste gebruiken als u werkelijk grote spreadsheets of je werkt met waarden die in de tijd zijn georganiseerd. Dit is de syntaxis:

= HLOOKUP ([zoekwaarde,] [tabelmatrix], [rij_index_num], [bereik_zoek])

[lookup_value], nogmaals, is de waarde die u kent en waarvoor u een overeenkomstige waarde wilt vinden. [table_array] is de cel waarin u wilt zoeken. [row_index_num] geeft de rij aan waar de geretourneerde waarde vandaan komt. En [range_lookup] is hetzelfde als hierboven; laat het leeg om de dichtstbijzijnde waarde te krijgen wanneer dat mogelijk is, of voer 0 in om alleen exacte overeenkomsten te zoeken.

Voor dit voorbeeld heb ik een nieuwe spreadsheet gemaakt met generateata.com. Deze bevat een rij voor elke staat, samen met een SAT-score (we zullen zeggen dat het de gemiddelde score voor de staat is) in de jaren 2000-2014. We gebruiken HLOOKUP om de gemiddelde score in Minnesota in 2013 te vinden. Hier is hoe we het doen:

= HLOOKUP (2013, A1: P51, 24)

(Merk op dat 2013 niet tussen aanhalingstekens staat omdat het een getal is, en geen reeks, ook komt de 24 uit Minnesota in de 24e rij.) Zoals u kunt zien in de onderstaande afbeelding, wordt de score geretourneerd:

Minnesotans was in 2013 gemiddeld 1014.

Opmerkingen over HORIZ.ZOEKEN

Net als bij VERT.ZOEKEN moet de opzoekwaarde zich in de eerste rij van uw tabelmatrix bevinden; dit is zelden een probleem met HORIZ.ZOEKEN, omdat u meestal een kolomtitel gebruikt voor een opzoekwaarde. HLOOKUP retourneert ook slechts één waarde.

De INDEX- en MATCH-functies

INDEX en MATCH zijn twee verschillende functies, maar wanneer ze samen worden gebruikt, kunnen ze het zoeken in een grote spreadsheet een stuk sneller maken. Beide functies hebben nadelen, maar door ze te combineren, bouwen we op de sterke punten van beide. Eerst echter de syntaxis:

= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])

In INDEX is [array] de array waarin u zoekt. [rijnummer] en [kolomnummer] kunnen worden gebruikt om uw zoekopdracht te verfijnen; we zullen daar even naar kijken.

MATCH's [lookup_value] is een zoekterm die een tekenreeks of een getal kan zijn; [lookup_array] is de array waarin Microsoft Excel naar de zoekterm zoekt. [match_type] is een optioneel argument dat 1, 0 of -1 kan zijn; 1 geeft de grootste waarde terug die kleiner is dan of gelijk is aan uw zoekterm; 0 retourneert alleen je exacte termijn; en -1 retourneert de kleinste waarde die groter is dan of gelijk is aan uw zoekterm.

Het is misschien niet duidelijk hoe we deze twee functies samen gaan gebruiken, dus ik zal het hier uitleggen. MATCH neemt een zoekterm en retourneert een celverwijzing. In de onderstaande afbeelding kunt u zien dat bij een zoekopdracht naar de waarde 646 in kolom F, MATCH 4 retourneert.

INDEX daarentegen doet het tegenovergestelde: er is een celverwijzing voor nodig en de waarde wordt erin vermeld. U kunt hier zien dat INDEX terugkeert wanneer wordt verteld dat de zesde cel van de kolom Stad moet worden geretourneerd “Ankerplaats,” de waarde van rij 6.

Wat we gaan doen is de twee combineren, zodat MATCH een celverwijzing retourneert en INDEX die verwijzing gebruikt om de waarde in een cel op te zoeken. Laten we zeggen dat je je herinnert dat er een student was wiens achternaam Waters was, en je wilt zien wat de score van deze student was. Dit is de formule die we zullen gebruiken:

= INDEX (F: F, MATCH ("Waters", C: C, 0))

Je zult opmerken dat het zoektype hier is ingesteld op 0; wanneer u op zoek bent naar een string, dat is wat u wilt gebruiken. Dit is wat we krijgen als we die functie uitvoeren:

Zoals je kunt zien aan de onderkant, scoorde Owen Waters 1720, het getal dat verschijnt als we de functie uitvoeren. Dit lijkt misschien niet zo handig als je alleen een paar kolommen kunt bekijken, maar stel je voor hoeveel tijd je zou besparen als je het 50 keer zou moeten doen in een grote database-spreadsheet Excel Vs. Access - Kan een spreadsheet een database vervangen? Excel Vs. Access - Kan een spreadsheet een database vervangen? Welke tool moet u gebruiken om gegevens te beheren? Toegang en Excel zijn beide voorzien van gegevensfiltering, sortering en query's. We laten u zien welke het beste geschikt is voor uw behoeften. Meer informatie bevatte enkele honderden kolommen!

Laat het zoeken beginnen

Microsoft Excel heeft veel extreem krachtige functies 3 Crazy Excel-formules die geweldige dingen doen 3 Crazy Excel-formules die geweldige dingen doen Voorwaardelijke formatteringsformules in Microsoft Excel kunnen geweldige dingen doen. Hier volgen enkele handige trucs voor de productiviteit van de Excel-formule. Lees meer, en de vier hierboven genoemde krassen alleen op het oppervlak. Zelfs met dit vluchtige overzicht zou je echter veel tijd moeten kunnen besparen als je met grote spreadsheets werkt. Als je wilt zien hoeveel meer je met INDEX kunt doen, ga je naar “De inslagindex” bij Microsoft Excel Hero.

Hoe voer je zoekopdrachten uit in grote spreadsheets? Hoe heb je gevonden dat deze opzoekfuncties nuttig zijn?? Deel hieronder je gedachten en ervaringen!

Afbeelding Credits: vergrootglas op een spreadsheet Via Shutterstock

Ontdek meer over: Microsoft Excel, Spreadsheet.