Použití kritérií v aplikaci Microsoft Excel

Aplikace Microsoft Excel není pouze editor tabulky, ale také výkonná aplikace pro různé výpočty. V neposlední řadě je tato funkce způsobena vestavěnými funkcemi. Pomocí určitých funkcí (operátorů) je možné specifikovat i podmínky výpočtu, které se nazývají kritéria. Dozvíme se podrobněji, jak je používat při práci v aplikaci Excel.

Použití kritérií

Kritéria jsou podmínky, za kterých program provádí určité akce. Používají se v mnoha vestavěných funkcích. Ve svém názvu se nejčastěji vyskytuje výraz "IF" . K této skupině operátorů je třeba nejprve zahrnout ZEMĚ , ÚČTY , SUM , SUMMY . Kromě vestavěných operátorů se kritéria v aplikaci Excel používají také pro podmíněné formátování. Uvažujme jejich aplikaci v práci s různými nástroji daného stolního procesoru podrobněji.

ÚČTY

Hlavním úkolem operátora COUNTIF , který se týká statistické skupiny, je počítání buněk obsazených různými hodnotami, které splňují určitou předem stanovenou podmínku. Jeho syntaxe je následující:

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

Jak vidíte, má tento operátor dva argumenty. "Rozsah" je adresa pole prvků na listu, do kterého se má počítat.

"Kritérium" je argument, který specifikuje podmínku, že musí obsahovat buňky určené oblasti, které mají být zahrnuty do počtů. Jako parametr můžete použít číselný výraz, text nebo odkaz na buňku, ve které je kritérium obsaženo. V tomto případě můžete použít následující symboly pro označení kritéria: "<" ( "méně" ), ">" ( "více" ), "=" ( "rovno" ), "<>" ( "není rovno" ). Například pokud zadáte výraz "<50" , ve výpočtu budou vzaty v úvahu pouze prvky zadané argumentem "Rozsah" , ve kterých jsou číselné hodnoty menší než 50. Použití těchto znaků pro zadání parametrů bude relevantní pro všechny další možnosti, které budou projednány v této lekci níže.

A teď se podívejme na konkrétní příklad toho, jak tento operátor pracuje v praxi.

Takže tam je stůl, který ukazuje příjmy z pěti obchodů za týden. Potřebujeme znát počet dnů pro toto období, kdy v Obchodě 2 tržby překročily 15 000 rublů.

1. Vyberte prvek listu, ve kterém operátor vytiskne výsledek výpočtu. Poté klikněte na ikonu "Vložit funkci" .
2. Spustí se Průvodce funkcí . Přejdeme k bloku "Statistické" . Zde najdeme a rozlišujeme název "ZEMĚ" . Poté klikněte na tlačítko "OK" .
3. Zobrazí se okno argumentů výše uvedeného operátora. Do pole "Rozsah" zadejte oblast buňky, mezi kterou se provede výpočet. V našem případě byste měli vybrat obsah řádku "Shop 2" , ve kterém se nachází výnos za den. Umístěte kurzor do zadaného pole a podržením levého tlačítka myši vyberte příslušné pole v tabulce. Adresa zvoleného pole se zobrazí v okně.

   V dalším poli "Kritérium" stačí zadat parametr přímého výběru. V našem případě musíme počítat pouze ty tabulkové prvky, jejichž hodnota přesahuje 15000. Proto pomocí klávesnice přejdeme do zadaného pole výraz "> 15000" .

   Po provedení všech výše uvedených manipulací klikněte na tlačítko "OK" .

4. Program vypočítá a zobrazí výsledek v prvku listu, který byl zvýrazněn před aktivací průvodce funkcí . Jak je vidět, v tomto případě je výsledek rovný číslu 5. To znamená, že ve vybraném poli v pěti buňkách jsou hodnoty přesahující 15000. To znamená, že můžeme usuzovat, že v Obchodě 2 za pět dnů analyzovaných sedmi příjmů přesáhl 15 000 rublů.

Lekce: Průvodce funkce v programu Excel

ZEMĚ

Další funkcí, která funguje s kritérii, je COUNTRY . Odkazuje také na statistickou skupinu operátorů. Úloha COUNTERMAN počítá buňky ve specifikovaném poli, které splňují určitou sadu podmínek. Je to skutečnost, že můžete zadat ne jeden, ale několik parametrů a tento operátor odlišuje od předchozího. Syntaxe je:

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

"Rozsah podmínek" je totožný s prvním argumentem předchozího příkazu. To znamená, že se jedná o oblast, ve které buňky, které splňují stanovené podmínky, budou započítány. Tento operátor vám umožňuje určit několik takových oblastí najednou.

"Podmínka" je kritérium, které určuje, které prvky z příslušného pole dat půjde do výpočtu a které z nich nebudou. Každá daná datová oblast musí být zadána samostatně, i když se shoduje. Je nutné, aby všechny matice používané jako stavové oblasti měly stejný počet řádků a sloupců.

Chcete-li zadat několik parametrů stejné oblasti dat, například počítat počet buněk, ve kterých jsou hodnoty větší než určité číslo, ale menší než jiné číslo, měli byste zadat stejné pole několikrát, jako argument "Rozmezí podmínek" . Jako odpovídající argumenty "Podmínka" by však měly být použity různé kritéria.

Na příkladu stejného stolu s týdenními příjmy z obchodů budeme vypadat, jak to funguje. Potřebujeme znát počet dní v týdnu, kdy příjmy ve všech uvedených prodejnách dosáhly u nich stanoveného kurzu. Výše příjmů je následující:

• Obchod 1 - 14000 rublů;
• Obchod 2 - 15 000 rublů;
• Obchod 3 - 24000 rublů;
• Obchod 4 - 11 000 rublů;
• Obchod 5 - 32000 rublů.

1. Chcete-li provést výše uvedenou úlohu, zvýrazněte kurzor pomocí prvku listu, kde bude zobrazen výsledek zpracování dat země . Klepněte na ikonu "Vložit funkci" .
2. Při přepnutí na Průvodce funkcemi se vrátíme zpět do bloku "Statistické" . V seznamu je nutné najít název COUNTRY FORM a provést jeho výběr. Po provedení této akce musíte kliknout na tlačítko "OK" .
3. Po provedení výše uvedeného algoritmu akcí se otevře okno argumentů COUNTLINES .

   V poli "Rozsah podmínek1" zadejte adresu linky, ve které se nacházejí údaje o příjmech týdne Shop 1 za týden. Chcete-li to provést, vložte kurzor do pole a vyberte odpovídající řádek v tabulce. Souřadnice se zobrazí v okně.

   Vzhledem k tomu, že denní tržní sazba Obchodu 1 činí 14 000 rublů, pak do pole "Podmínka 1" zadáme výraz "> 14000" .

   V poli "Rozsah podmínek2 (3,4,5)" musíte zadat souřadnice řádku s týdenním výnosem v souladu s Store 2, Store 3, Store 4 a Store 5. Akce se provádí podle stejného algoritmu jako u prvního argumentu této skupiny.

   V poli "Podmínka2" , "Podmínka3" , "Podmínka 4" a "Podmínka5" zadáme hodnoty "> 15000 ", "> 24000" , "> 11000" a "> 32000" . Jak můžete odhadnout, tyto hodnoty odpovídají příjmovému intervalu, který přesahuje normu pro příslušný obchod.

   Po zadání všech potřebných dat (celkem 10 polí) klikněte na tlačítko "OK" .

4. Program vypočítá a zobrazí výsledek na obrazovce. Jak je vidět, rovná se číslu 3. To znamená, že za tři dny od analyzovaného týdne tržby ve všech prodejnách překročily pro ně stanovenou sazbu.

Nyní trochu změňte problém. Měli bychom vypočítat počet dnů, kdy obchod 1 obdržel příjmy přesahující 14 000 rublů, ale méně než 17 000 rublů.

1. Kurzor vložíme do prvku, ve kterém bude výstup výsledků výpočtu vyveden. Klepneme na ikonu "Vložit funkci" nad pracovní plochu listu.
2. Vzhledem k tomu, že jsme nedávno použili vzorec COUNCILS , nyní není nutné jít do skupiny "statistických" průvodců funkcí . Název tohoto operátora se nachází v kategorii "10 nedávno použitých" . Vyberte jej a klikněte na tlačítko "OK" .
3. Otevře okno argumentů operátora СЧЁТЕСЛИМН, které je již známé. Kurzor jsme umístili do pole "Rozsah podmínek1" a po upnutí levého tlačítka myši vyberte všechny buňky, které obsahují výnosy v dnech obchodu 1. Nachází se v řádku, který se nazývá "Shop 1" . Potom se v okně promítnou souřadnice určené oblasti.

   Dále umístíme kurzor do pole "Podmínka1" . Zde je třeba zadat dolní mez hodnot v buňkách, které se budou podílet na výpočtu. Zadejte výraz "> 14000" .

   V poli "Rozsah podmínek2" zadáváme stejnou adresu stejným způsobem jako v poli "Rozsah podmínek1" , tj. Znovu zadáme souřadnice buňky s hodnotami výnosů v prvním prodejním místě.

   V poli "Podmínka2" udáváme horní hranici výběru: "<17000" .

   Po provedení všech výše uvedených akcí klikněte na tlačítko "OK" .

4. Program vygeneruje výsledek výpočtu. Jak vidíte, konečná hodnota se rovná 5. To znamená, že za 5 dnů analyzovaných sedmi se výnosy v prvním obchodě pohybovaly v rozmezí od 14 000 do 17 000 rublů.

LÉTO

Další operátor, který používá kritéria, je LETNÍ . Na rozdíl od předchozích funkcí odkazuje na matematický blok operátorů. Jeho úkolem je shrnout údaje v buňkách, které odpovídají určitému stavu. Syntaxe je:

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

Argument "Rozsah" označuje oblast buněk, které budou zkontrolovány na splnění podmínky. Ve skutečnosti je stanovena na stejném principu, jako na stejném argumentu funkce Rady.

"Kritérium" je povinný argument, který určuje parametr pro výběr buňky ze zadané datové oblasti, která se má sčítat. Principy indikace jsou stejné jako principy analogických argumentů předchozích operátorů, které jsme uvažovali výše.

"Sčítání" je volitelným argumentem. Označuje konkrétní oblast pole, ve které bude součet proveden. Pokud ji vynecháte a nezadáte jej, ve výchozím nastavení se má za to, že se rovná hodnotě požadovaného argumentu "Rozsah" .

Nyní, jako vždy, zvážit uplatnění tohoto operátora v praxi. Na základě stejné tabulky stojíme před úkolem vypočítat částku výnosu v Obchodě 1 za období od 11.03.2017.

1. Vyberte buňku, ve které bude výsledek vygenerován. Klepněte na ikonu "Vložit funkci" .
2. Přejděte na průvodce funkcí v bloku "Matematický" a najděte název "LETNÍ" . Klikněte na tlačítko "OK" .
3. Zobrazí se okno s argumenty funkce SUMIFER . Existují tři pole odpovídající argumentům daného operátora.

   Do pole "Rozsah" zadejte oblast tabulky, ve které se budou nacházet hodnoty, které mají být zkontrolovány, pro splnění podmínek. V našem případě to bude řetězec dat. V tomto poli umístíme kurzor a vybereme všechny buňky obsahující data.

   Vzhledem k tomu, že potřebujeme přidat pouze výnosy z 11. března, do pole "Kritérium" zadáme hodnotu "> 10.03.2017" .

   V poli "Rozsah sčítání" je třeba zadat oblast, jejíž hodnoty jsou shodné se zadanými kritérii. V našem případě jsou to výnosové hodnoty řady "Shop1" . Vyberte odpovídající pole prvků listu.

   Po zadání všech zadaných údajů klikněte na tlačítko "OK" .

4. Poté se výsledek zpracování dat pomocí funkce SUMMER zobrazí v dříve zadaném prvku listu. V našem případě je to 47921.53. To znamená, že od 11. 3. 2017 a až do konce sledovaného období činil celkový příjem za Store 1 47 921,53 rublů.

SHRNUTÍ

Dokončili jsme studie operátorů, kteří používají kritéria, a zastavili funkci SUMMARY . Úkolem této matematické funkce je součet hodnot indikovaných oblastí tabulky, vybraných několika parametry. Syntaxe daného operátora je:

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

"Rozsah součtu" je argument, který je adresou tohoto pole, buňky, do kterých budou přidány odpovídající kritéria.

"Rozsah podmínek" je argument, který představuje pole dat, které jsou kontrolovány v souladu se stavem;

"Podmínka" je argument, který je kritériem výběru pro přidání.

Tato funkce zahrnuje operace s několika sadami podobných operátorů.

Podívejme se, jak je tento operátor použitelný pro řešení problémů v kontextu tabulky výnosů z prodeje v prodejnách. Budeme muset vypočítat příjmy, které Shop 1 přinesl za období od 9. do 13. března 2017. Současně by při sčítání výnosů měly být brány v úvahu pouze ty dny, jejichž výnos přesahuje 14 000 rublů.

1. Znovu vyberte buňku pro zobrazení celkové hodnoty a klikněte na ikonu "Vložit funkci" .
2. Nejprve v Průvodci funkcí přejdeme do bloku "Matematický" a vybereme položku s názvem "SUMMESLIMN" . Klikněte na tlačítko "OK" .
3. Zobrazí se okno argumentů operátora, jehož jméno bylo uvedeno výše.

   Nastavte kurzor do pole "Rozsah sčítání" . Na rozdíl od následujících argumentů se jedná o tento druh svého druhu a poukazuje na řadu hodnot, kdy bude provedeno shrnutí dat odpovídajících určeným kritériím. Poté vyberte oblast řádku "Shop1" , která zaúčtovala hodnoty příjmů pro příslušnou zásuvku.

   Po zobrazení adresy v okně přejděte na pole "Rozsah podmínek1" . Zde budeme muset zobrazit souřadnice řádku s daty. Zajistíme levou klávesu myši a vybereme všechny údaje v tabulce.

   Kurzor vložíme do pole "podmínka1" . První podmínkou je, abychom shrnovali údaje nejdříve 9. března. Proto zadáme hodnotu "> 08.03.2017" .

   Přejdeme k argumentu "Rozsah podmínek2" . Zde je třeba zadat stejné souřadnice, které byly zaznamenány v poli "Rozsah podmínek1" . Děláme to stejným způsobem, to znamená zvýrazněním řádku s daty.

   Nastavte kurzor do pole "Podmínka2" . Druhá podmínka spočívá v tom, že dny, k nimž budou příjmy přidány, musí být nejpozději 13. března. Proto zapisujeme následující výraz: «<14.03.2017» .

   Přejděte do pole "Rozsah podmínek2" . V tomto případě musíme zvolit stejné pole, jehož adresa byla zadána jako pole součtu.

   Po zadání adresy zadaného pole v okně přejděte do pole "Podmínka3" . Vzhledem k tomu, že do součtu budou zahrnuty pouze hodnoty, které přesahují 14 000 rublů, zaznamenáme následující znak: "> 14000" .

   Po poslední akci klikněte na tlačítko "OK" .

4. Program vyvede výsledek na list. To se rovná 62491.38. To znamená, že za období od 9. do 13. března 2017 byla částka příjmů, když byla přidána ve dnech, kdy přesáhla 14 000 rublů, 62491,38 rublů.

Podmíněné formátování

Poslední, popsaný námi, nástrojem při práci s použitými kritérii je podmíněné formátování. Provádí zadané formátování buněk, které splňují zadané podmínky. Podívejme se na příklad práce s podmíněným formátováním.

Vyberte buňky v tabulce modře, kde hodnoty za den překračují 14 000 rublů.

1. Vyberte celou řadu položek v tabulce, která označuje příjmy z prodejen za den.
2. Přejdeme na kartu "Domov" . Klikneme na ikonu "Podmíněné formátování" umístěné v bloku "Styly" na kazetě. Zobrazí se seznam akcí. Klikneme na něj na pozici "Vytvořit pravidlo ..." .
3. Okno generování pravidel formátování je aktivováno. V oblasti výběru typu pravidla vyberte název "Formátovat pouze buňky, které obsahují" . V prvním poli bloku podmínek vyberte ze seznamu možných možností "Hodnota buňky" . V dalším poli vyberte pozici "Více" . V druhém z nich uveďme hodnotu samotnou, z čehož potřebujeme formátovat prvky tabulky. Máme 14000. Chcete-li vybrat typ formátování, klikneme na tlačítko "Formát ..." .
4. Okno formátu je aktivováno. Přejděte na kartu "Vyplnit" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

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

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

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

• ← Vytváření grafu závislosti v aplikaci Microsoft Excel
• Vytvoření plánu sítě v aplikaci Microsoft Excel →