Excel OFFSET Formula (obsah)

  • Co je OFFSET Formula v Excelu?
  • Jak používat vzorec OFFSET v Excelu?

Co je OFFSET Formula v Excelu?

OFFSET Formula v Excelu dává buňku nebo rozsah dynamických obdélníkových buněk jako výstup, který je podmnožinou celkového daného rozsahu se zadaným počtem řádků, sloupců, výšky a šířky.

Syntaxe vzorce OFFSET v Excelu

Vysvětlení funkce OFFSET v Excelu:

  • reference: Počáteční bod pro OFFSET nebo můžeme říci řadu buněk, které lze považovat za základ pro OFFSET.
  • rows : Počet řádků na OFFSET pod nebo nad referenční hodnotou.
  • cols: Počet sloupců na OFFSET vpravo nebo vlevo od základní reference.
  • height: Počet řádků ve vrácené reference.
  • width: Počet sloupců ve vrácené referenci.

Výška a šířka jsou volitelné parametry pro tuto funkci a lze je použít k určení velikosti vytvořeného odkazu.

Jak používat vzorec OFFSET v Excelu?

Nyní se naučíme, jak psát offsetový vzorec, abychom získali hodnotu vyhledávání. Podívejme se na offsetový vzorec s některými příklady v Excelu.

Tuto šablonu OFFSET Formula Excel si můžete stáhnout zde - OFFSET Formula Excel Template

Vzorec Excel OFFSET - Příklad č. 1

Předpokládejme, že máme níže uvedená data.

  • Do buňky E2 zadejte následující vzorec:

= OFFSET (A1, 11, 1)

  • Po dokončení vzorce stiskněte klávesu Enter a podívejte se na výstup.

Tento vzorec udělal, že vzal odkaz z buňky A1 (Rok) a posunul se dolů o 11 řádků po A1 (tj. Řádek č. 12) a poté získal hodnotu, která je souřadnicí 11. řádku po A1 a prvním sloupci ( sloupec A = 0 v tomto případě sloupec B = 1. Jak víme, můžeme jít doprava i doleva). Což je 141, 27 milionu dolarů.

Vzorec OFFSET Excel - Příklad č. 2

Nyní uvidíme další příklad referenční buňky pro konkrétní rok. Do buňky E3 vložte následující vzorec:

= OFFSET (A1, 11, 0)

  • Po dokončení vzorce stiskněte klávesu Enter a podívejte se na výstup.

Pokud jste viděli vzorec, je stejný jako v předchozím, jedinou změnou je odkaz na sloupec. Odkaz na sloupec 0 (nula) znamená, že hodnota bude zachycena ze sloupce A na 11. řádku po A1 (což je 2010).

Vzorec OFFSET Excel - Příklad č. 3

V tomto příkladu vypočítáme součet posledních N řádků pomocí funkce Excel OFFSET. Obecný vzorec pro sčítání posledních N čísel je:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Chcete-li shrnout poslední N pozorování z vašich dat, můžete použít funkci SUM spolu s funkcí OFFSET a COUNT. V tomto příkladu shrneme posledních 5 datových bodů. Vezměte prosím na vědomí, že když řekneme N, jedná se o zobecnění jakékoli celočíselné hodnoty. Můžete tedy počítat za poslední 3, poslední 4, posledních 5 atd. V závislosti na vašem datovém souboru a na vašem požadavku.

Udělejme to pro náš soubor dat:

  • Do buňky E2 souboru excel zadejte následující vzorec.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Kliknutím na Enter zobrazíte výstup.

Jak jsme již viděli, funkce Excel OFFSET vydává obdélníkové dynamické rozsahy, když je zadána počáteční reference.

  • V tomto případě je počáteční reference uvedena jako B1 (jako první argument funkce OFFSET).
  • Použili jsme funkci COUNT, protože potřebujeme součet pozorování z poslední hodnoty. Funkce COUNT pomáhá OFFSET určit, kolik pozorování (řádků) je ve sloupci B. Počítá všechny číselné hodnoty ve sloupci B (19 je počet).
  • S číslem 19 se vzorec OFFSET stane:

= OFFSET (B1, 19, 0, -5)

  • Tento vzorec začíná ofsetem na B1 s 19 řádky a stejným sloupcem (0). Pak parametr -5 pomáhá této funkci rozšířit rozsah směrem dozadu o 5 řádků (viz záporné znaménko spojené s 5).
  • Dynamický rozsah Posledních pěti pozorování je uveden jako funkce vstupu do součtu. Což vede k součtu výnosů ze sloupce B.

Vzorec OFFSET Excel - Příklad č. 4

V tomto příkladu vypočítáme střední tržby za poslední 3 roky. Funkci OFFSET lze použít jako argument pro její výpočet.

  • Do buňky E2 vložte následující vzorec:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Stisknutím klávesy Enter zobrazíte výstup.

Tento vzorec pracuje na řádcích podobných předchozím.

  • OFFSET Vytvoří dynamický rozsah posledních tří pozorování ze sloupce B pomocí funkce COUNT (= OFFSET (A1, 19, 0, -3)).
  • To lze zadat jako výstup pro funkci MEDIAN. Což pak třídí pozorování ve vzestupném nebo sestupném pořadí a poté vybere střední nejvyšší hodnotu, jak vidíte ve výstupu ($ 212, 07).

Vzorec OFFSET Excel - Příklad č. 5

Zde vypočítáme střední rok pomocí funkce MEDIAN a OFFSET.

  • V buňce E3 použijte stejný vzorec:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Kliknutím na Enter zobrazíte výstup.

Totéž se stalo tady. Vzorec zaujal dynamický rozsah vytvořený funkcí OFFSET pomocí COUNT. A použil ji jako argument k MEDIANské funkci, která pak třídila roční hodnoty ve vzestupném nebo sestupném pořadí a poté dala výstup jako 2017.

Použití vzorce OFFSET je při vytváření dynamických rozsahů. Vždy je lepší pracovat na dynamických rozsazích místo statických. Jak byste mohli mít data, která se přidávají každou chvíli. Například zaměstnanec Master. Vaše kmenová data zaměstnanců se budou občas zvyšovat, protože se musí připojit další zaměstnanci. Pokaždé, když použití stejného vzorce nedává žádný smysl. Místo toho vytvořte dynamický rozsah pomocí funkce OFFSET a podívejte se na kouzlo. Určitě vám to ušetří čas.

Co si pamatovat

  • Funkce OFFSET vrací pouze odkaz na určitý rozsah buněk v Excelu. Nepohybuje buňky z jejich původních pozic.
  • Ve výchozím nastavení funguje tato funkce na horní části (v řádcích) a na pravé straně (ve sloupcích). Může však být poskytnuta s negativními argumenty pro zvrácení výchozí funkce. Stejně jako argument -negativní řádek pomáhá kompenzovat data shora dolů a záporný argument col pomáhá kompenzovat data doleva.
  • Vzorec OFFSET může zpomalit funkčnost aplikace Excel, protože se jedná o vzorec, který se přepočítá pokaždé, když dojde ke změně v listu.
  • #REF chyba bude vyvolána, pokud je váš rozsah mimo rozsah vašeho sešitu.
  • Jakákoli jiná funkce, která očekává referenci, je schopna v ní použít funkci OFFSET.

Doporučené články

Toto je průvodce po vzorci OFFSET v Excelu. Zde diskutujeme, jak používat funkci OFFSET v Excelu, spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -

  1. Jak obtékat text v Excelu?
  2. Jednoduchý průvodce tříděním v Excelu
  3. LOOKUP Funkce v Excelu
  4. Jak používat funkci FIND v Excelu?

Kategorie:

Tipy pro Excel