Razvrščanje v Excelu: razvrstite podatke po besedilu, datumu, številki ali barvi

Razvrščanje podatkov v Excelu je pogosta in preprosta naloga, ki vam lahko pomaga pri prerazporejanju podatkov glede na vrsto razvrščanja, ki jo izberete. Običajno lahko z vgrajeno funkcijo razvrščanja razvrstite številke, besedilne nize, datume in ure v enega ali več stolpcev; Podatke lahko hitro in enostavno razvrstite po seznamu po meri, ki ste ga ustvarili, ali po oblikovanju celic (na primer barvi pisave, barvi ozadja ali ikoni).

Poleg tega preprostega razvrščanja je v vašem vsakdanjem delu veliko bolj uporabno in zapleteno razvrščanje, ki ga boste morda potrebovali. Ta vadnica bo predstavila različne vrste razvrščanja za rešitev vaše težave v Excelu.

Dostop do možnosti razvrščanja v Excelu

Za uporabo funkcije razvrščanja vam Excel ponuja več načinov dostopa do možnosti razvrščanja.

1.1 Gumbi za razvrščanje na traku

Najhitrejši način uporabe funkcije razvrščanja je uporaba gumbov za razvrščanje na traku.

Kliknite poljubno celico v stolpcu z vrednostmi, ki jih želite razvrstiti, nato kliknite datum jeziček, v Razvrsti in filtriraj kliknite Razvrsti od A do Ž or Razvrsti Z do A gumb za razvrščanje podatkov po naraščajočem ali padajočem abecednem vrstnem redu. Oglejte si posnetek zaslona:

Opombe: Če so v podatkovnem obsegu prazne vrstice, se lahko po uporabi teh gumbov uspešno razvrsti samo obseg podatkov izbrane celice, ki je nad ali pod praznimi vrsticami.

1.2 Pogovorno okno za razvrščanje

v Razvrsti in filtriraj skupina datum zavihek, obstaja še en Razvrščanje gumb, glej posnetek zaslona:

Po kliku na to Razvrščanje gumb, a Razvrščanje pogovorno okno se prikaže, kot je prikazano spodaj:

Nato v Razvrščanje V pogovornem oknu lahko izberete ustrezna pravila za razvrščanje podatkov, kot jih potrebujete.

nasveti: To lahko odprete tudi s tipkami za bližnjice Razvrščanje pogovorno okno, pritisnite Alt + A + S + S tipke zaporedoma v tipkovnici.

1.3 Možnosti razvrščanja v meniju Filter

Če ste v obseg podatkov uporabili filtre, lahko skupaj z možnostmi filtriranja najdete tudi možnosti razvrščanja. S klikom na ikono filtra v katerem koli stolpcu so prikazane možnosti razvrščanja na razširjenem seznamu, kot je prikazano na spodnjem posnetku zaslona:

Opombe: Te možnosti razvrščanja se bodo spremenile glede na podatke v stolpcu: Če je v stolpcu besedilo, se bo prikazalo Razvrsti od A do Ž, Razvrsti Z do A; Če so v stolpcu številke, se prikaže Razvrsti od Najmanjše do Največje, Razvrsti od Največje do Najmanjše; Če so v stolpcu datumi, se bo prikazal Razvrsti od najstarejšega do najnovejšega, Razvrsti od najnovejšega do najstarejšega.

1.4 Z desno tipko miške kliknite možnosti razvrščanja

V Excelu lahko uporabite tudi možnosti razvrščanja z desno tipko miške za hitro in preprosto razvrščanje podatkov, z desno tipko miške kliknite katero koli celico v stolpcu z vrednostmi, ki jih želite razvrstiti, in v kontekstnem meniju lahko vidite, da je na voljo šest možnosti razvrščanja, izberite tistega, ki ga potrebujete, si oglejte posnetek zaslona:

Osnovne navade razvrščanja

Ta funkcija razvrščanja v Excelu vam lahko pomaga pri preprostem razvrščanju, na primer pri razvrščanju številk, besedilnih nizov, datumov v naraščajočem ali padajočem vrstnem redu, razvrščanju celic glede na pisavo ali barvo ozadja. Ta razdelek bo govoril o nekaterih osnovnih uporabah te vrste funkcije.

2.1 Razvrstite podatke po besedilih, številkah ali datumih

Če želite razvrstiti obseg podatkov na podlagi besedil, številk ali datumov v naraščajočem ali padajočem vrstnem redu, naredite naslednje:

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite datum > Razvrščanje, glej posnetek zaslona:

2. v Razvrščanje v pogovornem oknu izvedite naslednje postopke:

• v Stolpec v razdelku izberite ime stolpca, ki ga želite razvrstiti;
• v Razvrsti naprej izberite, izberite Vrednosti celic možnost;
• v Naročilo razdelku, določite vrstni red razvrščanja. (Za razvrščanje besedilnih nizov izberite A do Ž or Z do A; Če želite razvrstiti seznam številk, izberite Najmanjša do največja or Največji do najmanjši; Če želite razvrstiti datumske celice, izberite Najstarejši do najnovejših or Najstarejši.)
• nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, bodo izbrani podatki razvrščeni glede na stolpec, ki ste ga navedli hkrati.

2.2 Razvrstite podatke glede na barvo celice, barvo pisave, ikono celice

Če želite razvrstiti obseg podatkov glede na barvo celice, barvo pisave ali ikono pogojnega oblikovanja, lahko funkcija Razvrščanje to nalogo hitro reši.

Recimo, da imate obseg podatkov, ki je oblikovan z nekaterimi barvami celic, kot je prikazano na spodnjem posnetku zaslona. Če morate podatke preurediti glede na barvo celice, bi na primer želeli postaviti svetlo rdeče vrstice, ki jim sledi svetloba rumene in svetlo modre vrstice, če želite vrstice razvrstiti po barvi celice, naredite naslednje:

1. Izberite obseg podatkov, ki ga želite razvrstiti glede na barvo celice, in kliknite datum > Razvrščanje Pojdite na Razvrščanje pogovorno okno.

2. v Razvrščanje pogovorno okno, določite naslednje operacije:

2.1) Prvo barvo celice na vrhu nastavite tako:

• v Stolpec izberite, izberite Ime ali druge stolpce, kjer imate obarvane celice. V tem primeru imam barvne celice v vseh stolpcih, lahko izberete poljubno ime stolpca;
• Pod Razvrsti naprej razdelek, izberite Barva celice možnost;
• v Naročilo v razdelku izberite eno barvo celice, ki jo želite postaviti zgoraj ali spodaj;

2.2) Nato kliknite Dodaj nivo Če želite dodati drugo in druge ravni pravil, ponovite zgornje korake za nastavitev druge in drugih barv celic.

• nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Po končanih nastavitvah kliknite OK gumb in obseg podatkov je bil razvrščen glede na barvo celice, ki ste jo navedli, glejte posnetek zaslona:

nasveti: V skladu z zgornjimi koraki lahko podatke razvrstite tudi glede na barvo pisave ali ikono celice, tako da izberete font Color or Ikona pogojnega oblikovanja v pogovornem oknu Razvrsti.

2.3 Razvrstite podatke po več stolpcih

Če imate velik nabor podatkov, kot je prikazano na spodnjem posnetku zaslona, ​​želite izvedeti večstopenjsko razvrščanje podatkov za jasnejše branje podatkov, na primer najprej razvrščanje po stolpcu Regija in nato stolpec Stanje, nazadnje stolpec Prodaja. Kako lahko to razvrščate v Excelu?

Če želite podatke razvrstiti po več stolpcih, sledite spodnjim korakom:

1. Izberite obseg podatkov, ki jih želite razvrstiti, in kliknite datum > Razvrščanje Pojdite na Razvrščanje pogovorno okno.

2. v Razvrščanje pogovorno okno, kliknite Dodaj nivo dvakrat, ker obstajajo trije stolpci, ki jih želite uporabiti za razvrščanje. Potem lahko vidite dva Potem pa V polje s seznamom so bile dodane ravni pravil:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Iz Razvrsti po in Potem pa na spustnem seznamu izberite imena stolpcev, ki jih želite razvrstiti, nato pa izberite Vrednosti celic Iz Razvrsti naprej ločeno za vsak izbrani stolpec, na koncu izberite vrstni red razvrščanja, ki ga potrebujete.

4. Nato kliknite OK, in dobili boste rezultat razvrščanja, kot je prikazano na spodnjem posnetku zaslona:

2.4 Razvrstite podatke na podlagi seznama po meri

Namesto razvrščanja podatkov po abecednem ali številčnem vrstnem redu ta funkcija razvrščanja ponuja tudi razvrščanje seznamov po meri. Na primer, spodnji obseg podatkov bi radi razvrstili po statusu - Ni začeto, V teku, Dokončano, ki ste ga določili. Tukaj bom predstavil, kako ravnati s to vrsto razvrščanja.

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite datum > Razvrščanje Pojdite na Razvrščanje pogovorno okno.

2. v Razvrščanje pogovorno okno, izberite stolpec, ki ga želite razvrstiti iz Stolpec in nato izberite Vrednosti celic Iz Razvrsti naprej oddelek, v Naročilo kliknite Seznam po meri možnost, glej posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. In a Seznami po meri pojavi se okno, naredite naslednje:

• klik NOV SEZNAM v po meri polje s seznami;
• Nato vnesite besedilne nize v vrstnem redu, ki ga morate razvrstiti glede na Vnosi v seznam škatla; (Ko vnašate besedila, pritisnite Vnesite tipko za ločevanje vnosov.)
• Končno kliknite Dodaj gumb, se nov seznam doda v Seznami po meri škatla naenkrat.

4. Nato kliknite OK za vrnitev na Razvrščanje pogovorno okno. Novi seznam predmetov po meri je zdaj prikazan v Naročilo spustni seznam.

5. In nato kliknite OK gumb, da zaprete pogovorno okno, zato je obseg podatkov razvrščen po seznamu po meri, ki ste ga določili, oglejte si posnetek zaslona:

2.5 Razvrščanje podatkov od leve proti desni (vodoravno razvrščanje)

Običajno podatkovno tabelo vedno razvrščate navpično od zgoraj navzdol, včasih pa boste morda želeli razvrstiti podatke glede na vrednosti vrstice (razvrščanje od leve proti desni). Na primer, v spodnjem obsegu podatkov ga želim razvrstiti glede na vrednosti v vrstici Ime.

V tem primeru ima funkcija razvrščanja vgrajeno funkcionalnost, ki vam omogoča razvrščanje od leve proti desni. Naredite naslednje korake:

1. Izberite obseg podatkov (izključite glave), ki ga želite razvrstiti, in kliknite datum > Razvrščanje , da odprete pogovorno okno Razvrsti.

2. V izpuščenem Razvrščanje pogovorno okno, kliknite možnosti nato v Možnosti razvrščanja pogovorno okno, izberite Razvrsti od leve proti desni možnost, glej posnetek zaslona:

3. Kliknite OK za vrnitev v pogovorno okno Razvrsti zdaj v Vrstica razdelku, določite številko vrstice, na podlagi katere želite razvrstiti podatke, in nato izberite Vrednosti celic v Razvrsti naprej v razdelku na koncu izberite vrstni red razvrščanja v Naročilo razdelek, ki ga potrebujete, si oglejte posnetek zaslona:

4. Nato kliknite OK, vaši podatki pa so razvrščeni, kot je prikazano na spodnjem posnetku zaslona:

2.6 Razvrstite podatke v vrstnem redu, ki je občutljiv na velike in male črke

Na splošno razvrščanje podatkov ne poteka v vrstnem redu, ki je odvisen od velikih in malih črk, kot je prikazano spodaj na prvem posnetku zaslona. Kaj pa, če želite, da je razvrščanje velikih in malih črk občutljivo, kot je prikazano na sliki drugega zaslona, ​​kako bi lahko to nalogo rešili v Excelu?

Če želite v Excelu razvrstiti velike in male črke, naredite naslednje:

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite datum > Razvrščanje Pojdite na Razvrščanje pogovorno okno.

2. v Razvrščanje v pogovornem oknu v Stolpec razdelku, določite ime stolpca, na podlagi katerega želite razvrstiti podatke, in nato izberite Vrednosti celic v Razvrsti naprej v razdelku na koncu izberite vrstni red razvrščanja v Naročilo razdelek, ki ga potrebujete, si oglejte posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nadaljujte s klikom na možnosti v tem pogovornem oknu in v Možnosti razvrščanja potrditveno polje, potrdite Za razliko od velikih in malih črk možnost, glej posnetek zaslona:

4. Kliknite OK > OK če želite zapreti pogovorna okna, se bodo podatki razvrstili po vrstnem redu glede na male in male črke, nato pa velike črke, če je besedilo enako. Glej posnetek zaslona:

Napredne uporabe razvrščanja

Pri vsakodnevnem delu lahko naletite na druge bolj zapletene, praktične, specifične težave pri razvrščanju, ta razdelek bo govoril o več vrstah nalog razvrščanja v excelu.

3.1 Razvrstite podatke glede na pogostost

Ob predpostavki, da imate v stolpcu seznam podatkov, ga želite razvrstiti po padajočem vrstnem redu pogostosti pojavljanja podatkov, kot je prikazano spodaj. Tukaj bom predstavil dve metodi za rešitev te naloge.

 Podatke razvrstite glede na pogostost s pomožnim stolpcem

V Excelu ni neposrednega načina za razvrščanje podatkov glede na število pojavitev, tukaj lahko ustvarite formulo za pomoč, da dobite pogostost pojavljanja vsakega besedila, nato pa razvrstite po stolpcu za pomoč, da dobite rezultat razvrščanja, ki ga potrebujete.

1. Vnesite spodnjo formulo v prazno celico, ki meji na izvirne podatke, na primer B2, in nato povlecite ročico za polnjenje navzdol do celic za uporabo te formule, glejte posnetek zaslona:

=COUNTIF($A$2:$A$16,A2)

Opombe: V zgornji formuli, A2: A16 je seznam s podatki, ki jih želite razvrstiti po pogostosti, in A2 so prvi podatki tega seznama.

2. Nato še naprej izbirajte celice formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetke zaslona:

3. In nato kliknite Razvrščanje gumb, zdaj je bil prvotni stolpec razvrščen po frekvenci, kot so prikazani naslednji posnetki zaslona:

 Razvrstite podatke glede na frekvenco s priročno funkcijo

Če imate Kutools za Excel, Z njegovim Napredno razvrščanje funkcijo, lahko hitro in enostavno razvrstite podatke glede na pogostost pojavljanja brez pomožnega stolpca.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite seznam podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje, glej posnetek zaslona:

2. v Napredno razvrščanje pogovorno okno, izberite stolpec, ki ga želite razvrstiti iz Stolpec in nato izberite frekvenca v Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo razdelek, glej posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, je bil seznam podatkov razvrščen po številu dogodkov naenkrat, oglejte si posnetek zaslona:

3.2 Razvrstite podatke glede na dolžino znakov

Če imate seznam podatkov, ki vsebujejo besedilne nize z različno dolžino, boste morda morali podatke razvrstiti po dolžini znakov, da bo stolpec videti čeden in urejen. Ta razdelek bo govoril o razvrščanju podatkov po številu znakov.

 Podatke razvrstite glede na dolžino znakov s pomožnim stolpcem

Če želite razvrstiti stolpec po dolžini znakov, uporabite funkcijo LEN za izračun števila znakov za vsako celico in nato uporabite funkcijo Sort za razvrščanje seznama podatkov, naredite naslednje:

1. Vnesite to formulo = LEN (A2) v prazno celico, ki meji na izvirne podatke, na primer B2, in nato povlecite ročico za polnjenje navzdol do celic za uporabo te formule, glejte posnetek zaslona:

2. Nato še naprej izbirajte celice formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje gumb, so bili nizi razvrščeni po dolžini znaka. Pomočni stolpec B lahko po potrebi izbrišete. Glej posnetek zaslona:

 Razvrstite podatke glede na dolžino znakov z enostavno možnostjo

Za hitro in enostavno razvrščanje podatkov glede na število znakov vam bom tukaj priporočil priročno orodje - Kutools za Excel, Z njegovim Napredno razvrščanje funkcijo, z lahkoto se spopadete s to nalogo.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite seznam podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite Dolžina besedila Iz Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo odsek. Oglejte si posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave ije preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, so bili besedilni nizi na seznamu razvrščeni po dolžini znakov, kot je prikazano na spodnjem posnetku zaslona:

3.3 Razvrstite polna imena po priimkih

Pri razvrščanju seznama polnih imen bo Excel za razvrščanje uporabil prvi znak imena, kaj pa, če želite razvrstiti podatke po priimkih? Tukaj vam bom predstavil nekaj trikov, s katerimi boste zlahka razvrstili polna imena po priimkih v Excelu.

 Polna imena razvrstite po priimkih s pomožnim stolpcem

Če razvrščate polna imena na podlagi priimkov, morate izvleči priimke v nov stolpec in nato uporabiti funkcijo Razvrsti, da polna imena razvrstite po ločenih priimkih po abecedi. Naredite naslednje:

1. Spodnjo formulo vnesite ali kopirajte v prazno celico poleg izvirnih podatkov, na primer B2, in nato povlecite ročico za polnjenje navzdol do celic za uporabo te formule, glejte posnetek zaslona:

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

2. Nadaljujte z izbiro celic formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje gumb in dobili boste polna imena, ki so razvrščena glede na priimke. Ko končate, lahko po potrebi izbrišete stolpec za pomoč. Oglejte si posnetek zaslona:

 Polna imena razvrstite po priimkih s hitro metodo

Če niste seznanjeni s formulo, razvrstite polna imena po priimkih brez formule, Napredno razvrščanje značilnost Kutools za Excel ti lahko naredi uslugo.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite celice s polnim imenom, ki jih želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite Priimek Iz Razvrsti naprej spustnem seznamu, na koncu v vrstici Naročilo odsek. Oglejte si posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, je stolpec s polnim imenom naenkrat razvrščen po priimkih.

3.4 Razvrščanje e -poštnih naslovov po domeni

Če imate seznam e -poštnih naslovov, ki jih je treba razvrstiti po domeni in ne le po prvi črki naslova, kako bi to storili v Excelu? Ta razdelek bo govoril o nekaterih hitrih metodah za razvrščanje e -poštnih naslovov po domenah.

 Razvrstite e -poštne naslove po domeni s pomožnim stolpcem

Tu lahko ustvarite formulo za ekstrahiranje e -poštnih domen v drug stolpec in nato razvrstite izvirne e -poštne naslove glede na nov stolpec domene.

1. V prazno celico poleg vašega e-poštnega naslova vnesite naslednjo formulo, v tem primeru bom to formulo vnesel v celico C2, nato pa povlecite ročico za polnjenje v obseg, v katerem želite uporabiti to formulo. Vse e -poštne domene bodo nato izvlečene iz naslovov, oglejte si posnetek zaslona:

=RIGHT(B2,LEN(B2)-FIND("@",B2))

2. Pustite celice formule izbrane in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A kot potrebujete, v Razvrsti opozorilo pogovorno okno, preverite Razširite izbor, glej posnetek zaslona:

3. In nato kliknite Razvrščanje gumb za razvrščanje podatkov po domenah e -pošte v naraščajočem ali padajočem vrstnem redu, kot ste podali.

4. Po razvrščanju lahko po potrebi izbrišete pomožni stolpec.

 E -poštne naslove razvrstite po domeni z več kliki

Če imate Kutools za Excel, Z njegovim Napredno razvrščanje funkcijo, lahko e -poštne naslove razvrstite po imenih domen po abecedi z le nekaj kliki.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite celoten obseg podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite Domena pošte Iz Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo odsek. Oglejte si posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Kliknite OK, obseg podatkov pa je razvrščen po domenah e -pošte, kot je prikazano na spodnjem posnetku zaslona:

3.5 Razvrstite en stolpec tako, da se ujema z drugim

Če imate dva stolpca s podatki, ki sta popolnoma enaka ali skoraj enaka, vendar v različnih zaporedjih, boste morda morali dva stolpca razvrstiti tako, da bodo iste vrednosti poravnane v istih vrsticah v dveh stolpcih. V tem razdelku bom predstavil dva primera za to vrsto razvrščanja.

 Razvrstite dva stolpca s popolnoma enakimi elementi, da se ujemata

Na primer, imam dva stolpca, ki vsebujeta iste postavke, vendar v različnih zaporedjih, tukaj želim razvrstiti drugi stolpec tako, da se ujema s prvim stolpcem, kot je prikazano na spodnjem posnetku zaslona.

1. Spodnjo formulo vnesite v prazno celico poleg izvirnih podatkov, nato povlecite ročico za polnjenje, da uporabite to formulo za vse celice na seznamu, in to bo vrnilo položaj vsake vrednosti v stolpcu B proti stolpcu A, glejte posnetek zaslona :

=MATCH(B2,$A$2:$A$10,0)

2. Nato izberite stolpec B in nov pomožni stolpec ter kliknite datum > Razvrščanje Pojdite na Razvrščanje v pogovornem oknu v Razvrščanje v pogovornem oknu izvedite naslednje postopke:

• Iz stolpca izberite stolpec za pomoč, po katerem želite razvrstiti podatke Stolpec odsek;
• Nato izberite Vrednosti celic v Razvrsti naprej odsek;
• Končno izberite Najmanjša do največja možnost v Naročilo oddelek.

3. In nato kliknite OK gumb. Zdaj se bosta dva stolpca ujemala tako, kot je prikazano na spodnjem posnetku zaslona. Stolpec formule lahko po potrebi izbrišete.

 Razvrstite dva stolpca, ki se ne ujemata popolnoma enako

Včasih elementi v dveh stolpcih morda niso popolnoma enaki. Na primer, podatke v drugem stolpcu želim razvrstiti tako, da se ujemajo s podatki v prvem stolpcu, tako da se iste vrednosti poravnajo v istih vrsticah, kot je prikazano na spodnjem posnetku zaslona.

1. Med oba stolpca vstavite nov prazen stolpec.

2. Spodnjo formulo vnesite v celico B2 in nato povlecite ročico za polnjenje, da izpolnite to formulo v celice, ki jih potrebujete. Zdaj lahko vidite, da so bili podatki v stolpcu C razvrščeni tako, da se ujemajo s podatki v stolpcu A.

=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))

3.6 Razvrstite in izvlecite edinstvene vrednosti s seznama podatkov

Če imate seznam vrednosti, ki vsebujejo nekaj dvojnikov, želite samo izvleči edinstvene vrednosti in jih razvrstiti po abecedi, kot je prikazano na spodnjem posnetku zaslona, ​​tukaj bom predstavil nekaj formul za obravnavo te vrste razvrščanja.

1. Spodnjo formulo vnesite v prazno celico - C2 in pritisnite Ctrl + Shift + Enter tipke za prvi rezultat, si oglejte posnetek zaslona:

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")

Opombe: V formuli, A2: A12 je seznam podatkov, iz katerega želite izvleči edinstvene vrednosti, C1 je celica nad formulo, ki jo vnesete. Prosimo, da jih spremenite glede na svoje potrebe.

2. Nato povlecite ročico za polnjenje, da izvlečete vrednosti, dokler se ne prikažejo prazne celice, vse edinstvene vrednosti bodo izvlečene in razvrščene v naraščajočem vrstnem redu, glejte posnetek zaslona:

3.7 Razvrstite več vrstic ali stolpcev hkrati neodvisno

Z lahkoto lahko razvrstimo obseg podatkov na podlagi ene vrstice ali stolpca, včasih pa boste morda želeli vsako vrstico ali stolpec v obsegu ločeno razvrstiti po abecedi, kako bi to nalogo lahko dosegli v Excelu?

 Razvrstite več vrstic ločeno

Recimo, da imate vrsto podatkov, ki jih je treba v vsaki vrstici preurediti po abecedi, kot je prikazano na spodnjem posnetku zaslona, ​​za rešitev te težave sta dva hitra trika za vas.

Razvrstite več vrstic neodvisno hkrati s formulo

1. Kopirajte oznake vrstic na drugo mesto, kjer želite dobiti razvrščeni rezultat.

2. Nato kopirajte ali vnesite spodnjo formulo v prazno celico - H2 in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat, glejte posnetek zaslona:

=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&$B2:$E2), 0))

3. Nato izberite celico formule H2 in povlecite ročico za polnjenje v desno, da kopirate formulo v druge celice prve vrstice, v tem primeru povlecite v celico K2, glejte posnetek zaslona:

4. Nadaljujte z izbiranjem celic formule v prvi vrstici (H2: K2) in povlecite ročico za polnjenje navzdol, da kopirate formulo v druge vrstice. Zdaj lahko vidite, da so bile vrednosti v vsaki vrstici razvrščene posamično v naraščajočem vrstnem redu.

Razvrstite več vrstic naenkrat neodvisno s kodo VBA

Naslednja koda VBA vam lahko pomaga tudi pri enostavnem razvrščanju podatkov v vsaki vrstici po abecedi. Naredite naslednje:

1. V vsaki vrstici izberite podatke, ki jih želite razvrstiti.

2. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

3. Nato kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Razvrstite več vrstic neodvisno hkrati

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
        yRg.Sort Key1:=yRg.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

4. Nato pritisnite F5 za zagon te kode so bili podatki v vsaki vrstici takoj razvrščeni po naraščajočem vrstnem redu, oglejte si posnetek zaslona:

 Razvrstite več stolpcev ločeno

Če želite razvrstiti podatke v vsakem stolpcu posebej, vam lahko naslednji dve metodi naredita uslugo.

Razvrsti več stolpcev neodvisno naenkrat s formulo

1. Kopirajte oznake stolpcev na drugo mesto, kjer želite dobiti razvrščeni rezultat.

2. Nato v prazno celico vnesite spodnjo formulo - F3 in pritisnite Ctrl + Shift + Enter skupaj, da dobite prvi rezultat, nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge vrstice, glejte posnetek zaslona:

=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&A$3:A$6),0))

3. Nadaljujte z izbiranjem celic formule v prvi vrstici (F3: F6) in povlecite ročico za polnjenje v desno, da kopirate formulo v druge stolpce. Zdaj so bile vrednosti v vsakem stolpcu razvrščene posamično v naraščajočem vrstnem redu, kot je prikazano na spodnjem posnetku zaslona:

Razvrstite več stolpcev naenkrat neodvisno s kodo VBA

Če želite podatke razvrstiti v več stolpcev neodvisno, vam lahko naredi uslugo tudi naslednja koda VBA, naredite to:

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Nato kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Razvrstite več stolpcev hkrati neodvisno

Sub SortIndividualJR()
'Updateby Extendoffice
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Kutools for excel", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

3. Nato pritisnite F5 tipko za zagon te kode in pojavil se bo poziv, izberite obseg podatkov, ki ga želite razvrstiti, oglejte si posnetek zaslona:

4. In nato kliknite OK, je bil vsak stolpec hitro razvrščen posebej.

3.8 Naključno razvrstite podatke v Excelu

Za nas je običajno, da podatke razvrščamo po abecedi v naraščajočem ali padajočem vrstnem redu, a ste kdaj poskušali podatke razvrstiti v izbranem obsegu naključno? V tem razdelku bom govoril o tem, kako v naključnem vrstnem redu premešamo vrsto celic.

 Stolpec podatkov naključno razvrstite s pomožnim stolpcem

Običajno lahko uporabite funkcijo RAND za pridobivanje naključnih števil, nato pa razvrstite podatke na podlagi tega naključnega seznama, naredite tako:

1. Vnesite to formulo: = RAND () v prazno celico poleg vaših podatkov in povlecite ročico za polnjenje navzdol, da zapolnite seznam naključnih števil, kot je prikazano na spodnjem posnetku zaslona:

2. Nadaljujte z izbiro celic formule in nato kliknite Data > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. In nato kliknite Razvrščanje gumb, seznam podatkov je bil takoj premešan, oglejte si posnetek zaslona:

 Razvrstite celice, vrstice ali stolpce naključno z neverjetno funkcijo

Kutools za Excel podpira močno funkcijo - Razvrsti obseg naključno, s to funkcijo lahko naključno razporedite podatke v obsegu celic, v vsakem stolpcu / vrstici izbora ali naključno razporedite celotne vrstice ali stolpce hkrati.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg podatkov, ki ga želite naključno razvrstiti, in nato kliknite Kutools Plus > Razvrščanje > Razvrsti obseg naključno. Oglejte si posnetek zaslona:

2. v Naključno razvrsti / izberi obseg v pogovornem oknu pod Razvrščanje zavihek, izberite eno od naslednjih možnosti, ki jih potrebujete:

• Cele vrstice: Naključno premešajte celotne vrstice v izbranem obsegu.
• Celi stolpci: Naključno premešajte celotne stolpce v izbranem obsegu.
• Celice v vsaki vrstici: Naključno premešajte celice v vsaki vrstici posebej.
• Celice v vsakem stolpcu: Naključno premešajte celice v vsakem stolpcu.
• Vse celice v območju: Naključno razporedi vse celice v izbranem območju.

3. Nato kliknite Ok gumb, so podatki takoj naključno izbrani.

3.9 Razvrstite seznam datumov glede na mesec, dan ali mesec in dan

Pri razvrščanju datumov v Excelu bo funkcija razvrščanja privzeto razvrstila seznam datumov po letu, mesecu in dnevu, v določenih primerih pa boste morda morali datume razvrstiti po mesecu ali dnevu ali mesecu in dnevu, pri tem pa prezreti leto. Kako rešiti to težavo v Excelu?

 Seznam datumov po mesecu ali dnevu razvrstite s pomožnim stolpcem

Če želite datume razvrstiti samo po mesecu ali dnevu, lahko iz njih izvlečete številke mesecev ali dni, nato pa datume razvrstite po stolpcu z izvlečenimi številkami meseca ali dneva.

1. Spodnjo formulo vnesite v prazno celico poleg svojih podatkov in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice, oglejte si posnetek zaslona:

=MONTH(B2)        (extract month number)
= DAN (B2)              (izvleči številko dneva)

2. Ko vrnete številke mesecev ali dni, še naprej izbirajte celice formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje gumb, so datumi zdaj razvrščeni po mesecih, pri čemer se leta in dnevi ne upoštevajo. Glej posnetek zaslona:

 Seznam datumov razvrstite po mesecu ali dnevu z več kliki

Če imate Kutools za Excel, Z njegovim Napredno razvrščanje funkcijo, lahko z več kliki razvrstite seznam datumov samo po mesecu ali dnevu.

Po namestitvi Kutools za Excel, naredite naslednje:

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite mesec or Dan Iz Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo oddelek.

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, so bili podatki razvrščeni glede na mesec ali dan, kot ste navedli, pri tem pa prezrli leto, glejte posnetek zaslona:

 Seznam datumov po mesecu in dnevu razvrstite s pomožnim stolpcem

Če morate seznam datumov razvrstiti samo po mesecu in dnevu brez leta, vam lahko funkcija TEXT pomaga pretvoriti datum v besedilni niz v podani obliki in nato po potrebi uporabite funkcijo razvrščanja.

1. Spodnjo formulo vnesite v prazno celico poleg svojih podatkov in nato kopirajte to formulo na dno vaših podatkov, mesec in dan sta izvlečena kot besedilni niz, kot je prikazano na spodnjem posnetku zaslona:

=TEXT(B2,"MMDD")

2. Obdržite celice formule pri izbiri in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje in zdaj so vaši podatki razvrščeni samo po mesecu in dnevu.

3.10 Razvrstite seznam datumov glede na dneve v tednu

Če želite razvrstiti seznam datumov glede na dneve v tednu, kar pomeni razvrščanje datumov od ponedeljka do nedelje ali nedelje do sobote. V tem razdelku bom predstavil dve metodi za dokončanje te naloge v Excelu.

 Seznam datumov razvrstite po dneh v tednu s stolpcem za pomoč

Če želite razvrstiti datume po dnevih v tednu, boste potrebovali tudi stolpec za pomoč, da vrnete številko, ki ustreza dnevom v tednu, nato pa datume razvrstite po stolpcu za pomoč.

1. Vnesite ali kopirajte spodnjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice, oglejte si posnetek zaslona:

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
= TEDEN (B2,2)       (Začetek od ponedeljka (1) do nedelje (7))

2. Obdržite izbiro celic formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. In nato kliknite Razvrščanje Če želite dobiti želeni rezultat razvrščanja, si oglejte posnetek zaslona:

 Seznam datumov razvrstite po dneh v tednu z enostavno možnostjo

S pomočjo Kutools za ExcelJe Napredno razvrščanje funkcijo, lahko to težavo rešite čim hitreje brez pomožne formule.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg podatkov, ki ga boste razvrstili, nato kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite Dan v tednu Iz Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo oddelek.

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK gumb, podatki so bili razvrščeni po dnevih v tednu, kot jih potrebujete. Glej posnetek zaslona:

3.11 Razvrstite seznam datumov glede na četrtletje

Ta razdelek bo govoril o tem, kako razvrstiti seznam datumov po četrtletjih brez upoštevanja leta, za vas bosta na voljo dva trika.

 Seznam datumov po četrtletjih razvrstite s pomožnim stolpcem

Tako kot zgornje rešitve morate ustvariti stolpec za pomoč formuli, da iz navedenih datumov izvlečete številko četrtine, nato pa datume razvrstite na podlagi tega novega stolpca pomočnikov.

1. Vnesite ali kopirajte spodnjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice, oglejte si posnetek zaslona:

=ROUNDUP(MONTH(B2)/3,0)

2. Nadaljujte z izbiro celic formule in nato kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. In nato kliknite Razvrščanje gumb, je obseg podatkov razvrščen po četrtletju, kot je prikazano na spodnjem posnetku zaslona:

 Seznam datumov po četrtletjih razvrstite s priročno funkcijo

Če imate Kutools za Excel nameščen, Napredno razvrščanje funkcija vam lahko pomaga rešiti to nalogo z več kliki.

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu izberite stolpec, ki ga želite razvrstiti v Stolpec in izberite četrtletje Iz Razvrsti naprej spustnem seznamu, na koncu, v vrstici Naročilo oddelek.

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Nazadnje kliknite OK gumb, izbrani obseg pa je glede na četrtino razvrščen po vaših potrebah.

3.12 Razvrstite podatke glede na imena mesecev ali imena delavnikov

Recimo, da imate seznam imen mesecev kot besedilo, ko bodo imena mesecev razvrščena po abecedi, namesto da jih razvrstite po mesečnem vrstnem redu od januarja do decembra. Če morate imena mesecev razvrstiti od januarja do decembra, vam lahko razvrstitev po meri v funkciji razvrščanja naredi uslugo.

1. Izberite obseg podatkov, ki ga želite razvrstiti glede na imena mesecev, in kliknite datum > Razvrščanje Pojdite na Razvrščanje pogovorno okno.

2. v Razvrščanje v pogovornem oknu v Stolpec v razdelku izberite ime stolpca, ki vsebuje imena mesecev, v Razvrsti naprej oddelek, izberite Vrednosti celickončno v Naročilo izberite, izberite Seznam po meri, glej posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. Potem, v izskočil Seznam po meri v pogovornem oknu izberite polna imena mesecev (januar, februar, marec, …) ali kratka imena (jan, februar, mar…) glede na to, kako so meseci navedeni na vašem delovnem listu, glejte posnetek zaslona:

4. In nato kliknite OK > OK da zaprete pogovorna okna in so zdaj vaši podatki razvrščeni po imenu meseca v kronološkem vrstnem redu, kot je prikazano na spodnjem posnetku zaslona:

nasveti: Če želite razvrstiti po imenih delavnikov, izberite polna imena (nedelja, ponedeljek, torek, ...) ali kratka imena (ned, pon, torek ...) v Seznami po meri pogovorno okno, kot ga potrebujete.

3.13 Razvrstite podatke na podlagi lihih ali parnih števil

Morda nam bo enostavno razvrščati številke v naraščajočem ali padajočem vrstnem redu v Excelu, toda ali ste že kdaj poskušali razvrstiti številke od lihega do snega ali celo do lihega na seznamu? V tem razdelku bom predstavil nekaj metod za izvedbo te naloge.

 Podatke razvrstite po lihih ali parnih številkah s pomožnim stolpcem

Seznam številk razvrstite po lihih ali parnih številkah, ustvarite formulo za identifikacijo lihih ali parnih številk in nato uporabite funkcijo razvrščanja.

1. Poleg seznama številk vnesite to formulo = ISODD (A2) v prazni celici in nato povlecite, da kopirate formulo v druge celice, zdaj lahko vidite, da so prikazane vrednosti TRUE in FALSE, TRUE označuje liha števila, FALSE pa sode številke.

2. Nadaljujte z izbiro celic formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje gumb, so vse parne številke razvrščene skupaj, nato pa sledijo lihe številke ali obratno. Oglejte si posnetek zaslona:

 Podatke razvrstite po lihih ali parnih številkah s koristno funkcijo

S pomočjo Kutools za ExcelJe Napredno razvrščanje funkcijo, lahko hitro razvrstite številke od lihih do parnih številk ali obratno.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu kliknite stolpec, pod katerim želite razvrstiti Stolpec in nato izberite Neparno in sodo število Iz Razvrsti naprej v razdelku nato določite vrstni red razvrščanja (A do Ž razvrsti od parnih do lihih številk in Z do A razvrsti od lihih do parnih številk), si oglejte posnetek zaslona:

nasveti: Če so v vaših podatkih glave, se prepričajte Moji podatki imajo glave je preverjeno. Če vaši podatki nimajo naslovov, jih počistite.

3. In nato kliknite OK gumb, boste dobili naslednje rezultate:

3.14 Razvrstite podatke na podlagi absolutnih vrednosti

Če v stolpcu obstajajo pozitivna in negativna števila, bodo pri razvrščanju številke v Excelu privzeto razporejene v naraščajočem ali padajočem vrstnem redu. Toda v določenem primeru boste morda želeli prezreti negativni znak pri razvrščanju podatkov, kar pomeni, da številke razvrstite po absolutnih vrednostih. Kako doseči to nalogo v Excelu?

 Podatke razvrstite po absolutnih vrednostih s pomožnim stolpcem

Če želite razvrstiti številke po absolutnih vrednostih, morate običajno ustvariti formulo za pretvorbo vseh vrednosti v absolutne vrednosti in nato uporabiti funkcijo razvrščanja za razvrščanje števil.

1. V sosednjo prazno celico, na primer B2, vnesite to formulo = ABS (A2), nato pa povlecite ročico za polnjenje, da kopirate to formulo v druge celice. Zdaj lahko vidite, da so bile vse vrednosti pretvorjene v absolutne vrednosti:

2. Obdržite izbiro celic formule in kliknite datum > Razvrsti od A do Ž or Razvrsti Z do A po potrebi v pojavnem oknu Razvrsti opozorilo v polju za poziv izberite Razširite izbor, glej posnetek zaslona:

3. Nato kliknite Razvrščanje gumb, vse številke pa so razvrščene po absolutnih vrednostih, kot je prikazano na spodnjem posnetku zaslona:

 Razvrstite podatke po absolutnih vrednostih s priročno možnostjo

Če ste utrujeni z ustvarjanjem pomožnega stolpca, vam priporočam tukaj Kutools za Excel, Z njegovim Napredno razvrščanje funkcije, lahko številke razvrstite po absolutnih vrednostih neposredno in preprosto.

Po namestitvi Kutools za Excel, naredite naslednje:

1. Izberite obseg podatkov, ki ga želite razvrstiti, in kliknite Kutools Plus > Razvrščanje > Napredno razvrščanje.

2. v Napredno razvrščanje v pogovornem oknu kliknite stolpec, pod katerim želite razvrstiti Stolpec in nato izberite absolutna vrednost iz Razvrsti naprej razdelku, nato določite vrstni red razvrščanja, si oglejte posnetek zaslona:

nasveti: Če so v vaših podatkih glave, preverite, ali so v mojih podatkih glave. Če vaši podatki nimajo naslovov, jih počistite.

3. Nato kliknite OK, bodo vse številke razvrščene od najmanjše do največje ali največje do najmanjše vrednosti, ne glede na negativni predznak.

Samodejno razvrstite podatke pri vnosu ali spreminjanju podatkov

Funkcija razvrščanja v Excelu ni dinamična, podatke boste morali znova razvrstiti po vsaki spremembi ali vsakič, ko so dodani novi podatki. V tem razdelku bom razpravljal o tem, kako samodejno razvrstiti vaše podatke vsakič, ko se v obseg podatkov doda nova vrednost.

4.1 Samodejno razvrščanje številskih vrednosti v stolpcu s formulami

Če želite samodejno razvrstiti seznam številk v naraščajočem ali padajočem vrstnem redu, lahko uporabite formule, ki temeljijo na funkcijah LERGE, SMALL in ROW.

1. Prosimo, vnesite ali kopirajte spodnjo formulo v prazno celico poleg izvornih podatkov, nato pa povlecite ročico za polnjenje navzdol do celic, v katere želite uporabiti to formulo, nato pa bodo številke razporejene od najmanjših do največjih vrednosti, glejte posnetek zaslona:

=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")

Opombe: V tej formuli, A2: A100 je seznam številk, ki ga želite samodejno razvrstiti, vključno z nekaj praznimi celicami za nove vnose, B2 je celica, v katero vnesete formulo.

2. Zdaj, ko spremenite izvorne podatke ali vnesete nove podatke, se razvrščeni seznam samodejno osveži, kot je prikazano spodaj:

4.2 Samodejno razvrščanje besedilnih vrednosti v stolpcu s formulami

Če želite samodejno razvrstiti besedilne vrednosti v stolpec, zgornja formula ne bo delovala, tukaj vam lahko pomaga druga formula, naredite tako:

1. Spodnjo formulo vnesite ali kopirajte v prazno celico poleg stolpca s podatki in pritisnite Ctrl + Shift + Enter skupaj, da dobite prvo besedilo, nato izberite celico formule in povlecite ročico za polnjenje navzdol do celic, ki jih želite razvrstiti, oglejte si posnetek zaslona:

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")

2. Od zdaj naprej se bodo pri vnosu vrednosti ali spreminjanju izvirnih podatkov v stolpcu A besedilni nizi v stolpcu B samodejno razvrščali po naraščajočem vrstnem redu, glej spodnji demo:

4.3 Samodejno razvrstite kombinacijo številskih in besedilnih vrednosti v stolpec s kodo VBA

Če so v stolpcu tako številske kot besedilne vrednosti, vam lahko spodnja koda VBA naredi uslugo za samodejno razvrščanje seznama podatkov.

1. Z desno miškino tipko kliknite zavihek lista, kjer želite samodejno razvrstiti podatke, nato izberite Ogled kode iz kontekstnega menija v pojavnem oknu Microsoft Visual Basic za aplikacije okno, kopirajte in prilepite naslednjo kodo v prazno Moduli okno, glej posnetek zaslona:

Koda VBA: samodejno razvrščanje, ko so podatki vneseni ali spremenjeni v naraščajočem vrstnem redu:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Opombe: V zgornji kodi bodo vneseni podatki samodejno razvrščeni v stolpec A. A1 je glava in A2 je prva celica na seznamu podatkov.

2. Nato shranite in zaprite okno s kodo. Ko vnesete nove podatke ali spremenite izvirne podatke v stolpec A, se bodo podatki samodejno razvrstili po naraščajočem vrstnem redu. Spodaj si oglejte demo:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Drugi primeri razvrščanja

Pri vsakodnevnem delu boste morda imeli druge različne zahteve glede razvrščanja. V tem razdelku bom predstavil nekatere druge vrste razvrščanja, na primer razvrščanje podatkov znotraj celice, razvrščanje podatkov v stolpec ali vrstico z obratnim vrstnim redom itd.

5.1 Razvrsti podatke v celici

Za razvrščanje besedilnega niza znotraj ene celice po abecednem vrstnem redu, na primer za razvrščanje besedila »HDAW« kot »ADHW«; Ali pa razvrstite več besed, ločenih z vejico v celici, na primer za razvrstitev »word, outlook, excel, access« kot »access, excel, outlook, word«. Ta razdelek bo govoril o tem, kako rešiti to vrsto razvrščanja v Excelu.

 Razvrsti vrednost niza v celici po abecednem vrstnem redu

Če razvrstite vrednost niza znotraj celice po abecednem vrstnem redu, morate ustvariti uporabniško definirano funkcijo. Prosim naredite takole:

1. Drži ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Razvrsti besedilno vrednost v celici

Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
    Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
    xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
    For xF2 = xF1 To UBound(xArr)
        If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
            xStrT = xArr(xF2)
            xArr(xF2) = xArr(xF1)
            xArr(xF1) = xStrT
        End If
    Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function

3. Nato shranite in zaprite to kodo, vrnite se na delovni list. Nato vnesite to formulo = SortCellContents (A2) v prazno celico, kamor želite vrniti rezultat, in povlecite ročico za polnjenje navzdol, da kopirate formulo v druge celice, nato pa bodo vse besedilne vrednosti v celicah razvrščene po abecednem vrstnem redu, glejte posnetek zaslona:

 Besedilne nize razvrstite po ločilniku znotraj celice po abecednem vrstnem redu

Če je v celici več besed, ki so ločene s posebnimi ločili, jih lahko razvrstite po abecednem vrstnem redu v celici, vam lahko pomaga spodnja funkcija, ki jo določi uporabnik.

1. Drži ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: razvrstite besedilne nize, ločene z vejico v celici

Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
    For xF1 = 0 To UBound(xSArr)
        For xF2 = xF1 + 1 To UBound(xSArr)
                If xSArr(xF2) < xSArr(xF1) Then
                    xStrT = xSArr(xF2)
                    xSArr(xF2) = xSArr(xF1)
                    xSArr(xF1) = xStrT
                End If
        Next xF2
    Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
    xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function

3. Nato shranite in zaprite to kodo, vrnite se na delovni list, vnesite to formulo = SortCellWithSeparator (A2, ",", TRUE) v prazno celico, kamor vrnete rezultat, nato pa povlecite ročico za polnjenje navzdol, da kopirate formulo v druge celice, vsi besedilni nizi v celicah bodo razvrščeni po abecednem vrstnem redu, glejte posnetek zaslona:

Opombe: Če so vaši besedilni nizi ločeni z drugimi ločili, morate vejico v tej formuli spremeniti v svoj ločilo.

5.2 Vrnite / obrnite vrstni red podatkov v stolpcu ali vrstici

Včasih boste morda morali obrniti vrstni red podatkov na glavo v navpičnem obsegu podatkov ali od leve proti desni v vodoravnem obsegu podatkov. V tem razdelku bodo predstavljene tri metode za reševanje te naloge v Excelu.

 Obrnite / obrnite vrstni red podatkov v stolpcu ali vrstici s formulami

Naslednje formule lahko pomagajo spremeniti vrstni red podatkov v stolpcu ali vrstici, naredite tako:

Obrnite vrstni red podatkov v stolpcu

Vnesite ali kopirajte naslednjo formulo v prazno celico, kamor želite vnesti rezultat, in nato povlecite ročico za polnjenje navzdol do celic, da vrnete vrednosti celic v obratnem vrstnem redu, glejte posnetek zaslona:

=OFFSET($A$10,-(ROW(A1)-1),0)

Opombe: V zgornji formuli, A1 je prva celica in A10 je zadnja celica v stolpcu.

Obračajte vrstni red podatkov zaporedoma

Uporabite spodnjo formulo, da obrnete vrstni red podatkov vodoravno v vrstici:

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

Opombe: V zgornji formuli, A1 je prva celica v vrstici in 1:1 je številka vrstice, v kateri so vaši podatki. Če so podatki v vrstici 10, jih spremenite na 10:10.

Nato povlecite ročico za polnjenje desno do celic, dokler ne izvlečete vseh vrednosti, in dobili boste, da so vsi podatki obrnjeni vodoravno, glejte posnetek zaslona:

 Obrnite / obrnite vrstni red podatkov v več stolpcih ali vrsticah s kodo VBA

Zgornje formule dobro delujejo samo za en stolpec ali vrstico, če je več stolpcev ali vrstic s podatki, ki jih je treba obrniti, vam lahko pomagajo naslednje kode VBA.

Obrnite vrstni red podatkov v obsegu celic navpično

1. Najprej morate varnostno kopirati izvirne podatke, nato pa držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: navpično obrnite obseg celic v obratnem vrstnem redu

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

3. Nato pritisnite F5 za zagon te kode in pojavil se bo poziv, ki vas bo prosil, da izberete obseg podatkov, ki ga želite obrniti navpično, glejte posnetek zaslona:

4. Nato kliknite OK gumb, se bo obseg podatkov obrnil navpično, kot je prikazano na spodnjih posnetkih zaslona:

Obrnite vrstni red podatkov v obsegu celic vodoravno

Če želite spremeniti obseg podatkov v vodoravnem vrstnem redu, uporabite spodnjo kodo VBA:

Koda VBA: navpično obrnite obseg celic v obratnem vrstnem redu

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

Po zagonu te kode so bili podatki v vrsticah obrnjeni vodoravno, glejte posnetke zaslona:

 Vrnite / obrnite vrstni red podatkov v stolpcih ali vrsticah samo z enim klikom

Če imate Kutools za Excel, Z njegovim Navpični navpični razpon in Obrnite vodoravni razpon funkcije, lahko območje celic obrnete navpično in vodoravno z enim samim klikom.

Po namestitvi Kutools za Excel, naredite tako:

Obrnite vrstni red podatkov v obsegu celic navpično

1. Izberite obseg podatkov, ki ga želite obrniti, nato kliknite Kutools > Območje > Navpični navpični razpon > vsi / Samo flip vrednosti, glej posnetek zaslona:

nasveti: Če izberete vsi možnost, se bo celotno oblikovanje celic istočasno obrnilo; Če se odločite Samo flip vrednosti, bodo le vrednosti celic obrnjene.

2. In potem se bo obseg podatkov hkrati obrnil navpično.

Obrnite vrstni red podatkov v obsegu celic vodoravno

1. Izberite obseg podatkov in kliknite Kutools > Območje > Obrnite vodoravni razpon > Vse / samo vrednosti preklopa, glej posnetek zaslona:

nasveti: Če izberete vsi možnost, se bo celotno oblikovanje celic istočasno obrnilo; Če se odločite Samo flip vrednosti, bodo le vrednosti celic obrnjene.

2. Nato se vse vrednosti celic v izboru takoj horizontalno obrnejo, kot je prikazano na spodnjih posnetkih zaslona:

5.3 Razvrstite zavihke delovnega lista po abecednem vrstnem redu ali barvi zavihkov v Excelu

Recimo, da je v vašem delovnem zvezku veliko delovnih listov, če morate zavihke listov razvrstiti po abecednem vrstnem redu ali razvrstiti zavihke glede na barvo zavihkov, običajna metoda - povleci in spusti zavihke, izgubi veliko časa. V tem razdelku bom obravnaval nekaj trikov za razporeditev zavihkov listov v naraščajočem ali padajočem vrstnem redu.

 Zavihke delovnega lista razvrstite po abecednem vrstnem redu s kodo VBA

Naslednja koda VBA vam lahko pomaga razvrstiti zavihke listov po abecedi v naraščajočem ali padajočem vrstnem redu, naredite tako:

1. Drži ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin nato v mapo prilepite naslednji makro Moduli Okno.

VBA: Vse liste razvrstite po abecednem vrstnem redu

Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. Nato pritisnite F5 tipko za zagon tega makra, v naslednjem polju s pozivom kliknite Da, bodo vsi delovni listi razvrščeni po naraščajočem abecednem vrstnem redu; Kliknite Ne, bodo vsi delovni listi razvrščeni po padajočem abecednem vrstnem redu, kot jih potrebujete.

 Razvrstite zavihke delovnega lista po abecednem / alfanumeričnem vrstnem redu z zmogljivo funkcijo

Kutools za Excel ponuja zmogljivo funkcijo - Razvrsti liste, s to funkcijo lahko razvrstite delovne liste po abecednem ali alfanumeričnem vrstnem redu, razvrstite liste glede na barvo zavihka ali obrnite zavihke listov, kot jih potrebujete.

Po namestitvi Kutools za Excel, naredite naslednje:

1. Odprite delovni zvezek, v katerem želite razvrstiti zavihke listov, in kliknite Kutools Plus > Delovni list > Razvrsti liste, glej posnetek zaslona:

2. v Razvrsti liste v desnem podoknu izberite eno vrsto razvrščanja, na primer Alfa Razvrsti, Črkovno številčno razvrščanje. Oglejte si posnetek zaslona:

3. Nato kliknite Ok gumb, bodo vsi delovni listi razvrščeni glede na vrsto razvrščanja, ki ste jo določili. Oglejte si posnetke zaslona:

 Razvrstite zavihke delovnega lista glede na barvo zavihka s kodo VBA

Običajno pri nas razvrščamo zavihke delovnega lista po abecedi v Excelu, a ste kdaj poskušali razvrstiti zavihke listov glede na barvo zavihkov? Spodnja koda VBA lahko pomaga rešiti to težavo, naredite naslednje:

1. Drži ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin nato v mapo prilepite naslednji makro Moduli Okno.

VBA: Razvrstite vse liste glede na barvo zavihka

Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

3. Nato pritisnite F5 Če želite izvesti to kodo, se listi z isto barvo zavihka razvrstijo skupaj. Oglejte si posnetke zaslona:

 Razvrstite zavihke delovnega lista glede na barvo zavihkov z neverjetno funkcijo

z Kutools za ExcelJe Razvrsti liste lahko hitro in enostavno razvrstite zavihke delovnega lista po barvah.

Po namestitvi Kutools za Excel, naredite tako:

1. Kliknite Kutools dodatek > Delovni list > Razvrsti liste iti na Razvrsti liste v pogovornem oknu v Razvrsti liste pogovorno okno, kliknite Razvrstitev barv gumb v desnem podoknu, glejte posnetek zaslona:

2. Nato kliknite Ok gumb, bodo listi z enako barvo zavihka razporejeni skupaj, kot je prikazano na spodnjih posnetkih zaslona:

---

Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

• Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
• Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
• Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
• Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
• Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
• Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
• Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
• Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
• Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...

Preberite več ... Popolne funkcije 30-dnevnega brezplačnega preskusa ... Nakup ... 

---

Zavihek Office - v Office vnese vmesnik z zavihki in vam olajša delo

• Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
• Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
• Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!

Preberite več ... Popolne funkcije 30-dnevnega brezplačnega preskusa ... Nakup ...