I denne artikel lærer vi, hvordan man søger værdi mellem to tal i Excel.
Scenarie:
Det er let at slå en værdi op med et kriterium i en tabel. Vi kan simpelthen bruge VLOOKUP. Men hvad kan vi gøre, hvis vi har flere kolonnekriterier, der matcher i dine data, og skal søge i flere kolonner for at matche en værdi. Lad os lære, hvordan dette problem kan løses ved hjælp af forskellige opslagsversioner af excel
LOOKUP -værdi mellem to tal
Brug af VLOOKUP -formel
I VLOOKUP omtrentlig match, hvis en værdi ikke findes, matches den sidste værdi, der er mindre end opslagsværdien, og værdien fra det givne kolonneindeks returneres.
Generisk formel til LOOKUP -værdi mellem to tal:
= VLOOKUP (værdi, tabel, lookup_col, 1) |
Og en ting mere ved Vlookup er, at den leder efter værdien i kolonnen, og hvis den ikke finder værdien i kolonnearrayet, matcher og returnerer den værdien, der er mindre end værdien i tabell arrayet.
Bemærk: VLOOKUP som standard matcher omtrentligt, hvis vi udelader variablen for opslag i rækkevidde. En omtrentlig match er nyttig, når du vil lave en omtrentlig match, og når du har sorteret din tabel array i stigende rækkefølge.
Brug af INDEX og MATCH formel
INDEX- og MATCH -formlen gør det samme arbejde som ovenfor, men med forskellig syntaks. Men hvis du kun skulle vælge den med hvilke funktioner du er fortrolig med
Generisk formel til LOOKUP -værdi mellem to tal
= INDEX (opslag_område, MATCH (1, INDEX ((kriterier1 = område1)*(kriterier2 = område2),0,1),0)) |
lookup_range: 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 Student -ID og deres respektive karakterer i en test. Nu skulle vi få karaktererne for hver elev via at slå op i karaktersystemtabellen.
For at gøre dette vil vi bruge en attribut for VLOOKUP -funktionen, hvilket er, hvis VLOOKUP -funktionen ikke finder det nøjagtige match, den kun ser efter den omtrentlige matchning i tabellen, og kun hvis funktionens sidste argument enten er SAND eller 1.
Tabellerne for point / score skal være stigende rækkefølge
Brug formlen:
= OPLYSNING (B2, tabel, 2, 1) |
Hvordan virker det?
Vlookup -funktionen finder værdien 40 i mærkesøjlen op og returnerer den tilsvarende værdi, hvis den matches. Hvis den ikke matcher, leder funktionen efter den mindre værdi end opslagsværdien (dvs. 40) og returnerer sit resultat.
Her benævnes table_array området som bord i formel og B2 er givet som cellereference.
Som du kan se, fik vi karakteren for den første elev. For at få alle andre karakterer vil vi bruge genvej Ctrl + D eller brug træk -ned -cellemulighed i excel.
Her er alle klasserne i klassen, der bruger VLOOKUP -funktionen.
LOOKUP -værdi mellem to tal på medarbejderbordet
Vi har en tabel, der indeholder detaljerne for alle medarbejderne i en organisation på et separat ark. Den første kolonne indeholder disse medarbejderes id. Jeg har navngivet denne tabel som emp_data.
Nu skal mit søgeblad hente medarbejderens oplysninger, hvis id er skrevet i celle B3. Jeg har navngivet B3 som ID.
For at lette forståelsen er alle kolonneoverskrifterne i nøjagtig samme rækkefølge som emp_data -tabellen.
Skriv nu nedenstående formel i celle C3 for at hente zonen for medarbejder -ID'et skrevet i B3.
= VLOOKUP (ID, Emp_Data, 2,0) |
Dette returnerer zonen for medarbejder-id 1-1830456593, fordi kolonne nummer 2 i databasen indeholder medarbejderzonen.
Kopier denne formel i resten af cellerne, og skift kolonnetallet i formlen for at få alle oplysninger om medarbejdere.
Du kan se alle de oplysninger, der er relateret til det nævnte ID i Cell B3. Uanset hvilket id du skriver i celle B3, hentes alle oplysninger uden at foretage ændringer i formlen.
Hvordan virker det?
Der er ikke noget tricky. Vi bruger simpelthen VLOOKUP -funktionen til at slå ID op og derefter hente den nævnte kolonne. Øv VLOOKUP ved at bruge sådanne data for at forstå VLOOKUP mere.
Hent medarbejderdata ved hjælp af overskrifter
I ovenstående eksempel havde vi alle kolonnerne organiseret i samme rækkefølge, men der vil være tidspunkter, hvor du har en database, der vil have hundredvis af kolonner. I sådanne tilfælde vil denne metode til at hente medarbejderinformation ikke være god. Det vil være bedre, hvis formlen kan se på kolonneoverskriften og hente data fra den kolonne fra medarbejderstabellen.
Så for at hente værdi fra tabellen ved hjælp af kolonneoverskrift vil vi bruge en 2-vejs opslagningsmetode eller sige dynamisk kolonne VLOOKUP.
Brug denne formel i celle C3 og kopier i resten celler. Du behøver ikke ændre noget i formlen, alt hentes fra thd emp_data.
= VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Denne formel henter simpelthen alle oplysninger fra matchede kolonner. Du kan blande overskrifterne i rapporten, dette vil ikke gøre nogen forskel. Uanset hvilken overskrift der skrives i cellen ovenfor, indeholder de respektive data.
Hvordan virker det?
Dette er simpelthen en dynamisk VLOOKUP. Du kan læse om det her. Hvis jeg forklarer det her, bliver det en for stor artikel.
Hent medarbejder -id med delvis match
Det kan ske, at du ikke husker hele en medarbejders id, men du vil stadig hente oplysningerne om et eller andet id. I sådanne tilfælde er delvis match VLOOKUP den bedste løsning.
For eksempel, hvis jeg ved, at et eller andet id indeholder 2345, men jeg ikke kender hele id'et. Hvis jeg indtaster dette nummer i celle C3, vil output være som.
Vi får ingenting. Da intet matcher 2345 i tabellen. Rediger ovenstående formel på denne måde.
=VLOOKUP("*"&ID&"*", Emp_Data,MATCH(C2, Emp_Data_Headers, 0), 0) |
Kopier dette i hele rækken. Og nu har du oplysningerne fra den første medarbejder, der indeholder dette nummer.
Bemærk, at vi får det første id, der indeholder det matchede nummer i kolonnen Emp Id. Hvis et andet id indeholder det samme nummer, henter denne formel ikke den pågældende medarbejders oplysninger.
Hvis du vil have alle medarbejder -id'erne, der indeholder det samme nummer, skal du bruge formlen, der finder alle de matchede værdier.
Brug af INDEX og MATCH formel
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)) |
Hvordan det virker:
Vi ved allerede, hvordan INDEX og MATCH -funktion fungerer i EXCEL, så det vil jeg ikke forklare 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
{FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT; SANDT
{FALSK; FALSK; FALSK; SAND; SAND; SAND; SAND; SAND: FALSK; FALSK; FALSK; FALSK; FALSK; SAND}} {FALSK; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK |
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).
Her er alle observationsnotaterne ved hjælp af formlen i Excel
Bemærkninger:
- Tabellerne for mærker / scoringer skal være i stigende rækkefølge, ellers kan funktionen give det forkerte resultat.
- Funktionen VLOOKUP søger efter værdien i den første række i Table_array og udtrækker de tilsvarende værdier kun til højre for opslag_området.
- Funktionen VLOOKUP's sidste argument for funktionen skal indstilles til enten SAND eller 1 for at få det omtrentlige match.
- VLOOKUP -funktionen returnerer en fejl, hvis projektmappens adresse er ugyldig eller forkert.
- VLOOKUP -funktionen returnerer en fejl, hvis værdien ikke matches.
Håber denne artikel om Sådan LOOKUP -værdi mellem to tal 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.
Måde at bruge Vlookup -funktion i datavalidering : Begræns brugere til at tillade værdier fra opslagstabellen ved hjælp af datavalideringsformelboksen i Excel. Formelboks i datavalidering giver dig mulighed for at vælge den type begrænsning, der kræves.
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 søge værdier 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.