Vzorec OFFSET Excel (obsah)
- Čo je to OFFSET Formula v Exceli?
- Ako používať vzorec OFFSET v Exceli?
Čo je to OFFSET Formula v Exceli?
OFFSET Formula v Exceli poskytuje bunku alebo rozsah dynamických pravouhlých buniek ako výstup, ktorý je podmnožinou celkového daného rozsahu so zadaným počtom riadkov, stĺpcov, výšky a šírky.
Syntax vzorca OFFSET v Exceli
Vysvetlenie funkcie OFFSET v Exceli:
- referencia: Východiskový bod pre OFFSET alebo môžeme povedať rad buniek, ktoré možno považovať za základ pre OFFSET.
- riadky : Počet riadkov na OFFSET pod alebo nad základnou referenciou.
- cols: Počet stĺpcov do polohy OFFSET napravo alebo naľavo od referencie základne.
- výška: Počet riadkov vo vrátenej referencii.
- width: Počet stĺpcov vo vrátenej referencii.
Výška a šírka sú voliteľnými parametrami pre túto funkciu a môžu sa použiť na určenie veľkosti vytvoreného odkazu.
Ako používať vzorec OFFSET v Exceli?
Teraz sa naučíme, ako písať ofsetový vzorec, aby sme získali hodnotu vyhľadávania. Pochopme vzorec ofset s niektorými príkladmi v Exceli.
Túto šablónu OFFSET Formula Excel si môžete stiahnuť tu - OFFSET Formula Excel TemplateVzorec OFFSET Excel - príklad č. 1
Predpokladajme, že máme nižšie uvedené údaje.
- Do bunky E2 zadajte tento vzorec:
= OFFSET (A1, 11, 1)
- Po dokončení vzorca stlačte kláves Enter a pozrite sa na výstup.
Tento vzorec urobil, že vzal odkaz z bunky A1 (Rok) a posunul sa o 11 riadkov po A1 (tj riadok č. 12) a potom získal hodnotu, ktorá je súradnicou 11. riadku po A1 a prvom stĺpci ( stĺpec A = 0 v tomto prípade stĺpec B = 1. Ako vieme, môžeme ísť doprava aj doľava). Čo je 141, 27 milióna dolárov.
Vzorec OFFSET Excel - príklad č. 2
Teraz uvidíme ďalší príklad referenčnej bunky pre konkrétny rok. Do bunky E3 vložte nasledujúci vzorec:
= OFFSET (A1, 11, 0)
- Po dokončení vzorca stlačte kláves Enter a pozrite sa na výstup.
Ak ste videli vzorec, je rovnaký ako v predchádzajúcom, jediná zmena je v odkazoch na stĺpce. Odkaz na stĺpec 0 (nula) znamená, že hodnota bude zaznamenaná zo stĺpca A pre 11. riadok po A1 (čo je rok 2010).
Vzorec OFFSET Excel - príklad č. 3
V tomto príklade vypočítame súčet posledných N riadkov pomocou funkcie Excel OFFSET. Všeobecný vzorec pre spočítanie posledných N čísel je:
= SUM (OFFSET (A1, COUNT (A: A), 0, -N))
Na zhrnutie posledných N pozorovaní z vašich údajov môžete použiť funkciu SUM spolu s funkciou OFFSET a COUNT. V tomto príklade zhrnieme posledných 5 údajových bodov. Upozorňujeme, že keď povieme N, ide o zovšeobecnenie akejkoľvek celočíselnej hodnoty. Môžete tak počítať za posledné 3, posledné 4, posledných 5 atď., V závislosti od vášho súboru údajov a vašich požiadaviek.
Urobme to pre náš súbor údajov:
- Do bunky E2 súboru excel zadajte nasledujúci vzorec.
= SUM (OFFSET (B1, COUNT (B: B), 0, -5))
- Kliknutím na Enter zobrazíte výstup.
Ako sme už videli, funkcia Excel OFFSET vydáva pri zadaní počiatočnej referencie obdĺžnikové dynamické rozsahy.
- V tomto prípade je počiatočný odkaz poskytnutý v zmysle B1 (ako prvý argument funkcie OFFSET).
- Použili sme funkciu COUNT, pretože potrebujeme súčet pozorovaní z poslednej hodnoty. Funkcia COUNT pomáha OFFSETu určiť, koľko pozorovaní (riadkov) je v stĺpci B. Počíta všetky číselné hodnoty v stĺpci B (19 je počet).
- S počtom 19 sa vzorec OFFSET stáva:
= OFFSET (B1, 19, 0, -5)
- Tento vzorec začína ofsetom na B1 s 19 riadkami a rovnakým stĺpcom (0). Potom parameter výšky -5 pomáha tejto funkcii rozšíriť rozsah smerom dozadu o 5 riadkov (pozri záporné znamienko spojené s 5).
- Dynamický rozsah posledných piatich pozorovaní sa uvádza ako funkcia súčtu. Čo vedie k súčtu výnosov zo stĺpca B.
Vzorec OFFSET Excel - Príklad č. 4
V tomto príklade vypočítame stredný predaj za posledné 3 roky. Funkciu OFFSET možno použiť ako argument na jej výpočet.
- Do bunky E2 vložte nasledujúci vzorec:
= MEDIÁLNE (OFFSET (B1, POČET (B: B), 0, -3))
- Stlačením klávesu Enter zobrazíte výstup.
Tento vzorec pracuje na riadkoch podobných predchádzajúcemu.
- OFFSET Vytvorí dynamický rozsah posledných troch pozorovaní zo stĺpca B pomocou funkcie COUNT (= OFFSET (A1, 19, 0, -3)).
- Môže to byť ako výstup pre funkciu MEDIAN. Čo potom zoradí pozorovania vzostupne alebo zostupne a potom vyberie strednú najvyššiu hodnotu, ako vidíte vo výstupe (212, 07 USD).
Vzorec OFFSET Excel - Príklad č. 5
Tu vypočítame stredný rok pomocou funkcie MEDIAN a OFFSET.
- V bunke E3 použite nasledujúci vzorec:
= Stredná (OFFSET (A1, COUNT (A: A), 0, -3))
- Kliknutím na Enter zobrazíte výstup.
To isté sa stalo aj tu. Vzorec zabral dynamický rozsah vytvorený pomocou funkcie OFFSET pomocou COUNT. A použil ju ako argument funkcie MEDIAN, ktorá potom zoradila ročné hodnoty vzostupne alebo zostupne a potom vydala výstup ako 2017.
Použitie vzorca OFFSET je pri vytváraní dynamických rozsahov. Vždy je lepšie pracovať na dynamických rozsahoch ako na statických. Ako by ste mohli mať údaje, ktoré sa tu a tam pridajú. Napríklad zamestnanec Master. Vaše kmeňové údaje zamestnanca sa tu a tam neustále zvyšujú, pretože k nim musia byť pripojení noví zamestnanci. Zakaždým, keď použitie rovnakého vzorca nedáva zmysel. Namiesto toho vytvorte dynamický rozsah pomocou funkcie OFFSET a pozrite sa na kúzlo. Času vám to ušetrí čas.
Čo je potrebné pamätať
- Funkcia OFFSET vráti iba odkaz na konkrétny rozsah buniek v Exceli. Nepohne bunky z ich pôvodných pozícií.
- V predvolenom nastavení táto funkcia funguje na hornej časti (v riadkoch) a na pravej strane (v stĺpcoch). Môže sa však poskytnúť záporné argumenty na zvrátenie predvolenej funkčnosti. Argument podobne ako-negatívny riadok pomáha kompenzovať údaje zhora nadol a negatívny stĺpcový argument pomáha kompenzovať údaje doľava.
- Vzorec OFFSET môže spomaliť funkčnosť programu Excel, pretože ide o vzorec, ktorý sa prepočítava vždy, keď dôjde k zmene v pracovnom hárku.
- #REF chyba bude vyvolaná, ak je váš dosah mimo rozsahu vášho zošita.
- Akákoľvek iná funkcia, ktorá očakáva referenciu, je schopná v nej použiť funkciu OFFSET.
Odporúčané články
Toto je príručka pre vzorec OFFSET v Exceli. Tu diskutujeme o tom, ako používať funkciu OFFSET v Exceli spolu s praktickými príkladmi a stiahnuteľnou šablónou Excel. Môžete si tiež prečítať naše ďalšie navrhované články -
- Ako zabaliť text v Exceli?
- Jednoduchý sprievodca triedením v Exceli
- LOOKUP Funkcia v Exceli
- Ako používať funkciu FIND v Exceli?