Ta priročnik vam bo pokazal, kako v Excelu razvrstite po abecednem redu s funkcijo Razvrsti in filtriraj ter formule za razporeditev in oblikovanje podatkov po vrstnem redu.
Microsoft Excel je učinkovita programska oprema za organiziranje pomembnih podatkov, kot so finance, podatki o prodaji, podatki o strankah itd. Abecedno razvrščanje podatkov na delovnih listih je eden najboljših načinov za hitro organiziranje, dostop do teh podatkov in sklicevanje nanje. Abecedno razvrščanje obsega celic pomeni razvrščanje obsega po abecedi v excelu. Podatke je mogoče razvrstiti na oba načina, v naraščajočem ali padajočem vrstnem redu.
Recimo, da imate na voljo velik nabor podatkov o strankinih naročilih in želite poiskati zgodovino naročil določene stranke. Prečesanje celotnega seznama bo postalo precej naporno. Namesto da izgubljate čas s pomikanjem, lahko svoje stolpce razvrstite po abecedi, da hitro najdete svoje podatke.
Za abecedno razvrstitev podatkov v Excelu lahko uporabite različne načine, odvisno od vrste nabora podatkov, s katerim delate. Excel vam omogoča razvrščanje enega stolpca ali ene vrstice ali izbranega obsega ali celotnega delovnega lista, pa tudi možnosti za združevanje več stolpcev ali vrstic, medtem ko jih razvrščate. Naučimo se, kako podatke v Excelovih preglednicah razvrstiti po abecednem redu po vrsticah ali stolpcih.
Zakaj podatke v Excelu razvrstiti po abecedi
Abecedno razvrščanje podatkov bo imelo številne prednosti:
- Podatke naredi bolj smiselne in jih olajša branje.
- Uporabniku olajša iskanje določene vrednosti ali imena stranke v podatkovnem listu Excel.
- Uporabniku pomaga vizualno prepoznati podvojene zapise, tako da lahko preprečimo napake pri vnosu podatkov.
- Uporabnikom omogoča enostavno združevanje stolpcev ali seznamov, tako da si jih lahko uporabniki ogledajo drug ob drugem.
Abecedno razvrščanje (razvrščanje po abecedi) je preprost in običajen način za hitro razvrščanje podatkov. Še posebej je uporaben za zelo velike nabore podatkov. Skupno obstajajo 4 načini za abecedno razvrstitev podatkov v Excelu: gumb A-Z ali Z-A, funkcija razvrščanja, funkcija filtra in formule.
Abecedno razvrščanje stolpca v Excelu
Najhitrejši način za razvrščanje po abecedi v Excelu je uporaba vgrajene funkcije razvrščanja.
Najprej odprite preglednico, ki vsebuje nabor podatkov. Izberite celice ali stolpec, ki jih želite razvrstiti, in se prepričajte, da ne izberete praznih celic. Nato pojdite na zavihek »Podatki« in kliknite »A-Z«, da razvrstite naraščajoče, ali »Z-A«, da razvrstite padajoče v skupini Razvrsti in filtriraj.
Če so vmes prazne celice, Excel predvideva, da so bili vsi podatki razvrščeni, in se ustavi nad črno celico. Če želite razvrstiti celoten stolpec, samo izberite katero koli celico v stolpcu in uporabite zgornjo možnost.
Do istih možnosti lahko dostopate tudi pod orodjem »Razvrsti in filtriraj« v razdelku Urejanje na zavihku »Domov«.
Ko kliknete gumb, takoj razvrstite svoj seznam po abecedi v Excelu.
Kako razvrstiti in obdržati vrstice skupaj v Excelu
Stolpec lahko preprosto razvrstite, če imate na delovnem listu samo en stolpec, vendar postane nekoliko zapleteno, če vaš delovni list vsebuje več stolpcev poleg stolpca, ki ga želite razvrstiti. Včasih delamo tudi na preglednicah, kjer moramo po razvrščanju stolpca ohraniti ustrezne vrstice nedotaknjene.
Na primer, če imamo preglednico s seznamom ocen študentov. Ko razvrstimo stolpec z imeni študentov po abecednem vrstnem redu, želimo tudi, da se ustrezno premikajo tudi ocene, vpisane v vrstice poleg njih.
V takih primerih lahko uporabite gumb 'A-Z' ali 'Z-A', da razvrstite enega od stolpcev po abecednem vrstnem redu in samodejno bo razporedil podatke v druge stolpce, pri čemer bodo vrstice ostale skupaj. Poglejmo, kako to deluje z drugim primerom.
Najprej izberite prvi stolpec, po katerem želite razvrstiti druge stolpce. Nato izberite 'A-Z' ali 'Z-A' pod zavihkom Podatki ali zavihkom Domov, da razvrstite podatke v enem stolpcu, Excel pa bo ustrezno prerazporedil druge stolpce.
Ko kliknete gumb za razvrščanje, se prikaže pogovorno okno Opozorilo o razvrščanju. Izberite izbirni gumb za »Razširi izbor« in kliknite gumb »Razvrsti«.
To bo ohranilo vrstice skupaj med razvrščanjem stolpcev brez neusklajenih podatkov (kot je prikazano spodaj).
Če izberete možnost »Nadaljuj s trenutno izbiro«, bo Excel razvrstil samo izbrani stolpec.
Kako razvrstiti po abecedi po več stolpcih
Če želite nabor podatkov razvrstiti po abecedi po več kot enem stolpcu, lahko to storite z Excelovo funkcijo za razvrščanje.
Naslednjo tabelo želimo na primer urediti po abecedi najprej po državi in nato po imenu:
Najprej izberite celotno tabelo, ki jo želite razvrstiti po abecedi, nato pojdite na zavihek »Podatki« in v skupini Razvrsti in filtriraj kliknite »Razvrsti«.
Ali pa kliknite ikono »Razvrsti in filtriraj« v razdelku Urejanje na zavihku »Domov« in izberite »Razvrsti po meri« za dostop do iste funkcije.
Prikaže se pogovorno okno Razvrsti in prikaže prvi stolpec (parameter), v katerem bo razvrščena tabela. V spustnem polju »Razvrsti po« izberite primarni stolpec, po katerem želite najprej razvrstiti podatke po abecedi, v našem primeru »Država« in v spustnem meniju »Vrst« izberite od »A do Ž« ali »Od do A« .
Nato kliknite gumb »Dodaj raven«, da dodate drugo raven razvrščanja. Izberite drugi stolpec (v našem primeru ime), po katerem želite podatke razvrstiti po abecedi v polju »Razvrsti po« in izberite bodisi »A do Z« ali »Z do A«. Če ima vaša tabela glave na vrhu, potrdite potrditveno polje »Moji podatki imajo glave«, da lahko med razvrščanjem preskoči glave. Po potrebi dodajte več ravni razvrščanja in kliknite »V redu«.
Tabela je urejena po abecedi: najprej po državi, nato pa po imenu, kot je prikazano spodaj.
Kako razvrstiti vrstice po abecedi v Excelu
Včasih boste morda želeli razvrstiti vrstice v Excelu po abecedi in ne stolpcev. To lahko storite z uporabo Excelove funkcije razvrščanja. Recimo, da imate preglednico, kjer stolpec od B do T prve vrstice vsebuje imena mest, kjer je število trgovin, ki so na voljo pod različnimi vrstami maloprodaje, in stolpec A vsebuje vrsto prodajalca. Celice se uporabljajo za spremljanje števila trgovin v posameznem mestu v različnih kategorijah trgovcev na drobno.
Najprej izberite vse vrstice, ki jih želite razvrstiti po abecedi; Če ne želite premakniti oznak vrstic, jih izpustite iz izbire. Zdaj se pomaknite do zavihka »Podatki« in kliknite »Razvrsti« v skupini Razvrsti in filtriraj.
V pogovornem oknu Razvrsti kliknite gumb »Možnosti«.
Prikaže se še eno majhno pogovorno okno, v katerem lahko izberete »Razvrsti od leve proti desni« ali »Razvrsti od zgoraj navzdol«. Izberite možnost »Razvrsti od leve proti desni«, da namesto tega razvrstite po vrstici, in kliknite »V redu«, da se vrnete v pogovorno okno Razvrsti.
Zdaj na spustnem seznamu »Razvrsti po« izberite vrstico, ki jo želite razvrstiti po abecedi (vrstica 1 v tem primeru). V drugem polju obdržite vrednosti celic v polju »Razvrsti« in izberite »A do Z« (naraščajoče) v polju Vrstni red. Nato kliknite »V redu«, da zaprete pogovorno okno.
Zdaj je prva vrstica v naši tabeli razvrščena po abecednem (naraščajočem) vrstnem redu, preostale vrstice pa so ustrezno preurejene, kot je prikazano spodaj.
Kako razvrstiti stolpec po abecedi z uporabo filtra
Drug hiter način za razvrščanje po abecedi v Excelu je uporaba funkcije Filter. Ko uporabite filtre za stolpce, lahko do možnosti razvrščanja za vse stolpce dostopate z enim klikom miške.
Če želite stolpcem dodati filter, izberite enega ali več stolpcev in kliknite možnost »Filter« v skupini Razvrsti in filtriraj na zavihku »Podatki«. Če želite uporabiti filter za celotno tabelo, izberite katero koli celico v tabeli in kliknite možnost »Filter«.
Do možnosti Filter lahko dostopate tudi tako, da kliknete orodje »Razvrsti in filtriraj« v skupini Urejanje na zavihku »Domov« in izberete »Filter«.
V vsakem od naslovov stolpcev se bo pojavila majhna spustna puščica. Kliknite puščico katerega koli stolpca, ki ga želite razvrstiti po abecedi, in izberite »Razvrsti od A do Z« ali »Razvrsti od Z do A«.
To bo razporedilo želeni stolpec po izbranem abecednem vrstnem redu, in če ste izbrali »Razvrsti od A do Z«, se bo na gumbu filtra pojavila majhna puščica navzgor, ki označuje vrstni red razvrščanja (naraščajoče):
Napredno razvrščanje po naročilu po meri
Razvrščanje po abecedi ni idealno za vse abecedne podatke. Včasih je mogoče podatke razvrstiti po abecedi, vendar to ni najučinkovitejši način za njihovo razvrščanje.
Predstavljajmo si situacijo, ko imate morda seznam z imeni mesecev ali delovnih dni, razvrščanje po abecedi v tej situaciji ni uporabno. Bolj smiselno je ta seznam razvrstiti kronološko. Če razvrstite od A do Ž, bo mesece razvrstil po abecednem vrstnem redu, najprej bo april, nato avgust, februar, junij itd. Ampak to ni tisto, kar si želiš. Na srečo je v Excelu zelo enostavno kronološko urediti z možnostjo Napredno razvrščanje po meri.
Začnite z izbiro tabele, ki jo želite razvrstiti. Nato izberite »Razvrsti po meri« pod »Razvrsti in filtriraj« v razdelku Urejanje na zavihku Domov.
V pogovornem oknu Razvrsti v razdelku stolpcev izberite stolpec, ki vsebuje mesece v letu, kot običajno razvrstite po »Vrednosti« in v razdelku Vrstni red izberite »Seznam po meri«.
V pogovornem oknu Seznami po meri lahko ustvarite svoj seznam. Obstaja nekaj privzetih seznamov po meri, vključno z dnevi v tednih, skrajšanimi meseci, meseci v letu itd. Izberite ustrezno možnost razvrščanja glede na vaše potrebe (v našem primeru januar, februar, ..) in kliknite »V redu«. Nato znova kliknite »V redu«, da svoj seznam razvrstite kronološko.
Opazili boste, da ste seznam uspešno razvrstili po mesečnem vrstnem redu.
Kako razvrstiti po abecedi v Excelu z uporabo Excelovih formul
Če ste ljubitelj formul, lahko uporabite Excelove formule za razvrstitev seznama po abecedi. Formuli, ki ju lahko uporabite za abecedno razvrščanje podatkov, sta COUNTIF in VLOOKUP.
Na primer, imamo seznam imen, ki jih želimo razvrstiti po abecedi s formulo.
Če želite to razvrstiti, bomo v obstoječo tabelo dodali začasen stolpec z imenom »Red razvrščanja«.
V celici (A2) poleg podatkov uporabite naslednjo formulo COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Zgornja formula primerja besedilno vrednost v celici B2 z vsemi drugimi besedilnimi vrednostmi (B3:B20) in vrne njen relativni rang. Na primer, v celici B2 vrne 11, saj je 11 besedilnih vrednosti, ki so po abecednem vrstnem redu nižje ali enake besedilu »Nancy«. Rezultat je vrstni red razvrščanja imena zaposlenega v celici B2.
Nato uporabite ročico za polnjenje povlecite to formulo, da jo izpolnite v celotnem obsegu. To daje vrstni red razvrščanja vsakega od imen na seznamu.
Zdaj moramo razporediti podatke, prikazane na zgornjem posnetku zaslona, na podlagi številke vrstnega reda razvrščanja, za to pa bomo uporabili funkcijo VLOOKUP.
Sintaksa:
=VLOOKUP(,A:B,2,0)Tukaj 'razvrstitvena številka' predstavlja številke v naraščajočem vrstnem redu od 1 do 20. Za padajoče naj bodo številke od 20 do 1.
Tukaj imamo stolpec D za razvrščena imena. Za celico D2 vnesite naslednjo formulo VLOOKUP:
=VPOISK(1,A:B,2,0)Podobno morate za drugo in tretjo celico uporabiti formulo kot:
=VPOISK(2,A:B,2,0) in =VPOISK(3,A:B,2,0) in tako naprej…
Ko vnesete to formulo VLOOKUP v vsako celico poleg podatkov, se seznam razvrsti po abecedi.
Namesto da ročno vnesete zgoraj omenjeno formulo (1-20) v vsako celico, lahko uporabite tudi funkcijo vrstice, da si olajšate delo. Funkcija Row () vrne številko vrstice trenutne celice. Torej, s pomočjo funkcije vrstice bo formula:
=VISK(VRSTICA()-1,A:B,2,0)Ta formula vam bo dala enak rezultat kot zgornja formula.
Nato uporabite ročico za polnjenje, da povlecite to formulo, da jo izpolnite v celotnem obsegu.
Kako razvrstiti vnose po abecedi po priimku
Občasno moramo podatkovne liste s priimki pogosto razvrstiti po abecedi. V takih primerih, čeprav se imena začnejo z imeni, jih morate razvrstiti po abecedi po priimku. To lahko storite z besedilnimi formulami Excel.
Če želite to narediti, boste morali najprej izvleči ime in priimek iz polnih imen v dva različna stolpca. Nato obrnite imena, jih razvrstite in jih nato obrnite nazaj v prvotno obliko.
S polnim imenom v A2 vnesite naslednje formule v dve različni celici (B2 in C2) in nato kopirajte formule navzdol po stolpcih (z uporabo ročice za polnjenje) do zadnje celice s podatki:
Če želite izvleči ime, vnesite to formulo v celico C2:
=LEVO(A2,ISK.(” “,A2)-1)
Če želite izvleči priimek, vnesite to formulo v celico D2:
=DESNO(A2,LEN(A2)-ISKANJE(" ",A2,1))
Nato v celici E2 združi ime in priimek v obratnem vrstnem redu, ločeno z vejico:
=D2&", "&C2
Ime in priimek smo ločili in ju zamenjali. Zdaj jih moramo razvrstiti po abecedi in jih obrniti nazaj. Kot lahko vidite zgoraj, ko izvlečemo imena s formulo, stolpci C, D in E dejansko vsebujejo formule, vendar so oblikovane tako, da so videti kot vrednosti. Torej moramo formulo pretvoriti v vrednosti.
Če želite to narediti, najprej izberite vse celice formule (E1:E31) in pritisnite Ctrl + C da jih kopiram. Z desno tipko miške kliknite izbrane celice, kliknite ikono »Vrednosti« pod »Možnosti lepljenja« in pritisnite tipko »Enter«.
Nato izberite katero koli celico v nastalem stolpcu in kliknite možnost »A do Ž« ali »Z do A« na zavihku »Podatki« ali »Domov« glede na vaše potrebe.
Ko kliknete gumb, se prikaže pogovorno okno Opozorilo o razvrščanju. Izberite možnost »Razširi izbor« in kliknite gumb »Razvrsti«.
Kot lahko vidite, je celoten stolpec razvrščen po priimku.
Zdaj, če ga želite obrniti nazaj v prvotni 'First Name Last Name format', morate vnesti iste formule, vendar z različnimi sklici na celice.
Ime (E2) morate ponovno ločiti na dva dela z uporabo spodnjih formul:
V G2 izvlecite ime:
=DESNO(E2,LEN(E2)-ISKANJE(" ",E2))
V G2 potegnite priimek:
=LEVO(E2,ISKANJE(" ",E2)-2)
In združite oba dela skupaj, da dobite izvirno polno ime:
=G2&" "&H2
Vsakič, ko vnesete formulo v prvo celico, se prepričajte, da ste formulo kopirali navzdol po stolpcu (z uporabo ročice za polnjenje) do zadnje celice s podatki.
Zdaj morate samo še enkrat pretvoriti formule v pretvorbo vrednosti, kot smo storili zgoraj, in končali ste. to storjeno in.