Funkcia Excel OFFSET vracia odkaz na rozsah vytvorený s piatimi vstupmi: (1) počiatočný bod, (2) odsadenie riadkov, (3) posun stĺpcov, (4) výška v riadkoch, (5) šírka v stĺpce. OFFSET je užitočný vo vzorcoch, ktoré vyžadujú dynamický rozsah.
Účel Vytvorenie referenčného posunu od daného počiatočného bodu Návratová hodnota Odkaz na bunku. Syntax = OFFSET (referencia, riadky, stĺpce, [výška], [šírka]) Argumenty
- odkaz - Počiatočný bod dodávaný ako referencia bunky alebo rozsah.
- riadky - Počet riadkov, ktoré sa majú vyrovnať, pod počiatočnú referenciu.
- cols - Počet stĺpcov, ktoré sa majú posunúť napravo od počiatočnej referencie.
- výška - [voliteľné] Výška vráteného odkazu v riadkoch.
- šírka - [voliteľné] Šírka vrátenej referencie v stĺpcoch.
Funkcia Excel OFFSET vracia dynamický rozsah vytvorený s piatimi vstupmi: (1) počiatočný bod, (2) posunutie riadka, (3) posun stĺpca, (4) výška v riadkoch, (5) šírka v stĺpcoch.
Východiskový bod ( odkaz argument) môže byť jedna bunka alebo rozsah buniek. The riadky a cols argumenty sú počet buniek, ktoré sa majú 'odsadiť' od počiatočného bodu. The výška a šírka argumenty sú voliteľné a určujú veľkosť vytvoreného rozsahu. Kedy výška a šírka sú vynechané, predvolene sú určené pre výšku a šírku odkaz .
ako vypočítať pomer dvoch čísel
Napríklad pre odkaz na C5 začínajúci na A1, odkaz je A1, riadky je 4 a cols je 2:
= OFFSET (A1,4,2) // returns reference to C5
K odkazu C1: C5 z A1, odkaz je A1, riadky je 0, cols je 2, výška je 5, a šírka je 1:
= OFFSET (A1,0,2,5,1) // returns reference to C1:C5
Poznámka: Šírka môže byť vynechaná, pretože bude predvolene nastavená na 1.
Je bežné, že OFFSET je zabalený do inej funkcie, ktorá očakáva rozsah. Napríklad na SUM C1: C5, začínajúc na A1:
= SUM ( OFFSET (A1,0,2,5,1)) // SUM C1:C5
Hlavným účelom OFFSETU je umožniť vzorcom dynamicky sa prispôsobiť dostupným údajom alebo vstupu užívateľa. Funkciu OFFSET je možné použiť na zostavenie a dynamický pomenovaný rozsah v prípade grafov alebo kontingenčných tabuliek zaistiť, aby boli zdrojové údaje vždy aktuálne.
Poznámka: Stavy dokumentácie k Excelu výška a šírka nemôže byť záporné, ale zdá sa, že záporné hodnoty fungovali dobre od začiatku deväťdesiatych rokov minulého storočia . Funkcia OFFSET v Tabuľkách Google nepovolí zápornú hodnotu pre argumenty výšky alebo šírky.
ako vytvoriť správu o citlivosti v programe Excel
Príklady
Nasledujúce príklady ukazujú, ako možno OFFSET nakonfigurovať tak, aby vracal rôzne druhy rozsahov. Tieto obrazovky boli nasnímané s Excel 365 , takže OFFSET vráti a dynamické pole keď je výsledkom viac ako jedna bunka. V starších verziách Excelu môžete použiť Kláves F9 na kontrolu výsledkov vrátených z OFFSET.
Príklad č. 1
Na nasledujúcej obrazovke použijeme 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 č. 2
Na nižšie uvedenej obrazovke použijeme 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 č. 3
Ď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 č. 4
Ďalej používame OFFSET na vrátenie všetkých hodnôt za máj (piaty riadok). Vzorec v H4 je:
= OFFSET (B3,5,1,1,4) // returns C8:F8
Príklad č. 5
Ďalej používame OFFSET na vrátenie hodnoty apríla, mája a júna pre región Západ. Vzorec v H4 je:
= OFFSET (B3,4,2,3,1) // returns D7:D9
Príklad č. 6
Ďalej používame OFFSET na vrátenie hodnoty apríla, mája a júna pre západ a sever. Vzorec v H4 je:
nájsť a nahradiť odkaz na bunku vo vzorci programu Excel
= OFFSET (B3,4,2,3,2) // returns D7:E9
Poznámky
- OFFSET vráti iba referenciu, žiadne bunky sa nepresunú.
- Obaja riadky a cols môžu byť dodané ako záporné čísla na zvrátenie ich normálneho smeru posunu - záporné cols posunutý doľava a záporný riadky odsadené vyššie.
- OFFSET je „ prchavá funkcia “ - prepočíta sa pri každej zmene pracovného hárka. Vďaka prchavým funkciám môžu pomalšie bežať väčšie a zložitejšie zošity.
- OFFSET zobrazí #REF! chybová hodnota, ak je posun mimo okraja pracovného hárka.
- Ak je výška alebo šírka vynechaná, výška a šírka položky odkaz sa používa.
- OFFSET je možné použiť s akoukoľvek ďalšou funkciou, ktorá očakáva prijatie referencie.
- Hovorí to dokumentácia programu Excel výška a šírka nemôže byť záporné, ale záporné hodnoty fungujú.