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.

Maticový vzorec může provádět více výpočtů s jednou nebo více položkami v matici. Matici je možné považovat za řádek nebo sloupec hodnot nebo kombinaci řádků a sloupců hodnot. Maticové vzorce mohou vracet více výsledků nebo jeden výsledek.

Počínaje září 2018 aktualizace pro Office 365se každý vzorec, který vrátí více výsledků, automaticky zabalí do sousedních buněk nebo do nich. Tato změna chování je také doprovázena několika novými dynamickými poli funkcí. Dynamické maticové vzorce, bez ohledu na to, jestli používají existující funkce nebo dynamické skupinové funkce, musí být zadané jenom do jedné buňky a potvrzené stisknutím klávesy ENTER. Starší starší maticové vzorce vyžadují nejdříve výběr celé výstupní oblasti a potvrzení vzorce pomocí CTRL + SHIFT + ENTER. Obvykle se označují jako vzorce CSE .

Pomocí maticových vzorců můžete provádět složité úkoly, například:

  • Rychle vytvářejte ukázkové datové sady.

  • Spočítá počet znaků v oblasti buněk.

  • Sečtěte jenom čísla, která splňují určité podmínky, třeba nejnižší hodnoty v oblasti nebo čísla, která spadají mezi horní a dolní hranici.

  • Sečte každou n-tý hodnotu v oblasti hodnot.

Následující příklady ukazují, jak vytvořit maticové a jednořádkové vzorce. Pokud je to možné, zahrnuli jsme do některých příkladů funkce dynamických polí a existující maticové vzorce zadané jako dynamická i starší pole.

Stažení příkladů

Stáhněte si ukázkový sešit se všemi příklady maticových vzorců v tomto článku.

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

    Funkce Array pro více buněk v buňce H10 = F10: F19 * G10: G19 pro výpočet počtu automobilů prodaných jednotkovou cenou

  • Zde počítáme celkové prodeje kupé a sedanů pro každého prodejce zadáním hodnoty = F10: F19 * G10: G19 v buňce H10.

    Když stisknete ENTER, uvidíte výsledky, které jsou v H10: h19. Všimněte si, že když vyberete libovolnou buňku v oblasti pro přesahující data, zvýrazní se oblast pro přesahy ohraničením. Můžete také všimnout, že vzorce v buňkách H10: H19 jsou šedé. Jsou tu jenom pro referenci, takže pokud chcete vzorec upravit, musíte vybrat buňku H10, kde je hlavní vzorec.

  • Maticový vzorec pro jednu buňku

    Maticový vzorec pro výpočet celkového součtu pomocí = suma (F10: F19 * G10: G19)

    Do buňky v ukázkovém sešitu H20 zadejte nebo zkopírujte vzorec = SUMA (F10: F19 * G10: G19)a stiskněte ENTER.

    V tomto případě Excel vynásobí hodnoty v poli (oblast buněk F10 až G19) a potom pomocí funkce SUMA sečte souhrny. 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 vzorec pro jednoduchou buňku v buňce H20 zcela nezávisle na vzorcích ve více buňkách (vzorec v buňkách H10 až H19). V tom spočívá další výhoda maticových vzorců – flexibilita. Ostatní vzorce ve sloupci H můžete změnit, aniž by to ovlivnilo vzorec v H20. Může být také dobrým zvykem mít v takovém případě nezávislé celkové součty, protože pomůže ověřit správnost vašich výsledků.

  • Dynamické maticové vzorce také poskytují tyto výhody:

    • Konzistence:    Když kliknete na některou z buněk H10 směrem dolů, uvidíte stejný vzorec. Tato konzistence pomůže zajistit větší přesnost.

    • Zabezpečení:    Nelze přepsat komponentu maticového vzorce pro více buněk. Například klikněte na buňku h11 a stiskněte Delete. Excel nemění výstup matice. Pokud ho chcete změnit, musíte vybrat levou horní buňku pole nebo H10 buňky.

    • Menší velikosti souborů    Místo několika mezilehlých vzorců můžete často použít jediný maticový vzorec. Například příklad prodejního automobilu používá jeden maticový vzorec pro výpočet výsledků ve sloupci E. Pokud jste použili standardní vzorce, například = F10 * G10, F11 * G11, F12 * G12 atd., použili jste k výpočtu stejných výsledků 11 různých vzorců. To není velké, ale co když máte tisíce řádků k sečtení? Pak může vést velký rozdíl.

    • Efektivita:    Pole funkcí může být efektivní způsob vytváření složitých vzorců. Maticový vzorec = SUMA (F10: F19 * G10: G19) je stejný jako: = SUMA (F10 * G10; F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15

    • Přetékají    Dynamické maticové vzorce se automaticky převrátí do výstupní oblasti. Pokud jsou zdrojová data v excelové tabulce, budou dynamické maticové vzorce při přidávání nebo odebírání dat automaticky měnit jejich velikost.

    • #SPILL! odoln    Dynamická pole zavádí chybu #SPILL!, což označuje, že z nějakého důvodu je požadovaný rozsah pro přesahující data blokován. Při řešení blokace se vzorec automaticky zapíše.

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} nebo = {"leden", "Únor", "březen"}

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ěrné pole, můžete položky v každém řádku omezit čárkami a každý řádek tak, aby středníkem.

Následující postupy vám umožní vyzkoušet si vytváření vodorovné, svislé a dvojrozměrné konstanty. Příklady použití funkce Sequence zobrazíme k automatickému generování maticových konstant a také ručním zadáváním maticových konstant.

  • Vytvoření vodorovné konstanty

    Použijte sešit z předchozích příkladů nebo vytvořte nový sešit. Vyberte libovolnou prázdnou buňku a zadejte = pořadí (1,5). Funkce SEQUENCe vytvoří 1 řádek a 5 pole. totéž jako = {1; 2; 3; 4; 5}. Zobrazí se následující výsledek:

    Vytvořte vodorovnou maticovou konstantu s = SEQUENCe (1; 5) nebo = {1; 2; 3; 4; 5}

  • Vytvoření svislé konstanty

    Vyberte prázdnou buňku s místností pod ní a zadejte = sekvence (5)nebo = {1; 2; 3; 4; 5}. Zobrazí se následující výsledek:

    Vytvořte svislou maticovou konstantu s = SEQUENCe (5) nebo = {1; 2; 3; 4; 5}

  • Vytvoření dvojrozměrné konstanty

    Vyberte prázdnou buňku s prostorem vpravo a pod ní a zadejte = sequences (3; 4). Zobrazí se následující výsledek.

    Vytvoření maticové konstanty 3 řádku a 4 s = SEQUENCe (3; 4)

    Můžete zadat taky: nebo = {1, 2, 3, 4; 5, 6, 7, 8; 9; 10, 11, 12}, ale budete chtít věnovat pozornost, kde zadáte středníky a čárky.

    Jak vidíte, volba POSLOUPNOSTi nabízí výrazné výhody před ručním zadáváním hodnot maticových konstant. Primárně vám ušetří čas, ale může taky pomoci omezit chyby před ručním zadáváním. Čtení je také snazší, obzvláště v případě, že je těžké odlišit čárky.

Tady je příklad, který používá maticové konstanty jako součást většího vzorce. V ukázkovém sešitu přejděte na konstantu v listu vzorců nebo vytvořte nový list.

V buňce D9 jsme zadali vzorec = sequences (1; 5; 3; 1), ale můžete také zadat 3, 4, 5, 6 a 7 do buněk \ 1: h9. O tomto výběru čísel jsme nic nepoužili, jsme pro odlišení vybrali něco jiného než 1-5.

V buňce E11 zadejte = SUMA (D9: h9 * Sequence (1; 5))nebo = SUMA (D9: h9 * {1; 2; 3; 4; 5}). Vzorce vrátí 85.

Použití maticových konstant ve vzorcích V tomto příkladu jsme použili = SUM (D9: H (* pořadí (1; 5))

Funkce SEQUENCe vytvoří ekvivalent maticové konstanty {1, 2, 3, 4, 5}. Vzhledem k tomu, že Excel provádí operace s výrazy uzavřenými v závorkách jako první, jsou hodnoty buněk v D9: h9 a operátor násobení (*). V tomto okamžiku vzorec vynásobí hodnoty v uložené matici odpovídajícími hodnotami v konstantě. Je to ekvivalentní vzorci:

= Sum (D9 * 1, E9 * 2, F9 * 3, G9 * 4, h9 * 5)nebo = SUMA (3 * 1, 4 * 3, 5 * 3, 6 * 4, 7 * 5)

Nakonec funkce SUMA sečte hodnoty a vrátí 85.

Abyste nepoužívali uložené pole a zachovali operaci v paměti, můžete ji nahradit jinou maticovou konstantou:

= SUMA (pořadí (1; 5; 3; 1) * pořadí (1; 5))nebo = SUMA ({3; 4; 5; 6; 7} * {1; 2; 3; 4; 5})

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

  • Maticové konstanty mohou obsahovat čísla, text, logické hodnoty (například pravda a NEPRAVDA) a chybové hodnoty jako #N/A. Můžete použít čísla v celých číslech, desítkových a matematických formátech. Pokud zahrnete text, musíte ho uvést do uvozovek ("text").

  • 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ů použití maticových konstant je jejich pojmenování. 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:

Přejděte na vzorce _GT_ definované názvy > definovat název. Do pole název zadejte Čtvrtletí1. Do pole Odkaz na zadejte následující konstantu (nezapomeňte ručně zadat složené závorky):

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

Dialogové okno by nyní mělo vypadat takto:

Přidání pojmenované maticové konstanty ze vzorců > definované názvy > > New

Klikněte na OK, vyberte řádek se třemi prázdnými buňkami a zadejte = Čtvrtletí1.

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

Ve vzorci použijte pojmenovanou maticovou konstantu like = Čtvrtletí1, kde Čtvrtletí1 byl definován jako = {"leden", "Únor", "březen"}

Pokud chcete, aby se výsledky ve svislém směru nevodorovně, můžete použít =transpozice(Čtvrtletí1).

Pokud chcete zobrazit seznam 12 měsíců, jako byste se mohli při vytváření finančního výkazu použít, můžete si odložit první rok pomocí funkce SEQUENCe. Úhledná věc o této funkci je, že i když se zobrazuje jenom měsíc, je za ní platné datum, které můžete použít v jiných výpočtech. Tyto příklady najdete v části list s názvem maticová konstanta a datové sady pro rychlé ukázky v sešitu.

= TEXT (datum (rok (dnes ()), pořadí (1; 12), 1), "MMM")

Vytvoření dynamického seznamu na 12 měsíců pomocí kombinace funkcí TEXT, datum, rok, dnes a POSLOUPNOSTi

Pomocí funkce Date můžete vytvořit datum založené na aktuálním roce, pořadí vytvoří maticovou konstantu od 1 do 12 pro leden až prosinec, funkce text převede formát zobrazení na "MMM" (leden, únor, březen atd.). Pokud chcete zobrazit celý název měsíce, třeba leden, použijte "MMMM".

Pokud použijete pojmenovaný konstantu jako maticový vzorec, nezapomeňte zadat rovnítko, jako je třeba = Čtvrtletí1, a ne jen Čtvrtletí1. Pokud tak neučiníte, aplikace Excel interpretuje matici jako textový řetězec a vzorec nebude fungovat očekávaným způsobem. Nakonec mějte na paměti, že můžete používat kombinace funkcí, textu a čísel. Vše závisí na tom, jak kreativní máte.

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é příklady používají funkci transpozice k převedení řádků na sloupce a naopak.

  • Více jednotlivých položek v matici

    ENTER = sekvence (1; 12) * 2nebo = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} * 2

    Můžete také rozdělit s (/), přidat s (+) a odečíst (-).

  • Druhá mocnina položek v matici

    ENTER = sekvence (1; 12) ^ 2nebo = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2

  • Vyhledání druhé odmocniny kvadratických položek v poli

    ENTER = odmocnina (pořadí (1; 12) ^ 2)nebo = odmocnina ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12} ^ 2)

  • Transpozice jednorozměrného řádku

    Zadejte = transpozice (pořadí (1, 5))nebo = transponovat ({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

    ENTER = transpozice (pořadí (5; 1))nebo = transponovat ({1; 2; 3; 4; 5})

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

  • Transpozice dvojrozměrné konstanty

    ENTER = transpozice (Sequence (3; 4))nebo = 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í matice z existujících hodnot

    Následující příklad vysvětluje, jak pomocí maticových vzorců vytvořit nové pole z existujícího pole.

    ENTER = sequences (3; 6; 10; 10)nebo = {10; 20; 30; 40; 50; 60; 70; 80; 90100110120; 130140150160170180}

    Než začnete psát 10 a} (pravá složená závorka 180), napište {(otevírací složená závorka), protože vytváříte pole čísel.

    Potom zadejte = D9 #nebo = D9: i11 do prázdné buňky. Zobrazí se pole buněk o rozměrech 3 x 6 se stejnými hodnotami v D9: D11. Symbol # se označuje jako nepřesahný operátor oblastia je Excel'sým způsobem, jak na něj odkazujete, aniž byste museli zadávat ho.

    Použití operátoru nepřesahujícího rozsahu (#) k odkazu na existující pole

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

    Výsledky maticového vzorce můžete převádět na jeho součásti. Vyberte buňku D9 a stisknutím klávesy F2 přepněte do režimu úprav. Potom stisknutím klávesy F9 převeďte odkazy na buňky na hodnoty, které Excel pak převede na maticovou konstantu. Po stisknutí klávesy ENTERby měl být vzorec, = D9 #, nyní = {10; 20; 30; 40, 50, 60; 70; 80; 90}.

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

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

    Spočítejte celkový počet znaků v oblasti a další matice pro práci s textovými řetězci

    = SUMA (DÉLKA (C9: C13))

    V tomto případě vrátí funkce len délku každého textového řetězce v každé z buněk v oblasti. Funkce SUMA pak tyto hodnoty sečte a zobrazí výsledek (66). Pokud chcete získat průměrný počet znaků, použijte:

    = AVERAGE (DÉLKA (C9: C13))

  • Obsah nejdelší buňky v oblasti C9: C13

    = INDEX (C9: C13 (MAX (DÉLKA (C9: C13)); LEN (C9: C13); 0); 1)

    Tento vzorec funguje jedině v případě, že oblast dat obsahuje jeden sloupec buněk.

    Podívejme se na vzorec, který začne od vnitřních prvků a pracujete s ním. Funkce Délka vrátí délku jednotlivých položek v oblasti buněk D2: D6. Funkce Max vypočítá největší hodnotu mezi položkami, které odpovídají nejdelšímu textovému řetězci, který je v buňce D3.

    Tady to začíná být trochu složitější. Funkce POZVYHLEDAT vypočítá posun (relativní pozici) buňky, která obsahuje 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 (C9: C13)

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

    LEN (C9: C13)

    Argument typ shody v tomto případě je 0. Typ shody může být hodnota 1, 0 nebo-1.

    • 1 – vrátí nejvyšší hodnotu, která je menší nebo rovna hodnotě Lookup.

    • 0 – vrátí první hodnotu přesně shodnou se vyhledávací hodnotou.

    • -1-vrátí nejmenší hodnotu, která je větší nebo rovna zadané hodnotě vyhledávání.

    • Jestliže typ shody vynecháte, aplikace Excel předpokládá hodnotu 1.

    Funkce index pak vezme tyto argumenty: matice a číslo řádku a sloupce v dané matici. Oblast buněk C9: C13 poskytuje matici adresu buňky a poslední argument (1) určuje, že hodnota pochází z prvního sloupce v matici.

    Pokud jste chtěli získat obsah nejmenšího textového řetězce, nahradili jste v příkladu maximální hodnotu min.

  • Nalezení n nejmenších hodnot v oblasti

    Tento příklad ukazuje, jak najít tři nejmenší hodnoty v oblasti buněk, kde je matice ukázkových dat v buňkách B9: B18has vytvořeno takto: = int (RANDARRAY(10; 1) * 100). RANDARRAY je nestálá funkce, takže při každém výpočtu v Excelu získáte novou sadu náhodných čísel.

    Maticový vzorec Excelu pro nalezení nejnižší hodnoty n: = SMALL (B9 #; SEQUENCe (D9))

    ENTER = Small (B9 #; Sequence (D9); = Small (B9: B18; {1; 2; 3})

    Tento vzorec používá maticovou konstantu k vyhodnocení malé funkce a k tomu, aby vrátila nejmenší 3 členy v matici, která je obsažená v buňkách B9: B18, kde 3 je proměnná hodnota v buňce D9. Pokud chcete zjistit víc hodnot, můžete hodnotu v pořadí zvětšit nebo do ní přidat další argumenty. Pomocí tohoto vzorce můžete také použít další funkce, například SUMA nebo průměr. Příklady:

    = SUMA (SMALL (B9 #; ŘADA (D9))

    = AVERAGE (SMALL (B9 #; ŘADA (D9))

  • Nalezení n nejvyšších hodnot v oblasti

    Pokud chcete najít největší hodnoty v oblasti, můžete nahradit malou funkci velkoufunkcí. V následujícím příkladu jsou navíc použity funkce ŘÁDEK a NEPŘÍMÝ.ODKAZ.

    ENTER = large (B9 #; řádek (nepřímý ("1:3")))nebo = Velká (B9: B18, řádek ("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 například prázdné a zadejte:

    =ŘÁDEK(1:10)

    Vzorec vytvoří sloupec deseti souvislých celých čísel. Pokud chcete zobrazit potenciální problém, vložte řádek nad oblast, která obsahuje maticový vzorec (to znamená nad řádkem 1). Excel upraví odkazy na řádky a vzorec nyní generuje celá čísla od 2 do 11. Tento problém můžete vyřešit tak, že do vzorce přidáte nepřímou funkci:

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

    Funkce nepřímý používá jako argumenty textové řetězce (proč je oblast 1:10 ohraničena uvozovkami). 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. Můžete jednoduše použít posloupnost:

    = SEQUENCE (10)

    Podívejme se na vzorec, který jste použili dřív (= Velká (B9 #, řádek ("1:3"))) – od vnitřních kulatých závorek a na vnější práci: funkce nepřímých vrátí sadu textových hodnot v tomto případě hodnoty 1 až 3. Funkce řádek zase vygeneruje pole sloupce se třemi buňkami. Funkce LARGe používá hodnoty v oblasti buněk B9: B18 a je vyhodnocena třikrát za každou z odkazů vrácených funkcí řádek. Pokud chcete najít další hodnoty, přidejte do nepřímé funkce větší oblast buněk. A konečně stejně jako v malých příkladech můžete tento vzorec použít s jinými funkcemi, jako je suma a průměr.

  • Součet oblasti obsahující chybové hodnoty

    Funkce SUMA v Excelu nefunguje, když se pokusíte sčítat oblast, která obsahuje chybovou hodnotu, třeba #VALUE! nebo #N/A. Tento příklad ukazuje, jak sčítat hodnoty v oblasti s názvem data, která obsahují chyby:

    Používejte pole k řešení chyb. Například = SUMA (když (je (data), ""; data) sečte oblast s názvem data, i když zahrnuje chyby, například #VALUE! nebo #NA!.

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

    Vzorec vytvoří nové pole obsahující původní hodnoty minus chybové hodnoty. Když začnete ze vnitřní funkce a pracujete mezi nimi, funkce ISERROR prohledá v oblasti buněk (data) chyby. Funkce když vrátí konkrétní hodnotu, pokud se zadaná podmínka vyhodnotí jako pravda, a jinou hodnotu, pokud se vyhodnotí jako FALSE. V tomto případě vrátí prázdné řetězce ("") pro všechny chybové hodnoty, protože se vyhodnotí na pravda, a vrátí zbývající hodnoty z oblasti (dat), 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 namísto jejich filtrová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.

Někdy je třeba sečíst hodnoty na základě určitých podmínek.

Pomocí polí můžete počítat na základě určitých podmínek. = SUMA (když (Sales>0; prodej)) bude sčítat všechny hodnoty větší než 0 v oblasti s názvem prodej.

Například tento maticový vzorec sečte pouze kladná celá čísla v oblasti s názvem prodej, která představuje buňky E9: E24 v předchozím příkladu:

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

Funkce když vytvoří pole hodnot kladné a NEPRAVDA. 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. Například tento maticový vzorec vypočítá hodnoty větší než 0 a menší než 2500:

= SUMA ((Sales>0) * (Sales<2500) * (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 sčítat hodnoty větší než 0 nebo menší než 2500:

= SUMA (když ((Sales>0) + (Sales<2500); prodej))

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. Jinak řečeno provádíte matematické operace, jako je sčítání nebo násobení hodnot, které splňují podmínku nebo nebo.

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.

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. Pokud chcete použít tento vzorec, musí mít oblasti buněk stejnou velikost a stejnou dimenzi. Pokud je třeba Data1 rozsah 3 řádků a 5 sloupců, musí být v Data2 také tři řádky a 5 sloupců:

=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 * (MyData<>YourData))

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

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

Podobné příklady najdete v ukázkovém sešitu na rozdílech mezi listem datových sad .

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 tabulku a vložte ji do buňky a1 na prázdném listu.

Prodej Osoba

Auto Type (typ )

Číslo Prodává

Unit (jednotka ) Cena

Celková hodnota 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. Pokud chcete zobrazit celkový prodej kupé a sedanů pro každého prodejce, vyberte buňky E2: E11, zadejte vzorec = C2: C11 * D2: D11a potom stiskněte kombinaci kláves CTRL + SHIFT + ENTER.

  2. Chcete-li zobrazit celkový součet všech prodejů, vyberte buňku F11, zadejte vzorec = SUMA (C2: C11 * D2: D11)a stiskněte kombinaci kláves CTRL + SHIFT + ENTER.

Po stisknutí kombinace kláves CTRL + SHIFT + ENTERbude vzorec ohraničený pomocí složených závorek ({}) a vloží do každé buňky vybrané oblasti instanci vzorce. 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

Do buňky D13 sešitu zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL + SHIFT + ENTER:

=SUMA(C2:C11*D2:D11)

V tomto případě Excel vynásobí hodnoty v poli (oblast buněk C2 až D11) a potom pomocí funkce SUMAsečte souhrny. 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 vzorec pro jednoduchou buňku v buňce D13 zcela nezávisle na vzorcích ve více buňkách (vzorec v buňkách E2 až E11). V tom spočívá další výhoda maticových vzorců – flexibilita. Vzorce ve sloupci E můžete změnit, aniž by to ovlivnilo vzorec v D13.

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. Například klikněte na buňku E3 a stiskněte 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 přidaná bezpečnostní opatření musíte stisknutím kombinace kláves CTRL + SHIFT + ENTER Potvrdit změnu vzorce.

  • Menší velikosti souborů    Místo několika mezilehlých vzorců můžete často použít jediný maticový vzorec. Sešit například používá jeden maticový vzorec pro výpočet výsledků ve sloupci E. Pokud jste použili standardní vzorce (například = C2 * D2, C3 * D3, C4 * D4...), měli byste použít 11 různých vzorců k výpočtu stejných výsledků.

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. Hlavním rozdílem je to, že když použijete maticový vzorec, stisknete kombinaci 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í.

Pole funkcí může být 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).

Důležité informace: Stiskněte kombinaci kláves CTRL + SHIFT + ENTER , kdykoli budete muset zadat maticový vzorec. 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 celou oblast vzorců (například E2: E11) a stiskněte klávesu Delete.

  • Prázdné buňky nemůžete vložit do buňky ani je z nich odstranit.

Někdy je třeba maticový vzorec rozbalit. Vyberte první buňku v existujícím maticovém rozsahu a pokračujte, dokud nevyberete celou oblast, na kterou chcete vzorec rozšířit. Stisknutím klávesy F2 upravte vzorec a po úpravě oblasti vzorců potvrďte vzorec stisknutím kombinace kláves CTRL + SHIFT + ENTER . Klávesou je výběr celé oblasti začínající horní buňkou v matici. Levá horní buňka je ta, která se upraví.

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

  • Občas můžete zapomenout 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é. Pokud tedy ostatní uživatelé potřebují změnit sešity, měli byste se vyhnout maticovým vzorům nebo zajistěte, aby tito lidé věděli o některých maticových vzorcích, a porozumět tomu, jak je 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}

Když teď vytvoříte maticové vzorce, stačí, když stisknete 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. Stisknutím kombinace kláves CTRL + SHIFT + ENTER zadáte celý vzorec.

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.

Tady je 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 poli dvou řádků je první řádek 1, 2, 3 a 4 a druhý řádek je 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. V řádku vzorců zadejte následující vzorec a potom stiskněte kombinaci kláves CTRL + SHIFT + ENTER:

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

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

    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. V řá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. 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

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.

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, že když do maticového vzorce přidáte konstantu, stisknete 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 chcete tento postup vyzkoušet, zkopírujte funkci, vyberte prázdnou buňku v sešitu, vložte vzorec do řádku vzorců a 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 jejich pojmenování. 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 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.

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 čárku nebo středník, nebo pokud nějakou hodnotu vložíte na nesprávné místo, nemusí být maticová konstanta vytvořena správně nebo se může zobrazit zpráva s upozorněním.

  • 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é příklady 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 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. Stiskněte kombinaci kláves CTRL + SHIFT + ENTER, která zadá toto pole čísel v oblasti buněk C8: E10 pomocí maticového vzorce. V listu by měla oblast C8 až E10 vypadat takto:

    10

    20

    končí

    40

    50

    60

    70

    80

    90

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

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

    =C8:E10

    V buňkách C1 až E3 se zobrazí matice s hodnotami stejných hodnot, které vidíte v C8 prostřednictvím E10.

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

  1. V buňkách C1: C3 přepněte stisknutím klávesy F2 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 měl být teď : = {10; 20; 30; 40; 50; 60; 70; 80; 90}.

  3. Stisknutím kombinace 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 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ších buněk a2: A6 (buňka a3).

V buňce A8 se používá následující vzorec: 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 pak tyto 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. Zadejte do buněk a1 některá náhodná čísla: A11.

  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 stiskněte kombinaci kláves CTRL + SHIFT + ENTER:

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

Tento vzorec používá maticovou konstantu k vyhodnocení malé funkce a k tomu, aby vrátila nejmenší (1), sekundu nejmenší (2), druhé nejmenšího (1), druhého nejmenšího (2) a třetího nejmenšího (3) člena, který je obsažen v buňkách a1: A10. zůstává. Pomocí tohoto vzorce můžete také použít další funkce, například SUMA nebo průměr. Příklady:

= SUMA (MALÉ (A1: A10; {1; 2; 3})

= PRŮMĚR (MALÉ (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. Do řádku vzorců zadejte tento 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 například prázdný sloupec 10 buněk v sešitě cvičení, zadejte tento maticový vzorec a stiskněte kombinaci kláves CTRL + SHIFT + ENTER:

=ŘÁDEK(1:10)

Vzorec vytvoří sloupec deseti souvislých celých čísel. Pokud chcete zobrazit potenciální problém, vložte řádek nad oblast, která obsahuje maticový vzorec (to znamená nad řádkem 1). Excel upraví odkazy na řádky a vzorec generuje celá čísla od 2 do 11. Tento problém můžete vyřešit tak, že do vzorce přidáte nepřímou funkci:

=ŘÁ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ívejme se na vzorec, který jste použili dřív, a to = Velká (A5: A14, řádek ("1:3"))) – od vnitřních kulatých závorek a na vnějším místě: funkce nepřímý vrátí sadu textových hodnot v tomto případě hodnoty 1 až 3. Funkce řádek zase vygeneruje sloupcovou matici se třemi buňkami. Funkce large používá hodnoty v oblasti buněk A5: A14 a je vyhodnocena třikrát za každou z odkazů vrácených funkcí řádek . Hodnoty 3200, 2700 a 2000 se vrátí do sloupcové matice tří buněk. Pokud chcete najít další hodnoty, přidejte do nepřímé funkce větší oblast buněk.

Stejně jako u dřívějších příkladů 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

Přejděte zpátky do příkladu předchozího 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 "svazek buněk".

Podívejme se na vzorec, který začne od vnitřních prvků a pracujete s ním. Funkce Délka vrátí délku jednotlivých položek v oblasti buněk a2: A6. Funkce Max vypočítá největší hodnotu mezi položkami, které odpovídají 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 (DÉLKA (A2: A6))

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

DÉLKA (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 poskytuje matici, funkce POZVYHLEDAT poskytne 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ří nové pole obsahující původní hodnoty minus chybové hodnoty. Když začnete ze vnitřní funkce a pracujete mezi nimi, funkce IsError prohledá v oblasti buněk (data) chyby. Funkce když vrátí konkrétní hodnotu, pokud se zadaná podmínka vyhodnotí jako pravda, a jinou hodnotu, pokud se vyhodnotí jako false. V tomto případě vrátí prázdné řetězce ("") pro všechny chybové hodnoty, protože se vyhodnotí na pravda, a vrátí zbývající hodnoty z oblasti (dat), 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 * (MyData<>YourData))

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

Byla přijata odpověď

Části tohoto článku byly založené na řadě uživatelských sloupců v Excelu napsaných Petr Karásek a přizpůsobená z kapitol 14 a 15 Excel 2002 vzorců, což je kniha, kterou napsal Petr Walkenbach, bývalý MVP pro Excel.

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 také

Dynamické matice a chování přesahujících matic

Dynamické maticové vzorce a starší maticové vzorce CSE

Funkce FILTER

Funkce RANDARRAY

Funkce SEQUENCE

Funkce SINGLE

Funkce SORT

Funkce SORTBY

Funkce UNIQUE

Chyby #PRESAH! v Excelu

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.

×