Sortiranje podataka u Excel-u je vrlo korisna funkcija, ali je treba koristiti s oprezom. Ako velika tablica sadrži složene formule i funkcije, onda je bolje izvršiti operaciju sortiranja na kopiji ove tablice.

Prvo, u formulama i funkcijama ciljanje u linkovima može biti narušeno i tada će rezultati njihovih proračuna biti pogrešni. Drugo, nakon ponovljenog sortiranja, možete izmešati podatke tabele tako da će se teško vratiti u prvobitni oblik. Treće, ako tablica sadrži spojene ćelije, trebali biste ih pažljivo odvojiti, jer ovaj format nije prihvatljiv za sortiranje.

Sortiranje podataka u Excel-u

Koje alate Excel ima za sortiranje podataka? Da bismo dali potpuni odgovor na ovo pitanje, razmotrimo ga na konkretnim primjerima.

Priprema tabele za pravilno i sigurno sortiranje podataka:

Sada naša tabela ne sadrži formule, već samo rezultate njihovog izračuna. Spojene ćelije su također odspojene. Ostaje samo ukloniti višak teksta u naslovima i tabela je spremna za sigurno sortiranje.

Da biste sortirali čitavu tabelu na osnovu jedne kolone, uradite sledeće:

Podaci sortirani kroz tabelu u odnosu na kolonu „Neto profit“.



Kako sortirati kolonu u Excelu

Sada sortirajmo samo jednu kolonu bez pozivanja na druge kolone i cijelu tabelu:

Kolona se sortira nezavisno od ostalih kolona u tabeli.

Sortiraj prema boji ćelije u Excelu

Kada kopiramo tabelu na poseban list, prenosimo samo njene vrijednosti koristeći paste special. Ali mogućnosti sortiranja nam omogućavaju da sortiramo ne samo po vrijednostima, već čak i po bojama fonta ili bojama ćelija. Stoga, također moramo prenijeti formate podataka. Da biste to učinili:

Kopija tablice sada sadrži vrijednosti i formate. Hajde da sortiramo po boji:

1. Odaberite tabelu i odaberite alat "Podaci" - "Sortiraj".
2. U opcijama sortiranja ponovo označite polje “Moji podaci sadrže naslove kolona” i naznačite: “Kolona” – Neto profit; “Sortiranje” – Boja ćelije; “Narudžba” – crvena, na vrhu. I kliknite OK.

Na vrhu sada imamo najlošije podatke o neto dobiti.

Napomena. Zatim možete odabrati raspon A4:F12 u ovoj tabeli i ponoviti drugi korak ovog odjeljka, samo navedite ružičastu boju na vrhu. Tako će prvo ići ćelije sa bojom, a zatim one normalne.

Hajde da sortiramo tabelu koja se sastoji od 2 kolone koristeći formule. Sortiraćemo po jednoj od kolona tabele (rešićemo 2 problema: sortiranje tabele po brojkama i sortiranje po tekstualnoj koloni). Postavićemo formule za sortiranje tako da kada se novi podaci dodaju izvornoj tabeli, sortirana tabela se dinamički menja. Ovo će vam omogućiti da uvijek imate sortiranu tabelu bez intervencije korisnika. Uradićemo i sortiranje na dva nivoa: prvo po numeričkom, zatim (za ponavljanje brojeva) po tekstualnoj koloni.

Neka postoji tabela koja se sastoji od 2 kolone. Jedna kolona je tekst: Lista voća; a drugi je numerički Obim prodaje(vidi primjer fajla).

Problem 1 (Sortiranje tabele po brojčanoj koloni)

Trebate sortirati redove tabele prema sadržaju numeričke kolone (prema obimu prodaje). Pretpostavlja se da korisnik stalno popunjava redove tabele, pa je potrebno pisati formule uzimajući u obzir dodane vrednosti.

Radi jasnoće, vrijednosti u koloni Obim prodaje označeno sa (). Ponovljene vrijednosti su također označene žutom bojom.

Napomena: Problem sortiranja jedne kolone (liste) riješen je u člancima i.

Rješenje1

Ako je zajamčeno da numerički stupac ne sadrži nikakve vrijednosti, onda je problem lako riješiti:

• Sortirajte numerički stupac pomoću funkcije LARGE() (pogledajte članak);
• Funkcija VLOOKUP() ili gomila funkcija INDEX()+MACH() odaberite vrijednosti iz tekstualne kolone prema odgovarajućoj numeričkoj vrijednosti.

Međutim, u stvarni problemi numerički stupac može sadržavati ponavljanja, a budući da funkcija VLOOKUP() u slučaju ponavljanja uvijek odabire samo prvu vrijednost s vrha (pogledajte članak), ovaj pristup nije prikladan (nazivi plodova će biti prikazani pogrešno ).

Stoga će se mehanizam sortiranja morati drugačije implementirati.

INDEX(Prodaja;
OKRUGLA(REMAT(NAJVEĆA(
---(COUNTIF(Prodaja;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-ROW($E$6));1)*1000;0)
)

Ova formula sortira kolonu Obim prodaje(dinamički raspon Prodaja) silazno. Praznine u izvornoj tabeli nisu dozvoljene. Broj redova u izvornoj tabeli mora biti manji od 1000.

Pogledajmo formulu detaljnije:

• Formula COUNTIF(Prodaja;"<"&Продажи) vraća niz (4:5:0:2:7:1:3:5). To znači da je broj 64 (iz ćelije B7 originalnu tabelu, tj. prvi broj iz raspona Prodaja) više od 4 vrijednosti iz istog raspona; broj 74 (iz mob B8 originalnu tabelu, tj. drugi broj iz raspona Prodaja) više od 5 vrijednosti iz istog raspona; sljedeći broj 23 je najmanji (nije veći od bilo koga) itd.
• Sada pretvorimo gornji niz cijelih brojeva u niz brojeva sa razlomkom, gdje će razlomak sadržavati broj pozicije broja u nizu: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,00 ). Ovo se implementira izrazom &","&REPEAT("0";3-DUŽINA(RED(Prodaja)-RED($E$6)))&RED(Prodaja)-RED($E$6)) Upravo u ovom dijelu formule postoji ograničenje od ne više od 1000 redova u izvornoj tabeli (vidi gore). Može se lako promijeniti po želji, ali to je besmisleno (pogledajte odjeljak o brzini računanja ispod).
• Funkcija LARGE() sortira gornji niz.
• Funkcija ROD() vraća razlomljeni dio broja, a to je položaj brojevi/1000, kao što je 0,005.
• Funkcija ROUND(), nakon množenja sa 1000, zaokružuje na cijeli broj i vraća broj pozicije. Sada svi brojevi pozicija odgovaraju brojevima kolona Obim prodaje sortirano u opadajućem redoslijedu.
• Funkcija INDEX(), kojoj je dat broj pozicije, vraća broj koji joj odgovara.

Slična formula se može napisati za prikaz vrijednosti u stupcu Voće=INDEX(Voće,OKRUGLO(...))

U datoteci primjera, zbog razmatranja brzine izračunavanja (vidi dolje), isti tip dijela formule, tj. sve unutar funkcije ROUND() stavlja se u zasebnu kolonu J . Stoga konačne formule u sortiranoj tabeli izgledaju ovako: =INDEX(Voće,J7) I =INDEX(Prodaja,J7)

Također, promjenom funkcije LARGE() u SMALL() u formuli niza, dobijamo sortiranje u rastućem redoslijedu.

Radi jasnoće, vrijednosti u koloni Obim prodaje istaknuto sa ( Početna/ Stilovi/ Uslovno oblikovanje/ Histogrami). Kao što vidite, sortiranje radi.

Testiranje

Sada dodajmo novi red originalnoj tabeli. U dinamički sortiranim tabelama moramo dobiti odgovarajuće sortiranje.

1. U ćeliju A15 izvorna tabela unesite riječ Šargarepa;
2. Na ćeliju B15 enter Obim prodajeŠargarepa = 25;
3. Nakon unosa vrijednosti u kolone D I E automatski će se prikazati tabela sortirana u opadajućem redoslijedu;
4. U sortiranoj tabeli, novi red će biti prikazan pretposlednji.

Brzina izračunavanja formule

Na "prosječnom" računaru u smislu performansi, preračunavanje para takvih formule niza, nalazi se u 100 redova, praktično je nevidljiv. Za tabele sa 300 redova, vreme ponovnog izračunavanja traje 2-3 sekunde, što je nezgodno. Ili trebate onemogućiti automatsko ponovno izračunavanje listova ( Formule/Izračuni/Opcije izračuna) i povremeno pritiskajte taster F9, ili napustiti upotrebu formula niza, zamjenjujući ih stupcima s odgovarajućim formulama, ili potpuno napustiti dinamičko sortiranje u korist korištenja standardnih pristupa (pogledajte sljedeći odjeljak).

Alternativni pristupi sortiranju tabela

Hajde da sortiramo redove izvorne tabele koristeći standardni filter (odaberite zaglavlja izvorne tabele i kliknite CTRL+SHIFT+L). Izaberite željeno sortiranje sa padajuće liste.

Dobit ćemo verziju tablice identičnu našoj, ali kada dodajemo nove vrijednosti u tablicu, morat ćemo ponovo primijeniti filter.

Možete koristiti i alat za sortiranje ( Podaci/Sortiraj i Filtriraj/Sortiraj). Da biste to učinili, morate odabrati sve vrijednosti izvorne tablice, ne uključujući zaglavlje, pozvati alat za sortiranje, odabrati kolonu po kojoj želite sortirati i opciju sortiranja.

Dobit ćemo verziju tablice identičnu našoj, ali kada dodajemo nove vrijednosti, također ćemo morati ponovo primijeniti filter.

Kao iu prethodnom zadatku, pretpostavimo da postoje ponavljanja u koloni po kojoj se vrši sortiranje (ponavljaju se nazivi voća).

Da biste sortirali tabelu, moraćete da kreirate 2 servisne kolone (D i E).

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

Ova formula je analogna tekstualnim vrijednostima (pozicija vrijednosti u odnosu na druge vrijednosti na listi). Tekstualna vrijednost niža u abecedi ima viši "rang". Na primjer, vrijednost Apples odgovara maksimalnom "rangu" od 7 (uključujući ponavljanja).

U kolonu E unesite uobičajenu formulu:

=COUNTIF($D$6:D6,D7)+D7

Ova formula uzima u obzir ponavljanja tekstualnih vrijednosti i prilagođava "rang". Sada različite vrijednosti jabuka odgovaraju različitim "rangovima" - 7 i 8. Ovo vam omogućava da prikažete listu sortiranih vrijednosti. Da biste to učinili, koristite formulu (kolona G):

=INDEX($B$7:$B$14,PODRŽAVA(RED()-RED($G$6),$E$7:$E$14,0))

Slična formula će prikazati odgovarajući obim prodaje (kolona H).

Problem 2.1 (razvrstavanje na dva nivoa)

Sada ponovo sortirajmo originalnu tabelu prema obimu prodaje. Ali sada za ponavljajuće vrijednosti (u koloni A postoje tri vrijednosti 74), prikazat ćemo odgovarajuće vrijednosti po abecednom redu.

Da bismo to uradili, koristićemo rezultate zadataka 1.1 i 2.

Detalji u primjeru datoteke na listu Zadatak2.

Za praktičnost rada sa velikim količinama podataka u tabelama, one se stalno moraju organizirati prema određenom kriteriju. Osim toga, da bi se ispunile određene svrhe, ponekad nije potreban cijeli niz podataka, već samo pojedinačni redovi. Stoga, kako se ne bismo zbunili u ogromnoj količini informacija, racionalno rješenje bi bilo organizirati podatke i filtrirati ih iz drugih rezultata. Hajde da saznamo kako sortirati i filtrirati podatke u programu Microsoft Excel.

Sortiranje je jedan od najprikladnijih alata za rad u Microsoft Excel-u. Koristeći ga, možete urediti redove tabele po abecednom redu, prema podacima koji se nalaze u ćelijama kolone.

Možete sortirati podatke u Microsoft Excel-u pomoću dugmeta „Sortiraj i filtriraj“ koje se nalazi na kartici „Početna“ na traci u bloku alata „Uređivanje“. Ali prvo moramo kliknuti na bilo koju ćeliju kolone po kojoj ćemo sortirati.

Na primjer, u tabeli ispod, zaposleni bi trebali biti razvrstani po abecednom redu. Idite na bilo koju ćeliju u koloni "Naziv" i kliknite na dugme "Sortiraj i filtriraj". Da biste sortirali imena po abecednom redu, odaberite "Sort from A to Z" sa liste koja se pojavi.

Kao što vidite, svi podaci u tabeli su raspoređeni prema abecednom popisu prezimena.

Da biste sortirali obrnutim redoslijedom, u istom meniju odaberite dugme Sortiraj od Z do A.”

Lista se obnavlja obrnutim redoslijedom.

Treba napomenuti da je ova vrsta sortiranja naznačena samo za format tekstualnih podataka. Na primjer, kod formata broja sortiranje je navedeno kao „Od minimuma do maksimuma” (i obrnuto), a kod formata datuma – „Od starog do novog” (i obrnuto).

Prilagođeno sortiranje

Ali, kao što vidimo, kod ovih tipova sortiranja po jednoj vrijednosti, podaci koji sadrže imena iste osobe su raspoređeni u rasponu slučajnim redoslijedom.

Što ako želimo sortirati imena po abecednom redu, ali na primjer, ako se ime podudara, provjerite jesu li podaci sortirani po datumu? Da bismo to uradili, kao i da bismo koristili neke druge funkcije, sve u istom meniju „Sortiranje i filtriranje“, potrebno je da odemo na stavku „Prilagođeno sortiranje...“.

Nakon toga otvara se prozor postavki sortiranja. Ako vaša tabela ima zaglavlja, uvjerite se da u ovom prozoru postoji kvačica pored opcije „Moji podaci sadrže zaglavlja“.

U polju „Kolona“ navedite naziv kolone po kojoj će se izvršiti sortiranje. U našem slučaju, ovo je kolona “Ime”. Polje „Sortiranje“ označava po kojoj vrsti sadržaja će se sortirati. Postoje četiri opcije:

• vrijednosti;
• Boja ćelije;
• Boja fonta;
• Ikona ćelije.

Ali, u velikoj većini slučajeva koristi se stavka “Vrijednosti”. Podešen je po defaultu. U našem slučaju koristićemo i ovu konkretnu tačku.

U koloni „Red“ treba da naznačimo kojim će redosledom podaci biti raspoređeni: „Od A do Z“ ili obrnuto. Odaberite vrijednost “Od A do Z”.

Dakle, postavili smo sortiranje po jednoj od kolona. Da biste konfigurisali sortiranje po drugoj koloni, kliknite na dugme „Dodaj nivo“.

Pojavljuje se još jedan skup polja koja se moraju popuniti da biste sortirali po drugoj koloni. U našem slučaju, prema koloni „Datum“. Budući da je format datuma postavljen u ovim ćelijama, u polju „Narudžba“ postavljamo vrijednosti ne „Od A do Z“, već „Od starog do novog“ ili „Od novog do starog“.

Na isti način, u ovom prozoru možete konfigurirati, ako je potrebno, sortiranje po drugim kolonama prema prioritetu. Kada su sva podešavanja završena, kliknite na dugme “OK”.

Kao što vidite, sada su u našoj tabeli svi podaci razvrstani, prije svega, po imenima zaposlenih, a zatim po datumima isplate.

Ali to nisu sve mogućnosti prilagođenog sortiranja. Ako želite, u ovom prozoru možete konfigurirati sortiranje ne po stupcima, već po redovima. Da biste to učinili, kliknite na dugme "Opcije".

U prozoru sa opcijama sortiranja koji se otvori, premjestite prekidač iz pozicije „Range Rows” u poziciju „Range Columns”. Kliknite na dugme “OK”.

Sada, po analogiji s prethodnim primjerom, možete unijeti podatke za sortiranje. Unesite podatke i kliknite na dugme “OK”.

Kao što vidite, nakon ovoga, kolone su zamijenjene mjestima, prema unesenim parametrima.

Naravno, za našu tabelu, uzetu kao primer, korišćenje sortiranja promenom lokacije kolona nije posebno korisno, ali za neke druge tabele ova vrsta sortiranja može biti vrlo prikladna.

Filter

Osim toga, Microsoft Excel ima funkciju filtriranja podataka. Omogućava vam da ostavite vidljivim samo one podatke koje smatrate potrebnima, a sakrijete ostale. Ako je potrebno, skriveni podaci se uvijek mogu vratiti u vidljivi način rada.

Da biste koristili ovu funkciju, stanite na bilo koju ćeliju u tabeli (po mogućnosti u zaglavlju), ponovo kliknite na dugme „Sortiraj i filtriraj“ u bloku alata „Uređivanje“. Ali, ovog puta, u meniju koji se pojavi izaberite stavku „Filter“. Također možete jednostavno pritisnuti kombinaciju tipki Ctrl+Shift+L umjesto ovih radnji.

Kao što vidite, u ćelijama s nazivima svih stupaca pojavljuje se ikona u obliku kvadrata u koji je upisan obrnuti trokut.

Kliknite na ovu ikonu u koloni po kojoj ćemo filtrirati. U našem slučaju, odlučili smo filtrirati po imenu. Na primjer, moramo ostaviti podatke samo zaposlenika Nikolaev. Stoga uklanjamo kvačice sa imena svih ostalih zaposlenih.

Kada je postupak završen, kliknite na dugme “OK”.

Kao što vidite, u tabeli ostaju samo redovi sa imenom radnika Nikolajeva.

Hajde da zakomplikujemo zadatak i ostavimo u tabeli samo podatke koji se odnose na Nikolaev za treći kvartal 2016. Da biste to učinili, kliknite na ikonu u ćeliji "Datum". U listi koja se otvori poništite mjesece „maj“, „jun“ i „oktobar“, jer ne pripadaju trećem kvartalu, i kliknite na dugme „OK“.

Kao što vidite, ostaju samo podaci koji su nam potrebni.

Da biste uklonili filter za određenu kolonu i prikazali skrivene podatke, ponovo kliknite na ikonu koja se nalazi u ćeliji sa naslovom ove kolone. U meniju koji se otvori kliknite na stavku „Ukloni filter iz...“.

Ako želite resetirati filtar za tablicu u cjelini, tada morate kliknuti gumb "Sortiraj i filtriraj" na traci i odabrati stavku "Obriši".

Ako trebate potpuno ukloniti filtar, tada, kao i prilikom pokretanja, trebate odabrati stavku "Filter" u istom izborniku ili upisati tipkovnu prečicu Ctrl+Shift+L.

Osim toga, treba napomenuti da nakon što smo omogućili funkciju „Filter“, kada kliknete na odgovarajuću ikonu u ćelijama zaglavlja tabele, funkcije sortiranja o kojima smo gore govorili postaju dostupne u meniju koji se pojavi: „Sortiraj od A do Z” , “Poređaj od Z do A” i “Poređaj po boji”.

Pametan sto

Sortiranje i filtriranje se također može aktivirati pretvaranjem područja podataka s kojim radite u takozvanu “pametnu tablicu”.

Postoje dva načina za kreiranje pametnog stola. Da biste koristili prvi od njih, odaberite cijelo područje tablice i, kada ste na kartici „Početna“, kliknite na dugme „Formatiraj kao tabelu“ na traci. Ovo dugme se nalazi u bloku alata „Stilovi“.

Nakon toga otvara se dijaloški okvir u kojem možete promijeniti koordinate tablice. Ali, ako ste prethodno ispravno odabrali područje, onda ne morate ništa drugo raditi. Glavna stvar je osigurati da postoji kvačica pored parametra "Tabela sa zaglavljima". Zatim samo kliknite na dugme “OK”.

Ako se odlučite za drugu metodu, tada morate odabrati i cijelo područje tablice, ali ovaj put idite na karticu "Umetanje". Dok ste ovdje, na traci u bloku alata „Tabele“, kliknite na dugme „Tabela“.

Nakon ovoga, kao i prošli put, otvoriće se prozor u kojem možete podesiti koordinate položaja tabele. Kliknite na dugme “OK”.

Bez obzira koju metodu koristite za kreiranje „pametne tabele“, na kraju ćete imati tabelu u ćelijama zaglavlja u kojoj će ikone filtera koje smo ranije opisali već biti instalirane.

Kada kliknete na ovu ikonu, sve iste funkcije će biti dostupne kao kada pokrenete filter na standardni način preko dugmeta „Sortiraj i filtriraj“.

Kao što vidite, alati za sortiranje i filtriranje, kada se pravilno koriste, mogu korisnicima znatno olakšati rad sa tabelama. Pitanje njihove upotrebe postaje posebno relevantno ako tabela sadrži veoma veliki niz podataka.

Sortiranje podataka u Excel-u je sređivanje tabelarnih podataka po željenom redoslijedu, na primjer, rastućim (od najmanjeg prema najvećem) ili opadajućim (od najvećeg prema najmanjem). Numeričke i tekstualne vrijednosti, vrijednosti datuma i vremena i formati su sortirani. Sortiranje podataka je moguće i po kolonama i po redovima. Skriveni redovi i kolone moraju biti prikazani prije sortiranja.

Vrste podataka koje treba sortirati i redoslijed sortiranja

Sortirajte numeričke vrijednosti u Excelu

Uzlazno sortiranje numeričkih vrijednosti je raspored vrijednosti u kojem su vrijednosti raspoređene od najmanje do najveće (minimalne do maksimalne).

U skladu s tim, sortiranje numeričkih vrijednosti u opadajućem redoslijedu je raspored vrijednosti od najveće do najmanje (od maksimuma do minimuma).

Sortiranje tekstualnih vrijednosti u Excelu

"Sortiranje od A do Z" - sortiranje podataka uzlaznim redoslijedom;

"Sortiraj od Z do A" - sortiranje podataka u opadajućem redoslijedu.

Za sortiranje tekstualnih vrijednosti po abecednom redu, ove vrijednosti se uspoređuju jedna s drugom. Kao što znate, prilikom spremanja teksta, računar koristi šemu u kojoj svaki znak ima svoj jedinstveni broj, koji se zove kod karaktera. Ovi kodovi se uspoređuju kako bi se utvrdilo koja je vrijednost teksta veća, a koja manja.

Tekstualne vrijednosti mogu sadržavati abecedne, numeričke i posebne znakove. U ovom slučaju, brojevi se mogu sačuvati iu numeričkom i u tekstualnom formatu. Brojevi pohranjeni u numeričkom formatu manji su od brojeva pohranjenih u tekstualnom formatu. Da biste ispravno sortirali tekstualne vrijednosti, svi podaci moraju biti sačuvani u tekstualnom formatu. Osim toga, kada se tekstualni podaci iz drugih aplikacija umetnu u ćelije, podaci mogu sadržavati razmake na početku. Prije nego što počnete s sortiranjem, morate ukloniti vodeće razmake (ili druge znakove koji se ne mogu ispisati) iz podataka koji se sortiraju, inače sortiranje neće biti izvedeno ispravno.

Možete sortirati tekstualne podatke na način koji razlikuje velika i mala slova. Da biste to učinili, morate označiti polje "Match case" u opcijama sortiranja.

Obično velika slova imaju manje brojeve od malih slova.

Sortirajte vrijednosti datuma i vremena

"Sort Old to New" je sortiranje vrijednosti datuma i vremena od najranije vrijednosti do najnovije vrijednosti.

"Sort New to Old" je sortiranje vrijednosti datuma i vremena od najnovije vrijednosti do najranije vrijednosti.

Formati za sortiranje

Microsoft Excel 2007 i novije verzije omogućavaju sortiranje formatiranjem. Ova metoda sortiranja se koristi kada je raspon ćelija formatiran pomoću boje popune ćelije, boje fonta ili skupa ikona. Boje ispune i fonta u Excel-u imaju svoje kodove, a upravo se ti kodovi koriste prilikom sortiranja formata.

Sortiraj po prilagođenoj listi

Tabelarni podaci se mogu sortirati po prilagođenim listama, kao što su lista meseci, lista dana u nedelji, lista strukturnih podela preduzeća, lista broja zaposlenih i tako dalje. Excel ima mogućnost kreiranja vlastitih lista za sortiranje podataka. Sa ovim redoslijedom sortiranja, podaci koji se sortiraju i vrijednosti liste moraju se podudarati.

Opcije sortiranja

Poredaj po koloni

U bilo kojoj verziji Excela, sortiranje po koloni je postavljeno prema zadanim postavkama, odnosno vrijednosti ćelija odabrane kolone su raspoređene po željenom redoslijedu, a redovi raspona se mijenjaju ovisno o poziciji ćelije u sortiranoj koloni.Da biste sortirali tabelu po koloni, samo postavite marker selekcije u bilo koju ćeliju tabele i kliknite na jednu od ikona koje simbolizuju sortiranje od minimuma do maksimuma ili od maksimuma do minimuma. Tabela će biti sortirana po koloni u kojoj se nalazi marker za odabir.

Poredaj po nizu

U postavkama programa možete promijeniti sortiranje po koloni u sortiranje po redu. Ako je sortiranje po redu postavljeno, tada se vrijednosti ćelija odabranog reda raspoređuju određenim redoslijedom, a stupci se zamjenjuju ovisno o pozicijama ćelija u sortiranom redu.

Razvrstavanje na više nivoa

Dakle, ako sortirate po koloni, onda se redovi zamjenjuju, ako se podaci sortiraju po redu, onda se stupci zamjenjuju.

Excel vam omogućava da sortirate podatke ne samo po jednoj koloni ili jednom redu, već kreiranjem različitog broja nivoa sortiranja. U programu Excel 2007, na primjer, postoji 64 nivoa sortiranja koji se mogu dodati, izbrisati, kopirati i zamijeniti.

Između ostalog, prilikom sortiranja možete uzeti u obzir velika ili mala slova.

Dodatak za sortiranje podataka u Excel-u

Microsoft Excel korisnicima pruža prilično veliki skup standardnih alata za sortiranje vrijednosti različitih tipova, ali postoje zadaci koji su ili nezgodni ili dugotrajni za rješavanje pomoću standardnih alata, na primjer, sortiranje svakog reda/svakog stupca u takvom način na koji se sortiranje vrši samo unutar reda/kolone i ne utiče na susjedne ćelije.

Sortiranje podataka u Excel-u je alat za predstavljanje informacija u obliku prilagođenom korisniku.

Numeričke vrijednosti se mogu sortirati u rastućem i opadajućem redoslijedu, tekstualne vrijednosti mogu se sortirati po abecedi i obrnutim redoslijedom. Dostupne su opcije - po boji i fontu, bilo kojim redoslijedom, prema nekoliko uslova. Kolone i redovi su sortirani.

Redoslijed sortiranja u Excel-u

Postoje dva načina da otvorite meni za sortiranje:

1. Kliknite desnim tasterom miša na tabelu. Odaberite „Sortiranje” i metodu.
2. Otvorite karticu "Podaci" - dijaloški okvir "Sortiranje".

Često korištene metode sortiranja predstavljene su jednim gumbom na traci zadataka:

Sortiranje tabele po jednoj koloni:

1. Da bi program ispravno izvršio zadatak, odaberite potrebnu kolonu u rasponu podataka.
2. Dalje djelujemo ovisno o zadatku. Ako trebate izvršiti jednostavno sortiranje uzlaznim/silaznim redoslijedom (abecednim redom ili obrnuto), tada samo kliknite na odgovarajuće dugme na traci zadataka. Kada raspon sadrži više od jedne kolone, Excel otvara dijaloški okvir koji izgleda ovako: Da bi vrijednosti u redovima bile dosljedne, odaberite akciju "automatski proširi odabrani raspon". U suprotnom, samo će odabrana kolona biti sortirana i struktura tabele će biti prekinuta.

Ako odaberete cijelu tabelu i sortirate, prva kolona će biti sortirana. Podaci u redovima će postati konzistentni sa pozicijom vrijednosti u prvoj koloni.

Sortiraj prema boji ćelije i fontu

Excel pruža korisniku bogate opcije formatiranja. Stoga možete raditi s različitim formatima.

Kreirajmo stupac "Ukupno" u tabeli za obuku i ispunimo ćelije vrijednostima s različitim nijansama. Hajde da sortiramo po boji:

1. Odaberite kolonu - desni gumb miša - "Sortiraj".
2. Na ponuđenoj listi odaberite "Prvo ćelije s označenom bojom".
3. Slažemo se da "automatski proširimo asortiman."

Program je sortirao ćelije po akcentu. Korisnik može samostalno odabrati redoslijed sortiranja boja. Da biste to učinili, odaberite "Prilagođeno sortiranje" na popisu opcija alata.

U prozoru koji se otvori unesite potrebne parametre:

Ovdje možete odabrati redoslijed prikazivanja ćelija različitih boja.

Podaci se sortiraju po fontu koristeći isti princip.

Sortiranje u Excelu po više kolona

Kako postaviti sekundarni redoslijed sortiranja u Excelu? Da biste riješili ovaj problem, morate postaviti nekoliko uvjeta sortiranja.

1. Otvorite meni „Prilagođeno sortiranje“. Dodjeljujemo prvi kriterij.
2. Kliknite na dugme "Dodaj nivo".
3. Pojavljuju se prozori za unos podataka za sljedeći uvjet sortiranja. Hajde da ih popunimo.

Program vam omogućava da dodate nekoliko kriterija odjednom kako biste izvršili sortiranje po posebnom redoslijedu.

Sortiranje redova u Excelu

Podrazumevano, podaci se sortiraju po kolonama. Kako sortirati po redovima u Excelu:

1. U dijalogu Prilagođeno sortiranje kliknite na dugme Opcije.
2. U meniju koji se otvori odaberite "Raspon kolone".
3. Kliknite OK. U prozoru „Sortiranje“ će se pojaviti polja za popunjavanje uslova po redu.

Ovako sortirate tabelu u Excelu prema nekoliko parametara.

Nasumično sortiranje u Excel-u

Ugrađene opcije sortiranja ne dozvoljavaju vam da nasumično rasporedite podatke u kolonu. Funkcija RAND će se nositi s ovim zadatkom.

Na primjer, trebate organizirati skup određenih brojeva po slučajnom redoslijedu.

Postavite kursor u susjednu ćeliju (lijevu ili desnu, nije važno). Unesite RAND() u traku formule. Pritisnite Enter. Kopiramo formulu u cijeli stupac - dobivamo skup slučajnih brojeva.

Sada sortirajmo rezultirajuću kolonu u rastućem/opadajućem redoslijedu - vrijednosti u originalnom rasponu automatski će biti raspoređene po slučajnom redoslijedu.

Dinamičko sortiranje tablica u MS Excel-u

Ako primijenite standardno sortiranje na tablicu, ono neće biti relevantno kada se podaci promijene. Moramo biti sigurni da se vrijednosti automatski sortiraju. Koristimo formule.

1. Postoji skup prostih brojeva koje je potrebno sortirati uzlaznim redoslijedom.
2. Postavite kursor u susjednu ćeliju i unesite formulu: =SMALL(A:A;ROW(A1)). Tako je. Navodimo cijeli stupac kao raspon. I kao koeficijent - funkcija ROW sa vezom na prvu ćeliju.
3. Promijenimo broj 7 u 25 u originalnom rasponu - promijenit će se i "razvrstavanje" u rastućem redoslijedu.

Ako trebate izvršiti dinamičko sortiranje u opadajućem redoslijedu, koristite funkciju LARGE.

Da biste dinamički sortirali tekstualne vrijednosti, trebat će vam formule niza.

1. Početni podaci su lista određenih imena po slučajnom redoslijedu. U našem primjeru, lista voća.
2. Odaberite kolonu i dajte joj naziv "Voće". Da biste to učinili, u polje za imena koje se nalazi pored trake formule unesite ime koje nam je potrebno da ga dodijelimo odabranom rasponu ćelija.
3. U sljedećoj ćeliji (u primjeru - u B5) pišemo formulu: Pošto je ovo formula niza, pritisnite kombinaciju Ctrl + Shift + Enter. Formulu množimo preko cijele kolone.
4. Ako će se redovi dodati originalnoj koloni, onda unesite malo izmijenjenu formulu: Dodajmo još jednu vrijednost "pomelo" rasponu "voće" i provjerimo:

Preuzmite formule za sortiranje podataka u Excelu

Nakon toga, kada dodate podatke u tabelu, proces sortiranja će se izvršiti automatski.

Rad sa tabelama u mnogim slučajevima zahteva analizu podataka koje one sadrže. Excel pruža moćne alate za obradu informacija, mogućnost pravljenja statističkih izvještaja i odabira podataka prema uvjetima. Ali ponekad je dovoljno samo sortirati elemente da bi se problem riješio.

Brojevi za naručivanje

Sortiranje brojeva u rastućem ili opadajućem redoslijedu je uobičajen zadatak. Pomaže u izgradnji numeričkih parametara - broja proizvoda i kupaca, iznosa prodaje i prihoda, te prezentiranja informacija u vizualnijem obliku. Hajde da sortiramo ove podatke u Excel-u.

Na primjer, tabela s podacima turističke kompanije pohranjuje informacije o troškovima boravka u hotelu. Cijena ovisi o vrsti sobe i ponuđenom obroku. Turista je zainteresiran za pronalaženje najekonomičnijeg smještaja, pa treba sortirati podatke po rastućoj cijeni.

Da biste to učinili, odaberite kolonu tabele "Cijena, rub". i idite na karticu "Početna". U bloku „Uređivanje“ nalazi se grupa alata „Sortiranje i filtriranje“. U meniju izaberite stavku „Sortiraj uzlaznim redosledom“.

Program, nakon što je otkrio prisustvo povezanih kolona, ​​pojašnjava da li je potrebno sortirati u Excelu samo po odabranom polju ili proširiti raspon.

Odabiremo “Proširi”, budući da nas zanima poređanje kompletnih zapisa tabele, i kliknemo “Sortiraj”. Unosi su raspoređeni rastućim redoslijedom troškova života.

Ova operacija je još lakša ako je tabela oblikovana po stilu. U tom slučaju morate pozvati kontekstni meni kolone klikom na padajuću listu u zaglavlju kolone. Izbornik sadrži slične komande koje možete koristiti za sortiranje u programu Excel.

Da biste bili sigurni da su brojevi pravilno sortirani, obratite pažnju na ispravan format podataka u koloni. Ako se vrijednosti unesu u tabelu kao tekst, a ne kao brojevi, poravnanje se neće dogoditi očekivanim redoslijedom. U tabeli je prikazan primjer sortiranja brojeva 10, 11, 100, 15, 110, 132 ovisno o formatu podataka.

Uređivanje teksta

U Excelu, sortiranje stupaca koji sadrže tekst je isto kao i sortiranje brojeva. Pokrenimo ovu naredbu u koloni tabele cijena soba u hotelu “Hrana” koju već poznajemo. Odaberite kolonu klikom na njeno zaglavlje i otvorite alatnu traku „Uređivanje“ na kartici „Početna“. Sada u padajućem izborniku, umjesto sortiranja naredbi u rastućem i opadajućem redoslijedu, postoje naredbe za sortiranje od A do Z i obrnuto.

Konstruiramo ćelije od A do Z, što opet ukazuje na proširenje opsega rada.

Prilikom naručivanja podataka u nizu vodite računa da vrijednosti ne sadrže vodeće razmake, jer će to poremetiti željeni redoslijed ćelija. Ovaj problem se javlja kada se informacije učitavaju u datoteku iz drugog izvora: baze podataka, aplikacije treće strane ili druge datoteke.

Za formatirane tabele, sortiranje u Excelu je još lakše. Kada odete u kontekstni meni zaglavlja kolone, vidjet ćete i komande za građenje ćelija redom od A do Z i obrnuto.

Datumi naručivanja

Sve gore navedene operacije koje se odnose na brojeve i informacije o nizovima također se koriste za konstruiranje datuma i vremena. Jedina razlika je u nazivu naredbe za ovu vrstu podataka ona se zove “Sort from old to new” i obrnuto. Primjer je prikazan ispod.

Za datume vrijedi isto pravilo kao i za brojeve: kako biste bili sigurni da je redoslijed ispravan, uvjerite se da su informacije koje sortirate predstavljene u formatu datuma, a ne u tekstualnom formatu.

Prilagođene liste

Ali nije uvijek moguće napraviti potrebnu listu korištenjem standardnih alata; Na primjer, u sedmičnoj listi obaveza želimo organizirati zadatke po danima u sedmici.

Za obavljanje ovog sortiranja, program pruža mogućnost korištenja prilagođenih lista. MS Excel ima nekoliko unapred podešenih lista, od kojih je jedna lista dana u nedelji. Da biste ih pregledali, idite na karticu Podaci i pronađite Excel alatnu traku za sortiranje i filtriranje. Dugme „Sortiraj“ otvara prozor za izvođenje operacija na nekoliko kolona.

U polju „Narudžba“ odaberite element „Prilagođena lista“. Odaberite dane u sedmici sa liste i kliknite OK.

Organizirajte po bojama i ikonama

Još jedna pogodna opcija za raspoređivanje elemenata ćelije po redoslijedu je mogućnost sortiranja po boji. U tom slučaju se mogu koristiti i boja ispune i boja fonta teksta. U tabeli obaveza označavamo radne zadatke narandžastom, porodične stvari plavom, a praznike bež žutom. I da ih rasporedimo po vrstama.

Da biste to učinili, idite na prilagođeno sortiranje. Pored gore opisane metode, možete koristiti i meni panela „Uređivanje“ na kartici „Početna“. Odaberite element "Prilagođeno sortiranje" sa liste naredbi "Sortiranje i filtriranje".

Odaberite polje "Zadatak", tip "Boja ćelije" i postavite gornju boju na narandžastu. Kako bismo osigurali da su kućni poslovi na dnu liste, dodajemo još jedan nivo i za iste parametre postavljamo plavu boju na dno.

Ovaj metod vam omogućava da organizujete elemente tabele po ikonama. Na primjer, možemo označiti pojedinačne zadatke uzvičnikom kao važne i prikazati ih na vrhu liste.

Gore smo opisali kako sortirati podatke različitih tipova u Excelu koristeći boju ćelija i font teksta i oznake u redovima. Nadamo se da će vam ove informacije pomoći u radu s programom.

Događaj Worksheet_Change ne odgovara na ponovno izračunavanje formule.

Ako su rasponi na koje se referiraju formule daleko od tabele (ili na drugim listovima) i ne mogu biti uključeni u ciljni raspon koji se provjerava, možete koristiti događaj ponovnog izračunavanja lista:

Private Sub Worksheet_Calculate() Application.EnableEvents = False .CurrentRegion.Sort , xlDescending, Header:=xlYes Application.EnableEvents = True End Sub

Application.EnableEvents - da bismo izbjegli petlje, onemogućujemo događaje na listovima tokom sortiranja.

Sortiranje će se desiti kada se formule ponovo izračunaju. Da bi se ponovno izračunavanje dogodilo čak i kada se podaci ručno mijenjaju, potrebno je da u proizvoljnu ćeliju dodate formulu s promjenjivom funkcijom (promjenjivo - preračunato za bilo koju promjenu na listu), na primjer:

DANAS()

Nedostatak ovog rješenja je što se sortiranje aktivira kad god dođe do promjene na listu.

Ako se formule ne preračunavaju često, možete koristiti događaj aktivacije lista - Worksheet_Activate - paralelno sa Worksheet_Change