Microsoft Excel nije samo urednik za proračunske tablice, već i najsnažnija aplikacija za različite izračune. Na kraju, ali ne i najmanje važno, ova značajka dolazi sa ugrađenim funkcijama. Uz pomoć nekih funkcija (operatora) moguće je odrediti i uslove izračuna, koji se obično nazivaju kriterijumima. Da naučimo više o tome kako ih možete koristiti kada radite u Excelu.
Primjena kriterija
Kriterijumi su uslovi pod kojima program obavlja određene radnje. Koriste se u različitim ugrađenim funkcijama. Njihovo ime najčešće sadrži izraz "IF" t. Ovoj grupi operatera, pre svega, treba pripisati COUNTES, COUNTERSILN, Sums, SUMMESLIMN. Osim ugrađenih operatora, kriteriji u Excelu se također koriste u uvjetnom formatiranju. Razmotrite njihovu upotrebu kada radite sa raznim alatima ovog procesora tabele detaljnije.
COUNTES
Glavni zadatak operatera COUNTESkoji pripadaju statističkoj grupi, je broj zauzetih različitim vrednostima ćelija koje zadovoljavaju određeni određeni uslov. Njegova sintaksa je sledeća:
= BROJEVI (raspon; kriterij)
Kao što vidite, ovaj operator ima dva argumenta. "Range" je adresa niza elemenata na listu u kojem treba napraviti brojanje.
"Kriterijum" - ovo je argument koji postavlja uslov da ćelije navedenog područja moraju sadržati da bi bile uključene u brojanje. Kao parametar može se koristiti numerički izraz, tekst ili referenca na ćeliju koja sadrži kriterij. U ovom slučaju, za označavanje kriterija, možete koristiti sljedeće znakove: "<" ("manje"), ">" ("više"), "=" (jednak), "" ("nije jednako"). Na primjer, ako navedete izraz "<50", onda će izračun uzeti u obzir samo elemente specificirane argumentom "Range"u kojima su numeričke vrijednosti manje od 50. Upotreba ovih znakova za određivanje parametara bit će relevantna za sve druge opcije, o čemu će biti riječi u ovoj lekciji ispod.
A sada pogledajmo konkretan primer kako ovaj operater radi u praksi.
Dakle, postoji tabela koja prikazuje prihod od pet prodavnica nedeljno. Potrebno je da saznamo broj dana tokom ovog perioda, u kome je prihod od prodaje prodavnice 2 premašio 15.000 rubalja.
- Odaberite element lista u kojem će operator ispisati rezultat izračuna. Nakon toga kliknite na ikonu "Umetni funkciju".
- Launch Funkcioneri. Premjesti u blok "Statistical". Tamo nalazimo i biramo ime "COUNTES". Zatim kliknite na dugme "OK".
- Aktivacija prozora argumenata gore navedenog operatora. Na terenu "Range" označite područje ćelija među kojima će se brojati. U našem slučaju, izaberite sadržaj linije. "Prodavnica 2"u kojoj se nalaze vrijednosti prihoda po danu. Postavite kursor u specificirano polje i, držeći lijevi gumb miša, odaberite odgovarajući niz u tablici. Adresa odabranog niza pojavljuje se u prozoru.
U sledećem polju "Kriterijum" samo trebate postaviti parametar trenutnog odabira. U našem slučaju, treba da prebrojite samo one elemente tabele u kojima vrednost prelazi 15.000, tako da pomoću tastature unosimo izraz u navedeno polje. ">15000".
Nakon što se sve gore navedene manipulacije završe, kliknite na dugme. "OK".
- Program izračunava i prikazuje rezultat u elementu lista koji je odabran prije aktivacije. Funkcioneri. Kao što možete vidjeti, u ovom slučaju rezultat je jednak broju 5. To znači da u odabranom nizu u pet ćelija postoje vrijednosti koje prelaze 15.000.To znači, možemo zaključiti da je u Trgovini 2 za pet dana od analiziranih sedam, prihod veći od 15.000 rubalja.
Lekcija: Majstor funkcija u Excelu
COUNTERSILN
Sledeća funkcija koja se bavi kriterijumom je COUNTERSILN. Ona također pripada statističkoj grupi operatora. Zadatak COUNTERSILN je brojanje ćelija u specificiranom nizu koje zadovoljavaju određeni skup uslova. To je činjenica da možete specificirati ne jedan, već nekoliko parametara, i razlikovati ovog operatera od prethodnog. Sintaksa je sljedeća:
= COUNTRY (uvjet_razina1; uvjet1; uvjet_razina2; uvjet2; ...)
"Opseg dometa" identičan je prvom argumentu prethodne izjave. To jest, to je veza sa područjem u kojem će se računati ćelije koje zadovoljavaju navedene uslove. Ovaj operator vam omogućava da navedete nekoliko takvih područja odjednom.
"Stanje" je kriterij koji određuje koji će se elementi iz odgovarajućeg skupa podataka prebrojati, a koji neće. Svako dato područje podataka mora specificirati uvjet zasebno, čak i ako se podudara. Važno je da svi nizovi koji se koriste kao uslovna područja imaju isti broj redova i kolona.
Da bi se postavilo nekoliko parametara iste oblasti podataka, na primjer, za brojanje ćelija u kojima su vrijednosti veće od određenog broja, ali manje od drugog broja, koristi se sljedeći argument: "Opseg dometa" nekoliko puta odredite isti niz. Ali u isto vrijeme kao i odgovarajući argumenti "Stanje" treba navesti različite kriterije.
Koristeći primer iste tabele sa nedeljnom prodajom prodavnica, da vidimo kako to radi. Moramo saznati broj dana u sedmici kada su prihodi u svim navedenim poslovnicama dostigli normu koja je za njih utvrđena. Standardi prihoda su sljedeći:
- Prodavnica 1 - 14.000 rubalja;
- Prodavnica 2 - 15.000 rubalja;
- Prodavnica 3 - 24.000 rubalja;
- Prodavnica 4 - 11.000 rubalja;
- Trgovina 5 - 32.000 rubalja.
- Da biste izvršili gore navedeni zadatak, odaberite element radnog lista s pokazivačem na kojem će biti prikazan rezultat obrade podataka. COUNTERSILN. Kliknite na ikonu "Umetni funkciju".
- Going to Čarobnjak za funkcije, ponovo se blokira "Statistical". Lista treba da pronađe ime COUNTERSILN i čine ga selekcijom. Nakon izvršenja navedene akcije, morate kliknuti na gumb. "OK".
- Nakon izvršenja gore navedenog algoritma akcije, otvara se prozor argumenta. COUNTERSILN.
Na terenu "Condition Range1" morate unijeti adresu linije u kojoj su podaci o prihodu Store 1 za tjedan. Da biste to uradili, postavite kursor u polje i izaberite odgovarajući red u tabeli. Koordinate su prikazane u prozoru.
S obzirom da je za Store 1 dnevna stopa prihoda 14.000 rubalja, zatim na terenu "Uslov 1" enter expression ">14000".
U poljima "Stanje Range2 (3,4,5)" Potrebno je unijeti koordinate linija sa tjednim prihodima Store 2, Store 3, Store 4 i Store 5. Radnju izvodimo po istom algoritmu kao za prvi argument ove grupe.
U poljima "Condition2", "Condition3", "Condition4" i "Condition5" unosimo vrednosti ">15000", ">24000", ">11000" i ">32000". Kao što možete pretpostaviti, ove vrijednosti odgovaraju intervalu prihoda, koji premašuje normu za odgovarajuću trgovinu.
Nakon što unesete sve potrebne podatke (ukupno 10 polja), kliknite na dugme "OK".
- Program izračunava i prikazuje rezultat na ekranu. Kao što vidite, to je jednako broju 3. To znači da je za tri dana od analizirane sedmice prihod u svim poslovnicama premašio utvrđenu stopu za njih.
Sada ćemo malo promijeniti zadatak. Trebalo bi izračunati broj dana u kojima je Prodavnica 1 primila prihod veći od 14.000 rubalja, ali manje od 17.000 rubalja.
- Postavite kursor u element gdje će se izlaz prikazati na listu rezultata prebrojavanja. Kliknite na ikonu "Umetni funkciju" preko radne površine lista.
- Pošto smo nedavno primenili formulu COUNTERSILN, sada nije potrebno ići u grupu "Statistical" Funkcioneri. Ime ovog operatera može se naći u kategoriji "10 Nedavno korištenih". Izaberite je i kliknite na dugme. "OK".
- Otvara se prozor poznatih operativnih argumenata. COUNTERSILN. Stavite kursor u polje "Condition Range1" i, nakon što ste pritisnuli levi taster miša, izaberite sve ćelije koje sadrže prihode po danima prodavnice 1. Nalaze se u redu, koji se zove "Prodavnica 1". Nakon toga, koordinate navedenog područja će biti prikazane u prozoru.
Zatim postavite kursor na polje "Condition1". Ovdje moramo odrediti donju granicu vrijednosti u ćelijama koje će sudjelovati u izračunu. Navedite izraz ">14000".
Na terenu "Opseg 2" unosimo istu adresu na isti način na koji smo ušli u polje "Condition Range1", to jest, ponovo unosimo koordinate ćelija sa prihodima iz prvog izlaza.
Na terenu "Condition2" odredite gornju granicu odabira: "<17000".
Nakon izvršenja svih navedenih akcija, kliknite na dugme. "OK".
- Program daje rezultat izračuna. Kao što vidimo, ukupna vrijednost je 5. To znači da je za pet dana od sedam studiranih, prihod u prvoj prodavnici bio u rasponu od 14.000 do 17.000 rubalja.
Sums
Drugi operator koji koristi kriterij je Sums. Za razliku od prethodnih funkcija, odnosi se na matematički blok operatora. Njegov zadatak je da doda podatke u ćelije koje ispunjavaju određeni uslov. Sintaksa je:
= SUMMERS (raspon; kriterij; [sum_range]]
Argument "Range" ukazuje na područje ćelija koje će biti provjerene za usklađenost. U stvari, postavljen je na istom principu kao i argument funkcije istog imena. COUNTES.
"Kriterijum" - je obavezni argument koji postavlja parametar za odabir ćelija iz specificiranog područja podataka koje treba dodati. Principi navođenja isti kao i kod sličnih argumenata prethodnih operatora, koje smo razmatrali gore.
"Raspon zbrajanja" - Ovo je opcionalni argument. Označava specifično područje niza u kojem će se izvršiti zbrajanje. Ako je izostavite i ne navedete, onda se podrazumevano smatra da je jednaka vrijednosti traženog argumenta "Range".
Sada, kao i uvijek, razmotrite primjenu ovog operatora u praksi. Na osnovu iste tabele, suočavamo se sa zadatkom izračunavanja iznosa prihoda u Prodavnici 1 za period koji počinje od 11.03.2017.
- Izaberite ćeliju u kojoj će rezultat biti prikazan. Kliknite na ikonu "Umetni funkciju".
- Going to Čarobnjak za funkcije u bloku "Matematički" pronađite i izaberite ime "SUMMESLI". Kliknite na dugme "OK".
- Počinje prozor argumenta funkcije. Sums. Ima tri polja koja odgovaraju argumentima navedenog operatora.
Na terenu "Range" unosimo područje tabele u kojoj će se nalaziti vrijednosti koje se provjeravaju u skladu sa uvjetima. U našem slučaju to će biti niz datuma. Postavite kursor u ovo polje i odaberite sve ćelije koje sadrže datume.
Budući da nam je samo potrebno dodati sredstva od 11. marta, zatim na terenu "Kriterijum" vozimo se u vrijednosti ">10.03.2017".
Na terenu "Raspon zbrajanja" morate specificirati područje, čije vrijednosti zadovoljavaju zadane kriterije će biti zbrojene. U našem slučaju, to su prihodne vrijednosti linije. "Shop1". Odaberite odgovarajući niz listnih elemenata.
Nakon uvođenja svih navedenih podataka, kliknite na dugme "OK".
- Nakon toga, rezultat obrade podataka od strane funkcije će biti prikazan u prethodno specificiranom elementu radnog lista. Sums. U našem slučaju, on je jednak 47921.53. To znači da je od 11.03.2017, pa do kraja analiziranog perioda, ukupan prihod za Prodavnicu 1 iznosio 47.921,53 rubalja.
SUMMESLIMN
Završićemo studiju operatora koji koriste kriterijume, fokusirajući se na funkciju SUMMESLIMN. Zadatak ove matematičke funkcije je da sumira vrijednosti označenih područja tablice, odabranih prema nekoliko parametara. Sintaksa ovog operatora je:
= SUMMESLIMN (sum_range_range; condition_range1; condition1; condition_range2; condition2; ...)
"Raspon zbrajanja" - Ovo je argument koji je adresa polja, ćelije u kojima se, odgovarajući određenom kriterijumu, dodaje.
"Opseg dometa" - argument koji predstavlja niz podataka, provjeren za usklađenost sa stanjem;
"Stanje" - argument koji predstavlja kriterij odabira za dodavanje.
Ova funkcija uključuje operacije s nekoliko skupova sličnih operatora odjednom.
Da vidimo kako je ovaj operator primjenjiv na rješavanje problema u kontekstu naše prodajne tablice u prodajnim mjestima. Potrebno je da izračunamo prihode koje donosi Prodavnica 1 za period od 9. marta do 13. marta 2017. godine. U ovom slučaju, zbrajanje prihoda treba uzeti u obzir samo one dane, prihod od kojeg je premašio 14.000 rubalja.
- Ponovo odaberite ćeliju da biste prikazali ukupnu količinu i kliknite na ikonu. "Umetni funkciju".
- U Čarobnjak za funkcijePrvo, prelazimo na blok. "Matematički", i tu odabiremo poziciju "SUMMESLIMN". Kliknite na dugme "OK".
- Pokreće se prozor argumenata operatora, čije je ime navedeno gore.
Postavite kursor u polje "Raspon zbrajanja". Za razliku od naknadnih argumenata, ovo je jedna vrsta i ukazuje na niz vrijednosti gdje će biti zbrojeni podaci koji odgovaraju navedenim kriterijima. Zatim odaberite područje linije "Shop1"U koje se stavljaju vrijednosti prihoda za odgovarajuće prodajno mjesto.
Nakon što se adresa prikaže u prozoru, idite na polje "Condition Range1". Ovdje ćemo morati prikazati koordinate niza s datumima. Napravimo isječak lijeve tipke miša i odaberemo sve datume u tablici.
Stavite kursor u polje "Condition1". Prvi uslov je da podatke sažimamo do 9. marta. Stoga unosimo vrijednost ">08.03.2017".
Pređite na argument "Opseg 2". Ovdje trebate unijeti iste koordinate koje su zabilježene u polju "Condition Range1". Mi to radimo na isti način, to jest, isticanjem linije sa datumima.
Postavite kursor u polje "Condition2". Drugi uslov je da dani za koje se dodaju prihodi budu najkasnije do 13. marta. Stoga, napišite sljedeći izraz: "<14.03.2017".
Idi na teren "Opseg 2". U ovom slučaju, moramo odabrati isti niz čija je adresa unesena kao niz sumacija.
Nakon što se adresa navedenog niza prikaže u prozoru, idite na polje "Condition3". Uzimajući u obzir da će u sumaciji učestvovati samo vrijednosti koje prelaze 14.000 rubalja, unosimo sljedeće: ">14000".
Nakon zadnje akcije, kliknite na gumb "OK".
- Program prikazuje rezultat na listu. To je jednako 62491.38. To znači da je za period od 09. do 13. marta 2017. iznos prihoda kada ga dodate za dane u kojima prelazi 14.000 rubalja iznosio je 62.491,38 rubalja.
Conditional Formatting
Posljednja, opisana od nas, alatka, pri radu s kojim se kriterijima koristi, je uvjetno formatiranje. On izvršava specificirani tip ćelija za formatiranje koje zadovoljavaju specificirane uslove. Pogledajte primer rada sa uslovnim formatiranjem.
Označite te ćelije u tabeli plavom bojom, gdje vrijednosti za dan prelaze 14.000 rubalja.
- Odaberite cijeli niz elemenata u tablici, koji prikazuje prihod maloprodajnih mjesta po danu.
- Pomaknite se na karticu "Home". Kliknite na ikonu "Uslovno oblikovanje"stavljen u blok "Stilovi" na kaseti. Otvara se lista radnji. Klatsat u njemu na poziciji "Napravi pravilo ...".
- Prozor za generisanje pravila formatiranja je aktiviran. U polju pravila za odabir tipa odaberite ime "Formatiraj samo ćelije koje sadrže". U prvom polju bloka uvjeta sa liste mogućih izbora odaberite "Cell Value". U sledećem polju izaberite poziciju "Više". U potonjem, označavamo samu vrijednost, od čega je veća formatiranje elemenata tablice. Imamo 14 000. Za odabir tipa formatiranja, kliknite na dugme. "Format ...".
- Prozor za formatiranje je aktiviran. Pomaknite se na karticu "Fill". Iz predloženih opcija boja popune, odaberite plavo klikom na njega lijevim gumbom miša. Nakon što se izabrana boja prikaže u "Uzorak"kliknite na dugme "OK".
- Automatski se vraća na prozor za generisanje pravila formatiranja. To je takođe u tom području "Uzorak" prikazano plavom bojom. Ovde moramo izvršiti jednu radnju: kliknite na dugme "OK".
- Nakon zadnje akcije, sve ćelije odabranog niza, koje sadrže broj veći od 14000, bit će popunjene plavom bojom.
Više informacija o mogućnostima uslovnog formatiranja opisano je u posebnom članku.
Lekcija: Uslovno formatiranje u Excelu
Kao što vidimo, pomoću alata koji koriste kriterijume u svom radu, u Excelu se mogu riješiti raznovrsni zadaci. To mogu biti brojanje i vrijednosti i formatiranje, kao i obavljanje mnogih drugih zadataka. Glavni alati koji rade u ovom programu sa kriterijima, to jest, sa određenim uslovima pod kojima se ova akcija aktivira, su skup ugrađenih funkcija, kao i uslovno formatiranje.
