Funkce Excel OFFSET (příklad + video)
Kdy použít funkci Excel OFFSET
Funkci Excel OFFSET lze použít, pokud chcete získat referenci, která od počátečního bodu kompenzuje zadaný počet řádků a sloupců.
Co vrací
Vrátí odkaz, na který ukazuje funkce OFFSET.
Syntax
= OFFSET (reference, řádky, sloupce, [výška], [šířka])
Vstupní argumenty
- reference - Odkaz, od kterého chcete kompenzovat. Může to být odkaz na buňku nebo řada sousedních buněk.
- řádky - počet řádků k odsazení. Pokud použijete kladné číslo, bude se kompenzovat na níže uvedené řádky, a pokud se použije záporné číslo, pak se odsadí na řádky výše.
- cols - počet sloupců k odsazení. Pokud použijete kladné číslo, bude odsazeno na sloupce napravo a pokud je použito záporné číslo, pak se odsadí na sloupce vlevo.
- [výška] - toto je číslo, které představuje počet řádků ve vrácené referenci.
- [šířka] - toto je číslo, které představuje počet sloupců ve vrácené referenci.
Pochopení základů funkce OFFSET aplikace Excel
Funkce OFFSET aplikace Excel je možná jednou z nejvíce matoucích funkcí v aplikaci Excel.
Ukažme si jednoduchý příklad šachové hry. V šachu je figurka jménem Rook (známá také jako věž, markýz nebo rektor).
[Obrázek s laskavým svolením: Nádherná Wikipedie]
Nyní, stejně jako všechny ostatní šachové figurky, má věž pevnou cestu, po které se může pohybovat po hrací ploše. Může jít rovně (vpravo/vlevo nebo nahoru/dolů po desce), ale nemůže jít šikmo.
Předpokládejme například, že máme šachovnici v Excelu (jak je uvedeno níže), v daném poli (v tomto případě D5) se věž může pohybovat pouze ve čtyřech zvýrazněných směrech.
Nyní, když vás požádám, abyste přenesli věž ze své aktuální pozice na tu, která je zvýrazněna žlutě, co jí řeknete?
Požádáte ho, aby udělal dva kroky dolů a dva kroky doprava … že?
A to je těsné přiblížení toho, jak funkce OFFSET funguje.
Nyní se podívejme, co to znamená v aplikaci Excel. Chci začít s buňkou D5 (což je místo, kde je věž) a pak jít o dva řádky dolů a dva sloupce doprava a načíst hodnotu z buňky tam.
Vzorec by byl:
= OFFSET (odkud začít, kolik řádků dolů, kolik sloupců vpravo)
Jak vidíte, vzorec ve výše uvedeném příkladu v buňce J1 je = OFFSET (D5,2,2).
Začalo to na D5 a pak šlo o dva řádky dolů a dva sloupce napravo a dosáhlo buňky F7. Poté vrátila hodnotu v buňce F7.
Ve výše uvedeném příkladu jsme se podívali na funkci OFFSET se 3 argumenty. Lze však použít ještě dva volitelné argumenty.
Podívejme se na jednoduchý příklad zde:
Předpokládejme, že chcete použít odkaz na buňku A1 (žlutě) a chcete ve vzorci odkazovat na celý rozsah zvýrazněný modře (C2: E4).
Jak byste to udělali pomocí klávesnice? Nejprve přejděte do buňky C2 a poté vyberte všechny buňky v C2: E4.
Nyní se podívejme, jak to provést pomocí vzorce OFFSET:
= OFFSET (A1,1,2,3,3)
Pokud použijete tento vzorec v buňce, vrátí #HODNOTU! chyba, ale pokud se dostanete do režimu úprav a vyberete vzorec a stisknete F9, uvidíte, že vrátí všechny hodnoty, které jsou zvýrazněny modře.
Nyní se podívejme, jak tento vzorec funguje:
= OFFSET (A1,1,2,3,3)
- Prvním argumentem je buňka, kde by měla začínat.
- Druhý argument je 1, který říká aplikaci Excel, aby vrátila referenci, která byla OFFSET o 1 řádek.
- Třetí argument je 2, který říká aplikaci Excel, aby vrátila referenci, která byla O 2 sloupce OFFSET.
- Čtvrtý argument je 3. Toto určuje, že odkaz by měl pokrývat 3 řádky. Tomu se říká argument výšky.
- Pátý argument je 3. Toto určuje, že odkaz by měl pokrývat 3 sloupce. Toto se nazývá argument šířky.
Nyní, když máte odkaz na řadu buněk (C2: E4), můžete ji použít v rámci jiné funkce (například SUM, COUNT, MAX, AVERAGE).
Naštěstí dobře rozumíte používání funkce Excel OFFSET. Nyní se podívejme na několik praktických příkladů použití funkce OFFSET.
Funkce Excel OFFSET - příklady
Zde jsou dva příklady použití funkce Excel OFFSET.
Příklad 1 - Nalezení poslední vyplněné buňky ve sloupci
Předpokládejme, že máte některá data ve sloupci, jak je uvedeno níže:
Chcete -li najít poslední buňku ve sloupci, použijte následující vzorec:
= OFFSET (A1, COUNT (A: A) -1,0)
Tento vzorec předpokládá, že neexistují žádné další hodnoty kromě uvedených a tyto buňky v sobě nemají prázdnou buňku. Funguje to tak, že spočítá celkový počet buněk, které jsou vyplněny, a podle toho odsadí buňku A1.
Například v tomto případě existuje 8 hodnot, takže COUNT (A: A) vrátí 8. Buňku A1 posuneme o 7, abychom získali poslední hodnotu.
Příklad 2 - Vytvoření dynamického rozevíracího seznamu
Koncept ukazuje v příkladu 1 můžete rozšířit a vytvořit dynamické pojmenované rozsahy. Ty by mohly být užitečné, pokud používáte pojmenované rozsahy ve vzorcích nebo při vytváření rozevíracích seznamů a pravděpodobně přidáváte/odstraňujete data z odkazu, který vytváří pojmenovaný rozsah.
Zde je příklad:
Po přidání nebo odebrání roku se rozevírací seznam automaticky upraví.
K tomu dochází, protože vzorec, který se používá k vytvoření rozevíracího seznamu, je dynamický a identifikuje jakékoli přidání nebo odstranění a podle toho upraví rozsah.
Postupujte takto:
- Vyberte buňku, do které chcete rozevírací seznam vložit.
- Přejděte na Data -> Datové nástroje -> Ověření dat.
- V dialogovém okně Ověření dat na kartě Nastavení vyberte z rozevíracího seznamu Seznam.
- Ve zdroji zadejte následující vzorec: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Klikněte na OK.
Podívejme se, jak tento vzorec funguje:
- První tři argumenty funkce OFFSET jsou A1, 0 a 0. To v podstatě znamená, že vrátí stejnou referenční buňku (což je A1).
- Čtvrtý argument je pro výšku a zde funkce COUNT vrací celkový počet položek v seznamu. Předpokládá, že v seznamu nejsou žádná mezera.
- Pátý argument je 1, což naznačuje, že šířka sloupce by měla být jedna.
Další poznámky:
- OFFSET je nestálá funkce (používejte opatrně).
- Přepočítá se vždy, když je sešit aplikace Excel otevřený nebo kdykoli je v listu spuštěn výpočet. To by mohlo prodloužit dobu zpracování a zpomalit váš sešit.
- Pokud je hodnota výšky nebo šířky vynechána, je brána jako referenční hodnota.
- Li řádky a cols jsou záporná čísla, pak se směr posunu obrátí.
Alternativy funkce Excel OFFSET
Kvůli některým omezením funkce Excel OFFSET mnoho z vás chce zvážit její alternativy:
- Funkce INDEX: Funkci INDEX lze také použít k vrácení odkazu na buňku. Kliknutím sem zobrazíte příklad, jak vytvořit dynamický pojmenovaný rozsah pomocí funkce INDEX.
- Tabulka Excel: Pokud v tabulce Excel používáte strukturované odkazy, nemusíte si dělat starosti s přidáváním nových dat a s nutností upravovat vzorce.
Funkce Excel OFFSET - video tutoriál
- Funkce VLOOKUP aplikace Excel.
- Funkce Excel HLOOKUP.
- Funkce Excel INDEX.
- Excel NEPŘÍMÁ funkce.
- Funkce Excel MATCH.
