I denne artikel lærer vi, hvordan du henter en pris fra en liste, der matcher både kategori- og varekriterier i Excel.
Scenarie:
Det er let at finde en værdi med et kriterium i en tabel, for det kunne vi simpelthen bruge VLOOKUP. Men når du ikke har et enkelt kolonnekriterium i dine data og skal gennemskue flere kolonnekriterier for at matche en værdi, hjælper VLOOKUP ikke.
Generisk formel for både kategori og vare
| = INDEX (opslag_område, MATCH (1, INDEX ((item1 = item_range) * (category2 = category_range), 0,1), 0)) |
lookup_range: Det er det område, hvorfra du vil hente værdi.
Kriterier1, Kriterier2, Kriterier N: Dette er de kriterier, du vil matche i område1, område2 og område N. Du kan have op til 270 kriterier - rækkeviddepar.
Range1, range2, rangeN: Disse er de områder, hvor du vil matche dine respektive kriterier
Eksempel:
Alt dette kan være forvirrende at forstå. Lad os forstå, hvordan du bruger funktionen ved hjælp af et eksempel. Her har vi en datatabel. Jeg vil trække kundens navn ved hjælp af bookingdato, bygherre og område. Så her har jeg tre kriterier og et opslagsområde.
Skriv denne formel i celle I4, tryk på enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Vi ved allerede, hvordan INDEX og MATCH -funktion fungerer i EXCEL, så vi vil ikke forklare det her. Vi vil tale om det trick, vi brugte her.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hoveddelen er denne. Hver del af denne sætning returnerer en række sand falsk.
Når boolske værdier multipliceres, returnerer de en matrix på 0 og 1. Multiplikation fungerer som en AND -operator. Hense når alle værdier kun er sande, returnerer den 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Dette vil helt tilbage
Hvilket vil oversætte til
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEX -funktionen returnerer det samme array ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) til MATCH -funktion som opslagsarray.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktionen leder efter 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, der findes i arrayet. Hvilket er 8 her.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Endelig vender INDEX tilbage værdien fra det givne område (E2: E16) ved fundet indeks (8).
Hvis du derfor kan trykke CTRL + SKIFT + ENTER, kan du fjerne den indre INDEX -funktion. Bare skriv denne formel og tryk på CTRL + SKIFT + ENTER.
Formel
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Generisk matrixformel til opslag med flere kriterier
| = INDEX (opslagsområde, MATCH (1, (kriterium1 = område1)*(kriterium2 = område2)*(kriterierN = områdeN), 0)) |
Her er alle observationsnotaterne ved hjælp af formlen i Excel
Bemærkninger:
- Brug Vlookup, hvis du har et kriterium, der matcher, det er almindelig praksis.
- Du kan tilføje flere rækker og kolonner i opslagsarrayet.
- Tekstargumenter skal angives inden for anførselstegn ("").
- VLOOKUP -tabellen skal have opslag_arrayet i venstre eller den første kolonne.
- Col -indekset kan ikke være 1, da det er opslagsarrayet
- 0 -argument bruges til den nøjagtige matchværdi. Brug 1 til den omtrentlige matchværdi.
- Funktionen returnerer #N/A -fejl, hvis opslagsværdien ikke findes i opslagsmatrixen. Så tag fejlen, hvis det er nødvendigt.
Håber denne artikel om, hvordan du henter en pris fra en liste, der matcher både kategori og varekriterier i Excel, er forklarende. Find flere artikler om beregning af værdier og relaterede Excel -formler her. Hvis du kunne lide vores blogs, kan du dele dem med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
Sådan hentes den seneste pris i Excel : Det er almindeligt at opdatere priser i enhver virksomhed, og det er et must at bruge de seneste priser for ethvert køb eller salg. For at hente den seneste pris fra en liste i Excel bruger vi LOOKUP -funktionen. LOOKUP -funktionen henter den seneste pris.
VLOOKUP -funktion til beregning af karakter i Excel : At beregne karakterer IF og IFS er ikke de eneste funktioner, du kan bruge. VLOOKUP er mere effektiv og dynamisk til sådanne betingede beregninger. For at beregne karakterer ved hjælp af VLOOKUP kan vi bruge denne formel.
17 Ting om Excel VLOOKUP : VLOOKUP bruges mest til at hente matchede værdier, men VLOOKUP kan meget mere end dette. Her er 17 ting om VLOOKUP, som du bør vide for at bruge effektivt.
LOOKUP den første tekst fra en liste i Excel : VLOOKUP -funktionen fungerer fint med jokertegn. Vi kan bruge dette til at udtrække den første tekstværdi fra en given liste i excel. Her er den generiske formel.
LOOKUP -dato med sidste værdi på listen : For at hente den dato, der indeholder den sidste værdi, bruger vi LOOKUP -funktionen. Denne funktion søger efter cellen, der indeholder den sidste værdi i en vektor og bruger derefter denne reference til at returnere datoen.
Populære artikler:
50 Excel -genveje til at øge din produktivitet : Bliv hurtigere til dine opgaver i Excel. Disse genveje hjælper dig med at øge din arbejdseffektivitet i Excel.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.