Používanie kritérií v programe Microsoft Excel

Program Microsoft Excel nie je iba editor tabuliek, ale je tiež výkonnou aplikáciou pre rôzne výpočty. V neposlednom rade je táto funkcia spôsobená vstavanými funkciami. Pomocou určitých funkcií (operátorov) je možné špecifikovať aj podmienky výpočtu, ktoré sa nazývajú kritériá. Dozvieme sa podrobnejšie ako ich používať pri práci v programe Excel.

Uplatňovanie kritérií

Kritériami sú podmienky, za ktorých program vykonáva určité činnosti. Používajú sa v mnohých vstavaných funkciách. Vo svojom názve sa najčastejšie vyskytuje výraz "IF" . Pre túto skupinu prevádzkovateľov je predovšetkým potrebné zahrnúť KRAJINY , ÚČTY , SUM , SUMMY . Okrem vstavaných operátorov sa kritériá v programe Excel používajú aj pre podmienené formátovanie. Uvažujme ich aplikáciu v práci s rôznymi nástrojmi daného stolového procesora podrobnejšie.

COUNTIF

Hlavnou úlohou operátora COUNTIF , ktorá sa týka štatistickej skupiny, je počítanie buniek obsadených rôznymi hodnotami, ktoré spĺňajú určitú vopred stanovenú podmienku. Jeho syntax je nasledujúci:

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

Ako vidíte, tento operátor má dva argumenty. "Rozsah" je adresa množiny prvkov na hárku, do ktorých sa má počítať.

"Kritérium" je argument, ktorý špecifikuje podmienku, že musí obsahovať bunky určenej oblasti, ktoré sa majú zahrnúť do počítania. Ako parameter môžete použiť číselný výraz, text alebo odkaz na bunku, v ktorej je toto kritérium obsiahnuté. V tomto prípade môžete použiť nasledujúce značky na označenie kritéria: "<" ( "menej" ), ">" ( "viac" ), "=" ( "rovnaké" ), "<>" ( "nie je rovnaké" ). Napríklad, ak zadáte výraz "<50" , pri výpočte sa vezmú do úvahy iba prvky špecifikované argumentom "Rozsah" , v ktorom sú číselné hodnoty menšie ako 50. Použitie týchto značiek na špecifikáciu parametrov bude relevantné pre všetky ďalšie možnosti, ktoré sa budú diskutovať v tejto lekcii nižšie.

A teraz sa pozrime na konkrétny príklad, ako tento prevádzkovateľ pracuje v praxi.

Takže, tam je tabuľka, ktorá ukazuje príjmy z piatich obchodov týždenne. Potrebujeme poznať počet dní za toto obdobie, v ktorých v obchode 2 dosiahli tržby viac ako 15 000 rubľov.

1. Vyberte prvok hárku, v ktorom operátor vytlačí výsledok výpočtu. Potom kliknite na ikonu "Vložiť funkciu" .
2. Spustí sa Sprievodca funkciami. Presunieme sa na blok "Štatistické" . Tam nájdeme a rozlišujeme názov "KRAJINY" . Potom kliknite na tlačidlo "OK" .
3. Zobrazí sa okno argumentov vyššie uvedeného operátora. Do poľa Rozsah zadajte bunkovú oblasť, medzi ktorou sa vykoná výpočet. V našom prípade by ste mali vybrať obsah riadka "Shop 2" , v ktorom sa nachádza výnos za deň. Vložte kurzor do zadaného poľa a podržte ľavé tlačidlo myši a zvoľte príslušné pole v tabuľke. V okne sa zobrazí adresa vybraného poľa.

   V ďalšom poli "Kritérium" stačí zadať parameter priameho výberu. V našom prípade musíme počítať iba tie tabuľkové prvky, ktorých hodnota presahuje 15000. Preto používame klávesnicu na zadanie výrazu "> 15000" do zadaného poľa.

   Po vykonaní všetkých vyššie uvedených manipulácií kliknite na tlačidlo "OK" .

4. Program vypočíta a zobrazí výsledok v prvku listu, ktorý bol zvýraznený pred aktiváciou sprievodcu funkciou . Ako vidíte, v tomto prípade sa výsledok rovná číslu 5. To znamená, že vo vybranom poli v piatich bunkách sú hodnoty presahujúce 15000. To znamená, že môžeme konštatovať, že v obchode 2 za päť dní analyzovaných siedmich výnosov presiahli 15 000 rubľov.

Lekcia: Sprievodca funkciami programu Excel

SCHOTESLIMN

Ďalšou funkciou, ktorá pracuje s kritériami, je KRAJINA . Tiež odkazuje na štatistickú skupinu prevádzkovateľov. Úloha COUNTERMAN počíta bunky v špecifikovanom poli, ktoré spĺňajú určitý súbor podmienok. Je to skutočnosť, že môžete určiť nie jeden, ale niekoľko parametrov a rozlišuje tohto operátora od predchádzajúceho. Syntax je:

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

"Rozsah podmienok" je totožný s prvým argumentom predchádzajúceho príkazu. To znamená, že ide o odkaz na oblasť, v ktorej sa spočítajú bunky, ktoré spĺňajú špecifikované podmienky. Tento operátor vám umožňuje určiť niekoľko takýchto oblastí naraz.

"Podmienka" je kritérium, ktoré určuje, ktoré elementy zo zodpovedajúceho poľa dát sa dostanú do výpočtu a ktoré z nich nebudú. Každá daná dátová oblasť musí byť špecifikovaná samostatne, aj keď sa zhoduje. Je potrebné, aby všetky polia použité ako podmienkové oblasti mali rovnaký počet riadkov a stĺpcov.

Ak chcete napríklad zadať niekoľko parametrov tej istej dátovej oblasti, napríklad počítať počet buniek, v ktorých sú hodnoty väčšie ako určité číslo, ale menej ako iné číslo, mali by ste zadať rovnaké pole niekoľkokrát ako argument "Rozsah podmienok" , Ako zodpovedajúce argumenty "Podmienka" by sa však mali použiť rôzne kritériá.

Na príklade tej istej tabuľky s týždenným výnosom z obchodov budeme vyzerať, ako to funguje. Potrebujeme poznať počet dní v týždni, kedy príjmy vo všetkých uvedených predajniach dosiahli pre nich stanovenú sadzbu. Výška príjmov je takáto:

• Obchod 1 - 14000 rubľov;
• Obchod 2 - 15 000 rubľov;
• Obchod 3 - 24000 rubľov;
• Obchod 4 - 11 000 rubľov;
• Obchod 5 - 32000 rubľov.

1. Ak chcete vykonať uvedenú úlohu, zvýraznite kurzor s prvkom pracovného hárka, ku ktorému bude výsledok spracovania údajov krajiny COUNTRY . Klikneme na ikonu "Vložiť funkciu" .
2. Po prepnutí na Sprievodcu funkciami sa vrátime do bloku "Štatistické" . V zozname je potrebné nájsť názov formulára COUNTRY FORMÁT a urobiť jeho výber. Po vykonaní tejto akcie musíte kliknúť na tlačidlo "OK" .
3. Po vykonaní vyššie uvedeného algoritmu akcií sa otvorí okno argumentov COUNTLINES .

   V poli "Rozsah podmienok1" zadajte adresu riadku, v ktorom sa nachádzajú údaje o príjme týždenne v obchode 1. Ak to chcete urobiť, umiestnite kurzor do poľa a vyberte príslušný riadok v tabuľke. Súradnice sa zobrazujú v okne.

   Vzhľadom na to, že denná miera výnosov Obchodu 1 je 14 000 rubľov, v poli "Podmienka 1" zadáme výraz "> 14000" .

   V poli "Rozsah podmienok2 (3,4,5)" musíte zadať súradnice riadkov s týždenným výnosom podľa Store 2, Store 3, Store 4 a Store 5. Akcia sa vykonáva podľa rovnakého algoritmu ako prvý argument tejto skupiny.

   V poliach "Podmienka2" , "Podmienka3" , "Podmienka4" a "Podmienka5" zadáme hodnoty "> 15000 ", "> 24000" , "> 11000" a "> 32000" . Ako by ste mohli odhadnúť, tieto hodnoty zodpovedajú intervalu príjmov, ktorý presahuje normu pre príslušný obchod.

   Po zadaní všetkých potrebných údajov (celkom 10 polí) kliknite na tlačidlo "OK" .

4. Program vypočíta a zobrazí výsledok na obrazovke. Ako vidíte, rovná sa číslu 3. To znamená, že za tri dni od analyzovaného týždňa príjmy vo všetkých predajniach prekročili pre nich stanovené sadzby.

Teraz trochu zmeniť problém. Mali by sme vypočítať počet dní, v ktorých obchod 1 získal príjmy viac ako 14 000 rubľov, ale menej ako 17 000 rubľov.

1. Kurzor sme umiestnili do prvku, v ktorom bude výstup výsledkov výpočtov vyvedený. Klikneme na ikonu "Vložiť funkciu" nad pracovnú oblasť listu.
2. Vzhľadom k tomu, že sme nedávno použili formulár RADY , teraz nie je potrebné ísť do skupiny "štatistických" funkčných sprievodcov . Názov tohto operátora sa nachádza v kategórii "10 nedávno použitých" . Vyberte ho a kliknite na tlačidlo "OK" .
3. Otvorí okno argumentov operátora СЧЁТЕСЛИМН, ktoré je už známe. Kurzor sme umiestnili do poľa "Rozsah podmienok1" a po stlačení ľavého tlačidla myši vyberte všetky bunky, ktoré obsahujú výnosy v dňoch Store 1. Nachádzajú sa v riadku, ktorý sa nazýva "Obchod 1" . Potom sa v okne odrazia súradnice určenej oblasti.

   Ďalej umiestňujeme kurzor do poľa "Podmienka1" . Tu je potrebné špecifikovať dolnú hranicu hodnôt v bunkách, ktoré sa zúčastnia výpočtu. Zadajte výraz "> 14000" .

   V poli "Rozsah podmienok2" zadávame rovnakú adresu rovnakým spôsobom, ako je zadané v poli "Rozsah podmienok1" , to znamená, že opäť zadáme súradnice buniek s hodnotami výnosov v prvom predajnom mieste.

   V poli "Podmienka2" uvádzame hornú hranicu výberu: "<17000" .

   Po vykonaní všetkých vyššie uvedených krokov kliknite na tlačidlo "OK" .

4. Program vygeneruje výsledok výpočtu. Ako vidíte, konečná hodnota sa rovná 5. To znamená, že za 5 dní analyzovaných sedem príjmov v prvom obchode bol v rozmedzí od 14 000 do 17 000 rubľov.

SUMIF

Ďalším operátorom, ktorý používa kritériá, je SUMMER . Na rozdiel od predchádzajúcich funkcií odkazuje na matematický blok operátorov. Jej úlohou je zhrnúť údaje v bunkách, ktoré zodpovedajú určitému stavu. Syntax je:

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

Argument "Rozsah" označuje oblasť buniek, ktoré sa skontrolujú, či sú splnené podmienky. V skutočnosti je založená na rovnakom princípe ako ten istý argument funkcie Rady .

"Kritérium" je povinný argument, ktorý špecifikuje parameter výberu bunky zo zadanej dátovej oblasti, ktorá sa má sumarizovať. Zásady označovania sú rovnaké ako zásady analogických argumentov predchádzajúcich operátorov, ktoré sme považovali za vyššie.

"Rozsah sumácie" je voliteľným argumentom. Označuje špecifickú oblasť poľa, v ktorej sa bude sumarizovať. Ak ho vynecháte a nešpecifikujete ho, predpokladá sa, že sa rovná hodnote požadovaného argumentu "Rozsah" .

Teraz, ako vždy, zvážte aplikáciu tohto operátora v praxi. Na základe tej istej tabuľky stojíme pred úlohou vypočítať výšku výnosov v obchode 1 za obdobie začínajúce od 11.03.2017.

1. Vyberte bunku, v ktorej sa bude výsledok zobrazovať. Klikneme na ikonu "Vložiť funkciu" .
2. Prejdite do sprievodcu funkciami v bloku "Matematický" a nájdite názov "LETO" . Kliknite na tlačidlo "OK" .
3. Zobrazí sa okno s argumentmi funkcie SUMIFER . Existujú tri polia zodpovedajúce argumentom konkrétneho operátora.

   Do poľa Rozsah zadajte oblasť tabuľky, v ktorej budú umiestnené hodnoty, ktoré sa majú skontrolovať, pokiaľ ide o súlad s podmienkami. V našom prípade to bude reťazec dátumov. Do tohto poľa umiestnime kurzor a vyberieme všetky bunky obsahujúce dátumy.

   Keďže potrebujeme pridať iba výnosy z 11. marca, do poľa "kritérium" zadáme hodnotu "> 10.03.2017" .

   V poli "Rozsah súčtu" je potrebné zadať oblasť, ktorej hodnoty, ktoré zodpovedajú zadaným kritériám, budú zhrnuté. V tomto prípade ide o výnosové hodnoty linky "Shop1" . Vyberte zodpovedajúce pole prvkov listu.

   Po zadaní všetkých zadaných údajov kliknite na tlačidlo "OK" .

4. Potom sa výsledok spracovania údajov pomocou funkcie SUMMER zobrazí v predtým špecifikovanom prvku pracovného hárka. V našom prípade je to 47921.53. Znamená to, že od 11.03.2017 a až do konca analyzovaného obdobia celkový výnos obchodov 1 bol 47 921,53 rubľov.

SUMIFS

Dokončili sme štúdiu prevádzkovateľov, ktorí používajú kritériá a zastavili funkciu ZHRNUTIE . Úlohou tejto matematickej funkcie je súčet hodnôt indikovaných plôch tabuľky, vybraných podľa viacerých parametrov. Syntax určeného operátora je:

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

"Rozsah súčtu" je argument, ktorý je adresou tohto poľa, do ktorého bunky sa pridajú zodpovedajúce určitému kritériu.

"Rozsah podmienok" je argument predstavujúci pole údajov, ktoré sú kontrolované z hľadiska súladu s podmienkou;

"Podmienka" je argument, ktorý je kritériom výberu pre pridanie.

Táto funkcia znamená operácie s viacerými súbormi podobných operátorov.

Pozrime sa, ako je tento operátor použiteľný na riešenie problémov v súvislosti s tabuľkou príjmov z predaja v predajných miestach. Budeme musieť vypočítať príjmy, ktoré priniesol Shop 1 za obdobie od 9. do 13. marca 2017. Súčasne by sa pri zhrnutí príjmov mali brať do úvahy len tie dni, ktorých príjmy presahujú 14 000 rubľov.

1. Znova vyberte bunku a zobrazí sa celková hodnota a kliknite na ikonu "Vložiť funkciu" .
2. V sprievodcovi funkcií sa najprv presunieme na "Matematický" blok a tam vyberieme položku s názvom "SUMMESLIMN" . Kliknite na tlačidlo "OK" .
3. Zobrazí sa okno argumentov operátora, ktorého meno bolo spomenuté vyššie.

   Nastavte kurzor do poľa "Rozsah súčtu" . Na rozdiel od nasledujúcich argumentov, toto je svojho druhu a poukazuje na súbor hodnôt, v ktorých sa vykoná súčet údajov zodpovedajúcich určeným kritériám. Potom vyberte oblasť riadka "Shop1" , ktorá uverejnila hodnoty príjmov pre príslušnú zásuvku.

   Po zobrazení adresy v okne prejdite na pole "Rozsah podmienok1" . Tu budeme musieť zobraziť súradnice riadku s dátumami. Prilepíme ľavé tlačidlo myši a vyberieme všetky údaje v tabuľke.

   Kurzor sme umiestnili do poľa "Podmienka1" . Prvou podmienkou je, že údaje zhrnieme najskôr 9. marca. Preto zadáme hodnotu "> 08.03.2017" .

   Prejdeme k argumentu "Rozsah podmienok2" . Tu je potrebné zadať tie isté súradnice, ktoré boli zaznamenané v poli "Rozsah podmienok1" . Robíme to rovnakým spôsobom, to znamená zvýraznením riadku s dátumami.

   Nastavte kurzor do poľa "Podmienka2" . Druhou podmienkou je, že dni, počas ktorých sa príjmy budú pripočítať, musia byť najneskôr do 13. marca. Preto zapíšeme nasledujúci výraz: «<14.03.2017» .

   Prejdite do poľa "Rozsah podmienok2" . V tomto prípade musíme vybrať rovnaké pole, ktorého adresa bola zadaná ako pole sumy.

   Po zobrazení adresy zadaného poľa v okne prejdite na pole "Podmienka3" . Ak vezmeme do úvahy, že do súčtu budú zahrnuté iba hodnoty, ktoré presahujú 14 000 rubľov, zaznamenávame nasledujúci znak: "> 14000" .

   Po poslednej akcii kliknite na tlačidlo "OK" .

4. Program dáva výsledok na hárok. To sa rovná 62491,38. To znamená, že za obdobie od 09 do 13 marca 2017 výška výnosov, keď bola pridaná v dňoch, keď presiahla 14 000 rubľov, predstavovala 62491,38 rubľov.

Podmienené formátovanie

Nástroj, ktorý sme opísali ako posledný, pri práci s týmito kritériami, je podmienené formátovanie. Vykonáva špecifikované formátovanie buniek, ktoré spĺňajú špecifikované podmienky. Pozrime sa na príklad práce s podmieneným formátovaním.

Vyberte bunky v tabuľke modrou farbou, kde hodnoty za deň presahujú 14 000 rubľov.

1. Vybrali sme celý rad prvkov v tabuľke, ktoré označujú príjmy z predajných miest za dni.
2. Prejdeme na kartu "Domov" . Klikneme na ikonu "Podmienené formátovanie" umiestnenú v bloku "Styly" na páske. Zobrazí sa zoznam akcií. Klikneme naň v pozícii "Vytvoriť pravidlo ..." .
3. Okno generovania pravidiel formátovania je aktivované. V oblasti výberu typu pravidla vyberte názov "Formátovať iba bunky, ktoré obsahujú" . V prvom poli bloku podmienok vyberte zo zoznamu možných možností hodnotu "Bunková hodnota" . V ďalšom poli zvoľte pozíciu "Viac" . V druhom z nich špecifikujeme samotnú hodnotu, z ktorej je potrebné formátovať prvky tabuľky. Máme 14000. Ak chcete vybrať typ formátovania, klikneme na tlačidlo "Formát ..." .
4. Okno formátu je aktivované. Presunutím na kartu "Vyplniť" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

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

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

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

• ← Vytváranie grafu závislosti v programe Microsoft Excel
• Vytvorenie plánu siete v programe Microsoft Excel →