Funkce POSUN v Excelu: Posouvání buněk a dynamické oblasti

Funkce POSUN patří mezi vyhledávací funkce Excelu a běžnými uživateli je neprávem opomíjena. Jak funguje v Microsoft Excelu funkce POSUN (OFFSET) a jak ji prakticky využít? Vrátí odkaz na oblast, která obsahuje určený počet řádků a sloupců, posunut od zadané buňky (nebo oblasti buněk). Funkce POSUN ve skutečnosti žádné buňky nepřesunuje ani nemění označenou oblast; pouze vrátí hodnotu typu odkaz. Funkci POSUN lze použít ve spojení s libovolnou funkcí, která očekává argument typu odkaz.

Argumenty funkce POSUN

Funkce POSUN má několik klíčových argumentů:

  • Odkaz: Odkaz na buňku, vůči které provádíte posun.
  • Řádky: Je počet řádků, o které se má posunout levá horní buňka nového odkazu (nahoru nebo dolů). Zadáte-li například číslo 5, levá horní buňka odkazu bude pět řádků pod levou horní buňkou původního odkazu.
  • Sloupce: Je počet sloupců vlevo nebo vpravo, o které se má posunout levá horní buňka výsledného odkazu vzhledem k původnímu odkazu. Zadáte-li například číslo 5, bude levá horní buňka odkazu o pět sloupců vpravo od levé horní buňky původního odkazu. Můžete použít kladnou (posun doprava od původního odkazu) i zápornou hodnotu (posun doleva od původního odkazu).
  • Výška: (Nepovinné) Je požadovaná výška (počet řádků) výsledného odkazu.
  • Šířka: (Nepovinné) Je požadovaná šířka (počet sloupců) výsledného odkazu.

Pokud se odkaz přesune za okraj listu, vrátí funkce POSUN chybovou hodnotu #HODNOTA!.

Praktické využití funkce POSUN

Dynamický graf

Dalším příkladem využití funkce POSUN může být dynamický graf. Představte si tabulku, ve které máte záznamy z každého dne. Graf, který vychází z takové tabulky může mít na ose X až 365 hodnot a tím se pak graf stává prakticky nečitelný. V tomto případě využijeme funkce POSUN k definování dynamické oblasti, která bude mít dva parametry. První bude lupa a druhý posun.

Dynamický sloupec a výpočty

Potřebujete-li dynamicky zvolit sloupce pro výpočet, tj. u sloupce dynamicky měnit polohu (výšku), můžete funkci POSUN využít. Z předchozí kapitoly už umíte pracovat s řádkem. Výpočet může být doplněn o dynamickou volbu výšky řádku, atd. Praktická použití funkce POSUN pro dynamický sloupec ve spojení s funkcí SUMA je ke stažení zdarma. V ukázce je použit výpočet sumy (využitím funkce SUMA). Použít lze i jiné matematické funkce, např. ve spojení s funkcemi MIN a MAX.

Čtěte také: vytvoření výběrového seznamu v buňce

Dynamické výpočty v oblasti buněk

Potřebujete-li dynamicky provádět výpočty v oblasti buněk, můžete využít funkci POSUN. Vychází z předchozích ukázek pro řádky a sloupce. V ukázce je použit výpočet sumy pomocí funkce SUMA, PRŮMĚR, a nalezení maximální hodnoty MAX ve spojení s funkcí SUMA.

Průměrování 4 čísel pod sebou

Příklad řešení situace, kdy potřebujete průměrovat vždy 4 čísla pod sebou v jednom sloupci a udělat z toho druhý sloupec: Ve sloupci A máte 4 údaje pro každou hodinu a potřebujete tyto 4 hodnoty zprůměrovat a udělat nový sloupec B, kde bude vždy 1 hodnota (průměrná) pro každou hodinu. Je potřeba, aby se oblast buněk, která se průměruje, posouvala o 4 místa dolů, ale když rozkopírujete vzorec do celého sloupce, oblast buněk se posouvá vždy jen o jeden řádek. Řešení pro jistotu umožnilo dynamickou volbu počtu řádků (ať lze zvolit, jak velká má být oblast zda 4, nebo jako v mém případě 6) a je rychlejší a implementaci jednodušší (přes proměnné pouze posunete na správný řádek/sloupec a ohraničíte požadovaný počet/velikost oblasti).

Získání průsečíku hodnot z tabulky

Potřebujete-li z tabulky získat průsečík hodnot, například z tabulky regionů a roků s údaji o prodejích, lze funkci POSUN použít. Stejně tak lze ze zdrojové tabulky získat každou druhou hodnotu.

Alternativa k funkci POSUN

K řešení některých úloh lze využít i funkci INDEX. Jak toto provést je uvedeno v článku "Jak využít INDEX ve spojení se SUMA, PRŮMĚR, MIN, MAX - při hledání v dynamické oblasti".

Formátování buněk

Pokud vkládáme nový sloupec, vloží se vlevo od vybraného sloupce. Označíme sloupec, vedle kterého chceme vložit sloupec nový. Pomocí pravého tlačítka myši zvolíme možnost Vložit buňky. Dále určíme, jakým způsobem chceme nové buňky vložit. Je také možné vkládat nikoliv celý sloupec či řádek, ale jen několik buněk. Podobně můžeme buňky, řádky nebo sloupce odstranit. Označíme buňky, které chceme odstranit, pravým tlačítkem myši zvolíme možnost Odstranit a určíme, jakým způsobem chceme buňky odstranit.

Čtěte také: Lepením lamina krok za krokem

Úprava výšky řádku a šířky sloupce

Někdy je potřeba změnit výšku řádků či šířku sloupců. V případě, že pracujeme např. ve skupině Buňky, klikneme na tlačítko Formát. Otevře se dialogové okno, kde můžeme nastavit výšku řádku. Po kliknutí na Přizpůsobit výšku řádků se výška řádku přizpůsobí obsahu. Pro sloupce máme možnost nastavit šířku sloupce nebo se vrátit k výchozí šířce, kterou Excel při spuštění automaticky nastavil. Pro úpravu šířky sloupce můžeme také táhnout mezi záhlavím sloupců (např. mezi písmena D a E).

Čtěte také: Vše o příchytkách na DIN lištu

tags: #jak #v #excelu #posunout #levou #listu

Oblíbené příspěvky: