Denne artikkelen vil snakke om hvordan du får alle verdier tilpasset fra en tabell og henter dem i forskjellige celler. Dette ligner på oppslag av flere verdier.
Generisk formel
{= INDEKS (navn, LITEN (HVIS (grupper = gruppenavn, RAD (navn) -MIN (RAD (navn))+1), KOLONNER (utvidende områder))), "--List Ends--")}
For mange funksjoner og variabler !!!. La oss se hva disse variablene er.
Navn: Dette er listen over navn.
Grupper: Listen over gruppen som disse navnene også tilhører.
Gruppenavn: referansen til gruppenavn.
Utvidelsesområder: dette er et ekspanderende område som brukes til å få økende antall når de kopieres til høyre.
Eksempel: Trekk ut ansattes navn i forskjellige kolonner i henhold til deres firma.
La oss si at du har en tabell over ansatte som er gruppert i henhold til deres selskap. Den første kolonnen inneholder ansattnavn og den andre kolonnen inneholder navnet på selskapet.
Nå må vi få hver ansattes navn i forskjellige kolonner i henhold til deres firma. Med andre ord, vi må fjerne dem.
Her har jeg navngitt A2: A10 som ansatt og B2: B10 som selskap, slik at formelen er lett å lese.
Skriv denne matriseformelen i F2. Bruk CTRL+SKIFT+ENTER for å angi denne formelen.
{= INDEX (Employee, SMALL (IF (Company = $ E2, ROW (Employee) -MIN (ROW (Employee))+1), COLUMNS ($ E $ 1: E1))), "--List Ends--" )}
Kopier denne formelen i alle cellene. Det trekker ut hvert enkelt navn i de forskjellige kolonnene i henhold til gruppen.
Som du kan se på bildet ovenfor, er hver ansatt skilt i forskjellige celler.
Så, hvordan fungerer denne formelen?
For å forstå formelen, la oss se på formelen i G2
Som er = IFERROR (INDEX (Ansatt, LITEN (HVIS (Firma = $ E3,RAD(Ansatt) -MIN (RAD (Ansatt))+1), COLUMNS ($ E $ 1: F2)))) "-List Ends--")
Mekanikken er enkel og nesten den samme som flere VLOOKUP -formler. Trikset er å få indeksnummeret til hver ansatt fra forskjellige grupper og sende det til INDEX -formelen. Dette gjøres av denne delen av formelen.
HVIS(Selskap=$ E3,RAD(Ansatt) -MIN (RAD (Ansatt))+1):
Denne delen returnerer en rekke indekser og falske for firmanavn i $ E3, som inneholder "Rankwatch".
{FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9}.
Hvordan? La oss rive det fra innsiden.
Her matcher vi firmanavnet i $ E3 med hver verdi i Bedriftssortiment (Firma = $ E3).
Dette returnerer en rekke sanne og usanne. {FALSE;EKTE;FALSK;EKTE; FALSK; FALSK;EKTE;FALSK;EKTE}.
Nå kjører IF-funksjonen sine TRUE-setninger for TRUE, som er ROW (Employee) -MIN (ROW (Ansatt))+1. Denne delen returnerer denne delen returnerer en rekke indekser som starter fra 1 til antall ansatte {1; 2; 3; 4; 5; 6; 7; 8; 9}. If -funksjonen viser verdier for bare TRUE som igjen gir oss {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Den nåværende formelen er forenklet til
= FEIL (INDEKS (Ansatt, SMÅ ({FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9},COLUMNS ($ E $ 1: F2))), "-List Ends--"). Som vi vet returnerer liten funksjon nth minste verdi fra en matrise. KOLONNER ($ E $ 1: F2) dette returnerer 2. SMALL -funksjonen returnerer den nest minste verdien fra matrisen ovenfor, som er 4.
Nå er formelen forenklet = IFERROR (INDEX (ansatt, 4), "-List Ends--"). Nå returnerer INDEX -funksjonen ganske enkelt det fjerde navnet fra ansatt matrise som gir oss "Sam”.
Så ja, dette er hvordan du trekker ut navn fra grupper i forskjellige kolonner ved hjelp av INDEX, SMALL, ROW, COLUMNS og IF -funksjonen. Hvis du er i tvil om denne funksjonen eller hvis den ikke fungerer for deg, gi meg beskjed om kommentarfeltet nedenfor.
Last ned fil:
Relaterte artikler:
VLOOKUP Flere verdier
Bruk INDEX og MATCH til å slå opp verdi
Oppslagsverdi med flere kriterier
Populære artikler:
VLOOKUP -funksjonen i Excel
COUNTIF i Excel 2016
Slik bruker du SUMIF -funksjonen i Excel