Excel SUM a OFFSET Formula

Použite SUM a OFFSET na nájdenie súčtov pre dynamické rozsahy dát

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 umožňuje nastaviť dynamický rozsah, ktorý sa stále mení, keď sa pridávajú údaje o predaji z každého dňa.

Samotná funkcia SUM môže zvyčajne prispôsobiť nové bunky údajov, ktoré sa vložia do sčítavaného rozsahu.

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

V príklade obrázku, ktorý je priložený k tomuto článku, sú nové údaje o predaji za každý deň pridané do spodnej časti zoznamu, čo núti celú hodnotu, aby vždy pri každom pridaní nových údajov postupne posúvala jednu bunku nadol.

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, ktorý je priložený k tomuto článku.

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é budú celkom zoradené funkciou SUM. V príklade obrázku je to bunka B2.

Odkaz - (povinné) referencia bunky použitá na výpočet koncového bodu rozsahu, ktorý sa nachádza vo viacerých riadkoch a stĺpcoch. V príklade obrázku referenčný argument je referencia bunky pre samotný vzorec, pretože vždy chceme, aby rozsah ukončil jednu bunku nad vzorecom.

Riadky - (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ť pozitívna, negatívna alebo nastavená na nulu.

Ak je umiestnenie ofsetu nad referenčným argumentom, táto hodnota je záporná. Ak je nižšie, argument Rows je pozitívny. Ak je posun umiestnený v rovnakom riadku, tento 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 - (vyžaduje sa) počet stĺpcov vľavo alebo vpravo od referenčného argumentu použitého na výpočet posunu. 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 bunku B7 a pridá bunku B6 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. Kliknite na kartu Vzorce v ponuke pásky .
3. Zvoľte možnosť Math & Trig z kariet a otvorte rozbaľovací zoznam funkcií.
4. Kliknutím na SUM v zozname vyvoláte dialógové okno funkcie .
5. V dialógovom okne kliknite na riadok Číslo1 .
6. Kliknutím na bunku B2 zadáte odkaz do bunky do dialógového okna. Toto umiestnenie je statický koncový bod pre vzorec;
7. V dialógovom okne kliknite na riadok Číslo2 .
8. Zadajte nasledujúcu funkciu OFFSET: OFFSET (B6, -1,0) na vytvorenie dynamického koncového bodu pre vzorec.
9. Kliknutím na tlačidlo OK dokončite funkciu a zatvorte dialógové okno.

Celkom 5679,15 dolárov sa zobrazí v bunke B7.

Keď kliknete na bunku B3, zobrazí sa vo formulári nad pracovným hárkom kompletná funkcia = SUM (B2: OFFSET (B6, -1,0)) .

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 na Vložiť a vložte 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 číslo 5, aby ste naznačili, že sa zadáva celkový predaj za piaty deň.
6. Kliknite na bunku B6.
7. Zadajte číslo 1458.25 dolárov a stlačte kláves Enter na klávesnici.

Bunka B7 aktualizuje novú celkovú sumu 7137,40 USD.

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

Poznámka : Funkcia OFFSET má dva voliteľné argumenty: Výška a Šírka, ktoré boli v tomto príklade vynechané.

Tieto argumenty možno použiť na to, aby funkciu OFFSET informovala o tvare výstupu, pokiaľ ide o to, že je toľko riadkov vysoko a toľko stĺpcov široký.

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ý.