Šajā rokasgrāmatā ir parādīts, kā programmā Excel sakārtot alfabētu, izmantojot funkciju Kārtot un filtrēt, kā arī formulas datu kārtošanai un formatēšanai.
Microsoft Excel ir efektīva programmatūra svarīgu datu, piemēram, finanšu, pārdošanas datu, klientu informācijas utt., organizēšanai. Datu sakārtošana darblapās pēc alfabēta ir viens no labākajiem veidiem, kā ātri kārtot, piekļūt šiem datiem un atsaukties uz tiem. Šūnu diapazona kārtošana alfabētā nozīmē diapazona kārtošanu alfabētiskā secībā programmā Excel. Datus var kārtot abos virzienos — augošā vai dilstošā secībā.
Piemēram, pieņemsim, ka jums ir liela datu kopa ar klienta pasūtījumu informāciju un vēlaties atrast konkrēta klienta pasūtījumu vēsturi. Visa saraksta izķemmēšana kļūs par diezgan sarežģītu darbu. Tā vietā, lai tērētu laiku ritināšanai, varat sakārtot slejas alfabēta secībā, lai ātri atrastu datus.
Atkarībā no datu kopas veida, ar kuru strādājat, programmā Excel varat izmantot dažādus veidus, kā sakārtot datus pēc alfabēta. Programmā Excel varat kārtot vienu kolonnu vai vienu rindu vai atlasīto diapazonu vai visu darblapu, kā arī iespējas grupēt vairākas kolonnas vai rindas, kamēr tās kārtojat. Uzziniet, kā Excel izklājlapās datus sakārtot pēc rindām vai kolonnām alfabēta secībā.
Kāpēc kārtot datus pēc alfabēta programmā Excel
Datu sakārtošanai alfabētā būs vairākas priekšrocības:
- Tas padara datus saprātīgākus un atvieglo lasīšanu.
- Lietotājam ir vieglāk atrast konkrētu vērtību vai klienta vārdu Excel datu lapā.
- Tas palīdz lietotājam vizuāli identificēt dublētos ierakstus, lai mēs varētu novērst datu ievades kļūdas.
- Tas ļauj lietotājiem viegli grupēt kolonnas vai sarakstus, lai lietotāji varētu tos aplūkot blakus.
Sakārtošana pēc alfabēta (kārtot alfabētiskā secībā) ir vienkāršs un izplatīts veids, kā ātri kārtot informāciju. Tas ir īpaši noderīgi ļoti lielām datu kopām. Kopumā programmā Excel ir 4 veidi, kā alfabētiski sakārtot datus: poga A–Z vai Z–A, kārtošanas līdzeklis, filtra funkcija un formulas.
Kolonnas alfabēta sakārtošana programmā Excel
Ātrākais veids, kā programmā Excel kārtot alfabētiskā secībā, ir izmantot iebūvēto Kārtošanas funkciju.
Vispirms atveriet izklājlapu, kurā ir datu kopa. Atlasiet šūnas vai kolonnu, ko vēlaties kārtot, un noteikti neatzīmējiet tukšas šūnas. Pēc tam atveriet cilni “Dati” un noklikšķiniet uz “A-Z”, lai kārtotu augošā secībā, vai uz “Z-A”, lai kārtotu grupā Kārtot un filtrēt dilstošā secībā.
Ja starp tām ir tukšas šūnas, programma Excel pieņem, ka visi dati ir sakārtoti, un apstājas virs melnās šūnas. Ja vēlaties kārtot visu kolonnu, atlasiet jebkuru kolonnas šūnu un izmantojiet iepriekš minēto opciju.
Tām pašām opcijām var piekļūt arī cilnes Sākums sadaļas Rediģēšanas rīkā Kārtot un filtrēt.
Kad esat noklikšķinājis uz pogas, Excel nekavējoties sakārtojiet sarakstu pēc alfabēta.
Kā kārtot un saglabāt rindas kopā programmā Excel
Kolonnu var viegli kārtot, ja darblapā ir tikai viena kolonna, taču tas kļūst nedaudz sarežģītāks, ja darblapā ir vairākas kolonnas blakus kolonnai, kuru vēlaties kārtot. Turklāt dažreiz mēs strādājam ar izklājlapām, kurās pēc kolonnas kārtošanas mums ir jāsaglabā neskartas atbilstošās rindas.
Piemēram, ja mums ir skolēnu atzīmju saraksta izklājlapa. Sakārtojot skolēnu vārdu kolonnu alfabētiskā secībā, mēs vēlamies, lai arī tām blakus esošajās rindās ievadītās atzīmes attiecīgi pārvietotos.
Šādos gadījumos varat izmantot pogu “A-Z” vai “Z-A”, lai sakārtotu vienu no kolonnām alfabēta secībā, un tas automātiski sakārtos datus citās kolonnās, saglabājot rindas kopā. Apskatīsim, kā tas darbojas ar citu piemēru.
Vispirms atlasiet pirmo kolonnu, pēc kuras vēlaties kārtot citas kolonnas. Pēc tam cilnē Dati vai Sākums atlasiet “A-Z” vai “Z-A”, lai kārtotu datus vienā kolonnā, un programma Excel automātiski pārkārtos pārējās kolonnas atbilstoši.
Kad noklikšķināsit uz kārtošanas pogas, tiks parādīts dialoglodziņš Kārtošanas brīdinājums. Atlasiet radio pogu “Izvērst atlasi” un noklikšķiniet uz pogas “Kārtot”.
Tādējādi rindas tiks saglabātas kopā, kārtojot kolonnas, nesakrītot datus (kā parādīts tālāk).
Ja atlasīsit opciju “Turpināt ar pašreizējo atlasi”, programma Excel kārtos tikai atlasīto kolonnu.
Kā kārtot alfabētiskā secībā pēc vairākām kolonnām
Ja vēlaties kārtot datu kopu alfabētiskā secībā pēc vairākām kolonnām, varat to izdarīt, izmantojot Excel funkciju Kārtot.
Piemēram, mēs vēlamies šo tabulu sakārtot alfabētiskā secībā vispirms pēc valsts un pēc tam pēc vārda:
Vispirms atlasiet visu tabulu, kuru vēlaties kārtot pēc alfabēta, pēc tam dodieties uz cilni "Dati" un grupā Kārtot un filtrēt noklikšķiniet uz "Kārtot".
Vai arī noklikšķiniet uz ikonas "Kārtot un filtrēt" cilnes "Sākums" sadaļā Rediģēšana un atlasiet "Pielāgota kārtošana", lai piekļūtu tai pašai funkcijai.
Tiks parādīts dialoglodziņš Kārtot un parādīs pirmo kolonnu (parametru), kurā tabula tiks kārtota. Nolaižamajā lodziņā "Kārtot pēc" atlasiet primāro kolonnu, pēc kuras vispirms vēlaties sakārtot datus alfabēta secībā, mūsu gadījumā "Valsts" un nolaižamajā izvēlnē "Pasūtījums" izvēlieties "A līdz Z" vai "Z līdz A". .
Pēc tam noklikšķiniet uz pogas "Pievienot līmeni", lai pievienotu otro kārtošanas līmeni. Atlasiet otro kolonnu (mūsu gadījumā Vārds), pēc kuras vēlaties sakārtot datus pēc alfabēta lodziņā “Kārtot pēc”, un atlasiet “A līdz Z” vai “Z līdz A”. Ja tabulas augšpusē ir galvenes, atzīmējiet izvēles rūtiņu “Maniem datiem ir galvenes”, lai tās varētu izlaist galvenes, kamēr tās tiek kārtotas. Ja nepieciešams, pievienojiet citus kārtošanas līmeņus un noklikšķiniet uz “OK”.
Tabula ir sakārtota alfabētiskā secībā: vispirms pēc valsts un pēc tam pēc vārda, kā parādīts tālāk.
Kā programmā Excel kārtot rindas alfabēta secībā
Dažreiz programmā Excel var vēlēties sakārtot rindas pēc alfabēta, nevis kolonnas. To var izdarīt, izmantojot Excel funkciju Kārtot. Piemēram, pieņemsim, ka jums ir izklājlapa, kurā pirmās rindas kolonnā B līdz T ir norādīti pilsētu nosaukumi, kur ir pieejami veikalu skaits dažādos mazumtirdzniecības veidos, un A slejā ir norādīts mazumtirgotāja veids. Šūnas tiek izmantotas, lai izsekotu, cik veikalu katrā pilsētā ir dažādās mazumtirgotāju kategorijās.
Vispirms atlasiet visas rindas, kuras vēlaties sakārtot pēc alfabēta; Ja nevēlaties pārvietot rindu iezīmes, atstājiet tās ārpus atlases. Tagad dodieties uz cilni “Dati” un grupā Kārtot un filtrēt noklikšķiniet uz “Kārtot”.
Dialoglodziņā Kārtot noklikšķiniet uz pogas Opcijas.
Parādīsies vēl viens mazs dialoglodziņš, kurā varēsit atlasīt “Kārtot no kreisās uz labo pusi” vai “Kārtot no augšas uz leju”. Atlasiet opciju “Kārtot no kreisās puses uz labo”, lai sakārtotu alfabētu pēc rindas, un noklikšķiniet uz “OK”, lai atgrieztos dialoglodziņā Kārtot.
Tagad nolaižamajā sarakstā “Kārtot pēc” atlasiet rindu, kuru vēlaties sakārtot pēc alfabēta (šajā piemērā 1. rinda). Otrā laukā saglabājiet šūnu vērtības lodziņā “Kārtot” un lodziņā “Sūtīt” atlasiet “A līdz Z” (augošā secībā). Pēc tam noklikšķiniet uz “OK”, lai aizvērtu dialoglodziņu.
Tagad mūsu tabulas pirmā rinda ir sakārtota alfabētiskā (augošā) secībā, un pārējās rindas ir attiecīgi pārkārtotas, kā parādīts tālāk.
Kā kārtot kolonnu alfabētiskā secībā, izmantojot filtru
Vēl viens ātrs veids, kā programmā Excel kārtot alfabētiskā secībā, ir izmantot filtru. Kad kolonnām lietojat filtrus, visu kolonnu kārtošanas opcijām var piekļūt tikai ar vienu peles klikšķi.
Lai kolonnām pievienotu filtru, atlasiet vienu vai vairākas kolonnas un cilnes Dati grupā Kārtot un filtrēt noklikšķiniet uz opcijas Filtrēt. Ja vēlaties lietot filtru visai tabulai, atlasiet jebkuru tabulas šūnu un noklikšķiniet uz opcijas "Filtrs".
Varat arī piekļūt opcijai Filtrs, noklikšķinot uz rīka “Kārtot un filtrēt” cilnes “Sākums” rediģēšanas grupā un atlasot “Filtrs”.
Katrā kolonnas galvenē tiks parādīta neliela nolaižamā bultiņa. Noklikšķiniet uz bultiņas jebkurā kolonnā, kuru vēlaties kārtot alfabētiskā secībā, un atlasiet “Kārtot no A līdz Z” vai “Kārtot no Z līdz A”.
Tādējādi vajadzīgā kolonna tiks sakārtota jūsu izvēlētajā alfabētiskā secībā, un, ja izvēlējāties "Kārtot no A līdz Z", uz filtra pogas parādīsies neliela augšupvērsta bultiņa, kas norāda kārtošanas secību (augošā secībā):
Papildu kārtošana pielāgotā secībā
Kārtošana alfabētiskā secībā nav ideāla visiem alfabētiskajiem datiem. Dažreiz datus var sakārtot pēc alfabēta, taču tas nav efektīvākais veids, kā tos kārtot.
Iedomāsimies situāciju, kad jums var būt saraksts ar mēnešu vai darba dienu nosaukumiem, kārtošana alfabētiskā secībā šajā situācijā nav lietderīga. Ir lietderīgāk kārtot šo sarakstu hronoloģiski. Ja šķirojat no A līdz Z, tas sakārtos mēnešus alfabēta secībā, vispirms būs aprīlis, pēc tam augusts, februāris, jūnijs utt. Bet tas nav tas, ko jūs vēlaties. Par laimi, to ir ļoti viegli sakārtot hronoloģiski programmā Excel, izmantojot opciju Advanced Custom Sort.
Sāciet, atlasot tabulu, kuru vēlaties kārtot. Pēc tam cilnes Sākums sadaļā “Kārtošana un filtrēšana” atlasiet “Pielāgota kārtošana”.
Dialoglodziņā Kārtot izvēlieties kolonnu, kurā ir gada mēneši, kārtojiet pēc “Vērtības”, kā parasti, un sadaļā “Pasūtījums” izvēlieties “Pielāgots saraksts”.
Dialoglodziņā Pielāgoti saraksti varat izveidot savu sarakstu. Ir daži noklusējuma pielāgotie saraksti, tostarp nedēļu dienas, saīsināti mēneši, gada mēneši un tā tālāk. Izvēlieties atbilstošo kārtošanas opciju atbilstoši savām vajadzībām (mūsu gadījumā janvāris, februāris, ..) un noklikšķiniet uz “OK”. Pēc tam vēlreiz noklikšķiniet uz Labi, lai sakārtotu sarakstu hronoloģiski.
Jūs pamanīsit, ka esat veiksmīgi sakārtojis sarakstu atbilstoši mēneša secībai.
Kā kārtot alfabētiskā secībā programmā Excel, izmantojot Excel formulas
Ja esat formulu cienītājs, varat izmantot Excel formulas, lai sakārtotu sarakstu pēc alfabēta. Divas formulas, ko var izmantot datu sakārtošanai alfabētā, ir COUNTIF un VLOOKUP.
Piemēram, mums ir saraksts ar vārdiem, kurus vēlamies sakārtot pēc alfabēta, izmantojot formulu.
Lai to sakārtotu, esošajai tabulai pievienosim pagaidu kolonnu ar nosaukumu “Šķirošanas secība”.
Šūnā (A2) blakus datiem izmantojiet šo COUNTIF formulu:
=COUNTIF($B$2:$B$20,"<="&B2) Iepriekš minētā formula salīdzina teksta vērtību šūnā B2 ar visām pārējām teksta vērtībām (B3:B20) un atgriež tās relatīvo rangu. Piemēram, šūnā B2 tas atgriež 11, jo ir 11 teksta vērtības, kas ir mazākas vai vienādas ar tekstu “Nancy” alfabētiskā secībā. Rezultāts ir darbinieka vārda kārtošanas secība šūnā B2.
Pēc tam izmantojiet aizpildīšanas turi, velciet šo formulu, lai aizpildītu to visā diapazonā. Tas parāda katra vārda kārtošanas secību sarakstā.
Tagad mums ir jāsakārto iepriekš redzamajā ekrānuzņēmumā redzamie dati, pamatojoties uz kārtošanas pasūtījuma numuru, un, lai to izdarītu, mēs izmantosim funkciju VLOOKUP.
Sintakse:
=VLOOKUP(,A:B,2,0)Šeit “šķirošanas numurs” apzīmē skaitļus augošā secībā no 1 līdz 20. Dilstošā secībā skaitļiem jābūt no 20 līdz 1.
Šeit mums ir D kolonna sakārtotiem nosaukumiem. Šūnai D2 ievadiet šādu VLOOKUP formulu:
=VLOOKUP(1;A:B;2;0)Līdzīgi otrajai un trešajai šūnai ir jāizmanto formula kā:
=VLOOKUP(2;A:B;2;0) un =VLOOKUP(3;A:B;2;0) un tā tālāk…
Pēc šīs VLOOKUP formulas ievadīšanas katrā šūnā blakus datiem saraksts tiek sakārtots alfabēta secībā.
Tā vietā, lai manuāli ievadītu iepriekš minēto formulu (1-20) katrā šūnā, varat izmantot arī rindas funkciju, lai atvieglotu darbu. Funkcija Rinda () atgriež pašreizējās šūnas rindas numuru. Tātad, izmantojot rindas funkciju, formula būs šāda:
=UZmeklēšana(RINDA()-1,A:B,2,0)Šī formula dos tādu pašu rezultātu kā iepriekš minētā formula.
Un pēc tam izmantojiet aizpildīšanas turi, lai velciet šo formulu, lai aizpildītu to visā diapazonā.
Kā sakārtot ierakstus pēc uzvārda alfabēta
Reizēm mums bieži ir jākārto alfabētiskā secībā datu lapas ar uzvārdiem. Šādos gadījumos, lai gan vārdi sākas ar vārdiem, tie ir jāsakārto pēc uzvārda alfabēta secībā. To var izdarīt, izmantojot Excel teksta formulas.
Lai to izdarītu, vispirms divās dažādās kolonnās būs jāizvelk vārds un uzvārds no pilnajiem vārdiem. Pēc tam apgrieziet nosaukumus, sakārtojiet tos un pēc tam apgrieziet tos atpakaļ sākotnējā formā.
Ja laukā A2 ir pilns nosaukums, ievadiet šādas formulas divās dažādās šūnās (B2 un C2) un pēc tam kopējiet formulas pa kolonnām (izmantojot aizpildīšanas turi) līdz pēdējai šūnai ar datiem:
Lai izvilktu vārdu, šūnā C2 ievadiet šo formulu:
=KREISI(A2,MEKLĒŠANA("",A2)-1)
Lai izvilktu uzvārdu, šūnā D2 ievadiet šo formulu:
=LABI(A2,GARA(A2)-MEKLĒŠANA(" ",A2,1))
Un pēc tam šūnā E2 savienoja vārdu un uzvārdu apgrieztā secībā, atdalot tos ar komatu:
=D2&", "&C2
Mēs esam atdalījuši vārdus un uzvārdus un apvērsuši tos. Tagad mums tie ir jāsakārto alfabēta secībā un jāapgriež atpakaļ. Kā redzat iepriekš, kad mēs izņemam nosaukumus, izmantojot formulu, kolonnas C, D un E faktiski satur formulas, taču tās ir formatētas tā, lai tās izskatītos kā vērtības. Tātad mums ir jāpārvērš formula vērtībās.
Lai to izdarītu, vispirms atlasiet visas formulas šūnas (E1:E31) un nospiediet Ctrl+C lai tās kopētu. Ar peles labo pogu noklikšķiniet uz atlasītajām šūnām, sadaļā "Ielīmēšanas opcijas" noklikšķiniet uz ikonas "Vērtības" un nospiediet taustiņu "Enter".
Pēc tam atlasiet jebkuru šūnu iegūtajā kolonnā un atbilstoši savām vajadzībām noklikšķiniet uz opcijas “A līdz Z” vai “Z līdz A” cilnē “Dati” vai “Sākums”.
Kad noklikšķināsit uz pogas, tiks parādīts dialoglodziņš Kārtošanas brīdinājums. Izvēlieties opciju "Izvērst atlasi" un noklikšķiniet uz pogas "Kārtot".
Kā redzat, visa kolonna alfabēta pēc uzvārda.
Tagad, ja vēlaties to mainīt atpakaļ uz sākotnējo “Vārda uzvārda formātu”, jums vienkārši jāievada tās pašas formulas, bet ar dažādām šūnu atsaucēm.
Nosaukums (E2) vēlreiz jāsadala divās daļās, izmantojot tālāk norādītās formulas:
Sadaļā G2 izņemiet vārdu:
=LABI (E2, LEN(E2)-MEKLĒŠANA(" ",E2))
G2 izvelciet uzvārdu:
=KREISI(E2,MEKLĒŠANA(" ",E2)-2)
Un savienojiet abas daļas kopā, lai iegūtu sākotnējo pilno vārdu:
=G2&" "&H2
Katru reizi, kad ievadāt formulu pirmajā šūnā, noteikti nokopējiet formulu kolonnā (izmantojot aizpildīšanas turi) līdz pēdējai šūnai ar datiem.
Tagad viss, kas jums jādara, ir vēlreiz konvertēt formulas uz vērtību konvertēšanu, kā mēs to darījām iepriekš, un esat pabeidzis. tas izdarīts un.