4 Excel-opslag-funktioner til effektiv søgning på regneark

• Produktivitet

Reklame

Det meste af tiden er det let at søge i et Microsoft Excel-regneark. Hvis du ikke bare kan scanne gennem rækkerne og kolonnerne for det, kan du bruge Ctrl + F til at søge efter det. Hvis du arbejder med et rigtig stort regneark, kan det spare en masse tid at bruge en af ​​disse fire opslagsfunktioner.

Lås op for snydarket "Væsentlige Excel-formler"!

Dette vil tilmelde dig vores nyhedsbrev

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

1. VLOOKUP-funktionen

Denne funktion giver dig mulighed for at specificere 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 i et øjeblik). To eksempler, hvor du muligvis gør dette, er at finde en medarbejders efternavn ud efter deres medarbejdernummer eller finde et telefonnummer ved at angive et efternavn.

Her er syntaks for funktionen:

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

• [lookup_value] er det information, du allerede har. For eksempel, hvis du har brug for at vide, hvilken stat en by er i, ville det være navnet på byen.
• [table_array] giver dig mulighed for at specificere de celler, som funktionen skal se efter opslag og returneringsværdier i. Når du vælger dit interval, skal du være sikker på, at den første kolonne, der er inkluderet i dit array, er den, der indeholder din opslagvæ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, ser funktionen efter den værdi, du har indtastet, eller det næste laveste tal. Så på 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 standard til 1.

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

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

Da SAT-scoringerne er den fjerde kolonne fra efternavns-kolonnen, er 4 kolonneindeksargumentet. Bemærk, at når du leder efter tekst, er det en god ide at indstille [range_lookup] til 0. Uden det kan du få dårlige resultater.

Her er resultatet:

Den returnerede 651, SAT-score tilhørende den studerende ved navn Kennedy Winters, der er i række 92 (vist i indsættelsen ovenfor). Det ville have taget meget længere tid at rulle igennem på udkig efter navnet, end det gjorde for hurtigt at indtaste syntaks!

Bemærkninger til VLOOKUP

Et par ting er gode at huske, når du bruger VLOOKUP. Sørg for, at den første kolonne i dit interval er den, der inkluderer din opslagværdi. Hvis det ikke er i den første kolonne, returnerer funktionen forkerte resultater. Hvis dine kolonner er velorganiserede, skulle dette ikke være et problem.

Husk også, at VLOOKUP kun nogensinde returnerer en værdi. Hvis du havde brugt "Georgia" som opslagværdi, ville det have returneret scoren for den første studerende fra Georgien og ikke give 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 nemmest 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 rigtig store regneark, eller du arbejder med værdier, der er organiseret efter tiden .

Her er syntaks for funktionen:

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

• [lookup_value] er den værdi, du kender og vil finde en tilsvarende værdi for.
• [table_array] er de celler, du vil søge i.
• [række_index_nummer] specificerer den række, som returværdien kommer fra.
• [range_lookup] er det samme som i VLOOKUP, lad det være tomt for at få den nærmeste værdi, når det er muligt, eller indtast 0 for kun at se 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 gennemsnitlige en score på 1014 i 2013. Bemærk, at 2013 ikke er i tilbud, fordi det er et tal og ikke en streng. Desuden kommer de 24 fra Minnesota på 24. række.

Bemærkninger til HLOOKUP

Som med VLOOKUP, skal søgningsværdien være i den første række i din tabellarray. Dette er sjældent et problem med HLOOKUP, da du normalt bruger en kolonnetitel til en opslagvæ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 det meget hurtigere at søge i et stort regneark. Begge funktioner har ulemper, men ved at kombinere dem bygger vi på begge sider.

Først dog syntaks for begge funktioner:

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

• [array] er den matrix, som du søger i.
• [række_nummer] og [kolonne_nummer] kan bruges til at indsnævre din søgning (vi ser på det i et øjeblik.)

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

• [lookup_value] er et søgeudtryk, der kan være en streng eller et tal.
• [lookup_array] er den matrix, hvor Microsoft Excel søger 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 vil returnere den mindste værdi, der er større end eller lig med dit søgeudtryk.

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

På den anden side gør INDEX det modsatte: det tager en cellehenvisning og returnerer værdien i det. Du kan her se, at når INDEX får besked om at returnere den sjette celle i bykolonnen, returnerer INDEX "Forankring" værdien fra række 6.

Det, vi skal gøre, er at kombinere de to, så MATCH returnerer en cellehenvisning, og INDEX bruger den henvisning til at slå værdien op i en celle. Lad os sige, at du husker, at der var en studerende, hvis efternavn var Waters, og du vil se, hvad denne studerendes score var. Her er formlen, vi bruger:

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

Du vil bemærke, at kamptypen 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 denne funktion:

Som du kan se fra indsættelsen, scorede Owen Waters 1720, det nummer, der vises, når vi kører funktionen. Dette synes muligvis ikke så nyttigt, når du bare kan kigge over et par kolonner, men forestil dig, hvor meget tid du ville spare, hvis du skulle gøre det 50 gange på et stort datablad regneark Excel Vs. Adgang - Kan et regneark erstatte en database? Excel Vs. Adgang - Kan et regneark erstatte en database? Hvilket værktøj skal du bruge til at administrere data? Access og Excel har begge datafiltrering, sortering og forespørgsel. Vi viser dig, hvilken der passer bedst til dine behov. Læs mere, der indeholdt flere hundrede kolonner!

Lad Excel-søgninger begynde

Microsoft Excel har en masse ekstremt kraftfulde funktioner til at manipulere data, og de fire nævnt ovenfor skraber bare overfladen. At lære at bruge dem vil gøre dit liv meget lettere.

Hvis du virkelig ønsker at mestre Microsoft Excel, kan du virkelig drage fordel af at beholde det væsentlige Excel-snyderi De væsentlige Microsoft Excel-formler og -funktioner Cheat Sheet De væsentlige Microsoft Excel-formler og -funktioner Cheat Sheet Download dette Excel-formel snyderark for at tage en genvej gennem verdens foretrukne regnearksprogram. Læs mere tæt på hånden!

Billedkredit: Cico / Shutterstock

Udforsk mere om: Microsoft Excel, søgetricks, regneark.