Den här artikeln kommer att prata om hur du får alla värden att matcha från en tabell och hämta dem i olika celler. Detta liknar uppslagningsvärden.
Generisk formel
{= INDEX (namn, SMÅ (OM (grupper = gruppnamn, RAD (namn) -MIN (RAD (namn))+1), KOLUMNER (expanderande intervall))), “--List Ends” ”)}
För många funktioner och variabler !!!. Låt oss se vad dessa variabler är.
Namn: Detta är listan med namn.
Grupper: Listan över gruppen som dessa namn tillhör också.
Grupp namn: gruppens namn.
Utökande intervall: detta är ett expanderande intervall som används för att få ökande antal när de kopieras till höger.
Exempel: Extrahera anställdas namn i olika kolumner beroende på deras företag.
Låt oss säga att du har en tabell över anställda som är grupperade efter deras företag. Den första kolumnen innehåller anställdas namn och den andra kolumnen innehåller företagets namn.
Nu måste vi få varje anställds namn i olika kolumner beroende på deras företag. Med andra ord måste vi avgruppera dem.
Här har jag namngett A2: A10 som anställd och B2: B10 som företag, så att formeln är lätt att läsa.
Skriv denna matrisformel i F2. Använd CTRL+SKIFT+ENTER för att ange denna formel.
{= INDEX (Anställd, Liten (OM (Företag = $ E2, RAD (Medarbetare) -MIN (RAD (Anställd))+1), KOLUMNER ($ E $ 1: E1))), "--Lista slutar--" )}}
Kopiera denna formel i alla celler. Det kommer att extrahera varje enskilt namn i de olika kolumnerna enligt deras grupp.
Som du kan se på bilden ovan är varje anställd segregerad i olika celler.
Så, hur fungerar denna formel?
För att förstå formeln, låt oss titta på formeln i G2
Vilket är = IFERROR (INDEX (Anställd, Liten (OM (Företag = $ E3,RAD(Anställd) -MIN (RAD (Anställd)))+1), COLUMNS ($ E $ 1: F2)))), "-List Ends--")
Mekaniken är enkel och nästan densamma som flera VLOOKUP -formler. Tricket är att få indexnumret för varje anställd från olika grupper och skicka det till INDEX -formeln. Detta görs med denna del av formeln.
OM(Företag=$ E3,RAD(Anställd) -MIN (RAD (Anställd))+1):
Denna del returnerar en rad index och falska för företagsnamn i $ E3, som innehåller "Rankwatch".
{FALSKT; 2; FALSKT; 4; FALSKT; FALSKT; 7; FALSKT; 9}.
Hur? Låt oss riva det inifrån.
Här matchar vi företagsnamnet i $ E3 med varje värde i Företagssortiment (Företag = $ E3).
Detta returnerar en uppsättning sant och falskt. {FALSE;SANN;FALSK;SANN; FALSKT; FALSKT;SANN;FALSK;SANN}.
Nu kör IF-funktionen dess TRUE-satser för TRUE, vilket är ROW (Employee) -MIN (ROW (Anställd))+1. Denna del returnerar denna del returnerar en rad index som börjar från 1 till antal anställda {1; 2; 3; 4; 5; 6; 7; 8; 9}. If -funktionen tar upp värden för endast SANT vilket i sin tur ger oss {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Den nuvarande formeln är förenklad till
= FEL (INDEX (anställd, Liten ({FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9},COLUMNS ($ E $ 1: F2))), "-List Ends--"). Som vi vet returnerar liten funktion nth minsta värde från en array. COLUMNS ($ E $ 1: F2) detta returnerar 2. SMALL -funktionen returnerar det näst minsta värdet från arrayen ovan, vilket är 4.
Nu är formeln förenklad = IFERROR (INDEX (Anställd, 4), "-List Ends--"). Nu returnerar INDEX -funktionen helt enkelt det fjärde namnet från anställd array som ger oss "Sam”.
Så ja, det här hur man extraherar namn från grupper i olika kolumner med hjälp av INDEX, SMALL, ROW, COLUMNS och IF -funktionen. Om du har några tvivel om denna funktion eller om den inte fungerar för dig, låt mig veta kommentarsfältet nedan.
Nedladdning fil:
Relaterade artiklar:
VLOOKUP Flera värden
Använd INDEX och MATCH för att leta upp värde
Sökvärde med flera kriterier
Populära artiklar:
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Hur man använder SUMIF -funktionen i Excel