Üldine valem
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Kokkuvõte
Andmete dünaamiliseks sortimiseks nii numbrite kui ka tekstiga tähestikulises järjekorras saate valemi abil abistaja veerus numbrilise asetuse genereerida, seejärel kasutage indeksi ja MATCH abil auastme põhjal väärtuste kuvamist. Näidatud näites on valem C5-s järgmine:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
kus "andmed" on nimega vahemik B5: B13.
Selgitus
See valem genereerib kõigepealt reastuse väärtuse, kasutades lauset, mis põhineb COUNTIF-il:
=COUNTIF(data,"<="&B5)
mida on siin täpsemalt selgitatud. Kui andmed sisaldavad kõiki tekstiväärtusi või kõiki arvväärtusi, on auaste õige. Kui aga andmed sisaldavad nii teksti kui ka numbreid, peame arvväärtuste arvestamiseks kõigi tekstiväärtuste asetuse "nihutama". Seda tehakse siin oleva valemi teise osaga:
+(COUNT(data)*ISTEXT(B7))
Siin kasutame funktsiooni COUNT andmete arvväärtuste arvu saamiseks, korrutades seejärel tulemuse ISTEXTi loogilise tulemusega, mis kontrollib, kas väärtus on tekst ja tagastab kas TÕENE või VALE. See tühistab COUNT tulemuse, kui töötame praeguse rea numbriga.
Duplikaatide käitlemine
Kui andmed sisaldavad duplikaate, saab valemit muuta, nagu allpool näidatud, et järjestikuse järjestuse määramiseks väärtustele, mis ilmuvad rohkem kui üks kord:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
See versioon kohandab algse funktsiooni COUNTIF loogikat ja lisab teise COUNTIF-i laieneva viitega juurdekasvu duplikaatidele.
Sorteeritud väärtuste kuvamine
Väärtuste järjestatud väärtuste hankimiseks ja kuvamiseks tähestikulises järjekorras, kasutades arvutatud auastmeväärtust, sisaldab E5 järgmist valemit INDEX ja MATCH
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
kus "andmed" on nimega vahemik B5: B13 ja "auaste" on nimega vahemik C5: C13.
Lisateavet selle valemi toimimise kohta leiate siit.
Toorikutega tegelemine
Tühjad lahtrid loovad auastme nulli. Eeldades, et soovite tühje lahtreid eirata, töötab see hästi, sest ülaltoodud valem INDEX ja MATCH algab punktist 1. Siiski näete sorteeritud väärtuste lõpus # N / A viga, üks iga tühja lahtri kohta. Lihtne viis sellega toime tulla on INDEX- ja MATCH-valemi mähkimine IFERROR-i järgmiselt:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
