Home-theater-designers
Mesteparten av tiden, søker en Microsoft Excel regnearket er ganske enkelt. Hvis du ikke bare kan skanne gjennom radene og kolonnene for det, kan du bruke Ctrl + F for å søke etter det. Hvis du jobber med et veldig stort regneark, kan det spare mye tid å bruke en av disse fire oppslagsfunksjonene.
Når du vet hvordan du søker i Excel ved hjelp av oppslag, spiller det ingen rolle hvor store regnearkene dine blir, du vil 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 gir mening, blir det klart om et øyeblikk). To eksempler hvor du kan gjøre dette er å slå opp en ansattes etternavn etter sitt ansattnummer eller finne et telefonnummer ved å angi et etternavn.
Her er syntaksen til funksjonen:
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])- [oppslagsverdi] er informasjonen du allerede har. For eksempel, hvis du trenger å vite hvilken tilstand en by er i, vil det være navnet på byen.
- [tabell_array] lar deg spesifisere cellene der funksjonen skal se etter oppslags- og returverdiene. Når du velger området, må du sørge for at den første kolonnen som er inkludert i matrisen, er den som inkluderer oppslagsverdien din!
- [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 -score på 652 returnere 646, ettersom det er det nærmeste tallet i listen som er mindre enn 652, og [range_lookup] er som standard 1.
La oss ta en titt på hvordan du kan bruke dette. Dette regnearket inneholder ID -numre, for- og etternavn, by-, delstat- og SAT -score. 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 -poengsummene er den fjerde kolonnen over fra etternavnskolonnen, er 4 kolonneindeksargumentet. Vær oppmerksom på 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 -poengsummen tilhørende studenten Kennedy Winters, som er i rad 92 (vist på innlegget ovenfor). Det ville ha tatt mye lengre tid å bla gjennom å lete etter navnet enn det gjorde for å raskt skrive ut syntaksen!
Merknader om VLOOKUP
Noen få ting er gode å huske når du bruker VLOOKUP. Sørg for at den første kolonnen i området ditt er den som inkluderer oppslagsverdien din. Hvis den ikke er i den første kolonnen, vil funksjonen returnere feilaktige resultater. Hvis kolonnene dine er godt organisert, bør dette ikke være et problem.
Husk også at VLOOKUP bare noen gang vil returnere én verdi. Hvis du hadde brukt 'Georgia' som oppslagsverdi, ville det ha returnert poengsummen til den første studenten fra Georgia, og ikke gitt noen 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 lettest å 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 du jobber med verdier som er organisert etter tid .
Her er syntaksen til funksjonen:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])- [oppslagsverdi] er verdien du kjenner og vil finne en tilsvarende verdi for.
- [tabell_array] er cellene du vil søke i.
- [rad_indeks_nummer] angir raden som returverdien skal komme 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 stat, sammen med en SAT -score i årene 2000–2014. Du kan bruke HLOOKUP til å finne gjennomsnittlig poengsum i Minnesota i 2013. Slik gjør vi det:
=HLOOKUP(2013, A1:P51, 24)Som du kan se på bildet nedenfor, returneres poengsummen:
Minnesotans hadde et gjennomsnitt på 1014 i 2013. Vær oppmerksom på at 2013 ikke er i anførselstegn fordi det er et tall, og ikke en streng. De 24 kommer også fra Minnesota i den 24. raden.
Her er hvordan beregne det veide gjennomsnittet i Excel .
Merknader om HLOOKUP
Som med VLOOKUP må oppslagsverdien være i den første raden i tabellmatrisen. Dette er sjelden et problem med HLOOKUP, ettersom du vanligvis bruker en kolonnetittel for en oppslagsverdi. HLOOKUP returnerer også bare en enkelt verdi.
3-4. INDEX- og MATCH -funksjonene
INDEX og MATCH er to forskjellige funksjoner, men når de brukes sammen, kan de gjøre søk i et stort regneark mye raskere. Begge funksjonene har ulemper, men ved å kombinere dem bygger vi på styrken til begge.
Først, men syntaksen til begge funksjonene:
=INDEX([array], [row_number], [column_number])- [array] er matrisen du vil søke i.
- [radnummer] og [kolonne_nummer] kan brukes til å begrense søket ditt (vi tar en titt på det om et øyeblikk.)
=MATCH([lookup_value], [lookup_array], [match_type])- [oppslagsverdi] er et søkeord som kan være en streng eller et tall.
- [oppslag_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 det eksakte uttrykket ditt, og -1 vil returnere den minste verdien som er større enn eller lik søketermen.
Det er kanskje ikke klart hvordan vi skal bruke disse to funksjonene sammen, så jeg legger det ut her. MATCH tar et søkeord og returnerer en cellereferanse. På bildet nedenfor kan du se at i et søk etter verdien 646 i kolonne F returnerer MATCH 4.
INDEX, derimot, gjør det motsatte: den tar en cellereferanse og returnerer verdien i den. Du kan se her at når INDEX får beskjed om å returnere den sjette cellen i bykolonnen, returnerer INDEX 'Anchorage', verdien fra rad 6.
Det vi skal gjøre er å kombinere de to slik at MATCH returnerer en cellereferanse og INDEX bruker referansen til å slå opp verdien i en celle. La oss si at du husker at det var en student som het Waters etternavn, og du vil se hva denne studentens poengsum var. Her er formelen vi skal bruke:
tekst fra en falsk nummerapp
=INDEX(F:F, MATCH('Waters', C:C, 0))Du vil legge merke til 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 den 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 noen kolonner over, men tenk deg hvor mye tid du ville spare hvis du måtte gjøre det 50 ganger på en stort databaseark som inneholdt flere hundre kolonner!
La Excel -søk begynne
Microsoft Excel har mange ekstremt kraftige funksjoner for å manipulere data, og de fire som er oppført ovenfor, klør bare på overflaten. Å lære å bruke dem vil gjøre livet ditt mye lettere.
Hvis du virkelig vil mestre Microsoft Excel, kan du virkelig dra nytte av å ha Essential Excel Cheat Sheet nær hånden!
Bildekreditt: Cico/ Shutterstock
Dele Dele kvitring E -post Canon vs Nikon: Hvilket kameramerk er bedre?Canon og Nikon er de to største navnene i kameraindustrien. Men hvilket merke tilbyr den bedre serien med kameraer og objektiver?
Les neste Relaterte temaer- Produktivitet
- Regneark
- Microsoft Excel
- Søketriks
Ian Buckley er frilansjournalist, musiker, utøver og videoprodusent bosatt i Berlin, Tyskland. Når han ikke skriver eller står på scenen, pusler han med DIY elektronikk eller kode i håp om å bli en gal forsker.
Mer fra Ian BuckleyAbonner på vårt nyhetsbrev
Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!
Klikk her for å abonnere