Šiame vadove bus parodyta, kaip suskirstyti abėcėlę programoje „Excel“, naudojant funkciją Rūšiuoti ir filtruoti, ir formules duomenims išdėstyti ir formatuoti.
„Microsoft Excel“ yra efektyvi programinė įranga, skirta svarbiems duomenims, pvz., finansams, pardavimo duomenims, klientų informacijai ir kt., tvarkyti. Duomenų abėcėlės tvarka darbalapiuose yra vienas geriausių būdų greitai tvarkyti, pasiekti ir nurodyti duomenis. Ląstelių diapazono rūšiavimas abėcėlės tvarka reiškia diapazono rūšiavimą abėcėlės tvarka programoje „Excel“. Duomenys gali būti rūšiuojami abiem būdais: didėjimo arba mažėjimo tvarka.
Pavyzdžiui, tarkime, kad turite didelį klientų užsakymų informacijos duomenų rinkinį ir norite rasti konkretaus kliento užsakymų istoriją. Viso sąrašo sušukavimas taps nemažu darbu. Užuot gaišę laiką slinkdami, galite suskirstyti stulpelius pagal abėcėlę, kad greitai rastumėte duomenis.
Atsižvelgiant į duomenų rinkinio tipą, su kuriuo dirbate, galite įvairiais būdais suskirstyti duomenis abėcėlės tvarka programoje „Excel“. „Excel“ leidžia rūšiuoti vieną stulpelį arba vieną eilutę, pasirinktą diapazoną arba visą darbalapį, taip pat parinktis, sugrupuoti kelis stulpelius ar eilutes kartu juos rūšiuojant. Sužinokime, kaip suskirstyti duomenis abėcėlės tvarka Excel skaičiuoklėse pagal eilutes arba stulpelius.
Kodėl „Excel“ duomenis reikia suskirstyti į abėcėlę
Duomenų išdėstymas abėcėlės tvarka turės daug privalumų:
- Dėl to duomenys tampa protingesni ir lengviau skaitomi.
- Tai leidžia vartotojui lengviau ieškoti konkrečios reikšmės arba kliento vardo „Excel“ duomenų lape.
- Tai padeda vartotojui vizualiai identifikuoti pasikartojančius įrašus, kad išvengtume duomenų įvedimo klaidų.
- Tai leidžia vartotojams lengvai sugrupuoti stulpelius ar sąrašus, kad vartotojai galėtų žiūrėti juos vienas šalia kito.
Abėcėlės tvarka (rūšiavimas pagal abėcėlę) yra paprastas, įprastas būdas greitai surūšiuoti informaciją. Tai ypač naudinga labai dideliems duomenų rinkiniams. Iš viso yra 4 būdai, kaip suskirstyti duomenis pagal abėcėlę: mygtukas A–Z arba Z–A, rūšiavimo funkcija, filtro funkcija ir formulės.
„Excel“ stulpelio abėcėlė
Greičiausias būdas „Excel“ rūšiuoti abėcėlės tvarka yra naudoti integruotą Rūšiavimo funkciją.
Pirmiausia atidarykite skaičiuoklę, kurioje yra duomenų rinkinys. Pasirinkite langelius ar stulpelius, kuriuos norite rūšiuoti, ir nepamirškite nepasižymėti tuščių langelių. Tada eikite į skirtuką „Duomenys“ ir spustelėkite „A-Z“, kad rūšiuotumėte didėjančia tvarka, arba „Z-A“, kad rūšiavimo ir filtravimo grupėje rūšiuotumėte mažėjančia tvarka.
Jei tarp jų yra tuščių langelių, „Excel“ daro prielaidą, kad visi duomenys buvo surūšiuoti, ir sustoja virš juodo langelio. Jei norite rūšiuoti visą stulpelį, tiesiog pasirinkite bet kurį stulpelio langelį ir naudokite aukščiau pateiktą parinktį.
Tas pačias parinktis taip pat galima pasiekti skirtuko „Pagrindinis“ skiltyje „Redagavimas“ esančiame įrankyje „Rūšiuoti ir filtruoti“.
Spustelėję mygtuką „Excel“ nedelsdami suskirstykite sąrašą pagal abėcėlę.
Kaip rūšiuoti ir laikyti eilutes kartu programoje „Excel“.
Jei darbalapyje yra tik vienas stulpelis, galite lengvai rūšiuoti stulpelį, tačiau tai tampa šiek tiek sudėtinga, kai darbalapyje yra keli stulpeliai šalia stulpelio, kurį norite rūšiuoti. Be to, kartais dirbame su skaičiuoklėmis, kuriose, surūšiavus stulpelį, reikia palikti nepažeistas atitinkamas eilutes.
Pavyzdžiui, jei turime mokinių pažymių sąrašo skaičiuoklę. Kai rūšiuojame mokinių vardų stulpelį abėcėlės tvarka, taip pat norime, kad šalia jų esančiose eilutėse įvesti ženklai taip pat atitinkamai pasislinktų.
Tokiais atvejais galite naudoti mygtuką „A-Z“ arba „Z-A“, kad surūšiuotumėte vieną iš stulpelių abėcėlės tvarka ir automatiškai sutvarkytumėte duomenis kituose stulpeliuose, išlaikant eilutes kartu. Pažiūrėkime, kaip tai veikia su kitu pavyzdžiu.
Pirmiausia pasirinkite pirmąjį stulpelį, pagal kurį norite rūšiuoti kitus stulpelius. Tada skirtuke „Duomenys“ arba „Pagrindinis“ pasirinkite „A-Z“ arba „Z-A“, kad rūšiuotumėte duomenis viename stulpelyje, o „Excel“ automatiškai pertvarkys kitus stulpelius.
Kai spustelėsite rūšiavimo mygtuką, pasirodys rūšiavimo įspėjimo dialogo langas. Pasirinkite akutę „Išplėsti pasirinkimą“ ir spustelėkite mygtuką „Rūšiuoti“.
Taip eilutės bus kartu rūšiuodami stulpelius ir nesutaps jokie duomenys (kaip parodyta toliau).
Jei pasirinksite parinktį „Tęsti dabartinį pasirinkimą“, „Excel“ rūšiuos tik pasirinktą stulpelį.
Kaip rūšiuoti abėcėlės tvarka pagal kelis stulpelius
Jei norite abėcėlės tvarka rūšiuoti duomenų rinkinį pagal daugiau nei vieną stulpelį, tai galite padaryti naudodami „Excel“ rūšiavimo funkciją.
Pavyzdžiui, mes norime šią lentelę išdėstyti abėcėlės tvarka pirmiausia pagal šalį, o tada pagal vardą:
Pirmiausia pasirinkite visą lentelę, kurią norite rūšiuoti pagal abėcėlę, tada eikite į skirtuką „Duomenys“ ir grupėje Rūšiuoti ir filtruoti spustelėkite „Rūšiuoti“.
Arba spustelėkite piktogramą „Rūšiuoti ir filtruoti“ skirtuko „Pagrindinis“ skiltyje „Redaguoti“ ir pasirinkite „Custom Sort“, kad pasiektumėte tą pačią funkciją.
Atsiras dialogo langas Rūšiuoti ir parodys pirmąjį stulpelį (parametrą), kuriame bus rūšiuojama lentelė. Išskleidžiamajame laukelyje „Rūšiuoti pagal“ pasirinkite pagrindinį stulpelį, pagal kurį pirmiausia norite suskirstyti duomenis abėcėlės tvarka, mūsų atveju „Šalis“, o išskleidžiamajame meniu „Užsakymas“ pasirinkite „A iki Z“ arba „Z į A“. .
Tada spustelėkite mygtuką „Pridėti lygį“, kad pridėtumėte antrą rūšiavimo lygį. Pasirinkite antrąjį stulpelį (mūsų atveju Vardas), pagal kurį norite suskirstyti duomenis abėcėlės tvarka laukelyje „Rūšiuoti pagal“ ir pasirinkite „A iki Z“ arba „Z iki A“. Jei lentelės viršuje yra antraštės, pažymėkite žymimąjį laukelį „Mano duomenys turi antraštes“, kad jas rūšiuojant būtų galima praleisti antraštes. Jei reikia, pridėkite daugiau rūšiavimo lygių ir spustelėkite „Gerai“.
Lentelė išdėstyta abėcėlės tvarka: pirmiausia pagal šalį, o paskui pagal vardą, kaip parodyta toliau.
Kaip rūšiuoti eilutes abėcėlės tvarka programoje „Excel“.
Kartais „Excel“ gali reikėti suskirstyti abėcėlę eilutes, o ne stulpelius. Tai galima padaryti naudojant „Excel“ rūšiavimo funkciją. Pavyzdžiui, tarkime, kad turite skaičiuoklę, kurioje pirmosios eilutės stulpeliuose nuo B iki T yra miestų pavadinimai, kuriuose yra įvairių mažmeninės prekybos tipų parduotuvių skaičius, o A stulpelyje – mažmenininko tipas. Ląstelės naudojamos norint sekti, kiek parduotuvių kiekviename mieste yra skirtingose mažmenininkų kategorijose.
Pirmiausia pasirinkite visas eilutes, kurias norite suskirstyti į abėcėlę; Jei nenorite perkelti eilučių etikečių, palikite jas iš pasirinkimo. Dabar eikite į skirtuką „Duomenys“ ir grupėje Rūšiuoti ir filtruoti spustelėkite „Rūšiuoti“.
Dialogo lange Rūšiuoti spustelėkite mygtuką „Parinktys“.
Atsiras kitas mažas dialogo langas, kuriame galėsite pasirinkti „Rūšiuoti iš kairės į dešinę“ arba „Rūšiuoti iš viršaus į apačią“. Pasirinkite parinktį „Rūšiuoti iš kairės į dešinę“, kad suskirstytumėte abėcėlę pagal eilutę ir spustelėkite „Gerai“, kad grįžtumėte į dialogo langą Rūšiuoti.
Dabar išskleidžiamajame sąraše „Rūšiuoti pagal“ pasirinkite eilutę, kurią norite surūšiuoti pagal abėcėlę (šio pavyzdžio 1 eilutė). Kitame lauke laukelyje „Rūšiuoti“ palikite langelių reikšmes ir užsakymo laukelyje pasirinkite „A iki Z“ (didėjimo tvarka). Tada spustelėkite „Gerai“, kad uždarytumėte dialogo langą.
Dabar pirmoji mūsų lentelės eilutė yra surūšiuota abėcėlės tvarka (didėjimo tvarka), o likusios eilutės atitinkamai pertvarkytos, kaip parodyta toliau.
Kaip rūšiuoti stulpelį abėcėlės tvarka naudojant filtrą
Kitas greitas būdas „Excel“ rūšiuoti abėcėlės tvarka yra naudoti filtro funkciją. Pritaikius filtrus stulpeliams, visų stulpelių rūšiavimo parinktis galima pasiekti vos vienu pelės paspaudimu.
Norėdami pridėti filtrą prie stulpelių, pasirinkite vieną ar daugiau stulpelių ir spustelėkite parinktį „Filtras“ skirtuko „Duomenys“ grupėje Rūšiuoti ir filtruoti. Jei norite taikyti filtrą visai lentelei, pasirinkite bet kurį lentelės langelį ir spustelėkite parinktį „Filtras“.
Taip pat galite pasiekti filtro parinktį spustelėję įrankį „Rūšiuoti ir filtruoti“ skirtuko „Pagrindinis“ redagavimo grupėje ir pasirinkę „Filtras“.
Kiekvienoje stulpelio antraštėje bus rodoma maža išskleidžiamoji rodyklė. Spustelėkite bet kurio stulpelio, kurį norite rūšiuoti abėcėlės tvarka, rodyklę ir pasirinkite „Rūšiuoti nuo A iki Z“ arba „Rūšiuoti nuo Z iki A“.
Taip norimas stulpelis bus išdėstytas jūsų pasirinkta abėcėlės tvarka, o jei pasirinkote „Rūšiuoti nuo A iki Z“, ant filtro mygtuko atsiras maža rodyklė aukštyn, nurodanti rūšiavimo tvarką (didėjimo tvarka):
Išplėstinis rūšiavimas pagal užsakymą
Rūšiavimas pagal abėcėlę nėra idealus visiems abėcėlės duomenims. Kartais duomenys gali būti išdėstyti abėcėlės tvarka, tačiau tai nėra pats efektyviausias būdas juos rūšiuoti.
Įsivaizduokime situaciją, kai galite turėti sąrašą su mėnesių ar savaitės dienų pavadinimais, rūšiavimas pagal abėcėlę šioje situacijoje nėra naudingas. Tikslingiau tą sąrašą rūšiuoti chronologiškai. Jei rūšiuosite nuo A iki Z, tai mėnesiai bus išdėstyti abėcėlės tvarka, pirmas bus balandis, tada rugpjūtis, vasaris, birželis ir t.t. Bet tai nėra tai, ko norite. Laimei, naudojant „Advanced Custom Sort“ parinktį „Excel“ labai lengva chronologiškai išdėstyti.
Pradėkite pasirinkdami lentelę, kurią norite rūšiuoti. Tada skirtuko Pagrindinis skiltyje „Rūšiuoti ir filtruoti“ pasirinkite „Tinkintas rūšiavimas“.
Dialogo lange Rūšiuoti pasirinkite stulpelį, kuriame yra metų mėnesiai, kaip įprasta, rūšiuokite pagal „Vertės“, o skiltyje „Užsakymas“ pasirinkite „Pasirinktinis sąrašas“.
Dialogo lange Pasirinktiniai sąrašai galite sukurti savo sąrašą. Yra keletas numatytųjų pasirinktinių sąrašų, įskaitant savaičių dienas, sutrumpintus mėnesius, metų mėnesius ir pan. Pasirinkite tinkamą rūšiavimo parinktį pagal savo poreikius (mūsų atveju sausio, vasario, ...) ir spustelėkite „Gerai“. Tada dar kartą spustelėkite „Gerai“, kad rūšiuotumėte sąrašą chronologiškai.
Pastebėsite, kad sėkmingai surūšiavote sąrašą pagal mėnesio tvarką.
Kaip rūšiuoti abėcėlės tvarka „Excel“ naudojant „Excel“ formules
Jei esate formulių gerbėjas, galite naudoti „Excel“ formules sąrašui suskirstyti abėcėlės tvarka. Dvi formulės, kurias galima naudoti abėcėliniam duomenims suskirstyti, yra COUNTIF ir VLOOKUP.
Pavyzdžiui, turime sąrašą vardų, kuriuos norime suskirstyti pagal abėcėlę naudodami formulę.
Norėdami tai surūšiuoti, prie esamos lentelės pridėsime laikiną stulpelį pavadinimu „Rūšiavimo tvarka“.
Šalia duomenų esančiame langelyje (A2) naudokite šią COUNTIF formulę:
=COUNTIF($B$2:$B$20,"<="&B2) Aukščiau pateikta formulė lygina teksto reikšmę langelyje B2 su visomis kitomis teksto reikšmėmis (B3:B20) ir grąžina jos santykinį rangą. Pavyzdžiui, langelyje B2 jis pateikia 11, nes yra 11 teksto reikšmių, kurios yra mažesnės arba lygios tekstui „Nancy“ abėcėlės tvarka. Rezultatas yra darbuotojo vardo rūšiavimo tvarka langelyje B2.
Tada naudokite užpildymo rankenėlę, vilkite šią formulę, kad užpildytumėte ją visame diapazone. Taip pateikiama kiekvieno vardo sąraše rūšiavimo tvarka.
Dabar turime sutvarkyti duomenis, rodomus aukščiau esančioje ekrano kopijoje, pagal rūšiavimo eilės numerį, o tai padaryti naudosime funkciją VLOOKUP.
Sintaksė:
=ŽIŪROS (,A:B,2,0)Čia „rūšiavimo skaičius“ reiškia skaičius didėjančia tvarka nuo 1 iki 20. Mažėjančia tvarka skaičiai turi būti nuo 20 iki 1.
Čia yra D stulpelis surūšiuotiems pavadinimams. D2 langelyje įveskite šią VLOOKUP formulę:
=ŽIŪROS (1;A:B;2;0)Panašiai antroje ir trečioje langeliuose turite naudoti formulę kaip:
=ŽIŪROS (2;A:B;2;0) ir =ŽIŪROS (3;A:B;2;0) ir taip toliau…
Įvedus šią VLOOKUP formulę į kiekvieną langelį šalia duomenų, sąrašas bus suskirstytas į abėcėlę.
Užuot rankiniu būdu įvedę pirmiau minėtą formulę (1-20) į kiekvieną langelį, taip pat galite naudoti eilutės funkciją, kad palengvintumėte darbą. Funkcija Eilutė () grąžina dabartinio langelio eilutės numerį. Taigi, naudojant eilutės funkciją, formulė bus tokia:
=ŽIŪROS (EILUTE()-1,A:B,2,0)Ši formulė duos tokį patį rezultatą kaip ir aukščiau pateikta formulė.
Tada naudokite užpildymo rankenėlę, kad vilkite šią formulę, kad užpildytumėte ją visame diapazone.
Kaip suskirstyti abėcėlę pagal pavardę
Kartais duomenų lapus su pavardėmis dažnai turime rūšiuoti abėcėlės tvarka. Tokiais atvejais, nors vardai prasideda vardais, juos reikia suskirstyti pagal abėcėlę pagal pavardę. Tai galite padaryti naudodami „Excel“ teksto formules.
Norėdami tai padaryti, pirmiausia turėsite ištraukti vardą ir pavardę iš pilnų vardų į du skirtingus stulpelius. Tada pakeiskite pavadinimus, surūšiuokite juos ir grąžinkite į pradinę formą.
A2 laukelyje nurodydami visą pavadinimą, dviejuose skirtinguose langeliuose (B2 ir C2) įveskite šias formules, tada nukopijuokite formules žemyn stulpeliais (naudodami užpildymo rankenėlę) iki paskutinio langelio su duomenimis:
Norėdami išgauti vardą, įveskite šią formulę langelyje C2:
=KAIRĖ(A2,PAIEŠKA(" ",A2)-1)
Norėdami išgauti pavardę, langelyje D2 įveskite šią formulę:
=DEŠINĖ(A2,LEN(A2)-PAIEŠKA(" ",A2,1))
Tada langelyje E2 sujungiami vardai ir pavardės atvirkštine tvarka, atskirti kableliu:
=D2&“, „&C2
Atskyrėme vardus ir pavardes ir juos sukeitėme. Dabar turime juos suskirstyti abėcėlės tvarka ir pakeisti atgal. Kaip matote aukščiau, kai ištraukiame pavadinimus naudodami formulę, C, D ir E stulpeliuose iš tikrųjų yra formulės, tačiau jos suformatuotos taip, kad atrodytų kaip reikšmės. Taigi formulę turime konvertuoti į reikšmes.
Norėdami tai padaryti, pirmiausia pažymėkite visus formulės langelius (E1:E31) ir paspauskite Ctrl + C juos kopijuoti. Dešiniuoju pelės mygtuku spustelėkite pažymėtus langelius, spustelėkite piktogramą „Vertės“ skiltyje „Įklijavimo parinktys“ ir paspauskite klavišą „Enter“.
Tada gautame stulpelyje pasirinkite bet kurį langelį ir spustelėkite parinktį „A iki Z“ arba „Z į A“ skirtuke „Duomenys“ arba „Pagrindinis“ pagal savo poreikius.
Kai spustelėsite mygtuką, pasirodys dialogo langas Rūšiuoti įspėjimas. Pasirinkite parinktį „Išplėsti pasirinkimą“ ir spustelėkite mygtuką „Rūšiuoti“.
Kaip matote, visas stulpelis suskirstytas į abėcėlę pagal pavardę.
Dabar, jei norite jį grąžinti į pradinį „Vardo pavardės formatą“, tereikia įvesti tas pačias formules, bet su skirtingomis langelių nuorodomis.
Jūs turite dar kartą atskirti pavadinimą (E2) į dvi dalis naudodami toliau pateiktas formules:
G2 ištraukite vardą:
=DEŠINĖ(E2,LEN(E2)-PAIEŠKA(" ",E2))
G2 ištraukite pavardę:
=KAIRĖ(E2,IEŠKA(" ",E2)-2)
Ir sujunkite dvi dalis, kad gautumėte originalų pilną vardą:
=G2&" "&H2
Kiekvieną kartą įvedę formulę į pirmąjį langelį, būtinai nukopijuokite formulę stulpeliu žemyn (naudodami užpildymo rankenėlę) iki paskutinio langelio su duomenimis.
Dabar viskas, ką jums reikia padaryti, tai dar kartą konvertuoti formules į verčių konvertavimą, kaip darėme aukščiau, ir viskas. tai padaryta ir.