Funkcia Excel OFFSET (obsah)

  • OFFSET v Exceli
  • OFFSET vzorec v Exceli
  • Ako používať funkciu OFFSET v Exceli?

OFFSET v Exceli

OFFSET Funkcia v Exceli pomáha užívateľovi vrátiť bunku alebo rozsah buniek, ktoré sú zadané č. riadkov a stĺpcov z bunky. Môžeme uviesť číslo. riadkov a č. stĺpcov, ktoré sa majú vrátiť.

Nasleduje vzorec OFFSET v Exceli:

Funkcia OFFSET v Exceli pozostáva z nasledujúcich argumentov:

  • Odkaz: Je to argument, z ktorého chceme založiť offset. Môže to byť odkaz na bunku alebo rad susedných buniek, ak nie Offset vráti # HODNU! Chybová hodnota.
  • Riadky: Nie. riadkov na odsadenie. Ak použijeme kladné číslo, potom sa posunie na riadky nižšie a ak sa použije záporné číslo, potom sa vyrovná riadky vyššie.
  • Stĺpce: Je to pre nie. stĺpcov na odsadenie. Ide teda o rovnaký koncept ako v prípade riadkov, ak použijeme kladné číslo, potom sa koriguje na stĺpce vpravo a ak sa použije záporné číslo, potom sa koriguje na stĺpce vľavo.
  • Výška: Tento argument je voliteľný. Malo by to byť kladné číslo. Je to číslo, ktoré predstavuje počet riadkov vo vrátenej referencii.
  • Šírka: Toto je tiež voliteľný argument. Musí to byť kladné číslo. Je to číslo, ktoré predstavuje počet stĺpcov vo vrátenej referencii.

Poznámky:

  • Funkcia OFFSET v Exceli vráti #REF! chybová hodnota, ak riadky a stĺpce posunú referenciu cez okraj pracovného listu.
  • Vo funkcii OFFSET sa predpokladá, že má byť rovnaká výška alebo šírka ako referencia, ak je výška alebo šírka vynechaná.
  • OFFSET vráti referenciu, v skutočnosti nepohybuje žiadne bunky ani rozsah buniek ani nezmení výber. Môže byť použitý s akoukoľvek funkciou očakávajúcou referenčný argument.

Ako používať funkciu OFFSET v Exceli?

OFFSET Funkcia v Exceli je veľmi jednoduchá a ľahko použiteľná. Nechajte pochopiť fungovanie funkcie OFFSET v Exceli pomocou nejakého príkladu OFFSET Formula.

Túto šablónu funkcie OFFSET Function Excel si môžete stiahnuť tu - šablónu funkcie OFFSET Function Excel

Príklad č. 1

Vzorec Offset vráti referenciu bunky na základe počiatočného bodu, riadkov a stĺpcov, ktoré určíme. Vidíme to v nižšie uvedenom príklade:

= OFFSET (A1, 3, 1)

Vzorec povie Excel, aby zvážil bunku A1 pre počiatočný bod (odkaz), potom presunul 3 riadky nadol (riadky) a 1 stĺpec doľava (argument stĺpcov). Potom vzorec OFFSET vráti hodnotu v bunke B4.

Obrázok vľavo ukazuje, že funkcia a snímka obrazovky vpravo ukazujú, ako môžeme použiť vzorec OFFSET na základe údajov v reálnom čase. Rozdiel medzi týmito dvoma vzorcami je v tom, že druhý (napravo) obsahuje odkaz na bunku (D1) v argumente riadku. Ale pretože bunka D1 obsahuje číslo 1 a presne rovnaké číslo sa objaví v argumentoch riadkov prvého vzorca, obe by vrátili rovnaký výsledok - hodnotu v B2.

Príklad č. 2 Použitie funkcie OFFSET pre súčet

Pretože vieme, že funkciu OFFSET je možné použiť aj s inou funkciou programu Excel, v tomto príklade uvidíme funkciu SUM.

Na obrázku vyššie predpokladajme, že musíme zistiť súčet známok, potom môžeme použiť aj funkciu OFFSET.

Vzorec je uvedený nižšie:

Takže vo vyššie uvedenom vzorci D5 je prvá bunka, kde začínajú údaje, ktorá je referenciou ako východisková bunka. Potom je hodnota riadkov a stĺpcov 0, takže ich môžeme dať ako 0, 0. Potom je tu 4, čo znamená 4 riadky pod referenčným číslom, pre ktoré Excel potrebuje vypočítať súčet, a nakoniec je to 1, to znamená, že je tu 1 stĺpec ako šírka.

Vyššie uvedený obrázok môžeme odkázať na spoločné vysvetlenie vysvetleného príkladu.

A nižšie je výsledok:

Stačí zadať Enter po zadaní vzorca do požadovanej bunky a potom máme výsledok.

Informácie o funkcii OFFSET v programe Excel

  • Funkcia OFFSET v programe Excel práve vracia odkaz, v skutočnosti nepohybuje žiadne bunky ani rozsah buniek.
  • Ak vzorec OFFSET vráti rozsah buniek, riadky a stĺpce sa vždy vzťahujú na ľavú hornú bunku vo vrátenom rozsahu.
  • Odkaz by mal obsahovať bunku alebo rozsah buniek, inak vzorec vráti chybu #VALUE.
  • Ak riadky a stĺpce zadané cez okraj tabuľky, vzorec Offset v programe Excel vráti #REF! chyba.
  • Funkcia OFFSET sa dá použiť v akejkoľvek inej funkcii Excel, ktorá vo svojich argumentoch akceptuje odkaz na bunku alebo rozsah.

Napríklad, ak použijeme vzorec = OFFSET (A1, 3, 1, 1, 3) samostatne, dá to # HODNOTA! Chyba, pretože rozsah návratnosti (1 riadok, 3 stĺpce) sa nehodí do bunky. Ak to však zoraďujeme do funkcie SUM, ako je uvedené nižšie:

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

Vzorec Offset vráti súčet hodnôt v 1-riadku rozsahom 3 stĺpcov, ktorý je 3 riadky pod a 1 stĺpec napravo od bunky A1, tj súčet hodnôt v bunkách B4: D4.

Ako vidíme, celkom 36 + 63 + 82 zvýrazneného Mar sa rovná 181, čo prišlo ako výsledok v G2 po použití súčtového a kompenzačného vzorca. (zvýraznené žltou farbou)

Použitie funkcie OFFSET v Exceli

Ako sme videli, že to, čo funkcia OFFSET vlastne robí pomocou vyššie uvedeného príkladu, môžeme mať otázky, že Prečo obťažovať spievaný vzorec OFFSET, ktorý je trochu zložitý, prečo jednoducho použiť priamy odkaz, ako súčet alebo B4: D4?

Funkcia OFFSET v Exceli je veľmi dobrá:

  • Získanie rozsahu od počiatočnej bunky:

Niekedy nevieme presnú alebo skutočnú adresu rozsahu, vieme iba to, odkiaľ to začína od určitej bunky. V tomto prípade môžeme použiť funkciu OFFSET, ktorá sa ľahko používa.

  • Vytváranie dynamického rozsahu:

Odkazy ako vyššie uvedený príklad B1: C4 sú statické, to znamená, že sa vždy vzťahujú na daný alebo pevný rozsah. V niektorých prípadoch sa však úlohy dajú ľahšie vykonávať pomocou dynamických rozsahov. Pomáha to najmä pri práci so zmenou údajov. Napríklad by sme mohli mať tabuľku, do ktorej sa každý deň / týždeň / mesiac vkladá alebo pridáva nový riadok alebo stĺpec, v tomto prípade pomôže funkcia OFFSET.

Obmedzenia a alternatívy funkcie OFFSET v Exceli

Pretože každý vzorec v programe Excel má svoje vlastné obmedzenia a alternatívy, videli sme, ako a kedy používať funkciu OFFSET v programe Excel.

Nižšie sú uvedené kritické obmedzenia funkcie OFFSET v Exceli:

  • Funkcia hladová po zdrojoch:

To znamená, že vždy, keď dôjde k zmene zdrojových údajov, vzorec sa prepočíta pre celú množinu, čo Excel zostane zaneprázdnené dlhšie. Ak existuje malá tabuľka, potom to nebude mať veľký vplyv, ale ak existuje niekoľko tabuliek, Excel môže nejaký čas prepočítať.

  • Problémy s kontrolou:

Ako vieme, odkazy vrátené funkciou Offset sú dynamické alebo sa menia a môžu obsahovať veľký vzorec, ktorý môže byť podľa potreby náročný na opravu alebo úpravu.

Alternatívy funkcie OFFSET v Exceli:

  • Funkcia INDEX:

Funkcia INDEX v programe Excel sa môže tiež použiť na vytvorenie dynamického rozsahu odkazov. Nie je to úplne to isté ako OFFSET. Ale rovnako ako funkcia OFFSET, funkcia INDEX nie je taká volatilná, preto nespomalí Excel, čo je obmedzenie pre OFFSET.

  • NEPRIAME Funkcia:

Funkciu INDIRECT môžeme použiť aj na vytvorenie dynamického rozsahu odkazov z mnohých zdrojov, ako sú napríklad hodnoty buniek , text a pomenované rozsahy.

Odporúčané články

Toto bol sprievodca pre OFFSET v Exceli. V tejto časti si preberieme vzorec OFFSET v Exceli a Ako používať funkciu OFFSET v Exceli spolu s praktickými príkladmi a šablónou Excel na stiahnutie. Môžete si tiež prečítať naše ďalšie navrhované články -

  1. MS Excel: Funkcia LOOKUP
  2. Najlepší príklad funkcie Excel XIRR
  3. Amazing Guide o NOT funkcie v Exceli
  4. IPMT v Exceli s príkladmi

Kategórie:

Tipy programu Excel