Prilikom rada s Excelovim tablicama često je potrebno provesti odabir na određenom kriteriju ili u nekoliko uvjeta. Program to može učiniti na različite načine pomoću niza alata. Da saznamo kako napraviti uzorak u Exceleu koristeći razne opcije.
Uzorkovanje
Uzorak podataka sastoji se u postupku odabira od ukupnog niza onih rezultata koji zadovoljavaju navedene uvjete, nakon čega slijedi izlaz na listu zasebnog popisa ili u rasponu izvora.Metoda 1: Primijenite prošireni automat
Najlakši način da odabir je korištenje proširenog autofiltera. Razmislite o tome kako to učiniti na određenom primjeru.
- Odaberite područje na listu, među podacima koje želite napraviti uzorak. Na kartici Početak kliknite gumb "Sortiraj i Filter". Stavlja se u blok postavki uređivanja. Na popisu koji se otvara nakon ovog popisa, kliknite na gumb "Filter".
Postoji mogućnost da se radi i drugačije. Da biste to učinili, nakon odabira područja na listu, krećemo na karticu "Data". Kliknite na gumb "Filter", koji se nalazi na vrpci u skupini sortiranja i filtra.
- Nakon ove akcije u zaglavlju stola, pojavljuju se piktogrami za filtriranje u obliku rubova malih trokuta u desnom rubu stanica. Kliknite na ovu ikonu u naslovu tog stupca, prema kojem želimo napraviti uzorak. U izborniku Izbornik prođite kroz stavku "Text Filters". Zatim odaberite položaj "Prilagodljivi filtar ...".
- Prozor filtriranja korisnika je aktiviran. U njemu možete postaviti ograničenje na koju će se izvršiti izbor. Na padajućem popisu za stupac stanice numeričkog formata, koji koristimo, na primjer, možete odabrati jedan od pet vrsta uvjeta:
- jednaka;
- nejednak;
- više;
- više ili jednaki;
- manji.
Postavimo stanje kao primjer kako bi odabrali samo vrijednosti za koje iznos prihoda prelazi 10.000 rubalja. Uspostavljamo prekidač na položaj "više". U desnom polju odgovara vrijednosti "10.000". Da biste izvršili radnju, kliknite na gumb "OK".
- Kao što vidimo, nakon filtracije ostale su samo linije, u kojima količina prihoda prelazi 10.000 rubalja.
- Ali u istom stupcu možemo dodati drugi uvjet. Da biste to učinili, vratite se na prozor prilagođenog filtracije. Kao što možemo vidjeti, u donjem dijelu postoji još jedan uvjet prekidača i odgovarajuće polje za unos. Dopustite da sada instaliramo gornju granicu odabira od 15.000 rubalja. Da biste to učinili, postavite prekidač na položaj "manje" i na polje na desnoj strani vrijednosti "15000".
Osim toga, još uvijek postoji prekidač uvjeta. Ima dvije odredbe "i" ili ". Prema zadanim postavkama instaliran je u prvom položaju. To znači da će samo linije ostati u uzorku koji zadovoljavaju i ograničenja. Ako je postavljen na "ili" položaj, tada će ostati vrijednosti koje su prikladne za bilo koji od dva uvjeta. U našem slučaju, morate postaviti prekidač na položaj "i", to jest, ostavite ovu zadanu postavku. Nakon što se unesete sve vrijednosti, kliknite gumb U redu.
- Sada su samo linije ostale u tablici, u kojoj iznos prihoda nije manji od 10.000 rubalja, ali ne prelazi 15.000 rubalja.
- Slično tome, možete konfigurirati filtre u drugim stupcima. Moguće je održavati filtriranje i prethodne uvjete koji su postavljeni u stupcima. Dakle, da vidimo kako se odabir uzima pomoću filtra za datume formata. Kliknite ikonu filtriranja u odgovarajućem stupcu. Dosljedno klikom na popis "Filter po datumu" i filter.
- Prilagođeni prozor za pokretanje autoferta ponovno počinje. Izdanje rezultata u tablici iz 4 do 6. svibnja 2016. uključivo. U prekidaču odabira, kao što možete vidjeti, još više opcija nego za numerički format. Odaberite položaj "poslije ili jednak". U polju na desnoj strani postavite vrijednost "04.05.2016". U donjem bloku postavljamo prekidač na položaj "do ili jednaka". U desnom polju unesite vrijednost "06.05.2016". Prekidač kompatibilnosti Stanje Ostavite u zadanom položaju - "i". Da biste primijenili filtriranje u akciji, pritisnite gumb "OK".
- Kao što možete vidjeti, naš je popis još više smanjio. Sada samo linije ostaju u njemu, u kojem se iznos prihoda varira od 10.000 do 15.000 rubalja za razdoblje od 04.05 do 06.05.2016.
- Možemo resetirati filtriranje u jednom od stupaca. Učinimo to za vrijednosti prihoda. Kliknite ikonu AutoFilter u odgovarajućem stupcu. Na padajućem popisu kliknite na stavku "Izbriši filtar".
- Kao što možete vidjeti, nakon ovih radnji, uzorak će se uzorak prihoda biti onemogućen, ali će se odabirati samo odabir (od 04/05/2016 do 06.05.2016.).
- Ova tablica ima još jedan stupac - "ime". Sadrži podatke u obliku teksta. Da vidimo kako formirati uzorak koristeći filtriranje kroz ove vrijednosti.
Kliknite ikonu filtra u nazivu stupca. Dosljedno idite na imena popisa "Text Filters" i "Prilagodljivi filtar ...".
- Prozor autofiltera korisnika ponovno se otvara. Napravimo uzorak imenima "krumpira" i "mesa". U prvom bloku, prekidač uvjeta postavljen na položaj "jednak". U polju desno od njega odgovara riječi "krumpir". Prekidač donje jedinice također će staviti "jednak" položaj. U polju nasuprot tome, napravim rekord - "meso". A sada provodimo ono što već nisu učinili: Postavite prekidač kompatibilnosti na položaj "ili". Sada će se prikazati crta koja sadrži bilo koji od navedenih uvjeta na zaslonu. Kliknite na gumb "OK".
- Kao što možete vidjeti, postoje ograničenja na datum u novom uzorku (od 04.05.2016 do 06.05.2016.) I po imenu (krumpir i meso). Nema ograničenja na iznos prihoda.
- Filtriranje možete u potpunosti ukloniti istim metodama koje su korištene za instalaciju. I nije važno koji je metoda korištena. Da biste resetirali filtriranje, dok je u kartici "Data" kliknite na gumb "Filter", koji se nalazi u skupini "Sortiraj i filtriranje".
Druga opcija uključuje prijelaz na karticu "Home". Kliknimo klik na vrpcu na gumb "Sortial i Filter" u jedinici za uređivanje. Na aktiviranom popisu kliknite na gumb "Filter".
Kada koristite bilo koju od gore navedenih metoda, filtriranje će se izbrisati, a rezultati uzorka su očišćeni. To jest, tablica će se prikazati cijeli niz podataka koje ima.
Lekcija: Funkcija auto filtra u Excelu
Metoda 2: Primjena formule polja
Učinite izbor također primijenite složenu formulu polja. Za razliku od prethodne verzije, ova metoda predviđa izlaz rezultata u zasebnom tablici.
- Na istom listu stvaramo prazan stol s istim imenima stupaca u zaglavlju kao izvor.
- Dodijelite sve prazne stanice prvog stupca novog tablice. Ugradite kursor u nizu formule. Upravo ovdje će se unijeti formula koja proizvodi uzorak na određenim kriterijima. Odabrat ćemo liniju, iznos prihoda u kojem prelazi 15.000 rubalja. U našem određenom primjeru, uvedena formula izgledat će na sljedeći način:
= Indeks (A2: A29; najmanji (ako (15000
Naravno, u svakom pojedinom slučaju, adresa stanica i raspona će biti vaša. Na ovom primjeru možete usporediti formulu s koordinatama na ilustraciji i prilagoditi ga za vaše potrebe.
- Budući da je to formula niza, kako bi se primijenili u akciji, morate pritisnuti gumb ENTER, ali CTRL + SHIFT + Unesite kombinaciju tipki. Mi to radimo.
- Nakon što označite drugi stupac s datumima i instaliranjem kursora u nizu formule, uvozimo sljedeći izraz:
= Indeks (B2: B29; najmanji (ako (15000
Kliknite kombinaciju tipki za tipku Ctrl + Shift +.
- Slično tome, u stupcu s prihodima unesite formulu sljedećeg sadržaja:
= Indeks (C2: C29; najmanji (ako (15000
Ponovno upišite CTRL + SHIFT + Unesite kombinaciju tipki.
U sva tri slučaja mijenja se samo prva vrijednost koordinata, a ostatak formule je potpuno identičan.
- Kao što vidimo, tablica je ispunjena podacima, ali njegov izgled nije u potpunosti atraktivan, štoviše, vrijednosti datuma su u njemu ispravno popunjene. Potrebno je ispraviti te nedostatke. Neispravnost datuma odnosi se na činjenicu da je format stanica odgovarajućeg stupca uobičajen, i moramo postaviti format datuma. Označavamo cijeli stupac, uključujući stanice s pogreškama i kliknite na označavanje desne tipke miša. Na popisu koji se pojavljuje, prođite kroz "format ćelija ...".
- U prozoru oblikovanja otvara se otvorite karticu "Broj". U bloku "brojčani formati" dodijelite vrijednost "datum". Na desnoj strani prozora možete odabrati željeni tip prikaza datuma. Nakon izlaganja postavki kliknite na gumb "OK".
- Sada se datum prikazuje ispravno. Ali, kao što vidimo, cijelo dno tablice je ispunjeno stanicama koje sadrže pogrešnu vrijednost "# broj!". U biti, to su te stanice, podaci iz uzorka za koje nisu imali dovoljno. Bilo bi privlačnije ako su uopće bili prazni. U te svrhe koristimo uvjetno oblikovanje. Označavamo sve tablice, osim zaglavlje. Dok je na kartici Početak kliknite na gumb "Uvjetno oblikovanje", koji je u bloku "stilova". Na popisu koji se pojavljuje, odaberite stavku "Napravite pravilo ...".
- U prozoru koji se otvara, odaberite vrstu pravila "format stanice koje sadrže". U prvom polju, pod natpisom "format samo stanice za koje slijedeće stanje" odaberite "pogrešku" položaj. Zatim kliknite gumb "Format ...".
- U prozoru oblikovanja idite na karticu "Font" i odaberite bijelo u odgovarajućem polju. Nakon tih radnji kliknite gumb "OK".
- Na gumbu s točno istim imenom, kliknite Nakon povratka u prozor za stvaranje ponašanja.
Sada imamo gotov uzorak na određenoj granici u odvojenom ispravno uređenom tablici.
Lekcija: Uvjetno oblikovanje u Excelu
Metoda 3: Uzorkovanje na nekoliko uvjeta pomoću formule
Kao i kada koristite filtar, pomoću formule možete odabrati u nekoliko uvjeta. Na primjer, uzmite istu izvornu tablicu, kao i praznu tablicu, gdje će se rezultati biti izlazni, s već izvedenim numeričkim i uvjetnim oblikovanjem. Uspostavit ćemo prvo ograničenje donje granice odabira prihoda u 15.000 rubalja, te drugo stanje gornje granice od 20.000 rubalja.
- Unesite u zaseban stupac, granični uvjeti za uzorkovanje.
- Kao iu prethodnoj metodi, naizmjenično rasporedite prazne stupce novog tablice i unesite odgovarajuće tri formule u njima. U prvom stupcu uvozimo sljedeći izraz:
= Indeks (A2: A29; najmanji (ako ((($ 2 = C2: C29); linija (C2: C29); ""); string (C2: C29) -strkok ($ c $ 1)) - linija ($ C $ 1))
U sljedećim stupcima, uklapaju se točno iste formule, samo promjenom koordinata odmah nakon imena operatera, indeks na odgovarajuće stupce koje nam je potrebno, analogno analogno s prethodnom metodom.
Svaki put nakon ulaska ne zaboravite dobiti CTRL + SHIFT + Unesite kombinaciju tipki.
- Prednost ove metode prije prethodne je da ako želimo promijeniti granice uzorka, neće biti potrebno promijeniti samonu formulu, koja je sama po sebi sasvim problematična. Dovoljno je u stupcu uvjeta na listu kako bi promijenio granične brojeve onih koji je potreban od strane korisnika. Rezultati odabira odmah će se automatski promijeniti.
Metoda 4: Slučajno uzorkovanje
U egzilu, uz pomoć posebne formule, također je moguće primijeniti slučajni odabir. To je potrebno u nekim slučajevima pri radu s velikom količinom podataka kada trebate predstaviti zajedničku sliku bez sveobuhvatne analize svih podataka polja.
- Lijevo od tablice preskočite jedan stupac. U ćeliji sljedeće kolone, koja se nalazi nasuprot prvoj ćeliji s podacima tablice, unesite formulu:
= Ljepilo ()
Ova značajka prikazuje slučajni broj. Da biste ga aktivirali, kliknite na gumb ENTER.
- Da biste napravili cijeli stupac slučajnih brojeva, postavite kursor u donji desni kut stanice, koji već sadrži formulu. Pojavljuje se marker za punjenje. Rastegnite ga s lijevom tipkom miša paralelno sa stolom s podacima do kraja.
- Sada imamo niz stanica ispunjenih slučajnim brojevima. Ali, sadrži formulu izračuna. Moramo raditi s čistim vrijednostima. Da biste to učinili, kopirajte na praznu kolonu na desnoj strani. Odaberite raspon stanica sa slučajnim brojevima. Smješten u kartici "Home", kliknite na ikonu "Kopiraj" na vrpci.
- Mi naglašavamo praznu stupac i kliknite desnom tipkom miša, pozivajući kontekstni izbornik. U alatnoj traci "Umetni parametri" odaberite klauzulu "Vrijednost", prikazana kao piktogrami s brojevima.
- Nakon toga, dok je u kartici "Home", kliknite na već poznatu ikonu "Sortiraj i filtriranje" ikone. Na padajućem popisu zaustavite odabir na stavci "Custom Sorting".
- Prozor postavki sortiranja je aktiviran. Svakako instalirajte oznaku nasuprot parametra "Moji podaci sadrže naslove" ako je kapica dostupna, ali bez krpelja. U polju "Sortiraj po" navedite ime tog stupca u kojem se nalaze kopirane vrijednosti slučajnih brojeva. U polje "Sortiraj" ostavite zadane postavke. U polje "Narudžba" možete odabrati parametar kao "uzlazni" i "silazno". Za slučajni uzorak ova vrijednost nema. Nakon što se postavke proizvode, kliknite na gumb "OK".
- Nakon toga, sve vrijednosti tablice ugrađene su u uzlaznom redoslijedu ili smanjenju slučajnih brojeva. Možete uzeti bilo koji broj prvih linija iz tablice (5, 10, 12, 15, itd.) I mogu se smatrati rezultatom slučajnog uzorka.
Lekcija: Razvrstavanje i filtriranje podataka u Excel
Kao što možete vidjeti, uzorak u Excel stolu može se proizvesti, i pomoću autofiltera i primjenom posebnih formula. U prvom slučaju rezultat će se prikazati u izvornoj tablici, au drugom - u odvojenom području. Moguće je proizvoditi odabir, i jedan uvjet i nekoliko. Osim toga, možete napraviti slučajni uzorak pomoću funkcije ljepila.