V tomto návodu se podrobně zaměříme na funkci POSUN, známou také jako OFFSET, v prostředí Microsoft Excel. Naučíte se, jak využít tuto funkci pro vytváření dynamických referencí, které se automaticky přizpůsobují vašim datům. Funkce POSUN je dostupná ve všech verzích Excelu od 2016 a je neocenitelným nástrojem pro zvyšování pracovní produktivity.

Co je funkce POSUN (OFFSET)?

Funkce POSUN vrátí odkaz na oblast buněk, která je určena posunem od zvolené buňky. Tento posun je definován počtem řádků a sloupců, které funkce posune od styčného bodu. Výsledkem může být odkaz na jednu buňku nebo na celou oblast buněk. Funkce POSUN je klíčová pro tvorbu dynamických funkcí, jejichž výsledky se automaticky přizpůsobují změnám ve vašich datech.

Jak používat funkci POSUN – Základní příklad

Představme si jednoduchou tabulku s hodnotami rozloženými ve čtyřech sloupcích a pěti řádcích. Potřebujeme získat hodnotu na základě dynamicky měněných koordinací řádku a sloupce.

Krok za krokem:

1. Výchozí bod: Označte první buňku v záhlaví tabulky jako styčný bod.
2. Parametry funkce POSUN:
   • Odkaz: Styčný bod (např. A1).
   • Počet řádků: Hodnota určená v barevné buňce (např. 2 řádky dolů).
   • Počet sloupců: Hodnota určená v barevné buňce (např. 3 sloupce doprava).
3. Vstup funkce: Do cílové buňky vložíte funkci POSUN s těmito parametry.
4. Výsledek: Funkce vrátí hodnotu z buňky D5 (pokud bylo posunuto o 2 řádky a 3 sloupce).

Příklad:

=POSUN(A1; 2; 3)

Tato funkce začne v buňce A1, posune se o 2 řádky dolů do buňky A5 a následně o 3 sloupce doprava do buňky D5, kde vrátí hodnotu.

Použití funkce POSUN s negativními hodnotami

Funkce POSUN může také pracovat s negativními hodnotami, což umožňuje posun nahoru a doleva od styčného bodu.

Příklad:

Chceme získat hodnotu z buňky D15, což vyžaduje posun o 3 řádky nahoru a 2 sloupce doleva od poslední buňky v tabulce.

=POSUN(D20; -3; -2)

Tato funkce začne v buňce D20, posune se o 3 řádky nahoru do buňky D17 a následně o 2 sloupce doleva do buňky B17, kde vrátí hodnotu.

Pokročilé příklady – Využití nepovinných parametrů

Funkce POSUN umožňuje využití dvou nepovinných parametrů: výška a šířka, které definují velikost oblasti, kterou funkce vrátí.

Příklad: Sčítání částí tabulky

Máme tabulku s několika tržbami a chceme dynamicky sečíst určité množství těchto tržeb.

1. Styčný bod: První buňka v tabulce.
2. Počet řádků: 1 (tržby začínají o jeden řádek níže než záhlaví).
3. Počet sloupců: 2 (přesun o 2 sloupce doprava do sloupce tržeb).
4. Výška: Počet tržeb k sečtení (např. 3).
5. Šířka: Nepotřebujeme, ponecháme prázdné nebo specifikujeme dle potřeby.

=SUMA(POSUN(A1; 1; 2; 3; 1))

Tato funkce sečte první tři tržby ve sloupci, který je o dva sloupce doprava od styčného bodu.

Poznámka: Ve verzích Excelu bez podpory dynamických polí může tato funkce vrátit chybu. V takovém případě lze funkci zabalit například do funkce SUMA, aby se hodnoty správně agregovaly.

Použití funkce POSUN v dynamických grafech

Funkce POSUN je také velmi užitečná při tvorbě dynamických grafů, které se automaticky aktualizují podle vybraných dat.

Postup:

1. Styčný bod: Opět první buňka v tabulce, s plným zafixováním pomocí klávesy F4.
2. Řádky: Pomocí funkce POZVYHLEDAT (MATCH) určíme řádek na základě vybraného čtvrtletí.
3. Šířka: Dynamicky se mění podle počtu sloupců v datech.
4. Definování názvu: Vytvoříme dynamický rozsah pro graf pomocí správce názvů.
5. Vložený graf: Vytvoříme graf, který bude používat dynamický rozsah dat.

Tip: Používejte funkci F9 k ověření správnosti výsledků funkcí POSUN ve formuláři.

Tipy a upozornění

• Výkon: Funkce POSUN se neustále přepočítává, což může zpomalit váš sešit při práci s obrovskými datovými sadami. Používejte ji uvážlivě.
• Dynamické oblasti: Využívejte funkci POSUN pro tvorbu dynamických funkcí a grafů, které se automaticky aktualizují podle změn ve vašich datech.
• Kompatibilita: U starších verzí Excelu může POSUN vyžadovat úpravy nebo zabalování do agregačních funkcí, jako je SUMA.

Často kladené dotazy (FAQ)

1. Jaká je základní syntaxe funkce POSUN?

=POSUN(odkaz; řádky; sloupce; [výška]; [šířka])

• odkaz: Styčný bod pro posun.
• řádky: Počet řádků pro posun nahoru/down.
• sloupce: Počet sloupců pro posun doleva/doprava.
• výška (nepovinné): Počet řádků v návratovém rozsahu.
• šířka (nepovinné): Počet sloupců v návratovém rozsahu.

2. Co dělat, pokud funkce POSUN vrací chybu?

Pokud používáte starší verzi Excelu a POSUN vrací chybu, zkuste funkci zabalit do agregační funkce, například SUMA:

=SUMA(POSUN(odkaz; řádky; sloupce; výška; šířka))

3. Může funkce POSUN pracovat s dynamickými poli?

Ano, ve verzích Excelu podporujících dynamická pole (od Office 365 a Excel 2021) funkce POSUN plně využívá tuto schopnost a umožňuje vrátit dynamicky se měnící množství dat.

4. Jak využít funkci POSUN pro dynamické grafy?

Použijte funkci POSUN k definování dynamického rozsahu dat, který graf bude zobrazovat. Tento rozsah se bude automaticky aktualizovat podle vašich parametrů, jako je výběr čtvrtletí nebo počet tržeb.

5. Je možné použít funkci POSUN s podmíněným formátováním?

Ano, můžete použít podmíněné formátování v kombinaci s funkcí POSUN. Například můžete formátovat výsledek jako měnu pouze tehdy, pokud je vybrána konkrétní kategorie.