I denne artikkelen lærer vi hvordan du henter en pris fra en liste som samsvarer med både kategori- og varekriterier i Excel.
Scenario:
Det er lett å finne en verdi med ett kriterium i en tabell, for det kan vi ganske enkelt bruke VLOOKUP. Men når du ikke har et enkelt kolonnekriterium i dataene dine og trenger å finne gjennom flere kolonnekriterier for å matche en verdi, hjelper VLOOKUP ikke.
Generisk formel for både kategori og vare
| = INDEX (oppslag_område, MATCH (1, INDEX ((item1 = item_range) * (category2 = category_range), 0,1), 0)) |
lookup_range: Det er 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 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)) |
Vi vet allerede hvordan INDEX og MATCH fungerer i EXCEL, så vi 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
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 vil returnere indeksnummeret til de første 1 som finnes 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).
Hvis du kan trykke CTRL + SKIFT + ENTER følgelig, kan du eliminere den indre INDEX -funksjonen. Bare skriv denne formelen og trykk CTRL + SKIFT + ENTER.
Formel
| = INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Generisk matriseformel for flere kriterier
| = INDEX (oppslagsområde, MATCH (1, (kriterium1 = område1)*(kriterier2 = område2)*(kriterierN = områdeN), 0)) |
Her er alle observasjonsnotatene ved hjelp av formelen i Excel
Merknader:
- Bruk Vlookup hvis du har ett kriterium som må samsvare, det er vanlig praksis.
- Du kan legge til flere rader og kolonner i oppslagsoppsettet.
- Tekstargumenter må angis i anførselstegn ("").
- VLOOKUP -tabellen må ha oppslag_arrayen til venstre eller den første kolonnen.
- Kol -indeksen kan ikke være 1, ettersom den er oppslagsoppsettet
- 0 -argumentet brukes for den eksakte matchverdien. Bruk 1 for den omtrentlige matchverdien.
- Funksjonen returnerer #N/A -feil, hvis oppslagsverdi ikke finnes i oppslagsoppsettet. Så ta feil om nødvendig.
Håper denne artikkelen om hvordan du henter en pris fra en liste som samsvarer med både kategori- og varekriterier i 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.
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 i excel som brukes til å slå opp verdi 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.