See juhend näitab teile, kuidas Excelis tähestiku järjestamist, kasutades funktsiooni Sorteerimine ja filtreerimine, ning valemeid andmete järjestamiseks ja vormindamiseks.
Microsoft Excel on tõhus tarkvara oluliste andmete (nt rahaasjad, müügiandmed, klienditeave jne) korraldamiseks. Töölehtedel olevate andmete tähestikuline järjestamine on üks parimaid viise nende andmete kiireks korraldamiseks, neile juurde pääsemiseks ja viitamiseks. Lahtrite vahemiku tähestikuline järjestamine tähendab vahemiku sorteerimist tähestikulises järjekorras Excelis. Andmeid saab sortida mõlemal viisil, kas kasvavas või kahanevas järjekorras.
Oletame näiteks, et teil on suur hulk kliendi tellimusteavet ja soovite leida konkreetse kliendi tellimuste ajalugu. Kogu nimekirja läbi kammimine muutub üsna tülikaks. Selle asemel, et raisata aega sirvimisele, saate andmete kiireks leidmiseks oma veerge tähestiku järgi järjestada.
Andmete tähestikuliseks järjestamiseks Excelis saate kasutada erinevaid viise, olenevalt andmestiku tüübist, millega töötate. Excel võimaldab teil sortida ühte veergu või ühte rida või valitud vahemikku või tervet töölehte, aga ka valikuid mitme veeru või rea rühmitamiseks nende sortimise ajal. Õppime, kuidas Exceli arvutustabelites andmeid ridade või veergude kaupa tähestiku järgi järjestada.
Miks koostada Excelis andmeid tähestiku järgi?
Andmete tähestikulisel järjestamisel on mitmeid eeliseid:
- See muudab andmed mõistlikumaks ja hõlbustab nende lugemist.
- See hõlbustab kasutajal konkreetse väärtuse või kliendinime otsimist Exceli andmelehelt.
- See aitab kasutajal dubleerivaid kirjeid visuaalselt tuvastada, et saaksime vältida andmesisestuse vigu.
- See võimaldab kasutajatel hõlpsasti rühmitada veerge või loendeid, et kasutajad saaksid neid kõrvuti vaadata.
Tähestiku järjestamine (sortimine tähestiku järjekorras) on lihtne ja levinud viis teabe kiireks sortimiseks. See on eriti kasulik väga suurte andmekogumite puhul. Kokku on Excelis andmete tähestikuliseks järjestamiseks neli võimalust: nupp A–Z või Z–A, sortimisfunktsioon, filtrifunktsioon ja valemid.
Veeru tähestikuline järjestamine Excelis
Kiireim viis tähestikulises järjekorras sortimiseks Excelis on kasutada sisseehitatud sortimisfunktsiooni.
Esmalt avage andmestikku sisaldav arvutustabel. Valige lahtrid või veerg, mida soovite sortida, ja ärge valige tühje lahtreid. Seejärel minge vahekaardile "Andmed" ja klõpsake kasvavalt sortimiseks "A-Z" või rühmas Sorteeri ja filtreerige sortimiseks kahanevalt "Z-A".
Kui nende vahel on tühje lahtreid, eeldab Excel, et kõik andmed on sorteeritud, ja peatub musta lahtri kohal. Kui soovite sortida kogu veergu, valige veerus suvaline lahter ja kasutage ülaltoodud valikut.
Samadele valikutele pääseb juurde ka vahekaardi „Kodu“ jaotises Redigeerimine tööriista „Sortimine ja filtreerimine“ alt.
Kui klõpsate nupul, koostage Excel kohe oma loend tähestiku järgi.
Kuidas Excelis ridu sortida ja koos hoida
Veergu saate hõlpsalt sortida, kui teie töölehel on ainult üks veerg, kuid see muutub pisut keeruliseks, kui teie tööleht sisaldab mitut veergu selle veeru kõrval, mida soovite sortida. Mõnikord töötame ka arvutustabelitega, kus peame pärast veeru sortimist vastavad read puutumata.
Näiteks kui meil on õpilaste hinnete loendi tabel. Kui sorteerime õpilaste nimede veergu tähestikulises järjekorras, soovime, et ka nende kõrval olevatele ridadele sisestatud märgid liiguksid vastavalt.
Sellistel juhtudel saate ühe veeru tähestikulises järjekorras sortimiseks kasutada nuppu "A-Z" või "Z-A" ja see järjestab andmed automaatselt teistesse veergudesse, hoides ridu koos. Vaatame, kuidas see teise näitega töötab.
Esmalt valige esimene veerg, mille järgi soovite teisi veerge sortida. Seejärel valige andmete ühes veerus sortimiseks vahekaardil Andmed või Kodu vahekaardil kas "A-Z" või "Z-A" ja Excel korraldab teised veerud automaatselt vastavalt ümber.
Kui klõpsate sortimisnupul, ilmub dialoogiaken Sorteerimishoiatus. Valige raadionupp "Laienda valikut" ja klõpsake nuppu "Sorteeri".
See hoiab read koos veergude sortimise ajal, ilma et need ühtiksid (nagu allpool näidatud).
Kui valite suvandi „Jätka praeguse valikuga”, sorteerib Excel ainult valitud veeru.
Kuidas sorteerida tähestikuliselt mitme veeru järgi
Kui soovite andmestiku sortida tähestikulises järjekorras rohkem kui ühe veeru järgi, saate seda teha Exceli sortimisfunktsiooniga.
Näiteks tahame järgmise tabeli järjestada tähestikulises järjekorras esmalt riigi ja seejärel eesnime järgi:
Esmalt valige kogu tabel, mida soovite tähestiku järgi sortida, seejärel minge vahekaardile „Andmed“ ja klõpsake rühmas „Sortimine ja filtreerimine“ nuppu „Sorteeri“.
Või klõpsake vahekaardi „Kodu“ jaotises Redigeerimine ikooni „Sortimine ja filtreerimine“ ja valige sama funktsiooni avamiseks „Kohandatud sortimine“.
Ilmub dialoogiboks Sordi ja näitab esimest veergu (parameetrit), milles tabel sorteeritakse. Valige rippmenüükastist „Sortimise alus” esmane veerg, mille järgi soovite andmed kõigepealt tähestiku järgi järjestada, meie puhul „Riik” ja valige rippmenüüst „Tellimus” kas „A kuni Z” või „Z kuni A”. .
Seejärel klõpsake teise sortimistaseme lisamiseks nuppu "Lisa tase". Valige teine veerg (meie puhul Eesnimi), mille järgi soovite kastis "Sorteeri" olevad andmed tähestikulises järjestuses ja valige kas "A kuni Z" või "Z kuni A". Kui teie tabeli ülaosas on päised, märkige ruut Minu andmetel on päised, et see võib päised nende sortimisel vahele jätta. Vajadusel lisage rohkem sortimistasemeid ja klõpsake nuppu OK.
Tabel on järjestatud tähestikulises järjekorras: esmalt riigi ja seejärel eesnime järgi, nagu allpool näidatud.
Kuidas sordida Excelis ridu tähestikulises järjekorras
Mõnikord võib tekkida vajadus määrata Excelis veergude asemel read tähestikuliseks. Seda saab teha Exceli sortimisfunktsiooni abil. Oletame näiteks, et teil on arvutustabel, kus esimese rea veerg B kuni T sisaldab linnanimesid, kus eri jaemüügitüüpide all saadaolevate kaupluste arv ja veerg A sisaldab jaemüüja tüüpi. Lahtreid kasutatakse selleks, et jälgida, mitu kauplust igas linnas on erinevates jaemüüjate kategooriates.
Esmalt valige kõik read, mida soovite tähestiku järgi järjestada; Kui te ei soovi ridade silte teisaldada, jätke need oma valikust välja. Nüüd liikuge vahekaardile „Andmed” ja klõpsake rühmas Sorteeri ja filtreerige nuppu „Sordi”.
Dialoogiboksis Sordi klõpsake nuppu "Valikud".
Ilmub veel üks väike dialoogiboks, mis annab teile võimaluse valida "Sortida vasakult paremale" või "Sortida ülalt alla". Valige suvand „Sorteeri vasakult paremale”, et järjestada tähestiku ridade järgi, ja klõpsake nuppu „OK”, et naasta dialoogiboksi Sortimine.
Nüüd valige ripploendist Sorteeri (selle näite rida 1) see rida, mille soovite tähestiku järgi järjestada. Teisel väljal jätke lahtri väärtused väljale Sorteeri ja valige kastis Järjestus A kuni Z (kasvav järjekord). Seejärel klõpsake dialoogiboksi sulgemiseks nuppu "OK".
Nüüd on meie tabeli esimene rida järjestatud tähestikulises (kasvavas) järjekorras ja ülejäänud read järjestatakse vastavalt allpool näidatud viisil.
Kuidas veergu filtri abil tähestikulises järjekorras sortida
Teine kiire viis Excelis tähestikulises järjekorras sortimiseks on kasutada filtrifunktsiooni. Kui olete veergudele filtrid rakendanud, pääsete kõigi veergude sortimisvalikutele juurde vaid ühe hiireklõpsuga.
Veergudele filtri lisamiseks valige üks või mitu veergu ja klõpsake vahekaardi Andmed rühmas Sorteeri ja filtreerige valikut Filter. Kui soovite rakendada filtrit kogu tabelile, valige tabelis suvaline lahter ja klõpsake suvandit „Filter”.
Suvandile Filter pääsete juurde ka siis, kui klõpsate vahekaardi Avaleht jaotises Redigeerimine tööriistal "Sortimine ja filtreerimine" ja valite "Filter".
Igas veerupäises kuvatakse väike rippmenüü nool. Klõpsake mis tahes veeru noolel, mida soovite tähestikulises järjekorras sortida, ja valige „Sordi A kuni Z” või „Sordi Z kuni A”.
See korraldab soovitud veeru teie valitud tähestikulises järjekorras ja kui valisite „Sorteeri A kuni Z”, ilmub filtri nupule väike ülespoole suunatud nool, mis näitab sortimise järjekorda (kasvav):
Täpsem sorteerimine kohandatud järjekorras
Tähestiku järgi sortimine pole ideaalne kõigi tähestikuliste andmete jaoks. Mõnikord võidakse andmeid järjestada tähestiku järgi, kuid see pole kõige tõhusam viis nende sortimiseks.
Kujutagem ette olukorda, kus teil võib olla kuude või nädalapäevade nimesid sisaldav loend, tähestikulises järjekorras sorteerimine pole selles olukorras kasulik. Mõistlikum on seda loendit kronoloogiliselt sortida. Kui sorteerite A-st Z-ni, järjestab kuud tähestikulises järjekorras, esimene on aprill, seejärel august, veebruar, juuni jne. Aga see pole see, mida sa tahad. Õnneks on väga lihtne Excelis kronoloogiliselt järjestada, kasutades valikut Advanced Custom Sort.
Alustuseks valige tabel, mida soovite sortida. Seejärel valige vahekaardi Avaleht jaotises Redigeerimine jaotises "Sortimine ja filtreerimine" "Kohandatud sortimine".
Valige dialoogiboksis Sorteeri veergude jaotisest veerg, mis sisaldab aasta kuud, sortige nagu tavaliselt väärtused Väärtused ja jaotises Tellimus valige "Kohandatud loend".
Dialoogiaknas Kohandatud loendid saate luua oma loendi. On mõned kohandatud vaikeloendid, sealhulgas nädalapäevad, lühendatud kuud, aasta kuud jne. Valige oma vajadustele vastav sortimisvalik (meie puhul jaanuar, veebruar, ..) ja klõpsake nuppu OK. Seejärel klõpsake loendi kronoloogiliseks järjestamiseks uuesti nuppu OK.
Märkate, et olete loendi kuujärjekorra järgi edukalt sorteerinud.
Kuidas sortida Excelis tähestiku järjekorras Exceli valemite abil
Kui olete valemite fänn, saate loendi tähestikuliseks järjestamiseks kasutada Exceli valemeid. Andmete tähestiku muutmiseks saab kasutada kahte valemit: COUNTIF ja VLOOKUP.
Näiteks on meil loend nimedest, mida tahame valemi abil tähestikuliseks muuta.
Selle sortimiseks lisame olemasolevasse tabelisse ajutise veeru nimega "Sortimisjärjekord".
Kasutage andmete kõrval asuvas lahtris (A2) järgmist COUNTIF valemit:
=COUNTIF($B$2:$B$20,"<="&B2) Ülaltoodud valem võrdleb lahtris B2 olevat tekstiväärtust kõigi teiste tekstiväärtustega (B3:B20) ja tagastab selle suhtelise järgu. Näiteks lahtris B2 tagastab see 11, kuna seal on 11 tekstiväärtust, mis on tähestikulises järjekorras tekstist Nancy väiksemad või sellega võrdsed. Tulemuseks on töötaja nime järjestus lahtris B2.
Seejärel kasutage täitmiskäepidet, lohistage see valem, et täita see kogu vahemikus. See annab loendis kõigi nimede sortimisjärjestuse.
Nüüd peame ülaltoodud ekraanipildil olevad andmed järjestama sorteerimisjärjekorra numbri alusel ja selleks kasutame funktsiooni VLOOKUP.
Süntaks:
=VLOOKUP(,A:B,2,0)Siin tähistab sortimisarv numbreid kasvavas järjekorras vahemikus 1–20. Kahanevas järjekorras peaksid numbrid olema vahemikus 20–1.
Siin on sorteeritud nimede jaoks veerg D. Lahtri D2 jaoks sisestage järgmine VLOOKUP valem:
=VLOOKUP(1;A:B;2;0)Samamoodi peate teise ja kolmanda lahtri jaoks kasutama valemit järgmiselt:
=VLOOKUP(2;A:B;2;0) ja =VLOOKUP(3;A:B;2;0) ja nii edasi…
Pärast selle VLOOKUP-i valemi sisestamist igasse andmete kõrval asuvasse lahtrisse muudetakse loend tähestikuliseks.
Selle asemel, et ülaltoodud valemit (1-20) igasse lahtrisse käsitsi sisestada, saate oma töö hõlbustamiseks kasutada ka reafunktsiooni. Funktsioon Rida () tagastab praeguse lahtri rea numbri. Seega on reafunktsiooni abil valem järgmine:
=VLOOKUP(RIDA()-1,A:B,2,0)See valem annab teile sama tulemuse kui ülaltoodud valem.
Seejärel lohistage see valem täitmiskäepidemega, et täita see kogu vahemikus.
Kirjete tähestikuline järjestamine perekonnanime järgi
Aeg-ajalt peame sageli perekonnanimedega andmelehti tähestikulises järjekorras sorteerima. Isegi kui nimed algavad eesnimedega, peate sellistel juhtudel järjestama need perekonnanime järgi tähestikulises järjekorras. Seda saate teha Exceli teksti valemitega.
Selleks peate esmalt eraldama täisnimedest ees- ja perekonnanime kahte erinevasse veergu. Seejärel muutke nimed ümber, sorteerige need ja seejärel pöörake need tagasi algsele kujule.
Kui A2-s on täisnimi, sisestage järgmised valemid kahte erinevasse lahtrisse (B2 ja C2) ja seejärel kopeerige valemid mööda veerge allapoole (täitekäepideme abil) kuni viimase andmetega lahtrini:
Eesnime eraldamiseks sisestage lahtrisse C2 järgmine valem:
=VAsak(A2,OTSING(” “,A2)-1)
Perekonnanime eraldamiseks sisestage lahtrisse D2 järgmine valem:
=PAREM(A2,PUU(A2)-OTSING(" ",A2,1))
Seejärel ühendati lahtris E2 ees- ja perekonnanimi vastupidises järjekorras, eraldades need komaga:
=D2&", "&C2
Oleme ees- ja perekonnanimed eraldanud ja ümber pööranud. Nüüd peame need tähestiku järgi järjestama ja tagasi pöörama. Nagu ülalt näha, sisaldavad veerud C, D ja E valemiga nimede eraldamisel tegelikult valemeid, kuid need on vormindatud nii, et need näevad välja nagu väärtused. Seega peame valemi väärtusteks teisendama.
Selleks valige esmalt kõik valemilahtrid (E1:E31) ja vajutage Ctrl + C neid kopeerida. Paremklõpsake valitud lahtreid, klõpsake jaotises "Kleebi suvandid" ikooni "Väärtused" ja vajutage sisestusklahvi.
Seejärel valige saadud veerus suvaline lahter ja klõpsake vastavalt oma vajadustele vahekaardil Andmed või Kodu suvandit A kuni Z või Z kuni A.
Kui klõpsate nupul, kuvatakse dialoogiboks Sorteerimishoiatus. Valige suvand "Laienda valikut" ja klõpsake nuppu "Sorteeri".
Nagu näete, on kogu veerg perekonnanime tähestiku all.
Nüüd, kui soovite selle tagasi pöörata esialgsele eesnime perekonnanime vormingule, peate lihtsalt sisestama samad valemid, kuid erinevate lahtriviitetega.
Peate eraldama nime (E2) uuesti kaheks osaks, kasutades järgmisi valemeid:
G2-s eraldage eesnimi:
=PAREM(E2,PIIK(E2)-OTSING(" ",E2))
G2-s tõmmake perekonnanimi:
=VAsak(E2,OTSING(" ",E2)-2)
Ja ühendage kaks osa kokku, et saada algne täisnimi:
=G2&" "&H2
Iga kord, kui sisestate valemi esimesse lahtrisse, kopeerige valem veerus alla (kasutades täitekäepidet) kuni viimase andmetega lahtrini.
Nüüd pole vaja muud teha, kui teisendada valemid veel kord väärtuste teisendamiseks, nagu me eespool tegime, ja oletegi valmis. see tehtud ja.