Tento článek bude hovořit o tom, jak získat všechny hodnoty z tabulky a načíst je do různých buněk. To je podobné vyhledávání více hodnot.
Obecný vzorec
{= INDEX (názvy, MALÉ (IF (skupiny = název_skupiny, Řádek (názvy) -MIN (ŘÁDEK (jména))+1), SLOUPKY (rozšiřující rozsahy)))), „--Seznam končí-“)}
Příliš mnoho funkcí a proměnných !!!. Podívejme se, jaké jsou tyto proměnné.
Jména: Toto je seznam jmen.
Skupiny: Seznam skupiny, do které tato jména také patří.
Skupinové jméno: odkaz na název skupiny.
Rozšiřování rozsahů: toto je rozšiřující se rozsah, který se používá ke zvýšení počtu při kopírování doprava.
Příklad: Extrahujte jména zaměstnanců do různých sloupců podle jejich společnosti.
Řekněme, že máte tabulku zaměstnanců, která je seskupena podle jejich společnosti. První sloupec obsahuje jména zaměstnanců a druhý sloupec obsahuje název společnosti.
Nyní musíme dostat jméno každého zaměstnance do různých sloupců podle jeho společnosti. Jinými slovy, musíme je oddělit.
Zde jsem pojmenoval A2: A10 jako zaměstnance a B2: B10 jako společnost, aby byl vzorec snadno čitelný.
Napište tento vzorec pole do F2. Tento vzorec zadejte pomocí CTRL+SHIFT+ENTER.
{= INDEX (Zaměstnanec, MALÝ (IF (Společnost = E2 $, ŘÁDEK (Zaměstnanec) -MIN (ŘÁDEK (zaměstnanec))+1), SLOUPKY ($ E $ 1: E1))))), „--Seznam končí-“ )}
Zkopírujte tento vzorec do všech buněk. Rozbalí každé jednotlivé jméno v různých sloupcích podle jejich skupiny.
Jak vidíte na obrázku výše, každý zaměstnanec je rozdělen do různých buněk.
Jak tedy tento vzorec funguje?
Abychom pochopili vzorec, podívejme se na vzorec v G2
Což je = IFERROR (INDEX (Zaměstnanec, MALÝ (IF (Společnost = E3 $,ŘÁDEK(Zaměstnanec) -MIN (ŘADA (Zaměstnanec))+1), SLOUPKY ($ E $ 1: F2))), "-Seznam končí-")
Mechanika je jednoduchá a téměř stejná jako více vzorců VLOOKUP. Jde o to, získat indexové číslo každého zaměstnance z různých skupin a předat jej do vzorce INDEX. To se provádí v této části vzorce.
LI(Společnost=$ E3,ŘÁDEK(Zaměstnanec) -MIN (ŘADA (Zaměstnanec))+1):
Tato část vrací řadu indexů a false pro název společnosti v $ E3, který obsahuje „Rankwatch“.
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Jak? Pojďme to zbourat zevnitř.
Zde přiřadíme název společnosti v $ E3 s každou hodnotou v Rozsah společnosti (Společnost = $ E3).
Tím se vrátí pole true a false. {FALSE;SKUTEČNÝ;NEPRAVDIVÉ;SKUTEČNÝ; FALSE; FALSE;SKUTEČNÝ;NEPRAVDIVÉ;SKUTEČNÝ}.
Nyní funkce IF spustí příkazy TRUE pro TRUE, což je ROW (zaměstnanec) -MIN (ROW (Zaměstnanec))+1. Tato část vrací tuto část vrací řadu indexů od 1 do počtu zaměstnanci {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkce if zobrazuje hodnoty pouze pro PRAVDA, což nám dává {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Aktuální vzorec je zjednodušen na
= IFERROR (INDEX (zaměstnanec, MALÝ ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},SLOUPKY ($ E $ 1: F2))), "-Seznam končí-"). Jak víme, malá funkce vrátí n -tu nejmenší hodnotu z pole. SLOUPKY ($ E $ 1: F2) toto vrátí 2. SMALL funkce vrátí druhou nejmenší hodnotu z výše uvedeného pole, což je 4.
Nyní je vzorec zjednodušen = IFERROR (INDEX (zaměstnanec, 4), "-Seznam končí-"). Nyní funkce INDEX jednoduše vrátí čtvrté jméno z zaměstnanec pole, které nám dává „Sam”.
Takže jo, takto extrahujte jména ze skupin v různých sloupcích pomocí funkcí INDEX, SMALL, ROW, COLUMNS a IF. Pokud máte ohledně této funkce jakékoli pochybnosti nebo pokud pro vás nefunguje, dejte mi vědět níže uvedenou sekci s komentáři.
Stáhnout soubor:
Související články:
VLOOKUP Více hodnot
K vyhledání hodnoty použijte INDEX a MATCH
Vyhledávací hodnota s více kritérii
Oblíbené články:
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel