I denne artikel lærer vi, hvordan du finder flere forekomster af en værdi i Excel.
Opslagværdier ved hjælp af rullemenuen?
Her forstår vi, hvordan vi kan slå forskellige resultater op ved hjælp af INDEX -funktionsmatrixformlen. Vælg blot værdien fra listen, og det tilsvarende resultat 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, hvorfra du vil hente data.
opslagsværdi: Din opslagsværdi, som du vil filtrere.
opslag_værdi_range: Det område, du vil filtrere lookup_value i.
Den første celle i lookup_value -området: hvis dit lookup_value -område er $ A $ 5: $ A $ 100, så er det $ A $ 5.
Vigtig: Alt skal være absolut refereret. opslagsværdi kan være relativ i henhold til krav.
Indtast det som en matrixformel. Efter at have skrevet formlen skal du bruge CTRL+SKIFT+ENTER -tasten for at gøre det til en matrixformel.
Eksempel på opslag efter flere resultater
Jeg har disse elevdata inden for rækkevidde A2: E14. I celle G1 har jeg en rulleliste med regionsværdier f.eks. Central, øst, nord, syd og vest. Nu vil jeg have, uanset hvilken region jeg har i G1, en liste over alle elever fra denne region skal vises i kolonnen H.
For at slå flere værdier op i excel, lad os identificere vores variabler.
Array: $ C $ 2: $ C $ 14
opslagsværdi: $ G $ 1
opslag_værdi_range: $ A $ 2: $ A $ 14
Den første celle i lookup_value -området: $ A $ 2
Ifølge ovenstående data vil vores formel til at hente flere værdier 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) , RÆK (1: 1))), "Ingen værdi mere")} |
Dette er en matrixformel. Brug ikke bare Enter efter at have skrevet formlen. Brug CTRL + SKIFT + ENTER sammen.
Lad os nu forstå, HVORDAN DET VIRKER.
Selvom formlen kan se kompleks ud, men ideen er enkel. Vi skal hente indeksnummeret for hver forekomst af værdi og derefter hente værdier ved hjælp af INDEX -funktionen i Excel.
Derfor er hovedudfordringen at få en række indeksnumre af opslagsværdi. For at få indeksnumre brugte vi IF- og ROW -funktioner. Formlen er faktisk helt kompleks, lad os bryde den ned.
Vi ønsker at få værdier fra elevkolonnen, så vores array til INDEX -funktion er $ C $ 2: $ C $ 14.
Nu skal vi give rækkenumre fra $ A $ 2: $ A $ 14 (opslagsværdi), hvor G1s værdi eksisterer (lad os nu sige G1 har en central i den).
HVIS ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Nu returnerer denne del rækkenummer, hvis en celletællings værdi af G1 (central) inden for rækkevidde $ A $ 2: $ A $ 14 ellers vender tilbage FALSK. I dette eksempel vender det tilbage
{2; FALSK; FALSK; FALSK; FALSK; 7; 8; FALSK; FALSK; 11; FALSK; FALSK; FALSK}.
Nu, da ovenstående array indeholder rækkenumre fra 1. række (1: 1), og vi har brug for rækker, der starter fra vores array (A2: A14). For at gøre det bruger vi -ROW ($ A $ 2) +1 i IF -formlen. Dette vil returnere et antal rækker, før vi starter vores array.
For dette eksempel er det -1. Hvis det startede fra A3, ville det returnere -2 og så videre. Dette tal trækkes fra hvert tal i arrayet returneret af IF. Så endelig IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) dette vil oversætte til {1; FALSK; FALSK; FALSK; FALSK;6;7; FALSK; FALSK;10; FALSK; FALSK; FALSK}.
Dernæst er dette array omgivet af SMALL -funktion. Denne funktion returnerer den nte mindste værdi i det givne array. Nu har vi SMÅ ({2; FALSK; FALSK; FALSK; FALSK;7;8; FALSK; FALSK;11; FALSK; FALSK; FALSK}, RÆKKE (1: 1)). RÆKKE (1: 1) returnerer 1. Derfor vil ovenstående funktion returnere 1. mindste værdi i arrayet, som er 2.
Når du kopierer denne formel i nedenstående celler, bliver RÆK (1: 1) til RÆKKE (2: 2), og den returnerer den 2. mindste værdi i matrixen, som er 7 og så videre. Dette gør det muligt for funktionen at returnere først fundet værdi først. Men hvis du først vil have den sidst fundne værdi, skal du bruge LARGE -funktionen i stedet for SMALL -funktionen.
Nu ved hjælp af værdier, der returneres over funktioner, returnerer INDEX -funktionen let hver matchende værdi fra et område.
Du kan også udføre nøjagtige opslag ved hjælp af INDEX og MATCH -funktionen i Excel. Lær mere om, hvordan du foretager sagfølsom opslag ved hjælp af INDEX & MATCH -funktionen i Excel. Du kan også slå op efter de delvise kampe ved hjælp af jokertegnene i Excel.
Håber, at denne artikel om Sådan opslås flere forekomster af en værdi 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 den 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.
Brug VLOOKUP fra to eller flere opslagstabeller | For at slå op fra flere tabeller kan vi tage en IFERROR -tilgang. For at slå op fra flere tabeller tager det fejlen som en switch til den næste tabel. En anden metode kan være en If -tilgang.
Sådan laver du store og små bogstaver i Excel | excels VLOOKUP -funktion er ikke store og små bogstaver, og den returnerer den første matchede værdi fra listen. INDEX-MATCH er ingen undtagelse, men det kan ændres for at gøre det store og små bogstaver. Lad os se, hvordan…
Opslag, der ofte vises tekst med kriterier i Excel | Opslaget vises oftest i tekst i et område, vi bruger INDEX-MATCH med MODE-funktionen. Her er metoden.
Populære artikler:
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 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 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.