Microsoft Excel neustále přichází s novinkami, které nám usnadňují práci a šetří čas. Jednou z nedávných revolučních funkcí, o které se hodně mluví, je OŘÍZNOUT.OBLAST (anglicky TRIMRANGE). Tato funkce elegantně řeší problém, se kterým se mnozí z nás potýkali: jak dynamicky pracovat s oblastmi dat bez zobrazení nadbytečných prázdných buněk (často reprezentovaných nulami). Spolu s ní přišla i další šikovná možnost – ořezávání oblastí pomocí teček přímo ve vzorci. Pojďme se na obě novinky podívat podrobněji.

Problém nadbytečných buněk

Představte si, že máte seznam dat (např. produkty, jména, hodnoty) v jednom sloupci a chcete se na něj odkázat v jiné části listu nebo ve vzorci. Zároveň očekáváte, že se původní seznam bude rozšiřovat. Běžným přístupem bylo odkázat se na větší oblast, než je aktuálně zaplněná (např. A2:A1000, i když data končí na A50). Problémem tohoto přístupu je, že vzorce pracující s touto oblastí často vracely nuly nebo chyby pro prázdné buňky v definovaném rozsahu.

Doposud jsme to řešili složitějšími metodami:

1. Kombinace funkcí: Použití funkce FILTR k odstranění nul (=FILTR(A2:A1000; A2:A1000<>0)).
2. Dynamické pojmenované oblasti: Vytváření pojmenovaných oblastí pomocí funkcí jako POSUN (OFFSET) a POČET2 (COUNTA), které jsou ale volatilní (přepočítávají se při každé změně v sešitu) a mohou zpomalovat výpočty.
3. Index a Počet2: Kombinace funkcí INDEX a POČET2 pro definování přesného rozsahu.

Nyní máme k dispozici mnohem jednodušší a elegantnější řešení.

Funkce OŘÍZNOUT.OBLAST (TRIMRANGE)

Tato funkce slouží k odstranění nadbytečných prázdných řádků a sloupců z definované oblasti. Vrátí pouze tu část oblasti, která obsahuje data.

Syntaxe:

OŘÍZNOUT.OBLAST(oblast; [oříznutí_řádku]; [oříznutí_sloupce])

• oblast (povinný): Oblast buněk, kterou chcete oříznout.
• [oříznutí_řádku] (nepovinný): Určuje, jak se mají oříznout řádky. Možnosti jsou:
  • 0: Neořezávat řádky.
  • 1: Oříznout řádky shora (odstraní prázdné řádky nad daty).
  • 2: Oříznout řádky zdola (odstraní prázdné řádky pod daty).
  • 3 (výchozí): Oříznout řádky shora i zdola.
• [oříznutí_sloupce] (nepovinný): Určuje, jak se mají oříznout sloupce. Možnosti jsou stejné jako u řádků, ale aplikují se na sloupce (zleva, zprava, obojí, nebo vůbec).
  • 0: Neořezávat sloupce.
  • 1: Oříznout sloupce zleva.
  • 2: Oříznout sloupce zprava.
  • 3 (výchozí): Oříznout sloupce zleva i zprava.

Základní použití (oříznutí zdola):

Mějme seznam produktů ve sloupci A (řekněme A2:A10). Chceme vytvořit dynamický odkaz na tento seznam, který se automaticky rozšíří, pokud přidáme další produkt, ale nezobrazí nuly z prázdných buněk, pokud se odkážeme na větší rozsah (např. A2:A20).

1. Do buňky, kde chcete zobrazit seznam, napíšete =A2:A20. Výsledkem bude seznam produktů a pod ním nuly.
2. Nyní tento vzorec „zabalíme“ do funkce OŘÍZNOUT.OBLAST: Excel=OŘÍZNOUT.OBLAST(A2:A20)
3. Potvrdíte Enterem. Funkce automaticky odstraní řádky obsahující nuly (tedy ty prázdné v původní oblasti A2:A20) a zobrazí pouze seznam produktů.
4. Když do sloupce A přidáte nový produkt (např. do buňky A11), seznam vytvořený funkcí OŘÍZNOUT.OBLAST se automaticky aktualizuje a nový produkt zahrne.

Použití s parametry (oříznutí shora i zdola):

Pokud máte data, která mohou mít prázdné řádky jak nad skutečným seznamem, tak pod ním (např. data jsou v A5:A15, ale vy se odkazujete na A2:A20), můžete použít výchozí nastavení nebo explicitně zadat parametry.

=OŘÍZNOUT.OBLAST(A2:A20)

nebo

=OŘÍZNOUT.OBLAST(A2:A20; 3)

Oba vzorce odstraní prázdné řádky nad i pod daty v rozsahu A2:A20. Pokud byste chtěli odstranit jen řádky nad daty, použili byste:

=OŘÍZNOUT.OBLAST(A2:A20; 1)

A pro odstranění řádků jen pod daty:

=OŘÍZNOUT.OBLAST(A2:A20; 2)

Stejná logika platí i pro horizontální data (v řádcích) a parametr [oříznutí_sloupce].

Nový způsob: Ořezávání oblastí pomocí teček

Spolu s funkcí OŘÍZNOUT.OBLAST přibyla do Excelu (pro uživatele Microsoft 365) ještě jedna fascinující možnost – oříznutí oblasti přímo v odkazu pomocí tečky (.) umístěné před nebo za dvojtečkou (:).

Tento zápis funguje jako zkratka pro specifické typy oříznutí:

• Tečka za dvojtečkou (A2:A.20): Ořízne oblast zdola (odpovídá parametru oříznutí_řádku = 2 nebo oříznutí_sloupce = 2). Odstraní prázdné buňky na konci rozsahu.
• Tečka před dvojtečkou (A2:.A20): Ořízne oblast shora (odpovídá parametru oříznutí_řádku = 1 nebo oříznutí_sloupce = 1). Odstraní prázdné buňky na začátku rozsahu.
• Tečka před i za dvojtečkou (A2:.A.20): Ořízne oblast shora i zdola (odpovídá parametru oříznutí_řádku = 3 nebo oříznutí_sloupce = 3). Odstraní prázdné buňky na začátku i na konci rozsahu.

Příklad použití teček:

Vraťme se k příkladu se seznamem produktů v A2:A10, kde se odkazujeme na A2:A20.

1. Místo =OŘÍZNOUT.OBLAST(A2:A20; 2) nebo =OŘÍZNOUT.OBLAST(A2:A20) (pokud nejsou prázdné řádky nahoře), můžeme napsat jednoduše: Excel=A2:A.20 Tento zápis automaticky ořízne prázdné buňky (nuly) na konci seznamu.
2. Pokud by data byla v A5:A15 a my se odkazovali na A2:A20 a chtěli odstranit prázdné řádky nahoře i dole, použili bychom: Excel=A2:.A.20

Tento způsob je velmi rychlý a intuitivní pro základní scénáře oříznutí.

Kombinace s jinými funkcemi (např. XLOOKUP)

Síla funkce OŘÍZNOUT.OBLAST i tečkového zápisu se projeví i v kombinaci s jinými funkcemi, například s vyhledávací funkcí XLOOKUP.

Představte si, že máte tabulku produktů a jejich slev (např. produkty v A2:A10, slevy v B2:B10). Chcete pomocí XLOOKUP dohledat slevu k seznamu produktů, který máte jinde (např. v D2:D5), ale opět předpokládáte, že zdrojová tabulka se může rozrůst, a proto ve funkci XLOOKUP odkážete na větší rozsahy (např. A2:A20 a B2:B20).

=XLOOKUP(D2:D5; A2:A20; B2:B20)

Pokud jsou buňky A11:A20 prázdné, XLOOKUP může pro některé hledané hodnoty vrátit chybu #N/A nebo jiné neočekávané výsledky, protože prohledává i prázdné buňky.

Řešení pomocí OŘÍZNOUT.OBLAST:

Můžete oříznout prohledávanou oblast i oblast vrácených hodnot:

=XLOOKUP(D2:D5; OŘÍZNOUT.OBLAST(A2:A20); OŘÍZNOUT.OBLAST(B2:B20))

(Poznámka: Často stačí oříznout jen prohledávanou oblast lookup_array)

=XLOOKUP(D2:D5; OŘÍZNOUT.OBLAST(A2:A20); B2:B20)

Řešení pomocí teček:

Pokud víte, že prázdné buňky budou pouze na konci (pod daty), můžete použít tečkový zápis:

=XLOOKUP(D2:D5; A2:A.20; B2:B.20)

(Opět, často stačí oříznout jen lookup_array)

=XLOOKUP(D2:D5; A2:A.20; B2:B20)

Oba přístupy zajistí, že XLOOKUP bude pracovat pouze s relevantní částí tabulky obsahující data, a přitom zůstane dynamický pro případné rozšíření zdrojových dat.

Časté otázky (FAQ)

Q: Kdy použít funkci OŘÍZNOUT.OBLAST a kdy stačí tečky? A: Tečkový zápis (A2:A.20, A2:.A20, A2:.A.20) je rychlejší a přehlednější pro základní oříznutí shora, zdola nebo obojí. Funkci OŘÍZNOUT.OBLAST použijte, pokud potřebujete jemnější kontrolu (např. oříznout pouze řádky, ale ne sloupce, nebo naopak) nebo pokud chcete, aby byl vzorec explicitnější v tom, co dělá. Tečky také nemusí být vždy intuitivní pro ostatní uživatele.

Q: Je tato funkce a tečkový zápis dostupný ve všech verzích Excelu? A: Ne, funkce OŘÍZNOUT.OBLAST a možnost použití teček v odkazech jsou dostupné pouze pro předplatitele Microsoft 365. Ve starších verzích Excelu (např. Excel 2019, 2016) tyto možnosti nenajdete.

Q: Jaké jsou hlavní výhody oproti starším metodám (POSUN, FILTR)? A: Hlavní výhody jsou jednoduchost, přehlednost a výkon. Funkce OŘÍZNOUT.OBLAST není volatilní jako funkce POSUN, takže nezpomaluje přepočet sešitu. Oproti funkci FILTR je často jednodušší na zápis, pokud jde čistě o odstranění prázdných buněk na okrajích oblasti. Tečkový zápis je pak nejstručnější variantou.

Q: Funguje to i na data s prázdnými řádky/sloupci uprostřed? A: Ne, funkce OŘÍZNOUT.OBLAST i tečkový zápis odstraňují pouze souvislé prázdné řádky a sloupce na okrajích definované oblasti. Pokud máte prázdný řádek uprostřed dat (např. data v A2, A3, pak prázdná A4, a data pokračují v A5), tento prázdný řádek zůstane. Pro odstranění prázdných řádků uvnitř oblasti je stále vhodnější použít funkci FILTR.

Q: Co se stane, když odkazovaná oblast neobsahuje žádná data? A: Pokud je celá oblast, na kterou se odkazujete pomocí OŘÍZNOUT.OBLAST nebo tečkového zápisu, prázdná, funkce vrátí chybu #CALC!.

Závěr

Funkce OŘÍZNOUT.OBLAST a možnost používat tečky v odkazech na oblasti představují významné zjednodušení práce s dynamickými rozsahy v Excelu. Zbavují nás nutnosti používat složité a někdy i výkonnostně náročné konstrukce a umožňují vytvářet přehlednější a efektivnější vzorce. Pokud máte Microsoft 365, rozhodně doporučujeme tyto novinky vyzkoušet a zařadit do svého arzenálu excelových nástrojů.