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

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

Poznámka:  Snažíme se pro vás co nejrychleji zajistit aktuální obsah nápovědy ve vašem jazyce. Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát ve spodní části této stránky vědět, jestli vám informace v článku pomohly? Pokud byste se rádi podívali na jeho anglickou verzi, najdete ji tady .

Osvobozením od uživatel power aplikace Excel, je potřeba vědět použití maticové vzorce, které mohou provádět výpočty, které nemůžete dělat pomocí jiných maticových vzorců. V následujícím článku vychází z na řadu sloupců Excelu Power User napsal Petr Karásek a upravený z kapitol 14 a 15 Vzorce 2002 aplikace Excel, knize napsal Jan Walkenbach, MVP pro aplikaci Excel.

Informace o maticových vzorcích

Maticové vzorce jsou někdy označovány jako vzorce CSE (CTRL+SHIFT+ENTER), protože se do sešitů místo klávesy ENTER zadávají stisknutím kombinace kláves CTRL+SHIFT+ENTER.

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

Pokud máte zkušenosti s používáním vzorců v aplikaci Excel, můžete provádět 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. Pomocí maticových vzorců můžete provádět 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

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

Maticový vzorec je vzorec, který může provádět více výpočtů s jednou nebo více položkami v matici. Matici si můžete představit jako řádek hodnot, sloupec hodnot nebo kombinaci řádků a sloupců hodnot. Maticové vzorce můžou vracet buď víc výsledků, nebo jenom jediný výsledek. Maticový vzorec například můžete umístit do oblasti buněk a použít ho pro výpočet sloupce nebo řádku souhrnů. Můžete ho taky umístit do jediné buňky a 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, který se nachází v jediné buňce, se nazývá 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.

Vícebuňkový maticový vzorec

Toto je sešit vložený do prohlížeče. Přestože obsahuje ukázková data, nelze ve vloženém sešitu vytvářet ani měnit maticové vzorce – k tomu potřebujete aplikaci Excel. Vložený sešit obsahuje odpovědi a objasňuje, jak maticový vzorec funguje. Pokud však chcete ocenit všechny výhody maticových vzorců, je potřeba sešit zobrazit v aplikaci Excel.

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

  1. Zkopírujte celou následující tabulku a vložte ji do buňky A1 v prázdném listu v Excelu.

    Prodejce

    Typ
    auta

    Počet
    prodaných kusů

    Jednotková
    cena

    Celkový
    prodej

    Klčo

    Sedan

    5

    33000

    Kupé

    4

    37000

    Pokorný

    Sedan

    6

    24000

    Kupé

    8

    21000

    Jelínek

    Sedan

    3

    29000

    Kupé

    1

    31000

    Veselý

    Sedan

    9

    24000

    Kupé

    5

    37000

    Svoboda

    Sedan

    6

    33000

    Kupé

    8

    31000

    Vzorec (celkový součet)

    Celkový součet

    '=SUMA(C2:C11*D2:D11)

    =SUMA(C2:C11*D2:D11)

  2. Abyste zjistili celkový prodej kupé a sedanů pro každého prodejce, vyberte oblast buněk E2:E11, zadejte vzorec =C2:C11*D2:D11 a pak stiskněte kombinaci kláves Ctrl+Shift+Enter.

  3. Celkový součet všech prodejů získáte tak, že vyberete buňku F11, zadáte vzorec =SUMA(C2:C11*D2:D11) a pak stisknete kombinaci kláves Ctrl+Shift+Enter.

Tento sešit můžete stáhnout tak, že kliknete na zelené tlačítko aplikace Excel, které je umístěno na černém pruhu v dolní části sešitu. Potom můžete soubor otevřít v aplikaci Excel, vybrat buňky obsahující maticové vzorce a stisknutím kombinace kláves CTRL+SHIFT+ENTER vzorec aktivovat.

Pokud pracujete současně s aplikací Excel, přesvědčte se, zda byl aktivován List1, a potom vyberte buňky E2:E11. Stiskněte klávesu F2 a zadejte do aktuální buňky E2 vzorec =C2:C11*D2:D11. Stisknete-li klávesu ENTER, bude vzorec vložen pouze do buňky E2 a zobrazí se výsledek 165000. Namísto klávesy ENTER zkuste po zadání vzorce stisknout kombinaci kláves Ctrl+Shift+Enter. Výsledky se nyní zobrazí v buňce E2:E11. Všimněte si, že na řádku vzorců je vzorec zobrazen jako {=C2:C11*D2:D11}. Tento formát označuje maticový vzorec, jak ukazuje následující tabulka.

Pokud stisknete kombinaci kláves Ctrl+Shift+Enter, Excel uzavře vzorec do složených závorek ({ }) a umístí jednu instanci vzorce do každé buňky vybrané oblasti. To se stane velmi rychle, takže ve sloupci E uvidíte částky celkových prodejů pro každý typ auta pro jednotlivé prodejce. Pokud vyberete buňky E2 a pak E3, E4 atd., zobrazí se stejný vzorec: {=C2:C11*D2:D11}.

Celkové částky ve sloupci E jsou vypočítány pomocí maticového vzorce

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

Zadejte v sešitu do buňky F10 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á částka prodejů 1 590 000 Kč. Tento příklad ukazuje, jak výkonný může tento typ vzorce být. Předpokládejme například, že máte 1 000 řádků dat. Všechna data nebo část z nich můžete místo přetažením vzorce směrem dolů přes všech 1 000 řádků sečíst vytvořením maticového vzorce v jediné buňce.

Všimněte si také, že jednobuňkový vzorec (v buňce G11) je zcela nezávislý na vícebuňkovém vzorci (vzorec v buňkách E2 až E11). V tom spočívá další výhoda maticových vzorců – flexibilita. Můžete změnit vzorce ve sloupci E nebo tento sloupec zcela odstranit, aniž by to ovlivnilo vzorec v buňce G11.

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. Jako další bezpečnostní opatření je nutné potvrdit změnu 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 například používá k výpočtu výsledků ve sloupci E jediný maticový vzorec. Při použití standardních vzorců (=C2*D2;C3*D3;C4*D4…), byste pro výpočet stejných výsledků použili 11 různých vzorců.

Syntaxe maticových vzorců

Pro maticové vzorce se obecně používá standardní syntaxe vzorce. Všechny začínají symbolem rovná se (=) a můžete v nich použít většinu z předdefinovaných funkcí aplikace Excel. Základní rozdíl při použití maticového vzorce spočívá v tom, že vzorec zadáte stisknutím kombinace kláves CTRL+SHIFT+ENTER. Když toto provedete, aplikace Excel uzavře maticový vzorec do složených závorek – pokud ale složené závorky zadáte ručně, vzorec bude převeden na textový řetězec a nebude funkční.

Maticové vzorce představují velmi efektivní způsob vytváření složitých vzorců. Maticový vzorec =SUMA(C2:C11*D2:D11) odpovídá vzorci: =SUMA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Vkládání a změny maticových vzorců

Důležité:    Kdykoli budete potřebovat zadat nebo upravit maticový vzorec, stiskněte kombinaci 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 také třeba dodržovat toto pravidlo:

  • Dříve než zadáte vzorec, je třeba vybrat oblast buněk pro ukládání výsledků. Udělali jste to při vytváření vícebuňkového maticového vzorce výběrem buněk E2 až E11.

  • Není možné změnit obsah jednotlivé buňky v maticovém vzorci. Chcete-li to vyzkoušet, vyberte v sešitu buňku E3 a stiskněte klávesu DELETE. V aplikaci Excel se zobrazí zpráva s upozorněním, že není možné změnit část matice.

  • Můžete přesunout nebo odstranit celý maticový vzorec, avšak nelze přesunout nebo odstranit jeho část. Jinými slovy: Chcete-li zmenšit maticový vzorec, je třeba nejprve odstranit existující vzorec a potom začít znovu.

  • 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 je třeba maticový vzorec rozbalit. Tento postup není složitý, je však nutné pamatovat na pravidla uvedená v předchozí části.

V tomhle listu jsme přidali další řádky údajů o prodeji – řádky 12 až 17. Teď chceme maticové vzorce aktualizovat tak, aby zahrnovaly tyto další řádky.

Udělejte to v desktopové aplikaci Excel (po stažení sešitu do počítače).

Rozšíření maticového vzorce

  1. Zkopírujte celou tuto tabulku do buňky A1 na excelovém listu.

    Prodejce

    Typ
    auta

    Počet
    prodaných kusů

    Jednotková
    cena

    Celkový
    prodej

    Klčo

    Sedan

    5

    33000

    165000

    Kupé

    4

    37000

    148000

    Pokorný

    Sedan

    6

    24000

    144000

    Kupé

    8

    21000

    168000

    Jelínek

    Sedan

    3

    29000

    87000

    Kupé

    1

    31000

    31000

    Veselý

    Sedan

    9

    24000

    216000

    Kupé

    5

    37000

    185000

    Svoboda

    Sedan

    6

    33000

    198000

    Kupé

    8

    31000

    248000

    Tálský

    Sedan

    2

    27000

    Kupé

    3

    30000

    Vozdecká

    Sedan

    4

    22000

    Kupé

    1

    41000

    Junk

    Sedan

    5

    32000

    Kupé

    3

    36000

    Celkový součet

  2. Vyberte buňku E18, do buňky A20 zadejte vzorec pro Celkový součet =SUMA(C2:C17*D2:D17) a stiskněte kombinaci kláves Ctrl+Shift+Enter.
    Výsledek by měl být 2 131 000.

  3. 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.

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

  5. Na řádku vzorců změňte C11 na C17 a D11 na D17 a potom stiskněte kombinaci kláves Ctrl+Shift+Enter.
    Excel zaktualizuje vzorce v buňkách E2 až E11 a umístí instanci vzorce do nových buněk E12 až E17.

  6. Do buňky F17 napište maticový vzorec =SUMA(C2:C17*D2*D17), který odkazuje na buňky v řádcích 2 až 17, a stisknutím kombinace kláves Ctrl+Shift+Enter ho vložte jako maticový vzorec.
    Nový celkový součet by měl být 2 131 000.

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

Maticové vzorce jsou velmi užitečné, mají však také některé nevýhody:

  • Může se stát, že zapomenete stisknout kombinaci kláves CTRL+SHIFT+ENTER. To se může přihodit i nejzkušenějším uživatelům aplikace Excel. Nezapomeňte tuto kombinaci stisknout vždy při zadávání nebo úpravě maticového vzorce.

  • Maticovým vzorcům nemusí rozumět ostatní uživatelé sešitu. V praxi bývají maticové vzorce na listu obvykle nezdokumentované. Takže pokud vaše sešity potřebují upravovat další uživatelé, měli byste se maticovým vzorcům raději vyhnout nebo zajistit, aby tito uživatelé věděli, jak je v případě potřeby měnit.

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

Začátek stránky

Informace o maticových konstantách

Maticové konstanty jsou součástí maticových vzorců. Maticové konstanty vytvoříte zadáním seznamu položek a jeho ručním uzavřením do složených závorek ( { } ), například:

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

Teď už víte, že při vytváření maticových vzorců je nutné stisknout kombinaci kláves Ctrl+Shift+Enter. 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 svislicemi, vytvoříte vodorovnou matici (řádek). Pokud položky oddělujete středníky, vytvoříte svislou matici (sloupec). Pokud chcete vytvořit dvourozměrnou matici, oddělujete položky v jednotlivých řádcích svislicemi a jednotlivé řádky oddělujete středníky.

Toto je příklad matice v jednom řádku: {1|2|3|4}. Toto je příklad matice v jednom sloupci: {1;2;3;4}. A toto je příklad matice se dvěma řádky a čtyřmi sloupci: {1|2|3|4;5|6|7|8}. V matici o dvou řádcích je první řádek tvořen čísly 1, 2, 3, 4 a druhý řádek čísly 5, 6, 7 a 8. Oba řádky jsou odděleny středníkem, který je umístěn mezi čísly 4 a 5.

Podobně jako u maticových vzorců je možné maticové konstanty používat s většinou předdefinovaných funkcí 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.

Začátek stránky

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

Následující postupy 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ích příkladů nebo vytvořte nový sešit.

  2. Vyberte buňky A1 až E1.

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

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

    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

Vytvoření svislé konstanty

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

  2. Na řádku vzorců zadejte následující vzorec a potom 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. Na řádku vzorců zadejte následující vzorec a 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

Toto je jednoduchý příklad použití konstanty:

  1. Vytvořte ve stejném sešitu nový list.

  2. Do buňky A1 zadejte hodnotu 3 a potom do buňky B1 zadejte hodnotu 4, do buňky C1 hodnotu 5, do buňky D1 hodnotu 6 a do buňky E1 hodnotu 7.

  3. 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.

Další část vysvětluje, jak vzorec funguje.

Syntaxe 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, zadáváte je 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})

Pokud to chcete vyzkoušet, zkopírujte funkci, vyberte prázdnou buňku v sešitu, 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 se použil 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 vědecké formáty. Vložíte-li text, je nutné jej ohraničit uvozovkami (").

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

Pojmenování maticových konstant

Jedním z nejlepších způsobů, jak používat maticové konstanty, je pojmenovat je. Pojmenované konstanty se dají jednodušeji používat a dají se pomocí nich skrýt některé složitosti vašich maticových vzorců, před ostatními. Pokud chcete maticovou konstantu pojmenovat a použít ji ve vzorci, udělejte tyto kroky:

  1. Na kartě Vzorce klikněte ve skupině Definované názvy na tlačítko 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 pak vypadá takto:

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

  4. Klikněte na tlačítko OK a potom vyberte řádek se třemi prázdnými buňkami.

  5. 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žijete jako maticový vzorec, nezapomeňte zadat znaménko rovná se. Pokud tak neučiníte, aplikace Excel interpretuje matici jako textový řetězec a vzorec nebude fungovat očekávaným způsobem. Mějte také na paměti, že můžete použít 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 svislici nebo středník, nebo pokud je umístíte na nesprávném místě, maticová konstanta nemusí být vytvořena správně nebo se může zobrazit upozorňující zpráva.

  • Je možné, že jste vybrali oblast buněk, která neodpovídá počtu prvků v konstantě. Vyberete-li například sloupec šesti buněk pro použití s 5buňkovou konstantou, 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. Vytvořte nový list a pak 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

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

  2. 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ů.

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 listu v Excelu vyberte buňky C8:E10 a zadejte tenhle vzorec:

    ={10;20;30|40;50;60|70;80;90}

    Před číslem 10 zadejte { (levou složenou závorku) a po číslu 90 zadejte } (pravou složenou závorku), protože vytváříte matici čísel.

  2. Potom stiskněte kombinaci kláves Ctrl+Shift+Enter – tím tuto číselnou matici vložíte do oblasti buněk C8:E10 jako maticovou konstantu.
    V listu by měla oblast C8 až E10 vypadat takto:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

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

    =C8:E10

    V buňkách C1 až E3 se objeví matice 3×3 se stejnými hodnotami, jaké jste viděli v buňkách C8 až E10.

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

  1. Vyberte buňky C1:C3 a stisknutím klávesy F2 přepněte do režimu úprav.
    Maticový vzorec by měl být stále =C8:E10.

  2. Stisknutím klávesy F9 převeďte odkazy na buňky na hodnoty. Excel převede hodnoty na maticovou konstantu. Vzorec by teď měl být ={10;20;30|40;50;60|70;80;90}, což odpovídá obsahu buněk C8:E10.

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

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.

  1. Zkopírujte celou tuhle tabulku a vložte jí do listu do místa buňky A1.

  2. Vyberte buňku A9 a pak stisknutím kombinace kláves Ctrl+Shift+Enter zobrazte celkový počet znaků v buňkách A2:A6 (66).

  3. Vyberte buňku A12 a pak stisknutím kombinace kláves Ctrl+Shift+Enter zobrazte obsah nejdelší z buněk A2:A6 (buňky A3).

Data

Tohle je

skupina buněk, které

společně

tvoří

jednu větu.

Celkový počet znaků v buňkách A2:A6

=SUMA(DÉLKA(A2:A6))

Obsah nejdelší buňky (A3)

=INDEX(A2:A6;POZVYHLEDAT(MAX(DÉLKA(A2:A6));DÉLKA(A2:A6);0);1)

Následující vzorec použitý v buňce A9 spočítá celkový počet znaků (52) v buňkách A2 až A6.

=SUMA(DÉLKA(A2:A6))

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

Nalezení n nejmenších hodnot v oblasti

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

  1. Vyberte buňky A16 až A18.
    V této množině buněk se budou uchovávat výsledky vrácené maticovým vzorcem.

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

    =SMALL(A5:A14;{1|2|3})

V buňkách A16 až A18 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(A 5 :A1 4 ,{1;2;3}))

=PRŮMĚR(SMALL(A 5 :A1 4 ,{1;2;3}))

Nalezení n největší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. Vyberte buňky A1 až A3.

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

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

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

V tomto okamžiku je užitečné se něco dovědět o funkcích ŘÁDEK a NEPŘÍMÝ.ODKAZ. Pomocí funkce ŘÁDEK lze 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 A5:A14 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 použili dříve: =LARGE(A5:A14;ŘÁ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 A5:A14 a je vyhodnocena 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 najít 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

Tento vzorec funguje jedině v případě, že oblast dat obsahuje jeden sloupec buněk. Zadejte na listu List3 do buňky A16 následující vzorec a stiskněte kombinaci kláves Ctrl+Shift+Enter:

=INDEX(A6:A9;POZVYHLEDAT(MAX(DÉLKA(A6:A9));DÉLKA(A6:A9);0);1)

Text „skupina buněk, které“ je zobrazen v buňce A16.

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 A6:A9. Funkce MAX vypočítá největší hodnotu z těchto položek, což odpovídá nejdelšímu textovému řetězci, který je v buňce A7.

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( A6 : A9 ))

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

DÉLKA( A6:A9 )

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.

Funkce INDEX má potom tyto argumenty: matici a číslo řádku a sloupce v rámci matice. Oblast buněk A6:A9 představuje matici, funkce POZVYHLEDAT poskytuje adresu buňky a poslední argument (1) určuje, že hodnota pochází z prvního sloupce v matici.

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, které obsahují chyby, a hodnotu 0 pro buňky, které neobsahují chyby. Vzorec můžete zjednodušit a dosáhnout stejného výsledku 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.

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ý, vzorec vrátí hodnotu 0. Chcete-li použít tento vzorec, musí mít oblasti buněk stejnou velikost a stejné rozměry (pokud oblast Data1 obsahuje 3 řádky o 5 sloupcích, musí mít oblast Data2 také 3 řádky o 5 sloupcích):

=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, bude matice obsahovat číslo řádku. V opačném případě bude matice obsahovat 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í hodnotou 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í hodnotou, použijte tento vzorec:

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

Začátek stránky

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Viz taky

Přehled vzorců

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

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

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×