Tweet

POSUN - (OFFSET) - funkce Excel

Úvodem do funkce POSUN (OFFSET)

V tomto článku Vás podrobněji seznámím s vyhledávací a referenční funkci POSUN (v anglické verzi Microsoft Excelu OFFSET).

• TEORIE
  • Teoretický úvod - POSUN (OFFSET)
  • Syntaxe funkce POSUN
• Prakticky se sloupci
  • Výpočty ve sloupcích - volba sloupce
  • Dynamická výška sloupce - výpočet průměru z posledního týdne
  • Opakovaná dynamická velikost sloupce
• Prakticky s řádky
  • Výpočty v řádcích - výpočty pro dynamicky voleného řádku.
  • Ve spojení se SUMA, MIN, MAX
• Prakticky s oblastmi
  • Výpočet v oblasti - výpočty dynamicky měněné oblasti.
  • Ve spojení se SUMA, MIN, MAX
• Kombinace s jinými funkcemi
  • s POZVYHLEDAT
  • s ŘÁDKY - potřebuji každý druhý (x-tý)
• Ke stažení zdarma
• Další náměty a nápady
• Související funkce

Teoretický úvod pro funkce POSUN (OFFSET)

Syntaxe česky

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

Syntaxe anglicky

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

Popis atributů

• Odkaz - odkaz na buňku, vůči které provádíte posun. Pokud odkaz není tvořen odkazem na buňku nebo oblast sousedících buněk, vrátí funkce POSUN chybovou hodnotu #HODNOTA!. Pro odkaz lze Použít funkce NEPŘÍMÝ.ODKAZ(odkaz).
• Řá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. Můžete použít kladnou (dolů od původního odkazu) nebo zápornou hodnotu (nahoru od 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). Přesáhne-li takto posunutý odkaz okraje listu, vrátí funkce POSUN chybovou hodnotu #REF!.
• Výška - Nepovinné - je požadovaná výška (počet řádků) výsledného odkazu. Výška je vždy kladné číslo.
• Šířka - Nepovinné - je požadovaná šířka (počet sloupců) výsledného odkazu. Šířka je vždy kladné číslo.

Poznámky:

• Vynecháte-li argument výška nebo šířka, implicitně se dosadí výška nebo šířka původního odkazu.
• Funkce POSUN (OFFSET) ve skutečnosti žádné buňky nepřesunuje ani nemění označenou oblast; pouze vrátí hodnotu typu odkaz.
• Při odkazu na jiný sešit, musí být pro správnou funkci tento sešit otevřen (jde o vlastnost Excel).

Jak může vypadat

Neboli

Zpět na seznam kapitol.

Syntaxe použití funkce

Jak použít funkci POSUN

Příklady

POSUN(C3;2;3;1;1)

rovná se F5. Zadáte-li tento vzorec na listu, zobrazí aplikace Microsoft Excel hodnotu uloženou v buňce F5.

POSUN(C3:E5;-1;0;3;3)

rovná se C2:E4

POSUN(C3:E5;0;-3;3;3)

rovná se #REF!

Zpět na seznam kapitol.

Volba sloupce - Výpočty ve sloupcích

Potřebujete-li dynamicky zvolit sloupce pro výpočet.

V ukázce je použít výpočet sumy SUMA. Použít lze i jiné matematické funkce, jako průměr, max, min, hodnoty, atd.

=SUMA(POSUN(B22;0;H24;3;1))

Poznámka: V tabulce je použito navíc podmíněné formátování, kter ukazuje který sloupce je právě vybrán.

Ke stažení

Soubor Praktická použití funkce POSUN pro sloupce ve spojení s funkci SUMA - ke stažení zdarma.

Zpět na seznam kapitol.

Dynamická výška sloupce

Potřebujete-li dynamicky zvolit sloupce pro výpočet. Tj. u sloupce dynamicky měnit polohu (výšku). Z předchozí kapitoly už umíte pracovat s řádkem. Teď ukáží jak k němu přidat další dynamickou volbu (jeho poloha).

V ukázce je použít výpočet sumy SUMA. Použít lze i jiné matematické funkce, jako průměr, max, min, hodnoty, atd.

=PRŮMĚR(POSUN(C10;POČET(C10:C30)-7;0;7;1))

Poznámky:

• Výpočet může být doplněn o dynamickou volbu výšky řádku, atd. Což už nechávám na čtenářích.
• V tabulce je použito navíc podmíněné formátování, který ukazuje který sloupce je právě vybrán.

Ke stažení

Soubor Praktická použití funkce POSUN pro dynamický sloupec. ve spojení s funkci SUMA - ke stažení zdarma.

Zpět na seznam kapitol.

Výpočty v řádcích

Potřebujete-li dynamicky zvolit pro výpočet řádky.

V ukázce je použit výpočet sumy (využitím funkce SUMA). Použít lze i jiné matematické funkce (podobně jako v předchozí ukázce), např. průměr, max, min, hodnoty, atd.

=SUMA(POSUN(B28;H28;1;1;4))

Poznámka: V tabulce je použito podmíněné formátování, které ukazuje který sloupc je právě vybrán.

Ke stažení

Soubor Praktická použití funkce POSUN pro řádky ve spojení s funkci SUMA - ke stažení zdarma.

Zpět na seznam kapitol.

Výpočet v oblasti

Potřebujete-li dynamicky provádět výpočty v oblasti buněk. Vychází z předchozích ukázek pro řádky a sloupce. Pro doplnění a dokreslení je zmíněno.

V ukázce je použít výpočet sumy pomocí funkce SUMA, PRŮMĚR, a nalezení maximální hodnoty MAX. Použít lze i jiné matematické funkce.

=SUMA(POSUN(B37;0;1;H38;H40))

Poznámka: V tabulce je použito podmíněné formátování, které ukazuje která oblast je právě vybrána.

Ke stažení

Soubor Praktická použití funkce POSUN pro oblast ve spojení s funkci SUMA - ke stažení zdarma.

Další náměty

K řešení 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. Děkuji Pepe za nápad odkaz.

Zpět na seznam kapitol.

Oblasti o určité velikosti

Z komentáře

Dobrý den, prosím o pomoc, zasekla jsem se u jedné věci. Nevíte někdo, jak udělat průměry vždy 4 čísel pod sebou v jednom sloupci a udělat z toho druhý sloupec? Poznámka: Ve sloupci A mám 4 údaje pro každou hodinu a potřebuji 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. Potřebuji, aby se mi oblast buněk, která se průměruje posouvala o 4 místa dolu, ale když rozkopíruji vzorec do celého sloupce, oblast buněk se posouvá vždy jen o jeden řádek. Bez použití kontingence (která z důvodu nutné aktualizace vždy při změně vstupních dat není vhodná), jde to? Předem děkuji za pomoc. Adéla

Řešení

Řešení. Pro jistotu jsem udělal dynamickou volbu počtu řádku (ať lze zvolit, jak velká má být oblast zda 4, nebo jako v mém případě 6). V emailu byl podobný dotaz na oblast o 10-ti prvcích ;)

Pro SUMU

=SUMA(POSUN($B$7;((D8-1)*$E$4)+1;0;$E$4;1))

Pro PRŮMĚR

=PRŮMĚR(POSUN($B$7;((D8-1)*$E$4)+1;0;$E$4;1))

Ukázka

Ke stažení

Soubor Praktická řešení za použití funkce POSUN - ke stažení zdarma.

Další náměty a nápady

Využití funkce POSUN ve spojení s dalšími funkcemi například PRŮMĚR, MEDIAN, MIN, MAX, atd.

=SUMA(POSUN(B3;0;1;1;4))

Poznámka: Z tohoto příkladu se může zdát, že použití funkce SUMA:

=SUMA(C3:E3)

je rychlejší. Což máte pravdu, ale pokud máte složitější tabulky, kdy nevíte, kolik řádku a sloupců máte, je tato metoda pro implementaci jednodušší (přes proměné pouze posunete na správný řádek/sloupec a ohraničíte požadovaný počet/ velikost oblasti). Změnou jednoho parametru můžete automaticky změnit oblast kterou pak automaticky spočtete.

MAX, MIN

Podobně můžete použít ve spojení s funkcemi MIN a MAX. Kdy nalezne minimum, maximum v danné oblasti.

PRŮMĚR, MEDIÁN

Postupně doplním i další praktické ukázky...

Ke stažení

Soubor Praktické použití funkce POSUN - ke stažení zdarma.

Zpět na seznam kapitol.

POSUN s POZVYHLEDAT

Potřebuji z tabulky získat průsečík hodnt. Máte tabulku regiony a roky, s údají o prodejích. Výběrem regionu a roku je potřeba do buňky získat daný průsečik.

Do pomocných buněk zadáte region a požadovaný rok:

=POSUN(A4;POZVYHLEDAT(B13;A5:A9;0);POZVYHLEDAT(B14;B4:E4;0))

Ukázka

Ke stažení

Soubor Praktické použití POSUN s POZVYHLEDAT - ke stažení zdarma.

Poznámka: Lze řešit i pomocí funkce SVYHLEDAT.

POSUN s ŘÁDKY

Ze zdrojové tabulky potřebuji získat každou druhou hodnotu. Případně každou x-tou.

=POSUN($B$6;ŘÁDKY($B$6:B6)*2;0)

Ukázka

Ukázka pro každou x-tou

Ke stažení

Soubor Praktické použití POSUN s ŘÁDKY ve spojení s funkci SUMA - ke stažení zdarma.

Ukázky ke stažení zdarma

Sešity s praktickými ukázkámi použití funkce POSUN v Microsoft Excel si můžete stáhnout zdarma:

• Praktické ukázky použití funkce POSUN
• Praktické použití POSUN s ŘÁDKY
• Praktické použití funkce POSUN
• Praktické použití funkce POSUN pro řádky

Související funkce

Další vyhledávací funkce jsem popsal v článku: Odkaz na seznam dalších vyhledávacích funkcí. K dispozici jsou tyto funkce:

• FORMULATEXT - (FORMULATEXT) - od Excel 2013
• HYPERTEXTOVÝ.ODKAZ - (HYPERLINK)
• INDEX - (INDEX)
• NEPŘÍMÝ.ODKAZ - (INDIRECT)
• ODKAZ - (ADDRESS)
• POČET.BLOKŮ - (AREAS)
• POSUN - (OFFSET) - popsána v tomto článku
• POZVYHLEDAT - (MATCH)
• RTD - (RTD)
• ŘÁDEK - (ROW)
• ŘÁDKY - (ROWS)
• SLOUPCE - (COLUMNS)
• SLOUPEC - (COLUMN)
• SVYHLEDAT - (VLOOKUP)
• TRANSPOZICE - (TRANSPOSE)
• VVYHLEDAT - (HLOOKUP)
• VYHLEDAT - (LOOKUP)
• ZÍSKATKONTDATA - (GETPIVOTDATA)
• ZVOLIT - (CHOOSE)

Zpět na seznam kapitol.

Závěrem

Napadá Vás něco k funkci POSUN? Do komentářů můžete doplnit své postřehy, ukázky použití funkce, atd.

Zpět na seznam kapitol

Článek byl aktualizován: 19.09.2020 11:00

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře