Jistě někdy Potýkali jste se se stovkami vzorců v tabulce vygenerovat složitou zprávu. Kdybych znal maticové vzorce, bylo by to pravděpodobně mnohem jednodušší. Jsou jedním z těch pokročilé funkce Excelu které většina uživatelů přehlíží, ale které hrají zásadní roli, když začnete provádět seriózní výpočty.
the maticové vzorce Umožňují Excelu zpracovat mnoho hodnot najednou, provádět mezivýpočty „neviditelně“ a vracet jeden nebo více výsledků, aniž by se pracovní list zaplňoval pomocnými sloupci. Zpočátku se zdají být zastrašující, ale jakmile si je osvojíte, stanou se neuvěřitelně výkonným nástrojem pro komplexní analýzy, reporty a modely.
Co je to matice a co přesně je maticový vzorec?
Než se budeme zabývat maticovými vzorci, musíme Pochopení toho, co je matice v ExceluMatice je jednoduše sada hodnot považovaných za blok: mohou to být čísla nebo text a mohou být v jednom řádku, v jednom sloupci nebo v rozsahu několika řádků a sloupců.
Klasickým příkladem matice je seznam měsíců v roce.Představte si maticový vzorec obsahující všechny měsíce: {"leden";"únor";"březen";"duben";"květen";"červen";"červenec";"srpen";"září";"říjen";"listopad";"prosinec"}. Pokud vyberete 12 buněk ve sloupci, zadejte ={"leden";…;"prosinec"} a potvrďte jako maticový vzorec, Excel vyplní všechny tyto buňky najednou.
Maticový vzorec je vzorec, který pracuje s poli namísto jednotlivých hodnot.Hlavní rozdíl oproti „běžným“ recepturám spočívá v tom, že dokáže:
- Přijímat více hodnot jako vstup (celý rozsah nebo konstanta několika prvků).
- Provádějte více interních výpočtů o každém z těchto prvků.
- Vrátit jednu hodnotu nebo celé pole výsledků.
Představte si maticový vzorec jako masivní paralelní výpočetExcel vyhodnocuje prvek po prvku, ukládá mezivýsledky do paměti a zobrazuje pouze konečný výsledek (buňku nebo blok buněk), aniž byste museli vidět nebo udržovat mezikroky.
Jak zadat a rozpoznat maticový vzorec v Excelu
Charakteristickým znakem klasického maticového vzorce jsou složené závorky { } Tyto složené závorky vidíte ve vzorovém řádku, když je buňka vybrána. Tyto závorky se nepíší; Excel je přidá automaticky, když potvrdíte správnost vzorce.
Zadání maticového vzorce v tradičních verzích aplikace Excel (před dynamickými vzorci) je postup tento:
- Napište vzorec jako obvykle.Například: =SUMA(B2:B11*C2:C11).
- Nestačí jen stisknout EnterMusíte stisknout kombinaci Ctrl + Shift + Enter (Ctrl + Shift + Enter).
- Excel uzavře vzorec do složených závorek. ve vzorci: {=SUM(B2:B11*C2:C11)}. To znamená, že se s ním zachází jako s maticovým vzorcem.
Existuje několik důležitých detailů, které by měly být zcela jasné. abys z nich nezbláznil/a:
- Ruční psaní klíčů nebude fungovat.Pokud kolem vzorce zadáte znaky { a }, Excel s ním bude stále zacházet jako s běžným vzorcem; pouze kombinace kláves ho změní na maticový vzorec.
- Při úpravě maticového vzorce složené závorky dočasně zmizí.Po úpravě budete muset znovu stisknout Ctrl + Shift + Enter, aby se z něj opět stala matice.
- Pokud zapomenete použít Ctrl + Shift + EnterVzorec bude vyhodnocen jako normální a pravděpodobně použije pouze první prvek každého rozsahu, což vrátí nesprávný výsledek.
- V maticových vzorcích, které vracejí více výsledků (více buněk), nejprve musíte vybrat celý výstupní rozsah, zadat vzorec a poté potvrdit stisknutím kláves Ctrl + Shift + Enter.
Kromě toho můžete „špehovat“ to, jak se počítají části maticového vzorce. výběrem části vzorce (například B2:B11*C2:C11) ve vzorci a stisknutím klávesy F9Excel zobrazí výsledek této části jako pole hodnot. Poté pomocí Esc, zrušíte, abyste nic neupravili.
Maticové vzorce s výsledkem v jedné buňce
Mnoho z nejúčinnějších použití maticových vzorců vrací jednu hodnotu. Ale pod povrchem spravují celé rozsahy a provádějí řadu interních operací. Podívejme se na několik běžných vzorců, které stojí za to zvládnout.
Základní příklad: celkový prodej bez pomocných sloupců
Představte si jednoduchý prodejní grafSloupec A obsahuje produkt, sloupec B prodané jednotky (B2:B11) a sloupec C jednotkovou cenu (C2:C11). Chcete získat celkovou fakturovanou částku za dané období.
Tradičním způsobem by bylo vytvořit sloupec s částkami (jednotky * cena) a poté sečíst tento sloupec. Pomocí maticového vzorce uložíte celý pomocný sloupec:
=SUMA(B2:B11*C2:C11)
Po zadání do buňky potvrďte stisknutím kláves Ctrl + Shift + Enter.Excel vynásobí každou dvojici buněk řádek po řádku (B2*C2, B3*C3 atd.), tyto mezisoučty interně uloží a nakonec je všechny sečte, čímž vrátí součet v jedné buňce.
Sečtěte oblast obsahující chyby
Pokud se pokusíte sečíst oblast, která obsahuje chyby, jako například #N/A, funkce SUM selže.Pomocí maticového vzorce můžete tyto chyby „ignorovat“ a sečíst pouze platné hodnoty. Předpokládejme, že máte oblast s názvem Data, která obsahuje čísla a nějaké chyby:
{=SUMA(POKUD(JECHYBA(Data);"";Data))}
Logika je následující:
- ISERROR(Data) vrací pole hodnot TRUE/FALSE v závislosti na chybě v každé buňce.
- IF(ISERROR(Data);"";Data) generuje novou matici kde chyby jsou nahrazeny znakem «» (prázdný řetězec) a správné hodnoty jsou zachovány.
- SUMA operuje s touto filtrovanou maticíignoruje prázdné řetězce a vrací součet pouze zdravých číselných hodnot.
Spočítejte, kolik chyb je v rozsahu
Podle stejného principu jako dříve můžete také spočítat chyby v daném rozsahu Datové volání:
{=SUMA(POKUD(JEDNACHYBA(Data);1;0))}
Tento vzorec vytvoří pole jedniček a nul.tak, že do buněk s chybami vložíte 1 a do buněk bez chyb 0 a poté toto pole sečtete. Zjednodušit to lze několika způsoby:
{=SUMA(POKUD(JECHYBA(Data);1))}
V této verzi, když je ISERROR(Data) NEPRAVDAFunkce IF vrací přímo hodnotu FALSE, kterou funkce SUM považuje za 0. Lze ji ještě dále ořezávat:
{=SUMA(POKUD(JECHYBA(Data)*1))}
Toto využívá skutečnosti, že TRUE*1=1 a FALSE*1=0Funkce ISERROR(Data) vrací hodnotu TRUE/FALSE, vynásobením 1 získáte 1/0 a funkce SUM počítá jedničky (tj. chyby).
Přidávání hodnot, které splňují jednu nebo více podmínek
Jedním z nejtypičtějších použití maticových vzorců je provádění velmi flexibilních podmíněných součtů., kombinující několik logických testů, aniž by se vždy spoléhal na SUMIFS nebo SUMIFS.
Například sečtěte pouze kladné hodnoty v rozsahu s názvem Prodej:
{=SUMA(POKUD(Prodej>0;Prodej))}
IF(Prodej>0;Prodej) generuje pole, ve kterém se objevují kladné hodnoty a zbytek FALSEFunkce SUM ignoruje hodnotu FALSE a sečte pouze čísla.
Pokud potřebujete použít více podmínek ANDLogické podmínky můžete vynásobit. Například součet tržeb větších než 0 a menších nebo rovných 5:
{=SUM((Prodej>0)*(Prodej<=5)*Prodej)}
Každý logický výraz vrací PRAVDA/NEPRAVDAPři jejich vynásobení a následném vynásobení hodnotou Prodej přispívají k celkovému součtu pouze buňky, které splňují obě podmínky (TRUE se chová jako 1, FALSE jako 0). Poznámka: Tento vzorec vrátí chybu, pokud se v rozsahu nacházejí buňky smíšené s nečíselnými hodnotami.
Pro podmínky typu O můžete přidat logické kontroly.Například sečtení prodejů menších než 5 nebo větších než 15:
{=SUMA(POKUD((Prodej<5)+(Prodej>15);Prodej))}
Součet (Prodej<5)+(Prodej>15) vrací 1 pro buňky, které splňují alespoň jednu z podmínek. a 0 pro zbytek. POKUD se hodnoty prodeje zachovají, když je součet 1, a v ostatních případech se zachovají hodnoty NEPRAVDA, což funkce SUM ignoruje.
Je důležité pochopit, proč se funkce AND a OR nepoužívají přímo v maticových vzorcích.Tyto funkce vracejí jednu hodnotu TRUE nebo FALSE, zatímco maticové vzorce vyžadují pole logických výsledků (jeden na buňku). Podmínky se proto násobí nebo sčítají prvek po prvku.
Vypočítejte průměr bez nul
Další typickou potřebou je vypočítat průměry bez nul, které by „kontaminovaly“ výsledek.Pomocí maticového vzorce můžete tyto nuly filtrovat z oblasti s názvem Prodej:
{=PRŮMĚR(POKUD(Prodej<>0;Prodej))}
Funkce KDYŽ vytvoří pole s nenulovými hodnotami a vrací FALSE, kde jsou nuly. Funkce AVERAGE vypočítá průměr pouze s číselnými hodnotami a ignoruje FALSE.
Počítání rozdílů mezi dvěma rozsahy
Pokud chcete vědět, kolik buněk se liší mezi dvěma oblastmi stejné velikostiNapříklad maticový vzorec pro MojeData a VašeData to řeší velmi kompaktním způsobem:
{=SUM(POKUD(MojeData=VašeData;0;1))}
Vzorec porovnává oba rozsahy pozici po pozici.Pokud se hodnoty shodují, funkce IF vrátí 0; pokud se neshodují, vrátí 1. Funkce SUM počítá rozdíly. Můžete napsat kratší verzi:
{=SUM(1*(MojeData<>VašeData))}
MyData<>YourData generuje TRUE tam, kde existují rozdíly a NEPRAVDA, kde se vše shoduje. Vynásobením 1 se tyto logické hodnoty převedou na 1 a 0, které funkce SUM sečte a získá celkové odchylky.
Nalezení pozice maximální hodnoty v rozsahu
Maticové vzorce také umožňují najít pozice v rozsahuNapříklad číslo řádku, kde se nachází maximální hodnota v rozsahu sloupce s názvem Data:
{=MIN(POKUD(Data=MAX(Data);ŘÁDEK(Data);»»))}
Tato formule funguje takto:
- MAX(Data) vrací maximální hodnotu z rozsahu.
- KDYŽ(Data=MAX(Data);ROW(Data);»») generuje pole s číslem řádku pro buňky, které obsahují toto maximum, a «» pro zbytek.
- Funkce MIN aplikovaná na toto pole vrací nejmenší číslo řádku.Tedy první výskyt maximální hodnoty.
Pokud místo čísla řádku chcete zobrazit celý odkaz na buňkuMůžete to zabalit do funkce ADDRESS:
{=ADRESA(MIN(POKUD(Data=MAX(Data);ŘÁDEK(Data);»»));SLOUPEC(Data))}
Zde je vypočítaný řádek kombinován se sloupcem z datové oblasti. vrátit odkaz jako „$C$15“.
Maticové vzorce s výsledky ve více buňkách
Kromě maticových vzorců, které vracejí jednu hodnotuExistují vzorce, které po potvrzení vyplní celý rozsah najednou. Ty jsou obzvláště užitečné pro vytváření transformovaných tabulek, provádění výpočtů řádek po řádku nebo pro konstrukci výsledků, které závisí na více než jedné dimenzi.
Příklad jednorozměrné matice: měsíce v roce
Již jsme viděli, že vertikální jednorozměrná matice může obsahovat například měsíceChcete-li je automaticky vyplnit ve sloupci, vyberte 12 buněk v jednom sloupci, zadejte konstantu pole a potvrďte ji stisknutím kláves Ctrl + Shift + Enter.
Můžete také použít vzorce, které vypočítávají celé rozsahy.Například pokud chcete vypočítat 5% daň z množství každého produktu (s využitím prodeje a cen z předchozího příkladu), můžete napsat:
=B2:B11*C2:C11*0,05
Nejprve vyberte oblast 10 svislých buněk. Zadejte vzorec, kam chcete tyto částky daně zobrazit, a poté vzorec potvrďte jako maticový vzorec. Každý řádek zobrazí daň odpovídající každému produktu.
Příklad vícerozměrné matice: TRANSPOZE tabulky
Excel obsahuje funkce určené k vrácení celých polí, jako například TRANSPOZE, TREND, FREKVENCE nebo Funkce MAPTRANSPOSE je jednou z nejpopulárnějších funkcí, protože umožňuje prohodit řádky a sloupce a naopak.
Předpokládejme, že máte tabulku v A1:D11. A chcete to otočit tak, aby se z původních řádků staly sloupce. Postup je následující:
- Vyberte prázdný rozsah s invertovanou velikostíPokud má původní tabulka 4 sloupce a 11 řádků, vyberte rozsah 11 sloupců a 4 řádků.
- Do řádku vzorců zadejte vzorec =TRANSPOSE(A1:D11) zatímco je daný rozsah vybrán.
- Potvrďte stisknutím Ctrl + Shift + Enter pro generování transponované matice v Excelu. ve všech buňkách rozsahu.
Při práci s maticovými vzorci, které vracejí více buněkMusíte vzít v úvahu několik pravidel hry:
- Nejprve musíte vybrat celý rozsah, kam bude matice umístěnaPak napíšete vzorec a potvrdíte ho.
- Nelze upravovat ani jednu buňku v poli.Jakákoli úprava ovlivní celý vzorec; pokud se pokusíte změnit izolovanou buňku, zobrazí se chybová zpráva.
- Chcete-li odstranit pole, vyberte celý rozsah a odstraňte vzorec. z řádku vzorců a poté potvrďte stisknutím kláves Ctrl + Shift + Enter nebo jednoduše Delete, pokud jej chcete odstranit.
- Pokud chcete, aby matice zabírala méně buněkNejprve budete muset smazat původní pole a znovu ho vytvořit v novém rozsahu.
- Pokud potřebujete pokrýt více buněkVyberte starý rozsah a nové buňky, upravte vzorec tak, aby odpovídal nové velikosti, a znovu jej potvrďte jako maticový vzorec.
Konstantní matice: pevné hodnoty ve vzorcích
„Konstantní matice“ je jednoduše sada statických hodnot zapsaných v samotném vzorci.uzavřené ve složených závorkách a oddělené odpovídajícím znakem. Jsou velmi užitečné pro seskupování kritérií, vah nebo pevných seznamů, které nechcete v buňkách listu mít.
V Excelu ve španělštině se konstantní pole řídí těmito obecnými pravidly:
- Jednorozměrné horizontální pole: hodnoty v jednom řádku, oddělené zpětnými lomítky (\) a uzavřené ve složených závorkách. Například: {1\2\3\4}.
- Jednorozměrné vertikální poleHodnoty v jednom sloupci oddělené středníky (;) ve složených závorkách. Například: {"leden";"únor";"březen"}.
- Dvourozměrná maticeKombinace obojího; sloupce jsou odděleny zpětným lomítkem a řádky středníkem. Například: {1\2\3;4\5\6} představuje dva řádky a tři sloupce.
Prvky maticové konstanty mohou být čísla, text, logické hodnoty a chyby..
- Číslacelá čísla, desetinná čísla nebo vědecký zápis.
- Textvždy v uvozovkách.
- BooleansPRAVDA a NEPRAVDA (bez uvozovek).
- Chyby: #N/A, #DIV/0! atd.
Konstanta pole nemůže obsahovat odkazy na buňky, jiné rozsahy, funkce ani definované názvy.Pouze „čisté“ hodnoty.
Přiřazování názvů konstantním polím
Pro pohodlné opětovné použití maticové konstantyMůžete mu přiřadit název, abyste ho nemuseli pokaždé psát:
- Přejděte na kartu Vzorce > Definované názvy > Přiřadit název.
- Napište popisný název (například Měsíce).
- Do pole „Odkazuje na“ zadejte maticovou konstantuNapříklad: ={"leden"\"únor"\"březen"\"duben"\"květen"\"červen"\"červenec"\"srpen"\"září"\"říjen"\"listopad"\"prosinec"}.
- Klikněte na OK pro uložení jména.
Použití této matice v listuVyberte tolik buněk, kolik je položek, zadejte =Měsíce a potvrďte stisknutím Ctrl + Shift + Enter. Excel rozdělí každý měsíc do odpovídající buňky.
Pokud maticová konstanta „nefunguje“ a vrátí chybuExistuje několik bodů, které byste měli zkontrolovat:
- Zkontrolujte, zda jste použili správné oddělovače: zpětné lomítko pro vodorovné oddělení sloupců a středník pro svislé oddělení řádků.
- Zkontrolujte, zda má oblast, kam chcete materiál vyhodit, vhodnou velikost a orientaci..
- Ujistěte se, že je text uzavřen v uvozovkách a že jste si nezaměnili odkazy na buňky.které nejsou v konstantách povoleny.
Příklad: sečtěte N nejvyšších hodnot v rozsahu
Velmi zajímavým využitím maticových konstant je jejich kombinace s funkcemi jako LARGE nebo SMALL.Představte si, že chcete sečíst tři nejvyšší hodnoty v rozsahu dat, řekněme A1:A100.
Logické by bylo zavolat K.ESIMO.MAYOR třikrát. (pro 1, 2 a 3) a sečtěte výsledek, ale s maticovou konstantou to uděláte najednou:
{=SUMA(K.ESIMO.MAYOR(A1:A100;{1\2\3}))}
Konstanta {1\2\3} říká funkci K.ESIMO.MAYOR, aby vrátila tři hodnoty.Největší, druhý největší a třetí největší. Funkce SUMA tyto tři výsledky sečte. Změnou na NEJMENŠÍ získáte součet tří nejmenších.
Příklad: Počítání buněk, které splňují mnoho kritérií
Pokud potřebujete zohlednit více podmínek, je COUNTIFS dobrou volbou.Pokud je však kritérií mnoho, vzorec se stává dlouhým a obtížně čitelným, protože rozsah kritérií opakujete znovu a znovu.
Představte si seznam produktů, které Pedro ohodnotil od 1 do 5. Jméno hodnotitele je ve sloupci C (C2:C28) a ve sloupci A (A2:A28). Chceme vědět, kolikrát Pedro udělil 3, 4 nebo 5 bodů.
Místo tří různých funkcí COUNTIFS můžete použít maticovou konstantu.:
=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))
Konstanta {3\4\5} způsobí, že funkce COUNTIFS interně generuje tři výsledky. (pro každé skóre) a funkce SUM je seskupí. To vám dává kompaktní, snadno čitelný a efektivnější vzorec při práci s mnoha kritérii.
Jak vidíte, maticové vzorce proměňují Excel v jakýsi mini vektorový výpočetní nástroj.Je schopen zpracovávat velké rozsahy, více podmínek a řetězené operace, aniž by zahlcoval tabulku podpůrnými sloupci. Zvládnutí klávesové zkratky Ctrl+Shift+Enter, pochopení chování logických polí (PRAVDA/NEPRAVDA) a procvičování příkladů, jako je sčítání s podmínkami, zpracování chyb, průměrování bez nul nebo práce s konstantami pole, vám poskytne úroveň kontroly daleko za hranicí průměrného uživatele a umožní vám vytvářet čistší, rychlejší a snadněji udržovatelné tabulky.
