Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Jak funguje v Microsoft Excelu funkce POSUN (OFFSET) a jak ji prakticky využít? Funkci POSUN naleznete v kategorií: vyhledávací a referenční na kartě Vzorce.
Doplněno: 16.1.2016 o příklady POSUN s ŘÁDKY, POSUN s ŘÁDKY
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).
Vrátí odkaz na oblast, která obsahuje určený počet řádků a sloupců, posunut od zadané buňky (nebo oblasti buněk). Vrácený odkaz může být jedna buňka nebo oblast buněk.
POSUN(odkaz; řádky; sloupce; [výška]; [šířka])
OFFSET(odkaz; řádky; sloupce; [výška]; [šířka])
Jak použít funkci POSUN
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!
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.
Soubor
Praktická použití funkce POSUN pro sloupce
ve spojení s funkci SUMA - ke stažení zdarma.
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))
Soubor
Praktická použití funkce POSUN pro dynamický sloupec.
ve spojení s funkci SUMA - ke stažení zdarma.
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.
Soubor
Praktická použití funkce POSUN pro řádky
ve spojení s funkci SUMA - ke stažení zdarma.
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.
Soubor
Praktická použití funkce POSUN pro oblast
ve spojení s funkci SUMA - ke stažení zdarma.
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.
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í. 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
Soubor
Praktická řešení za použití funkce POSUN
- ke stažení zdarma.
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.
Podobně můžete použít ve spojení s funkcemi MIN a MAX. Kdy nalezne minimum, maximum v danné oblasti.
Postupně doplním i další praktické ukázky...
Soubor
Praktické použití funkce POSUN
- ke stažení zdarma.
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
Soubor
Praktické použití POSUN s POZVYHLEDAT
- ke stažení zdarma.
Poznámka: Lze řešit i pomocí funkce SVYHLEDAT.
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
Soubor
Praktické použití POSUN s ŘÁDKY
ve spojení s funkci SUMA - 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:
Další vyhledávací funkce jsem popsal v článku: Odkaz na seznam dalších vyhledávacích funkcí. K dispozici jsou tyto funkce:
Soubor
Jak na funkci POSUN (OFFSET) v pdf
ke stažení zdarma.
Napadá Vás něco k funkci POSUN? Do komentářů můžete doplnit své postřehy, ukázky použití funkce, atd.
Článek byl aktualizován: 24.03.2023 13:39
Pomohl vám článek? Vyřešili jste problém? Můžete mě podpořit zakoupení tabulky (samozdřejmě čokoládové), když kafe nepiji ;) Odkaz na zakoupení čokolády. Za veškerou podporu vám děkuji a samozdřejmě jí využiji do zdokonalování a rozšířování webu.
Případně přidejte odkaz na vaši oblíbenou sociální síť, případně využijste hashtag #JakNaExcel .
Děkuji za váš čas a doufám, že jste nalezli odpověď na svůj problém.
Narazili jste v článku na nejasnost, chybu? Máte tip na vylepšení nebo doplnění článku? Budu rád pokud se zmínite v komentářích.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). 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 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2025 |