Dohvaćanje podataka u Microsoft Excelu

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" .

   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. 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 ..." .
3. 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" .

4. Kao što vidite, nakon filtracije postojale su samo linije u kojima je iznos prihoda veći od 10.000 rubalja.
5. 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" .

6. Sada je tablica ostavila samo linije u kojima iznos prihoda nije manji od 10.000 rubalja, ali ne prelazi 15.000 rubalja.
7. 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" .
8. 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" .
9. 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.
10. 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" .
11. 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).
12. 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 ..." .

13. 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" .
14. 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.
15. 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" .

    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" .

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.

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. 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.

3. 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.
4. 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 .

5. 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.

6. 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 ..." .
7. 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" .
8. 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 ..." .
9. 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 ..." .
10. 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" .
11. Na gumbu s istim imenom kliknite nakon povratka u prozor Stvori uvjet.

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

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. 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 .

3. 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.

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. 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.
3. 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.
4. 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.
5. Nakon toga na kartici "Početna" kliknite na poznatu ikonu "Sortiraj i filtriraj" . Na padajućem popisu zaustavite odabir stavke "Prilagođena vrsta" .
6. 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" .
7. 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.

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 .

• ← Korištenje interpolacije u programu Microsoft Excel
• Korištenje funkcije tablice u programu Microsoft Excel →