✅ Funkcia Excel OFFSET - Príklady vzorcov + video ZADARMO

Obsah

Funkcia Excel OFFSET (príklad + video)
Pochopenie základov funkcie Excel OFFSET
Funkcia Excel OFFSET - príklady
Alternatívy funkcie Excel OFFSET
Funkcia Excel OFFSET - video návod

Funkcia Excel OFFSET (príklad + video)

Kedy použiť funkciu Excel OFFSET

Funkciu Excel OFFSET je možné použiť, ak chcete získať referenciu, ktorá odsúva zadaný počet riadkov a stĺpcov od počiatočného bodu.

Čo vracia

Vráti referenciu, na ktorú ukazuje funkcia OFFSET.

Syntax

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

Vstupné argumenty

referencia - Referencia, od ktorej chcete kompenzovať. Môže to byť odkaz na bunku alebo rozsah susedných buniek.
riadky - počet riadkov na vyrovnanie. Ak použijete kladné číslo, posunie sa do riadkov nižšie a ak sa použije záporné číslo, posunie sa do vyššie uvedených riadkov.
cols - počet stĺpcov na kompenzáciu. Ak použijete kladné číslo, posunie sa k stĺpcom vpravo a ak sa použije záporné číslo, posunie sa k stĺpcom vľavo.
[výška] - toto je číslo, ktoré predstavuje počet riadkov vo vrátenej referencii.
[šírka] - toto je číslo, ktoré predstavuje počet stĺpcov vo vrátenej referencii.

Pochopenie základov funkcie Excel OFFSET

Funkcia Excel OFFSET je možno jednou z najviac mätúcich funkcií v programe Excel.

Zoberme si jednoduchý príklad šachovej hry. V šachu je figúrka nazývaná Veža (známa tiež ako veža, markíz alebo rektor).

[Obrázok so súhlasom: Úžasná Wikipedia]

Teraz, ako všetky ostatné šachové figúrky, má veža pevnú cestu, po ktorej sa môže pohybovať po šachovnici. Môže ísť rovno (vpravo/vľavo alebo hore/dole po doske), ale nemôže ísť šikmo.

Predpokladajme napríklad, že máme šachovnicu v programe Excel (ako je uvedené nižšie), v danom poli (v tomto prípade D5) môže veža ísť iba štyrmi zvýraznenými smermi.

Teraz, keď vás požiadam, aby ste vežu presunuli zo súčasnej polohy do žlto zvýraznenej, čo jej poviete?

Požiadate ho, aby urobil dva kroky nadol a dva kroky doprava … však?

A to je tesná blízkosť toho, ako funguje funkcia OFFSET.

Teraz sa pozrime, čo to znamená v programe Excel. Chcem začať s bunkou D5 (kde je veža) a potom ísť o dva riadky nižšie a dva stĺpce napravo a načítať hodnotu z bunky tam.

Vzorec by bol:
= OFFSET (odkiaľ začať, koľko riadkov dole, koľko stĺpcov vpravo)

Ako vidíte, vzorec vo vyššie uvedenom príklade v bunke J1 je = OFFSET (D5,2,2).

Začalo sa to na D5, potom išlo o dva riadky nižšie a dva stĺpce doprava a dosiahlo celu F7. Potom vrátil hodnotu v bunke F7.

V uvedenom príklade sme sa pozreli na funkciu OFFSET s 3 argumentmi. Existujú však ešte dva voliteľné argumenty.

Pozrime sa na jednoduchý príklad tu:

Predpokladajme, že chcete použiť odkaz na bunku A1 (žltou farbou) a chcete vo vzorci odkazovať na celý rozsah zvýraznený modrou farbou (C2: E4).

Ako by ste to urobili pomocou klávesnice? Najprv by ste šli do bunky C2 a potom vyberte všetky bunky v C2: E4.

Teraz sa pozrime, ako to urobiť pomocou vzorca OFFSET:

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

Ak použijete tento vzorec v bunke, vráti #HODNOTU! chyba, ale ak sa dostanete do režimu úprav a vyberiete vzorec a stlačíte kláves F9, uvidíte, že vráti všetky hodnoty, ktoré sú zvýraznené modrou farbou.

Teraz sa pozrime, ako tento vzorec funguje:

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

Prvým argumentom je bunka, kde by mala začať.
Druhý argument je 1, ktorý hovorí Excelu, aby vrátil referenciu, ktorá bola O 1 riadok OFFSET.
Tretí argument je 2, ktorý hovorí Excelu, aby vrátil referenciu, ktorá bola O 2 stĺpce OFFSET.
Štvrtý argument je 3. Toto určuje, že referencia by mala pokrývať 3 riadky. Toto sa nazýva argument výšky.
Piaty argument je 3. Toto určuje, že referencia by mala pokrývať 3 stĺpce. Toto sa nazýva argument šírky.

Teraz, keď máte odkaz na rozsah buniek (C2: E4), môžete ho použiť v rámci inej funkcie (napríklad SUM, COUNT, MAX, AVERAGE).

Našťastie dobre rozumiete používaniu funkcie Excel OFFSET. Teraz sa pozrime na niekoľko praktických príkladov použitia funkcie OFFSET.

Funkcia Excel OFFSET - príklady

Tu sú dva príklady použitia funkcie Excel OFFSET.

Príklad 1 - Nájdenie poslednej vyplnenej bunky v stĺpci

Predpokladajme, že máte nejaké údaje v stĺpci, ako je uvedené nižšie:

Na nájdenie poslednej bunky v stĺpci použite nasledujúci vzorec:

= OFFSET (A1, COUNT (A: A) -1,0)

Tento vzorec predpokladá, že okrem uvedených hodnôt neexistujú žiadne ďalšie hodnoty a tieto bunky neobsahujú prázdnu bunku. Funguje to tak, že spočíta celkový počet buniek, ktoré sú vyplnené, a podľa toho odsadí bunku A1.

Napríklad v tomto prípade existuje 8 hodnôt, takže funkcia COUNT (A: A) vráti 8. Bunku A1 posunieme o 7, aby sme získali poslednú hodnotu.

Príklad 2 - Vytvorenie dynamického rozbaľovacieho zoznamu

Koncepty uvedené v Príklade 1 môžete rozšíriť a vytvoriť dynamické pomenované rozsahy. Tieto môžu byť užitočné, ak používate pomenované rozsahy vo vzorcoch alebo pri vytváraní rozbaľovacích zoznamov a pravdepodobne budete pridávať/odstraňovať údaje z odkazu, ktorý tvorí pomenovaný rozsah.

Tu je príklad:

Rozbaľovací zoznam sa automaticky upraví, keď je rok pridaný alebo odstránený.

Stáva sa to, pretože vzorec, ktorý sa používa na vytvorenie rozbaľovacej ponuky, je dynamický a identifikuje akékoľvek pridanie alebo odstránenie a podľa toho upraví rozsah.

Postupujte takto:

Vyberte bunku, do ktorej chcete vložiť rozbaľovaciu ponuku.
Prejdite na položku Údaje -> Nástroje údajov -> Overenie údajov.
V dialógovom okne Overenie údajov na karte Nastavenia vyberte z rozbaľovacieho zoznamu položku Zoznam.
V zdroji zadajte nasledujúci vzorec: = OFFSET (A1,0,0, COUNT (A: A), 1)
Kliknite na tlačidlo OK.

Pozrime sa, ako tento vzorec funguje:

Prvé tri argumenty funkcie OFFSET sú A1, 0 a 0. To v podstate znamená, že vráti rovnakú referenčnú bunku (ktorá je A1).
Štvrtý argument je pre výšku a tu funkcia COUNT vráti celkový počet položiek v zozname. Predpokladá, že v zozname nie sú žiadne medzery.
Piaty argument je 1, ktorý naznačuje, že šírka stĺpca by mala byť jedna.

Doplňujúce Poznámky:

OFFSET je nestála funkcia (používajte opatrne).
- Prepočíta sa vždy, keď je otvorený zošit programu Excel alebo keď sa v pracovnom hárku spustí výpočet. To by mohlo predĺžiť čas spracovania a spomaliť váš zošit.
Ak je hodnota výšky alebo šírky vynechaná, považuje sa za referenčnú hodnotu.
Ak riadky a cols sú záporné čísla, potom sa smer posunu zmení.

Alternatívy funkcie Excel OFFSET

Vzhľadom na niektoré obmedzenia funkcie Excel OFFSET, mnohí chcete zvážiť alternatívy k nej:

Funkcia INDEX: Funkciu INDEX je možné použiť aj na vrátenie odkazu na bunku. Kliknutím sem zobrazíte príklad vytvorenia dynamického pomenovaného rozsahu pomocou funkcie INDEX.
Tabuľka programu Excel: Ak v tabuľke programu Excel používate štruktúrované odkazy, nemusíte sa obávať pridávania nových údajov a úpravy vzorcov.