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). Postavimo formule za sortiranje tako da kada se novi podaci dodaju izvornoj tablici, sortirana tablica se dinamički mijenja. 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.

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

Rješenje1

Ako je zagarantovano 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 stvarnim problemima, numerički stupac može sadržavati ponavljanja, a budući da funkcija VLOOKUP() u slučaju ponavljanja uvijek bira samo prvu vrijednost s vrha (pogledajte članak), ovaj pristup nije prikladan (nazivi voće će biti prikazano 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) u opadajućem redoslijedu. 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 označeno sa ( Početna/ Stilovi/ Uvjetno 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/Sortiranje i Filtriranje/Sortiranje). 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 tabele 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.

Listu možete sortirati abecedno, numerički ili hronološki po najviše tri polja. Za svaku kolonu možete odrediti rastući i silazni redoslijed sortiranja.

Uzlazni redoslijed sortiranja:

Brojevi će biti sortirani od najmanjeg negativnog do najvećeg pozitivnog broja.

Vrijednosti datuma i vremena bit će sortirane od najranije do najnovije vrijednosti.

Tekst će biti poređan po abecednom redu. U ovom slučaju, numeričke vrijednosti navedene kao tekst će biti locirane prve.

Prilikom sortiranja logičkih vrijednosti u rastućem redoslijedu, prvo će se prikazati vrijednost FALSE, a zatim vrijednost TRUE.

Vrijednosti greške će biti sortirane redoslijedom kojim su naišle (iz perspektive sortiranja, sve su jednake).

Prazne ćelije će se pojaviti na kraju sortirane liste.

Silazni redoslijed sortiranja:

U slučaju opadajućeg redoslijeda sortiranja, Excel sortira zapise obrnutim redoslijedom opisanim gore. Samo prazne ćelije će i dalje biti prikazane na kraju liste.

Prilagođeni redoslijed sortiranja:

Umjesto uzlaznog i opadajućeg redoslijeda sortiranja, možete koristiti i prilagođeni redoslijed sortiranja određen određenom prilagođenom listom.

Sortiranje liste

Da biste sortirali listu, postavite pokazivač ćelije unutar liste i izvršite naredbu Podaci – Sortiraj.

Excel automatski bira listu i prikazuje dijaloški okvir Sortiranje raspona u kojem možete odrediti opcije sortiranja.

Možete postaviti sortiranje po najviše tri polja liste i odrediti drugačiji redoslijed sortiranja za svako polje.

Excel automatski prepoznaje imena polja ako se format ćelija koje sadrže imena razlikuje od formata ćelija koje sadrže podatke.

Okvir za dijalog Sort Range.

Ako izbor opsega koji je izvršio program nije u potpunosti ispravan, postavite prekidač na dnu dijaloškog okvira na željenu poziciju (Identifikujte polja prema „oznakama (prvi red opsega)” ili „oznakama kolona lista”).

Opseg i opcije sortiranja koje navedete u okviru za dijalog Sortiranje biće sačuvane i prikazane u okviru za dijalog sledeći put kada ga otvorite.

Također možete odabrati da sortirate kolone raspona umjesto redova. Da biste to učinili, kliknite na dugme „Opcije“ u dijaloškom okviru „Raspon sortiranja“ i u dijaloškom okviru „Opcije sortiranja“, u grupi „Sortiraj“, postavite prekidač na poziciju „Kolone opsega“.

26. Filtriranje podataka u Excelu.

Filtriranje podataka na listi vam omogućava da prikažete samo one zapise koji ispunjavaju određene uslove.

Autofilter

Prilikom primjene autofiltra, nazivi polja će biti dopunjeni padajućim listama iz kojih možete odabrati željenu vrijednost polja ili postaviti prilagođeni kriterij.

Umetanje automatskog filtera

1. Postavite pokazivač ćelije unutar liste.

2. U podmeniju Podaci – Filter izaberite komandu „Autofilter“. Dugmad sa strelicama će se pojaviti pored naziva polja, koje možete kliknuti da biste otvorili listu.

3. Otvorite listu za polje čiju vrijednost želite koristiti kao filter (kriterijum za odabir). Na listi će se prikazati vrijednosti ćelija odabranog polja.

4. Izaberite željenu stavku sa liste. Na ekranu će biti prikazani samo oni zapisi koji odgovaraju navedenom filteru.

5. Ako je potrebno, izaberite željeni element sa liste drugog polja. Na ekranu će biti prikazani samo oni zapisi koji odgovaraju svim navedenim uslovima filtriranja (uslovi pojedinačnih polja se kombinuju pomoću logičke operacije „I“).

Dugmad za otvaranje autofiltera za polja za koja su specificirani uslovi filtriranja obojena su plavom bojom.

Ako ste odabrali jednu ili više kolona prije izvršavanja naredbe AutoFilter, padajuće liste će se dodati samo odgovarajućim poljima.

Da biste ponovo prikazali sve unose liste na ekranu, izvršite komandu „Prikaži sve“ iz podmenija Podaci – Filter.

Možete ukloniti kriterij filtriranja za pojedinačno polje odabirom stavke „Sve“ na listi automatskog filtriranja za ovo polje.

Da biste deaktivirali funkciju automatskog filtriranja (uklonili padajuće liste), ponovo izaberite naredbu Podaci – Filter – Autofilter.

Primjena prilagođenog automatskog filtera

Koristeći prilagođeni autofilter, možete povezati pojedinačne uvjete odabira zapisa pomoću logičkih operatora.

Ubacite autofilter u listu odabirom naredbe Podaci – Filter – Autofilter.

Otvorite listu automatskih filtera za željeno polje i izaberite element (Uslov) u njemu.

U dijaloškom okviru “Custom AutoFilter” koji se otvara (slika 6.3.27.), navedite prvi kriterij.

Odaberite logički operator koji kombinira prvi i drugi kriterij.

Dijalog „Prilagođeni automatski filter”.

Možete postaviti jedan ili dva kriterija za pojedinačno polje u prilagođenom automatskom filteru. U potonjem slučaju, oni se mogu kombinirati s logičkim operatorom “AND” ili “OR”.

Postavite drugi kriterijum.

Kliknite na dugme “OK”. Excel će filtrirati zapise na osnovu kriterijuma koje odredite.

Napredni filter

Za postavljanje složenih uslova za filtriranje podataka sa liste, Excel obezbeđuje takozvani napredni filter koji pomaže korisniku.

Raspon kriterijuma

Kriterijumi se mogu postaviti na bilo koji slobodan prostor na radnom listu. U rasponu kriterija možete unijeti i kombinirati dvije vrste kriterija:

Jednostavni kriterijumi: program će uporediti sadržaj polja sa navedenim kriterijumom (slično kao kod korišćenja autofiltera).

Izračunati kriteriji: U ovom slučaju možete odrediti i vrijednosti koje vraća formula koje nisu navedene.

Kada specificirate jednostavne kriterije, prvo morate navesti imena polja u rasponu kriterija, a možete unijeti samo ona imena polja za koja specificirate kriterije.

Red(ovi) neposredno ispod reda za nazive polja je mjesto gdje specificirate kriterij. Excel ne razlikuje velika i mala slova pri određivanju kriterijuma. Možete li koristiti zamjenske znakove pri određivanju kriterija? I *.

Svi kriterijumi navedeni u jednoj liniji moraju se izvršavati istovremeno (odgovara logičkom operatoru “AND”). Da biste specificirali vezu kriterija koristeći “OR” operator, navedite kriterije u različitim redovima.

Primjena naprednog filtera

Nakon što kreirate raspon kriterija, možete pokrenuti napredni filter i filtrirati podatke liste.

1. Postavite pokazivač ćelije unutar liste. U tom slučaju, Excel će automatski prepoznati raspon liste i prikazati vezu do raspona u dijaloškom okviru.

2. Izvršiti naredbu Podaci – Filter – Napredni filter. Postavite kursor za unos u polje „Raspon uslova“ i označite odgovarajući opseg na radnom listu.

3. Zatvorite dijaloški okvir klikom na dugme “OK”. Na ekranu će se sada prikazati zapisi koji ispunjavaju navedene kriterijume.

Možete primijeniti samo jedan napredni filter po radnom listu.

Ako ne želite da se duplirani zapisi prikazuju kao rezultat primjene naprednog filtera, potvrdite izbor u polju za potvrdu Samo jedinstveni zapisi u dijalogu Napredni filter.

Da biste postavili prikaz svih unosa liste u radnom listu nakon filtriranja, pokrenite naredbu Podaci – Filtriraj – Prikaži sve.

Da bi korisniku stvorio pogodne i udobne uslove prilikom pregleda i analize informacija smeštenih u tabele, program MS Excel pruža prilično široke mogućnosti za sortiranje podataka. Sortiranje zapisa u velikoj bazi podataka u Excel-u...

Olakšava korisniku pronalaženje informacija od interesa.

Prilikom sortiranja, svi redovi baze podataka mijenjaju svoju lokaciju u skladu s algoritmom koji smo odabrali, a ostaju, naravno, nepromijenjeni.

Tri glavna algoritma za sortiranje u Excelu.

1. numeric vrijednosti se sortiraju po principu "od najmanjeg do najvećeg" ili obrnuto.

2. Kolone sa ćelijama koje sadrže tekst vrijednosti su poredane po abecednom redu u smjeru naprijed ili nazad.

3. Kolone sa ćelijama koje sadrže datumi, sortirani su po principu „od najstarijeg do najnovijeg“ ili obrnuto.

Sortiranje u Excelu 2007 i novijim verzijama može se dodatno obaviti po kriterijima formatiranja - po boji popune ćelije, po boji fonta ćelije, po skupu ikona uslovnog oblikovanja.

Nastavimo rad sa DB2 bazom podataka „Proizvodnja metalnih konstrukcija na lokaciji br. 2“, kreiranom u članku „ ».

Baza podataka obuke koja se razmatra sastoji se od samo 6 polja (kolona) i 10 zapisa (redova). Prave baze podataka obično sadrže više od desetak polja i hiljade zapisa! Pronalaženje potrebnih informacija u takvoj tabeli nije lako! Kroz prizmu takvog razumijevanja moramo sagledati naše daljnje postupke.

Dalji rad će biti strukturiran na sljedeći način: u ovom i sljedećim člancima ove serije koristit ćemo različite mogućnosti programa GOSPOĐA Excel , formuliraju trenutne odgovore na različita pitanja i zadatke koji se javljaju u praktičnim aktivnostima voditelja proizvodnog mjesta. Odnosno, riješit ćemo stvarne proizvodne probleme!

Održavanje baze podataka – prikupljanje informacija – samo po sebi ne daje ništa, naprotiv, oduzima nam neko vrijeme! Ali rezultati analize ovih informacija, koji vam omogućavaju da date tačne odgovore na razna pitanja, mogu uvelike poboljšati upravljivost objekata baze podataka u stvarnom životu!

U proizvodnji, uz pravilnu izgradnju i pažljivo održavanje baza podataka, korištenje rezultata analize omogućava vam da udvostručite, a ponekad i utrostručite obim proizvodnje koristeći iste površine, opremu i isti sastav tehničke i tehničke opreme.

Najjednostavnije sortiranje.

Najjednostavnije sortiranje u Excel-u se vrši pomoću dugmadi "Sort Ascending" i "Sort Descending" koji se nalaze na "Standard" alatnoj traci. (Na donjoj slici ova dugmad su zaokružena crvenom elipsom.)

Zadatak #1:

Odredite: koji je od proizvedenih proizvoda najteži i kolika mu je masa? Kada je ovaj proizvod napravljen?

1. Otvorite datoteku u MS Excel-u.

2. Aktiviramo klikom na ćeliju E7 sa naslovom kolone “Težina 1 komada, t” (možete aktivirati bilo koju ćeliju u koloni koja nas zanima).

3. Kliknite na dugme "Sort Descending" na "Standard" alatnoj traci.

4. Odgovor na postavljeno pitanje čitamo u samom gornjem redu baze podataka (red br. 8). Najteži proizvod u bazi je Greda 045 iz narudžbe br. 2. Greda 045 je proizvedena od 23. aprila do 25. aprila 2014. (vidi unose u Excel redovima br. 8-10).

5. Možete vratiti bazu podataka u obrazac prije sortiranja u Excel-u (ako je potrebno) klikom na dugme “Otkaži” na istoj “Standard” alatnoj traci. Ili možete primijeniti sortiranje uzlazno na stupac Datum u bazi podataka.

Sortiranje u Excelu po više kolona.

Sortiranje na ovaj način može se vršiti uzastopno u dvije ili tri kolone.

Zadatak #2:

Predstavite zapise baze podataka grupisane po narudžbama i sa nazivima proizvoda unutar narudžbi raspoređenih po abecednom redu.

1. Aktiviramo bilo koju ćeliju baze podataka klikom miša (na primjer, ćelija C11).

2. Kliknite na dugme glavnog menija „Podaci“ i izaberite „Sortiranje...“.

3. U prozoru "Raspon sortiranja" koji se pojavi, odaberite vrijednosti s padajućih lista kao što je prikazano na snimku zaslona s lijeve strane i kliknite na "OK".

4. Zadatak br. 2 završen. Zapisi su, prvo, sortirani po brojevima narudžbi, a drugo, unutar svake narudžbe su raspoređeni po abecednom redu prema nazivima proizvoda.

Rezultati.

U ovom drugom članku iz serije o skladištenju informacija i upravljanju podacima Pogledali smo osnovne mogućnosti sortiranja zapisa baze podataka u Excel-u. Ovaj vrlo jednostavan mehanizam Excela sigurno je poznat većini vas, dragi čitatelji. Nadam se da vam nisam oduzeo previše vremena banalnom pričom o poznatom servisu. Ali... put do kompleksa počinje jednostavnim!

Da budem iskren, u praksi rijetko morate pribjeći sortiranju. Zašto? Odgovor na ovo pitanje će biti u narednim člancima iz serije, koji će govoriti o drugim, uključujući naprednije i moćnije Excel mehanizme za brzu i efikasnu obradu podataka.

molim poštovanjem autorski rad pretplatite se za najave članaka u prozorčiću koji se nalazi na kraju svakog članka ili u prozoru na vrhu stranice!

Dragi čitatelji, napišite svoje kritike i komentare u komentarima.

Sortiranje podataka koji se nalaze u oblasti redova i kolona pivot tabele se podrazumevano izvodi uzlaznim redosledom (slika 1a) ili korišćenjem prilagođenih lista za sortiranje. Ovo ne odgovara uvijek korisniku. Na primjer, kada želite da prikažete kupce sa najvećim prihodom na vrhu liste (slika 1b). Ako je vaša zaokretna tabela sortirana u rastućem (opadajućem) redoslijedu, trebali biste kreirati pravilo koje kontrolira redoslijed sortiranja po polju. Štaviše, ovo pravilo (za ovo polje) biće primenjeno čak i nakon dodavanja novih polja u stožernu tabelu (slika 1c).

Rice. 1. Sortiranje po polju Kupac: (a) po defaultu – od A do Z; (b) prema opadajućem prihodu; (c) sortiranje po polju Kupac nije se promijenilo prilikom dodavanja polja Sektor

Preuzmite bilješku u formatu ili, primjere u formatu

Sortiranje kupaca u opadajućem redoslijedu prihoda

Da biste sortirali redove zaokretne tabele u opadajućem redosledu prihoda, izaberite bilo koju ćeliju u koloni Iznos po polju Prihod, na primjer E4 (ali ne i naslov), i kliknite na ikonu JA nalazi se na kartici Podaci(Sl. 2). Ovo sortiranje liči na standardno, ali ovo je samo površna sličnost. Kada sortirate zaokretnu tabelu, Excel kreira pravilo koje će nastaviti da radi nakon što izvršite dalje promene u zaokretnoj tabeli.

Koristeći primjer pivot tablice koja se nalazi u kolonama G:I (slika 1c), možete vidjeti šta će se dogoditi nakon dodavanja novog vanjskog polja reda Sektor. Zaokretna tabela nastavlja sortirati podatke u opadajućem redoslijedu prihoda unutar svakog sektora. Na primjer, u sektoru Proizvodnja General Motors je na prvom mjestu sa prihodom od 750.163 dolara. Ford slijedi usko iza sa prihodom od 622.794 dolara. Čak i ako izbrišete polje Kupac iz zaokretne tabele, napravite dodatna podešavanja i vratite ovo polje nazad, ali u oblast kolona, ​​Excel će zapamtiti sortiranje kupaca u opadajućem redosledu prihoda.

Tako da su u zbirnoj tabeli, koja se nalazi u kolonama G:I (slika 1c), sektori takođe sortirani u opadajućem redosledu prihoda, možete ići na jedan od tri načina:

• Odaberite ćeliju G4, kliknite desnim tasterom miša i odaberite Sažmi svepolje sakriti sve elemente koji se odnose na kupca. Nakon što se na ekranu prikaže samo jedan sektor, odaberite ćeliju I4 i kliknite na YA ikonu na kartici Podaci da izvrši sortiranje u opadajućem redosledu. Ovo će kreirati pravilo sortiranja za polje Sektor. Ponovo izaberite ćeliju G4, kliknite desnim tasterom miša i izaberite Proširi svepolje.
• Privremeno uklonite polje Kupac iz pivot tabele, sortirajte tabelu u opadajućem redosledu prihoda (pomoću metode opisane na slici 2), a zatim ponovo vratite polje Kupac.
• Iskoristite mogućnosti tima Dodatnoopcijesortiranje(Koristim upravo ovu metodu). Da biste pozvali naredbu: (a) Odaberite ćeliju G4, kliknite desnim tasterom miša i odaberite Sortiranje → Dodatnoopcijesortiranje(Sl. 3) ili (b) kliknite na ikonu trougla u polju Sektor, a zatim odaberite Dodatnoopcijesortiranje(Sl. 4). U oba slučaja otvoriće se prozor Sortiranje(Sl. 5). Postavite prekidač u položaj silazno i odaberite liniju Iznos po polju Prihod.

Rice. 3. Pozovite komandu Dodatnoopcijesortiranje desni klik

Rice. 4. Pozovite komandu Dodatnoopcijesortiranje koristeći meni Sortiranje i filteri polja Sektor

Rice. 5. Podešavanje parametara u prozoru Sektor

U donjem lijevom kutu dijaloškog okvira Sortiranje postoji dugme Napredno... Nakon što kliknete na ovo dugme, na ekranu će se pojaviti okvir za dijalog . U ovom prozoru možete: (a) definirati prilagođenu listu koja će se koristiti za sortiranje po prvom ključu (pogledajte detalje ispod); (b) umjesto kolone Ukupno ukupno Odaberite drugu kolonu kao osnovnu kolonu za sortiranje.

Na primjer, za stožernu tabelu prikazanu na sl. 6, možete podesiti sortiranje ne prema ukupnom prihodu, već prema prihodu od prodaje jedne vrste robe, na primjer, Uređaji(imajte na umu da kupci nisu sortirani po koloni F, već po koloni C).

Rice. 6. Dodatni parametri vam omogućavaju da sortirate kupce ne prema ukupnom prihodu, već prema prihodu od prodaje proizvoda Uređaj

Da sortirate ovako:

1. Proširite listu kupaca koja se nalazi u ćeliji A4.
2. Odaberite opciju Više opcija sortiranja.
3. U dijaloškom okviru sortiranje (kupac) kliknite na dugme Dodatno…
4. U dijaloškom okviru Dodatne opcije sortiranja (kupac) Odaberite odjeljak Redosled sortiranja i postavite prekidač Vrijednosti u označenoj koloni.
5. Kliknite na okvir veze, a zatim odaberite ćeliju C5. Imajte na umu da morate kliknuti na jednu od ćelija vrijednosti Uređaj, jer na zaglavlju Uređaj Ne može se kliknuti na ćeliju C4.
6. Da biste dovršili podešavanje parametara, dvaput kliknite OK.

Ne budite uznemireni, opis ovog korak-po-korak algoritma je dat radije u obrazovne svrhe. Počevši od Excela 2013, sortiranje podataka zaokretne tabele postalo je mnogo lakše. Sada su YA i AYA dugmad na kartici Podaci koristiti inteligentne algoritme za sortiranje. Prilikom pokušaja sortiranja pomoću ovih dugmadi, program će pokušati predvidjeti namjere korisnika na osnovu toga koja je ćelija označena prije pritiska na tipku za sortiranje (slika 7):

• A1, C1, D1, E1, F1, F2, A30, F30 – nije dostupno
• A2:A29 – poređat će imena kupaca po abecednom redu u koloni A
• V1, V2, S2, D2, E2 – poređaće nazive robe abecednim redom u 2. redu
• B30, C30, D30, E30 – rasporedit će u silaznom (uzlaznom) redoslijedu iznos prihoda u red 30
• rastuća (opadajuća) prodaja B3:B29 – moduli, C3:C29 – uređaji, D3:D29 – dijelovi, E3:E29 – lijekovi, F3:F29 – ukupno.

Ručno sortiranje

Imajte na umu da u okviru za dijalog Sortiranje(vidi sliku 5) možete ručno definirati pravila sortiranja podataka. Ali ručno sortiranje zaokretne tablice također se radi na drugačiji, prilično neobičan način. U izveštaju izvedene tabele na sl. 8a prikazuje redoslijed kategorija proizvoda, poredanih po abecednom redu: Dio, modul, priprema I Uređaj. Napominjemo da je količina prodatih proizvoda po kategorijama Detalj, nije najveća. I teško da je vrijedno prvo prikazati ovu kategoriju. Postavite pokazivač miša u ćeliju E4 i unesite riječ Detalj. Samo pritisnite tipku Enter Kako će Excel utvrditi da ste odlučili premjestiti kolonu Detalj do poslednje kolone tabele. Sve numeričke vrijednosti koje se odnose na ovu kategoriju proizvoda će se pomjeriti iz stupca B u kolonu E. Vrijednosti koje se odnose na druge kategorije proizvoda će se pomjeriti ulijevo. Ovo ponašanje izgleda nelogično i tipično je samo za Excel zaokretne tabele. Običan Excel skup podataka ne može se preurediti na ovaj način. Na sl. Slika 8b prikazuje stožernu tabelu nakon pomeranja novog zaglavlja kolone u ćeliju E4.

Rice. 8. Ručno sortiranje: (a) kategorije proizvoda su sortirane po abecednom redu, (b) kategorija Dijelovi se postavlja na posljednju

Ljubitelji miša mogu jednostavno prevući i ispustiti željene naslove kolona (ili pojedinačne redove). Kliknite na područje zaglavlja kolone i držite pokazivač miša iznad granice odabranog raspona ćelija dok se ne promijeni u četverosmjernu strelicu. Počnite povlačiti ćeliju na odabranu lokaciju; Pojavit će se pokazivač u obliku debele linije i serifa. Čim otpustite tipku miša, numeričke vrijednosti će se odmah premjestiti u novu kolonu. Imajte na umu da se kada koristite ručno sortiranje, proizvodi dodani izvoru podataka dodaju na kraj liste. To je zbog činjenice da Excel ne zna gdje tačno dodati novu regiju.

Sortiranje podataka prema prilagođenim listama

Drugo rješenje za problem prilagođavanja redoslijeda u kojem su polja predstavljena je kreiranje prilagođenih lista. Koristeći takvu listu, pivot tabele kreirane u budućnosti će se sortirati. Excel podrazumevano sadrži četiri prilagođene liste: za dane u nedelji, mesece u godini i skraćene nazive za dane u nedelji i mesece u godini. Program sortira nazive dana u sedmici u prirodnom nizu, počevši sa pon i završavajući sa ned (a ne po abecednom redu).

Za kreiranje vlastite liste sortiranja slijedite ove korake:

1. U području radnog lista bez podataka unesite nazive kategorija proizvoda redoslijedom koji odgovara prilagođenoj listi koju kreirate. Unesite jedno ime u svaku ćeliju i stavite imena u jednu kolonu (slika 9).
2. Odaberite rezultirajuću listu naziva kategorija proizvoda (ćelije A10:A13).
3. Odaberite karticu trake File i na dnu navigacijske trake prikazane u prozoru s lijeve strane kliknite na dugme Opcije da otvorite dijaloški okvir Excel opcije.
4. izaberi kategoriju Dodatno, idite na odjeljak Uobičajeni su i kliknite na dugme Uredi liste.
5. U dijaloškom okviru Liste u polju se prikazuje adresa opsega koji sadrži unapred odabranu listu naslova Uvoz liste iz ćelija(Sl. 10). Kliknite na dugme Uvoz da generišete novu listu kategorija proizvoda na osnovu navedenih podataka. Nova lista se dodaje na dno oblasti Liste.
6. Kliknite OK da zatvorite dijaloški okvir Liste. Ponovo kliknite na OK da zatvorite dijaloški okvir Excel opcije.

Rice. 10. Prozor Liste

Novokreirana lista se pohranjuje u postavkama programa i postaje dostupna u budućim Excel sesijama. Sada će sve zaokretne tabele kreirane u budućnosti automatski sortirati po polju proizvoda u skladu sa redosledom navedenim na listi. Na sl. Na slici 11 prikazana je nova pivot tabela (koja je kreirana na osnovu dodavanja prilagođene liste proizvoda), sortirana prema kreiranoj listi.

Za sortiranje prethodno kreiranih pivot tablica prema novoj prilagođenoj listi, slijedite ove korake:

1. Proširite listu polja Proizvod i odaberite opciju Dodatne opcije sortiranja.
2. U dijaloškom okviru Sortiranje (proizvod) dugme za odabir uzlazno (od A do Z) po polju, i sa padajuće liste izaberite Proizvod.
3. Kliknite na dugme Dodatno…
4. U dijaloškom okviru Dodatne opcije sortiranja (proizvod) opozovite izbor u polju za potvrdu Automatsko sortiranje.
5. Proširite listu Sortiraj po prvom ključu i izaberite listu koja uključuje nazive kategorija proizvoda (slika 12).
6. Dvaput kliknite na OK.

Bilješka je napisana prema knjizi Billa Jelena, Michael Alexander. . Poglavlje 4.

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 početne razmake. 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 "Razmotriti slučaj" u opcijama sortiranja.

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

Sortirajte vrijednosti datuma i vremena

"Sortiraj od najstarijih do najnovijih" 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 koja sadrži marker odabira.

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 mijenjaju 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 Excelu 2007, na primjer, ima ih 64. Nivoi sortiranja se mogu dodavati, brisati, kopirati i mijenjati.

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

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.