Exceli sisseehitatud andmete sortimine on hämmastav, kuid see pole dünaamiline. Kui sorteerite andmeid ja seejärel lisate neile andmeid, peate need uuesti sorteerima.
Sorteeri andmed tähestikulises järjekorras
Selles postituses näitan teile erinevaid viise andmete sortimiseks tähestikulises järjekorras valemite abil. See tähendab, et saate andmeid lisada ja see sorteerib need teie jaoks automaatselt.
Kui andmed on kõik tekstid, millel pole duplikaate
Oletame, et teil on allpool näidatud andmed:
Selles näites on kõik andmed tekstivormingus (ei numbreid, tühikuid ega duplikaate). Selle sortimiseks kasutan abistajaveergu. Andmete kõrval olevas veerus kasutage järgmist COUNTIF valemit:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
See valem võrdleb tekstiväärtust kõigi teiste tekstiväärtustega ja tagastab selle suhtelise asetuse. Näiteks lahtris B2 tagastab see 8, kuna seal on 8 teksti väärtust, mis on väiksemad või võrdsed tekstiga „USA” (tähestikulises järjekorras).
Nüüd väärtuste sortimiseks kasutage järgmist funktsioonide INDEX, MATCH ja ROWS kombinatsiooni:
= INDEX ($ A $ 2: $ A $ 9, MATCH (RIDA ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
See valem lihtsalt ekstraheerib nimed tähestikulises järjekorras. Esimeses lahtris (C2) otsitakse selle riigi nime, millel on madalaim arv (Austraalias on 1). Teises lahtris tagastab see Kanada (mille number on 2) ja nii edasi …
Abi veergude suhtes allergiline ??
Siin on valem, mis teeb sama ilma abistajaveerguta.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
See on massiivivalem, seega kasutage Ctrl + Tõstuklahv + Enter sisestusklahvi asemel.
Jätan selle teile dekodeerimiseks.
Proovige ise… Laadige alla näidisfail
See valem töötab hästi, kui teil on teksti- või tähtnumbrilisi väärtusi.
Kuid see ebaõnnestub, kui:
- Teil on andmetes duplikaate (proovige sisestada USA kaks korda).
- Andmetes on tühjad kohad.
- Teil on mitu numbrit ja teksti (proovige panna ühte lahtrisse 123).
Kui andmed on numbrite, teksti, duplikaatide ja tühikute segu
Nüüd on see natuke keeruline. Ma kasutan 4 abistajaveergu, et näidata teile, kuidas see töötab (ja siis annan teile tohutu valemi, mis teeb seda ilma abistajate veergudeta). Oletame, et teil on järgmised andmed:
Näete, et on topeltväärtusi, tühje ja numbreid. Nii et ma kasutan kõigi nende probleemide lahendamiseks abiveerge.
Abistajaveerg 1
Sisestage abistaja 1. veergu järgmine COUNTIF -valem
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
See valem teeb järgmist:
- See tagastab tühikute jaoks 0.
- Duplikaatide puhul tagastab sama numbri.
- Teksti ja numbreid töödeldakse paralleelselt ning see valem tagastab teksti ja arvu jaoks sama arvu (näiteks 123 ja India saavad mõlemad 1).
Abistav veerg 2
Sisestage abistaja veergu järgmine IS -funktsioon:
=-ISNUMBER (A2)
Abistav veerg 3
Sisestage abivahendi veergu 3 järgmine valem:
=-ISBLANK (A2)
Abistajaveerg 4
Sisestage abivahendi veergu 4 järgmine valem
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Selle valemi idee on eraldada tühikud, numbrid ja tekstiväärtused.
- Kui lahter on tühi, tagastab see väärtuse lahtris B2 (mis oleks alati 0) ja lisab väärtuse lahtrisse D10. Lühidalt, see tagastab andmete tühjade lahtrite koguarvu
- Kui lahter on arvväärtus, tagastab see võrdleva auastme ja lisab tühikute koguarvu. Näiteks 123 korral tagastab see 2 (1 on andmetes 123 ja 1 tühi lahter)
- Kui see on tekst, tagastab see võrdleva auastme ning lisab arvväärtuste ja tühikute koguarvu. Näiteks India jaoks lisab see teksti võrdleva asetuse tekstis (mis on 1) ning lisab tühjade lahtrite arvu ja arvväärtuste arvu.
Lõpptulemus - sorteeritud andmed
Nüüd kasutame sorteeritud loendi saamiseks neid abiveerge. Siin on valem:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
See sortimismeetod muutub nüüd lollikindlaks. Olen näidanud teile meetodit 8 üksuse jaoks, kuid saate seda laiendada nii mitmele üksusele kui soovite.
Proovige ise… Laadige alla näidisfail
Üks valem kõikide sortimiseks (ilma abiveergudeta)
Kui saate hakkama ekstreemsete valemitega, siis siin on kõik-ühes valem, mis sorteerib andmed tähestikulises järjekorras (ilma abistajate veerguta).
Siin on valem:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL (NOT ($ A $ 2: $ A $ 9 = ""))*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), RIDA ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), EI ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Sisestage see valem lahtrisse ja lohistage see alla, et saada sorteeritud loend. Kuna see on massiivivalem, kasutage ka Ctrl + Tõstuklahv + Enter sisestusklahvi asemel.
Sellel valemil on reaalne kasulikkus. Mida sa arvad? Mulle meeldiks sinult õppida. Jäta oma jalajäljed kommentaaride sektsiooni!
