I denne artikkelen vil vi lære hvordan du søker etter verdi mellom to tall i Excel.
Scenario:
Det er lett å slå opp en verdi med ett kriterium i en tabell. Vi kan ganske enkelt bruke VLOOKUP. Men hva kan vi gjøre hvis vi har de samme kolonnekriteriene for å matche dataene dine og trenger å slå opp i flere kolonner for å matche en verdi. La oss lære hvordan dette problemet kan løses ved hjelp av forskjellige oppslagsversjoner av excel
LOOKUP -verdi mellom to tall
Bruker VLOOKUP -formel
I omtrentlig samsvar i VLOOKUP, hvis en verdi ikke blir funnet, blir den siste verdien som er mindre enn oppslagsverdien, matchet, og verdien fra den gitte kolonneindeksen returneres.
Generisk formel for LOOKUP -verdi mellom to tall:
| = VLOOKUP (verdi, tabell, lookup_col, 1) |
Og en ting til med Vlookup er at den ser etter verdien i kolonnen, og hvis den ikke finner verdien i kolonnematrisen, samsvarer den og returnerer verdien som er mindre enn den verdien i tabellmatrisen.
Merk: VLOOKUP som standard samsvarer omtrentlig hvis vi utelater variabelen for oppslag i området. En omtrentlig match er nyttig når du vil gjøre en omtrentlig kamp og når du har tabelloppsettet sortert i stigende rekkefølge.
Bruker INDEX og MATCH formel
INDEX- og MATCH -formelen gjør det samme arbeidet som ovenfor, men med forskjellig syntaks. Men hvis du bare skulle velge den med hvilke funksjoner du er komfortabel med
Generisk formel til LOOKUP -verdi mellom to tall
| = INDEKS (oppslag_område, MATCH (1, INDEKS ((kriterier1 = område1)*(kriterier2 = område2),0,1),0)) |
lookup_range: området du vil hente verdi fra.
Kriterier1, Kriterier2, Kriterier N: Dette er kriteriene du vil matche i område1, område2 og område N. Du kan ha opptil 270 kriterier - rekkeviddepar.
Område1, område2, områdeN: Dette er områdene der du vil matche dine respektive kriterier.
Eksempel:
Alt dette kan være forvirrende å forstå. La oss forstå hvordan du bruker funksjonen ved hjelp av et eksempel. Her har vi student -ID og deres respektive karakterer i en test. Nå måtte vi få karakterene for hver elev ved å slå opp i karaktersystemtabellen.
For å gjøre det, bruker vi et attributt for VLOOKUP -funksjonen, som er hvis VLOOKUP -funksjonen ikke finner den eksakte matchen den ser ut for den omtrentlige kampen i tabellen, og bare hvis det siste argumentet til funksjonen enten er EKTE eller 1.
Merke- / poengtabellen må være stigende rekkefølge
Bruk formelen:
| = VISNING (B2, tabell, 2, 1) |
Hvordan virker det?
Vlookup -funksjonen slår opp verdien 40 i merkekolonnen og returnerer den tilsvarende verdien hvis den samsvarer. Hvis den ikke stemmer overens, ser funksjonen etter den minste verdien enn oppslagsverdien (dvs. 40) og returnerer resultatet.
Her heter tabellen_array området som bord i formel og B2 er gitt som cellereferanse.
Som du kan se fikk vi karakteren for den første studenten. For å få alle andre karakterer, bruker vi snarvei Ctrl + D eller bruk dra ned cellealternativet i excel.
Her er alle klassene i klassen som bruker VLOOKUP -funksjonen.
LOOKUP -verdi mellom to tall på medarbeiderbordet
Vi har en tabell som inneholder detaljene til alle ansatte i en organisasjon på et eget ark. Den første kolonnen inneholder ID -en til disse ansatte. Jeg har kalt denne tabellen som emp_data.
Nå må søkearket mitt hente den ansattes informasjon hvis ID er skrevet i celle B3. Jeg har kalt B3 som ID.
For enkel forståelse er alle kolonneoverskriftene i nøyaktig samme rekkefølge som emp_data -tabellen.
Skriv nå formelen nedenfor i celle C3 for å hente sonen til ansatt -ID -en skrevet i B3.
| = VISNING (ID, Emp_Data, 2,0) |
Dette vil returnere sonen til medarbeider-ID 1-1830456593 fordi kolonne nummer 2 i databasen inneholder sonen for ansatte.
Kopier denne formelen i resten av cellene og endre kolonnetallet i formelen for å få all informasjon fra ansatte.
Du kan se all informasjon relatert til den nevnte IDen i Cell B3. Uansett hvilken ID du skriver i celle B3, vil all informasjon bli hentet uten å gjøre noen endring i formelen.
Hvordan virker dette?
Det er ikke noe vanskelig. Vi bruker ganske enkelt VLOOKUP -funksjonen til å slå opp ID og deretter hente den nevnte kolonnen. Øv VLOOKUP ved å bruke slike data for å forstå VLOOKUP mer.
Hent ansattedata ved hjelp av overskrifter
I eksemplet ovenfor hadde vi alle kolonnene organisert i samme rekkefølge, men det vil være tider når du har en database som vil ha hundrevis av kolonner. I slike tilfeller vil ikke denne metoden for å hente ansattinformasjon være god. Det vil være bedre hvis formelen kan se på kolonneoverskriften og hente data fra den kolonnen fra arbeidstabellen.
Så for å hente verdi fra tabellen ved hjelp av kolonneoverskrift vil vi bruke en 2-veis oppslagsmetode eller si dynamisk kolonne VLOOKUP.
Bruk denne formelen i celle C3 og kopier inn resten celler. Du trenger ikke å endre noe i formelen, alt vil bli hentet fra thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Denne formelen henter ganske enkelt all informasjon fra matchede kolonner. Du kan blande overskriftene i rapporten, dette vil ikke gjøre noen forskjell. Uansett hvilken overskrift som er skrevet i cellen ovenfor, inneholder de respektive dataene.
Hvordan virker dette?
Dette er ganske enkelt en dynamisk oppslag. Du kan lese om det her. Hvis jeg skal forklare det her, blir det en for stor artikkel.
Hent ansatt -ID med delvis match
Det kan skje at du ikke husker hele ID -en til en ansatt, men du vil fortsatt hente informasjonen til en ID. I slike tilfeller er delvis match VLOOKUP den beste løsningen.
For eksempel, hvis jeg vet at noen ID inneholder 2345, men jeg vet ikke hele ID -en. Hvis jeg skriver inn dette nummeret i celle C3, vil utgangen være som.
Vi får ingenting. Siden ingenting stemmer med 2345 i tabellen. Endre formelen ovenfor slik.
| =VLOOKUP("*"&ID&"*", Emp_Data,KAMP(C2, Emp_Data_Headers, 0), 0) |
Kopier dette i hele raden. Og nå har du informasjonen til den første ansatte som inneholder dette nummeret.
Vær oppmerksom på at vi får den første ID -en som inneholder det samsvarende nummeret i Emp Id -kolonnen. Hvis en annen ID inneholder det samme nummeret, vil denne formelen ikke hente den ansattes informasjon.
Hvis du vil få alle ansatt -ID -ene som inneholder samme nummer, bruker du formelen som ser opp alle de matchede verdiene.
Bruker INDEX og MATCH formel
Her har vi en datatabell. Jeg vil trekke navnet på kunden ved hjelp av bestillingsdato, byggmester og område. Så her har jeg tre kriterier og ett oppslagsområde.
Skriv denne formelen i celle I4, trykk enter.
| = INDEKS (E2: E16, MATCH (1, INDEKS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Hvordan det fungerer:
Vi vet allerede hvordan INDEX og MATCH -funksjonen fungerer i EXCEL, så jeg skal ikke forklare det her. Vi vil snakke om trikset vi brukte her.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hoveddelen er denne. Hver del av denne setningen returnerer en rekke sanne usanne.
Når boolske verdier multipliseres returnerer de en matrise på 0 og 1. Multiplikasjon fungerer som en AND -operator. Hense når alle verdier er sanne bare da returnerer den 1 annen 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Dette vil helt tilbake
| {FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; SANN; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}*
{FALSK; FALSK; FALSK; SANN; SANN; SANN; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; SANN}* {FALSK; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK |
Som vil oversette til
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEKS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEKS -funksjonen returnerer samme matrise ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) til MATCH -funksjonen som oppslagsarray.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funksjonen vil se etter 1 i array {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Og returnerer indeksnummeret for de første 1 som er funnet i matrisen. Som er 8 her.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Endelig kommer INDEX tilbake verdien fra det angitte området (E2: E16) ved funnet indeks (8).
Her er alle observasjonsnotatene ved hjelp av formelen i Excel
Merknader:
- Tabellen kolonne med poeng / poengsum må være i stigende rekkefølge, ellers kan funksjonen gi feil resultat.
- VLOOKUP -funksjonen ser etter verdien i den første raden i Table_array og trekker ut tilsvarende verdier bare til høyre for lookup_range.
- VLOOKUP -funksjonens siste argument for funksjonen må settes til enten TRUE eller 1 for å få omtrentlig samsvar.
- VLOOKUP -funksjonen returnerer en feil hvis arbeidsbokadressen er ugyldig eller feil.
- VLOOKUP -funksjonen returnerer en feil hvis verdien ikke samsvarer.
Håper denne artikkelen om How to LOOKUP value between two numbers in Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del dem med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
Måte å bruke Vlookup -funksjonen i datavalidering : Begrens brukere til å tillate verdier fra oppslagstabellen ved hjelp av formelen for datavalidering i Excel. Formelboks i datavalidering lar deg velge hvilken type begrensning som kreves.
Hvordan hente siste pris i Excel : Det er vanlig å oppdatere priser i enhver virksomhet, og å bruke de nyeste prisene for kjøp eller salg er et must. For å hente den siste prisen fra en liste i Excel bruker vi LOOKUP -funksjonen. LOOKUP -funksjonen henter den siste prisen.
VLOOKUP -funksjon for å beregne karakter i Excel : Å beregne karakterer IF og IFS er ikke de eneste funksjonene du kan bruke. VLOOKUP er mer effektiv og dynamisk for slike betingede beregninger. For å beregne karakterer ved hjelp av VLOOKUP kan vi bruke denne formelen.
17 ting om Excel VLOOKUP : VLOOKUP brukes oftest for å hente samsvarende verdier, men VLOOKUP kan gjøre mye mer enn dette. Her er 17 ting om VLOOKUP som du bør vite for å bruke effektivt.
SLÅ OPP den første teksten fra en liste i Excel : VLOOKUP -funksjonen fungerer fint med jokertegn. Vi kan bruke dette til å trekke ut den første tekstverdien fra en gitt liste i excel. Her er den generiske formelen.
OPPLAGSDATO med siste verdi i listen : For å hente datoen som inneholder den siste verdien bruker vi LOOKUP -funksjonen. Denne funksjonen søker etter cellen som inneholder den siste verdien i en vektor, og bruker deretter referansen til å returnere datoen.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten : Bli raskere med oppgavene i Excel. Disse snarveiene hjelper deg med å øke arbeidseffektiviteten din i Excel.
Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdier fra forskjellige områder og ark.
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.