Splošna formula
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Povzetek
Če želite dinamično razvrščati in izvleči enolične vrednosti s seznama podatkov, lahko s formulo matrike določite uvrstitev v pomožnem stolpcu, nato pa s posebno izdelano formulo INDEX in MATCH izvlečete unikatne vrednosti. V prikazanem primeru je formula za določitev ranga v C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kjer je "podatki" imenovano območje B5: B13.
Opomba: to je formula z več celicami, vnesena s tipko control + shift + enter.
Pojasnilo
Opomba: osrednja ideja te formule je prilagojena primeru iz odlične knjige Mike Girvin Control + Shift + Enter.
V prikazanem primeru je uporabljenih več formul, ki so opisane spodaj. Na visoki ravni se funkcija MMULT uporablja za izračun številskega ranga v pomožnem stolpcu (stolpec C) in ta rang nato s formulo INDEX in MATCH v stolpcu G izvleče edinstvene vrednosti.
Vrednosti podatkov o razvrstitvi
Funkcija MMULT izvaja množenje matric in se uporablja za dodelitev številskega ranga vsaki vrednosti. Prvo polje je ustvarjeno z naslednjim izrazom:
--(data>TRANSPOSE(data))
Tu uporabljamo funkcijo TRANSPOSE za ustvarjanje vodoravnega polja podatkov in vse vrednosti se primerjajo med seboj. V bistvu se vsaka vrednost primerja z vsako drugo vrednostjo, da se odgovori na vprašanje "ali je ta vrednost večja od vseh drugih vrednosti". Rezultat je dvodimenzionalno polje, 9 stolpcev x 9 vrstic, napolnjeno z vrednostmi TRUE in FALSE. Dvojni negativ (-) se uporablja za prisile vrednosti TRUE FALSE na 1s in ničle. Nastalo matriko lahko vizualizirate tako:
Matrica 1s in ničel zgoraj postane matrika1 znotraj funkcije MMULT. Array2 je ustvarjen s tem izrazom:
ROW(data)^0
Tu se vsaka številka vrstice v "podatkih" dvigne na stopnjo nič, da se ustvari enodimenzionalno polje, 1 stolpec x 9 vrstic, napolnjeno s številko 1. MMULT nato vrne matrični zmnožek obeh nizov, ki postaneta vrednosti, vidne v stolpcu rank.
V matriko dobimo vseh 9 uvrstitev hkrati, zato moramo rezultate hkrati postaviti v različne celice. V nasprotnem primeru bo vsaka celica prikazala samo prvo vrednost uvrstitve v vrnjenem polju.
Opomba: to je formula z več celicami, vnesena s tipko control + shift + enter, v območju C5: C13.
Ravnanje s praznimi celicami
Prazne celice se obravnavajo s tem delom formule uvrstitve:
=IF(data="",ROWS(data)
Tukaj, preden zaženemo MMULT, preverimo, ali je trenutna celica v "data" prazna. V tem primeru dodelimo vrednost ranga, ki je enaka številu vrstic v podatkih. To se naredi tako, da se prazne celice prisilijo na dno seznama, kjer jih je mogoče kasneje zlahka izključiti, ko se izvlečejo edinstvene vrednosti (razloženo spodaj).
Štetje edinstvenih vrednosti
Za štetje enoličnih vrednosti v podatkih je formula v E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Ker zgornja formula za uvrstitev vsaki vrednosti dodeli številski rang, lahko s funkcijo FREQUENCY s SUM štejemo unikatne vrednosti. Ta formula je tukaj podrobno razložena. Nato od rezultata odštejemo 1, če so v podatkih prazne celice:
-(blank>0)
kjer je "prazno" imenovani obseg E8 in vsebuje to formulo:
=COUNTBLANK(data)
V bistvu enolično število zmanjšamo za eno, če so v podatkih prazne celice, saj jih ne vključimo v rezultate. Edinstveno štetje v celici E5 se imenuje "edinstveno" (za enotno štetje) in ga s formulo INDEX in MATCH uporabljata za filtriranje praznih celic (opisano spodaj).
Izdvajanje edinstvenih vrednosti
Če želite izvleči edinstvene vrednosti, G5 vsebuje naslednjo kopirano formulo:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Preden zaženemo formulo INDEX in MATCH, najprej preverimo, ali je trenutno število vrstic v območju za ekstrakcijo večje od števila unikatov, imenovanega obsega "edinstveno" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Če je temu tako, smo končali z ekstrahiranjem edinstvenih vrednosti in vrnili smo prazen niz (""). Če ne, zaženemo formulo ekstrakcije:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Upoštevajte, da sta tu dve funkciji MATCH, ena v drugi. Notranji MATCH uporablja obseg, ki se širi za matriko, in imenovani obseg "podatki" za vrednost iskanja:
MATCH(data,$G$4:G4,0)
Upoštevajte, da se razširitev obsega začne v "vrstici zgoraj", vrstica 4 v primeru. Rezultat notranjega MATCH je matrika, ki za vsako vrednost v podatkih vsebuje bodisi številčni položaj (vrednost je že bila izvlečena) bodisi napaka # N / A (vrednost še ni bila izvlečena). Nato uporabimo IF in ISNA za filtriranje teh rezultatov in vrnemo vrednost ranga za vse vrednosti v "podatkih", ki še niso ekstrahirani:
IF(ISNA(results),rank))
Ta operacija povzroči matriko, ki se vnese v funkcijo MIN, da dobi "najmanjšo vrednost ranga" za vrednosti podatkov, ki še niso izvlečene. Funkcija MIN vrne to vrednost zunanjemu MATCH kot iskalno vrednost in imenovani obseg "rank" kot matriko:
MATCH(min_not_extracted,rank)),rank,0)
Končno, MATCH vrne položaj najnižje vrednosti ranga v INDEX kot številko vrstice, INDEX pa vrne podatkovno vrednost v trenutni vrstici obsega ekstrakcije.
