Korištenje kriterija u programu Microsoft Excel

Microsoft Excel nije samo uređivač proračunskih tablica, već i moćan program za različite proračune. I konačno, ali ne najmanje bitno, ova se prilika pojavila zahvaljujući ugrađenim funkcijama. Uz pomoć nekih funkcija (operatora) možete čak odrediti uvjete izračuna, koji se nazivaju kriterijima. Naučimo detaljnije kako ih možete koristiti tokom rada u Excelu.

Kriteriji za primjenu

Kriteriji su uvjeti pod kojima program obavlja određene radnje. Koriste se u velikom broju ugrađenih funkcija. Njihovo ime najčešće sadrži izraz AKO. Ovoj grupi operatora, prije svega, potrebno je pripisati RAČUNANJE, COUNTIMO, SUMMES, SUMMESLIMN. Pored ugrađenih operatora, kriteriji u Excelu se koriste i za uvjetno oblikovanje. Razmotrimo njihovu upotrebu prilikom rada sa različitim alatima ovog tabličnog procesora.

RAČUNANJE

Glavni zadatak operatera RAČUNANJEpripadnost statističkoj grupi je brojanje zauzetih različitim vrijednostima ćelija koje zadovoljavaju određeni dani uvjet. Njegova sintaksa je sljedeća:

= COUNTIF (raspon; kriterijum)

Kao što vidite, ovaj operator ima dva argumenta. "Domet" predstavlja adresu matrice elemenata na listu u koje treba računati.

"Kriterij" - ovo je argument koji postavlja uvjet šta tačno moraju sadržavati ćelije navedenog područja kako bi bile uvrštene u brojanje. Kao parametar može se koristiti numerički izraz, tekst ili veza do ćelije u kojoj se kriterij nalazi. U ovom slučaju za označavanje kriterija možete koristiti sljedeće znakove: "<" (manje), ">" (više), "=" (jednako), "" (nije jednako) Na primjer, ako navedete izraz "<50", samo će se elementi navedeni argumentom uzeti u obzir prilikom izračuna "Domet", u kojima su brojčane vrijednosti manje od 50. Upotreba ovih znakova za označavanje parametara bit će relevantna za sve ostale mogućnosti, o kojima će u ovoj lekciji biti govora u nastavku.

Pogledajmo sada konkretan primjer kako ovaj operator funkcionira u praksi.

Dakle, tu je tabela u kojoj su prikazani prihodi od pet prodavnica sedmično. Moramo saznati broj dana za ovaj period u kojima je u trgovini 2 prihod od prodaje premašio 15.000 rubalja.

1. Odaberite element lima na kojem će operator ispostaviti rezultat izračuna. Nakon toga kliknite na ikonu "Umetanje funkcije".



2. Pokretanje Čarobnjaci za funkcije. Prelazimo na blok "Statistički". Tu pronalazimo i ističemo ime "COUNTIF". Zatim kliknite na gumb. "OK".



3. Prozor argumenata gornje izjave je aktiviran. U polju "Domet" potrebno je navesti područje ćelija među kojima će se izvršiti proračun. U našem slučaju treba istaknuti sadržaj retka "Prodavnica 2", u kojem se vrijednosti prihoda nalaze po danu. Postavljamo kursor u određeno polje i držeći lijevu tipku miša odaberite odgovarajući niz u tablici. Adresa odabranog niza prikazana je u prozoru.

   U sledećem polju "Kriterij" samo je potrebno postaviti parametar neposrednog odabira. U našem slučaju moramo brojati samo one elemente tablice u kojima vrijednost prelazi 15000. Stoga, pomoću tipkovnice, vozimo izraz u navedeno polje ">15000".

   Nakon što su sve gore navedene manipulacije izvršene, kliknite na gumb "OK".



4. Program broji i prikazuje rezultat u elementu lista koji je odabran prije aktivacije Čarobnjaci za funkcije. Kao što vidite, u ovom slučaju rezultat je jednak 5. To znači da u odabranom nizu u pet ćelija postoje vrijednosti veće od 15 000. To jest, možemo zaključiti da je u trgovini 2 u pet dana od sedam analiziranih prihod premašio 15.000 rubalja.

Lekcija: Čarobnjak za funkcije u Excelu

COUNTIMO

Sljedeća funkcija koja djeluje na kriterije je COUNTIMO. Takođe spada u statističku grupu operatera. Zadatak COUNTIMO broji ćelije u specificiranom nizu koje zadovoljavaju određeni skup uvjeta. Činjenica je da možete navesti ne jedan, već nekoliko parametara i razlikuje ovog operatera od prethodnog. Sintaksa je sljedeća:

= COUNTIME (condition_range1; condition1; condition_range2; condition2; ...)

"Opseg stanja" identičan je prvom argumentu prethodne izjave. Odnosno, to je veza na područje u kojem će se brojati ćelije koje zadovoljavaju navedene uvjete. Ovaj operater vam omogućuje da odredite više takvih područja odjednom.

"Stanje" predstavlja kriterij koji određuje koji će se elementi iz odgovarajućeg polja podataka računati, a koji ne. Svako područje podataka mora biti specificirano odvojeno, čak i ako se podudara. Neophodno je da svi nizovi koji se koriste kao područja stanja imaju isti broj redaka i stupaca.

Kako bi se postavilo nekoliko parametara istog područja podataka, na primjer, prebrojavanje broja ćelija u kojima su vrijednosti veće od određenog broja, ali manje od drugog broja, treba uzeti kao argument "Opseg stanja" navedite istu matricu nekoliko puta. Ali istovremeno, kao primjereni argumenti "Stanje" treba navesti različite kriterije.

Pomoću primjera iste tablice sa nedjeljnim prihodima od prodaje, pogledajmo kako to funkcionira. Moramo saznati broj dana u sedmici kada su dohodak na svim navedenim maloprodajnim mjestima dostigao normu utvrđenu za njih. Standardi prihoda su sljedeći:

• Trgovina 1 - 14 000 rubalja;
• Trgovina 2 - 15 000 rubalja;
• Trgovina 3 - 24 000 rubalja;
• Trgovina 4 - 11 000 rubalja;
• Trgovina 5 - 32.000 rubalja.

1. Da biste izvršili gornji zadatak, pomoću elementa radnog lista pomoću pokazivača odaberite gde će se prikazati rezultat obrade podataka COUNTIMO. Kliknite na ikonu "Umetanje funkcije".



2. Odlazak Čarobnjak za funkcijeponovo se prebacite na blok "Statistički". Na listi bi trebalo biti ime COUNTIMO i odaberite ga. Nakon izvođenja navedene akcije, trebate pritisnuti tipku "OK".



3. Nakon izvršenja gornjeg algoritma radnji, otvara se prozor argumenta COUNTIMO.

   U polju "Raspon stanja 1" unesite adresu retka na kojem se nalaze podaci o trgovini 1, prihod za tjedan. Da biste to učinili, stavite kursor u polje i odaberite odgovarajući red u tabeli. Koordinate su prikazane u prozoru.

   S obzirom na to da za Store 1 dnevna stopa prihoda iznosi 14.000 rubalja, onda na terenu "Uslov 1" napišite izraz ">14000".

   Na polja "Opseg stanja 2 (3,4,5)" treba unijeti koordinate redaka sa tjednim prihodima trgovine 2, trgovine 3, trgovine 4 i trgovine 5. Akcija se izvodi prema istom algoritmu kao i za prvi argument ove grupe.

   Na polja "Stanje 2", "Stanje 3", "Stanje4" i "Stanje5" unosimo u skladu s tim vrijednosti ">15000", ">24000", ">11000" i ">32000". Kao što možda nagađate, ove vrijednosti odgovaraju intervalu prihoda koji premašuje normu za odgovarajuću trgovinu.

   Nakon što ste unijeli sve potrebne podatke (ukupno 10 polja), kliknite na gumb "OK".



4. Program broji i prikazuje rezultat na ekranu. Kao što vidite, ono je jednako broju 3. To znači da je za tri dana od analiziranog tjedna prihod na svim prodajnim mjestima premašio normu utvrđenu za njih.

Sada promijenimo zadatak. Trebali bismo izračunati broj dana u kojima je Shop 1 primio prihod veći od 14.000 rubalja, ali manji od 17.000 rubalja.

1. Kursor postavljamo u element u kojem će se proizvoditi na listu rezultata brojanja. Kliknite na ikonu "Umetanje funkcije" preko radne površine lista.



2. Otkad smo nedavno primijenili formulu COUNTIMO, sada ne morate ići u grupu "Statistički" Čarobnjaci za funkcije. Naziv ovog operatera može se naći u kategoriji "10 nedavno korištenih". Odaberite ga i kliknite na gumb. "OK".



3. Otvara se prozor poznatog operatora argumenata. COUNTIMO. Stavite kursor u polje "Raspon stanja 1" i držeći lijevu tipku miša odaberite sve ćelije koje sadrže prihod do dana Store 1. Smještene su u liniji, koja se naziva "Prodavnica 1". Nakon toga će se koordinate određenog područja reflektirati u prozoru.

   Zatim postavite kursor u polje "Uslov 1". Ovdje moramo navesti donju granicu vrijednosti u ćelijama koje će sudjelovati u proračunu. Navedite izraz ">14000".

   U polju "Raspon stanja 2" na istu adresu unesite na isti način kao i u polje "Raspon stanja 1", to jest, ponovo unosimo koordinate ćelija s vrijednostima prihoda za prvu utičnicu.

   U polju "Stanje 2" navedite gornju granicu odabira: "<17000".

   Nakon što su sve navedene radnje izvršene, kliknite na gumb "OK".



4. Program daje rezultat izračuna. Kao što vidite, konačna vrijednost je 5. To znači da je za 5 dana od sedam proučenih, prihod u prvoj trgovini bio u rasponu od 14.000 do 17.000 rubalja.

SUMMES

Drugi operater koji koristi kriterije je SUMMES. Za razliku od prethodnih funkcija, on pripada matematičkom bloku operatora. Njegov je zadatak da objedini podatke u ćelijama koji odgovaraju određenom stanju. Sintaksa je sljedeća:

= SUMMES (raspon; kriterij; [sum_range])

Argument "Domet" označava područje ćelija koje će se provjeriti u skladu sa stanjem. Zapravo je postavljen po istom principu kao i istoimeni argument funkcije RAČUNANJE.

"Kriterij" - je obvezan argument koji određuje izbor ćelija iz određenog područja podataka koje treba dodati. Principi specificiranja su isti kao i za slične argumente prethodnih operatera koje smo gore ispitali.

"Opseg sumiranja" Ovo je neobavezan argument. Ukazuje na određeno područje polja u kojem će se izvesti zbrajanje. Ako ga izostavite i ne navedete, onda se podrazumijevano smatra da je jednaka vrijednosti traženog argumenta "Domet".

Sada, kao i uvijek, razmotrite primjenu ovog operatora u praksi. Na temelju iste tablice suočeni smo sa zadatkom izračunavanja iznosa prihoda u trgovini 1 za razdoblje od 11. ožujka 2017. godine.

1. Odaberite ćeliju u kojoj će se izlaziti rezultat. Kliknite na ikonu. "Umetanje funkcije".



2. Odlazak Čarobnjak za funkcije u bloku "Matematički" pronađite i istaknite ime SUMMS. Kliknite na dugme "OK".



3. Pokreće se prozor argumenta funkcije SUMMES. Ima tri polja koja odgovaraju argumentima navedenog operatera.

   U polju "Domet" unesite područje tablice u kojem će se nalaziti vrijednosti za provjeru usklađenosti s uvjetima. U našem slučaju to će biti niz datuma. Stavite kursor u ovo polje i odaberite sve ćelije koje sadrže datume.

   Budući da na terenu moramo dodati samo prihod koji počinje od 11. marta "Kriterij" pogon vrijednosti ">10.03.2017".

   U polju "Opseg sumiranja" trebate navesti područje čije će vrijednosti koje ispunjavaju navedene kriterije biti sumirane. U našem slučaju to su linijske vrednosti prihoda "Shop1". Odaberite odgovarajući niz elemenata lista.

   Nakon unosa svih navedenih podataka kliknite na gumb "OK".



4. Nakon toga, rezultat obrade podataka pomoću funkcije bit će prikazan u prethodno navedenom elementu radnog lista. SUMMES. U našem slučaju je jednaka 47921,53. To znači da je od 11. marta 2017. i do kraja analiziranog razdoblja ukupni prihod za Shop 1 iznosio 47.921,53 rubalja.

SUMMESLIMN

Završavamo studiju operatora koji koriste kriterije, fokusirajući se na funkcije SUMMESLIMN. Cilj ove matematičke funkcije je sažeti vrijednosti označenih područja tablice, odabranih prema nekoliko parametara. Sintaksa navedenog operatora je sljedeća:

= SUMMER (sum_range; stanje_range1; stanje1; stanje_range2; uslov2; ...)

"Opseg sumiranja" - ovo je argument, a to je adresa matrice u koju će se dodati ćelije koje ispunjavaju određeni kriterij.

"Opseg stanja" - argument, koji je niz podataka, provjerava se u skladu s uvjetom;

"Stanje" - argument koji predstavlja kriterij odabira za dodavanje.

Ova funkcija podrazumijeva operacije s nekoliko skupova sličnih operatera odjednom.

Pogledajmo kako je ovaj operater primjenjiv za rješavanje problema u kontekstu tablice s prihodima od prodaje na maloprodajnim mjestima. Morat ćemo izračunati prihod koji je Trgovina 1 donijela za razdoblje od 9. do 13. ožujka 2017. godine. U ovom slučaju, prilikom zbrajanja prihoda treba uzeti u obzir samo one dane u kojima je prihod premašio 14.000 rubalja.

1. Opet odaberite ćeliju za prikaz ukupnog broja i kliknite na ikonu "Umetanje funkcije".



2. In Čarobnjak za funkcijePrije svega, prelazimo na blok "Matematički", i tamo biramo stavku koja se zove SUMMESLIMN. Kliknite na dugme. "OK".



3. Pokreće se prozor argumenata operatera, čije je ime gore navedeno.

   Postavite kursor u polje "Opseg sumiranja". Za razliku od sljedećih argumenata, ovaj svojevrsni ukazuje i na niz vrijednosti gdje će se sakupljati podaci koji odgovaraju navedenim kriterijima. Zatim odaberite područje reda "Shop1", u kojem se nalaze vrijednosti prihoda za odgovarajuće prodajno mjesto.

   Nakon što se adresa prikaže u prozoru, idite na polje "Raspon stanja 1". Ovdje ćemo morati prikazati koordinate niza s datumima. Stisnite lijevi gumb miša i odaberite sve datume u tablici.

   Stavite kursor u polje "Uslov 1". Prvi je uvjet da ćemo podatke sažeti najkasnije do 09. marta. Stoga unesite vrijednost ">08.03.2017".

   Prelazimo na argument "Raspon stanja 2". Ovdje morate unijeti iste koordinate koje su zabilježene u polju "Raspon stanja 1". To radimo na isti način, odnosno isticanjem crte s datumima.

   Postavite kursor u polje "Stanje 2". Drugi je uvjet da dani za koje će se dodavati prihod moraju biti najkasnije do 13. marta. Stoga pišemo sljedeći izraz: "<14.03.2017".

   Idi na teren "Raspon stanja 2". U ovom slučaju trebamo odabrati isti niz čija je adresa unesena kao niz sažetka.

   Nakon što se adresa određenog niza prikaže u prozoru, idite na polje "Stanje 3". S obzirom da će u zbrajanju učestvovati samo vrijednosti čija vrijednost prelazi 14 000 rubalja, napravimo unos sljedeće prirode: ">14000".

   Nakon dovršetka zadnje akcije kliknite na gumb "OK".



4. Program prikazuje rezultat na listu. To je jednako 62491,38. To znači da je za period od 9. do 13. marta 2017. zbroj prihoda kada ga dodate za dane u kojima prelazi 14.000 rubalja iznosio 62.491,38 rubalja.

Uslovno oblikovanje

Posljednji alat koji smo opisali koji koristi kriterije za rad s njim je uvjetno oblikovanje. Izvodi specificiranu vrstu ćelija za oblikovanje koje ispunjavaju navedene uvjete. Pogledajte primjer rada sa uvjetnim oblikovanjem.

Te ćelije u tabeli biramo plavom bojom, gdje dnevne vrijednosti prelaze 14 000 rubalja.

1. U tablici odabiremo čitav niz elemenata, koji prikazuje prihod prodajnih mjesta po danu.



2. Pomicanje na karticu "Početna". Kliknite na ikonu Uslovno oblikovanjesmešteni u blok Stilovi na vrpci. Otvara se popis radnji. Kliknite na nju u položaju "Stvorite pravilo ...".



3. Aktivira se prozor za generisanje pravila formatiranja. U području odabira vrste pravila odaberite ime "Formatirajte samo ćelije koje sadrže". U prvom polju bloka stanja s liste mogućih opcija odaberite "Vrednost ćelije". U sljedećem polju odaberite položaj Još. U zadnjem - navedite samu vrijednost, više od koje želite formatirati elemente tablice. Imamo 14000. Da biste odabrali vrstu formatiranja, kliknite na gumb "Formatiraj ...".



4. Aktivira se prozor za formatiranje. Pomicanje na karticu "Fill". Od predloženih opcija za popunu boja odaberite plavu tipku lijevim klikom na nju. Nakon što se u području prikazuje odabrana boja Uzorakkliknite na gumb "OK".



5. Prozor generacije pravila oblikovanja automatski se vraća. U njemu takođe na terenu Uzorak prikazana je plava boja. Ovdje moramo obaviti jednu jedinu radnju: kliknite na gumb "OK".



6. Nakon posljednje akcije sve ćelije odabranog niza, koje sadrže broj veći od 14000, bit će ispunjene plavom bojom.

Više informacija o mogućnostima uvjetnog oblikovanja raspravlja se u zasebnom članku.

Lekcija: Uslovno oblikovanje u Excelu

Kao što vidite, pomoću alata koji u svom radu koriste kriterije, Excel može riješiti prilično raznolike probleme. To može biti, poput izračunavanja iznosa i vrijednosti, i oblikovanja, kao i provođenja mnogih drugih zadataka. Glavni alati koji rade u ovom programu s kriterijima, to jest, s određenim uvjetima pod kojima se ova radnja aktivira, skup je ugrađenih funkcija, kao i uvjetno oblikovanje.