4 oppslagfunksjoner i Excel for å søke på regneark effektivt

• Produktivitet

Annonse

Det meste av tiden er ganske enkelt å søke i et Microsoft Excel-regneark. Hvis du ikke bare kan skanne gjennom radene og kolonnene etter det, kan du bruke Ctrl + F for å søke etter det. Hvis du jobber med et virkelig stort regneark, kan det spare mye tid å bruke en av disse fire oppslagsfunksjonene.

Lås opp juksearket "Essential Excel Formulas" nå!

Dette vil registrere deg på vårt nyhetsbrev

Når du vet hvordan du søker i Excel ved hjelp av oppslag, vil det ikke si hvor store regnearkene dine blir, vil du alltid kunne finne noe i Excel!

1. VLOOKUP-funksjonen

Denne funksjonen lar deg spesifisere en kolonne og en verdi, og vil returnere en verdi fra den tilsvarende raden i en annen kolonne (hvis det ikke er fornuftig, blir det klart om et øyeblikk). To eksempler der du kan gjøre dette er å slå opp en ansattes etternavn etter deres ansattnummer eller finne et telefonnummer ved å spesifisere et etternavn.

Her er syntaks for funksjonen:

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

• [lookup_value] er det informasjonsstykket du allerede har. Hvis du for eksempel trenger å vite hvilken stat en by er i, vil det være navnet på byen.
• [table_array] lar deg spesifisere hvilke celler funksjonen skal se etter oppslag- og returverdiene. Når du velger rekkevidden din, må du sørge for at den første kolonnen som er inkludert i matrisen, er den som vil inneholde oppslagsverdien!
• [col_index_num] er nummeret på kolonnen som inneholder returverdien.
• [range_lookup] er et valgfritt argument, og tar 1 eller 0. Hvis du skriver inn 1 eller utelater dette argumentet, ser funksjonen etter verdien du skrev inn eller det nest laveste tallet. Så på bildet nedenfor vil en VLOOKUP som leter etter en SAT-poengsum på 652 returnere 646, ettersom det er det nærmeste tallet på listen som er mindre enn 652, og [range_lookup] er standard til 1.

La oss se på hvordan du kan bruke dette. Dette regnearket inneholder ID-nummer, for- og etternavn, by-, stat- og SAT-poengsummer. La oss si at du vil finne SAT-poengsummen til en person med etternavnet “Winters.” VLOOKUP gjør det enkelt. Her er formelen du vil bruke:

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

Fordi SAT-score er den fjerde kolonnen fra etternavns-kolonnen, er 4 kolonneindeksargumentet. Merk at når du leter etter tekst, er det en god idé å sette [range_lookup] til 0. Uten det kan du få dårlige resultater.

Her er resultatet:

Den returnerte 651, SAT-poengsum tilhørende studenten ved navn Kennedy Winters, som er på rad 92 (vist i innsatsen ovenfor). Det ville tatt mye lengre tid å bla gjennom å lete etter navnet enn det gjorde for å raskt skrive ut syntaks!

Merknader om VLOOKUP

Noen få ting er godt å huske når du bruker VLOOKUP. Forsikre deg om at den første kolonnen i området ditt er den som inkluderer oppslagsverdien. Hvis det ikke er i den første kolonnen, vil funksjonen gi feil resultater. Hvis kolonnene dine er godt organisert, bør dette ikke være noe problem.

Husk også at VLOOKUP bare noen gang vil returnere en verdi. Hvis du hadde brukt “Georgia” som oppslagsverdi, ville det gitt poengsummen til den første studenten fra Georgia, og gitt ingen indikasjon på at det faktisk er to studenter fra Georgia.

2. HLOOKUP-funksjonen

Der VLOOKUP finner tilsvarende verdier i en annen kolonne, finner HLOOKUP tilsvarende verdier i en annen rad. Fordi det vanligvis er enklest å skanne gjennom kolonneoverskrifter til du finner den rette og bruker et filter for å finne det du leter etter, brukes HLOOKUP best når du har virkelig store regneark eller hvis du jobber med verdier som er organisert etter tid .

Her er syntaks for funksjonen:

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

• [lookup_value] er verdien du kjenner og vil finne en tilsvarende verdi for.
• [table_array] er cellene du vil søke i.
• [row_index_num] spesifiserer raden som returverdien kommer fra.
• [range_lookup] er det samme som i VLOOKUP, la det stå tomt for å få den nærmeste verdien når det er mulig, eller skriv inn 0 for bare å se etter eksakte treff.

Dette regnearket inneholder en rad for hver tilstand, sammen med en SAT-score i årene 2000–2014. Du kan bruke HLOOKUP for å finne gjennomsnittlig poengsum i Minnesota i 2013. Slik gjør vi det:

 =HLOOKUP(2013, A1:P51, 24) 

Som du ser på bildet under, blir poengsummen returnert:

Minnesotans var i gjennomsnitt 1014 i 2013. Merk at 2013 ikke er i anførselstegn fordi det er et tall og ikke en streng. De 24 kommer også fra Minnesota på 24. rad.

Merknader om HLOOKUP

Som med VLOOKUP, må oppslagsverdien være i den første raden i tabellarrayen. Dette er sjelden noe problem med HLOOKUP, siden du vanligvis bruker en kolonnetittel for en oppslagsverdi. HLOOKUP gir også bare en enkelt verdi.

3-4. Funksjonene INDEX og MATCH

INDEX og MATCH er to forskjellige funksjoner, men når de brukes sammen kan de gjøre det å søke i et stort regneark mye raskere. Begge funksjonene har ulemper, men ved å kombinere dem bygger vi på styrkene til begge.

Først men syntaks for begge funksjoner:

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

• [array] er matrisen du søker i.
• [rad_nummer] og [kolonne_nummer] kan brukes til å begrense søket ditt (vi ser på det i løpet av et øyeblikk.)

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

• [lookup_value] er et søkeord som kan være en streng eller et tall.
• [lookup_array] er matrisen der Microsoft Excel vil lete etter søkeordet.
• [match_type] er et valgfritt argument som kan være 1, 0 eller -1. 1 vil returnere den største verdien som er mindre enn eller lik søkeordet ditt. 0 vil bare returnere den eksakte termen din, og -1 vil returnere den minste verdien som er større enn eller lik søkeordet.

Det er kanskje ikke klart hvordan vi skal bruke disse to funksjonene sammen, så jeg skal legge det ut her. MATCH tar et søkeord og returnerer en cellehenvisning. På bildet nedenfor kan du se at i et søk etter verdien 646 i kolonne F returnerer MATCH 4.

INDEX gjør derimot det motsatte: det tar en cellereferanse og returnerer verdien i den. Her kan du se at når INDEX får beskjed om å returnere den sjette cellen i City-kolonnen, returnerer "Anchorage" verdien fra rad 6.

Det vi skal gjøre er å kombinere de to slik at MATCH returnerer en cellehenvisning og INDEX bruker den referansen til å slå opp verdien i en celle. La oss si at du husker at det var en student som etternavn var Waters, og du vil se hva denne studentens poengsum var. Her er formelen vi bruker:

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

Du vil merke at kamptypen er satt til 0 her. Når du leter etter en streng, er det det du vil bruke. Her er hva vi får når vi kjører denne funksjonen:

Som du kan se fra innsatsen, scoret Owen Waters 1720, tallet som vises når vi kjører funksjonen. Dette virker kanskje ikke så nyttig når du bare kan se på noen kolonner, men forestill deg hvor mye tid du ville spart hvis du måtte gjøre det 50 ganger på et stort datablad regneark Excel Vs. Tilgang - Kan et regneark erstatte en database? Excel Vs. Tilgang - Kan et regneark erstatte en database? Hvilket verktøy skal du bruke for å administrere data? Access og Excel har begge datafiltrering, sortering og spørring. Vi viser deg hvilken som passer best for dine behov. Les mer som inneholdt flere hundre kolonner!

La Excel-søkene begynne

Microsoft Excel har mange ekstremt kraftige funksjoner for å manipulere data, og de fire som er listet over, bare klør overflaten. Å lære hvordan du bruker dem vil gjøre livet ditt mye enklere.

Hvis du virkelig vil beherske Microsoft Excel, kan du virkelig ha nytte av å beholde det essensielle Excel-juksearket De essensielle Microsoft Excel-formlene og -funksjonene Cheat Sheet De essensielle Microsoft Excel-formlene og funksjonene Cheat Sheet Last ned dette Excel-formlene jukseark for å ta en snarvei gjennom verdens favoritt regnearksprogram. Les mer i nærheten!

Bildekreditt: Cico / Shutterstock

Utforsk mer om: Microsoft Excel, søketriks, regneark.