Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » posun-offset-funkce-excel

POSUN - (OFFSET) - funkce Excel

Videokurzy Excel

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

Ú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).

MS Excel 2010 - POSUN OFFSET funkce

Teoretický úvod pro funkce POSUN (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.

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

MS Excel 2010 - POSUN OFSET funkce

Neboli

MS Excel 2010 - POSUN OFSET funkce

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.

MS Excel 2010 - POSUN OFSET funkce - výpočty

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 soubor ve formátu *.xlsx 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).

MS Excel 2010 - POSUN OFSET funkce - výpočty

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. soubor ve formátu *.xlsx 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.

MS Excel 2010 - POSUN OFSET funkce - výpočty

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 soubor ve formátu *.xlsx 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.

MS Excel 2010 - POSUN OFSET funkce - výpočty

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 soubor ve formátu *.xlsx 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 soubor ve formátu *.xls - 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 soubor ve formátu *.xlsx - 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 soubor ve formátu *.xls - 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 soubor ve formátu *.xls ve spojení s funkci SUMA - ke stažení zdarma.

Ukázky ke stažení zdarma

Microsoft Excel VBA - stahuj logo

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

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: 20.01.2017 09:01

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


BigBoss

Přidáno: 15.12.12 12:38

Perfektní návod a příklady. Tvé stránky jsou super.

Pavel Lasák

Přidáno: 15.12.12 16:41

To BigBoss: Jsem rád, že Ti stránky pomahají.

Petr Janes

Přidáno: 08.04.13 10:16

krasna vec, zase mi to usetri par zbytecnych chvilek v praci. Moc dik!

Pavel Lasák

Přidáno: 08.04.13 21:49

To Petr Janes: Rádo se stalo.

Marťa

Přidáno: 24.04.13 17:58

Perfektní vysvětlení s příklady. Díky

Vitek

Přidáno: 06.08.13 17:40

Vazne pekne zpracovane s priklady a perfektnimi ukazkami i slozitejsich aplikaci. Dekuju

Pavel Lasák

Přidáno: 11.08.13 22:18

To Marťa a Vitek: Rádo se stalo.

Tonda

Přidáno: 28.03.14 10:24

Díky, za ukázky funkce POSUN. Je fakt parádní.

Pavel Lasák

Přidáno: 28.03.14 20:31

To Tonda: A ve spojení s INDEX, POZVYHLEDAT, NEPŘÍMÝ.ODKAZ atd. jdou dělat další úžasné věci ;)

Tomas

Přidáno: 06.06.14 08:59

Pavle, fakt skvělý ! Smekám!

Adéla

Přidáno: 21.06.14 19:11

Geniální návod, konečně jsem pochopila tuto funkci. Díky za tento web a návody moc mi pomáhají. Adéla

Pavel Lasák

Přidáno: 23.06.14 06:59

To Adéla, Tomáš: Rádo se stalo, ať návody slouží.

Adéla

Přidáno: 13.02.15 09:20

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

Pavel Lasák

Přidáno: 19.04.15 09:44

To Adéla: řešení je doplněno v článku.

pepe

Přidáno: 30.06.15 10:41

Velmi dobre vysvetlena funkce Offset. Mozna by stalo za zminku zminit i non-volatile alternativu index, ktera je sice o neco malo slozitejsi, nicmene je vyrazne vhodnejsi pri velkem mnozstvi radku/sloupcu/funkci, protoze pokazde neprepocitava vzorce. Praci to pak vyrazne urychli.

Pepe

Přidáno: 01.07.15 16:13

Abych byl konkretni: napriklad vzorec z kapitoly "Vypocet v oblasti" =SUMA(POSUN(B37;0;1;H38;H40)) se da nahradit vzorcem =SUMA(C37:INDEX(C37:E39;H38,H40))

Pavel Lasák

Přidáno: 05.07.15 12:21

To Pepe: děkuji za doplnění, už je nově uvedeno u funkce INDEX, POZVYHLEDAT, kam se logicky hodí. Do článku jsem doplnil odkaz ať čtenáře mají možnost projít i další možné řešení.

David

Přidáno: 04.08.15 15:43

Diky za perfektni navod - mel jsem stejny problem jako kdysi Adela (prumerovani z vice hodnot - konkretne ze sloupce s tisici hodnotami vytvorit sloupec se 100 hodnotami, kde graf z nej je vyhlazenejsi). Sice se mi nepodarilo plne pochopit, jak vzorec funguje, ale podarilo se mi jej bez vetsich problemu aplikovat :-) Mockrat diky - i adblock jsem si pro tuto stranku deaktivoval, autor si to urcite zaslouzi!

Martin D.

Přidáno: 18.08.15 15:42

Dobrý den, ve spojitosti s touto funkcí jsem narazil na oříšek. Snažili jsme se ji použít při vyhledávání v jiném souboru, což funguje, ale pouze do chvíle, než zdrojový soubor zavřeme. Funkce pak vrací hlášku: #HODNOTA Použitá syntaxe: =POSUN('C:\Users\xxx\Desktop\[zdroj.xlsx]List1'!$D$4;0;1;1;1) Nevíte někdo, kde děláme chybu? Díky předem, M.

Pavel Lasák

Přidáno: 10.10.15 16:14

To Martin D.: Některé funkce jako např. POSUN (OFFSET) požadují, aby byl zdroj otevřen. Chybu neděláte, jde o vlastnost Excel. Nutno otevřít zdrojový sešit.

Ramon

Přidáno: 17.01.16 09:34

Čučím jak tele na nový vrata! Úžasný!

Pavel Lasák

Přidáno: 17.01.16 17:31

To Ramon: Děkuji a to je jenom funkce POSUN ;)

Honza

Přidáno: 05.02.16 09:07

Dobrý den, mám jednoduché zadání, ale nedaří se. Mám hodnotu v buňce D1, potřebuji tuto hodnotu vyhledat v sloupci A1:A100 a zapsat do buňky D2 hodnotu na řádku pod vyhledaným záznamem. Nedaří se mi vytvořit funkci. =POSUN(SVYHLEDAT(D1;A1:A100;1);1;0) Děkuji za pomoc. Honza

Pepe

Přidáno: 12.02.16 12:41

Honza: Vhodnejsi je pouzit index+match. Napr takto: =Index(A1:A100;pozvyhledat(D1;A1:A100,0)+1)

Pepe

Přidáno: 12.02.16 12:51

Nebo pokud chcete pouzit Posun, muzete nasledovne: =Posun(A1;;;pozvyhledat(D1;A1:A100,0)+1)

Honza

Přidáno: 17.02.16 13:43

To Pepe: Díky moc, funguje to parádně.

Reddy

Přidáno: 16.03.16 07:33

Neuveriteľné! Úžasná dokumentácia!

Pavel Lasák

Přidáno: 03.11.16 12:13

To Reddy: Děkuji







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články

25.03.2017:
ROUNDUP, ROUNDDOWN - zaokrouhlování

07.04.2017:
Řazení Excel - bug - problém

09.04.2017:
Podmínkové funkce Excel - bug - problém


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

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 - 2017 | 5176

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.