Excelin sisäänrakennettu tietojen lajittelu on hämmästyttävää, mutta se ei ole dynaamista. Jos lajittelet tiedot ja lisäät niihin sitten tietoja, sinun on lajiteltava ne uudelleen.
Lajittele tiedot aakkosjärjestykseen
Tässä viestissä näytän sinulle erilaisia tapoja lajitella tiedot aakkosjärjestykseen kaavojen avulla. Tämä tarkoittaa, että voit lisätä tietoja, ja se lajittelee ne automaattisesti puolestasi.
Kun tiedot ovat kaikki tekstiä ilman kaksoiskappaleita
Oletetaan, että sinulla on alla olevan kuvan mukaiset tiedot:
Tässä esimerkissä kaikki tiedot ovat tekstimuodossa (ei numeroita, tyhjiä tai kaksoiskappaleita). Tämän lajittelemiseksi käytän auttajasaraketta. Käytä tietojen vieressä olevassa sarakkeessa seuraavaa COUNTIF -kaavaa:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tämä kaava vertaa tekstiarvoa kaikkiin muihin tekstiarvoihin ja palauttaa sen suhteellisen sijoituksen. Esimerkiksi solussa B2 se palauttaa 8, koska siinä on 8 tekstiarvoa, jotka ovat pienempiä tai yhtä suuria kuin teksti "US" (aakkosjärjestys).
Jos haluat lajitella arvot, käytä seuraavaa INDEX-, MATCH- ja ROWS -toimintojen yhdistelmää:
= INDEKSI ($ A $ 2: $ A $ 9, MATCH (RIVIT ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Tämä kaava vain poimii nimet aakkosjärjestyksessä. Ensimmäisessä solussa (C2) se etsii maan nimen, jolla on pienin numero (Australiassa on 1). Toisessa solussa se palauttaa Kanadan (jonka numero on 2) ja niin edelleen …
Oletko allerginen auttajapylväille ??
Tässä on kaava, joka tekee saman ilman auttajasaraketta.
= INDEKSI ($ A $ 2: $ A $ 9, MATCH (RIVIT ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Tämä on taulukkokaava, joten käytä sitä Control + Vaihto + Enter Enterin sijaan.
Annan sinun purkaa koodin.
Kokeile itse… Lataa esimerkkitiedosto
Tämä kaava toimii hyvin, jos sinulla on teksti- tai aakkosnumeerisia arvoja.
Mutta se epäonnistuu surkeasti, jos:
- Sinulla on kaksoiskappaleita tiedoissa (yritä lisätä US kahdesti).
- Aineistossa on tyhjiä kohtia.
- Sinulla on sekoitus numeroita ja tekstiä (yritä laittaa 123 johonkin soluun).
Kun data on sekoitus numeroita, tekstiä, kaksoiskappaleita ja tyhjiöitä
Nyt tämä on vähän hankala. Käytän neljää apulaissaraketta näyttääkseni, miten se toimii (ja annan sitten sinulle valtavan kaavan, joka tekee sen ilman apulaissarakkeita). Oletetaan, että sinulla on alla olevan kuvan mukaiset tiedot:
Näet, että on olemassa päällekkäisiä arvoja, tyhjiä ja numeroita. Käytän siis auttajasarakkeita käsitelläkseni kaikkia näitä ongelmia.
Auttaja -sarake 1
Kirjoita seuraava COUNTIF -kaava Helper -sarakkeeseen 1
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tämä kaava toimii seuraavasti:
- Se palauttaa tyhjät kohdat 0.
- Jos kyseessä on kaksoiskappale, se palauttaa saman numeron.
- Teksti ja numerot käsitellään rinnakkain, ja tämä kaava palauttaa saman numeron tekstille ja numerolle (esimerkiksi 123 ja Intia saavat molemmat 1).
Auttaja -sarake 2
Kirjoita seuraava IS -toiminto Helper -sarakkeeseen 2:
=-ISNUMBER (A2)
Auttaja -sarake 3
Kirjoita seuraava kaava Helper -sarakkeeseen 3:
=-ISBLANK (A2)
Auttaja -sarake 4
Kirjoita seuraava kaava Helper -sarakkeeseen 4
= JOS (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Tämän kaavan idea on erottaa tyhjät kohdat, numerot ja tekstiarvot.
- Jos solu on tyhjä, se palauttaa arvon solussa B2 (joka olisi aina 0) ja lisää arvon soluun D10. Lyhyesti sanottuna se palauttaa tietojen tyhjien solujen kokonaismäärän
- Jos solu on numeerinen arvo, se palauttaa vertailevan sijoituksen ja lisää tyhjien kohtien kokonaismäärän. Esimerkiksi 123: lle se palauttaa 2 (1 on 123: n sijoitus tiedoissa ja 1 tyhjä solu)
- Jos se on teksti, se palauttaa vertailevan sijoituksen ja lisää numeeristen arvojen ja aihioiden kokonaismäärän. Esimerkiksi Intiassa se lisää tekstin vertailevan sijoituksen tekstiin (joka on 1) ja lisää tyhjien solujen määrän ja numeeristen arvojen määrän.
Lopputulos - lajiteltu data
Nyt käytämme näitä auttajasarakkeita lajitellun luettelon saamiseen. Tässä on kaava:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (PIENI ($ E $ 2: $ E $ 9, RIVIT ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Tästä lajittelumenetelmästä tulee nyt typerä. Olen näyttänyt sinulle menetelmän kahdeksalle kohteelle, mutta voit laajentaa sen niin monta kohdetta kuin haluat.
Kokeile itse… Lataa esimerkkitiedosto
Yksi kaava kaiken lajittelemiseksi (ilman apusarakkeita)
Jos pystyt käsittelemään äärimmäisiä kaavoja, tässä on all-in-one-kaava, joka lajittelee tiedot aakkosjärjestykseen (ilman apuohjelma-saraketta).
Tässä on kaava:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (PIENI (EI ($ A $ 2: $ A $ 9 = ""))*JOS (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUMMA (-ISNUMBER ($ A $ 2: $ A $ 9))), RIVIT ($ A $ 2: A2)+SUMMA (-ISBLANK ($ A $ 2: $ A $ 9)))), EI ($ A $ 2: $ A $ 9 = "")*JOS (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUMMA (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Kirjoita tämä kaava soluun ja vedä se alas saadaksesi lajiteltu luettelo. Lisäksi, koska tämä on taulukkokaava, käytä Control + Vaihto + Enter Enterin sijaan.
Tällä kaavalla on todellista hyötyä. Mitä mieltä sinä olet? Haluaisin oppia sinulta. Jätä jalanjälkesi kommenttikenttään!
