Če vaš Excelov delovni list vključuje izračune, ki temeljijo na spreminjajočem se obsegu celic, uporabite funkciji SUM in OFFSET skupaj v formuli SUM OFFSET, da poenostavite nalogo posodabljanja izračunov.
Navodila v tem članku veljajo za Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013 in Excel 2010.
Ustvarite dinamični razpon s funkcijama SUM in OFFSET
Če uporabljate izračune za časovno obdobje, ki se nenehno spreminja - na primer določanje prodaje za mesec - uporabite funkcijo OFFSET v Excelu, da nastavite dinamični obseg, ki se spreminja, ko se dodajajo številke prodaje za vsak dan.
Funkcija SUM sama po sebi običajno omogoča vstavljanje novih celic podatkov v obseg, ki se sešteva. Ena izjema se pojavi, ko so podatki vstavljeni v celico, kjer se trenutno nahaja funkcija.
V spodnjem primeru so nove prodajne številke za vsak dan dodane na dno seznama, zaradi česar se skupni znesek nenehno premakne za eno celico navzdol vsakič, ko so dodani novi podatki.
Če želite slediti tej vadnici, odprite prazen Excelov delovni list in vnesite vzorčne podatke. Vašega delovnega lista ni treba oblikovati kot v primeru, vendar pazite, da vnesete podatke v iste celice.
Če se za seštevek podatkov uporablja samo funkcija SUM, bi bilo treba obseg celic, uporabljenih kot argument funkcije, spremeniti ob vsakem dodajanju novih podatkov.
S skupno uporabo funkcij SUM in OFFSET postane sešteti obseg dinamičen in se spremeni tako, da se prilagodi novim celicam podatkov. Dodajanje novih celic s podatki ne povzroča težav, ker se obseg še naprej prilagaja, ko je dodana nova celica.
Sintaksa in argumenti
V tej formuli se funkcija SUM uporablja za seštevek obsega podatkov, podanih kot argument. Začetna točka tega obsega je statična in je označena kot referenca celice na prvo številko, ki jo formula sešteje.
Funkcija OFFSET je ugnezdena znotraj funkcije SUM in ustvari dinamično končno točko za obseg podatkov, ki jih sešteje formula. To dosežete tako, da končno točko obsega nastavite na eno celico nad mestom formule.
Sintaksa formule je:
=SUM(Začetek obsega:OFFSET(Sklic, vrstice, stolpci))
Argumenti so:
- Začetek obsega: Začetna točka za obseg celic, ki jih bo seštela funkcija SUM. V tem primeru je začetna točka celica B2.
- Reference: zahtevana referenca celice, uporabljena za izračun končne točke obsega. V primeru je argument Referenca sklic na celico za formulo, ker se obseg konča eno celico nad formulo.
- Rows: Zahtevano je število vrstic nad ali pod referenčnim argumentom, uporabljenim pri izračunu odmika. Ta vrednost je lahko pozitivna, negativna ali nastavljena na nič. Če je lokacija odmika nad argumentom Reference, je vrednost negativna. Če je odmik pod, je argument Vrstice pozitiven. Če se odmik nahaja v isti vrstici, je argument enak nič. V tem primeru se odmik začne eno vrstico nad argumentom Reference, tako da je vrednost argumenta negativna ena (-1).
- Cols: število stolpcev levo ali desno od referenčnega argumenta, uporabljenega za izračun odmika. Ta vrednost je lahko pozitivna, negativna ali nastavljena na nič. Če je lokacija odmika levo od argumenta Reference, je ta vrednost negativna. Če je odmik v desno, je argument Cols pozitiven. V tem primeru so podatki, ki se seštevajo, v istem stolpcu kot formula, zato je vrednost tega argumenta nič.
Uporabite formulo SUM OFFSET za skupne podatke o prodaji
Ta primer uporablja formulo SUM OFFSET za vrnitev vsote za dnevne prodajne številke, navedene v stolpcu B delovnega lista. Sprva je bila formula vnesena v celico B6 in je seštela podatke o prodaji za štiri dni.
Naslednji korak je premakniti formulo SUM OFFSET eno vrstico navzdol, da naredite prostor za skupno prodajo petega dne. To se doseže z vstavljanjem nove vrstice 6, ki premakne formulo v vrstico 7.
Kot rezultat premika, Excel samodejno posodobi argument Reference v celico B7 in doda celico B6 v obseg, sešteven s formulo.
- Izberite celico B6, ki je mesto, kjer bodo prvotno prikazani rezultati formule.
-
Izberite zavihek Formule na traku.
Image -
Izberite Math & Trig.
Image -
Izberi SUM.
Image - V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje Number1.
-
Na delovnem listu izberite celico B2, da v pogovorno okno vnesete sklic na to celico. Ta lokacija je statična končna točka za formulo.
Image - V pogovornem oknu Argumenti funkcije postavite kazalec v besedilno polje Number2.
-
Vnesite OFFSET(B6, -1, 0). Ta funkcija OFFSET tvori dinamično končno točko za formulo.
Image -
Izberite OK za dokončanje funkcije in zapiranje pogovornega okna. Skupni znesek je prikazan v celici B6.
Image
Dodaj podatke o prodaji za naslednji dan
Za dodajanje podatkov o prodaji za naslednji dan:
- Z desno miškino tipko kliknite glavo vrstice za vrstico 6.
-
Izberite Insert, da vstavite novo vrstico v delovni list. Formula SUM OFFSET se premakne eno vrstico navzdol v celico B7 in vrstica 6 je zdaj prazna.
Image - Izberite celico A6 in vnesite številko 5, da označite, da se vnaša skupni znesek prodaje za peti dan.
-
Izberite celico B6, vnesite $1458.25, nato pritisnite Enter.
Image - Celica B7 se posodobi na novo vsoto 7137,40 $.
Ko izberete celico B7, se posodobljena formula prikaže v vrstici s formulami.
=SUM(B2:OFFSET(B7, -1, 0))
Funkcija OFFSET ima dva neobvezna argumenta: Višina in Širina, ki v tem primeru nista bila uporabljena. Ti argumenti povedo funkciji OFFSET obliko izhoda glede na število vrstic in stolpcev.
Če izpustite te argumente, funkcija namesto tega uporabi višino in širino argumenta Reference, ki je v tem primeru ena vrstica visoka in en stolpec širok.
