Yleinen kaava
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Yhteenveto
Jos haluat lajitella tietoja sekä numeroilla että tekstillä dynaamisesti aakkosjärjestyksessä, voit luoda kaavan avulla numeerisen sijoituksen auttajasarakkeeseen ja näyttää sitten indeksin ja MATCH: n avulla arvot perustuen arvoon. Esitetyssä esimerkissä kaava kohdassa C5 on:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
missä "data" on nimetty alue B5: B13.
Selitys
Tämä kaava luo ensin sijoitusarvon käyttämällä lauseketta, joka perustuu COUNTIF: iin:
=COUNTIF(data,"<="&B5)
joka selitetään tarkemmin täällä. Jos tiedot sisältävät kaikki tekstiarvot tai kaikki numeeriset arvot, sijoitus on oikea. Jos tiedot sisältävät kuitenkin sekä tekstiä että numeroita, meidän on "siirrettävä" kaikkien tekstiarvojen sijoitus numeeristen arvojen huomioon ottamiseksi. Tämä tehdään tässä olevan kaavan toisen osan kanssa:
+(COUNT(data)*ISTEXT(B7))
Tässä käytämme COUNT-funktiota saadaksesi numeeristen arvojen määrän tietoihin, ja kerrotaan sitten tulos ISTEXT: n loogisella tuloksella, joka testaa, onko arvo teksti ja palauttaa joko TOSI tai EPÄTOSI. Tämä peruu COUNT-tuloksen tehokkaasti, kun työskentelemme nykyisen rivin numeron kanssa.
Kopioiden käsittely
Jos tiedot sisältävät kaksoiskappaleita, kaavaa voidaan muuttaa alla olevan kuvan mukaisesti, jotta peräkkäinen sijoitus voidaan antaa arvoille, jotka näkyvät useammin kuin kerran:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Tämä versio säätää alkuperäisen COUNTIF-funktion logiikkaa ja lisää toisen COUNTIF: n laajentuvalla viitteellä lisääntyviin kaksoiskappaleisiin.
Näytä lajitellut arvot
Jos haluat noutaa ja näyttää arvot lajiteltuina aakkosjärjestyksessä käyttäen laskettua sijoitusarvoa, E5 sisältää seuraavan INDEX- ja MATCH-kaavan:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
missä "data" on nimetty alue B5: B13 ja "sijoitus" on nimetty alue C5: C13.
Katso lisätietoja tämän kaavan toiminnasta tästä esimerkistä.
Aihioiden käsittely
Tyhjät solut tuottavat nollatason. Olettaen, että haluat jättää tyhjät solut huomiotta, tämä toimii hyvin, koska yllä oleva HAKEMISTO- ja MATCH-kaava alkaa kohdasta 1. Lajiteltujen arvojen lopussa näet kuitenkin # N / A-virheitä, yksi kutakin tyhjää solua kohden. Helppo tapa hoitaa tämä on kietoa INDEX- ja MATCH-kaava IFERROR-muotoon seuraavasti:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
