Sisällys
Jos haluat lajitella luetteloa, palveluksessasi on monia tapoja, joista helpoin on välilehden tai valikon lajittelupainikkeet Päiväys (Tiedot — Lajittele). On kuitenkin tilanteita, joissa listan lajittelu on suoritettava automaattisesti eli kaavoja. Tämä voi olla tarpeen esimerkiksi luotaessa tietoja pudotusluetteloon, laskettaessa tietoja kaavioita varten jne. Kuinka lajitella luettelo kaavalla lennossa?
Menetelmä 1. Numeerinen data
Jos lista sisältää vain numeerista tietoa, sen lajittelu onnistuu helposti funktioiden avulla VÄHITEN (PIENI) и LINE (RIVI):
Toiminto VÄHITEN (PIENI) vetää ulos taulukosta (sarake A) rivin n:nneksi pienin elementti. Nuo. PIENI(A:A;1) on sarakkeen pienin luku, PIENI(A:A;2) on toiseksi pienin ja niin edelleen.
Toiminto LINE (RIVI) palauttaa määritetyn solun rivinumeron, eli ROW(A1)=1, ROW(A2)=2 jne. Tässä tapauksessa sitä käytetään yksinkertaisesti numerosarjan n=1,2,3… generaattorina lajiteltu luettelomme. Samalla menestyksellä oli mahdollista tehdä lisäsarake, täyttää se manuaalisesti numerosarjalla 1,2,3 … ja viitata siihen ROW-funktion sijaan.
Menetelmä 2. Tekstilista ja säännölliset kaavat
Jos luettelo ei sisällä numeroita, vaan tekstiä, PIENI-toiminto ei enää toimi, joten sinun on mentävä eri, hieman pidempään polkua.
Lisätään ensin palvelusarake kaavalla, jossa kunkin nimen sarjanumero tulevassa lajitetussa luettelossa lasketaan funktiolla LASKE.JOS (COUNTIF):
Englanninkielisessä versiossa se tulee olemaan:
=LASKEJOS(A:A,»<"&A1)+LASKEJOS($A$1:A1,"="&A1)
Ensimmäinen termi on funktio, joka laskee solujen lukumäärän, jotka ovat pienempiä kuin nykyinen. Toinen on turvaverkko siltä varalta, että jokin nimi esiintyy useammin kuin kerran. Silloin heillä ei ole samoja, vaan peräkkäin kasvavia lukuja.
Nyt vastaanotetut numerot on järjestettävä peräkkäin nousevaan järjestykseen. Tätä varten voit käyttää toimintoa VÄHITEN (PIENI) ensimmäisestä tapaa:
No, lopuksi jää vain vetää nimet luettelosta numeroiden mukaan. Voit tehdä tämän käyttämällä seuraavaa kaavaa:
Toiminto LISÄTIETOJA (OTTELU) etsii sarakkeesta B haluttua sarjanumeroa (1, 2, 3 jne.) ja itse asiassa palauttaa sen rivin numeron, jolla tämä numero sijaitsee. Toiminto INDEX (INDEKSI) vetää pois sarakkeesta A nimen tässä rivinumerossa.
Menetelmä 3: Array Formula
Tämä menetelmä on itse asiassa sama sijoitusalgoritmi kuin menetelmässä 2, mutta toteutettu taulukkokaavalla. Kaavan yksinkertaistamiseksi solualueelle C1:C10 annettiin nimi Lista (valitse solut, paina Ctrl + F3 ja -painiketta luoda):
Kopioi soluun E1 kaavamme:
=INDEKSI(Lista; MATCH(SMALL(COUNTIF(Lista; “<"&Lista); ROW(1:1)); COUNTIF(Lista; "<"&Lista); 0))
Tai englanninkielisessä versiossa:
=INDEKSI(Lista, VASTAA(PIENI(LASKEJOS(Lista, «<"&Lista), RIVI(1:1)), COUNTIF(Lista, "<"&Lista), 0))
ja työnnä Ctrl + Vaihto + Entersyöttääksesi sen taulukkokaavana. Sitten tuloksena oleva kaava voidaan kopioida koko listan pituudelta.
Jos haluat, että kaava ei ota huomioon kiinteää vaihteluväliä, vaan pystyy mukautumaan, kun lisäät uusia elementtejä luetteloon, sinun on muutettava strategiaa hieman.
Ensin List-alue on asetettava dynaamisesti. Tätä varten luodessasi sinun ei tarvitse määrittää kiinteää aluetta C3:C10, vaan erityinen kaava, joka viittaa kaikkiin käytettävissä oleviin arvoihin niiden lukumäärästä riippumatta. Klikkaus Alt + F3 tai avaa välilehti Kaavat – nimien hallinta (Kaavat — Name Manager), luo uusi nimi ja kenttään Linkki (Viite) syötä seuraava kaava (oletan, että lajiteltava tietoalue alkaa solusta C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=SIIRTO(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX),XNUMX)
Toiseksi, yllä olevaa taulukkokaavaa on venytettävä alas marginaalilla - odotettaessa lisätietoa syötettävistä tulevaisuudessa. Tässä tapauksessa taulukkokaava alkaa antaa virheen #NUMBER soluissa, joita ei ole vielä täytetty. Voit siepata sen käyttämällä toimintoa IFERROR, joka on lisättävä taulukkokaavamme "ympäri":
=IFERROR(INDEX(Lista; MATCH(SMALL(COUNTIF(Lista; “<"&Lista); ROW(1:1)); COUNTIF(Lista; "<"&Lista); 0));»»)
=IFERROR(NDEX(Lista, MATCH(PIENI(LASKENTA(Lista, «<"&Lista), RIVI(1:1)), COUNTIF(Lista, "<"&Lista), 0));"")
Se havaitsee virheen #NUMBER ja tulostaa sen sijaan tyhjät lainausmerkit.
:
- Lajittele valikoima värin mukaan
- Mitä ovat taulukkokaavat ja miksi niitä tarvitaan
- LAJITtele lajittelua ja dynaamisia taulukoita uudessa Office 365:ssä