Üldine valem
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Kokkuvõte
Andmebaasist ainulaadsete väärtuste dünaamiliseks sortimiseks ja väljavõtmiseks võite massiivivalemiga luua abistaja veerus auastme, seejärel kasutada spetsiaalselt loodud valemit INDEX ja MATCH ainulaadsete väärtuste väljavõtmiseks. Näidatud näites on valem C5: C13 auastme määramiseks:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
kus "andmed" on nimega vahemik B5: B13.
Märkus: see on mitme lahtriga massiivi valem, mis sisestatakse juhtklahviga + tõstuklahv + sisestusklahv.
Selgitus
Märkus: selle valemi põhiidee on mugandatud Mike Girvini suurepärase raamatu Control + Shift + Enter näite põhjal.
Näidatud näites kasutatakse mitut valemit, mida kirjeldatakse allpool. Kõrgel tasemel kasutatakse funktsiooni MMULT abistaja veerus (veerg C) arvulise auastme arvutamiseks ja seejärel kasutatakse seda auastet veerus G valemi INDEX ja MATCH abil ainulaadsete väärtuste väljavõtmiseks.
Andmete väärtuste järjestamine
Funktsioon MMULT teostab maatriksi korrutamist ja seda kasutatakse igale väärtusele numbrilise järjestuse määramiseks. Esimene massiiv luuakse järgmise avaldisega:
--(data>TRANSPOSE(data))
Siin me kasutame TRANSPOSE funktsioon on luua horisontaalne massiiv andmed ja kõik väärtused on võrreldes üksteist. Sisuliselt võrreldakse kõiki väärtusi kõigi teiste väärtustega, et vastata küsimusele "kas see väärtus on suurem kui iga teine väärtus". Selle tulemuseks on kahemõõtmeline massiiv, 9 veergu x 9 rida, mis on täidetud TRUE ja FALSE väärtustega. Topeltnegatiivi (-) kasutatakse TRUE FALSE väärtuste sundimiseks 1-deks ja nullideks. Saadud massiivi saate visualiseerida järgmiselt:
1s ja nullide maatriksist saab funktsiooni MMULT sees massiiv1 . Massiiv2 luuakse selle avaldisega:
ROW(data)^0
Siin tõstetakse iga "andmete" rea number nullini, et luua ühemõõtmeline massiiv, 1 veerg x 9 rida, mis on täidetud arvuga 1. Seejärel tagastab MMULT kahe massiivi maatriksi korrutise, millest saab auastme veerus näha olevad väärtused.
Saame massiivi kaudu tagasi kõik 9 edetabelit korraga, seega peame tulemused panema korraga erinevatesse lahtritesse. Vastasel juhul kuvatakse igas lahtris ainult tagastatava massiivi esimene järjestusväärtus.
Märkus: see on mitme lahtriga massiivi valem, mis sisestatakse juhtklahvi + shift + enter abil vahemikku C5: C13.
Tühjade lahtrite käitlemine
Tühjad lahtrid käsitletakse pingerida valemi selle osaga:
=IF(data="",ROWS(data)
Siin kontrollime enne MMULT-i käivitamist, kas "andmete" praegune lahter on tühi. Kui jah, määrame auastme väärtuse, mis võrdub andmete reaarvuga. Seda tehakse tühjade lahtrite sundimiseks loendi lõppu, kus neid saab pärast unikaalsete väärtuste väljavõtmist hiljem hõlpsasti välistada (selgitatud allpool).
Ainulaadsete väärtuste loendamine
Andmete ainulaadsete väärtuste lugemiseks on valem E5-s järgmine:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Kuna ülaltoodud järjestusvalem määrab igale väärtusele numbrilise asetuse, saame unikaalsete väärtuste loendamiseks kasutada funktsiooni FREQUENCY koos summaga. Seda valemit selgitatakse siin üksikasjalikult. Seejärel lahutame tulemusest 1, kui andmetes on tühje lahtreid:
-(blank>0)
kus "tühi" on nimega vahemik E8 ja sisaldab järgmist valemit:
=COUNTBLANK(data)
Põhimõtteliselt vähendame kordumatut arvu ühe võrra, kui andmetes on tühje lahtreid, kuna me ei hõlma neid tulemustes. Lahtris E5 on ainulaadne arv nimega "kordumatu" (kordumatu loenduse jaoks) ning valem INDEX ja MATCH kasutavad seda tühjade lahtrite filtreerimiseks (kirjeldatud allpool).
Unikaalsete väärtuste väljavõtmine
Unikaalsete väärtuste väljavõtmiseks sisaldab G5 järgmist valemit, mis on kopeeritud:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Enne valemi INDEX ja MATCH käivitamist kontrollime kõigepealt, kas praegune ridade arv ekstraheerimisalas on suurem kui kordumatu arv nimega vahemik "unikaalne" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Kui jah, siis oleme unikaalsete väärtuste väljavõtmise lõpetanud ja tagastame tühja stringi (""). Kui ei, käivitame ekstraheerimisvalemi:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Pange tähele, et siin on kaks MATCH-funktsiooni, üks teise sees. Sisemine MATCH kasutab massiivi jaoks laienevat vahemikku ja otsitud väärtuse jaoks nimega vahemikku "andmed":
MATCH(data,$G$4:G4,0)
Pange tähele, et laienev vahemik algab näite 4. real "ülal". Sisemise MATCHi tulemuseks on massiiv, mis sisaldab iga andmete väärtuse kohta kas numbrilist positsiooni (väärtus on juba välja tõmmatud) või viga # N / A (väärtust pole veel välja tõmmatud). Seejärel kasutame nende tulemuste filtreerimiseks IF-i ja ISNA-d ning tagastame kõigi veel andmestikus "andmed" olevate väärtuste auastme väärtuse:
IF(ISNA(results),rank))
Selle toimingu tulemusena saadakse massiiv, mis sisestatakse MIN-funktsiooni, et saada veel väljavõtmata andmeväärtuste "minimaalne järjekoha väärtus". Funktsioon MIN tagastab selle väärtuse välimisele MATCH otsingu väärtusena ja nimega vahemik "auaste" massiivina:
MATCH(min_not_extracted,rank)),rank,0)
Lõpuks tagastab MATCH madalaima auastme väärtuse positsiooni reale INDEX ja INDEX tagastab väljavõtte vahemiku praeguse rea andmete väärtuse.
