Korištenje kriterija u programu Microsoft Excel

Microsoft Excel nije samo urednik proračunske tablice, već i snažan program za različite izračune. Posljednje, ali ne manje važno, ova je značajka zbog ugrađenih funkcija. Pomoću određenih funkcija (operatora) moguće je odrediti čak i uvjete izračuna, koji se nazivaju kriterijima. Učimo detaljnije kako ih koristiti prilikom rada u programu Excel.

Primjena kriterija

Kriteriji su uvjeti pod kojima program izvodi određene radnje. Koriste se u brojnim ugrađenim funkcijama. U njihovo ime, izraz "IF" najčešće je prisutan. Za ovu grupu operatora, prije svega, potrebno je uključiti ZEMLJE , RAČUNATE , SUM , SUMMES . Uz ugrađene operatore, kriteriji u programu Excel također se koriste za uvjetno oblikovanje. Razmotrimo njihovu primjenu na radu s različitim alatima danog procesora stolova u više detalja.

COUNTIF

Glavna zadaća COUNTIF operatora, koja se odnosi na statističku skupinu, je brojanje stanica koje zauzimaju različite vrijednosti koje zadovoljavaju određeno unaprijed određeno stanje. Sintaksa je sljedeća:

=СЧЁТЕСЛИ(диапазон;критерий)

Kao što možete vidjeti, ovaj operator ima dva argumenta. "Raspon" je adresa niz elemenata na listi u kojima treba računati.

"Kriterij" je argument koji specificira uvjet da mora sadržavati stanice određenog područja koje treba uključiti u brojanje. Kao parametar možete upotrijebiti numerički izraz, tekst ili referencu na ćeliju u kojoj se nalazi kriterij. U tom slučaju možete upotrijebiti sljedeće simbole da biste označili kriterij: "<" ( "manje" ), ">" ( "više" ), "=" ( "jednak" ), "<>" ( "nejednak" ). Na primjer, ako navedete izraz "<50" , u obzir će se uzeti u obzir samo elementi navedeni u "rasponu" , u kojem su brojčane vrijednosti manje od 50. Korištenje ovih znakova za određivanje parametara bit će relevantno za sve druge opcije koje će se raspravljati u ovoj lekciji u nastavku.

A sada pogledajmo konkretan primjer kako ovaj operater radi u praksi.

Dakle, postoji tablica koja prikazuje prihod pet trgovina tjedno. Moramo znati broj dana za ovo razdoblje, u kojem je u Trgovini 2 prihod od prodaje premašio 15.000 rubalja.

1. Odaberite element listova u kojem će operator ispisati rezultat izračuna. Nakon toga kliknite ikonu "Umetni funkciju" .
2. Pokreće se Čarobnjak za funkcije . Prelazimo u blok "Statističke" . Tamo pronađemo i razlikujemo naziv "ZEMLJE" . Zatim kliknite gumb "OK" .
3. Aktiviran je prozor argumenata navedenog operatora. U polju "Raspon" odredite područje ćelije, među kojima će se izvršiti izračun. U našem slučaju trebate odabrati sadržaj retka "Trgovina 2" , u kojoj se nalazi dnevni prihod. Postavite pokazivač u određeno polje i držite lijevu tipku miša odaberite odgovarajući polje u tablici. Adresa odabranog polja bit će prikazana u prozoru.

   U sljedećem polju "Kriterij" samo trebate odrediti parametar izravnog odabira. U našem slučaju moramo računati samo one elemente tablice u kojima vrijednost prelazi 15000. Zbog toga mi koristimo tipkovnicu za ulazak u navedeno polje izraz "> 15000" .

   Nakon svih gore navedenih manipulacija, kliknite na gumb "OK" .

4. Program izračunava i prikazuje rezultat u elementu listova koji je istaknut prije aktiviranja čarobnjaka za funkcije . Kao što vidite, u ovom je slučaju rezultat jednak broju 5. To znači da u odabranom nizu u pet ćelija ima vrijednosti veće od 15000. To je, možemo zaključiti da je u Trgovini 2 u pet dana od analiziranih sedam, prihod je premašio 15.000 rubalja.

Pouka: Čarobnjak za funkcije u programu Excel

SCHOTESLIMN

Sljedeća funkcija koja funkcionira s kriterijima je COUNTRY . Također se odnosi na statističku skupinu operatora. Zadatak COUNTERMAN računa stanice u određenom nizu koji zadovoljavaju određeni skup uvjeta. Činjenica je da ne možete odrediti jedan, već nekoliko parametara, a razlikujete od operatora od prethodne. Sintaksa je:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Raspon stanja" identičan je prvom argumentu prethodne izjave. To jest, upućuje na područje u kojem će se brojati stanice koje zadovoljavaju navedene uvjete. Ovaj operator vam omogućuje da odredite nekoliko takvih područja odjednom.

"Stanje" je kriterij koji određuje koji će elementi iz odgovarajućeg polja podataka ući u izračun i koji neće. Svako dano područje podataka mora se navesti odvojeno, čak i ako se podudara. Potrebno je da sva polja koja se koriste kao područja uvjeta imaju isti broj redaka i stupaca.

Na primjer, za određivanje nekoliko parametara istog područja podataka, na primjer, za brojanje ćelija u kojima su vrijednosti veće od određenog broja, ali manje od drugog broja, potrebno je nekoliko puta odrediti isti polje kao i argument "Razina uvjeta" , Međutim, različiti kriteriji trebali bi se koristiti kao odgovarajući argumenti "Uvjet" .

Na primjeru iste tablice s tjednim prihodima trgovina ćemo pogledati, kako to funkcionira. Trebamo znati broj dana u tjednu, kada su prihodi u svim navedenim prodajnim mjestima dosegnuli utvrđenu stopu za njih. Stope prihoda su kako slijedi:

• Kupujte 1 - 14000 rubalja;
• Trgovite 2 - 15,000 rubalja;
• Trgovina 3 - 24000 rubalja;
• Trgovite 4 - 11.000 rubalja;
• Trgovite 5 - 32000 rubalja.

1. Da biste izvršili gore navedeni zadatak, označite pokazivač elementa radnog lista, gdje će se prikazati rezultat obrade podataka o zemlji . Kliknemo na ikonu "Umetni funkciju" .
2. Prebacivanje na Čarobnjak za funkcije vratit ćemo se u blok "Statističke" . Na popisu je potrebno pronaći naziv obrasca zemlje i izvršiti odabir. Nakon izvođenja ove akcije morate kliknuti gumb "OK" .
3. Nakon izvršenja gore navedenog algoritma postupaka, otvorit će se prozor argumenata COUNTLINES .

   U polju "Raspon uvjeta1" unesite adresu retka u kojoj se nalaze podaci za tjedni tjedan u trgovini 1. Da biste to učinili, stavite pokazivač u polje i odaberite odgovarajući redak u tablici. Koordinate se prikazuju u prozoru.

   S obzirom da za dnevni prihod Store 1 dnevno iznosi 14.000 rubalja, u polju "Uvjet 1" unosimo izraz "> 14000" .

   U polju "Raspon uvjeta2 (3,4,5)" morate unijeti koordinate linije tjednim postupcima u skladu s trgovinom 2, Trgovina 3, Trgovina 4 i Spremnik 5. Radnja se izvodi prema istom algoritmu kao i za prvi argument ove grupe.

   U polju "Condition2" , "Condition3" , "Condition4" i "Condition5" unosimo vrijednosti "> 15000 ", "> 24000" , "> 11000" i "> 32000" . Kao što možda pogodite, te vrijednosti odgovaraju intervalu prihoda koji premašuje normu odgovarajuće trgovine.

   Nakon unosa svih potrebnih podataka (ukupno 10 polja), pritisnite gumb "OK" .

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

Sada nekako promijenimo problem. Trebali bismo izračunati broj dana u kojima je Shop 1 dobio prihod veći od 14.000 rubalja, ali manje od 17.000 rubalja.

1. Pokazivač stavimo u element gdje će se izlazni rezultati rezultata proračuna prikazati. Kliknemo na ikonu "Umetanje funkcije" iznad radnog područja listova.
2. Budući da smo nedavno primijenili formulare VIJEĆA , sada nije nužno ići u skupinu "statističkih" čarobnjaka funkcija . Naziv ovog operatora može se naći u kategoriji "10 nedavno korišten" . Odaberite ga i kliknite gumb "U redu" .
3. Otvara prozor argumenata operatora СОТЕЛЕММН koji nam je već poznat. Stavili smo pokazivač u polje "Raspon uvjeta1" i, nakon što ste stisnuli lijevu tipku miša, odaberite sve ćelije koje sadrže prihod do dana trgovine 1. Nalaze se u retku koji se zove "Trgovina 1" . Nakon toga, koordinate navedenog područja odražavat će se u prozoru.

   Zatim postavljamo pokazivač u polje "Stanje1" . Ovdje moramo odrediti donju granicu vrijednosti u stanicama koje će sudjelovati u izračunu. Navedite izraz "> 14000" .

   U polju "Raspon uvjeta2" unosimo istu adresu na isti način kao što je unesen u polje "Raspon uvjeta1" , odnosno ponovno unosimo koordinate ćelije s vrijednostima prihoda na prvoj prodajnoj poziciji .

   U polju "Uvjet2" označavamo gornju granicu odabira: "<17000" .

   Nakon svih gore navedenih radnji, kliknite na gumb "OK" .

4. Program izlaže rezultat izračuna. Kao što možete vidjeti, konačna vrijednost je jednaka 5. To znači da je u 5 dana analiziranih sedam, prihod u prvoj trgovini bio u rasponu od 14.000 do 17.000 rubalja.

SUMIF

Drugi operator koji koristi kriterije je LJETO . Za razliku od prethodnih funkcija, odnosi se na matematički blok operatora. Njegova je zadaća sažeti podatke u stanicama koje odgovaraju određenom stanju. Sintaksa je:

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

Argument "Raspon" označava područje ćelija koje će biti provjerene radi usklađenosti sa stanjem. Zapravo, postavlja se na isti princip, kao i isti argument funkcije VIJEĆA .

"Kriterij" je obvezni argument koji određuje parametar odabira ćelije od navedenog područja podataka koji se zbraja. Načela indikacije su ista kao i one analognih argumenata prethodnih operatora, koje smo gore razmotrili.

"Raspon zbrajanja" je izborni argument. Označava specifično područje polja u kojem će se zbroj izvršiti. Ako ga izostavite i ne odredite, prema zadanim postavkama smatra se da je jednaka vrijednosti potrebnog argumenta "Raspon" .

Sada, kao i uvijek, razmotrite primjenu ovog operatora u praksi. Na temelju iste tablice suočavamo se s zadatkom izračuna iznosa prihoda u Trgovini 1 za razdoblje od 11.03.2017.

1. Odaberite ćeliju u kojoj će se rezultat prikazati. Kliknemo na ikonu "Umetni funkciju" .
2. Idite na čarobnjak za funkcije u bloku "Matematički" i pronađite naziv "SUMMER" . Kliknite gumb "OK" .
3. Pokreće se prozor argumenata SUMIFER funkcije. Postoje tri polja koja odgovaraju argumente određenog operatora.

   U polju "Raspon" unesite područje tablice u kojoj će se postaviti vrijednosti za provjeru sukladnosti s uvjetima. U našem slučaju to će biti niz datuma. Stavili smo pokazivač u ovo polje i odaberite sve stanice koje sadrže datume.

   Budući da trebamo dodati samo prihode od 11. ožujka, unosimo vrijednost "> 10.03.2017" u polje "Kriterij" .

   U polju "Raspon zbrajanja" morate odrediti područje, čije će vrijednosti odgovarati određenim kriterijima biti zbrojene. U našem slučaju to su vrijednosti prihoda linije "Shop1" . Odaberite odgovarajući niz elemenata listova.

   Nakon uvođenja svih navedenih podataka, kliknite na "OK" gumb.

4. Nakon toga, rezultat obrade podataka pomoću funkcije SUMMER bit će prikazan u prethodno navedenom elementu radnog lista. U našem slučaju, to je 47921.53. To znači da od 11.03.2017., A do kraja analiziranog razdoblja, ukupni prihod za Trgovinu 1 bio je 47.921,53 rubalja.

SUMIFS

Završimo proučavanje operatera koji koriste kriterije, zaustavljajući se na funkciju sažetka . Zadatak ove matematičke funkcije je zbrajanje vrijednosti naznačenih područja tablice, odabranog prema nekoliko parametara. Sintaksa navedenog operatera je:

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Raspon zbrajanja" je argument koji je adresa tog polja, ćelije u kojima će odgovarati određeni kriterij.

"Raspon stanja" je argument koji predstavlja skup podataka koji se provjerava radi usklađenosti sa stanjem;

"Stanje" je argument koji je kriterij odabira za dodavanje.

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

Pogledajmo kako je ovaj operator primjenjiv za rješavanje problema u kontekstu tablice prihoda od prodaje u prodajnim mjestima. Morat ćemo izračunati prihode koje je Shop 1 donio za razdoblje od 09. do 13. ožujka 2017. godine. Istodobno, kada se zbroje dohodak, trebalo bi razmotriti samo one dane s prihodom većim od 14.000 rubalja.

1. Ponovno odaberite ćeliju za prikaz ukupnog broja i kliknite na ikonu "Umetni funkciju" .
2. U čarobnjaku za funkciju prvo se prebacujemo na blok "Matematički" , a tu odaberemo stavku pod nazivom "SUMMESLIMN" . Kliknite gumb "U redu" .
3. Pokrenut je prozor argumenata operatera, čije je ime gore spomenuto.

   Postavite pokazivač u polje "Raspon zbroja" . Za razliku od sljedećih argumenata, ovo je takve vrste i upućuje na niz vrijednosti u kojima će se zbroj podataka koji odgovaraju definiranim kriterijima. Zatim odaberite područje linije "Shop1" , koji je postavio vrijednosti prihoda za odgovarajuću utičnicu.

   Nakon što se adresa prikazuje u prozoru, idite na polje "Raspon stanja1" . Ovdje ćemo morati prikazati koordinate reda s datumima. Mi stisnuti lijevu tipku miša i odabrati sve datume u tablici.

   Stavili smo pokazivač u polje "Stanje1" . Prvi uvjet je da ćemo sažeti podatke prije 9. ožujka. Stoga unosimo vrijednost "> 08.03.2017" .

   Prebacimo se na argument "Raspon uvjeta2" . Ovdje morate unijeti iste koordinate zabilježene u polju "Raspon uvjeta1" . To činimo na isti način, to jest, isticanjem linije s datumima.

   Postavite pokazivač u polje "Stanje2" . Drugi uvjet je da će dani za koji će biti dodani prihodi biti najkasnije do 13. ožujka. Stoga pišemo sljedeći izraz: «<14.03.2017» .

   Idite na polje "Raspon stanja2" . U tom slučaju moramo odabrati isti polje čija je adresa unesena kao niz sumiranja.

   Nakon što je adresa navedenog polja prikazana u prozoru, idite na polje "Status3" . Uzimajući u obzir da će samo zbrojiti vrijednosti koje prelaze 14.000 rubalja, bilježimo sljedeći znak: "> 14000" .

   Nakon posljednje radnje kliknite gumb "U redu" .

4. Program ispisuje rezultat na listu. To je jednako 62491,38. To znači da je za razdoblje od 09. do 13. ožujka 2017. godine iznos prihoda koji je dodan u dane kada prelazi 14.000 rubalja bio 62491,38 rubalja.

Uvjetno oblikovanje

Posljednje, koje smo opisali, alati, pri radu s kriterijima koji se koriste, uvjetni su oblikovanje. Obavlja određeno oblikovanje ćelija koje zadovoljavaju navedene uvjete. Pogledajmo primjer rada s uvjetovanim oblikovanjem.

Odaberite ćelije u tablici plave boje, gdje vrijednosti za taj dan prelaze 14.000 rubalja.

1. U tablici smo odabrali čitav niz elemenata, što ukazuje na prihod prodajnih mjesta po danima.
2. Prelazimo na karticu "Dom" . Kliknemo na ikonu "Uvjetno oblikovanje" , koji se nalazi u bloku "Stilovi" na vrpci. Prikazuje se popis akcija. Kliknemo na njega na poziciji "Napravi pravilo ..." .
3. Aktiviran je prozor generiranja pravila za oblikovanje. U području odabira vrste pravila odaberite naziv "Format samo ćelije koje sadrže" . U prvom polju uvjetnog bloka, s popisa mogućih opcija, odaberite "Stanična vrijednost" . U sljedećem polju odaberite položaj "Više" . U potonjem se navodi sama vrijednost, od kojih više moramo oblikovati elemente tablice. Imamo 14000. Da bismo odabrali vrstu oblikovanja, kliknite na gumb "Format ..." .
4. Aktiviran je prozor formata. Prijeđite na karticu "Ispunite" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

Более подробно о возможностях условного форматирования рассказывается в отдельной статье.

Pouka: Условное форматирование в программе Эксель

Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.

• ← Izrada grafikona ovisnosti u programu Microsoft Excel
• Izrada mrežnog rasporeda u programu Microsoft Excel →