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 je vzorec, který lze provést několik výpočtů na jednu nebo více položek v matici. Si můžete představit matice jako řádek nebo sloupec hodnot nebo kombinace řádků a sloupců hodnot. Maticové vzorce můžete vrátit výsledkem je více hodnot, nebo výsledkem je jedna hodnota.

Začínající září 2018 aktualizace pro Office 365, všechny vzorce, které můžete vrátit výsledkem je více hodnot bude automaticky přepadového je buď směrem dolů, nebo do sousedních buněk. Tato změna chování připojen také několik nových funkcí dynamické pole. Dynamické maticové vzorce, ať půjde existující funkce nebo dynamické maticových funkcí, stačí se při zadávání do jedné buňky a potom stisknutím klávesy Enterpole Potvrzeno. Starší, starší verze maticové vzorce je nutné nejprve výběr celého výstupní oblasti a poté potvrzení vzorec s funkcí Kombinaci kláves Ctrl + Shift + Enter. Budou se označované jako vzorce CSE .

Maticové vzorce můžete provádět složité, jako například:

  • Rychlé vytvoření ukázkové datové sady.

  • Zjištění počtu znaků v oblasti buněk.

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

  • Součet všech n-Tých hodnot v oblasti hodnoty.

Následující příklady ukazují, jak vytvořit vícebuňkový a jednobuňkový maticový vzorec. Pokud je to možné, jsme zadali příklady některých dynamických maticových funkcí, jakož i existující maticové vzorce zadán jako matice dynamické a starší verze.

Stažení příkladů

Stáhnout ukázkový sešit s všechny maticové vzorce příklady 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 vícebuňkového maticového v buňce H10 = F10:F19 * G10:G19 Chcete-li vypočítat počet automobilů prodávaných Jednotková cena

  • Tady celkový prodej kupé a sedanů pro každého prodejce při výpočtu zadáním = F19:F19 * G10:G19 v buňce H10.

    Když stisknete Enter, uvidíte výsledky přepadového dolů H10:H19 buněk. Všimněte si, že oblast přepadového při je zvýrazněná s ohraničením vyberte libovolnou buňku v oblasti přepadového. Taky můžete narazit neaktivní vzorců v buňkách H10:H19. Stačí tam budou pro informaci, pokud chcete upravit vzorce, musíte vybrat buňku H10, kde jsou umístěná předlohy vzorec.

  • Jednobuňkového maticového vzorce

    Vypočítá celkový součet s =SUM(F10:F19*G10:G19) jednobuňkového maticového vzorce

    Do buňky H20 Ukázkový sešit zadejte nebo zkopírujte a vložte =SUM(F10:F19*G10:G19)a stiskněte klávesu Enter.

    V tomto případě Excel Vynásobí hodnoty v poli (oblast buněk F10 až G19) a potom použije funkci SUMA společně přidat souhrny. Výsledkem je celkový součet 1,590,000 prodeje.

    Tento příklad ukazuje, jak výkonné může být tento typ vzorce. Předpokládejme například, že máte 1 000 řádků dat. Část nebo celý tato data můžete sečíst tak, že vytvoříte maticový vzorec v jedné buňce místo tažením vzorec dolů až 1 000 řádků. Všimněte si také, že je jednobuňkového vzorec v buňce H20 nezávislou vzorec více buňkách (vzorec v buňkách H10 až H19). Toto je Další výhodou používání maticových vzorců – flexibilitu. Další vzorce ve sloupci H může změnit beze změny vzorec v H20. Lze ji vhodné mít nezávislé součty, jako je tady, jako je dobré ověřte správnost výsledky.

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

    • Soulad    Pokud kliknete na kteroukoliv z buněk z H10 směrem dolů, podívejte se ve vzorci. Tento konzistence lze zajistit větší přesnost.

    • Zabezpečení    Součástí vícebuňkového maticového vzorce nelze přepsat. Například klikněte na buňku H11 a stiskněte klávesu Delete. Excel se nezmění výstupní pole. Ho změnit, budete muset vyberte levou horní buňku v matici nebo buňce H10.

    • Menší velikosti souborů:    Místo několika intermediate vzorců můžete použít často jedním maticovým vzorcem. Například v auta prodejní příkladu jeden maticového vzorce pro výpočet výsledky ve sloupci E. Při použití standardní vzorce jako = F10 * G10, F11 * G11, F12 * G12, atd byste použili jste 11 různé vzorce pro výpočet stejných výsledků. To není koupi velká, ale co dělat, když jste měli tisíce řádky celkové hodnoty? Potom můžete provést velký rozdíl.

    • Efektivity    Funkce Array může být efektivně vytvářet složité vzorce. Pole vzorce =SUM(F10:F19*G10:G19) je stejná jako takto: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Přesahu    Dynamické maticových vzorců se automaticky přepadového do výstupní oblasti. Pokud zdrojová data v tabulce aplikace Excel, pak dynamické maticových vzorcích automaticky změní velikost při přidání nebo odebrání dat.

    • #SPILL! chyby    Dynamická pole zavádí #SPILL! chyby, což znamená, že je blokován oblasti určené přepadového z nějakého důvodu. Při řešení zablokování vzorec bude automaticky přepadového.

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 = {"Od", "Únor", "Březen"}

Pokud položky oddělte čárkami, vytvoříte matice (řádku). Pokud oddělit položek pomocí středníků vytvoříte svislou matici (sloupce). Vytvoření dvojrozměrné vymezení položek v jednotlivých řádcích čárkami a vymezení každý řádek středníkem.

Následující postupy vám umožní vyzkoušet si vytváření vodorovné, svislé a dvojrozměrné konstanty. Budeme se zobrazit příklady použití funkce POSLOUPNOST k automatickému vygenerování maticových konstant, jakož i ručně zadat maticové konstanty.

  • Vytvoření vodorovné konstanty

    Použijte sešit z předchozích příkladů nebo vytvořte nový sešit. Vyberte prázdnou buňku a zadejte =SEQUENCE(1,5). Funkce POSLOUPNOST vytvoří 1 řádek 5 sloupci matice shodný = {1,2,3,4,5}. Zobrazí se následující výsledek:

    Vytvoření vodorovná maticová konstanta s =SEQUENCE(1,5) nebo = {1,2,3,4,5}

  • Vytvoření svislé konstanty

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

    Vytvoření svislá maticová konstanta se vzorcem = SEQUENCE(5) nebo = {1; 2; 3; 4; 5}

  • Vytvoření dvojrozměrné konstanty

    Vyberte prázdnou buňku s místnosti, vpravo a pod ní a zadejte =SEQUENCE(3,4). Zobrazí se následující výsledek:

    Vytvoříte 4 sloupce maticová konstanta s =SEQUENCE(3,4) 3 řádky

    Je rovněž možné zadat: nebo = {1,2,3,4 5,6,7,8; 9,10,11,12}, ale budete chtít věnujte pozornost umístění středníky versus čárkami.

    Jak vidíte, nabízí možnost POSLOUPNOST významné výhody ručně zadat maticové konstanty. Především šetří čas, ale taky pomáhá snížení chyby, které představují ruční zadání. Je taky snadněji číst, zejména jako středníky může být obtížné odlišit od oddělovače čárkou.

Tady je příklad, použití maticové konstanty v rámci větší vzorce. V ukázkovém sešitu přejděte na konstanta ve vzorci listu nebo vytvořte nový list.

V buňce D9 jsme zadali =SEQUENCE(1,5,3,1), ale můžete také zadat 3, 4, 5, 6 a 7 v A9:H9 buněk. Žádné zvláštní informace o této konkrétní výběr čísel, stačí zvolili jsme jinou hodnotu než 1-5 pro rozlišení.

Do buňky 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 = SUMA (D9:H(*SEQUENCE(1,5))

Funkce POSLOUPNOST vytvoří stejně jako maticové konstanty {1,2,3,4,5}. Protože provádění operací výrazů nejdřív uzavřeno do závorek, dalších dvou prvky, které uplatnit jsou hodnoty buněk v D9:H9 a operátor násobení (*). V tomto okamžiku vzorec Vynásobí hodnoty v uloženou matici odpovídající hodnoty konstanty. Se jednat o ekvivalent:

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

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

Eliminování možnosti použití uložené matice a Udržovat operace úplně v paměti, můžete nahradit ho další maticovou konstantou:

=Sum(SEQUENCE(1,5,3,1)*SEQUENCE(1,5))nebo =SUM({3,4,5,6,7}*{1,2,3,4,5})

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

  • Maticové konstanty mohou obsahovat čísla, text, logické hodnoty (například PRAVDA a NEPRAVDA) a chybové hodnoty, například #není_k_dispozici. Ve formátech desetinné a matematickém celé číslo, můžete čísla. Jestliže zahrnete textu, budete muset prostorový 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.

Jednou z doporučených postupů při použití maticových konstant je název je. Pojmenované konstanty mohou být jednodušší použít, a jejich skrytí některých složitost maticových vzorcích provedenými ostatními uživateli. Pojmenování maticové konstanty a použít ve vzorci, postupujte takto:

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

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

Dialogové okno by měl vypadat takto:

Přidat novou pojmenované maticové konstanty z vzorce > definované názvy > > Správce názvů

Klikněte na tlačítko OK a pak vyberte všechny řádek se třemi prázdnými buňkami a zadejte vzorec = čtvrtletí1.

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

Použití pojmenovaných maticová konstanta ve vzorci, třeba = čtvrtletí1, kde má čtvrtletí1 definován jako = {"Od", "Únor", "březen"}

Pokud chcete výsledky přepadového svisle místo ve vodorovném směru, můžete použít =TRANSPONOVAT(čtvrtletí1).

Pokud chcete zobrazit seznam 12 měsíců, to, můžete použít při vytváření finanční výkaz, můžete vytvořit jednu vypnout aktuální rok s funkcí pořadí. Úhledných věc o této funkci je, že i když jsou zobrazeny pouze měsíc, za které můžete použít v dalších výpočtech platná kalendářní data. Tyto příklady najdete na listech s názvem maticové konstanty a Snadné ukázkové datové sady v ukázkovém sešitu.

=TEXT(Date(Year(Today()),SEQUENCE(1,12),1),"MMM")

Použijte kombinaci funkcí TEXT, datum, rok v současné době a POSLOUPNOST vytvářet dynamické seznam 12 měsíců

To použije Funkce DATE k vytvoření kalendářního aktuálního roku, POSLOUPNOST vytvoří maticové konstanty od 1 do 12 leden až prosinec a pak hodnota.na.text převede formát zobrazení "mmm" (leden, únor, březen, atd.). Pokud byste chtěli zobrazit úplný název měsíce, například leden, použijete "mmmm".

Pokud používáte pojmenovaná konstanta jako maticový vzorec, nezapomeňte zadat rovná, jako v = čtvrtletí1, nikoli pouze Čtvrtletí1. Pokud nechcete, interpretován matice jako textový řetězec a vzorec nefunguje očekávaným způsobem. Nakonec mějte na paměti, že můžete pomocí kombinace funkcí, textu a čísel. Všechny závisí jak kreativní chcete odeslat.

Následující příklady ukazují některé způsoby, ve kterém můžete dát maticových konstant v maticových vzorcích. Několik příkladů pomocí transpozice převeďte řádků na sloupce a naopak.

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

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

    Můžete taky dělení s (/), přidání s (+) a odčítání s (-).

  • Druhá mocnina položek v matici

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

  • Vyhledání druhou odmocninu čtverců položek v matici

    Zadejte =odmocnina(SEQUENCE(1,12)^2), nebo =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Transpozice jednorozměrného řádku

    Zadejte =TRANSPOSE(SEQUENCE(1,5))nebo =TRANSPOSE({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

    Zadejte =TRANSPOSE(SEQUENCE(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

    Zadejte =TRANSPOSE(SEQUENCE(3,4))nebo = TRANSPONOVAT ({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

    V následujícím příkladu vysvětluje, jak používat maticové vzorce k vytvoření nové pole z existující pole.

    Zadejte =SEQUENCE(3,6,10,10)nebo = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

    Zadejte {(levá složená závorka) před číslem 10 a} (pravou složenou závorku) po zadání 180, protože vytváříte matici čísel.

    Snímek potom zadejte = D9 #nebo = D9:I11 prázdnou buňku. Se stejnými hodnotami, které vidíte v D9: D11 se objeví matice 3 × 6 buněk. Znak # se nazývá možno operátor oblastia je v aplikaci Excel způsob odkazování na celou oblast matice takže není nutné zadávat se.

    Operátor oblasti možno (#) neodkazuje existujícího pole

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

    Můžete pořídit výsledky možno maticový vzorec a převést, jeho součásti. Vyberte buňku D9 a potom stisknutím klávesy F2 přepněte do režimu úprav. Pak stisknutím klávesy F9 převést odkazy na buňky hodnoty, které Excel pak převede maticovou konstantu. Když stisknete Enter, vzorec = D9 #, by 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. Jedná se o mezery.

    Určení počtu celkový počet znaků v oblasti a dalších polí pro práci s textové řetězce

    = SUMA (LEN(C9:C13))

    V tomto případě funkce délka vrátí délku každý textový řetězec ve všech buněk v oblasti. Funkce SUMA pak sečte se nejprve tyto hodnoty a zobrazí výsledek (66). Pokud byste chtěli získat průměrný počet znaků, můžete použít:

    = PRŮMĚR (LEN(C9:C13))

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

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

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

    Podívejme se podrobněji na vzorec, počínaje vnitřní elementy a ven práce. Funkce délka vrátí délku všechny položky v oblasti buňku D2: D6. Funkce MAX vypočítá největší hodnotu mezi tyto položky, která odpovídá nejdelšího textového řetězce, který je v buňce D3.

    Tady je, kde získat trochu složité věci. Funkce POZVYHLEDAT vypočítá posun (relativní pozici) na buňku obsahující nejdelšího textového řetězce. Aby je dostala, vyžaduje tři argumenty: vyhledávací hodnoty, do vyhledávacího pole a typ shody. Funkce POZVYHLEDAT hledá vyhledávací pole pro zadané hledanou hodnotu. V tomto případě vyhledávací hodnotu nejdelšího textového řetězce:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

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

    • 1 – vrátí největší hodnotu, která je menší nebo rovna vyhledávání val

    • 0 - vrátí první hodnotu přesně shoduje s za hledanou hodnotou

    • -1 - vrátí nejmenší hodnotu, která je větší než nebo rovná hodnotě zadaný vyhledávání

    • Pokud vynecháte typ shody, Excel přiřadí 1.

    Nakonec funkce INDEX má následující argumenty: matice a čísla řádků a sloupců v dané pole. Oblast buněk C9:C13 obsahuje pole, funkce POZVYHLEDAT obsahuje adresa buňky a konečné argument (1) určuje, že hodnota pochází z první sloupec v poli.

    Pokud byste chtěli získat obsah nejmenší textový řetězec, by nahradíte MAX v tomto příkladu MIN.

  • Nalezení n nejmenších hodnot v oblasti

    Tento příklad ukazuje, jak vyhledat tři nejmenší hodnoty v oblasti buněk, kde maticových ukázková data v buňkách B9:B18has byla vytvořená pomocí: = INT (RANDARRAY(10,1) * 100). Všimněte si, že RANDARRAY stále přepočítávané funkce, zobrazí se nová sada náhodná čísla pokaždé, když Excel počítá.

    Maticový vzorec pro určení n-tou nejmenší hodnotu v Excelu: =SMALL(B9#,SEQUENCE(D9))

    Zadejte =SMALL(B9#,SEQUENCE(D9)= malé (B9:B18, {1; 2; 3})

    Tento vzorec používá maticové konstanty zjistit hodnotu funkce SMALL třikrát a vraťte nejmenší 3 členové v matici obsažené v buňkách B9:B18, kde je proměnná hodnotu v buňce D9 3. Najít další hodnoty, můžete zvětšit hodnoty ve funkci SEKVENCE, nebo můžete přidat další argumenty na konstantu. Další funkce můžete taky pomocí tohoto vzorce, například SUMA a průměr. Příklad:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

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

    Najít největší hodnoty v oblasti, můžete funkce SMALL nahradit funkce LARGE. Kromě toho v následujícím příkladu funkce řádek a nepřímý.odkaz .

    Zadejte vzorec = LARGE (B9 # řádku (nepřímý.odkaz ("1:3"))), nebo = LARGE (B9:B18,ROW(INDIRECT("1:3")))

    V tomto okamžiku mohou pomoci trochu vědět o těchto funkcích řádku a nepřímý.odkaz. Vytvoření matice po sobě jdoucí celých čísel můžete pomocí funkce řádek. Například vyberte prázdné a zadejte:

    =ROW(1:10)

    Vzorec vytvoří sloupec s 10 po sobě jdoucí celými čísly. Pokud chcete zobrazit možný problém, Vložit řádek nad oblast obsahující daný maticový vzorec (to znamená nad řádek 1). Upraví odkazy na řádku a vzorec teď generuje celá čísla 2 až 11. Tento problém vyřešíte přidat funkce NEPŘÍMÝ.odkaz ve vzorci:

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

    Funkce NEPŘÍMÝ.odkaz používá textové řetězce jako argumenty (což je proč oblast 1:10 ohraničená uvozovkami). Excel se neupraví textové hodnoty po vložení řádků nebo jinak umístění maticový vzorec. Funkce řádek v důsledku toho generuje vždy pole celých čísel, které chcete. Můžete použít jenom jako snadno pořadí:

    =SEQUENCE(10)

    Podívejme se podrobněji vzorec, který jste dříve použili – = LARGE (B9 # řádku (nepřímý.odkaz ("1:3"))) – od vnitřní závorky a práce ven: nepřímý.odkaz vrátí sadu textových hodnot v tomto případě hodnoty 1 až 3. Funkce řádek zase generuje sloupec tří buněk matice. Funkce LARGE používá hodnoty v oblasti buněk B9:B18 a ho Vyhodnocená každá její položka třikrát jednou pro každý odkaz vrácené funkcí řádku. Pokud chcete najít další hodnoty, přidejte funkce NEPŘÍMÝ.odkaz větší oblast buněk. Nakonec stejně jako u malé příklady, můžete tento vzorec s jinými funkcemi, například SUMA a průměr.

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

    Funkce SUMA v aplikaci Excel nefunguje při pokusu o součet oblasti obsahující chybové hodnoty, například #VALUE! nebo #N/A. Tento příklad ukazuje, jak součet hodnot v oblasti s názvem Data, která obsahuje chyby:

    Pomocí polí pro řešení chyb. Například bude =SUM(IF(ISERROR(Data),"",Data) součet oblasti s názvem Data, i když obsahuje chyby, jako je #VALUE! nebo #NA!.

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

    V tomto příkladu je jako 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.

Možná budete muset sčítání hodnot na základě podmínek.

Pole můžete použít k výpočtu podle určitých podmínek. =SUM(IF(Sales>0,Sales)) bude součet všech hodnot větší než 0 v oblasti s názvem Prodej.

Například tento maticový vzorec sečte jenom kladné celá čísla v oblasti s názvem Prodej, která představuje E9:E24 buněk v předchozím příkladu:

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

Funkce když vytvoří matici hodnot kladné a NEPRAVDA. Funkci SUMA v podstatě ignoruje hodnoty false, protože 0 + 0 = 0. Oblast buněk, použijte tento vzorec může obsahovat libovolný počet řádků a sloupců.

Můžete sečíst taky hodnoty, které splňují více podmínek. Příklad tento maticový vzorec pro výpočet hodnoty větší než 0 a menší než 2 500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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 taky vytvořit maticové vzorce, které použijte typ podmínce. Součet hodnot, které jsou větší než 0, nebo menší než 2 500 lze například

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Nelze použít a a nebo funguje v maticových vzorcích přímo, protože tyto funkce vracejí výsledkem je jedna hodnota, TRUE nebo FALSE a funkce array vyžadují matice výsledků. Problém můžete vyřešit pomocí logiky podle předchozímu vzorci. Jinými slovy, provádět matematických operací, jako je sčítání nebo násobení na hodnoty, které splňují poli se Seznamem nebo podmínka a.

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 ve dvou oblastí buněk s názvem Data1 a data2 a vrátí počet rozdílů mezi nimi. Pokud shodná obsah dvou oblastí, vzorec vrátí 0. Použít tento vzorec, oblasti buněk musí být stejný velikost a stejné dimenze. Například pokud Data1 je oblast 3 řádky sloupů 5, data2 musí být také 3 řádky podle 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*(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.

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ě jako příklady najdete v ukázkovém sešitu na list rozdíly mezi datové sady .

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 je do buňky A1 prázdného listu.

Prodej Osoba

Auta Typ

Číslo Prodané

Jednotky Price

Součet 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 buňky E2:E11, zadejte vzorec = C2: C11 * D2: D11, a stiskněte Kombinaci kláves Ctrl + Shift + Enter.

  2. Zobrazíte celkový součet všech prodejů, že vyberete buňku F11, zadejte vzorec =SUM(C2:C11*D2:D11)a stiskněte Kombinaci kláves Ctrl + Shift + Enter.

Po stisknutí klávesy Ctrl + Shift + Enter, aplikace Excel uzavře vzorec s funkcí složených závorek ({}) a vloží instanci vzorec do každé buňky vybrané oblasti. Velmi rychle se tak, aby uvidíte ve sloupci E celková částka prodeje pro každý typ auta pro každého prodejce. Pokud vyberte E2 a pak vyberte E3, E4 a tak dále, zobrazí se, že se zobrazují ve vzorci: {= 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 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 SUMAspolečně přidat souhrny. Výsledkem je celkový součet 1,590,000 prodeje. Tento příklad ukazuje, jak výkonné může být tento typ vzorce. Předpokládejme například, že máte 1 000 řádků dat. Část nebo celý tato data můžete sečíst tak, že vytvoříte maticový vzorec v jedné buňce místo tažením vzorec dolů až 1 000 řádků.

Všimněte si také, že je jednobuňkového vzorec v buňce D13 nezávislou vzorec více buňkách (vzorec do buňky E2 Až E11). Toto je Další výhodou používání maticových vzorců – flexibilitu. Může změnit vzorce ve sloupci E nebo odstranění sloupce úplně odebrat, beze změny 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í    Součástí vícebuňkového maticového vzorce nelze přepsat. Například klikněte na buňku E3 a stiskněte klávesu Delete. Je potřeba vybrat celou oblast buněk (E2 prostřednictvím E11) a změňte vzorec pro celé pole nebo nechte pole je. Jako míra přidané zabezpečení máte stiskněte Kombinaci kláves Ctrl + Shift + Enter potvrďte změny ve vzorci.

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

Obecně maticové vzorce syntaxi standardní vzorce. Všechny začínala znaménko rovná se (=) a většina předdefinované funkce aplikace Excel můžete použít v maticových vzorcích. Klíčové rozdíl je, pokud používáte maticový vzorec, stiskněte klávesu Ctrl + Shift + Enter zadejte vzorec. Až to uděláte, aplikace Excel uzavře maticového vzorce s složených závorek – Pokud ručně zadat složené závorky vzorec se převedou na textového řetězce a nebude fungovat.

Funkce Array může být efektivně vytvářet složité vzorce. Pole vzorce =SUM(C2:C11*D2:D11) je stejná jako to: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Důležité informace: Kdykoli budete muset zadat maticový vzorec, stiskněte Kombinaci kláves Ctrl + Shift + Enter. Toto nastavení se projeví na jedné buňce i více buňkách 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 oblast celý vzorec (například E2:E11) a potom stiskněte klávesu Delete.

  • Nelze vložit prázdné buňky nebo odstranění buněk z vícebuňkového maticového vzorce.

V některých případech může potřebujete rozšíření maticového vzorce. Vyberte první buňku v oblasti existující pole a pokračujte, než jste vybrali celou oblast, že chcete rozšířit vzorec. Stisknutím klávesy F2 upravte vzorec a potom stiskněte Kombinaci kláves CTRL + SHIFT + ENTER Potvrďte vzorec, jakmile jste upravit vzorce oblast. Klávesa se má vybrat celou oblast, počínaje buňkou vlevo nahoře v poli. Levé horní buňky se upraví.

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

  • Může občas zapomenete stiskněte Kombinaci kláves Ctrl + Shift + Enter. Může dojít k i nejčastěji zkušení uživatelé aplikace Excel. Nezapomeňte stisknutím kombinace kláves pokaždé, když se připojíte nebo upravit maticový vzorec.

  • Ostatní uživatelé sešitu nemusí Principy vzorců. Ve skutečnosti nejsou maticové vzorce obecně vysvětlení v listu. Proto pokud ostatní tito uživatelé musí změnit svoje sešity, si vyhnout maticových vzorcích nebo zkontrolujte, jestli ti lidé, kteří vědět o všech maticových vzorců a pochopit, jak změnit, pokud budou muset.

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

Nyní by vás upozorní že budete muset po vytvoření maticové vzorce stiskněte Kombinaci kláves Ctrl + Shift + Enter. Protože maticové konstanty jsou součástí maticové vzorce, uzavřete konstanty se závorkami zadáním ručně. Můžete pomocí Kombinace kláves Ctrl + Shift + Enter zadejte 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 pole v jediném řádku: {1,2,3,4}. Tady je pole v jednom sloupci: {1; 2; 3; 4}. A tady je palety dva řádky a čtyři sloupce: {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. Jeden středník odděluje dva řádky 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. V 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 počáteční a koncovou složenou závorku ({}) a Excel přidá druhé sadě za vás.

    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 v závorkách je maticové konstanty: {1,2,3,4,5}. Mějte na paměti, že Excel není prostorový maticové konstanty se závorkami; je skutečně zadáte. Nezapomeňte taky, že po přidání konstantu do maticový vzorec vytvoříte stisknutím Kombinace kláves Ctrl + Shift + Enter zadejte vzorec.

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

Zkuste toto 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. Stejný výsledek zobrazí stejně jako při starší výkonu, který používá 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.

Jednou z nejlepší způsob, jak používání maticových konstant je název je. Pojmenované konstanty mohou být jednodušší použít, a jejich skrytí některých složitost maticových vzorcích provedenými ostatními uživateli. Pojmenování maticové konstanty a použít ve vzorci, postupujte takto:

  1. Na kartě vzorce klikněte ve skupině Definované názvy na položku Definovat název.
    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 nemusí oddělené pomocí mapy znaků správné. Pokud nezadáte čárkou nebo středníkem nebo vložení na špatném místě, maticová konstanta nemusí být vytvořen správně, nebo může se 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é způsoby, ve kterém můžete dát maticových konstant v maticových vzorcích. Několik příkladů pomocí transpozice převeďte řá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. Stiskněte Kombinaci kláves Ctrl + Shift + Enter, které slouží k vložení tohoto pole čísel v oblasti buňky C8: E10 pomocí maticového vzorce. Na listu C8 až E10 by měl vypadat takto:

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

  4. V řádku vzorců zadejte následující vzorec a potom stiskněte Kombinaci kláves Ctrl + Shift + Enter:

    =C8:E10

    Matice 3 × 3 buněk se zobrazí v buňkách C1 až E3 se stejnými hodnotami, které vidíte v buňkách C8 až E10.

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

  1. S buňkami s C1: C3 pracovní, stisknutím klávesy F2 přepněte do režimu úprav.

  2. Stisknutím klávesy F9 převést odkazy na buňky k hodnotám. Excel převede hodnoty maticové konstanty. Vzorec by měla = {10,20,30; 40,50,60; 70,80,90}.

  3. Stiskněte Kombinaci 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 stiskněte Kombinaci kláves Ctrl + Shift + Enter zobrazte celkový počet znaků v buňkách a2: a6 (66).

  3. Vyberte buňky A10 a stiskněte Kombinaci kláves Ctrl + Shift + Enter zobrazte obsah nejdelší z buněk a2: a6 (buňky 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ě funkce Délka vrátí délku každý textový řetězec ve všech buněk v oblasti. Funkce SUMA pak sečte se nejprve tyto hodnoty 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 několik náhodná čísla v buňkách A1:A11.

  2. Vyberte oblast buněk C1 až C3. Tuto sadu buněk bude obsahovat výsledky vrácené maticový vzorec.

  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á maticové konstanty vyhodnocení funkce SMALL třikrát a vraťte se nejmenší (1), druhé nejmenší (2) a třetí nejmenší (3) členové v matici obsažené v buňkách a1: a10 na Najít další hodnoty, přidejte další argumenty konstanta. Další funkce můžete taky pomocí tohoto vzorce, například SUMA a průměr. Příklad:

= 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. V řá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 mohou pomoci vědět o něco o řádek a funkce nepřímý.odkaz . Vytvoření matice po sobě jdoucí celých čísel můžete pomocí funkce řádek . Například vyberte prázdný sloupec 10 buněk v sešitu praktické cvičení zadat 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.

Podívejme se na vzorec, který jste dříve použili – = LARGE (A5:A14,ROW(INDIRECT("1:3"))) – počínaje vnitřní závorky a práce ven: funkce nepřímý.odkaz vrátí sadu textových hodnot v tomto případě hodnoty 1 až 3. Funkce řádek zase vygeneruje sloupcové pole tři buňky. Funkce LARGE používá hodnoty v oblasti buněk A5:A14 a ho Vyhodnocená každá její položka třikrát jednou pro každý odkaz vrácené funkcí řádku . Hodnoty 3200, 2700 a 2000 vracejí do tří buněk sloupcová pole. Pokud chcete najít další hodnoty, přidejte funkce nepřímý.odkaz větší oblast buněk.

Stejně jako u předchozích příkladech můžete použít tento vzorec 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 na předchozím příkladu řetězec textu zadejte následující vzorec do prázdné buňky a stiskněte Kombinaci kláves Ctrl + Shift + Enter:

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

Text "skupina buněk, které" se zobrazí.

Podívejme se podrobněji na vzorec, počínaje vnitřní elementy a ven práce. Funkce Délka vrátí délku všechny položky v oblasti buněk a2: a6. Funkce MAX vypočítá největší hodnotu mezi tyto položky, která odpovídá nejdelšího textového řetězce, 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.

Nakonec funkce INDEX má následující argumenty: matice a čísla řádků a sloupců v dané pole. Oblast buněk a2: a6 obsahuje pole, funkce POZVYHLEDAT obsahuje adresa buňky a konečné argument (1) určuje, že hodnota pochází z první sloupec v poli.

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

Potvrzení

Části tohoto článku byly založeny na základě série sloupce uživatelů Power Excel napsal Petr Karásek a upravený z kapitol 14 a 15 Excel 2002 vzorců knize napsal Jan Walkenbach bývalého MVP aplikace 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ých vzorců a starší verze maticové vzorce CSE

Funkce FILTR

Funkce RANDARRAY

Funkce SEQUENCE

Funkce SINGLE

Funkce SEŘADIT

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.

×