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

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

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

Prodejce	Typ  auta	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)

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

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

Když stisknete kombinaci kláves CTRL+SHIFT+ENTER, Excel vzorec obklopí složenou závorkou ({ }) a vloží 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}. 

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

Zadejte v sešitu do buňky D13 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ů. 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 D13 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 D13.

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

• Konzistence (Consistency)    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í musíte stisknout kombinaci kláves CTRL+SHIFT+ENTER, abyste potvrdili všechny změny vzorce.

• Menší velikosti souborů    Místo několika intermediárních vzorců můžete často použít jeden 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ů (např. =C2*D2;C3*D3;C4*D4…), byste pro získání stejných výsledků museli použít 11 různý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é funkce můžou být efektivním způsobem tvorby 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).

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.

• Pokud chcete odstranit maticový vzorec, vyberte celou oblast vzorce (například E2:E11) a stiskněte tlačítko DELETE.

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

Někdy je třeba maticový vzorec rozbalit. Vyberte první buňku v existující oblasti matice a pokračujte, dokud nevyberete celou oblast, na kterou chcete vzorec rozšířit. Stiskněte klávesu F2 a upravte vzorec a po úpravě vzorce stisknutím kombinace kláves CTRL+SHIFT+ENTER potvrďte vzorec. Klíčem je vybrat celou oblast počínaje levou horní buňkou matice. Levá horní buňka je ta, která se upraví..

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.

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.

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

Vytvoření vodorovné konstanty

1. Na prázdném listu vyberte buňky A1 až E1.

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

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

   V takovém případě byste měli zadat levou a pravou složenou závorku ({ }) a Excel za vás přidá druhou sadu.

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

   

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.

   

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

   

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.

   

   V buňce A3 se zobrazí hodnota 85.

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

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

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})

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.

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:

   

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.

   

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.

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ísto, maticová konstanta se nemusí vytvořit správně nebo se může zobrazit zpráva upozornění.

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

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

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é vidíte 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. 

2. Stisknutím klávesy F9 převeďte odkazy na buňky na hodnoty. Excel hodnoty převede na maticovou konstantu. Vzorec by teď měl být ={10\20\30;40\50\60;70\80\90}.

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.

   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)

2. Vyberte buňku A8 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 A10 a pak stisknutím kombinace kláves CTRL+SHIFT+ENTER zobrazte obsah nejdelší z buněk A2:A6 (buňka A3).

Následující vzorec použitý v buňce A8 spočítá celkový počet znaků (66) 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 zobrazí výsledek (66).

Nalezení n nejmenších hodnot v oblasti

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

1. Do buněk A1:A11 zadejte náhodná čísla.

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

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

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

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ší argument. S tímto vzorcem můžete také použít další funkce, například SUMA nebo PRŮMĚR. Příklady:

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

=PRŮMĚR(SMALL(A1:A10;{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 D1 až D3.

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

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

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 třeba prázdný sloupec s 10 buňkami v sešitu cvičení, zadejte tento maticový vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

=ŘÁDEK(1:10)

Vzorec vytvoří sloupec s 10 po sobě jdoucími celými čísly. Pokud chcete zobrazit potenciální problém, vložte řádek nad oblast obsahující maticový vzorec (to znamená nad řádek 1). Excel upraví odkazy na řádky a vzorec vygeneruje celá čísla od 2 do 11. Tento problém vyřešíte tak, že do vzorce přidáte 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.

Podíváme se na vzorec, který jste použili dříve – =LARGE(A5:A14;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3"))) – od vnitřních závorek směrem ven: Funkce NEPŘÍMÝ ODKAZ vrátí sadu textových hodnot, v tomto případě hodnoty 1 až 3. Funkce ŘÁDEK zase vygeneruje pole sloupců se třemi buňkami. Funkce LARGE používá hodnoty v oblasti buněk A5:A14 a vyhodnocuje se třikrát, jednou pro každý odkaz vrácený funkcí ŘÁDEK. Hodnoty 3200, 2700 a 2000 se vrátí do sloupcového pole se třemi buňkami. Pokud chcete najít další hodnoty, přidejte do funkce NEPŘÍMÝ.ODKAZ větší oblast buněk.

Stejně jako předchozí vzorce, i tento vzorec můžete použít s jinými funkcemi, například SUMA a PRŮMĚR.

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

Vraťte se k předchozímu příkladu textového řetězce, zadejte do prázdné buňky následující vzorec a stiskněte kombinaci kláves CTRL+SHIFT+ENTER:

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

Zobrazí se text „skupina buněk, které“.

Na vzorec se podíváme podrobněji, začneme od vnitřních prvků a budeme pokračovat směrem ven. Funkce DÉLKA vrátí délku každé položky v oblasti buněk A2:A6. Funkce MAX vypočítá nejvyšší hodnotu z těchto položek, která odpovídá nejdelšímu textovému řetězci, který je v buňce A3.

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

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

LEN(A2:A6)

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 A2:A6 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.

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, která obsahuje původní hodnoty minus všechny chybové hodnoty. Počínaje vnitřními funkcemi směrem ven hledá funkce JE.CHYBHODN chyby v oblasti buněk (Data). Funkce KDYŽ vrátí konkrétní hodnotu, pokud je zadaná podmínka vyhodnocena jako PRAVDA, a jinou hodnotu, pokud je zadaná podmínka vyhodnocena jako NEPRAVDA. V tomto případě vrátí prázdné řetězce ("") pro všechny chybové hodnoty, protože se vyhodnotí jako PRAVDA, a vrátí zbývající hodnoty z rozsahu (Data), protože se vyhodnotí jako NEPRAVDA, což znamená, že neobsahují chybové hodnoty. Funkce SUMA pak vypočítá součet pro filtrované pole.

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))