Ak váš excelový pracovný hárok obsahuje výpočty, ktoré sú založené na meniacom sa rozsahu buniek, použite funkcie SUM a OFFSET spolu vo vzorci SUM OFFSET na zjednodušenie úlohy udržiavania výpočtov v aktuálnom stave.
Pokyny v tomto článku platia pre Excel pre Microsoft 365, Excel 2019, Excel 2016, Excel 2013 a Excel 2010.
Vytvorte dynamický rozsah pomocou funkcií SUM a OFFSET
Ak používate výpočty za časové obdobie, ktoré sa neustále mení – ako je napríklad určovanie predaja za mesiac – použite funkciu OFFSET v Exceli na nastavenie dynamického rozsahu, ktorý sa mení s každým dňom pridávania údajov o predaji.
Funkcia SUM sa sama o sebe zvyčajne dokáže prispôsobiť vloženiu nových buniek údajov do rozsahu, ktorý sa sčítava. Jedna výnimka nastane, keď sa údaje vložia do bunky, kde sa funkcia momentálne nachádza.
V nižšie uvedenom príklade sú nové údaje o predaji za každý deň pridané na koniec zoznamu, vďaka čomu sa súčet pri každom pridávaní nových údajov neustále posúva o jednu bunku nadol.
Ak chcete pokračovať v tomto návode, otvorte prázdny hárok programu Excel a zadajte vzorové údaje. Váš pracovný hárok nemusí byť naformátovaný ako v príklade, ale nezabudnite zadať údaje do rovnakých buniek.
Ak sa na sčítanie údajov použije iba funkcia SUM, rozsah buniek použitý ako argument funkcie by bolo potrebné upraviť pri každom pridaní nových údajov.
Spoločným použitím funkcií SUM a OFFSET sa sčítaný rozsah stane dynamickým a zmení sa tak, aby vyhovoval novým bunkám údajov. Pridávanie nových buniek s údajmi nespôsobuje problémy, pretože rozsah sa s každou novou bunkou prispôsobuje.
Syntax a argumenty
V tomto vzorci sa funkcia SUM používa na sčítanie rozsahu údajov dodaných ako argument. Počiatočný bod pre tento rozsah je statický a identifikuje sa ako odkaz na bunku na prvé číslo, ktoré má vzorec sčítať.
Funkcia OFFSET je vnorená do funkcie SUM a vytvára dynamický koncový bod pre rozsah údajov sčítaných vzorcom. To sa dosiahne nastavením koncového bodu rozsahu na jednu bunku nad umiestnením vzorca.
Syntax vzorca je:
=SUM(Začiatok rozsahu:OFFSET(Referencia, Riadky, Stĺpce))
Argumenty sú:
- Začiatok rozsahu: Počiatočný bod pre rozsah buniek, ktorý bude sčítaný funkciou SUM. V tomto príklade je počiatočným bodom bunka B2.
- Referencia: Požadovaný odkaz na bunku použitý na výpočet koncového bodu rozsahu. V príklade je argument Odkaz odkazom na bunku pre vzorec, pretože rozsah končí jednu bunku nad vzorcom.
- Rows: Vyžaduje sa počet riadkov nad alebo pod referenčným argumentom použitým pri výpočte posunu. Táto hodnota môže byť kladná, záporná alebo nastavená na nulu. Ak je umiestnenie posunu nad argumentom Referencia, hodnota je záporná. Ak je posun nižšie, argument Riadky je kladný. Ak sa posun nachádza v rovnakom riadku, argument je nula. V tomto príklade sa posun začína jeden riadok nad argumentom Reference, takže hodnota argumentu je záporná jedna (-1).
- Stĺpce: Počet stĺpcov naľavo alebo napravo od argumentu Referencie použitého na výpočet posunu. Táto hodnota môže byť kladná, záporná alebo nastavená na nulu. Ak je umiestnenie posunu naľavo od argumentu Reference, táto hodnota je záporná. Ak je posun vpravo, argument Cols je pozitívny. V tomto príklade sú sčítané údaje v rovnakom stĺpci ako vzorec, takže hodnota tohto argumentu je nula.
Na celkové údaje o predaji použite vzorec SUM OFFSET
Tento príklad používa vzorec SUM OFFSET na vrátenie súčtu pre denné čísla predaja uvedené v stĺpci B pracovného hárka. Spočiatku bol vzorec zadaný do bunky B6 a súhrnné údaje o predaji za štyri dni.
Ďalším krokom je posunúť vzorec SUM OFFSET o jeden riadok nižšie, aby sa vytvoril priestor pre celkový predaj za piaty deň. To sa dosiahne vložením nového riadku 6, čím sa vzorec presunie do riadka 7.
V dôsledku presunu Excel automaticky aktualizuje argument Referencia do bunky B7 a pridá bunku B6 do rozsahu sčítaného vzorcom.
- Vyberte bunku B6, čo je miesto, kde sa na začiatku zobrazia výsledky vzorca.
-
Vyberte kartu Formulas na páse s nástrojmi.
Image -
Vyberte Math & Trig.
Image -
Vybrať SUM.
Image - V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa Number1.
-
V pracovnom hárku vyberte bunku B2 a zadajte odkaz na túto bunku do dialógového okna. Toto umiestnenie je statickým koncovým bodom pre vzorec.
Image - V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa Number2.
-
Zadajte OFFSET(B6, -1, 0). Táto funkcia OFFSET tvorí dynamický koncový bod pre vzorec.
Image -
Výberom OK dokončite funkciu a zatvorte dialógové okno. Celková suma sa zobrazí v bunke B6.
Image
Pridať údaje o predaji nasledujúci deň
Pridanie údajov o predaji na ďalší deň:
- Kliknite pravým tlačidlom myši na hlavičku riadka pre riadok 6.
-
Vyberte Vložiť na vloženie nového riadku do pracovného hárka. Vzorec SUM OFFSET sa presunie o jeden riadok nižšie do bunky B7 a riadok 6 je teraz prázdny.
Image - Vyberte bunku A6 a zadajte číslo 5 na označenie, že sa zadáva celková suma predaja za piaty deň.
-
Vyberte bunku B6, zadajte $1458,25, potom stlačte Enter.
Image - Aktualizácie bunky B7 na novú celkovú sumu 7137,40 $.
Keď vyberiete bunku B7, aktualizovaný vzorec sa zobrazí v riadku vzorcov.
=SUM(B2:POSUN(B7, -1, 0))
Funkcia OFFSET má dva voliteľné argumenty: Height a Width, ktoré v tomto príklade neboli použité. Tieto argumenty informujú funkciu OFFSET o tvare výstupu z hľadiska počtu riadkov a stĺpcov.
Vynechaním týchto argumentov funkcia namiesto toho použije výšku a šírku argumentu Reference, ktorý je v tomto príklade jeden riadok vysoký a jeden stĺpec široký.
