4 Excel-opzoekfuncties om efficiënt in spreadsheets te zoeken

• produktiviteit

Advertentie

Meestal is het zoeken naar een Microsoft Excel-spreadsheet vrij eenvoudig. Als u er niet alleen door de rijen en kolommen naar kunt scannen, kunt u Ctrl + F gebruiken om ernaar te zoeken. Als u met een hele grote spreadsheet werkt, kan het veel tijd besparen om een ​​van deze vier opzoekfuncties te gebruiken.

Ontgrendel nu het cheatsheet "Essentiële Excel-formules"!

Hiermee meld je je aan voor onze nieuwsbrief

Als je eenmaal weet hoe je in Excel moet zoeken met behulp van lookup, maakt het niet uit hoe groot je spreadsheets worden, je kunt altijd iets vinden in Excel!

1. De VLOOKUP-functie

Met deze functie kunt u een kolom en een waarde opgeven en een waarde retourneren uit de overeenkomstige rij van een andere kolom (als dat niet logisch is, wordt dit meteen duidelijk). Twee voorbeelden waarin u dit kunt doen, zijn de achternaam van een werknemer opzoeken op basis van zijn werknemersnummer of een telefoonnummer zoeken door een achternaam op te geven.

Dit is de syntaxis van de functie:

 =VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup]) 

• [lookup_value] is het stukje informatie dat je 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 opzoek- en retourwaarden. Let er bij het selecteren van uw bereik op dat de eerste kolom in uw array degene is die uw opzoekwaarde bevat!
• [col_index_num] is het nummer van de kolom die de retourwaarde bevat.
• [bereik_opzoeken] 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 op één na laagste getal. Dus in de onderstaande afbeelding zal een VERT.ZOEKEN op zoek naar een SAT-score van 652 646 retourneren, omdat dit het dichtstbijzijnde getal in de lijst is dat lager is dan 652, en [bereik_verkenning] is standaard 1.

Laten we eens kijken hoe u dit zou kunnen gebruiken. Deze spreadsheet bevat ID-nummers, voor- en achternaam, stad, staat en SAT-scores. Stel dat u de SAT-score van een persoon met de achternaam 'Winters' wilt vinden. VLOOKUP maakt het u gemakkelijk. Hier is de formule die u zou gebruiken:

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

Omdat de SAT-scores de vierde kolom zijn ten opzichte van de achternaamkolom, is 4 het kolomindexargument. Merk op dat wanneer u op zoek bent naar tekst, het instellen van [bereik_opzoeken] op 0 een goed idee is. Zonder dit kun je slechte resultaten krijgen.

Dit is het resultaat:

Het leverde 651 op, de SAT-score van de student Kennedy Winters, die in rij 92 staat (weergegeven in de inzet hierboven). Het zou veel langer hebben gekost om door de naam te bladeren dan om de syntaxis snel te typen!

Opmerkingen over VERT.ZOEKEN

Een paar dingen zijn goed om te onthouden wanneer u VLOOKUP gebruikt. Zorg ervoor 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.

Houd er ook rekening mee dat VLOOKUP slechts één waarde zal retourneren. Als je 'Georgia' als de opzoekwaarde had gebruikt, zou het de score van de eerste student uit Georgia hebben geretourneerd en geen indicatie hebben gegeven dat er in feite twee studenten uit Georgia zijn.

2. De HLOOKUP-functie

Waar VLOOKUP overeenkomstige waarden in een andere kolom vindt, vindt HLOOKUP overeenkomstige waarden in een andere rij. Omdat het meestal het eenvoudigst is om door kolomkoppen te bladeren totdat je de juiste vindt en een filter gebruikt om te vinden wat je zoekt, kun je HLOOKUP het beste gebruiken als je hele grote spreadsheets hebt of als je met waarden werkt die op tijd zijn georganiseerd .

Dit is de syntaxis van de functie:

 =HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup]) 

• [lookup_value] is de waarde die u kent en waarvoor u een overeenkomstige waarde wilt vinden.
• [table_array] is de cellen waarin u wilt zoeken.
• [row_index_num] geeft de rij aan waaruit de geretourneerde waarde zal komen.
• [range_lookup] is hetzelfde als in VERT.ZOEKEN, laat dit leeg om de dichtstbijzijnde waarde te krijgen indien mogelijk of voer 0 in om alleen te zoeken naar exacte overeenkomsten.

Deze spreadsheet bevat een rij voor elke staat, samen met een SAT-score in de jaren 2000–2014. U kunt HLOOKUP gebruiken om de gemiddelde score in Minnesota in 2013 te vinden. Dit doen we als volgt:

 =HLOOKUP(2013, A1:P51, 24) 

Zoals je in onderstaande afbeelding kunt zien, wordt de score geretourneerd:

Minnesotans scoorde gemiddeld een score van 1014 in 2013. Merk op dat 2013 niet tussen aanhalingstekens staat omdat het een getal is en geen string. De 24 komt ook uit Minnesota dat op de 24e rij staat.

Opmerkingen over HLOOKUP

Net als bij VLOOKUP moet de opzoekwaarde zich in de eerste rij van uw tabelmatrix bevinden. Dit is zelden een probleem met HLOOKUP, omdat u meestal een kolomtitel gebruikt voor een opzoekwaarde. HLOOKUP retourneert ook slechts een enkele waarde.

3-4. De functies INDEX en MATCH

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

Allereerst echter de syntaxis van beide functies:

 =INDEX([array], [row_number], [column_number]) 

• [array] is de array waarin u gaat zoeken.
• [rij_nummer] en [kolom_nummer] kunnen worden gebruikt om uw zoekopdracht te verfijnen (we zullen er zo naar kijken).

 =MATCH([lookup_value], [lookup_array], [match_type]) 

• [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 retourneert de grootste waarde die kleiner is dan of gelijk is aan uw zoekterm. 0 retourneert alleen uw exacte term 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 leg het hier uit. VERGELIJKEN 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: het neemt een celverwijzing en retourneert de waarde erin. Je kunt hier zien dat INDEX, wanneer hem wordt gevraagd om de zesde cel van de kolom Stad te retourneren, 'Anchorage' retourneert, de waarde uit 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. Hier is de formule die we zullen gebruiken:

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

U zult zien dat het zoektype hier is ingesteld op 0. Als je op zoek bent naar een string, is dat wat je wilt gebruiken. Dit krijgen we als we die functie uitvoeren:

Zoals u aan de inzet kunt zien, scoorde Owen Waters 1720, het nummer dat verschijnt wanneer we de functie uitvoeren. Dit lijkt misschien niet zo handig als je gewoon een paar kolommen kunt bekijken, maar stel je voor hoeveel tijd je zou besparen als je het 50 keer moest doen in een grote database-spreadsheet Excel Vs. Toegang - Kan een spreadsheet een database vervangen? Excel versus Toegang - Kan een spreadsheet een database vervangen? Welke tool moet u gebruiken om gegevens te beheren? Access en Excel hebben beide gegevensfiltering, sortering en query's. We laten u zien welke het meest geschikt is voor uw behoeften. Lees meer dat honderden kolommen bevatte!

Laat de Excel-zoekopdrachten beginnen

Microsoft Excel heeft veel zeer krachtige functies voor het manipuleren van gegevens, en de vier hierboven genoemde krassen gewoon op het oppervlak. Leren hoe je ze kunt gebruiken, maakt je leven veel gemakkelijker.

Als u Microsoft Excel echt wilt beheersen, kunt u er echt baat bij hebben om het essentiële cheatsheet van Excel te behouden De essentiële cheatsheet van Microsoft Excel-formules en functies De essentiële cheatsheet van Microsoft Excel-formules en -functies Download dit cheatsheet van Excel-formules om een ​​snelkoppeling door de 's werelds favoriete spreadsheetprogramma. Meer lezen binnen handbereik!

Afbeelding tegoed: Cico / Shutterstock

Ontdek meer over: Microsoft Excel, Search Tricks, Spreadsheet.