Uporaba kriterijev v programu Microsoft Excel

Microsoft Excel ni le urejevalnik preglednic, ampak tudi močna aplikacija za različne izračune. Nenazadnje je ta funkcija posledica vgrajenih funkcij. S pomočjo nekaterih funkcij (operaterjev) je mogoče določiti tudi pogoje za izračun, ki se imenujejo merila. Učimo se podrobneje, kako jih uporabljati pri delu v Excelu.

Uporaba meril

Kriteriji so pogoji, pod katerimi program izvaja določene ukrepe. Uporabljajo se v številnih vgrajenih funkcijah. V njihovem imenu je izraz "IF" najpogosteje prisoten. Za to skupino operaterjev je najprej treba vključiti DRŽAVE , RAČUNE , SUM , SUMMES . Poleg vgrajenih operaterjev se merila v Excelu uporabljajo tudi za pogojno oblikovanje. Oglejmo si njihovo uporabo na delovnem mestu z različnimi orodji danega procesorja.

RAČUNI

Glavna naloga operaterja COUNTIF , ki se nanaša na statistično skupino, je štetje celic, ki jih zasedajo različne vrednosti, ki izpolnjujejo določeno vnaprej določeno stanje. Sintaksa je naslednja:

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

Kot lahko vidite, ima ta operater dva argumenta. "Obseg" je naslov množice elementov na listu, v katerem naj se šteje.

"Kriterij" je argument, ki določa pogoj, da mora vsebovati celice določenega območja, ki jih je treba vključiti v štetje. Kot parameter lahko uporabite številski izraz, besedilo ali sklic na celico, v kateri je merilo vsebovano. V tem primeru lahko uporabite naslednje znake, ki označujejo merilo: "<" ( "manj" ), ">" ( "več" ), "=" ( "enak" ), "<>" ( "ni enak" ). Če na primer navedete izraz "<50" , bodo v izračunu upoštevani samo elementi, ki jih določi argument "Range" , pri katerih so številčne vrednosti manjše od 50. Uporaba teh znakov za določitev parametrov bo ustrezna za vse druge možnosti, o katerih se bo razpravljalo v tej lekciji spodaj.

In zdaj si oglejmo konkreten primer, kako ta operater deluje v praksi.

Torej, obstaja tabela, ki prikazuje prihodke petih trgovin na teden. V tem obdobju moramo vedeti, koliko dni je bilo v trgovini 2 prihodki od prodaje večji od 15.000 rubljev.

1. Izberite element lista, na katerem bo operator natisnil rezultat izračuna. Nato kliknite ikono »Vstavi funkcijo« .
2. Začne se čarovnik za funkcije . Premaknemo se na "Statistični" blok. Tam najdemo in razlikujemo ime »DRŽAVE« . Nato kliknite gumb "OK" .
3. Vključeno je okno argumentov zgoraj navedenega operaterja. V polju "Range" določite območje celice, med katerim bo izračun izveden. V našem primeru bi morali izbrati vsebino vrstice "Shop 2" , v kateri se nahaja prihodek na dan. Postavite kazalko v podano polje in držite levi gumb miške izberite ustrezno matriko v tabeli. Naslov izbranega polja bo prikazan v oknu.

   V naslednjem polju »Kriterij« morate samo določiti neposredni izbirni parameter. V našem primeru moramo računati samo tiste elemente tabele, v katerih vrednost presega 15000. Zato uporabljamo tipkovnico za vožnjo v določeno polje izraz "> 15000" .

   Ko končate vse zgoraj navedene manipulacije, kliknite gumb "OK" .

4. Program izračuna in prikaže rezultat v elementu stanja, ki je bil poudarjen pred aktiviranjem čarovnika za funkcije . Kot vidite, v tem primeru je rezultat enako številu 5. To pomeni, da v izbranem nizu v petih celicah obstajajo vrednosti, ki presegajo 15000. To pomeni, da lahko sklepamo, da je v trgovini 2 v petih dneh od sedmih analiziranih prihodkov presegel 15.000 rubljev.

Lekcija: Čarovnik za funkcije v programu Excel

COUNTRYSIDE

Naslednja funkcija, ki deluje z merili, je COUNTRY . Opozarja tudi na statistično skupino operaterjev. Naloga COUNTERMANa šteje celice v določeni matriki, ki izpolnjujejo določen niz pogojev. Dejstvo je, da lahko določite samo en parameter, vendar ga ločite od prejšnjega. Sintaksa je:

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

"Območje pogojev" je enako prvemu argumentu prejšnjega stavka. To pomeni, da gre za območje, na katerem se upoštevajo celice, ki izpolnjujejo določene pogoje. Ta operater vam omogoča, da določite več takih območij hkrati.

"Pogoj" je merilo, ki določa, kateri elementi iz ustreznega nabora podatkov bodo vključeni v izračun in ki jih ne bodo. Vsako navedeno območje podatkov je treba navesti ločeno, tudi če se ujema. Zahtevati je, da imajo vse matrike, uporabljene kot pogojna območja, enako število vrstic in stolpcev.

Če želite določiti več parametrov istega podatkovnega območja, na primer za štetje števila celic, v katerih so vrednosti večje od določenega števila, vendar manjše od druge številke, morate večkrat navesti isti niz več kot "Range Range" . Za primerne argumente »Condition« pa je treba uporabiti drugačna merila.

Na primer vse iste mize z tedenskimi iztržki trgovin bomo videli, kako deluje. Moramo vedeti, koliko dni v tednu, ko so prihodki v vseh navedenih prodajnih mestih dosegli določeno stopnjo za njih. Stopnje prihodkov so naslednje:

• Trgovina 1 - 14000 rubljev;
• Trgovina 2 - 15.000 rubljev;
• Trgovina 3 - 24000 rubljev;
• Trgovina 4 - 11.000 rubljev;
• Trgovina 5 - 32000 rubljev.

1. Če želite opraviti zgornjo opravilo, označite kazalec z elementom delovnega lista, na katerega bo prikazan rezultat obdelave podatkov COUNTRY . Kliknemo na ikono "Vstavi funkcijo" .
2. Preklopimo na Čarovnik za funkcije , se vrnemo v "Statistični" blok. Na seznamu je potrebno najti ime DRŽAVE OBRAZEC in izbrati. Ko izvedete to dejanje, morate klikniti gumb »V redu« .
3. Po izvedbi zgornjega algoritma ukrepov se odpre okno argumentov COUNTLINES .

   V polju "Stanje obsega1" vnesite naslov vrstice, v kateri so podatki za prihodek Shop 1 na teden. Če želite to narediti, postavite kurzor v polje in izberite ustrezno vrstico v tabeli. Koordinate so prikazane v oknu.

   Glede na to, da je dnevni tečaj Store 1 14.000 rubljev, potem v polju "Stanje 1" vnesemo izraz "> 14000" .

   V polju »Pogojni razpon2 (3,4,5)« vnesite koordinate vrstic z tedenskimi prihodki v skladu s Store 2, Store 3, Store 4 in Store 5. Dejanje se izvaja v skladu z istim algoritmom kot prvi argument te skupine.

   V poljih Condition2 , Condition3 , Condition4 in Condition5 vnesemo vrednosti "> 15000 ", "> 24000" , "> 11000" in "> 32000" . Kot verjetno mislite, te vrednosti ustrezajo dohodkovnemu intervalu, ki presega normo za ustrezno skladišče.

   Po vnosu vseh potrebnih podatkov (10 polj skupaj) kliknite gumb "OK" .

4. Program izračuna in prikaže rezultat na zaslonu. Kot lahko vidite, je enako številu 3. To pomeni, da so prihodki v vseh prodajnih mestih v treh dneh po analiziranem tednu presegli določeno stopnjo za njih.

Zdaj pa nekoliko spremenimo težavo. Izračunati bi morali število dni, v katerih je Shop 1 prejemal prihodke, ki presegajo 14.000 rubljev, vendar manj kot 17.000 rubljev.

1. Kursor postavimo v element, kjer bo izhod iz rezultatov izračuna. Kliknemo na ikono "Vstavi funkcijo" nad delovno površino lista.
2. Ker smo pred kratkim uporabili formulo Sveta, zdaj ni potrebno iti v skupino čarovnikov za statistično funkcijo . Ime tega operaterja je mogoče najti v kategoriji "10 nedavno uporabljenih" . Izberite in kliknite gumb »V redu« .
3. Odpre okno argumentov operaterja SČËTESLIMN, ki je že znan. Postavili smo kazalko v polje »Pogojni razpon1« in po tem, ko ste levo miškino tipko pritiskali , izberite vse celice, ki vsebujejo prihodek, v dnevih Store 1. Nahajajo se v vrstici, ki se imenuje »Shop 1« . Po tem se bodo v oknu prikazale koordinate določenega območja.

   Nato postavimo kurzor v polje »Stanje1« . Tukaj moramo določiti spodnjo mejo vrednosti v celicah, ki bodo sodelovale pri izračunu. Navedite izraz "> 14000" .

   V polju »Pogojni razpon2« vnesemo isti naslov na enak način, kot smo ga vnesli v polje »Stanje obsega1« , to pomeni, da znova vnesemo koordinate celice z vrednostmi prihodkov na prvi prodajni točki.

   V polju "Condition2" označujemo zgornjo mejo izbora: "<17000" .

   Ko so končana vsa navedena dejanja, kliknite gumb »V redu« .

4. Program izračuna rezultat izračuna. Kot lahko vidite, je končna vrednost enaka 5. To pomeni, da so bili v petih dneh po analizi sedmih prihodki v prvi trgovini v razponu od 14.000 do 17.000 rubljev.

POLETJE

Drugi operator, ki uporablja merila, je LETO . Za razliko od prejšnjih funkcij se nanaša na matematični blok operaterjev. Njena naloga je povzeti podatke v celicah, ki ustrezajo določenemu pogoju. Sintaksa je:

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

Argument "Range" označuje območje celic, ki se preveri za skladnost s pogojem. Pravzaprav je postavljeno na isto načelo, kot isti argument funkcije Sveta .

"Kriterij" je obvezen argument, ki določa parameter izbire celic iz določenega podatkovnega območja, ki se sumi. Načela indikacije so enaka kot pri analognih argumentih prejšnjih operaterjev, ki smo jih obravnavali zgoraj.

"Obseg zbiranja" je izbirni argument. Označuje specifično področje matrike, v katerem se bo opravila vsota. Če ga izpustite in ga ne določite, se privzeto šteje, da je enaka vrednosti zahtevanega argumenta »Range« .

Zdaj, kot vedno, upoštevajte uporabo tega operaterja v praksi. Na podlagi iste mize smo se soočili z nalogo izračunavanja višine prihodka v trgovini 1 za obdobje, ki se začne s 11.03.2017.

1. Izberite celico, v kateri bo rezultat prikazan. Kliknemo na ikono "Vstavi funkcijo" .
2. Odprite čarovnika za funkcije v matematičnem bloku in poiščite ime »LETO« . Kliknite gumb "V redu" .
3. Začne se okno argumentov funkcije SUMIFER . Obstajajo tri polja, ki ustrezajo argumentom določenega operaterja.

   V polju "Range" vnesite območje tabele, v kateri bodo nameščene vrednosti, ki jih je treba preveriti za skladnost s pogoji. V našem primeru je to niz nizov. V to polje postavimo kazalko in izberemo vse celice, ki vsebujejo datume.

   Ker moramo dodati samo prihodke od 11. marca, v polje »Kriterij« vnesemo vrednost »> 10.03.2017« .

   V polju »Območje seštevanja« morate določiti območje, katerega vrednosti, ki ustrezajo določenim merilom, se bodo povzeli. V našem primeru so to vrednosti prihodka vrstice "Shop1" . Izberite ustrezno vrsto elementov lista.

   Po uvedbi vseh navedenih podatkov, kliknite gumb "OK" .

4. Po tem bo rezultat obdelave podatkov s funkcijo SUMMER prikazan v predhodno določenem elementu delovnega lista. V našem primeru je 47921.53. To pomeni, da je skupni prihodek za Store 1 z dne 11.03.2017 in do konca analiziranega obdobja znašal 47.921,53 rubljev.

POVZETEK

Končali smo študije operaterjev, ki uporabljajo merila, ki se ustavijo pri funkciji POVZETEK . Naloga te matematične funkcije je vsota vrednosti navedenih območij tabele, ki jih izbere več parametrov. Sintaksa določenega operaterja je:

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

"Območje seštevanja" je argument, ki je naslov te matrike, v katere bodo dodane celice, ki ustrezajo določenemu kriteriju.

"Območje pogojev" je argument, ki predstavlja množico podatkov, ki so preverjeni za skladnost s pogojem;

"Pogoj" je argument, ki je izbirni kriterij za dodajanje.

Ta funkcija vključuje operacije z več nizi podobnih operaterjev.

Poglejmo, kako se ta operater uporablja za reševanje težav v okviru tabele prihodkov od prodaje v prodajnih mestih. Dohodek, ki ga je Shop 1 prinesel v obdobju od 9. do 13. marca 2017, bomo morali izračunati. Ob istem času, ko seštevek dohodka, je treba upoštevati samo tiste dni z dohodkom, ki presega 14.000 rubljev.

1. Ponovno izberite celico, da prikažete skupno vrednost in kliknete ikono »Vstavi funkcijo« .
2. V čarovniku za funkcije najprej premaknemo v matematični blok in tam izberemo postavko, ki se imenuje »SUMMESLIMN« . Kliknite gumb »V redu« .
3. Začelo se je okno argumentov operaterja, katerega ime je bilo omenjeno zgoraj.

   Nastavite kazalko v polju »Obseg vsote« . V nasprotju z naslednjimi argumenti, ta tovrstne vrste in opozarja na množico vrednosti, kjer bo opravljena vsota podatkov, ki ustrezajo določenim merilom. Nato izberite območje vrstice "Shop1" , ki je objavil vrednosti prihodkov za ustrezno vtičnico.

   Ko se v oknu prikaže naslov, pojdite na polje "Pogojno območje1" . Tukaj bomo morali prikazati koordinate vrstice z datumi. Z levim gumbom miške pritiskamo in izberemo vse datume v tabeli.

   Postavili smo kazalko v polje »Stanje1« . Prvi pogoj je, da podatke povzamemo ne prej kot 9. marca. Zato vnesemo vrednost "> 08.03.2017" .

   Premaknemo se na argument "Območje pogojev2" . Tukaj morate vnesti iste koordinate, ki so bile zabeležene v polju "Stanje obsega1" . To naredimo na enak način, to je s poudarjanjem vrstice z datumi.

   Kazalec postavite v polje »Stanje2« . Drugi pogoj je, da morajo biti dnevi, za katere se doda prihodek, najkasneje do 13. marca. Zato zapisujemo naslednji izraz: «<14.03.2017» .

   Pojdite na polje »Stanje obsega2« . V tem primeru moramo izbrati isto polje, katerega naslov je bil vnesen kot množica vsote.

   Ko je bil v podoknu prikazan naslov določenega polja, pojdite na polje »Stanje3« . Upoštevajoč, da bodo v skupno vsoto vključene le vrednosti, ki presegajo 14.000 rubljev, zabeležimo naslednji značaj: "> 14000" .

   Po zadnjem dejanju kliknite gumb »V redu« .

4. Rezultat je rezultat na listu. To je enako 62491.38. To pomeni, da je bil za obdobje od 9. do 13. marca 2017 znesek prihodkov, ko je bil dodan v dneh, ko je presegel 14.000 rubljev, 62491,38 rubljev.

Pogojno oblikovanje

Pogojno oblikovanje je zadnje, opisano z nami, orodje pri delu s katerimi merili. Izvaja določeno oblikovanje celic, ki ustrezajo določenim pogojem. Oglejmo si primer dela s pogojnim oblikovanjem.

Izberite celice v tabeli v modri barvi, kjer vrednosti za dan presegajo 14.000 rubljev.

1. Izberemo celoten niz elementov v tabeli, ki prikazuje prihodke prodajnih mest po dnevih.
2. Premaknemo se na zavihek "Domov" . Kliknemo na ikono "Pogojno oblikovanje" , ki se nahaja v bloku "Slogi" na traku. Prikaže se seznam dejanj. Kliknemo jo na položaj »Ustvari pravilo ...« .
3. Vključeno je okno za generiranje pravil oblikovanja. V področju izbiranja vrste pravila izberite ime »Oblikujte samo celice, ki vsebujejo« . V prvem polju pogojnega polja s seznama možnih možnosti izberite »Vrednost celice« . V naslednjem polju izberite položaj »Več« . V slednjem imenujemo samo vrednost, več pa moramo oblikovati elemente tabele. Imamo 14000. Če želite izbrati vrsto oblikovanja, kliknite gumb "Format ..." .
4. Okno oblikovanja je aktivirano. Premik na kartico »Izpolnite «. Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

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

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

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

• ← Gradimo graf odvisnosti v programu Microsoft Excel
• Ustvarjanje omrežnega programa v Microsoft Excelu →