Pokyny k používání a příklady maticových vzorců

Důležité :  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

Chcete-li se stát výkonným uživatelem aplikace Excel, musíte vědět, jak se používají maticové vzorce, pomocí nichž lze provádět výpočty, které byste pomocí nematicových vzorců provádět nemohli. Následující článek je založen na řadě Sloupce výkonného uživatele aplikace Excel od Colina Wilcoxe, které byly převzaty z kapitoly 14 a 15 knihy Vzorce aplikace Excel 2002od Johna Walkenbacha, odborníka MVP pro aplikaci Excel, a vhodně přizpůsobeny. Informace o dalších knihách Johna Walkenbacha naleznete na jeho stránce.

V tomto článku:

Informace o maticových vzorcích

Informace o maticových konstantách

Vytvoření jednorozměrné a dvojrozměrné konstanty

Použití základních maticových vzorců

Použití pokročilých maticových vzorců

Informace o maticových vzorcích

Tato část představuje maticové vzorce a vysvětluje postupy jejich zadávání a úprav a odstraňování problémů s nimi.

Důvody použití maticových vzorců

Pokud máte zkušenosti s používáním vzorců v Excelu, dobře víte, že s nimi zvládnete některé poměrně složité operace. Můžete například vypočítat celkové náklady na půjčku za libovolný počet let. Jestli ale chcete ovládnout vzorce v Excelu na skutečně mistrovské úrovni, neobejdete se bez maticových vzorců. Ty umožňují provádět opravdu složité úlohy, například:

  • Vypočítat počet znaků obsažených v oblasti buněk

  • Sečíst pouze čísla, která splňují určité podmínky, například nejnižší hodnoty v oblasti, nebo čísla, která spadají mezi horní a dolní mez

  • Sečíst každou n-tou hodnotu v oblasti hodnot

Poznámka : Maticovým vzorcům se někdy říká taky „vzorce CSE“. To proto, že se do sešitů zadávají kombinací kláves CTRL+SHIFT+ENTER.

Rychlé seznámení s maticemi a maticovými vzorci

Pokud jste se někdy setkali s programováním, třeba jen letmo, nejspíš už jste narazili na pojem matice. Pro naše potřeby můžeme matici definovat jako kolekci položek. V Excelu mohou tyto položky ležet v jednom řádku (ten se nazývá jednorozměrná vodorovná matice), v jednom sloupci (jednorozměrná svislá matice), nebo ve více řádcích a sloupcích (dvojrozměrná matice). V Excelu se nedají vytvářet trojrozměrné matice ani vzorce pro ně.

Maticový vzorec je vzorec, který může provádět více výpočtů s jednou nebo více položkami v matici. Může vracet buď více výsledků, nebo jediný výsledek. Maticový vzorec můžete například umístit do oblasti buněk a použít jej k výpočtu sloupce nebo řádku souhrnů. Můžete ho také umístit do jediné buňky a potom vypočítat jednu hodnotu. Maticový vzorec, který se nachází v několika buňkách, se nazývá vícebuňkový vzorec a maticový vzorec umístěný v jediné buňce naopak jednobuňkový vzorec.

Příklady v následující části ukazují, jak vytvořit vícebuňkový a jednobuňkový maticový vzorec.

Vyzkoušejte to!

Toto cvičení ukazuje, jak používat vícebuňkové a jednobuňkové vzorce pro výpočet údajů o prodeji. První sada kroků používá vícebuňkový vzorec k výpočtu množiny souhrnů. Druhá sada používá jednobuňkový vzorec k výpočtu celkového součtu.

Vytvoření vícebuňkového maticového vzorce

  1. Otevřete nový prázdný sešit.

  2. Zkopírujte data ukázkového listu a vložte je do nového sešitu s počátkem v buňce A1.

    Jak zkopírovat data ukázkového listu

    1. Vytvořte prázdný sešit nebo list.

    2. Vyberte příklad v tématu nápovědy.

      Poznámka : Nevybírejte záhlaví řádků ani sloupců.

      Výběr příkladu z nápovědy
      Výběr příkladu z nápovědy
    3. Stiskněte kombinaci kláves CTRL+C.

    4. Vyberte v listu buňku A1 a stiskněte klávesy CTRL+V.

Prodávající osoba

Typ auta

Počet prodaných kusů

Jednotková cena

Celkový prodej

Klčo

Sedan

17

2200

Kupé

1

1800

Hernady

Sedan

18

2 300

Kupé

2,5

1700

Kutěj

Sedan

15

2000

Kupé

2,5

1 600

Opravil

Sedan

3

2150

Kupé

17

1950

Pobudová

Sedan

18

2250

Kupé

2,5

2000

  1. Použijte tlačítko Možnosti vložení Obrázek tlačítka , které se zobrazí vedle, k výběru cílového formátování.

  2. Chcete-li vynásobit hodnoty v matici (oblast buněk C2 až D11) vyberte buňky E2 až E11 a potom na řádku vzorců zadejte následující vzorec:

    =C2:C11*D2:D11

  3. Stiskněte klávesy CTRL+SHIFT+ENTER.

V Excelu se vzorec automaticky uzavře do složených závorek ({}) a jeho instance se umístí do každé buňky vybrané oblasti. Celá operace proběhne velmi rychle, takže ve sloupci E ihned uvidíte celkový objem prodeje jednotlivých typů aut u každého prodejce.

Ukázková data

Vytvoření jednobuňkového maticového vzorce

  1. Do buňky A13 napište Celkový prodej.

  2. Do buňky B13 zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =SUMA(C2:C11*D2:D11)

V tomto případě aplikace Excel vynásobí hodnoty v matici (oblast buněk C2 až D11) a použije funkci SUMA k sečtení součtů dohromady. Výsledkem je celkový součet prodejů 111 800. Tento příklad ukazuje, jak výkonný může tento typ vzorce být. Předpokládejme například že máte 15000 řádků dat. Všechna data nebo část z nich můžete sečíst vytvořením maticového vzorce v jediné buňce.

Všimněte si také, že jednobuňkový vzorec (v buňce B13) je zcela nezávislý na vícebuňkovém vzorci (vzorec v buňkách E2 až E11). To ukazuje další výhodu použití maticových vzorců– flexibilitu. Je možné provést libovolný počet akcí, například změnit vzorce ve sloupci E nebo tento sloupec zcela odstranit, aniž by to ovlivnilo jednobuňkový vzorec.

Maticové vzorce nabízejí také tyto výhody:

  • Konzistence:    Kliknete-li na některou z buněk od E2 směrem dolů, zobrazí se stejný vzorec. Tato konzistence pomůže zajistit větší přesnost.

  • Zabezpečení:    Nelze přepsat součást vícebuňkového maticového vzorce. Klikněte například na buňku E3 a stiskněte klávesu DELETE. Je třeba buď vybrat celou oblast buněk (E2 až E11) a změnit vzorec pro celou matici, nebo ponechat matici, jak je. Další bezpečnostní opatření vyžaduje potvrzení změny vzorce stisknutím kombinace kláves CTRL+SHIFT+ENTER.

  • Menší velikosti souborů:    Často lze místo několika vzorců použít jediný maticový vzorec. Sešit, který jsme vytvořili v tomto cvičení, například používá k výpočtu výsledků ve sloupci E jediný maticový vzorec. Při použití standardních vzorců (jako =C2*D2) byste k získání stejných výsledků potřebovali 11 různých vzorců.

Syntaxe maticových vzorců

Pro maticové vzorce se z větší části používá standardní syntaxe vzorce. Všechny začínají rovnítkem a můžete v nich použít kteroukoli z předdefinovaných funkcí aplikace Excel. Základní rozdíl při použití maticového vzorce spočívá v tom, že k zadání vzorce je třeba stisknout klávesy CTRL+SHIFT+ENTER. Když toto provedete, aplikace Excel uzavře maticový vzorec do složených závorek — pokud závorky zadáte ručně, vzorec bude převeden na textový řetězec a nebude funkční.

Dále je třeba pochopit, že maticové funkce jsou určitou formou zkráceného zápisu. Například vícebuňková funkce, kterou jste použili dříve, je ekvivalentem:

=C2*D2
=C3*D3

a tak dále. Jednobuňkový vzorec v buňce B13 kondenzuje všechny tyto operace násobení a k tomu aritmetický výpočet potřebný k sečtení těchto dílčích součtů: =E2+E3+E4 a tak dále.

Pravidla zadávání a úprav maticových vzorců

Základní pravidlo vytváření maticových vzorců stojí za to ještě jednou zopakovat: Vždy, když chcete zadat nebo upravit maticový vzorec, začněte stisknutím kombinace kláves CTRL + SHIFT + ENTER. Toto pravidlo platí pro jednobuňkové i vícebuňkové vzorce.

Při práci s vícebuňkovými vzorci je navíc třeba dodržovat následující pravidla:

  • Ještě než zadáte vzorec, je třeba vybrat oblast buněk pro ukládání výsledků. Při vytváření vícebuňkového maticového vzorce v našem cvičení jste to udělali v kroku 3 výběrem buněk E2 až E11.

  • V rámci maticového vzorce nelze měnit obsah jednotlivých buněk. Chcete-li to vyzkoušet, vyberte v ukázkovém sešitě buňku E3 a stiskněte klávesu DELETE.

  • Můžete přesunout nebo odstranit celý maticový vzorec, avšak nelze přesunout ani odstranit jeho část. Jinými slovy, pokud chcete maticový vzorec zmenšit, je třeba nejdřív odstranit ten stávající a potom začít znovu.

    Tip : Chcete-li odstranit maticový vzorec, vyberte celý vzorec (například =C2:C11*D2:D11), stiskněte klávesu DELETE a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

  • Do vícebuňkového maticového vzorce nelze vložit prázdné buňky ani z něj buňky odstranit.

Rozbalení maticového vzorce

Někdy se vám může stát, že budete potřebovat maticový vzorec rozšířit. (Připomínáme, že zmenšit ho nemůžete.) Postup není složitý, je ale třeba dodržet pravidla uvedená v předchozí části.

  1. V ukázkovém sešitu vymažte všechny textové a jednobuňkové vzorce umístěné pod hlavní tabulkou.

  2. Tyto další řádky dat vložte do sešitu s počátkem v buňce A12. Použijte tlačítko Možnosti vložení Obrázek tlačítka , které se zobrazí vedle, k výběru cílového formátování.

Tomek

Sedan

18

25 000

Kupé

7:

19000

Vozdecká

Sedan

1

2200

Kupé

15

2000

Junk

Sedan

2,5

2 300

Kupé

2,5

2100

  1. Vyberte oblast buněk, která obsahuje aktuální maticový vzorec (E2:E11), a prázdné buňky (E12:E17), které jsou vedle nových dat. Jinými slovy, vyberte buňky E2:E17.

  2. Stisknutím klávesy F2 přepněte do režimu úprav.

  3. V řádku vzorců změňte C11 na C17 a D11 na D17 a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER. V aplikaci Excel se aktualizují vzorce v buňkách E2 až E11 a do nových buněk E12 až E17 se umístí instance vzorce .

    Ukázková data

Nevýhody použití maticových vzorců

Maticové vzorce se mohou zdát přímo zázračné, mají ale i svoje nevýhody:

  • Může se stát, že zapomenete stisknout kombinaci kláves CTRL+SHIFT+ENTER. Nezapomeňte tuto kombinaci stisknout vždy při zadávání nebo úpravě maticového vzorce.

  • Ostatní uživatelé pravděpodobně nebudou vašim vzorcům rozumět. Maticové vzorce jsou relativně nedokumentované, takže pokud ostatní lidé potřebují vaše sešity upravovat, měli byste se maticovým vzorcům raději vyhnout, nebo zajistit, aby tito uživatelé věděli, jak je měnit.

  • V závislosti na rychlosti zpracování a paměti počítače mohou velké maticové vzorce zpomalovat výpočty.

Začátek stránky

Informace o maticových konstantách

Tato část představuje maticové konstanty a vysvětluje postupy zadávání a úprav maticových konstant a odstraňování problémů s nimi.

Stručný úvod do maticových konstant

Maticové konstanty jsou součástí maticových vzorců. Vytvoříte je tak, že zadáte seznam položek a ručně ho uzavřete do složených závorek ({ } ), například:

={ 1;2;3;4;5}

Dříve v tomto článku jsme zdůrazňovali nutnost stisknutí kombinace kláves CTRL+SHIFT+ENTER při vytváření maticových vzorců. Vzhledem k tomu, že maticové konstanty jsou součástí maticových vzorců, uzavřete je do složených závorek ručním zadáním. K zadání celého vzorce pak použijete kombinaci kláves CTRL+SHIFT+ENTER.

Pokud položky oddělujete čárkami, vytvoříte vodorovnou matici (řádek). Oddělujete-li položky středníky, vytvoříte svislou matici (sloupec). Chcete-li vytvořit dvourozměrnou matici, oddělujete položky v jednotlivých řádcích čárkami a jednotlivé řádky oddělujete středníky.

Podobně jako u maticových vzorců je možné maticové konstanty používat s libovolnými předdefinovanými funkcemi aplikace Excel. V následujících částech je vysvětleno, jak se vytvářejí jednotlivé druhy konstant a jak se tyto konstanty používají s funkcemi aplikace Excel.

Vytvoření jednorozměrné a dvojrozměrné konstanty

Následující postup vám umožní vyzkoušet si vytváření vodorovné, svislé a dvojrozměrné konstanty.

Vytvoření vodorovné konstanty

  1. Použijte sešit z předchozího sloupce nebo otevřete nový sešit.

  2. Vyberte buňky A1 až E1.

  3. V řádku vzorců zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    ={1,2,3,4,5}

    Poznámka : V tomto případě je třeba zadat počáteční a koncovou složenou závorku ({ }).

Zobrazí se následující výsledek.

Vodorovná maticová konstanta ve vzorci

Pravděpodobně nevíte, proč nelze čísla prostě zadat ručně. Pokračujte dál, protože v části Konstanty ve vzorcích tohoto článku jsou ukázány výhody používání maticových konstant.

Vytvoření svislé konstanty

  1. V sešitu vyberte sloupec z pěti buněk.

  2. V řádku vzorců zadejte následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    ={1;2;3;4;5}

Zobrazí se následující výsledek.

Svislá maticová konstanta v maticovém vzorci

Vytvoření dvojrozměrné konstanty

  1. Vyberte v sešitu blok buněk široký čtyři sloupce a vysoký tři řádky.

  2. V řádku vzorců zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    ={1;2;3;4|5;6;7;8|9;10;11;12}

Zobrazí se následující výsledek.

Dvourozměrná maticová konstanta v maticovém vzorci

Použití konstant ve vzorcích

Už jste se seznámili se zadáváním maticových konstant. Podívejte se teď na jednoduchý příklad, kde se popsané principy používají:

  1. Otevřete prázdný list.

  2. Zkopírujte následující tabulku počínaje buňkou A1. Použijte tlačítko Možnosti vložení Obrázek tlačítka , které se zobrazí vedle, k výběru cílového formátování.

15

1

17

18

7:

  1. Do buňky A3 zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    = SUMA( A1:E1*{1,2,3,4,5})

Všimněte si, že aplikace Excel uzavře konstantu do další dvojice složených závorek, protože je zadaná jako maticový vzorec.

Maticový vzorec s maticovou konstantou

V buňce A3 se zobrazí hodnota 85. V další části je vysvětleno, jak vzorec pracuje.

Informace o syntaxi maticové konstanty

Vzorec, který jste právě použili, obsahuje několik částí.

Syntaxe maticového vzorce s maticovou konstantou

1. Funkce

2. Uložená matice

3. Operátor

4. Maticová konstanta

Poslední prvek uvnitř závorek je maticová konstanta: {1;2;3;4;5}. Nezapomeňte, že aplikace Excel neuzavírá maticové konstanty do složených závorek, ty zadáváte přímo vy. Nezapomeňte také, že po přidání konstanty do maticového vzorce je k zadání vzorce třeba stisknout kombinaci kláves CTRL+SHIFT+ENTER.

Vzhledem k tomu, že aplikace Excel provede operace s výrazy uzavřenými v závorkách jako první, další dva prvky přicházející do hry jsou hodnoty uložené v sešitu (A1:E1) a operátor. V tomto okamžiku vzorec vynásobí hodnoty v uložené matici odpovídajícími hodnotami v konstantě. Je to ekvivalentní vzorci:

= SUMA( A1*1;B1*2;C1*3;D1*4;E1*5)

Nakonec funkce SUMA sečte hodnoty a v buňce A3 se zobrazí součet 85.

Chcete-li se vyhnout použití uložené matice a uchovat celou operaci v paměti, nahraďte uloženou matici další maticovou konstantou:

= SUMA( {3;4;5;6;7}*{1;2;3;4;5})

Chcete-li to vyzkoušet, zkopírujte tento vzorec, vyberte v sešitě prázdnou buňku, vložte vzorec do řádku vzorců a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER. Zobrazí se stejný výsledek jako v předchozím cvičení, ve kterém jste použili maticový vzorec =SUMA(A1:E1*{1;2;3;4;5}).

Prvky, které můžete použít v konstantách

Maticové konstanty mohou obsahovat čísla, text, logické hodnoty (například PRAVDA a NEPRAVDA) a chybové hodnoty (například #NENÍ_K_DISPOZICI). Používat můžete čísla ve formátu celé číslo, desetinné číslo a ve vědeckých formátech. Vložíte-li text, je nutné jej ohraničit uvozovkami (").

Maticové konstanty nesmí obsahovat další matice, vzorce ani funkce. Jinými slovy, mohou obsahovat pouze text nebo čísla oddělená středníky či svislicemi. Zadáte-li například vzorec {1;2;A1:D4} nebo {1;2;SUMA(Q2:Z8)}, zobrazí aplikace Excel upozornění. Číselné hodnoty navíc nemohou obsahovat znak procenta, znak dolaru, označení měny Kč, čárky ani kulaté závorky.

Pojmenování maticových konstant

Pravděpodobně nejlepší způsob, jak používat maticové konstanty, je pojmenovat je. Pojmenované konstanty mohou být snáze používány a mohou skrýt některé složitosti vašich maticových vzorců začínajícím uživatelům. Chcete-li pojmenovat maticovou konstantu a použít ji ve vzorci, proveďte následující kroky:

  1. Na kartě Vzorce klepněte ve skupině Definované názvy na položku Definovat název.

    Zobrazí se dialogové okno Definovat název.

  2. Do pole Název zadejte Čtvrtletí1.

  3. Do pole Odkaz na zadejte následující konstantu (nezapomeňte ručně zadat složené závorky):

    ={"Leden";"Únor";"Březen"}

    Obsah dialogového okna by měl vypadat následovně:

    Úprava dialogového okna Název pomocí vzorce

  4. Klikněte na OK.

  5. Na listu vyberte řádek se třemi prázdnými buňkami.

  6. Zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

    =Čtvrtletí1

Zobrazí se následující výsledek.

Pojmenovaná matice zadaná jako vzorec

Když pojmenovanou konstantu používáte jako maticový vzorec, nezapomeňte zadat znaménko rovná se. Pokud ho nepoužijete, Excel matici interpretuje jako textový řetězec. Nezapomeňte, že můžete používat také kombinace textu a čísel.

Odstraňování problémů s maticovými konstantami

Pokud maticové konstanty nefungují, hledejte následující problémy:

  • Některé prvky pravděpodobně nejsou odděleny odpovídajícím znakem. Pokud vynecháte středník či svislici nebo je nesprávně umístíte, maticová konstanta se nemusí vytvořit správně nebo se může zobrazit upozornění.

  • Vybraná oblast buněk možná neodpovídá počtu prvků v konstantě. Vyberete-li například k použití s 5buňkovou konstantou sloupec šesti buněk, zobrazí se v prázdné buňce chybová hodnota #NENÍ_K_DISPOZICI. Vyberete-li naopak příliš málo buněk, aplikace Excel vynechá hodnoty, které nemají odpovídající buňky.

Maticové konstanty v akci

Následující příklady ukazují některé ze způsobů, kterými můžete zadat maticové konstanty, jež chcete použít v maticových vzorcích. Některé z příkladů používají funkci TRANSPOZICE k převedení řádků na sloupce a naopak.

Vynásobení každé položky v matici

  1. Vyberte blok prázdných buněk o šířce čtyř sloupců a výšce tří řádků.

  2. Zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

    ={1;2;3;4|5;6;7;8|9;10;11;12}*2

Druhá mocnina položek v matici

  • Vyberte blok prázdných buněk o šířce čtyř sloupců a výšce tří řádků.

  • Zadejte následující maticový vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

    ={1;2;3;4|5;6;7;8|9;10;11;12}*{1;2;3;4|5;6;7;8|9;10;11;12}

    Můžete také zadat tento maticový vzorec, který používá operátor stříška ( ^):

    ={1;2;3;4|5;6;7;8|9;10;11;12}^2

Transpozice jednorozměrného řádku

  1. Vyberte sloupec pěti prázdných buněk.

  2. Zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =TRANSPOZICE({1;2;3;4;5})

Ačkoli jste zadali vodorovnou maticovou konstantu, funkce TRANSPOZICE převede maticovou konstantu na sloupec.

Transpozice jednorozměrného sloupce

  1. Vyberte řádek pěti prázdných buněk.

  2. Zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =TRANSPOZICE({1|2|3|4|5})

Ačkoli jste zadali svislou maticovou konstantu, funkce TRANSPOZICE převede maticovou konstantu na řádek.

Transpozice dvojrozměrné konstanty

  1. Vyberte blok prázdných buněk o šířce tří sloupců a výšce čtyř řádků.

  2. Zadejte následující konstantu a stiskněte kombinaci kláves CTRL+SHIFT+ENTER.

    =TRANSPOZICE({1;2;3;4|5;6;7;8|9;10;11;12})

Funkce TRANSPOZICE převede jednotlivé řádky na řadu sloupců.

Začátek stránky

Použití základních maticových vzorců

V této části jsou uvedeny příklady základních maticových vzorců.

Začínáme

S využitím dat v tomto oddílu vytvořte dva ukázkové listy.

  1. Otevřete existující sešit nebo vytvořte nový a zkontrolujte, že obsahuje dva prázdné listy.

  2. Zkopírujte data z následující tabulky a vložte je do listu tak, že začnete v buňce A1.

4000

příliš

2,5

5,08

15

1

1200

žluťoučký kůň

17

18

7:

2,5

3200

úpěl nad

3

10

11

3,5

475

ďábelsky líným

13

14

150

16

5000

uživatelem

2000

600

1700

20 000

2700

  1. Hotový list by měl vypadat takto.

    Celá ukázková data

  2. První list pojmenujte Data, druhý prázdný list pak Matice.

Vytvoření matic a maticových konstant z existujících hodnot

Následující příklad vysvětluje, jak lze používat maticové vzorce k vytvoření propojení mezi oblastmi buněk v různých listech. Ukazuje také, jak lze ze stejných hodnot vytvořit maticovou konstantu.

Vytvoření matice z existujících hodnot

  1. V ukázkovém sešitě vyberte list Matice.

  2. Vyberte oblast buněk C1 až E3.

  3. Zadejte do řádku vzorců následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =Data!E1:G3

Zobrazí se následující výsledek.

Výsledky na listu Matice

Vzorec obsahuje odkazy na hodnoty uložené v buňkách E1 až G3 na listu Data. Alternativou k tomuto vícebuňkovému maticovému vzorci je umístit do každé buňky na listu Matice jedinečný vzorec. Vypadalo by to takto:

=Data!E1

=Data!F1

=Data!G1

=Data!E2

=Data!F2

=Data!G2

=Data!E3

=Data!F3

=Data!G3

Změníte-li některé hodnoty na listu Data, tyto změny se projeví na listu Matice. Pamatujte, že chcete-li změnit nějaké hodnoty na listu Data, musíte postupovat podle pravidel pro úpravy maticových vzorců. Více informací o těchto pravidlech naleznete v části Informace o maticových vzorcích.

Vytvoření maticové konstanty z existujících hodnot

  1. V listu Matice vyberte buňky C1 až E3.

  2. Stisknutím klávesy F2 přepněte do režimu úprav.

  3. Stisknutím klávesy F9 převeďte odkazy na buňky na hodnoty. Excel hodnoty převede na maticovou konstantu.

  4. Stisknutím kláves CTRL+SHIFT+ENTER zadejte maticovou konstantu jako maticový vzorec.

Aplikace Excel nahradí maticový vzorec =Data!E1:G3 následující maticovou konstantou:

={ 1;2;3|5;6;7|9;10;11}

Propojení mezi listy Data a Matice bylo přerušeno a maticový vzorec byl nahrazen maticovou konstantou.

Výpočet počtu znaků v oblasti buněk

Následující příklad ukazuje, jak spočítat počet znaků včetně mezer v oblasti buněk.

  • Na listu Data zadejte do buňky C7 následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =SUMA(DÉLKA(C1:C5))

V buňce C7 se zobrazí hodnota 50.

V tomto případě vrátí funkce DÉLKA délku jednotlivých textových řetězců v každé z buněk oblasti. Funkce SUMA potom tyto hodnoty sečte a výsledek zobrazí v buňce obsahující vzorec (C7).

Nalezení n nejmenších hodnot v oblasti

Tento příklad ukazuje, jak vyhledat tři nejmenší hodnoty v oblasti buněk.

  1. Na listu Data vyberte buňky A12 až A14.

    V této množině buněk se budou uchovávat výsledky vrácené maticovým vzorcem.

  2. V řádku vzorců zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    = SMALL(A1:A10;{1|2|3})

V buňkách A12 až A14 se zobrazí hodnoty 400, 475 a 500.

Tento vzorec používá maticovou konstantu k vyhodnocení funkce SMALL třikrát a vrátí nejmenší (1), druhou nejmenší (2) a třetí nejmenší (3) hodnotu v matici obsažené v buňkách A1:A10. Pokud chcete najít více hodnot, přidejte do konstanty další argumenty a odpovídající počet buněk pro výsledek do oblasti A12:A14. U tohoto vzorce můžete použít i další funkce, například SUMA nebo PRŮMĚR:

= SUMA( SMALL(A1:A10;{1|2|3}))

= PRŮMĚR( SMALL(A1:A10;{1|2|3}))

Nalezení n nejvyšších hodnot v oblasti

Chcete-li vyhledat nejvyšší hodnoty v oblasti, můžete nahradit funkci SMALL funkcí LARGE. V následujícím příkladu jsou navíc použity funkce ŘÁDEK a NEPŘÍMÝ.ODKAZ.

  1. Na listu Data vyberte buňky A12 až A14.

  2. Stisknutím klávesy DELETE vymažte stávající vzorec, ale buňky ponechte vybrané.

  3. V řádku vzorců zadejte následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    = LARGE(A1:A10;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3")))

V buňkách A12 až A14 se zobrazí hodnoty 3200, 2700 a 2000.

V tomto okamžiku je užitečné říct si něco o funkcích ŘÁDEK a NEPŘÍMÝ.ODKAZ. Funkce ŘÁDEK umožňuje vytvořit matici po sobě jdoucích celých čísel. Vyberte ve cvičném sešitu například prázdný sloupec 10 buněk, do buněk A1:A10 zadejte tento maticový vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

=ŘÁDEK(1:10)

Vzorec vytvoří sloupec 10 po sobě jdoucích celých čísel. Existuje ale potenciální problém, který uvidíte, pokud vložíte řádek nad oblast, která obsahuje maticový vzorec (tj. nad řádek 1). Aplikace Excel upraví odkazy na řádky a vzorec vygeneruje celá čísla od 2 do 11. Pokud chcete tento problém vyřešit, přidejte do vzorce funkci NEPŘÍMÝ.ODKAZ:

= ŘÁDEK( NEPŘÍMÝ.ODKAZ("1:10"))

Funkce NEPŘÍMÝ.ODKAZ používá jako svůj argument textový řetězec (z toho důvodu je oblast 1:10 uzavřena v uvozovkách). Když vložíte řádky nebo maticový vzorec jinak posunete, aplikace Excel textové hodnoty nepřizpůsobí. V důsledku toho vygeneruje funkce ŘÁDEK vždy matici celých čísel, kterou požadujete.

Prozkoumejme vzorec, který jste před chvílí použili: =LARGE(A1:A10;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3"))).  Začneme od vnitřních závorek a budeme postupovat směrem ven: Funkce NEPŘÍMÝ.ODKAZ vrací množinu textových hodnot, v tomto případě hodnoty 1 až 3. Funkce ŘÁDEK pak vygeneruje tříbuňkovou sloupcovou matici. Funkce LARGE použije hodnoty v oblasti buněk A1:A10 a vyhodnotí se celkem třikrát, jednou pro každý odkaz vrácený funkcí ŘÁDEK. Do tříbuňkové sloupcové matice jsou vráceny hodnoty 3200, 2700 a 2000. Chcete-li zjistit více hodnot, přidejte do funkce NEPŘÍMÝ.ODKAZ větší oblast buněk.

Nakonec můžete tento vzorec použít s jinými funkcemi, například SUMA a PRŮMĚR.

Nalezení nejdelšího textového řetězce v oblasti buněk

V tomto příkladu se vyhledá nejdelší řetězec textu v oblasti buněk. Vzorec funguje pouze v případě, že oblast dat obsahuje jeden sloupec buněk.

  • Na listu Data vymažte z buňky C7 stávající vzorec, zadejte do ní následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

    =INDEX(C1:C5;POZVYHLEDAT(MAX(DÉLKA(C1:C5));DÉLKA(C1:C5);0);1)

V buňce C7 se zobrazí hodnota ďábelsky líným.

Prozkoumejme tento vzorec. Začneme od vnitřních prvků a budeme postupovat směrem ven. Funkce DÉLKA vrací délku každé z položek v oblasti buněk C1:C5. Funkce MAX určí nejvyšší hodnotu z těchto položek, což odpovídá nejdelšímu textovému řetězci, který je v buňce C4.

Tady to začíná být trochu složitější. Funkce POZVYHLEDAT vypočítá offset (relativní polohu) buňky obsahující nejdelší textový řetězec. Potřebuje k tomu tři argumenty: vyhledávací hodnotu, prohledávanou matici a typ shody. Funkce POZVYHLEDAT hledá v prohledávané matici zadanou vyhledávací hodnotu. V tomto případě je to nejdelší textový řetězec:

( MAX ( DÉLKA(C1:C5))

a tento řetězec je umístěn v této matici:

DÉLKA( C1:C5)

Argument typ shody je 0. Typ shody může nabývat hodnot 1, 0 nebo -1. Pokud zadáte 1, funkce POZVYHLEDAT vrátí nejvyšší hodnotu, která je menší nebo rovna vyhledávací hodnotě. Pokud zadáte 0, funkce POZVYHLEDAT vrátí první hodnotu, která je přesně rovna vyhledávací hodnotě. Pokud zadáte -1, najde funkce POZVYHLEDAT nejmenší hodnotu, která je větší než nebo rovna zadané vyhledávací hodnotě. Jestliže typ shody vynecháte, aplikace Excel předpokládá hodnotu 1.

A nakonec, funkce INDEX má tyto argumenty: matici a číslo řádku a sloupce v rámci matice. Oblast buněk C1:C5 představuje matici, funkce MATCH dává adresu buňky a poslední argument (1) určuje, že hodnota pochází z prvního sloupce v matici.

Další informace o funkcích, které zde byly probírány, naleznete v nápovědě aplikace Excel.

Začátek stránky

Použití pokročilých maticových vzorců

V této části jsou uvedeny příklady pokročilých maticových vzorců.

Součet oblasti obsahující chybové hodnoty

Funkce SUMA v aplikaci Excel nefunguje, pokoušíte-li se sečíst oblast obsahující chybovou hodnotu, například #NENÍ_K_DISPOZICI. Tento příklad ukazuje, jak sečíst hodnoty v oblasti s názvem Data, která obsahuje chyby.

= SUMA( KDYŽ(JE.CHYBHODN(Data);"";Data))

Vzorec vytvoří novou matici obsahující původní hodnoty bez všech chybových hodnot. Začneme vnitřní funkcí a budeme postupovat směrem ven. Funkce JE.CHYBHODN vyhledá chyby v oblasti buněk (Data). Funkce KDYŽ vrátí zadanou hodnotu, pokud je podmínka vyhodnocena jako PRAVDA, a jinou hodnotu, pokud je podmínka vyhodnocena jako NEPRAVDA. V tomto případě vrátí prázdné řetězce ("") pro všechny chybové hodnoty, protože ty se vyhodnotí jako PRAVDA, a zbývající hodnoty oblasti (Data), protože ty se vyhodnotí jako NEPRAVDA, což znamená, že nejde o chybové hodnoty. Funkce SUMA pak vypočítá celkový součet vyfiltrované matice.

Počet chybových hodnot v oblasti

Tento příklad je podobný předchozímu vzorci, ale vrátí počet chybových hodnot v oblasti s názvem Data místo jejich odfiltrování:

= SUMA( KDYŽ(JE.CHYBHODN(Data);1,0))

Tento vzorec vytvoří matici obsahující hodnotu 1 pro buňky s chybami a hodnotu 0 pro buňky, které chyby neobsahují. Dosáhnout stejného výsledku můžete i po zjednodušení vzorce odebráním třetího argumentu funkce KDYŽ, tedy takto:

= SUMA( KDYŽ(JE.CHYBHODN(Data);1))

Pokud nezadáte argument, funkce KDYŽ vrátí hodnotu NEPRAVDA, jestliže buňka neobsahuje chybovou hodnotu. Vzorec lze ještě dále zjednodušit:

= SUMA( KDYŽ(JE.CHYBHODN(Data)*1))

Tato verze funguje díky tomu, že PRAVDA*1=1 a NEPRAVDA*1=0.

Součet hodnot na základě podmínek

Někdy je třeba sečíst hodnoty na základě určitých podmínek. Tento maticový vzorec například sečte v oblasti s názvem Prodej pouze kladná čísla:

= SUMA( KDYŽ(Prodej>0;Prodej))

Funkce KDYŽ vytvoří matici odpovídajících a neodpovídajících hodnot. Funkce SUMA v podstatě ignoruje neodpovídající hodnoty, protože 0+0=0. Oblast buněk, kterou v tomto vzorci používáte, se může skládat z libovolného počtu řádků a sloupců.

Můžete také sčítat hodnoty splňující více podmínek. Následující maticový vzorec například sečte hodnoty větší než 0 a menší než nebo rovny 5:

= SUMA( (Prodej>0)*(Prodej<=5)*(Prodej))

Mějte na paměti, že tento vzorec vrátí chybu, pokud oblast obsahuje jednu nebo více nečíselných buněk.

Můžete také vytvořit maticové vzorce, které používají typ podmínky NEBO. Můžete například sečíst hodnoty, které jsou menší než 5 a větší než 15:

= SUMA( KDYŽ((Prodej<5)+(Prodej>15),Prodej))

Funkce KDYŽ vyhledá všechny hodnoty menší než 5 a větší než 15 a pak tyto hodnoty předá funkci SUMA.

Důležité : Funkce A a NEBO nelze v maticových vzorcích použít přímo, protože tyto funkce vracejí jeden výsledek (PRAVDA nebo NEPRAVDA) a maticové funkce vyžadují matice výsledků. Toto omezení lze obejít pomocí logiky předvedené v předchozím vzorci. Jinými slovy, provádíte matematické operace, například sčítání nebo násobení, s hodnotami splňujícími podmínku NEBO nebo A.

Výpočet průměru s vyloučením nul

Tento příklad ukazuje, jak odebrat nuly z oblasti v případě, že v dané oblasti potřebujete vypočítat průměrné hodnoty. Vzorec používá oblast dat s názvem Prodej:

= PRŮMĚR( KDYŽ(Prodej<>0,Prodej))

Funkce KDYŽ vytvoří matici hodnot, které nejsou rovny 0, a předá tyto hodnoty funkci PRŮMĚR.

Výpočet počtu rozdílů mezi dvěma oblastmi buněk

Tento maticový vzorec porovná hodnoty dvou oblastí buněk s názvy Data1 a Data2 a vrátí počet rozdílů mezi nimi. Je-li obsah obou oblastí shodný, vrátí vzorec hodnotu 0. Tento vzorec je možné použít jen v případě, že obě oblasti buněk mají stejnou velikost a stejné rozměry:

= SUMA( KDYŽ( Data1 =Data2;0;1))

Vzorec vytvoří novou matici stejné velikosti jako oblasti, které porovnáváte. Funkce KDYŽ vyplní matici hodnotami 0 a 1 (0 pro neshodu a 1 pro stejné buňky). Funkce SUMA pak vrátí součet hodnot v matici.

Vzorec můžete zjednodušit takto:

= SUMA( 1*( Data1 <> Data2 ))

Tento vzorec – podobně jako vzorec, který počítá chybové hodnoty v oblasti – funguje díky tomu, že PRAVDA*1=1 a NEPRAVDA*1=0.

Nalezení umístění maximální hodnoty v oblasti

Tento maticový vzorec vrátí číslo řádku maximální hodnoty v jednosloupcové oblasti s názvem Data:

= MIN( KDYŽ(Data=MAX(Data);ŘÁDEK(Data);""))

Funkce KDYŽ vytvoří novou matici, která odpovídá oblasti Data. Pokud odpovídající buňka obsahuje maximální hodnotu v oblasti, matice obsahuje číslo řádku. V opačném případě obsahuje matice prázdný řetězec ( ""). Funkce MIN použije novou matici jako svůj druhý argument a vrátí nejmenší hodnotu, která odpovídá číslu řádku s maximální hodnotu v oblasti Data. Pokud oblast DATA obsahuje několik shodných maximálních hodnot, vrátí vzorec číslo řádku první hodnoty.

Chcete-li, aby vzorec vracel skutečnou adresu buňky s maximální hodnotu, použijte tento vzorec:

= ODKAZ( MIN(KDYŽ(Data=MAX(Data);ŘÁDEK(Data);""));SLOUPEC(Data))

Začátek stránky

Poznámka : Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Byly tyto informace užitečné?

Výborně! Je ještě něco dalšího, co byste nám chtěli dát vědět?

Jak bychom ho mohli vylepšit?

Děkujeme vám za zpětnou vazbu.

×