Obecný vzorec
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
souhrn
Chcete-li dynamicky řadit a extrahovat jedinečné hodnoty ze seznamu dat, můžete pomocí maticového vzorce určit pořadí ve sloupci pomocníka a poté pomocí speciálně vytvořeného vzorce INDEX a MATCH extrahovat jedinečné hodnoty. V zobrazeném příkladu je vzorec pro stanovení pořadí v C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kde „data“ je pojmenovaný rozsah B5: B13.
Poznámka: toto je vzorec pole s více buňkami, zadaný pomocí kláves Control + Shift + Enter.
Vysvětlení
Poznámka: Základní myšlenka tohoto vzorce je převzata z příkladu ve vynikající knize Mika Girvina Control + Shift + Enter.
Zobrazený příklad používá několik vzorců, které jsou popsány níže. Na vyšší úrovni se funkce MMULT používá k výpočtu číselného pořadí ve sloupci pomocníka (sloupec C) a toto pořadí se pak používá vzorcem INDEX a MATCH ve sloupci G k extrahování jedinečných hodnot.
Hodnoty datových hodnot
Funkce MMULT provádí násobení matic a používá se k přiřazení číselného pořadí každé hodnotě. První pole je vytvořeno s následujícím výrazem:
--(data>TRANSPOSE(data))
Zde používáme funkci TRANSPOSE k vytvoření horizontálního pole dat a všechny hodnoty jsou navzájem porovnány. V podstatě je každá hodnota porovnána s každou jinou hodnotou, aby bylo možné odpovědět na otázku „je tato hodnota větší než každá jiná hodnota“. Výsledkem je dvourozměrné pole, 9 sloupců x 9 řádků, vyplněné hodnotami TRUE a FALSE. Dvojitý zápor (-) se používá k vynucení hodnot TRUE FALSE na 1 s a nuly. Výsledné pole můžete vizualizovat takto:
Matice 1s a nul výše se stane array1 uvnitř funkce MMULT. Pole 2 je vytvořeno s tímto výrazem:
ROW(data)^0
Zde se každé číslo řádku v „datech“ zvýší na nulu, aby se vytvořilo jednorozměrné pole, 1 sloupec x 9 řádků, vyplněné číslem 1. MMULT poté vrátí maticový produkt dvou polí, která se stanou hodnoty zobrazené ve sloupci hodnocení.
Vracíme zpět všech 9 žebříčků najednou v poli, takže musíme dát výsledky do různých buněk najednou. Jinak každá buňka zobrazí pouze první hodnotící hodnotu v poli, které je vráceno.
Poznámka: jedná se o vzorec vícebuněčného pole zadaný pomocí kláves Control + Shift + Enter v rozsahu C5: C13.
Zpracování prázdných buněk
S prázdnými buňkami se zachází s touto částí hodnotícího vzorce:
=IF(data="",ROWS(data)
Tady, než spustíme MMULT, zkontrolujeme, zda je aktuální buňka v „datech“ prázdná. Pokud ano, přiřadíme hodnostní hodnotu, která se rovná počtu řádků v datech. To slouží k vynucení prázdných buněk do dolní části seznamu, kde je lze později snadno vyloučit při extrahování jedinečných hodnot (vysvětleno níže).
Počítání jedinečných hodnot
Chcete-li spočítat jedinečné hodnoty v datech, vzorec v E5 je:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Vzhledem k tomu, že výše uvedený vzorec hodnocení přiřazuje každé hodnotě číselné hodnocení, můžeme k počítání jedinečných hodnot použít funkci FREQUENCY s funkcí SUM. Tento vzorec je podrobně vysvětlen zde. Poté od výsledku odečteme 1, pokud jsou v datech nějaké prázdné buňky:
-(blank>0)
kde „blank“ je pojmenovaný rozsah E8 a obsahuje tento vzorec:
=COUNTBLANK(data)
V zásadě snížíme jedinečný počet o jednu, pokud jsou v datech prázdné buňky, protože je nezahrnujeme do výsledků. Jedinečný počet v buňce E5 má název „jedinečný“ (pro jedinečný počet) a je používán vzorcem INDEX a MATCH k odfiltrování prázdných buněk (popsáno níže).
Extrakce jedinečných hodnot
Chcete-li extrahovat jedinečné hodnoty, obsahuje G5 následující vzorec zkopírovaný dolů:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Než spustíme vzorec INDEX a MATCH, nejprve zkontrolujeme, zda je aktuální počet řádků v oblasti těžby větší než jedinečný počet pojmenovaného rozsahu „unique“ (E5):
=IF(ROWS($G$5:G5)>unique,"",
Pokud ano, jsme hotovi s extrahováním jedinečných hodnot a vrátíme prázdný řetězec (""). Pokud ne, spustíme extrakční vzorec:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Všimněte si, že zde existují dvě funkce MATCH, jedna uvnitř druhé. Vnitřní MATCH používá rozšiřující se rozsah pro pole a pojmenovaný rozsah „data“ pro vyhledávací hodnotu:
MATCH(data,$G$4:G4,0)
Všimněte si, že rozšiřující se rozsah začíná v řádku „výše“, v příkladu na řádku 4. Výsledkem vnitřní MATCH je pole, které pro každou hodnotu v datech obsahuje buď číselnou pozici (hodnota již byla extrahována), nebo chybu # N / A (hodnota ještě nebyla extrahována). Potom použijeme IF a ISNA k filtrování těchto výsledků a vrátíme hodnotu pořadí pro všechny hodnoty v dosud nevyextrahovaných "datech":
IF(ISNA(results),rank))
Výsledkem této operace je pole, které se přivede do funkce MIN za účelem získání „minimální hodnoty hodnoty“ pro dosud nevyextrahované hodnoty dat. Funkce MIN vrací tuto hodnotu k vnějšímu MATCH jako vyhledávací hodnotu a pojmenovaný rozsah „rank“ jako pole:
MATCH(min_not_extracted,rank)),rank,0)
Nakonec MATCH vrátí pozici nejnižší hodnoty hodnoty na INDEX jako číslo řádku a INDEX vrátí hodnotu dat v aktuálním řádku rozsahu extrakce.
