Pomocí propracované funkce Offset aplikace Excel mohou uživatelé dynamicky odkazovat na rozsah buněk. Při práci s obrovskými soubory dat nebo při vytváření dynamických výpočtů je tento nástroj docela užitečný. V tomto příspěvku prozkoumáme fungování funkce Offset a nabídneme její užitečné aplikace. Jaká je činnost funkce Offset? Chcete-li zadat odkaz na rozsah buněk, které jsou vzdáleny určitý počet řádků a sloupců od zadaného počátečního bodu, použijte funkci Offset aplikace Excel. Funkce Offset je definována následující syntaxí: =OFFSET(reference, rows, cols, [height], [width]).
Počáteční buňka, ze které se posun počítá, se označuje jako argument „reference“. Počet řádků a sloupců, o které je reference posunuta, je určen argumenty „rows“ a „cols“. Velikost rozsahu, který se má vrátit, je určena nepovinnými argumenty „height“ a „width“.
Abychom pochopili fungování funkce Offset, podívejme se na příklad. Řekněme, že chceme odkázat na rozsah, který začíná na B2 a pokračuje o dva řádky dolů a dva sloupce doprava. Máme soubor dat v buňkách A1 až C5 a rozsah začíná na B2. Funkci Offset lze použít následujícím způsobem: =OFFSET(B2, 2, 2, 2, 2, 2). Výsledkem tohoto vzorce je rozsah D4:E5.
Funkci Offset lze spojit s dalšími funkcemi, jako je Count nebo CountA, a vytvořit tak dynamické rozsahy. Například vzorec =OFFSET(1, 0, 0, COUNTA(:), 1) lze použít k vytvoření dynamického rozsahu, který se zvětší, když se do datového souboru přidají nová data ve sloupci A. Všechny neprázdné buňky ve sloupci A budou zahrnuty do rozsahu, který tento vzorec vrátí.
Funkci Offset lze použít při konstrukci dynamických vzorců k odkazování na různé buňky na základě předem stanovených kritérií. Například můžeme použít vzorec =OFFSET(B2, 0, -1) – B2 k určení nárůstu prodeje z jednoho měsíce na druhý, pokud máme datový soubor s měsíčními hodnotami prodeje. Růst prodeje se vypočítá tak, že se vezme hodnota v buňce nalevo od aktuální buňky a odečte se od aktuální buňky.
Při ověřování dat lze také použít funkci Offset k vytvoření dynamických rozevíracích seznamů. Kombinací funkce Offset s funkcí Ověření dat můžeme navrhnout rozevírací seznam, který se automaticky aktualizuje v reakci na změny v souboru dat. Například vzorec =OFFSET(1, 0, 0, COUNTA(:), 1) lze použít k sestavení rozevíracího seznamu v buňce B1, pokud sloupec A obsahuje seznam států.
Co znamená posun o 40 %?
Při použití funkce Excel Offset se obvykle nehovoří o „40% posunu“. Lze ji však použít k dynamickému odkazu na rozsah, který začíná v určité buňce a pokračuje o 40 % dolů nebo napříč. To lze provést tak, že se zjistí, kolik řádků nebo sloupců se má odsunout, na základě toho, kolik řádků nebo sloupců se celkově vyskytuje v datové sadě.
Například vzorec =OFFSET(1, 0, 0, ROUND(COUNTA(:) * 0.4, 0), 1) lze použít k vytvoření dynamického rozsahu, který začíná v A1 a sahá o 40 % níže pro datovou sadu se 100 řádky ve sloupci A. Rozsah, který tento vzorec vrátí, pokrývá prvních 40 % datové sady.
Závěrem lze říci, že funkce Offset aplikace Excel je flexibilní nástroj, který dává uživatelům možnost dynamicky odkazovat na rozsahy buněk. Pochopení toho, jak funkce Offset funguje, může výrazně zlepšit efektivitu a přizpůsobivost vašich operací v aplikaci Excel, ať už ji používáte ke generování vzorců, provádění validace dat nebo vytváření dynamických rozsahů.
K určení posunu je třeba použít funkci OFFSET aplikace Excel. Pomocí funkce OFFSET můžete odkazovat na rozsah buněk, které jsou od daného referenčního bodu vzdáleny určitý počet řádků a sloupců.
Syntaxe funkce OFFSET je následující: „Další proměnné zahrnují řádky, sloupce, [výška] a [šířka]. Je možné použít pojmenovaný rozsah nebo odkaz na buňku. Počet řádků, které chcete posunout od referenčního bodu, je ve vzorci označen „řádky“. Kladné číslo znamená odsazení směrem dolů, zatímco záporné číslo znamená odsazení směrem nahoru. Počet sloupců, které chcete posunout od referenčního bodu, je ve vzorci označen „sloupci“. Kladné číslo znamená odsazení směrem doprava, zatímco záporné číslo znamená odsazení směrem doleva.
– Volitelné parametry „[výška]“ a „[šířka]“ umožňují zvolit výšku a šířku rozsahu, který chcete vrátit. Pokud není zadán žádný posun, funkce OFFSET vrátí jednu buňku. Můžete například použít vzorec =OFFSET(A1, 3, 2) pro určení odsazení buňky A1, která je o 3 řádky níže a o 2 sloupce vpravo.
Buňka, která je o 3 řádky a 2 sloupce vpravo od buňky A1, by byla vrácena tímto vzorcem. V závislosti na vašich jedinečných potřebách můžete změnit hodnoty parametrů „řádky“ a „sloupce“.
Pomocí těchto kroků lze pomocí funkce OFFSET aplikace Excel vytvořit posun o 0,2: V prvním kroku vyberte buňku, ve které se má zobrazit hodnota posunu. 2. Do řádku vzorců zadejte následující vzorec: (reference, 0,2, 0) =OFFSET
„Reference“ v tomto vzorci označuje buňku, od které chcete provést offset. Pokud byste například chtěli provést odsazení od buňky A1, zadali byste jako odkaz „A1“.
Počet řádků, které chcete posunout, je reprezentován druhým vstupem „0,2“. V této situaci byste měli provést odsazení o 0,2 řádku, tedy o jednu pětinu řádku.
Třetí vstup, „0“, představuje počet sloupců, které chcete odsunout. Tuto možnost nastavíte na 0, protože všechny vaše potřeby odsazení jsou vertikální. 3. Chcete-li vzorec uvést do chodu, stiskněte klávesu Enter.
Výsledkem bude hodnota, která je vzdálena 0,2 řádku od buňky, kterou jste zadali jako odkaz.