Denne artikel vil tale om, hvordan du får alle værdier matchet fra en tabel og henter dem i forskellige celler. Dette ligner opslag i flere værdier.
Generisk formel
{= INDEX (navne, LILLE (HVIS (grupper = gruppenavn, RÆK (navne) -MIN (RÆKKE (navne))+1), KOLONNER (udvidede områder))), “--List Ends--”)}
For mange funktioner og variabler !!!. Lad os se, hvad disse variabler er.
Navne: Dette er listen over navne.
Grupper: Listen over gruppen, som disse navne også tilhører.
Gruppe navn: referencen til gruppens navn.
Udvidelsesområder: dette er et ekspanderende område, der bruges til at få stigende antal, når det kopieres til højre.
Eksempel: Uddrag medarbejdernes navne i forskellige kolonner i henhold til deres virksomhed.
Lad os sige, at du har en tabel med medarbejdere, der er grupperet efter deres virksomhed. Den første kolonne indeholder medarbejdernavne, og den anden kolonne indeholder virksomhedens navn.
Nu skal vi få hver medarbejders navn i forskellige kolonner i henhold til deres virksomhed. Med andre ord er vi nødt til at opdele dem.
Her har jeg navngivet A2: A10 som medarbejder og B2: B10 som virksomhed, så den formel er let at læse.
Skriv denne matrixformel i F2. Brug CTRL+SKIFT+ENTER for at indtaste denne formel.
{= INDEX (Medarbejder, LILLE (HVIS (Firma = $ E2, RÆKKE (Medarbejder) -MIN (RÆKKE (Medarbejder))+1), KOLONNER ($ E $ 1: E1))), "--List Slutter--" )}}
Kopier denne formel i alle cellerne. Det vil udtrække hvert enkelt navn i de forskellige kolonner i henhold til deres gruppe.
Som du kan se på billedet ovenfor, er hver medarbejder adskilt i forskellige celler.
Så hvordan fungerer denne formel?
For at forstå formlen, lad os se på formlen i G2
Hvilket er = HVISFEJL (INDEX (Medarbejder, LILLE (HVIS (Firma = $ E3,RÆKKE(Medarbejder) -MIN (RÆKKE (Medarbejder)))+1), COLUMNS ($ E $ 1: F2)))) "-Liste slutter-")
Mekanikken er enkel og næsten den samme som flere VLOOKUP -formler. Tricket er at få indeksnummeret for hver medarbejder fra forskellige grupper og sende det til INDEX -formlen. Dette gøres ved denne del af formlen.
HVIS(Selskab=$ E3,RÆKKE(Medarbejder) -MIN (RÆKKE (Medarbejder))+1):
Denne del returnerer en række indekser og falsk for virksomhedsnavn i $ E3, som indeholder "Rankwatch".
{FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9}.
Hvordan? Lad os rive det ned indefra.
Her matcher vi firmanavnet i $ E3 med hver værdi i Virksomhedssortiment (Firma = $ E3).
Dette returnerer en matrix med sand og falsk. {FALSE;SAND;FALSK;SAND; FALSK; FALSK;SAND;FALSK;SAND}.
Nu kører IF-funktionen sine TRUE-sætninger for TRUE, som er ROW (Medarbejder) -MIN (ROW (Medarbejder))+1. Denne del returnerer denne del returnerer en række indekser, der starter fra 1 til antal medarbejdere {1; 2; 3; 4; 5; 6; 7; 8; 9}. If -funktionen viser kun værdier for SAND, hvilket igen giver os {FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9}.
Den nuværende formel er forenklet til
= FEJL (INDEKS (Medarbejder, LILLE ({FALSK; 2; FALSK; 4; FALSK; FALSK; 7; FALSK; 9},KOLONNER ($ E $ 1: F2))), "-Liste slutter-"). Som vi kender returnerer den lille funktion nth mindste værdi fra en matrix. KOLONNER ($ E $ 1: F2) dette returnerer 2. SMALL -funktionen returnerer den næstmindste værdi fra arrayet ovenfor, som er 4.
Nu er formlen forenklet = IFERROR (INDEX (Medarbejder, 4), "-Liste slutter-"). Nu returnerer INDEX -funktionen simpelthen det fjerde navn fra medarbejder array, der giver os "Sam”.
Så ja, dette er hvordan man udtrækker navne fra grupper i forskellige kolonner ved hjælp af INDEX, SMALL, ROW, COLUMNS og IF funktion. Hvis du er i tvivl om denne funktion, eller hvis den ikke fungerer for dig, så lad mig vide kommentarfeltet herunder.
Download fil:
Relaterede artikler:
VLOOKUP Flere værdier
Brug INDEX og MATCH til at slå op på værdi
Opslagsværdi med flere kriterier
Populære artikler:
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Sådan bruges SUMIF -funktionen i Excel