Moderní Excel mi konečně dával smysl poté, co jsem se naučil, jak se liší pole a oblasti

Téměř každý, kdo zadal data do tabulky Excel, má hrubou představu o tom, co je rozsah. Je to jeden z těch konceptů, které nemusíte studovat, protože je přirozeně pochopíte při používání softwaru. Pole (arrays) jsou naopak obvykle trochu těžší pochopit.

I když mohou tyto dvě věci na pracovním listu často vypadat podobně, Excel je ukládá a zpracovává odlišně. Porozumění tomuto rozdílu vám může pomoci vyhnout se běžným chybám v Excelu, jako jsou #VALUE! a #SPILL!, a usnadnit provádění více výpočtů efektivněji.

Pole vs. rozsahy

Mohou obsahovat stejná data, ale nejsou to stejné věci

Rozsah je shromáždění jedné nebo více hodnot fyzicky umístěných v buňkách na mřížce pracovního listu. Protože rozsahy existují přímo na listu, odkazujete na ně pomocí adres buněk, například C3 nebo C1.

Rozsahy v Excelu mohou obsahovat hodnoty, které jste zadali ručně, ale mohou mít také podobu speciálního typu rozsahu nazývaného rozlitý rozsah. Když vzorec dynamického pole vrátí více hodnot, Excel tyto výsledky automaticky rozlije na list, čímž vytvoří rozlitý rozsah. Na rozdíl od standardního rozsahu, kde můžete upravovat každou buňku samostatně, lze rozlitý rozsah upravovat pouze z jeho levého horního rohu. Můžete však odkazovat na celý rozlitý rozsah přidáním symbolu # k adrese levé horní buňky. Například pokud rozlitý rozsah začíná v N2, můžete odkazovat na všechny jeho hodnoty pomocí N2#.

Pole je mezitím seznam nebo tabulka dat, která Excel ukládá do paměti, spíše než jako hodnoty vázané na konkrétní adresy buněk. Během výpočtu Excel uchovává pole v operační paměti, což mu umožňuje předávat data mezi funkcemi, aniž by zapisoval mezivýsledky zpět do listu. Vezměte si například tento vzorec:

=COUNT(UNIQUE(A1))

Funkce UNIQUE nejprve vytvoří pole v paměti a funkce COUNT následně toto pole vyhodnotí. Na pracovní list se zobrazí pouze konečný výsledek, zatímco mezilehlý seznam unikátních hodnot zůstává skrytý v paměti Excelu.

Pole se na listu objeví pouze tehdy, když se rozlije do buněk. V té chvíli výstup nabývá formy rozsahu. Například zadáním =SEQUENCE(10) do buňky se v paměti vytvoří pole čísel od 1 do 10, které se následně rozlije do buněk níže. Jakmile jsou rozlita, tyto hodnoty se stávají rozlitém rozsahem. Před rozlitím, nebo i když je pole pouze předáváno mezi funkcemi, zůstává polem v paměti. V praxi, když Excel zpracovává data prostřednictvím funkcí jako LET, UNIQUE a dalších dynamických poliových vzorců, nadále s těmito daty pracuje jako s polem, nikoli jako s rozsahem na listu.

V Excelu můžete také vytvářet konstanty polí přímo ve vzorcích obklopením hodnot složenými závorkami. To vám umožňuje zadat více hodnot jako jediné pole, aniž byste odkazovali na buňky. Například můžete definovat číselná pole jako {1, 2, 3, 4, 5} nebo {6, 2, 10, 9, 3} a textová pole jako {"a", "a"}, pokud funkce podporuje vstupy v podobě polí.

Například zatímco některé podmíněné funkce jsou striktní při požadavku na rozsahy buněk, SUMPRODUCT přijímá jak rozsahy listu, tak i pevně zadané pole. V následujícím vzorci Excel vyhodnocuje hodnoty v A1:A5 podle kritéria

=SUMPRODUCT((A1:A5

Můžete také pozorovat, jak Excel během výpočtů převádí oblasti na pole. Pokud v řádku vzorců vyberete odkaz na oblast, například A1, a stisknete F9, Excel odkaz vyhodnotí a nahradí ho pevně kódovaným polem uzavřeným ve složených závorkách. To umožňuje vidět přesné hodnoty, se kterými Excel v daném okamžiku pracuje v paměti.

Proč je rozdíl ve vašich vzorcích důležitý

Vědět, kde Excel ukládá data, vám pomáhá zvolit správný přístup

Některé funkce Excelu, včetně SUMIF, COUNTIF a RANK, jsou naprogramovány tak, aby pracovaly s fyzickými oblastmi. Pokud některé z těchto funkcí předáte pole založené na paměti místo odkazu na oblast, Excel vrátí chybu #VALUE!.

Například funkce SUMIF nemůže zpracovat pole vytvořené funkcí SEQUENCE v následujícím vzorci:

=SUMIF(SEQUENCE(10), ">5")

Nicméně, pokud zadáte =SEQUENCE(10) do buňky A1, Excel výsledky rozlije do buňky A1. Poté můžete použít tento vzorec:

=SUMIF(A1#, ">5")

Tato verze funguje, protože A1# odkazuje na fyzický rozsah na listu namísto pole v paměti.

Moderní funkce, jako jsou FILTER, UNIQUE, SORT a LET, fungují jinak. Mohou předávat pole přímo mezi funkcemi, aniž by ukládaly mezilehlé výsledky na list. To činí složité vzorce efektivnějšími, protože Excel může provádět výpočty zcela v paměti, dokud není připraven vrátit konečný výsledek.

Zvažte tento vzorec:

=LET(HighSales, FILTER(B2:B100, I2:I100 > 9000), UniqueCountries, UNIQUE(HighSales), SORT(UniqueCountries))

Funkce LET vytváří proměnné, které existují pouze v rámci vzorce. Nejprve funkce FILTER prozkoumá sloupec I a vrátí země ve sloupci B, jejichž prodeje jsou vyšší než 9 000. Excel tento seznam uloží jako pole do paměti a přiřadí ho k proměnné HighSales. Poté funkce UNIQUE odstraní duplikáty z tohoto pole a výsledek uloží jako UniqueCountries. Nakonec funkce SORT seřadí seznam podle abecedy a hotový výsledek zobrazí na listu.

Kdybych to dělal staromódním způsobem, potřeboval bych několik pomocných sloupců k uložení každého mezikroku. S dynamickými poli Excel vytváří tyto dočasné pole v paměti, předává je z FILTER do UNIQUE a pak do SORT a zapisuje pouze konečný výsledek do listu. Vzorec zůstává samostatný a sešit zůstává přehlednější a snadněji spravovatelný.

Rozlišení mezi poli a rozsahy je také důležité, protože některé funkce Excelu stále nepodporují dynamická pole. Validace dat v Excelu například vyžaduje fyzický rozsah. Nemůžete vložit maticový vzorec přímo do pole zdroje validace. Místo toho musíte vzorec zadat do buňky na listu, nechat ho rozpustit a potom odkazovat na rozpustený rozsah pomocí operátoru #. Pokud například váš vzorec začíná v buňce P1, použijete jako zdroj validace =P1#.

Tabulky v Excelu mají podobné omezení. Protože jsou tabulky navrženy kolem nezávislých řádků a sloupců, vysypané maticové vzorce nejsou v rámci struktury tabulky podporovány. Jakýkoli dynamický maticový vzorec musí být umístěn mimo tabulku.

Nakonec potřebují dynamická pole místo k rozšíření. Pokud má vzorec přetékat do dvaceti buněk, musí být tyto buňky prázdné. Pokud stávající data blokují oblast přetečení, Excel vrátí chybu #SPILL!. Tradiční oblasti tuto podmínku nemají, protože zabírají pouze buňky, které jste výslovně určili. V důsledku toho se musíte starat o vytvoření místa pouze tehdy, když je vzorec navržen k přetečení.

Moderní Excel funguje lépe, když víte, kde se vaše data nacházejí

Rozsah má své místo na mřížce vašeho listu, zatímco pole žije v paměti Excelu, dokud mu nenaleznete místo, kam se uloží. Pochopení tohoto rozdílu činí práci s moderním Excelem mnohem snazší. Můžete vytvářet odolnější, automatizované tabulky a vnořovat funkce jako LET, FILTER a UNIQUE, aniž byste zalévali svůj list mezičlánkovými výpočty.