Kada radite sa Excel tabelama, često je potrebno odabrati ih prema određenom kriterijumu ili na nekoliko uslova. Program to može učiniti na različite načine koristeći brojne alate. Hajde da smislimo kako da probamo u Excelu koristeći razne opcije.
Uzorkovanje
Uzorkovanje podataka se sastoji od procedure odabira iz opšteg niza rezultata koji zadovoljavaju specificirane uvjete, s njihovim naknadnim izlazom na listu u zasebnoj listi ili u početnom rasponu.
Metoda 1: koristite napredni autofilter
Najlakši način da odaberete je da koristite napredni autofilter. Razmotrite kako to uraditi sa specifičnim primerom.
- Odaberite područje na listu, među podacima koje želite uzorkovati. Na kartici "Home" kliknite na dugme "Sortiraj i filtriraj". Nalazi se u bloku postavki. Uređivanje. Na listi koja se otvara nakon toga, kliknite na dugme. "Filter".
Moguće je raditi drugačije. Da biste to uradili, nakon odabira područja na listu, pređite na karticu "Data". Kliknite na dugme "Filter"koja je postavljena na traku u grupi "Sortiraj i filtriraj".
- Nakon ove akcije, u zaglavlju tabele se pojavljuju ikone za početak filtriranja u obliku malih trokuta okrenutih naopačke na desnoj ivici ćelija. Kliknite na ovu ikonu u naslovu kolone na kojoj želite izvršiti odabir. U start meniju kliknite na stavku "Tekstualni filtri". Zatim odaberite poziciju "Prilagođeni filter ...".
- Aktiviran je prilagođeni prozor za filtriranje. Moguće je postaviti granicu na kojoj će se izvršiti odabir. U padajućoj listi za stupac koji sadrži ćelije formata brojeva, koje koristimo kao primjer, možete odabrati jedan od pet tipova uvjeta:
- jednako;
- nije jednako;
- više;
- veća ili jednaka;
- manje
Postavimo uvjet kao primjer tako da možemo odabrati samo vrijednosti za koje iznos prihoda prelazi 10.000 rubalja. Postavite prekidač u položaj "Više". Unesite vrijednost na desnoj margini "10000". Da biste izvršili neku radnju, kliknite na dugme. "OK".
- Kao što vidite, nakon filtriranja, postoje samo linije u kojima iznos prihoda prelazi 10.000 rubalja.
- Ali u istoj koloni možemo dodati drugi uslov. Da biste to uradili, vratite se na prozor prilagođenog filtera. Kao što vidite, u donjem delu se nalazi još jedan prekidač stanja i odgovarajuće polje za unos. Postavimo sada gornju granicu izbora od 15.000 rubalja. Da biste to uradili, postavite prekidač na položaj "Manje", au polje desno unesite vrijednost "15000".
Pored toga, postoje uslovi za prebacivanje. On ima dvije pozicije "I" i "ILI". Podrazumevano je postavljen na prvu poziciju. To znači da će samo linije koje zadovoljavaju oba ograničenja ostati u selekciji. Ako je stavljen na poziciju "ILI", onda će biti vrijednosti koje su prikladne za bilo koji od ova dva uvjeta. U našem slučaju, potrebno je postaviti prekidač na "I", to jest, ostavite ovo podrazumevano podešavanje. Nakon unosa svih vrijednosti, kliknite na gumb. "OK".
- Sada tabela ima samo linije u kojima iznos prihoda nije manji od 10.000 rubalja, ali ne prelazi 15.000 rubalja.
- Slično tome, možete konfigurirati filtre u drugim stupcima. U isto vrijeme, moguće je također spremiti filtriranje prema prethodnim uvjetima koji su specificirani u stupcima. Dakle, da vidimo kako se vrši odabir pomoću filtera za ćelije u formatu datuma. Kliknite na ikonu filtera u odgovarajućoj koloni. Zatim kliknite na stavke na listi. "Filtriraj po datumu" i "Prilagođeni filter".
- Ponovo se pokreće prilagođeni prozor autofiltra. Izvršite izbor rezultata u tabeli od 4. do 6. maja 2016. godine. U prekidaču za odabir uvjeta, kao što možete vidjeti, ima još više opcija nego za format broja. Izaberite poziciju "Nakon ili jednako". U polju desno postavite vrijednost "04.05.2016". U donjem bloku, postavite prekidač na položaj "Do ili jednako". Unesite vrijednost u desno polje "06.05.2016". Prekidač kompatibilnosti uvjeta ostaje u zadanom položaju - "I". Da biste primijenili filtriranje u akciji, kliknite na gumb "OK".
- Kao što vidite, naša lista se još više smanjila. Sada ostaju samo linije, u kojima iznos prihoda varira od 10.000 do 15.000 rubalja za period od 04.05. Do 06.05.2016.
- Možemo resetirati filtriranje u jednom od stupaca. Učinite to za vrijednosti prihoda. Kliknite na ikonu autofiltera u odgovarajućoj koloni. Na padajućoj listi kliknite na stavku. "Ukloni filtar".
- Kao što možete vidjeti, nakon ovih akcija, uzorak po visini prihoda će biti onemogućen, a ostaće samo selekcija po datumima (od 04.05.2016 do 06.05.2016).
- Ova tabela ima drugu kolonu - "Ime". Sadrži podatke u tekstualnom formatu. Da vidimo kako da kreiramo uzorak koristeći filtriranje po ovim vrednostima.
Kliknite na ikonu filtera u nazivu stupca. Uzastopno prolazite kroz listu "Tekstualni filtri" i "Prilagođeni filter ...".
- Ponovo se otvara prozor za automatsko filtriranje korisnika. Uradimo uzorak po imenu. "Krompir" i "Meso". U prvom bloku, prekidač stanja je postavljen na "Jednako". U polju desno od njega unesite reč "Krompir". Prekidač donjeg bloka je takođe postavljen u poziciju "Jednako". U polju nasuprot njemu pravimo ulaz - "Meso". I onda radimo ono što do sada nismo uradili: postavimo prekidač kompatibilnosti na poziciju "ILI". Sada se na ekranu prikazuje linija koja sadrži bilo koji od navedenih uslova. Kliknite na dugme "OK".
- Kao što možete vidjeti, u novom uzorku postoje ograničenja na datum (od 04/05/2016 do 05/06/2016) i po imenu (krompir i meso). Ne postoji ograničenje na iznos prihoda.
- Možete potpuno ukloniti filter koristeći iste metode koje su korištene za instalaciju. I bez obzira na metod. Da biste poništili filtriranje na kartici "Data" kliknite na dugme "Filter"koja se nalazi u grupi "Sortiraj i filtriraj".
Druga opcija uključuje prebacivanje na karticu "Home". Tamo vršimo klik na traku na dugmetu. "Sortiraj i filtriraj" u bloku Uređivanje. U aktiviranoj listi kliknite na dugme. "Filter".
Kada se koristi bilo koji od ova dva metoda, filtriranje će biti uklonjeno, a rezultati uzorka će biti izbrisani. To znači da će tabela prikazati čitav niz podataka koje ima.
Lekcija: Funkcija automatskog filtriranja u Excelu
Metod 2: Koristite formulu matrice
Također možete napraviti izbor primjenom kompleksne matrice. Za razliku od prethodne verzije, ovaj metod daje izlaz rezultata u zasebnu tablicu.
- Na istom listu kreirajte praznu tablicu s istim nazivima stupaca u zaglavlju kao izvorni kod.
- Izaberite sve prazne ćelije prve kolone nove tabele. Postavite kursor u formulu. Upravo ovdje će se unijeti formula, uzorkovati prema zadanim kriterijima. Odabrat ćemo linije, čiji iznos prihoda prelazi 15.000 rubalja. U našem specifičnom primjeru, formula koju unesete izgledat će ovako:
= INDEX (A2: A29; NAJMANJI (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Naravno, u svakom slučaju adresa ćelija i opsega će biti različiti. U ovom primjeru, možete usporediti formulu s koordinatama na slici i prilagoditi je vašim potrebama.
- Pošto je ovo formula matrice, da biste je primenili u akciji, morate pritisnuti ne dugme Enteri tastersku prečicu Ctrl + Shift + Enter. Mi to radimo.
- Odabirom drugog stupca s datumima i postavljanjem kursora u traci s formulama unesite sljedeći izraz:
= INDEX (B2: B29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Pritisnite tastersku prečicu Ctrl + Shift + Enter.
- Slično tome, u koloni sa prihodom unosimo sljedeću formulu:
= INDEX (C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Opet, otkucamo prečicu Ctrl + Shift + Enter.
U sva tri slučaja mijenja se samo prva vrijednost koordinata, a ostale formule su potpuno identične.
- Kao što vidite, tabela je popunjena podacima, ali njen izgled nije baš atraktivan, osim toga, datumske vrednosti su popunjene pogrešno. Potrebno je ispraviti te nedostatke. Neispravan datum je zbog činjenice da je format ćelija u odgovarajućoj koloni uobičajen, i moramo postaviti format datuma. Izaberite celu kolonu, uključujući i ćelije sa greškama, i kliknite na selekciju desnom tipkom miša. Na listi koja se pojavljuje na stavci "Format ćelije ...".
- U prozoru za formatiranje koji se otvori, otvorite karticu "Broj". U bloku "Formati brojeva" select value "Datum". U desnom dijelu prozora možete odabrati željeni tip prikaza datuma. Nakon podešavanja, kliknite na dugme. "OK".
- Sada je datum ispravno prikazan. Ali, kao što možete vidjeti, cijelo dno tablice je popunjeno ćelijama koje sadrže pogrešnu vrijednost. "#NUM!". U stvari, to su ćelije koje nisu imale dovoljno podataka iz uzorka. Bilo bi privlačnije ako bi se uopšte ispraznile. Za ove svrhe koristimo uslovno formatiranje. Odaberite sve ćelije u tablici osim zaglavlja. Biti na kartici "Home" kliknite na dugme "Uslovno oblikovanje"koji je u bloku alata "Stilovi". Na listi koja se pojavi izaberite stavku "Napravi pravilo ...".
- U prozoru koji se otvori, izaberite tip pravila "Formatiraj samo ćelije koje sadrže". U prvom polju ispod natpisa "Formatiraj samo ćelije za koje je ispunjen sljedeći uvjet" izaberite poziciju "Greške". Zatim kliknite na dugme "Format ...".
- U prozoru za formatiranje koji se otvori, idite na karticu "Font" i izaberite belu boju u odgovarajućem polju. Nakon ovih akcija, kliknite na dugme. "OK".
- Kliknite na dugme sa istim imenom nakon povratka u prozor za podešavanje.
Sada imamo gotov uzorak za specificirano ograničenje u odvojenoj, pravilno postavljenoj tabeli.
Lekcija: Uslovno formatiranje u Excelu
Metod 3: uzorak po nekoliko uslova koristeći formulu
Baš kao što koristite filter, koristeći formulu, možete uzorkovati po nekoliko uvjeta. Na primjer, uzmimo svu istu izvornu tablicu, kao i praznu tablicu gdje će se prikazati rezultati, s već izvedenim numeričkim i uvjetnim formatiranjem. Postavite prvo ograničenje na donji limit izbora za prihod od 15.000 rubalja, a drugi uslov je gornja granica od 20.000 rubalja.
- U posebnu kolonu unosimo granične uslove za uzorak.
- Kao iu prethodnoj metodi, naizmenično izaberite prazne kolone nove tabele i unesite odgovarajuće tri formule u njih. U prvu kolonu unesite sljedeći izraz:
= INDEKS (A2: A29; NAJMANJI (IF (($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ C $ 1))U narednim kolonama unosimo potpuno iste formule, samo promjenom koordinata odmah nakon imena operatora. INDEX na odgovarajuće kolone koje su nam potrebne, po analogiji sa prethodnom metodom.
Svaki put nakon unosa ne zaboravite da otkucate prečice Ctrl + Shift + Enter.
- Prednost ove metode u odnosu na prethodnu je da ako želimo da promijenimo granice uzorkovanja, onda nećemo morati mijenjati samu formulu polja, što je samo po sebi problematično. Dovoljno je promijeniti brojeve granica u stupcu uvjeta na listu na one koje korisnik treba. Rezultati odabira će se automatski promijeniti.
Metoda 4: slučajno uzorkovanje
U Excelu sa posebnom formulom SLCIS može se primijeniti i nasumični odabir. U nekim slučajevima to je potrebno uraditi kada se radi s velikim količinama podataka, kada je potrebno prikazati opću sliku bez sveobuhvatne analize svih podataka u nizu.
- S lijeve strane tablice preskočite jednu kolonu. U ćeliju sljedeće kolone, koja je nasuprot prvoj ćeliji s podacima u tablici, unesite formulu:
= RAND ()Ova funkcija prikazuje nasumični broj. Da biste je aktivirali, kliknite na dugme ENTER.
- Da biste napravili celu kolonu nasumičnih brojeva, postavite kursor u donji desni ugao ćelije, koji već sadrži formulu. Pojavljuje se oznaka za popunjavanje. Povucite ga sa levim tasterom miša pritisnutim paralelno sa tabelom sa podacima do kraja.
- Sada imamo niz ćelija ispunjenih slučajnim brojevima. Ali, ona sadrži formulu SLCIS. Moramo da radimo sa čistim vrednostima. Da biste to uradili, kopirajte u praznu kolonu desno. Izaberite opseg ćelija sa slučajnim brojevima. Nalazi se na kartici "Home", kliknite na ikonu "Kopiraj" na kaseti.
- Izaberite praznu kolonu i kliknite desnim tasterom miša, pozivanjem kontekstnog menija. U grupi alata "Opcije umetanja" izaberite stavku "Vrijednosti"prikazan kao piktogram sa brojevima.
- Posle toga, u tabulatoru "Home", kliknite na već poznatu ikonu "Sortiraj i filtriraj". Na padajućoj listi zaustavite izbor na stavci "Prilagođeno sortiranje".
- Prozor za podešavanje sortiranja je aktiviran. Obavezno označite polje pored parametra. "Moji podaci sadrže zaglavlja"ako postoji kapa, ali nema kvačice. Na terenu "Sortiraj po" navedite ime stupca koji sadrži kopirane vrijednosti slučajnih brojeva. Na terenu "Sortiraj" ostavite podrazumevane postavke. Na terenu "Red" možete odabrati opciju kao "Ascending"tako i "Descending". Za slučajni uzorak, to nije važno. Nakon podešavanja, kliknite na dugme. "OK".
- Nakon toga, sve vrijednosti tablice su raspoređene u rastućem ili opadajućem redoslijedu slučajnih brojeva. Možete uzeti bilo koji broj prvih redova iz tabele (5, 10, 12, 15, itd.) I oni se mogu smatrati rezultatom slučajnog uzorka.
Lekcija: Sortiranje i filtriranje podataka u Excelu
Kao što možete videti, uzorak u Excel tabeli može da se napravi, kao uz pomoć automatskog filtera, i primenom specijalnih formula. U prvom slučaju, rezultat će biti prikazan u originalnoj tabeli, au drugom - u zasebnom području. Postoji mogućnost da se napravi selekcija, i pod jednim uslovom, i na nekoliko. Osim toga, možete izvesti slučajno uzorkovanje pomoću funkcije SLCIS.
