4 Excel -opslagsfunktioner til effektiv søgning i regneark

4 Excel -opslagsfunktioner til effektiv søgning i regneark

For det meste søger du på en Microsoft Excel regneark er ret let. Hvis du ikke bare kan scanne gennem rækker og kolonner for det, kan du bruge Ctrl + F til at søge efter det. Hvis du arbejder med et virkelig stort regneark, kan det spare meget tid at bruge en af ​​disse fire opslagsfunktioner.





Når du ved, hvordan du søger i Excel ved hjælp af opslag, er det ligegyldigt, hvor store dine regneark bliver, du vil altid kunne finde noget i Excel!



1. Funktionen VLOOKUP

Denne funktion giver dig mulighed for at angive en kolonne og en værdi og returnerer en værdi fra den tilsvarende række i en anden kolonne (hvis det ikke giver mening, bliver det klart om et øjeblik). To eksempler, hvor du kan gøre dette, er at finde en medarbejders efternavn efter medarbejdernummeret eller finde et telefonnummer ved at angive et efternavn.





Her er funktionens syntaks:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [opslagsværdi] er den information, du allerede har. For eksempel, hvis du har brug for at vide, hvilken tilstand en by er i, ville det være navnet på byen.
  • [tabel_array] lader dig angive de celler, hvori funktionen skal lede efter opslag og returneringsværdier. Når du vælger dit område, skal du sørge for, at den første kolonne, der er inkluderet i dit array, er den, der inkluderer din opslagsværdi!
  • [col_index_num] er nummeret på den kolonne, der indeholder returværdien.
  • [range_lookup] er et valgfrit argument og tager 1 eller 0. Hvis du indtaster 1 eller udelader dette argument, leder funktionen efter den værdi, du har indtastet, eller det næstlaveste tal. Så i billedet herunder vil en VLOOKUP, der leder efter en SAT -score på 652, returnere 646, da det er det nærmeste nummer på listen, der er mindre end 652, og [range_lookup] er som standard 1.

Lad os se på, hvordan du kan bruge dette. Dette regneark indeholder id -numre, for- og efternavne, by, stat og SAT -score. Lad os sige, at du vil finde SAT -score for en person med efternavnet 'Winters'. VLOOKUP gør det let. Her er formlen, du vil bruge:



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

Fordi SAT -score er den fjerde kolonne over fra efternavnskolonnen, er 4 kolonneindeksargumentet. Bemærk, at når du leder efter tekst, er det en god idé at indstille [range_lookup] til 0. Uden det kan du få dårlige resultater.

Her er resultatet:



Det returnerede 651, SAT -score tilhørende eleven ved navn Kennedy Winters, som er i række 92 (vist i indføjelsen ovenfor). Det ville have taget meget længere tid at rulle igennem og lede efter navnet, end det gjorde for hurtigt at indtaste syntaksen!

Noter om VLOOKUP

Et par ting er gode at huske, når du bruger VLOOKUP. Sørg for, at den første kolonne i dit område er den, der indeholder din opslagsværdi. Hvis den ikke er i den første kolonne, returnerer funktionen forkerte resultater. Hvis dine kolonner er velorganiseret, burde dette ikke være et problem.



Husk også, at VLOOKUP kun nogensinde vil returnere en værdi. Hvis du havde brugt 'Georgia' som opslagsværdi, ville det have returneret scoren for den første elev fra Georgien og ikke givet nogen indikation af, at der faktisk er to studerende fra Georgien.

2. HLOOKUP -funktionen

Hvor VLOOKUP finder tilsvarende værdier i en anden kolonne, finder HLOOKUP tilsvarende værdier i en anden række. Fordi det normalt er lettest at scanne gennem kolonneoverskrifter, indtil du finder den rigtige og bruger et filter til at finde det, du leder efter, bruges HLOOKUP bedst, når du har virkelig store regneark, eller du arbejder med værdier, der er organiseret efter tid .

Her er funktionens syntaks:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [opslagsværdi] er den værdi, du kender og vil finde en tilsvarende værdi for.
  • [tabel_array] er de celler, du vil søge i.
  • [row_index_num] angiver rækken, som returværdien kommer fra.
  • [range_lookup] er det samme som i VLOOKUP, lad det stå tomt for at få den nærmeste værdi, når det er muligt, eller indtast 0 for kun at kigge efter nøjagtige matches.

Dette regneark indeholder en række for hver stat sammen med en SAT -score i årene 2000–2014. Du kan bruge HLOOKUP til at finde den gennemsnitlige score i Minnesota i 2013. Sådan gør vi det:

=HLOOKUP(2013, A1:P51, 24)

Som du kan se på billedet herunder, returneres scoren:

Minnesotans havde et gennemsnit på 1014 i 2013. Bemærk, at 2013 ikke er i anførselstegn, fordi det er et tal og ikke en streng. Også de 24 kommer fra Minnesota i den 24. række.

Her er hvordan man beregner det vejede gennemsnit i Excel .

Noter om HLOOKUP

Som med VLOOKUP skal opslagsværdien være i den første række i dit tabel array. Dette er sjældent et problem med HLOOKUP, da du normalt vil bruge en kolonnetitel til en opslagsværdi. HLOOKUP returnerer også kun en enkelt værdi.

3-4. INDEX- og MATCH -funktionerne

INDEX og MATCH er to forskellige funktioner, men når de bruges sammen, kan de gøre søgning i et stort regneark meget hurtigere. Begge funktioner har ulemper, men ved at kombinere dem bygger vi på begge styrker.

Først dog syntaksen for begge funktioner:

=INDEX([array], [row_number], [column_number])
  • [array] er den matrix, du vil søge i.
  • [række_nummer] og [kolonnenummer] kan bruges til at indsnævre din søgning (vi tager et kig på det om et øjeblik.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [opslagsværdi] er et søgeudtryk, der kan være en streng eller et tal.
  • [opslag_array] er matrixen, hvor Microsoft Excel vil søge efter søgeudtrykket.
  • [match_type] er et valgfrit argument, der kan være 1, 0 eller -1. 1 returnerer den største værdi, der er mindre end eller lig med dit søgeudtryk. 0 returnerer kun dit nøjagtige udtryk, og -1 returnerer den mindste værdi, der er større end eller lig med dit søgeudtryk.

Det er måske ikke klart, hvordan vi skal bruge disse to funktioner sammen, så jeg vil lægge det ud her. MATCH tager et søgeudtryk og returnerer en cellereference. På billedet herunder kan du se, at i en søgning efter værdien 646 i kolonne F returnerer MATCH 4.

INDEX gør derimod det modsatte: den tager en cellereference og returnerer værdien i den. Du kan her se, at INDEX returnerer 'Anchorage', værdien fra række 6, når du får besked på at returnere den sjette celle i bykolonnen.

Hvad vi skal gøre er at kombinere de to, så MATCH returnerer en cellereference og INDEX bruger denne reference til at slå værdien op i en celle. Lad os sige, at du husker, at der var en elev, hvis efternavn var Waters, og du vil se, hvad denne elevs score var. Her er formlen, vi vil bruge:

kunne ikke fuldføre din anmodning photoshop
=INDEX(F:F, MATCH('Waters', C:C, 0))

Du vil bemærke, at matchtypen er indstillet til 0 her. Når du leder efter en streng, er det det, du vil bruge. Her er hvad vi får, når vi kører den funktion:

Som du kan se fra indsatsen, scorede Owen Waters 1720, det nummer, der vises, når vi kører funktionen. Dette virker måske ikke så nyttigt, når du bare kan se et par kolonner over, men forestil dig, hvor meget tid du ville spare, hvis du skulle gøre det 50 gange på en stort databaseark der indeholdt flere hundrede kolonner!

Lad Excel -søgninger begynde

Microsoft Excel har en masse ekstremt kraftfulde funktioner til manipulation af data, og de fire anførte ovenfor kradser bare overfladen. At lære at bruge dem vil gøre dit liv meget lettere.

Hvis du virkelig vil mestre Microsoft Excel, kan du virkelig have fordel af at holde Essential Excel Cheat Sheet lige ved hånden!

Billedkredit: Cico/ Shutterstock

Del Del Tweet E -mail Canon vs Nikon: Hvilket kameramærke er bedre?

Canon og Nikon er de to største navne i kameraindustrien. Men hvilket mærke tilbyder den bedre serie af kameraer og objektiver?

Læs Næste Relaterede emner
  • Produktivitet
  • Regneark
  • Microsoft Excel
  • Søgetricks
Om forfatteren Ian Buckley(216 artikler udgivet)

Ian Buckley er freelance journalist, musiker, performer og videoproducent bosat i Berlin, Tyskland. Når han ikke skriver eller på scenen, piller han med DIY -elektronik eller kode i håb om at blive en gal videnskabsmand.

Mere fra Ian Buckley

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere