Prilikom rada s Excel proračunskim tablicama često je potrebno odabrati ih prema određenom kriteriju ili nekoliko uvjeta. U programu možete to učiniti na različite načine pomoću brojnih alata. Doznajemo kako napraviti izbor u programu Excel, koristeći različite opcije.

Izvršenje uzorka

Uzorkovanje podataka sastoji se u postupku odabira iz općeg polja onih rezultata koji zadovoljavaju navedene uvjete, a zatim ih izlažu na listu na zasebnom popisu ili u izvornom rasponu.

1. način: primijenite napredni autofilter

Najjednostavniji način odabira je korištenje proširenog autofiltera. Razmotrimo kako to učiniti na konkretan primjer.

  1. Odaberite područje na listu, među kojima morate odabrati. Na kartici "Početna" kliknite gumb "Sortiraj i filtriraj" . Postavlja se u blok za postavke "Uredi" . Na popisu koji se otvori nakon toga, kliknite gumb "Filtar" .

    Omogućivanje filtra u programu Microsoft Excel

    Postoji i mogućnost da to učinite na drugačiji način. Da biste to učinili, nakon odabira područja na listi, pomaknite se na karticu "Podaci" . Kliknemo na gumb "Filtar" koji se nalazi na vrpci u grupi "Sortiraj i filtriraj" .

  2. Omogućivanje filtra kroz karticu Podaci u programu Microsoft Excel

  3. Nakon ove akcije, ikone se pojavljuju u zaglavlju tablice da bi počele filtrirati u obliku malih obrnutih trokuta na desnom rubu ćelija. Kliknemo na ovu ikonu u naslovu stupca za koji želimo odabrati. Na izborniku koji se otvori, idite na stavku "Tekstni filtri" . Zatim odaberite stavku "Prilagođeni filtar ..." .
  4. Idite na prilagođeni filtar u programu Microsoft Excel

  5. Aktiviran je prozor filtriranja korisnika. U njemu možete odrediti ograničenje kojim će se odabir učiniti. Na padajućem popisu za stupac koji sadrži ćeliju numeričkog formata, koju koristimo za primjer, možete odabrati jednu od pet vrsta uvjeta:
    • jednaka je;
    • nejednak;
    • više;
    • veća ili jednaka;
    • manje.

    Postavimo primjer kao primjer da bismo odabrali samo vrijednosti pomoću kojih iznos prihoda prelazi 10.000 rubalja. Postavite prekidač na položaj "Više" . U desnom polju unesite vrijednost "10000" . Da biste izvršili akciju, kliknite gumb "U redu" .

  6. Korisnički filtar u Microsoft Excelu

  7. Kao što vidite, nakon filtracije postojale su samo linije u kojima je iznos prihoda veći od 10.000 rubalja.
  8. Rezultati filtriranja u programu Microsoft Excel

  9. Ali u istom stupcu možemo dodati drugo stanje. Da bismo to učinili, ponovno se vraćamo u prozor filtriranja korisnika. Kao što vidite, u donjem dijelu postoji još jedan prekidač za stanje i odgovarajući polje za unos. Sada postavimo gornju granicu odabira na 15.000 rubalja. Da biste to učinili, postavite prekidač na položaj "Manje" , au polju s desne strane upisujemo vrijednost "15000" .

    Osim toga, tu je i prekidač uvjeta. Ima dva "I" i "OR" pozicija. Prema zadanim se postavkama postavlja na prvu poziciju. To znači da će u uzorku biti samo linije koje zadovoljavaju oba ograničenja. Ako je postavljen na "OR" položaj, tada ostaju vrijednosti koje su prikladne za bilo koji od dva uvjeta. U našem slučaju morate prebaciti prekidač u položaj "I" , tj. Ostaviti ovu postavku kao zadanu. Nakon što unesete sve vrijednosti, kliknite gumb "OK" .

  10. Postavljanje gornje granice u prilagođenom filtru u programu Microsoft Excel

  11. Sada je tablica ostavila samo linije u kojima iznos prihoda nije manji od 10.000 rubalja, ali ne prelazi 15.000 rubalja.
  12. Rezultati filtriranja na donjoj i gornjoj granici u programu Microsoft Excel

  13. Slično tome, filtre možete konfigurirati u drugim stupcima. U tom slučaju, također je moguće pohraniti i filtriranje pod prethodnim uvjetima koji su postavljeni u stupcima. Dakle, vidjet ćemo kako se filtar radi za ćelije u datumu. Kliknite ikonu filtar u odgovarajućem stupcu. Dosljedno kliknite stavke na popisu "Filtriraj po datumu" i "Prilagođeni filtar" .
  14. Filtriranje datuma u Microsoft Excelu

  15. Prozor Custom AutoFilter ponovno je pokrenut. Izvršit ćemo odabir rezultata u tablici od 4. do 6. svibnja 2016. godine. U izborniku za odabir stanja, kao što vidite, postoji još više mogućnosti nego za numerički format. Odaberite položaj "Nakon ili jednako". U polju s desne strane postavite vrijednost na "04/05/2016" . U donjem dijelu postavite prekidač na položaj "Prije ili jednak" . U desnom polju upisujemo vrijednost "06.05.2016" . Prekidač za kompatibilnost stanja ostaje u zadanom položaju - "I" . Da biste primijenili filtriranje u akciji, kliknite gumb "OK" .
  16. Korisnički filtar za format datuma u programu Microsoft Excel

  17. Kao što vidite, naš popis je dodatno smanjen. Sada ostavlja samo linije u kojima se iznos prihoda razlikuje od 10.000 do 15.000 rubalja za razdoblje od 04.05 do 06.05.2016., Uključujući.
  18. Rezultati filtriranja zbrojem i datumom u Microsoft Excelu

  19. Možemo ponovno postaviti filtriranje u jedan od stupaca. Učinimo to za podatke o prihodima. Kliknite ikonu automatskog filtra u odgovarajućem stupcu. Na padajućem popisu kliknite stavku "Izbriši filtar" .
  20. Brisanje filtra iz jednog od stupaca u programu Microsoft Excel

  21. Kao što vidite, nakon ovih radnji, uzorak za iznos prihoda bit će onemogućen, a dostupni će se samo odabir prema datumima (od 04/05/2016 do 06/05/2016).
  22. Ograničenja samo po datumu u programu Microsoft Excel

  23. U ovoj tablici nalazi se još jedan stupac - "Ime" . Sadrži podatke u obliku teksta. Pogledajmo kako generirati uzorak filtriranjem tih vrijednosti.

    Kliknite ikonu filtra u nazivu stupca. Prođite kroz nazive popisa "Tekstni filtri" i "Prilagođeni filtar ..." .

  24. Prijeđite na filtriranje teksta u programu Microsoft Excel

  25. Ponovno se otvara prozor Custom AutoFilter. Napravimo izbor na nazivima "Krumpir" i "Meso" . U prvom bloku postavite prekidač stanja na "Jednostavan" položaj. U polju s desne strane upisujemo riječ "krumpir" . Prekidač donjeg dijela također se stavlja u položaj "Jednak" . Na polju nasuprot njemu pišemo - "Meso" . I tada izvršavamo ono što nismo učinili prije: postavite preklopku kompatibilnosti stanja na položaj "ILI" . Sada će se prikazati linija koja sadrži bilo koji od navedenih uvjeta. Kliknemo na gumb "OK" .
  26. Prilagođeni filtar za format teksta u programu Microsoft Excel

  27. Kao što vidite, u novom uzorku postoje ograničenja na datum (od 04/05/2016 do 06/05/2016) i po imenu (krumpir i meso). Iznos prihoda nije ograničen.
  28. Ograničenja po datumu i po imenu u Microsoft Excelu

  29. Potpuno uklanjanje filtra može biti ista metoda korištena za njegovo instaliranje. I nije važno koja je metoda korištena. Da biste ponovno postavili filtriranje, na kartici "Podaci" kliknite gumb "Filtar" koji se nalazi u grupi "Sortiraj i filtriraj" .

    Očistite filtar u Microsoft Excelu

    Druga opcija uključuje prebacivanje na karticu Početna . Tamo kliknite na vrpcu na gumbu "Sortiraj i filtriraj" u bloku "Uredi" . Na aktiviranom popisu kliknite gumb "Filtar" .

Očistite filtar na kartici Početna u Microsoft Excelu

Ako koristite jednu od gore navedenih dviju metoda, filtriranje će biti uklonjeno i rezultati izbora bit će izbrisani. To jest, tablica će prikazati cijeli niz podataka koje ima.

Filtar se vraća u Microsoft Excel

Pouka: AutoFilter funkcija u programu Excel

Način 2: Primjena formule polja

Izbor možete napraviti i primjenom složene formule polja. Za razliku od prethodne verzije, ova metoda pruža rezultat rezultata u zasebnoj tablici.

  1. Na istom listu izrađujemo praznu tablicu s istim nazivom stupaca u zaglavlju kao izvor.
  2. Izrada praznu tablicu u programu Microsoft Excel

  3. Odaberite sve prazne ćelije prvog stupca nove tablice. Postavite pokazivač u traku s formulama. Upravo ovdje će biti unesena formula koja će napraviti uzorak u skladu s navedenim kriterijima. Odabiremo redove, iznos prihoda koji premašuje 15.000 rubalja. U našem primjeru, formula za unos će izgledati ovako:

    =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Naravno, u svakom slučaju adresa ćelija i raspona bit će ista. U ovom primjeru možete usporediti formulu s koordinatama na slici i prilagoditi je za vaše potrebe.

  4. Unos formule u Microsoft Excel

  5. Budući da je riječ o formuli polja, kako biste ga primijenili u akciji, trebate pritisnuti tipku Enter i kombinaciju tipki Ctrl + Shift + Enter . Mi to radimo.
  6. Polje formule uneseno je u naslovnu kolonu u programu Microsoft Excel

  7. Odaberite drugi stupac s datumima i stavite pokazivač u liniju formule, unesite sljedeći izraz:

    =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Pritisnite tipku Ctrl + Shift + Enter .

  8. Polje formule uneseno je u stupac datuma u programu Microsoft Excel

  9. Slično tome, u stupcu s prihodom unosimo sljedeću formulu:

    =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

    Ponovno upišite Ctrl + Shift + Enter .

    U sva tri slučaja mijenja se samo prva vrijednost koordinata, a inače su formule potpuno identične.

  10. Polje formule uneseno je u stupac prihoda u programu Microsoft Excel

  11. Kao što vidite, tablica je popunjena podacima, ali njegov izgled nije vrlo atraktivan, osim toga, datumske vrijednosti se pogrešno popunjavaju. Potrebno je ispraviti ove nedostatke. Neispravan datum uzrokovan je činjenicom da je format ćelija odgovarajućeg stupca općenit, a mi moramo postaviti format datuma. Odaberite cijeli stupac, uključujući ćelije s pogreškama i desnom tipkom miša kliknite desnu tipku miša. Na popisu koji će se pojaviti idite na stavku "Format Cell ..." .
  12. Prebacivanje na oblikovanje ćelija u Microsoft Excelu

  13. U prozoru otvorenog formata otvorite karticu "Broj" . U bloku "Numerički formati" odaberite vrijednost "Datum" . U desnom dijelu prozora možete odabrati željenu vrstu prikaza datuma. Nakon što postavite postavke, kliknite gumb "U redu" .
  14. Postavljanje oblika datuma u programu Microsoft Excel

  15. Sada je datum ispravno prikazan. No, kao što možete vidjeti, cijeli donji dio tablice ispunjen je ćelijama koje sadrže pogrešnu vrijednost "# BROJ!" . Zapravo, to su stanice, podaci iz uzorka za koje nije bilo dovoljno. Bilo bi atraktivnije ako bi bili prazni. U ove svrhe koristimo uvjetno oblikovanje. Odaberite sve ćelije u tablici, osim poklopca. Na kartici "Početna" kliknite gumb "Uvjetno oblikovanje" koji se nalazi u okviru alata "Stilovi" . Na popisu koji će se pojaviti odaberite stavku "Izradi pravilo ..." .
  16. Idite za stvaranje pravila u programu Microsoft Excel

  17. U prozoru koji se otvori odaberite vrstu pravila "Format samo ćelije koje sadrže" . U prvom polju ispod teksta "Format samo stanice za koje je ispunjen sljedeći uvjet", odaberite stavku "Pogreške" . Zatim kliknite gumb "Format ..." .
  18. Idite na odabir formata u programu Microsoft Excel

  19. U prozoru za oblikovanje koji se otvori, idite na karticu "Font" i odaberite bijelu boju u odgovarajućem polju. Nakon ovih akcija kliknite gumb "U redu" .
  20. Format stanica u Microsoft Excelu

  21. Na gumbu s istim imenom kliknite nakon povratka u prozor Stvori uvjet.

Napravite stanje oblikovanja u programu Microsoft Excel

Sad imamo spremni uzorak za navedeno ograničenje u zasebnoj ispravno dizajniranoj tablici.

Uzorak je napravljen u Microsoft Excelu

Pouka: Uvjetno oblikovanje u programu Excel

Metoda 3: Višestruki uvjeti korištenja formule

Baš kao i upotreba filtra, možete upotrijebiti formulu za uzorak s nekoliko uvjeta. Na primjer, uzmite istu izvornu tablicu, kao i praznu tablicu, gdje će se rezultati prikazati, s već izvršenim numeričkim i uvjetnim oblikovanjem. Utvrđujemo prvo ograničenje donje granice odabira za prihod od 15.000 rubalja, a drugi uvjet je gornja granica od 20.000 rubalja.

  1. U zasebni stupac unosimo granične uvjete za uzorak.
  2. Uvjeti u Microsoft Excelu

  3. Kao u prethodnom postupku, jedan po jedan odabire prazne stupce nove tablice i umetne odgovarajuće tri formule u njih. U prvom stupcu unosimo sljedeći izraz:

    =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

    U sljedećim stupcima upisujemo točno iste formule, samo mijenjajući koordinate odmah nakon naziva operatora INDEX na stupcima koji odgovaraju stupcima koje nam je potrebno, analogno prethodnoj metodi.

    Svaki put nakon unosa, ne zaboravite upisati Ctrl + Shift + Enter .

  4. Više uvjeta u programu Microsoft Excel

  5. Prednost ove metode u odnosu na prethodno je da ako želimo promijeniti granice uzoraka, ne moramo mijenjati samu formulu polja, koja je sama po sebi vrlo problematična. Dovoljno je u stupcu uvjeta na listi promijeniti granične brojeve onima koje korisnik treba. Rezultati odabira automatski će se promijeniti.

Promjena rezultata uzorka u programu Microsoft Excel

4. metoda: slučajno uzorkovanje

U Excelu uz pomoć posebne formule RAND također može koristiti slučajni odabir. Potrebno ga je proizvesti u nekim slučajevima kada radite s velikom količinom podataka, kada trebate prikazati opću sliku bez složene analize svih podataka polja.

  1. S lijeve strane tablice prolazimo jedan stupac. U ćeliji sljedećeg stupca, koji je nasuprot prvoj ćeliji s podacima tablice, unosimo sljedeću formulu:

    =СЛЧИС()

    Ova funkcija prikazuje slučajni broj. Da biste ga aktivirali, pritisnite tipku ENTER .

  2. Nasumični broj u programu Microsoft Excel

  3. Da biste napravili cijeli stupac slučajnih brojeva, postavite pokazivač u donji desni kut ćelije, koji već sadrži formulu. Prikazuje se ručka za punjenje. Povucite je dolje pomoću lijeve tipke miša paralelno s podatkovnom tablicom do kraja.
  4. Popunjavanje u programu Microsoft Excel

  5. Sada imamo niz stanica ispunjenih slučajnim brojevima. No, ona sadrži formulu RAND-a . Također trebamo raditi sa čistim vrijednostima. Da biste to učinili, kopirajte u prazni stupac s desne strane. Odabiremo niz stanica s nasumičnim brojevima. Nakon što se nalazi na kartici "Početna" , kliknite ikonu "Kopiraj" na vrpci.
  6. Kopiranje u Microsoft Excel

  7. Odaberite prazan stupac i kliknite desnu tipku miša, pozivajući se na kontekstni izbornik. U skupini alata "Parametri umetanja" odaberite stavku "Vrijednosti" , označenu kao piktogram s brojevima.
  8. Lijepljenje u Microsoft Excel

  9. Nakon toga na kartici "Početna" kliknite na poznatu ikonu "Sortiraj i filtriraj" . Na padajućem popisu zaustavite odabir stavke "Prilagođena vrsta" .
  10. Prebacite se na prilagođeno sortiranje u programu Microsoft Excel

  11. Aktiviran je prozor za sortiranje. Provjerite potvrdni okvir pored stavke "Moji podaci sadrže zaglavlja" , ako postoji zaglavlje i nema kvačicu. U polju "Poredaj po" navedite naziv stupca u kojem su sadržane kopirane vrijednosti slučajnih brojeva. U polju "Sort" ostavljamo zadane postavke. U polju "Narudžba" možete odabrati opciju "Uzlazno" ili "Silazno" . Za slučajno uzorkovanje, ovo nije važno. Nakon podešavanja kliknite gumb "OK" .
  12. Postavite sortiranje u programu Microsoft Excel

  13. Nakon toga sve su vrijednosti tablice raspoređene u redoslijedu povećanja ili smanjenja 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 uzorkovanja.

Nasumično uzorkovanje u Microsoft Excelu

Pouka: Razvrstavanje i filtriranje podataka u programu Excel

Kao što možete vidjeti, izbor u Excel proračunskoj tablici može se obaviti uz pomoć automatskog filtra ili pomoću posebnih formula. U prvom slučaju, rezultat će biti prikazan u izvornoj tablici, a drugi - u zasebnom području. Moguće je odabrati, bilo po jednom ili više uvjeta. Osim toga, možete odabrati nasumično pomoću funkcije RANDOM .