Kada radite s Excel tablicama, često ih morate odabrati prema određenom kriteriju ili prema nekoliko uvjeta. Program to može učiniti na različite načine koristeći brojne alate. Doznajmo kako uzorkovati u Excelu koristeći različite opcije.
Uzorkovanje
Izbor podataka sastoji se u postupku odabira iz općeg niza onih rezultata koji zadovoljavaju dane uvjete, s njihovim naknadnim ispisom na listu kao zasebnom popisu ili u izvornom rasponu.
1. metoda: koristite napredni autofilter
Najlakši način za odabir je korištenje naprednog automatskog filtera. Razmislite kako to učiniti na konkretnom primjeru.
- Odaberite područje na listu, među podacima koje želite izvršiti izbor. Na kartici "Početna" kliknite na gumb Poredaj i filtriraj. Nalazi se u bloku postavki. "Uređivanje". Na popisu koji se otvori nakon ovoga, kliknite na gumb "Filter".
Postoji prilika da se djeluje drugačije. Da biste to učinili, nakon što ste odabrali područje na listu, pređite na karticu "Podaci". Kliknite na dugme "Filter"koja je objavljena na vrpci u grupi Poredaj i filtriraj.
- Nakon ove akcije u zaglavlju tablice pojavljuju se piktogrami za početak filtriranja u obliku malih trouglova okrenutih naopako na desnoj ivici ćelije. Kliknemo na ovu ikonu u zaglavlju stupca kojim želimo izvršiti izbor. U meniju koji se otvori idite na stavku "Tekst filteri". Zatim odaberite položaj "Prilagođeni filter ...".
- Korisnički prozor za filtriranje je aktiviran. U njemu možete postaviti granicu do koje će se izvršiti izbor. Na padajućem popisu za stupac koji sadrži ćelije oblika broja koji koristimo kao primjer možete odabrati jedan od pet vrsta uvjeta:
- jednak;
- nije jednako;
- više;
- više ili jednako;
- manje.
Navedimo primjer kao uvjet na način da odaberemo samo vrijednosti za koje iznos prihoda prelazi 10 000 rubalja. Podesite prekidač u položaj Još. Unesite vrijednost u desno polje "10000". Da biste izvršili neku radnju, kliknite na dugme "OK".
- Kao što vidite, nakon filtriranja postojale su samo linije u kojima iznos prihoda premašuje 10 000 rubalja.
- Ali u isti stupac možemo dodati i drugi uvjet. Da bismo to učinili, ponovo se vraćamo na prozor za filtriranje korisnika. Kao što vidite, u njegovom donjem dijelu nalazi se još jedan prekidač stanja i pripadajuće polje za unos. Postavimo sada gornju granicu odabira na 15 000 rubalja. Da biste to učinili, postavite prekidač u položaj Manje, a u polje s desne strane unosimo vrijednost "15000".
Pored toga, tu je i prekidač za stanje. Ima dvije pozicije "I" i "ILI". Prema zadanim postavkama postavlja se na prvo mjesto. To znači da će u uzorku ostati samo redovi koji zadovoljavaju oba ograničenja. Ako će se staviti u položaj "ILI"tada će postojati vrijednosti koje odgovaraju bilo kojem od dva uvjeta. U našem slučaju morate postaviti prekidač "I", to jest, ovu postavku ostavite kao zadanu. Nakon unosa svih vrijednosti kliknite na gumb "OK".
- Sada u tablici postoje samo retci 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. Istovremeno je moguće spremiti filtriranje prema prethodnim uvjetima koji su postavljeni u stupcima. Dakle, pogledajmo kako se vrši filtriranje za ćelije u formatu datuma. Kliknite ikonu filtra u odgovarajućem stupcu. Sekvencijalno kliknite na stavke liste "Filtriraj po datumu" i Prilagođeni filter.
- Korisnički prozor za automatsko filtriranje ponovo se pokreće. Izbor rezultata vršimo u tabeli od 4. do 6. maja 2016. godine. U prekidaču za odabir stanja, kao što vidimo, postoji još više opcija nego za format broja. Odaberite poziciju "Nakon ili jednak". U polju s desne strane postavite vrijednost "04.05.2016". U donjem bloku postavite prekidač na položaj "Za ili jednako". Unesite vrijednost u desno polje "06.05.2016". Prekidač kompatibilnosti stanja ostavljamo u zadanom položaju - "I". Da biste primijenili filtriranje na djelu, kliknite na gumb "OK".
- Kao što vidite, naša je lista dodatno umanjena. Sada su u njemu preostale samo linije u kojima iznos prihoda varira od 10.000 do 15.000 rubalja za razdoblje od 4. do 6. maja 2016. godine.
- Možemo resetirati filtriranje u jednom od stupaca. To ćemo učiniti za vrijednosti prihoda. Kliknite ikonu automatskog filtriranja u odgovarajućem stupcu. Na padajućoj listi kliknite na stavku Uklonite filter.
- Kao što vidite, nakon ovih radnji odabir prema visini prihoda bit će onemogućen, a ostat će samo izbor po datumima (od 04.05.2016. Do 06.06.2016.).
- U ovoj tabeli nalazi se još jedan stupac - "Ime". Sadrži podatke u tekstualnom formatu. Pogledajmo kako stvoriti izbor pomoću filtriranja po tim vrijednostima.
Kliknite na ikonu filtra u nazivu stupca. Prolazimo kroz imena popisa "Tekst filteri" i "Prilagođeni filter ...".
- Ponovno se otvara prozor za automatsko filtriranje korisnika. Napravimo izbor po stavkama "Krompir" i Meso. U prvom bloku postavite prekidač stanja na "Jednak". U polje desno od njega unosimo riječ "Krompir". Prekidač donjeg bloka se takođe postavlja u položaj "Jednak". Na polju nasuprot tome napravite zapis - Meso. A onda radimo ono što prije nismo radili: postavimo preklopnik za kompatibilnost s uvjetima "ILI". Sada će se na ekranu pojaviti redak koji sadrži bilo koji od navedenih uvjeta. Kliknite na dugme "OK".
- Kao što vidite, u novom uzorku postoje ograničenja po datumu (od 04.04.2016. Do 06.06.2016.) I po nazivu (krompir i meso). Ne postoje ograničenja u visini prihoda.
- Možete u potpunosti ukloniti filter na iste načine na koji ste ga koristili. Štaviše, nije važno koja je metoda korištena. Da biste resetirali filtriranje, nalazite se na kartici "Podaci" kliknite na gumb "Filter"koja je smeštena u grupu Poredaj i filtriraj.
Druga opcija uključuje odlazak na karticu "Početna". Tamo kliknemo dugme na vrpci Poredaj i filtriraj u bloku "Uređivanje". Na aktiviranom spisku kliknite na dugme "Filter".
Koristeći bilo koju od gore spomenute dvije metode, filtriranje će se izbrisati, a rezultati odabira izbrisati. Odnosno, tabela će prikazati cijeli niz podataka koje posjeduje.
Lekcija: Funkcija automatskog filtriranja u Excelu
Druga metoda: primjena formule niza
Možete odabrati i primjenom složene formule niza. Za razliku od prethodne verzije, ova metoda predviđa izlaz rezultata u zasebnoj tablici.
- Na istom listu stvorite praznu tablicu s istim imenima stupaca u zaglavlju kao i izvor.
- Odaberite sve prazne ćelije u prvom stupcu nove tablice. Kursor postavljamo u red formula. Upravo ovdje će se unijeti formula koja proizvodi izbor prema navedenim kriterijima. Odabiremo retke u kojima iznos prihoda prelazi 15.000 rubalja. U našem konkretnom primjeru, formula za unos će izgledati ovako:
= INDEX (A2: A29; NISKO (AKO (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Naravno, u svakom će slučaju adresa ćelija i raspona biti različiti. U ovom primjeru možete usporediti formulu s koordinatama na slici i prilagoditi je vašim potrebama.
- Budući da je ovo formula matrice, da biste je primijenili u akciji, morate pritisnuti ne gumb Unesite, i prečica na tastaturi Ctrl + Shift + Enter. Mi to radimo.
- Odabirom drugog stupca s datumima i smještanjem kursora u traku formule unosimo sljedeći izraz:
= INDEX (B2: B29; NIZKO (AKO (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Pritisnite prečicu na tastaturi Ctrl + Shift + Enter.
- Slično tome, u stupac s prihodom unosimo formulu na sljedeći način:
= INDEX (C2: C29; NISKO (AKO (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Ponovo upišite prečicu na tastaturi Ctrl + Shift + Enter.
U sva tri slučaja mijenja se samo prva vrijednost koordinata, a ostatak formule je potpuno identičan.
- Kao što vidite, tablica je ispunjena podacima, ali njen izgled nije u potpunosti privlačan, osim toga, vrijednosti datuma unose se pogrešno. Trebate ispraviti ove nedostatke. Datum nije točan jer je oblik ćelije odgovarajućeg stupca uobičajen i trebamo postaviti format datuma. Odaberite cijeli stupac, uključujući ćelije s greškama, i kliknite na odabir desnim gumbom miša. Na popisu koji se pojavi idite na "Format ćelije ...".
- U prozoru za oblikovanje koji se otvori otvorite karticu "Broj". U bloku "Brojevi formati" istaknite vrijednost Datum. U desnom dijelu prozora možete odabrati željenu vrstu prikaza datuma. Nakon što su postavke postavljene, kliknite na gumb "OK".
- Sada je datum ispravno prikazan. Ali, kao što vidimo, čitav donji dio tablice ispunjen je ćelijama koje sadrže pogrešnu vrijednost "# BROJ!". U stvari, to su one ćelije za koje nije bilo dovoljno podataka iz uzorka. Bilo bi atraktivnije kada bi ih uopće prikazali praznima. U ove svrhe koristit ćemo uslovno oblikovanje. Odaberite sve ćelije u tablici osim zaglavlja. Biti na kartici "Početna" kliknite na gumb Uslovno oblikovanjekoja se nalazi u bloku alata Stilovi. Na popisu koji se pojavi odaberite "Stvorite pravilo ...".
- U prozoru koji se otvori odaberite vrstu pravila "Formatirajte samo ćelije koje sadrže". U prvom okviru ispod natpisa "Formatirajte samo ćelije za koje vrijedi sljedeći uvjet" odaberite položaj "Greške". Zatim kliknite na dugme "Formatiraj ...".
- U prozoru za oblikovanje koji se pokrene, idite na karticu Font i u odgovarajućem polju odaberite bijelo. Nakon ovih radnji kliknite na gumb "OK".
- Nakon povratka u prozor za stvaranje uvjeta kliknite na gumb s istim nazivom.
Sada imamo gotov uzorak za navedeno ograničenje u zasebnoj pravilno dizajniranoj tablici.
Lekcija: Uslovno oblikovanje u Excelu
Metoda 3: uzorkovanje prema nekoliko uvjeta pomoću formule
Baš kao i kad koristite filter, koristeći formulu, možete birati prema nekoliko uvjeta. Za primjer ćemo uzeti istu izvornu tablicu, a također i praznu tablicu u kojoj će se prikazati rezultati, s već izvršenim numeričkim i uvjetnim oblikovanjem. Prvo ograničenje postavili smo donjoj granici odabira za prihod od 15 000 rubalja, a drugi uvjet za gornju granicu od 20 000 rubalja.
- Granicne uvjete za odabir unosimo u poseban stupac.
- Kao i u prethodnoj metodi, jednu po jednu biramo prazne stupce nove tablice i u njih unesemo odgovarajuće tri formule. U prvi stupac dodajte sljedeći izraz:
= INDEX (A2: A29; LOW (AKO ((($ D $ 2 = C2: C29); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1)) - LINE ($ C $ 1))U sljedeće stupce unosimo potpuno iste formule, samo mijenjajući koordinate odmah nakon imena operatera INDEX do odgovarajućih stupaca koji su nam potrebni analogno prethodnoj metodi.
Svaki put nakon unosa ne zaboravite upisati kombinaciju tipki Ctrl + Shift + Enter.
- Prednost ove metode u odnosu na prethodnu je ta što ako želimo promijeniti granice uzorka, nećemo trebati mijenjati formulu samog niza, što je po sebi prilično problematično. Dovoljno je u stupcu uvjeta na listu promijeniti granične brojeve u one koji korisniku trebaju. Rezultati odabira će se automatski promijeniti odmah.
Metoda 4: slučajno uzorkovanje
U Excelu pomoću posebne formule HAPPEN može se primijeniti i nasumični odabir. Mora se proizvesti u nekim slučajevima kada se radi s velikom količinom podataka, kad je potrebno predstaviti opću sliku bez sveobuhvatne analize svih podataka u nizu.
- S lijeve strane tablice preskačemo jednu kolonu. U ćeliju sljedećeg stupca koja se nalazi nasuprot prvoj ćeliji s podacima tablice unosimo formulu:
= RAND ()Ova funkcija prikazuje slučajni broj. Da biste ga aktivirali, kliknite na gumb ULAZ.
- Da biste napravili čitav stupac slučajnih brojeva, postavite kursor u donji desni ugao ćelije koji već sadrži formulu. Pojavi se oznaka za unos. Povučemo ga prema dolje pritiskom lijeve tipke miša paralelno s tablicom podataka do kraja.
- Sada imamo niz ćelija napunjenih slučajnim brojevima. Ali, sadrži formulu HAPPEN. Moramo raditi s čistim vrijednostima. Da biste to učinili, kopirajte u prazan stupac s desne strane. Odaberite raspon ćelija sa slučajnim brojevima. Smješten na kartici "Početna"kliknite na ikonu Kopiraj na vrpci.
- Odaberite prazan stupac i desnom tipkom miša kliknite pozivajući kontekstni izbornik. U grupi alata Umetanje opcija odaberite stavku "Vrijednosti"prikazana kao piktogram s brojevima.
- Nakon toga, biti u kartici "Početna", kliknite na ikonu koju već znamo Poredaj i filtriraj. Na padajućem popisu zaustavite izbor na Prilagođena vrsta.
- Aktivira se prozor postavki sortiranja. Obavezno potvrdite okvir pored parametra "Moji podaci sadrže zaglavlja"ako postoji šešir ali nema kvačice. U polju Poredaj po navedite naziv stupca koji sadrži kopirane vrijednosti slučajnih brojeva. U polju "Sortiraj" ostavite zadane postavke. U polju "Naruči" možete odabrati parametar kao "Uzlazno"tako i "Silazno". Za slučajno uzorkovanje to nije važno. Nakon podešavanja, kliknite na dugme "OK".
- Nakon toga se sve vrijednosti tablice raspoređuju u uzlaznom ili silaznom redoslijedu slučajnih brojeva. Iz tablice možete uzeti bilo koji broj prvih redaka (5, 10, 12, 15, itd.) I oni se mogu smatrati rezultatom slučajnog uzorkovanja.
Lekcija: Poredaj i filtriraj podatke u Excelu
Kao što vidite, izbor u Excelovoj proračunskoj tablici može se obaviti ili automatskim filterom ili primjenom posebnih formula. U prvom slučaju rezultat će biti prikazan u izvornoj tablici, a u drugom - u zasebnom području. Moguće je izvršiti izbor, pod jednim uvjetom i na nekoliko. Takođe možete nasumično odabrati pomoću funkcije HAPPEN.
