Ako vaš Excel radni list uključuje izračune koji se temelje na promjenjivom rasponu ćelija, koristite funkcije SUM i OFFSET zajedno u formuli SUM OFFSET kako biste pojednostavili zadatak održavanja izračuna ažurnim.
Upute u ovom članku odnose se na Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013 i Excel 2010.
Stvorite dinamički raspon s funkcijama SUM i OFFSET
Ako koristite izračune za vremenski period koji se neprestano mijenja - kao što je određivanje prodaje za mjesec - koristite funkciju OFFSET u Excelu za postavljanje dinamičkog raspona koji se mijenja kako se zbrajaju brojke prodaje za svaki dan.
Sama po sebi, funkcija SUM obično može omogućiti umetanje novih ćelija podataka u raspon koji se zbraja. Jedna se iznimka događa kada se podaci umetnu u ćeliju u kojoj se funkcija trenutno nalazi.
U donjem primjeru, nove brojke prodaje za svaki dan dodaju se na dnu popisa, prisiljavajući ukupan pomak za jednu ćeliju prema dolje svaki put kada se dodaju novi podaci.
Da biste slijedili ovaj vodič, otvorite prazan Excel radni list i unesite ogledne podatke. Vaš radni list ne mora biti formatiran kao u primjeru, ali svakako unesite podatke u iste ćelije.
Ako se za zbrajanje podataka koristi samo funkcija SUM, raspon ćelija koje se koriste kao argument funkcije morat će se mijenjati svaki put kada se dodaju novi podaci.
Upotrebom funkcija SUM i OFFSET zajedno, zbrojeni raspon postaje dinamičan i mijenja se kako bi se prilagodio novim ćelijama podataka. Dodavanje novih ćelija podataka ne uzrokuje probleme jer se raspon nastavlja prilagođavati kako se dodaje svaka nova ćelija.
Sintaksa i argumenti
U ovoj formuli, funkcija SUM koristi se za zbrojavanje raspona podataka navedenih kao argument. Početna točka za ovaj raspon je statična i identificirana je kao referenca ćelije na prvi broj koji treba zbrojiti formulom.
Funkcija OFFSET ugniježđena je unutar funkcije SUM i stvara dinamičku krajnju točku raspona podataka zbrojenih formulom. To se postiže postavljanjem krajnje točke raspona na jednu ćeliju iznad lokacije formule.
Sintaksa formule je:
=SUM(Početak raspona:OFFSET(Referenca, Redovi, Stupci))
Argumenti su:
- Početak raspona: početna točka za raspon ćelija koje će zbrojiti funkcija SUM. U ovom primjeru početna točka je ćelija B2.
- Referenca: Potrebna referenca ćelije koja se koristi za izračun krajnje točke raspona. U primjeru, argument Referenca je referenca ćelije za formulu jer raspon završava jednu ćeliju iznad formule.
- Reci: Potreban je broj redaka iznad ili ispod Referentnog argumenta koji se koristi za izračunavanje pomaka. Ova vrijednost može biti pozitivna, negativna ili postavljena na nulu. Ako je mjesto pomaka iznad argumenta Referenca, vrijednost je negativna. Ako je pomak ispod, argument redaka je pozitivan. Ako se pomak nalazi u istom redu, argument je nula. U ovom primjeru, pomak počinje jedan redak iznad argumenta Referenca, tako da je vrijednost za argument negativan jedan (-1).
- Cols: Broj stupaca lijevo ili desno od referentnog argumenta koji se koristi za izračunavanje pomaka. Ova vrijednost može biti pozitivna, negativna ili postavljena na nulu. Ako je mjesto pomaka lijevo od argumenta Referenca, ta je vrijednost negativna. Ako je pomak udesno, argument Cols je pozitivan. U ovom primjeru, podaci koji se zbrajaju nalaze se u istom stupcu kao i formula, tako da je vrijednost za ovaj argument nula.
Koristite formulu SUM OFFSET za ukupne podatke o prodaji
Ovaj primjer koristi formulu SUM OFFSET za vraćanje ukupnog iznosa za dnevne prodaje navedene u stupcu B radnog lista. U početku je formula unesena u ćeliju B6 i zbrojila je podatke o prodaji za četiri dana.
Sljedeći korak je pomicanje formule SUM OFFSET za jedan red niže kako biste napravili mjesta za ukupnu prodaju za peti dan. To se postiže umetanjem novog retka 6, koji premješta formulu u redak 7.
Kao rezultat premještanja, Excel automatski ažurira argument Referenca na ćeliju B7 i dodaje ćeliju B6 u raspon zbrojen formulom.
- Odaberite ćeliju B6, koja je mjesto gdje će se rezultati formule inicijalno prikazati.
-
Odaberite karticu Formule na vrpci.
Image -
Odaberite Math & Trig.
Image -
Odaberi SUM.
Image - U dijaloškom okviru Argumenti funkcije, postavite kursor u tekstualni okvir Broj1.
-
Na radnom listu odaberite ćeliju B2 za unos ove reference ćelije u dijaloški okvir. Ova lokacija je statična krajnja točka za formulu.
Image - U dijaloškom okviru Argumenti funkcije postavite kursor u tekstualni okvir Number2.
-
Unesite OFFSET(B6, -1, 0). Ova funkcija OFFSET tvori dinamičku krajnju točku za formulu.
Image -
Odaberite OK za dovršetak funkcije i zatvaranje dijaloškog okvira. Zbroj se pojavljuje u ćeliji B6.
Image
Dodajte podatke o prodaji sljedećeg dana
Za dodavanje podataka o prodaji sljedećeg dana:
- Desnom tipkom miša kliknite zaglavlje retka za redak 6.
-
Odaberite Insert za umetanje novog reda u radni list. Formula SUM OFFSET pomiče se jedan red dolje do ćelije B7 i red 6 je sada prazan.
Image - Odaberite ćeliju A6 i unesite broj 5 kako biste označili da se unosi ukupna prodaja za peti dan.
-
Odaberite ćeliju B6, unesite $1458.25, zatim pritisnite Enter.
Image - Ćelija B7 ažurira se na novi ukupni iznos od 7137,40 USD.
Kada odaberete ćeliju B7, ažurirana formula pojavljuje se u traci formule.
=SUM(B2:OFFSET(B7, -1, 0))
Funkcija OFFSET ima dva izborna argumenta: Visina i Širina, koji nisu korišteni u ovom primjeru. Ovi argumenti govore funkciji OFFSET oblik izlaza u smislu broja redaka i stupaca.
Izostavljanjem ovih argumenata, funkcija umjesto toga koristi visinu i širinu argumenta Referenca, koji je u ovom primjeru jedan redak visok i jedan stupac širok.
