Tento článok bude hovoriť o tom, ako získať všetky hodnoty z tabuľky a načítať ich do rôznych buniek. Je to podobné ako pri vyhľadávaní viacerých hodnôt.
Generický vzorec
{= INDEX (názvy, MALÉ (IF (skupiny = názov_skupiny, ROW (názvy) -MIN (ROW (názvy))+1), COLUMNS (rozširujúce sa rozsahy)))), „--Seznam končí-“)}
Príliš veľa funkcií a premenných !!!. Pozrime sa, aké sú tieto premenné.
Mená: Toto je zoznam mien.
Skupiny: Zoznam skupín, do ktorých tieto mená tiež patria.
Názov skupiny: odkaz na názov skupiny.
Rozširovanie rozsahov: toto je rozširujúci sa rozsah, ktorý sa používa na získanie rastúceho počtu pri kopírovaní doprava.
Príklad: Extrahujte mená zamestnancov do rôznych stĺpcov podľa ich spoločnosti.
Povedzme, že máte tabuľku zamestnancov, ktorá je zoskupená podľa ich spoločnosti. Prvý stĺpec obsahuje mená zamestnancov a druhý stĺpec obsahuje názov spoločnosti.
Teraz musíme dostať meno každého zamestnanca do rôznych stĺpcov podľa jeho spoločnosti. Inými slovami, musíme ich oddeliť.
Tu som pomenoval A2: A10 ako zamestnanca a B2: B10 ako spoločnosť, aby bol vzorec ľahko čitateľný.
Napíšte tento vzorec poľa do F2. Na zadanie tohto vzorca použite CTRL+SHIFT+ENTER.
{= INDEX (zamestnanec, MALÝ (IF (spoločnosť = E2 $, ROW (zamestnanec) -MIN (ROW (zamestnanec))+1), COLUMNS ($ E $ 1: E1))))), „-zoznam končí-“ )}
Skopírujte tento vzorec do všetkých buniek. Extrahuje každé jednotlivé meno do rôznych stĺpcov podľa ich skupiny.
Ako vidíte na obrázku vyššie, každý zamestnanec je rozdelený do rôznych buniek.
Ako teda tento vzorec funguje?
Aby sme pochopili vzorec, pozrime sa na vzorec v G2
Čo je = IFERROR (INDEX (Zamestnanec, MALÝ (AK (Spoločnosť = E3 USD, RIADOK (Zamestnanec) -MIN (RIADOK (Zamestnanec))+1), SLOUPKY ($ E $ 1: F2))), "-Zoznam sa končí-")
Mechanika je jednoduchá a takmer rovnaká ako pri viacnásobnom vzorci VLOOKUP. Ide o to, získať indexové číslo každého zamestnanca z rôznych skupín a preniesť ho do vzorca INDEX. To sa deje v tejto časti vzorca.
IF (Spoločnosť=E3 dolárov, RIADOK (Zamestnanec) -MIN (RIADOK (Zamestnanec))+1):
Táto časť vracia pole indexov a nepravdivých pre názov spoločnosti v $ E3, ktoré obsahuje „Rankwatch“.
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Ako? Zbúrajme to zvnútra.
Tu priradíme názov spoločnosti v E3 $ k každej hodnote v Rozsah spoločnosti (Spoločnosť = E3 $).
Vráti sa tým pole pravdivých a nepravdivých. {FALSE;PRAVDA; NEPRAVDA;PRAVDA; FALSE; FALSE;PRAVDA; NEPRAVDA;PRAVDA}.
Teraz funkcia IF spustí svoje PRAVDIVÉ príkazy pre TRUE, čo je ROW (zamestnanec) -MIN (ROW (Zamestnanec))+1. Táto časť vráti túto časť vráti pole indexov od 1 do počtu zamestnancov {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkcia if zobrazí iba hodnoty TRUE, čo nám poskytne {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Súčasný vzorec je zjednodušený na
= IFERROR (INDEX (zamestnanec, MALÝ ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},STĹPCE ($ E $ 1: F2))), "-Zoznam končí-"). Ako vieme, malá funkcia vráti z poľa n -tú najmenšiu hodnotu. STĹPCE ($ E $ 1: F2) toto vráti 2. SMALL funkcia vráti druhú najmenšiu hodnotu z vyššie uvedeného poľa, ktorá je 4.
Teraz je vzorec zjednodušený = IFERROR (INDEX (zamestnanec, 4), "-Zoznam končí-"). Teraz funkcia INDEX jednoducho vráti štvrtý názov z zamestnanec pole, ktoré nám dáva „Sam”.
Takže áno, takto extrahujte názvy zo skupín v rôznych stĺpcoch pomocou funkcií INDEX, SMALL, ROW, COLUMNS a IF. Ak máte akékoľvek pochybnosti o tejto funkcii alebo ak vám nefunguje, dajte mi vedieť nižšie uvedenú sekciu komentárov.
Stiahnuť súbor:
Súvisiace články:
VLOOKUP Viacnásobné hodnoty
Na vyhľadanie hodnoty použite INDEX a MATCH
Hodnota vyhľadávania s viacerými kritériami
Populárne články:
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel