Excel SUM a OFFSET Formula

Ak pracovný hárok programu Excel obsahuje výpočty založené na meniacom sa rozsahu buniek, používanie funkcií SUM a OFSET spolu vo formulári SUM OFFSET zjednodušuje úlohu udržiavať výpočty aktuálne.

Vytvorte dynamický rozsah pomocou funkcií SUM a OFFSET

Ak používate výpočty na časové obdobie, ktoré sa neustále mení - napríklad celkový predaj za mesiac - funkcia OFFSET v programe Excel vám umožňuje nastaviť dynamický rozsah, ktorý sa mení podľa údajov o predaji každého dňa.

Samotná funkcia SUM môže zvyčajne prispôsobiť vloženie nových buniek dát do rozsahu, ktorý je sumarizovaný.

Jedna výnimka nastane, keď sa údaje vložia do bunky, v ktorej je aktuálne umiestnená funkcia.

Vo vyššie uvedenom príklade obrázkov sa v dolnej časti zoznamu pridávajú nové údaje o predaji za každý deň, čím sa celková suma neustále posúva jedna bunka vždy, keď sa pridajú nové údaje.

Ak by sa funkcia SUM použila sama o sebe na to, aby sa údaje úplne utriedili, bolo by potrebné zmeniť rozsah buniek použitých ako argument funkcie pri každom pridaní nových údajov.

Pomocou funkcií SUM a OFFSET sa však celkový rozsah stáva dynamickým. Inými slovami, mení sa na prispôsobenie sa novým bunkám údajov. Pridanie nových buniek údajov nespôsobuje problémy, pretože rozsah sa naďalej prispôsobuje pri pridávaní každej novej bunky.

Syntax a argumenty

Pozrite si obrázok vyššie, ktorý bude nasledovať spolu s týmto tutoriálom.

V tomto vzorci sa funkcia SUM používa na doplnenie rozsahu poskytnutých údajov ako jeho argument. Počiatočný bod pre tento rozsah je statický a je označený ako odkaz na prvé číslo, ktoré sa má dosiahnuť vzorecom.

Funkcia OFFSET je vnořená vo vnútri funkcie SUM a používa sa na vytvorenie dynamického koncového bodu na rozsah údajov, ktorý je celkom vzorec. To sa dosiahne nastavením koncového bodu rozsahu na jednu bunku nad umiestnením vzorca.

Syntax vzorca:

= SUM (Rozsah štartu: OFFSET (referencia, riadky, stĺpce))

• Rozsah štartu: Vyžaduje sa začiatočný bod pre rozsah buniek, ktoré sa majú doplniť funkciou SUM. V príklade obrázku je to bunka B2.
• referencie: Požadovaná bunková referencia použitá na výpočet koncového bodu rozsahu je umiestnená v mnohých riadkoch a stĺpcoch. Na obrázku v príklade referenčný argument je odkaz na bunku pre samotný vzorec, pretože vždy chcete, aby rozsah ukončil jednu bunku nad vzorecom.
• riadky: Počet riadkov nad alebo pod referenčným argumentom, ktorý sa používa pri výpočte posunu, je potrebný. Táto hodnota môže byť pozitívna, negatívna alebo nastavená na nulu. Ak je poloha ofsetu vyššia ako referencie argument, hodnota je negatívna. Ak je nižšie, argument Rows je pozitívny. Ak je posun umiestnený v rovnakom riadku, argument je nulový. V tomto príklade posun začína jeden riadok nad referenčným argumentom, takže hodnota pre tento argument je záporná (-1).
• cols: Počet stĺpcov vľavo alebo vpravo od referenčného argumentu použitý pri výpočte posunu sa vyžaduje. Táto hodnota môže byť pozitívna, negatívna alebo nastavená na nulu. Ak je umiestnenie posunu vľavo od referenčného argumentu, táto hodnota je záporná. Ak je napravo, argument Cols je pozitívny. V tomto príklade sú úplné údaje v rovnakom stĺpci ako vzorec, takže hodnota pre tento argument je nula.

Použitie vzorca SUM OFFSET na celkové údaje o predaji

Tento príklad používa vzorec SUM OFFSET na vrátenie sumy pre denné údaje o predaji uvedené v stĺpci B pracovného hárka.

Spočiatku bol vzorec vložený do bunky B6 a celkový objem predajov bol na štyri dni.

Ďalším krokom je presunúť vzorec SUM OFFSET dole v riadku, čím sa vytvorí priestor pre predaj celkovo piateho dňa. To sa dosiahne vložením nového riadku 6, ktorý posunie vzorec do riadku 7.

V dôsledku presunu program Excel automaticky aktualizuje referenčný argument na bunkuB7 a pridá bunkuB6 do rozsahu sumarizovaného vzorecom.

Zadanie vzorca SUM OFFSET

1. Kliknite na bunku B6, čo je miesto, kde sa najskôr zobrazia výsledky vzorca.
2. Klikni navzorca v ponuke pásky.
3. zvoliťMath & Trigz pásu k otvoreniu rolovacieho zoznamu funkcií.
4. Kliknite naSUM v dialógovom okne funkcie otvorte dialógové okno funkcie.
5. V dialógovom okne kliknite naČíslo 1linka.
6. Kliknite na bunku B2 zadajte tento odkaz na bunku do dialógového okna. Toto umiestnenie je statický koncový bod pre vzorec.
7. V dialógovom okne kliknite nanumber2linka.
8. Zadajte nasledujúcu funkciu OFFSET: OFFSET (B6, -1,0) na vytvorenie dynamického koncového bodu pre vzorec.
9. kliknite OK dokončite funkciu a zatvorte dialógové okno.

Celková suma sa zobrazí v bunke B7.

Pridávanie predajných dát nasledujúceho dňa

Ak chcete pridať údaje o predaji nasledujúceho dňa:

1. Kliknutím pravým tlačidlom myši na hlavičku riadku pre riadok 6 otvoríte kontextovú ponuku.
2. V ponuke kliknite nainsert vložiť do pracovného hárka nový riadok.
3. V dôsledku toho sa vzorec SUM OFFSET posunie do bunky B7 a riadok 6 je teraz prázdny.
4. Kliknite na bunku A6.
5. Zadajte číslo5 aby ste uviedli, že sa zapisuje celkový predaj za piaty deň.
6. Kliknite na bunku B6.
7. Zadajte číslo$1458.25 a stlačte tlačidlovstúpiť na klávesnici.

Bunka B7 aktualizuje novú celkovú hodnotu$7137.40.

Keď kliknete na bunku B7, aktualizovaný vzorec= SUM (B2: OFFSET (B7, -1,0)) sa objaví vo vzorcovom riadku.

Funkcia OFFSET má dva voliteľné argumenty: Výška a šírka , ktoré neboli použité v tomto príklade.

Tieto argumenty môžu byť použité na to, aby funkcia OFFSET vyložila tvar výstupu z hľadiska toľkých riadkov vysokých a toľkých stĺpcov širokých.

Vynechaním týchto argumentov funkcia v predvolenom nastavení používa namiesto toho výšku a šírku referenčného argumentu, čo je v tomto príklade vysoký jeden riadok a jeden stĺpec široký.