Generička formula
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Sažetak
Da biste dinamički sortirali i izvukli jedinstvene vrijednosti s popisa podataka, pomoću formule niza možete uspostaviti rang u pomoćnom stupcu, a zatim upotrijebiti posebno izrađenu formulu INDEX i MATCH za izdvajanje jedinstvenih vrijednosti. U prikazanom primjeru formula za utvrđivanje ranga u C5: C13 je:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
gdje je "podatak" imenovani raspon B5: B13.
Napomena: ovo je formula s više ćelija, unesena sa control + shift + enter.
Obrazloženje
Napomena: srž ideje ove formule prilagođena je primjeru u izvrsnoj knjizi Mikea Girvina Control + Shift + Enter.
Prikazani primjer koristi nekoliko formula koje su opisane u nastavku. Na visokoj razini, funkcija MMULT koristi se za izračunavanje numeričkog ranga u pomoćnom stupcu (stupac C), a taj rang zatim koristi INDEX i MATCH formula u stupcu G za izdvajanje jedinstvenih vrijednosti.
Vrijednosti podataka o poretku
Funkcija MMULT izvodi množenje matrice i koristi se za dodjeljivanje numeričkog ranga svakoj vrijednosti. Prvi niz kreira se sa sljedećim izrazom:
--(data>TRANSPOSE(data))
Ovdje koristimo funkciju TRANSPOSE za stvaranje vodoravnog niza podataka i sve se vrijednosti uspoređuju jedna s drugom. U osnovi se svaka vrijednost uspoređuje sa svakom drugom vrijednošću kako bi se odgovorilo na pitanje "je li ta vrijednost veća od svake druge vrijednosti". Rezultat je dvodimenzionalni niz, 9 stupaca x 9 redaka, ispunjen vrijednostima TRUE i FALSE. Dvostruki negativ (-) koristi se za prisiljavanje vrijednosti TRUE FALSE na 1s i nule. Dobiveni niz možete vizualizirati ovako:
Matrica +1 i nula gore postaje polje1 unutar funkciju MMULT. Array2 je stvoren s ovim izrazom:
ROW(data)^0
Ovdje se svaki broj retka u "podacima" podiže na nulu da bi se stvorio jednodimenzionalni niz, 1 stupac x 9 redaka, ispunjen brojem 1. MMULT zatim vraća matrični umnožak dva polja, koja postaju vrijednosti vidljive u stupcu ranga.
Svih 9 ljestvica istodobno vraćamo u niz, tako da rezultate moramo odjednom staviti u različite ćelije. Inače, svaka ćelija prikazat će samo prvu rangiranu vrijednost u polju koji se vraća.
Napomena: ovo je formula s više ćelija, unesena sa control + shift + enter, u rasponu C5: C13.
Rukovanje praznim stanicama
Prazne stanice obrađuju se ovim dijelom formule za rangiranje:
=IF(data="",ROWS(data)
Ovdje, prije nego što pokrenemo MMULT, provjeravamo je li trenutna ćelija u "podacima" prazna. Ako je tako, dodjeljujemo vrijednost ranga koja je jednaka broju redova u podacima. To se radi kako bi se prazne stanice natjerale na dno popisa, gdje se kasnije mogu lako izuzeti kad se izvade jedinstvene vrijednosti (objašnjeno u nastavku).
Brojanje jedinstvenih vrijednosti
Za brojanje jedinstvenih vrijednosti u podacima, formula u E5 je:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Budući da gornja formula za rangiranje svakoj vrijednosti dodjeljuje numerički rang, možemo koristiti funkciju FREQUENCY sa SUM za brojanje jedinstvenih vrijednosti. Ova je formula ovdje detaljno objašnjena. Zatim od rezultata oduzimamo 1 ako u podacima ima praznih ćelija:
-(blank>0)
gdje je "prazno" imenovani raspon E8 i sadrži ovu formulu:
=COUNTBLANK(data)
U osnovi jedinstveni broj smanjujemo za jedan ako u podacima postoje prazne stanice, jer ih ne uključujemo u rezultate. Jedinstveni broj u ćeliji E5 naziva se "jedinstven" (za jedinstveni broj), a koristi ga formula INDEX i MATCH za filtriranje praznih stanica (opisano u nastavku).
Izdvajanje jedinstvenih vrijednosti
Da bi izvukao jedinstvene vrijednosti, G5 sadrži sljedeću kopiranu formulu:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Prije nego što pokrenemo formulu INDEX i MATCH, prvo provjeravamo je li trenutni broj redaka u području ekstrakcije veći od jedinstvenog broja imenovanog raspona "jedinstveno" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Ako je to slučaj, gotovi smo s izvlačenjem jedinstvenih vrijednosti i vraćamo prazan niz (""). Ako ne, pokrećemo formulu ekstrakcije:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Imajte na umu da su ovdje dvije funkcije MATCH, jedna u drugoj. Unutarnji MATCH koristi opseg koji se širi za niz i imenovani raspon "podaci" za vrijednost pretraživanja:
MATCH(data,$G$4:G4,0)
Primijetite da raspon širenja započinje u "retku iznad", retku 4 u primjeru. Rezultat iz unutarnjeg MATCH-a je niz koji za svaku vrijednost u podacima sadrži ili numerički položaj (vrijednost je već izdvojena) ili pogrešku # N / A (vrijednost još nije izvučena). Zatim koristimo IF i ISNA za filtriranje ovih rezultata i vraćamo vrijednost ranga za sve vrijednosti u "podacima" koji još nisu izvučeni:
IF(ISNA(results),rank))
Ova operacija rezultira nizom, koji se dovodi u funkciju MIN kako bi se dobila "minimalna vrijednost ranga" za vrijednosti podataka koje još nisu izvađene. MIN funkcija vraća ovu vrijednost na vanjski MATCH kao vrijednost pretraživanja i imenovani raspon "rank" kao niz:
MATCH(min_not_extracted,rank)),rank,0)
Napokon, MATCH vraća položaj vrijednosti najnižeg ranga u INDEX kao broj retka, a INDEX vraća vrijednost podataka u trenutnom retku raspona ekstrakcije.
