4 Excel-oppslagsfunksjoner for å effektivt søke regneark

Mesteparten av tiden er det ganske enkelt å finne en celle i et Excel-regneark. Hvis du ikke bare kan skanne gjennom rader og kolonner for det, kan du bruke CTRL + F for å søke etter det. Men hvis du jobber med et veldig stort regneark Hvordan dele et stort CSV Excel-regneark i separate filer Slik splitter du et stort CSV Excel-regneark i separate filer En av Microsoft Excel's mangler er den begrensede størrelsen på et regneark. Hvis du trenger å gjøre Excel-filen mindre eller dele en stor CSV-fil, les videre! Les mer, det kan spare mye tid å bruke en av disse fire oppslagsfunksjonene. Uansett størrelsen på Microsoft Excel-dokumentet, blir de mye mer effektive.

VLOOKUP-funksjonen

Denne funksjonen lar deg spesifisere en kolonne og en verdi, og returnerer en verdi fra den tilsvarende raden i en annen kolonne (hvis det ikke gir mening, blir det klart i et øyeblikk). To eksempler hvor du kan gjøre dette, ser opp en ansattes etternavn ved hjelp av deres ansattes nummer eller å finne et telefonnummer ved å angi et etternavn. Her er syntaxen til funksjonen:

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

[Lookup_value] er den informasjonen du allerede har; for eksempel hvis du trenger å vite hvilken stat en by er i, ville det være navnet på byen. [table_array] lar deg angi hvilke celler som funksjonen vil se etter oppslag og returverdier. Når du velger rekkevidde, må du være sikker på at den første kolonnen som er inkludert i din matrise, er den som vil inneholde oppsummeringsverdien din! Dette er avgjørende. [col_index_num] er nummeret til kolonnen som inneholder returverdien.

[range_lookup] er et valgfritt argument, og tar 1 eller 0. Hvis du skriver inn 1 eller slipper dette argumentet, ser funksjonen etter verdien du angav eller det nest laveste nummeret. Så i bildet nedenfor vil en VLOOKUP som ser etter en SAT-poengsum på 652, returnere 646, da det er nærmeste nummer i listen som er mindre enn 652, og [range_lookup] som standard er 1.

La oss ta en titt på hvordan vi kan bruke dette. Som jeg har i artiklene mine om boolske operatører i Microsoft Excel Mini Excel Tutorial: Bruk boolsk logikk til å behandle komplekse data Mini Excel-opplæring: Bruk boolsk logikk til å behandle komplekse datalogiske operatører, hvis ikke, og, og eller, kan hjelpe deg med å komme fra Excel nybegynner til strømbruker. Vi forklarer grunnleggende om hver funksjon og demonstrerer hvordan du kan bruke dem for maksimale resultater. Les mer og avanserte teller og legg til Mini Excel-veiledning: Bruke avansert telle og legge til funksjoner i Excel Mini Excel-veiledning: Bruke avansert telle og legge til funksjoner i Excel Telle og legge til formler kan virke verdslig i forhold til mer avanserte Excel-formler. Men de kan hjelpe deg med å spare mye tid når du trenger å samle inn informasjon om dataene i regnearket ditt. Les mer, jeg bruker et regneark som jeg genererte med generatedata.com. Den inneholder ID-nummer, for- og etternavn, by, stat og SAT-score. La oss si at jeg vil finne SAT-poengsummen til en person med etternavnet “Winters.” VLOOKUP gjør det enkelt. Her er formelen vi vil bruke:

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

Fordi SAT-scoreene er den fjerde kolonnen over fra etternavnet kolonnen, har jeg brukt 4 for kolonneindeks argumentet. Legg merke til at når du leter etter tekst, er innstillingen [range_lookup] til 0 en god ide; uten det kan du få dårlige resultater. Her er hva Microsoft Excel gir oss:

Den returnerte 651, SAT-poengsummen til studenten som heter Kennedy Winters, som er i rad 92 (vist i innsatsen ovenfor). Det ville ha tatt mye lengre tid å bla gjennom å lete etter navnet enn det gjorde for å raskt skrive ut syntaksen!

VLOOKUP kan også være ganske nyttig hvis du bruker Microsoft Excel til å gjøre dine skatter Gjør dine skatter? 5 Excel-formler du må vite å gjøre dine skatter? 5 Excel-formler du må vite Det er to dager før skattene dine er forfalte, og du vil ikke betale et annet forsendelsesgebyr. Dette er på tide å utnytte kraften i Excel for å få alt i orden. Les mer .

Merknader om VLOOKUP

Noen ting er bra å huske når du bruker VLOOKUP. For det første, sørg for at den første kolonnen i ditt utvalg er den som inneholder oppslagsverdien, som jeg nevnte tidligere. Hvis det ikke er i den første kolonnen, returnerer funksjonen feil resultat. Hvis kolonnene dine er godt organisert, bør dette ikke være et problem.

Den andre tingen å huske på er at VLOOKUP bare vil returnere en verdi. Hvis vi hadde brukt “Georgia” som oppslagsverdi, ville det ha returnert poengsummen til den første studenten fra Georgia, og ga ingen indikasjon på at det faktisk er to studenter fra Georgia.

HLOOKUP-funksjonen

Hvor 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, er HLOOKUP best brukt når du har egentlig store regneark eller du arbeider med verdier som er organisert etter tid. Her er syntaksen:

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

[lookup_value], igjen, er verdien du vet og vil finne en tilsvarende verdi for. [table_array] er cellene du vil søke i. [row_index_num] angir rækken som returverdien kommer fra. Og [range_lookup] er det samme som ovenfor; la det være tomt for å få den nærmeste verdien når det er mulig, eller skriv inn 0 for bare å se etter eksakte kamper.

For dette eksempelet har jeg opprettet et nytt regneark med generatedata.com. Denne inneholder en rad for hver stat, sammen med en SAT-poengsum (vi sier at det er gjennomsnittspoeng for staten) i årene 2000-2014. Vi bruker HLOOKUP for å finne gjennomsnittlig poengsum i Minnesota i 2013. Slik gjør vi det:

= HLOOKUP (2013, A1: P51, 24)

(Merk at 2013 ikke er i sitater fordi det er et tall, og ikke en streng, også de 24 kommer fra Minnesota som er i den 24. rad.) Som du kan se på bildet nedenfor, blir poengsummen returnert:

Minnesotans var i gjennomsnitt en poengsum på 1014 i 2013.

Notater om HLOOKUP

Som med VLOOKUP må oppslagsverdien være i den første raden i tabelloppsettet ditt; Dette er sjelden et problem med HLOOKUP, da du vanligvis bruker en kolonne tittel for en oppslagsverdi. HLOOKUP returnerer også bare en enkelt verdi.

INDEX- og MATCH-funksjonene

INDEX og MATCH er to forskjellige funksjoner, men når de brukes sammen, kan de gjøre det enklere å søke i et stort regneark. Begge funksjonene har ulemper, men ved å kombinere dem bygger vi på begge sider. Først, men syntaksen:

= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])

I INDEX er [array] det arrayet du søker etter. [radnummer] og [kolonnummer] kan brukes til å begrense søket ditt; Vi tar en titt på det på et øyeblikk.

MATCHs [lookup_value] er et søkeord som kan være en streng eller et tall; [lookup_array] er det arrayet som Microsoft Excel vil se etter søkeordet. [match_type] er et valgfritt argument som kan være 1, 0 eller -1; 1 returnerer den største verdien som er mindre enn eller lik søkeordet ditt 0 vil bare returnere ditt eksakte uttrykk; og -1 returnerer den minste verdien som er større enn eller lik søkeordet ditt.

Det kan ikke være klart hvordan vi skal bruke disse to funksjonene sammen, så legger jeg det ut her. MATCH tar et søkeord og returnerer en cellereferanse. I 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: det tar en cellereferanse og returnerer verdien i den. Du kan se her at, når du blir fortalt å 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 den referansen til å slå opp verdien i en celle. La oss si at du husker at det var en student hvis etternavn var Waters, og du vil se hva denne studentens poengsum var. Her er formelen vi skal bruke:

= 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, så er det du vil bruke. Her er hva vi får når vi kjører den funksjonen:

Som du ser fra innsatsen, scoret Owen Waters 1720, nummeret som vises når vi kjører funksjonen. Dette kan ikke virke som nyttig når du bare kan se noen kolonner over, men forestill deg hvor mye tid du vil spare hvis du måtte gjøre det 50 ganger på en stor database regneark Excel Vs. Tilgang - Kan et regneark erstatte en database? Excel Vs. Tilgang - Kan et regneark erstatte en database? Hvilket verktøy bør du bruke til å administrere data? Tilgang og Excel har begge data filtrering, sortering og spørring. Vi viser deg hvilken som passer best for dine behov. Les mer som inneholdt flere hundre kolonner!

La søket begynne

Microsoft Excel har mange ekstremt kraftige funksjoner 3 Crazy Excel-formler som gjør fantastiske ting 3 Crazy Excel-formler som gjør fantastiske ting Betingede formateringsformler i Microsoft Excel kan gjøre fantastiske ting. Her er noen ryddige Excel-produktivitetstrykk. Les mer, og de fire nevnte ovenfor bare riper overflaten. Selv med denne oversiktlige oversikten, bør du imidlertid spare mye tid når du jobber med store regneark. Hvis du vil se hvor mye mer du kan gjøre med INDEX, sjekk ut “Den imponerende indeksen” på Microsoft Excel Hero.

Hvordan kjører du søk i store regneark? Hvordan har du funnet disse oppslagsfunksjonene til å være nyttig? Del dine tanker og erfaringer nedenfor!

Image Credits: forstørrelsesglass på et regneark Via Shutterstock

Utforsk mer om: Microsoft Excel, regneark.