Všeobecný vzorec
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Zhrnutie
Ak chcete dynamicky triediť a extrahovať jedinečné hodnoty zo zoznamu údajov, môžete pomocou maticového vzorca určiť poradie v pomocnom stĺpci a potom pomocou špeciálne vytvoreného vzorca INDEX a MATCH extrahovať jedinečné hodnoty. V zobrazenom príklade je vzorec na určenie poradia v C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kde „údaj“ je pomenovaný rozsah B5: B13.
Poznámka: toto je vzorec pre viacbunkové pole zadaný pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Poznámka: Hlavná myšlienka tohto vzorca je prevzatá z príkladu vo vynikajúcej knihe Mika Girvina Control + Shift + Enter.
Zobrazený príklad používa niekoľko vzorcov, ktoré sú opísané nižšie. Na vysokej úrovni sa funkcia MMULT používa na výpočet číselného poradia v pomocnom stĺpci (stĺpec C) a toto poradie sa potom použije pomocou vzorca INDEX a MATCH v stĺpci G na extrahovanie jedinečných hodnôt.
Hodnotové hodnoty údajov
Funkcia MMULT vykonáva násobenie matíc a používa sa na priradenie číselného poradia každej hodnote. Prvé pole je vytvorené s nasledujúcim výrazom:
--(data>TRANSPOSE(data))
Tu používame funkciu TRANSPOSE na vytvorenie horizontálneho poľa údajov a všetky hodnoty sa porovnávajú navzájom. V zásade sa každá hodnota porovnáva s každou ďalšou hodnotou, aby sa odpovedalo na otázku „je táto hodnota väčšia ako každá iná hodnota“. Výsledkom je dvojrozmerné pole, 9 stĺpcov x 9 riadkov, vyplnené hodnotami TRUE a FALSE. Dvojitý zápor (-) sa používa na vynútenie hodnôt TRUE FALSE na 1 s a nuly. Výsledné pole môžete vizualizovať takto:
Matica 1s a núl vyššie sa stane array1 vo vnútri funkcie MMULT. Pole 2 je vytvorené s týmto výrazom:
ROW(data)^0
Tu sa každé číslo riadku v „dátach“ zvýši na nulu, aby sa vytvorilo jednorozmerné pole, 1 stĺpec x 9 riadkov, vyplnené číslom 1. MMULT potom vráti maticový produkt dvoch polí, ktoré sa stanú hodnoty videné v stĺpci poradia.
Získame späť všetkých 9 rebríčkov súčasne v poli, takže musíme dať výsledky do rôznych buniek naraz. V opačnom prípade bude každá bunka zobrazovať iba prvú hodnotenú hodnotu v poli, ktoré sa vráti.
Poznámka: toto je vzorec pre viacbunkové pole zadaný pomocou klávesov Control + Shift + Enter v rozsahu C5: C13.
Zaobchádzanie s prázdnymi bunkami
S prázdnymi bunkami sa narába s touto časťou hodnotiaceho vzorca:
=IF(data="",ROWS(data)
Tu predtým, ako spustíme MMULT, skontrolujeme, či je aktuálna bunka v „dátach“ prázdna. Ak je to tak, priraďujeme hodnostnú hodnotu, ktorá sa rovná počtu riadkov v dátach. Toto slúži na vynútenie prázdnych buniek v dolnej časti zoznamu, kde ich možno neskôr ľahko vylúčiť pri extrahovaní jedinečných hodnôt (vysvetlené nižšie).
Počítanie jedinečných hodnôt
Na počítanie jedinečných hodnôt v dátach je vzorec v E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Pretože vzorec na hodnotenie vyššie priraďuje každej hodnote číselné poradie, môžeme na spočítanie jedinečných hodnôt použiť funkciu FREKVENCIA so SUMOM. Tento vzorec je podrobne vysvetlený tu. Potom od výsledku odpočítame 1, ak sú v dátach nejaké prázdne bunky:
-(blank>0)
kde „blank“ je pomenovaný rozsah E8 a obsahuje tento vzorec:
=COUNTBLANK(data)
V zásade znížime jedinečný počet o jeden, ak sú v dátach prázdne bunky, pretože tieto nezahŕňame do výsledkov. Jedinečný počet v bunke E5 sa nazýva „jedinečný“ (pre jedinečný počet) a používa sa vo vzorcoch INDEX a MATCH na filtrovanie prázdnych buniek (popísané nižšie).
Extrahovanie jedinečných hodnôt
Na extrahovanie jedinečných hodnôt obsahuje program G5 nasledujúci vzorec skopírovaný nadol:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Pred spustením vzorca INDEX a MATCH najskôr skontrolujeme, či je aktuálny počet riadkov v oblasti extrakcie väčší ako jedinečný počet pomenovaných rozsahov „jedinečný“ (E5):
=IF(ROWS($G$5:G5)>unique,"",
Ak áno, skončili sme s extrahovaním jedinečných hodnôt a vrátime prázdny reťazec („“). Ak nie, spustíme extrakčný vzorec:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Upozorňujeme, že tu sú dve funkcie MATCH, jedna vo vnútri druhej. Vnútorná ZÁPAS používa rozbaľovací rozsah pre pole a pomenovaný rozsah „data“ pre vyhľadávaciu hodnotu:
MATCH(data,$G$4:G4,0)
Všimnite si, že rozširujúci sa rozsah začína na „riadku nad“, na riadku 4 v príklade. Výsledkom vnútornej ZHODY je pole, ktoré pre každú hodnotu v dátach obsahuje buď číselnú pozíciu (hodnota už bola extrahovaná), alebo chybu # N / A (hodnota ešte nebola extrahovaná). Potom použijeme IF a ISNA na filtrovanie týchto výsledkov a vrátime hodnotu poradia pre všetky hodnoty v „údajoch“, ktoré ešte neboli extrahované:
IF(ISNA(results),rank))
Výsledkom tejto operácie je pole, ktoré sa privedie do funkcie MIN, aby sa získala „minimálna hodnota“ pre hodnoty údajov, ktoré ešte neboli extrahované. Funkcia MIN vráti túto hodnotu do vonkajšej ZHODY ako vyhľadávaciu hodnotu a pomenovaný rozsah „poradie“ ako pole:
MATCH(min_not_extracted,rank)),rank,0)
Nakoniec funkcia MATCH vráti pozíciu najnižšej hodnoty poradia na INDEX ako číslo riadku a INDEX vráti údajovú hodnotu v aktuálnom riadku rozsahu extrakcie.
