Funkce Excel OFFSET (obsah)

  • OFFSET v Excelu
  • OFFSET vzorec v Excelu
  • Jak používat funkci OFFSET v Excelu?

OFFSET v Excelu

Funkce OFFSET v Excelu pomáhá uživateli vracet buňku nebo oblast buněk, která je zadána ne. řádků a sloupců z buňky. Můžeme zadat ne. řádků a ne. sloupců, které mají být vráceny.

Níže je vzorec OFFSET v Excelu:

Funkce OFFSET v Excelu se skládá z následujících argumentů:

  • Reference: Je to argument, ze kterého chceme založit offset. Může to být odkaz na buňku nebo řada sousedních buněk, pokud není Offset, vrátí # HODNU! Chybová hodnota.
  • Řádky: Je to ne. řádků k odsazení. Použijeme-li kladné číslo, vyrovná se řádky níže a pokud se použije záporné číslo, vyrovná řádky výše.
  • Sloupce: Je to pro ne. sloupců k posunutí. Jedná se tedy o stejný koncept jako u řádků, pokud použijeme kladné číslo, pak se odsadí do sloupců napravo a pokud se použije záporné číslo, pak se odsadí do sloupců vlevo.
  • Výška: Tento argument je volitelný. Mělo by to být kladné číslo. Je to číslo, které představuje počet řádků ve vrácené referenci.
  • Šířka: Toto je také volitelný argument. Musí to být kladné číslo. Je to číslo, které představuje počet sloupců ve vrácené referenci.

Poznámky:

  • Funkce OFFSET v Excelu vrátí #REF! chybová hodnota, pokud řádky a sloupce posunou odkaz přes okraj listu.
  • Ve funkci OFFSET se předpokládá, že má stejnou výšku nebo šířku jako reference, pokud je výška nebo šířka vynechána.
  • OFFSET vrátí odkaz, ve skutečnosti nepřesune žádné buňky ani oblast buněk ani nezmění výběr. Může být použit s jakoukoli funkcí, která očekává referenční argument.

Jak používat funkci OFFSET v Excelu?

OFFSET Funkce v Excelu je velmi jednoduchá a snadno použitelná. Nechte pochopit fungování funkce OFFSET v Excelu pomocí příkladu OFFSET Formula.

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

Příklad č. 1

Vzorec Offset vrací odkaz na buňku na základě počátečního bodu, řádků a sloupců, které určíme. Vidíme to v níže uvedeném příkladu:

= OFFSET (A1, 3, 1)

Vzorec říká Excelu, aby zvážil buňku A1 pro počáteční bod (odkaz), pak přesunul 3 řádky dolů (řádky) a 1 sloupec doleva (argument sloupců). Poté vzorec OFFSET vrátí hodnotu v buňce B4.

Obrázek vlevo ukazuje, že cesta funkce a snímek obrazovky vpravo ukazují, jak můžeme použít vzorec OFFSET na základě dat v reálném čase. Rozdíl mezi těmito dvěma vzorci je v tom, že druhý (napravo) obsahuje odkaz na buňku (D1) v argumentu řádku. Ale protože buňka D1 obsahuje číslo 1 a přesně stejné číslo se objeví v argumentech řádků prvního vzorce, oba by vrátili stejný výsledek - hodnotu v B2.

Příklad č. 2 Použití funkce OFFSET pro součet

Protože víme, že funkci OFFSET lze použít také s další funkcí aplikace Excel, v tomto příkladu se podíváme na funkci SUM.

Ve výše uvedeném obrázku předpokládejme, že musíme zjistit součet známek, pak můžeme také použít funkci OFFSET.

Vzorec je níže:

Takže ve výše uvedeném vzorci D5 je první buňka, kde data začínají, což je odkaz jako počáteční buňka. Poté je hodnota řádků a sloupců 0, takže ji můžeme dát jako 0, 0. Pak je zde 4, což znamená 4 řádky pod referencí, pro které Excel potřebuje vypočítat součet, a nakonec je to 1, to znamená, že je zde 1 sloupec jako šířka.

Můžeme se odkazovat na výše uvedený obrázek, abychom spolu vysvětlili vysvětlený příklad.

A níže je výsledek:

Stačí zadat Enter po zadání vzorce v požadované buňce a pak máme výsledek.

Důležité informace o funkci OFFSET v Excelu

  • Funkce OFFSET v Excelu vrací pouze odkaz, ve skutečnosti nepohybuje žádné buňky ani rozsah buněk.
  • Pokud vzorec OFFSET vrací řadu buněk, řádky a sloupce vždy odkazují na levou horní buňku ve vráceném rozsahu.
  • Odkaz by měl obsahovat buňku nebo oblast buněk, jinak by vzorec vrátil chybu #VALUE.
  • Pokud řádky a sloupce zadané přes okraj tabulky, vyrovnání vzorec v aplikaci Excel vrátí #REF! chyba.
  • Funkci OFFSET lze použít v jakékoli jiné funkci Excel, která ve svých argumentech akceptuje odkaz na buňku nebo rozsah.

Pokud například použijeme vzorec = OFFSET (A1, 3, 1, 1, 3) samostatně, dá to #VALUE! Chyba, protože rozsah návratnosti (1 řádek, 3 sloupce) se do buňky nevejde. Pokud ji však porovnáme do funkce SUMA, jak je uvedeno níže:

= SUM (OFFSET (A1, 3, 1, 1, 3))

Vzorec Offset vrací součet hodnot v 1-řádku rozsahem 3 sloupců, který je 3 řádky pod a 1 sloupec napravo od buňky A1, tj. Součet hodnot v buňkách B4: D4.

Jak vidíme, celkem 36 + 63 + 82 zvýrazněných Mar je rovno 181, což přišlo jako výsledek v G2 po použití součtu a Offsetového vzorce. (zvýrazněno žlutě)

Použití funkce OFFSET v Excelu

Jak jsme viděli, co funkce OFFSET ve skutečnosti dělá pomocí výše uvedeného příkladu, můžeme mít otázky, proč Proč obtěžovat zpívaný OFFSET vzorec, který je trochu složitý, proč jednoduše použít přímý odkaz, jako je suma nebo B4: D4?

Funkce OFFSET v Excelu je velmi dobrá níže:

  • Získání rozsahu od počáteční buňky:

Někdy neznáme přesnou nebo skutečnou adresu rozsahu, víme pouze od místa, kde začíná od určité buňky. V tomto případě můžeme použít funkci OFFSET, kterou lze snadno použít.

  • Vytváření dynamického rozsahu:

Odkazy jako výše uvedený příklad B1: C4 jsou statické, to znamená, že vždy odkazují na daný nebo pevný rozsah. V některých případech jsou však úlohy snadnější provádět s dynamickými rozsahy. Zejména to pomáhá, když pracujeme na změně dat. Například bychom mohli mít tabulku, do které se každý den / týden / měsíc vkládá nebo přidává nový řádek nebo sloupec, v tomto případě pomůže funkce OFFSET.

Omezení a alternativy funkce OFFSET v Excelu

Protože každý vzorec v Excelu má svá vlastní omezení a alternativy, viděli jsme, jak a kdy použít funkci OFFSET v Excelu.

Níže jsou uvedena kritická omezení funkce OFFSET v Excelu:

  • Funkce hladová po zdrojích:

To znamená, že vždy, když dojde ke změně zdrojových dat, bude vzorec přepočítán pro celou sadu, což udržuje Excel zaneprázdněný po delší dobu. Pokud existuje malá tabulka, pak to nebude mít velký vliv, ale pokud existuje několik tabulek, Excel může nějakou dobu trvat, než přepočítat.

  • Potíže s kontrolou:

Jak víme, odkazy vrácené funkcí Offset jsou dynamické nebo se mění a mohou obsahovat velký vzorec, který může být obtížný opravit nebo upravit podle požadavku.

Alternativy funkce OFFSET v Excelu:

  • Funkce INDEX:

Funkce INDEX v Excelu lze také použít k vytvoření dynamického rozsahu odkazů. Není to úplně stejné jako u OFFSET. Stejně jako funkce OFFSET však funkce INDEX není tak nestálá, a proto nezpomalí Excel, což je omezení pro OFFSET.

  • NEPŘÍMÉ Funkce:

Funkci INDIRECT můžeme také použít k vytvoření dynamického rozsahu odkazů z mnoha zdrojů, jako jsou hodnoty buněk , text a pojmenované rozsahy.

Doporučené články

Toto byl průvodce OFFSET v Excelu. Zde diskutujeme vzorec OFFSET v Excelu a 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. MS Excel: LOOKUP Funkce
  2. Nejlepší příklad funkce Excel XIRR
  3. Úžasný průvodce funkcí NOT v Excelu
  4. IPMT v Excelu s příklady

Kategorie:

Tipy pro Excel