I denne artikkelen lærer vi hvordan du finner flere forekomster av en verdi i Excel.
Slå opp verdier ved hjelp av nedtrekksalternativet?
Her forstår vi hvordan vi kan slå opp forskjellige resultater ved å bruke INDEX -funksjonsoppsettformelen. Bare velg verdien fra listen, og det tilsvarende resultatet vil være der.
Generisk formel
| {= INDEX (array, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (first cell of lookup_value_range) +1), ROW (1: 1))))} |
Array: Området hvor du vil hente data.
oppslagsverdi: Oppslagsverdi du vil filtrere.
oppslag_verdi_område: Området du vil filtrere oppslagsverdi i.
Den første cellen i oppslag_verdi -området: Hvis området for oppslagsverdi er $ A $ 5: $ A $ 100, er det $ A $ 5.
Viktig: Alt burde være absolutt referert. oppslag_verdi kan være relativ i henhold til kravet.
Skriv den inn som en matriseformel. Etter å ha skrevet formelen bruker du CTRL+SKIFT+ENTER -tasten for å gjøre den til en matriseformel.
Eksempel på oppslag for flere resultater
Jeg har disse elevdataene innen rekkevidde A2: E14. I celle G1 har jeg en rullegardinmeny med regionverdier f.eks. Sentral, øst, nord, sør og vest. Nå vil jeg, uansett hvilken region jeg har i G1, en liste over alle studenter fra den regionen skal vises i H -kolonnen.
For å slå opp flere verdier i excel, la oss identifisere våre variabler.
Array: $ C $ 2: $ C $ 14
oppslagsverdi: $ G $ 1
oppslag_verdi_område: $ A $ 2: $ A $ 14
Den første cellen i lookup_value -området: $ A $ 2
I henhold til dataene ovenfor vil formelen vår for å hente flere verdier i excel være:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, SMALL (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) , RAD (1: 1))), "Ingen mer verdi")} |
Dette er en matriseformel. Ikke bruk bare Enter etter å ha skrevet formelen. Bruk CTRL + SKIFT + ENTER sammen.
La oss nå forstå hvordan det fungerer.
Selv om formelen kan se kompleks ut, men ideen er enkel. Vi må få indeksnummeret for hver forekomst av verdi, og deretter hente verdier ved hjelp av INDEX -funksjonen i Excel.
Derfor er hovedutfordringen å få en rekke indeksnummer på oppslag_verdi. For å få indeksnumre brukte vi IF- og ROW -funksjoner. Formelen er virkelig kompleks totalt, la oss bryte den ned.
Vi ønsker å få verdier fra studentkolonnen, så matrisen vår for INDEX -funksjonen er $ C $ 2: $ C $ 14.
Nå må vi oppgi radnummer fra $ A $ 2: $ A $ 14 (oppslagsverdi) der G1s verdi eksisterer (la oss si for nå G1 har en sentral i den).
HVIS ($ G $ 1 = $ A $ 2: $ A $ 14, RAD ($ A $ 2: $ A $ 14): Nå returnerer denne delen radnummer hvis en cellefontens verdi på G1 (sentral) innen rekkevidde $ A $ 2: $ A $ 14 annet kommer tilbake FALSK. I dette eksemplet kommer det tilbake
{2; FALSK; FALSK; FALSK; FALSK; 7; 8; FALSK; FALSK; 11; FALSK; FALSK; FALSK}.
Siden matrisen ovenfor inneholder radnummer fra 1. rad (1: 1), og vi trenger rader som starter fra matrisen vår (A2: A14). For å gjøre det bruker vi -ROW ($ A $ 2) +1 i IF -formelen. Dette vil returnere et antall rader før du starter matrisen vår.
For dette eksemplet er det -1. Hvis den startet fra A3, ville den returnere -2 og så videre. Dette tallet blir trukket fra hvert tall i rekken returnert av IF. Så endelig IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) dette vil oversette til {1; FALSK; FALSK; FALSK; FALSK;6;7; FALSK; FALSK;10; FALSK; FALSK; FALSK}.
Deretter er denne matrisen omgitt av SMALL -funksjon. Denne funksjonen returnerer den n. Minste verdien i den gitte matrisen. Nå har vi SMÅ ({2; FALSK; FALSK; FALSK; FALSK;7;8; FALSK; FALSK;11; FALSK; FALSK; FALSK}, RAD (1: 1)). RAD (1: 1) returnerer 1. Derfor vil funksjonen ovenfor returnere 1. minste verdi i matrisen, som er 2.
Når du kopierer denne formelen i cellene nedenfor vil RAD (1: 1) bli RAD (2: 2) og den vil returnere den andre minste verdien i matrisen, som er 7 og så videre. Dette gjør at funksjonen kan returnere først funnet verdi først. Men hvis du vil få den sist funnet verdien først, bruk LARGE -funksjonen i stedet for SMALL -funksjonen.
Ved å bruke verdier som returneres over funksjoner, returnerer INDEX -funksjonen enkelt hver matchende verdi fra et område.
Du kan også utføre nøyaktige oppslag ved å bruke INDEX og MATCH -funksjonen i Excel. Lær mer om hvordan du gjør saksfølsom oppslag ved hjelp av INDEX & MATCH -funksjonen i Excel. Du kan også slå opp for de delvise treffene ved hjelp av jokertegnene i Excel.
Håper denne artikkelen om hvordan du finner flere forekomster av en verdi i Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del den 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.
Bruk VLOOKUP fra to eller flere oppslagstabeller | For å slå opp fra flere tabeller kan vi ta en IFERROR -tilnærming. For å slå opp fra flere tabeller, tar det feilen som en bryter for den neste tabellen. En annen metode kan være en If -tilnærming.
Hvordan gjøre saksfølsom oppslag i Excel | excels VLOOKUP -funksjon er ikke mellom store og små bokstaver, og den returnerer den første matchede verdien fra listen. INDEX-MATCH er intet unntak, men det kan endres for å gjøre det saksfølsomt. La oss se hvordan …
Oppslag som ofte vises tekst med kriterier i Excel | Søket vises oftest i tekst i et område vi bruker INDEX-MATCH with MODE-funksjonen. Her er metoden.
Populære artikler:
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 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 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.