Jak používat tabulky v Excelu k automatizaci vzorců a zabránění poškozeným oblastem

Většina online tutoriálů na Excel se zaměřuje na efektní nové funkce, složité vzorce nebo chytré triky, které řeší jednoduché problémy příliš komplikovaným způsobem. Ale opravdové vylepšení není vůbec nové ani vzrušující. Tabulky Excel jsou staré, nenápadné a jedna z nejmocnějších funkcí na pásu karet - a změní způsob, jak pracujete.

Výchozím výchozím bodem v Excelu je převést svá data na tabulku

Vybudujte chytřejší základ pro tabulky

Většina lidí otevře nový list Excelu a hned začne formátovat—tučné záhlaví, okraje, možná i nějaké barevné kódování. Působí to produktivně, ale většinou jde jen o estetiku. Místo toho se soustřeďte na strukturu. U téměř jakéhokoli datasetu, který plánujete udržovat, by měl být prvním krokem stisk Ctrl+T (nebo přechod na Vložit > Tabulka). Než se nadějete, proměníte statickou mřížku v tabulku Excelu: strukturovaný objekt, který chápe své vlastní hranice a chová se inteligentně, jak roste.

Pokud je váš datový soubor souvislý (bez prázdných řádků nebo sloupců), ani ho nemusíte nejprve vybrat. Excel dokáže zjistit, kde vaše data končí, a automaticky zachytí všechny příslušné buňky. Pokud však vaše data obsahují mezery, můžete nejprve ručně vybrat oblast, aby Excel zahrnul vše, ale to obvykle znamená, že struktura vašich dat potřebuje úpravu. Tabulky fungují nejlépe, když jsou vaše data pevný, souvislý blok.

Jakmile je tabulka připravena, hned ji přejmenujte na kartě Design tabulky. Něco jako T_Sales nebo T_Inventory může zpočátku působit trochu formálně, ale rychle se to vyplatí. Když budete později psát vzorce, nebudete hádat, co "Table1" obsahuje. A pokud ji později znovu přejmenujete, Excel aktualizuje všechny odkazy.

Strukturované odkazy usnadňují čtení a údržbu vzorců

Piš vzorce jako člověk

Tradiční vzorce se mohou rychle stát nepřehlednými. Vzorec jako:

=H2-I2

téměř nic vám neřekne o významu - jsou to jen souřadnice. Tabulky tento problém řeší nahrazením surových odkazů na buňky strukturovanými odkazy.

Když zadáte = a kliknete na buňku ve vaší tabulce, Excel už nezobrazuje „H2“—zobrazí něco jako [@Sales], kde symbol @ říká Excelu, aby použil hodnotu ze stejného řádku v tomto sloupci.

Než se nadějete, máte vzorec, který je mnohem čitelnější:

=[@Sales]-[@COGS]

Jakmile dokončíte psaní svého vzorce, stiskněte Enter a Excel okamžitě vyplní celý sloupec vaším výpočtem. Pryč jsou dny dvojitého klikání na úchyt pro vyplnění nebo tažení vzorce až do řádku 5 000.

Odkazování na data z tabulky z jiných listů

Používejte názvy tabulek k vytváření čistších globálních souhrnů

Logika tabulek v Excelu se stává ještě silnější, když přejdete na jiný list, abyste vytvořili souhrn nebo dashboard. V běžném nastavení byste mohli napsat něco jako:

=SUM(Sheet1!J2:J5000)

Je to křehké, těžko udržitelné a rozbije se ve chvíli, kdy přeuspořádáte tabulku.

S pojmenovanou tabulkou můžete jednoduše použít:

=SUM(T_Sales[Profit])

kdekoli v sešitu. Proto je tak důležité pojmenovat svou tabulku - přeměňuje vaše data na pojmenovaný rozsah, kterému Excel rozumí globálně. Přestanete se obávat, na kterém listu se data nacházejí, a začnete svou tabulku považovat za skutečnou databázi. Pokud do té tabulky přidáte 1 000 řádků, vaše shrnující vzorce na jiném listu se automaticky aktualizují, protože se dívají na název sloupce, nikoli na statický rozsah buněk.

Tabulky Excelu se automaticky rozšiřují tak, aby zahrnovaly nové řádky a logiku

Přestaňte pořád převádět vzorce a znovu aplikovat nastavení

Tady se stává, že se tabulky mění z trikového prvku na skutečně užitečný nástroj, který transformuje způsob, jakým pracujete. Tabulky jsou živé kontejnery. Když pod ně nebo vedle nich přidáte data, samy se rozšiřují. Pokud zadáváte data řádek po řádku, stisknutí Tab v poslední buňce tabulky okamžitě vytvoří zcela nový řádek, který je již propojen s vaší stávající logikou.

Doslova všechno se přenáší dál—vaše interní vzorce, formátování čísel, podmíněné formátování a pravidla ověřování dat se rozšiřují na nové řádky, aniž byste se něčeho dotkli. V běžném rozsahu pracovního listu se právě tyto věci obvykle porouchají, protože jejich základní nastavení závisí na statických odkazech na buňky. V tabulce se však struktura jednoduše rozšiřuje.

Vestavěný souhrnný řádek v Excelu vám umožňuje analyzovat data bez psaní vzorců

Okamžitá matematika s téměř nulovým úsilím

Tabulky obsahují tichou, ale silnou funkci: řádek součtů. Aktivujte jej na kartě Návrh tabulky a Excel přidá na konec vyhrazený řádek se souhrnem.

Místo psaní ručních vzorců můžete okamžitě přepínat mezi Průměr, Počet, Maximum nebo Minimum. Dokonce můžete získat přístup k pokročilejším výpočtům, jako je směrodatná odchylka, kliknutím na Další funkce.

Pro jednoduché součty tento systém ve výchozím nastavení používá funkci SUBTOTAL. Na rozdíl od standardního vzorce SUM, který vždy počítá všechno v rozsahu, řádek se součtem vypočítá pouze to, co je viditelné, když jsou použity filtry.

Používání tabulek jako dynamického centra pro další nástroje Excelu

Dynamicky propojte svá data s grafy, kontingenčními tabulkami a Power Query

Ať už vytváříte jednoduchý sloupcový graf nebo složitou kontingenční tabulku, použití tabulky jako zdroje zajišťuje, že vše zůstane synchronizované. Když ostatní objekty odkazují na název tabulky místo na rozsah buněk, automaticky se rozšiřují, jak data tabulky rostou, takže nemusíte ručně aktualizovat zdrojový rozsah pokaždé, když přidáte nový týden prodeje.

Tato spolehlivost se rozšiřuje i na nástroje náročnější na výkon, jako jsou Power Query a Power Pivot. Protože tabulky poskytují čisté, pojmenované připojení, mohou tyto nástroje vaše data "uchopit" bez jakýchkoli nejasností. Dostanete jasná připojení, konzistentní odkazy a mnohem méně problémů s neplatnými rozsahy.

Stoly jsou skvělé, ale všechny skvělé věci mají výjimky

Vyvinutí zvyku nejprve používat tabulky zajišťuje, že vaše listy fungují jako hodinky, ale není to univerzální řešení. Pro jednorázovou logiku nebo při použití dynamických polí, která se musí volně rozlévat do prázdných buněk, může být standardní rozsah ve skutečnosti lepší volbou. Abyste své sešity opravdu optimalizovali, stojí za to vědět, kdy Excel tabulky nepoužívat, abyste mohli vyvážit automatizaci s flexibilitou.