Bendroji formulė
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Santrauka
Norėdami dinamiškai rūšiuoti duomenis su skaičiais ir tekstu abėcėlės tvarka, naudokite formulę, kad sugeneruotumėte skaitinį rangą pagalbiniame stulpelyje, tada naudokite INDEX ir MATCH, kad būtų rodomos vertės pagal rangą. Parodytame pavyzdyje formulė C5 yra:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
kur „duomenys“ yra įvardytas diapazonas B5: B13.
Paaiškinimas
Ši formulė pirmiausia sukuria reitingo vertę, naudojant išraišką, pagrįstą COUNTIF:
=COUNTIF(data,"<="&B5)
kas čia išsamiau paaiškinta. Jei duomenyse yra visos teksto vertės arba visos skaitinės vertės, reitingas bus teisingas. Tačiau jei į duomenis įeina ir tekstas, ir skaičiai, turime „pakeisti“ visų teksto reikšmių reitingą, kad būtų atsižvelgta į skaitines vertes. Tai daroma naudojant antrąją formulės dalį čia:
+(COUNT(data)*ISTEXT(B7))
Čia mes naudojame funkciją COUNT, kad gautume skaitinių reikšmių skaičių duomenyse, tada rezultatą padauginame iš loginio ISTEXT rezultato, kuris patikrina, ar reikšmė yra tekstas, ir pateikia TRUE arba FALSE. Tai veiksmingai panaikina COUNT rezultatą, kai dirbame su skaičiumi dabartinėje eilutėje.
Tvarkyti dublikatus
Jei duomenyse yra dublikatų, formulę galima pakeisti taip, kaip parodyta žemiau, kad priskirtumėte eilės reitingą reikšmėms, kurios rodomos daugiau nei vieną kartą:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ši versija pakoreguoja pradinės COUNTIF funkcijos logiką ir prideda dar vieną COUNTIF su besiplečiančia nuoroda į prieaugio dublikatus.
Rodyti rūšiuojamas vertes
Norėdami gauti ir rodyti reikšmes surūšiuotas vertes abėcėlės tvarka, naudodami apskaičiuotą rango vertę, E5 yra tokia INDEX ir MATCH formulė:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
kur „duomenys“ yra pavadintas diapazonas B5: B13, o „rangas“ - pavadintas diapazonas C5: C13.
Norėdami gauti daugiau informacijos apie šios formulės veikimą, žr. Pavyzdį čia.
Tuščių ruošinių sprendimas
Tuščios ląstelės sukurs nulį. Darant prielaidą, kad norite nepaisyti tuščių langelių, tai gerai veikia, nes aukščiau pateiktos INDEX ir MATCH formulės prasideda 1. Tačiau išrūšiuotų reikšmių pabaigoje pamatysite # N / A klaidas, po vieną kiekvienai tuščiai langelei. Lengvas būdas tai išspręsti yra apgaubti INDEX ir MATCH formules IFERROR taip:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
