Ako používať funkciu Excel OFFSET

Zhrnutie

Funkcia Excel OFFSET vráti odkaz na rozsah zostavený z piatich vstupov: (1) začiatočný bod, (2) posun riadkov, (3) posun stĺpca, (4) výška v riadkoch, (5) šírka v stĺpce. OFFSET sa hodí do vzorcov, ktoré vyžadujú dynamický rozsah.

Účel

Vytvorte referenčný offset od daného začiatočného bodu

Návratová hodnota

Odkaz na bunku.

Syntax

= OFFSET (referencia, riadky, stĺpce, (výška), (šírka))

Argumenty

  • referencia - začiatočný bod poskytovaný ako referencia alebo rozsah bunky.
  • riadky - Počet riadkov, ktoré sa majú posunúť pod počiatočnú referenciu.
  • cols - Počet stĺpcov, ktoré sa majú posunúť napravo od počiatočnej referencie.
  • height - (voliteľné) Výška v riadkoch vráteného odkazu.
  • width - (voliteľné) Šírka vráteného odkazu v stĺpcoch.

Verzia

Excel 2003

Poznámky k použitiu

Funkcia Excel OFFSET vracia dynamický rozsah zostavený z piatich vstupov: (1) začiatočný bod, (2) posun riadkov, (3) posun stĺpca, (4) výška v riadkoch, (5) šírka v stĺpcoch.

Východiskovým bodom ( referenčným argumentom) môže byť jedna bunka alebo rozsah buniek. Tieto riadky a cols argumenty sú počet buniek na "kompenzovať" od východiskového bodu. Argumenty výška a šírka sú voliteľné a určujú veľkosť vytvoreného rozsahu. Ak sú výška a šírka vynechané, štandardne sa nastavia na referenčnú výšku a šírku .

Napríklad pre referenčné C5 počnúc A1, odkaz je A1, riadky 4 a stĺpcov je 2:

=OFFSET(A1,4,2) // returns reference to C5

Pre odkaz C1: C5 z A1, odkaz je A1, riadky je 0, stĺpcov je 2, výška 5, a šírka je 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Poznámka: šírku je možné vynechať, pretože bude predvolene nastavená na hodnotu 1.

Je bežné vidieť OFFSET zabalený v inej funkcii, ktorá očakáva rozsah. Napríklad do SUM C1: C5, počnúc od A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Hlavným účelom OFSETU je umožniť, aby sa vzorce dynamicky prispôsobovali dostupným údajom alebo vstupom používateľa. Funkciu OFFSET je možné použiť na vytvorenie dynamického pomenovaného rozsahu pre grafy alebo kontingenčné tabuľky, aby sa zabezpečilo, že zdrojové údaje sú vždy aktuálne.

Poznámka: V dokumentácii k programu Excel sa uvádza, že výška a šírka nemôžu byť záporné, ale zdá sa, že záporné hodnoty fungujú od začiatku 90. rokov dobre. Funkcia OFFSET v Tabuľkách Google nepovolí zápornú hodnotu pre argumenty výšky alebo šírky.

Príklady

Nasledujúce príklady ukazujú, ako je možné konfigurovať funkciu OFFSET na vrátenie rôznych druhov rozsahov. Tieto obrazovky boli urobené v programe Excel 365, takže funkcia OFFSET vráti dynamické pole, ak je výsledkom viac ako jedna bunka. V starších verziách programu Excel môžete pomocou klávesu F9 skontrolovať výsledky vrátené z funkcie OFFSET.

Príklad č

Na obrazovke nižšie používame funkciu OFFSET na vrátenie tretej hodnoty (marec) v druhom stĺpci (západ). Vzorec v H4 je:

=OFFSET(B3,3,2) // returns D6

Príklad č

Na obrazovke nižšie používame funkciu OFFSET na vrátenie poslednej hodnoty (jún) v treťom stĺpci (sever). Vzorec v H4 je:

=OFFSET(B3,6,3) // returns E9

Príklad č

Ďalej použijeme OFFSET na vrátenie všetkých hodnôt v treťom stĺpci (sever). Vzorec v H4 je:

=OFFSET(B3,1,3,6) // returns E4:E9

Príklad č

Ďalej použijeme OFFSET na vrátenie všetkých hodnôt pre máj (piaty riadok). Vzorec v H4 je:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Príklad č

Ďalej použijeme OFFSET na vrátenie hodnoty apríla, mája a júna pre západný región. Vzorec v H4 je:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Príklad č

Ďalej použijeme OFFSET na vrátenie hodnoty apríla, mája a júna pre západ a sever. Vzorec v H4 je:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Poznámky

  • Funkcia OFFSET vráti iba referenciu, žiadne bunky sa nepresunú.
  • Obe rady a stĺpcov môže byť dodávaný ako záporné čísla obrátiť ich normálny smer odsadenie - negatívne Stĺpce posun vľavo, a negatívne riadky kompenzovať vyššie.
  • OFFSET je „volatilná funkcia“ - prepočíta sa pri každej zmene pracovného hárka. Vďaka prchavým funkciám sa môžu pomalšie spúšťať väčšie a zložitejšie zošity.
  • OFFSET zobrazí #REF! chybová hodnota, ak je odsadenie mimo okraj tabuľky.
  • Ak je výška alebo šírka vynechaná, použije sa výška a šírka referencie .
  • OFFSET je možné použiť s akoukoľvek inou funkciou, ktorá očakáva prijatie referencie.
  • Dokumentácia programu Excel hovorí, že výška a šírka nemôžu byť záporné, ale záporné hodnoty fungujú.

Podobné videá

Ako vytvoriť dynamický pomenovaný rozsah pomocou funkcie OFFSET V tomto videu sa pozrieme na to, ako vytvoriť dynamický pomenovaný rozsah pomocou funkcie OFFSET, ktorá je najbežnejším spôsobom vytvárania rozsahu dynamických mien pomocou vzorca.

Zaujímavé články...