I den här artikeln kommer vi att lära oss hur man letar upp flera instanser av ett värde i Excel.
Leta upp värden med rullgardinsmenyn?
Här förstår vi hur vi kan leta upp olika resultat med hjälp av INDEX -funktionsmatrisformeln. Välj bara värdet från listan så kommer motsvarande resultat att finnas där.
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 där du vill hämta data.
lookup_value: Ditt uppslagsvärde som du vill filtrera.
lookup_value_range: Området där du vill filtrera lookup_value.
Den första cellen i lookup_value -intervallet: om ditt lookup_value -intervall är $ A $ 5: $ A $ 100 är dess $ A $ 5.
Viktig: Allt borde vara absolut refererad. uppslagningsvärde kan vara relativ enligt krav.
Ange det som en matrisformel. När du har skrivit formeln använder du CTRL+SKIFT+ENTER -tangenten för att göra det till en matrisformel.
Exempel på sökning efter flera resultat
Jag har denna elevdata inom räckvidd A2: E14. I cell G1 har jag en nedrullning av regionvärden t.ex. Central, öst, norr, söder och väst. Nu vill jag, oavsett region jag har i G1, en lista över alla elever från den regionen ska visas i kolumnen H.
För att slå upp flera värden i excel, låt oss identifiera våra variabler.
Array: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
lookup_value_range: $ A $ 2: $ A $ 14
Den första cellen i lookup_value -intervallet: $ A $ 2
Enligt ovanstående data är vår formel för att hämta flera värden i excel:
| {= IFERROR (INDEX ($ C $ 2: $ C $ 14, LITEN (IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) , RAD (1: 1))), "Inget mer värde")} |
Detta är en matrisformel. Använd inte bara Enter när du har skrivit in formeln. Använd CTRL + SKIFT + ENTER tillsammans.
Låt oss nu förstå hur det fungerar.
Även om formeln kan se komplex ut, men tanken är enkel. Vi måste få indexnumret för varje värdeförekomst och sedan hämta värden med INDEX -funktionen i Excel.
Därför är den största utmaningen att få en rad indexnummer på uppslagningsvärde. För att få indexnummer använde vi IF- och ROW -funktioner. Formeln är verkligen komplex, låt oss bryta ner den.
Vi vill få värden från studentkolumnen, så vår array för INDEX -funktion är $ C $ 2: $ C $ 14.
Nu måste vi ange radnummer från $ A $ 2: $ A $ 14 (uppslagsvärde) där G1: s värde finns (låt oss säga för nu G1 har en central i den).
OM ($ G $ 1 = $ A $ 2: $ A $ 14, RAD ($ A $ 2: $ A $ 14): Nu returnerar denna del radnummer om en cellräknings värde G1 (central) inom räckhåll $ A $ 2: $ A $ 14 annars återkommer FALSK. I det här exemplet kommer det tillbaka
{2; FALSKT; FALSKT; FALSKT; FALSKT; 7; 8; FALSKT; FALSKT; 11; FALSKT; FALSKT; FALSKT}.
Nu, eftersom ovanstående array innehåller radnummer från första raden (1: 1) och vi behöver rader som börjar från vår array (A2: A14). För att göra det använder vi -ROW ($ A $ 2) +1 i IF -formeln. Detta kommer att returnera ett antal rader innan vi startar vår array.
För det här exemplet är det -1. Om det började från A3 skulle det returnera -2 och så vidare. Detta nummer kommer att subtraheras från varje nummer i matrisen som returneras av IF. Så äntligen IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) detta kommer att översättas till {1; FALSKT; FALSKT; FALSKT; FALSKT;6;7; FALSKT; FALSKT;10; FALSKT; FALSKT; FALSKT}.
Därefter omges denna array av SMALL -funktionen. Denna funktion returnerar det n: a minsta värdet i den angivna matrisen. Nu har vi SMÅ ({2; FALSKT; FALSKT; FALSKT; FALSKT;7;8; FALSKT; FALSKT;11; FALSKT; FALSKT; FALSKT}, RAD (1: 1)). RAD (1: 1) returnerar 1. Därför kommer ovanstående funktion att returnera 1: a minsta värdet i matrisen, vilket är 2.
När du kopierar denna formel i nedanstående celler blir RAD (1: 1) RÅ (2: 2) och den returnerar det 2: a minsta värdet i matrisen, som är 7 och så vidare. Detta gör att funktionen först kan returnera först hittat värde. Men om du vill få det senast hittade värdet först använder du LARGE -funktionen istället för SMALL -funktionen.
Med hjälp av värden som returneras ovanför funktioner returnerar INDEX -funktionen enkelt varje matchande värde från ett intervall.
Du kan också utföra exakta träffar med hjälp av INDEX och MATCH -funktionen i Excel. Läs mer om hur man gör skiftlägeskänslig sökning med hjälp av INDEX & MATCH -funktionen i Excel. Du kan också leta efter delmatchningarna med jokertecken i Excel.
Hoppas att den här artikeln om hur man letar upp flera instanser av ett värde i Excel är förklarande. Hitta fler artiklar om beräkning av värden och relaterade Excel -formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Använd VLOOKUP från två eller fler uppslagstabeller | För att leta upp från flera tabeller kan vi använda en IFERROR -metod. För att leta upp från flera tabeller tar det felet som en switch för nästa tabell. En annan metod kan vara en If -metod.
Hur man gör skiftlägeskänslig sökning i Excel | excels VLOOKUP -funktion är inte skiftlägeskänslig och den returnerar det första matchade värdet från listan. INDEX-MATCH är inget undantag, men det kan ändras för att göra det skiftlägeskänsligt. Låt oss se hur …
Slå upp ofta text med kriterier i Excel | Sökningen visas oftast i text i ett område som vi använder INDEX-MATCH med MODE-funktionen. Här är metoden.
Populära artiklar:
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.