Ako vaš Excel radni list uključuje proračune koji se zasnivaju na promjenjivom rasponu ćelija, koristite funkcije SUM i OFFSET zajedno u formuli SUM OFFSET kako biste pojednostavili zadatak ažuriranja izračuna.
Upute u ovom članku odnose se na Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013 i Excel 2010.
Kreirajte dinamički raspon sa funkcijama SUM i OFFSET
Ako koristite kalkulacije za vremenski period koji se stalno mijenja - kao što je određivanje prodaje za mjesec - koristite funkciju OFFSET u Excelu da postavite dinamički raspon koji se mijenja kako se dodaju podaci o prodaji svakog dana.
Sama po sebi, funkcija SUM obično može prihvatiti umetanje novih ćelija podataka u opseg koji se sumira. Jedan izuzetak se javlja kada se podaci umetnu u ćeliju u kojoj se funkcija trenutno nalazi.
U primjeru ispod, novi podaci o prodaji za svaki dan se dodaju na dno liste, prisiljavajući ukupni iznos da se stalno pomiče za jednu ćeliju svaki put kada se dodaju novi podaci.
Da biste pratili ovaj vodič, otvorite prazan Excel radni list i unesite uzorke podataka. Vaš radni list ne mora biti formatiran kao u primjeru, ali obavezno unesite podatke u iste ćelije.
Ako se za zbroj podataka koristi samo funkcija SUM, raspon ćelija korištenih kao argument funkcije bi trebao biti modificiran svaki put kada se dodaju novi podaci.
Upotrebom funkcija SUM i OFFSET zajedno, opseg koji se zbroji postaje dinamičan i mijenja se kako bi se prilagodile 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 se koristi da zbroji opseg podataka koji se unose kao argument. Početna tačka za ovaj raspon je statična i identificira se kao referenca ćelije na prvi broj koji se zbroji po formuli.
Funkcija OFFSET je ugniježđena unutar funkcije SUM i stvara dinamičku krajnju tačku za raspon podataka zbrojenih formulom. Ovo se postiže postavljanjem krajnje tačke opsega na jednu ćeliju iznad lokacije formule.
Sintaksa formule je:
=SUM(Početak raspona:OFFSET(referenca, redovi, kolone))
Argumenti su:
- Početak raspona: Početna tačka za raspon ćelija koje će se zbrojiti funkcijom SUM. U ovom primjeru, početna tačka je ćelija B2.
- Referenca: Potrebna referenca ćelije koja se koristi za izračunavanje krajnje tačke raspona. U primjeru, argument Reference je referenca ćelije za formulu jer raspon završava jednu ćeliju iznad formule.
- Redovi: Potreban je broj redova 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 lokacija pomaka iznad argumenta Reference, vrijednost je negativna. Ako je pomak ispod, argument Rows je pozitivan. Ako se pomak nalazi u istom redu, argument je nula. U ovom primjeru, pomak počinje jedan red iznad argumenta Reference, tako da je vrijednost za argument negativna jedan (-1).
- Cols: Broj kolona 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 lokacija pomaka lijevo od argumenta Reference, ova vrijednost je negativna. Ako je pomak udesno, argument Cols je pozitivan. U ovom primjeru, podaci koji se zbrajaju nalaze se u istoj koloni 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 broja dnevnih prodaja navedenih u koloni B radnog lista. Prvobitno, formula je unesena u ćeliju B6 i zbrojila je podatke o prodaji za četiri dana.
Sljedeći korak je pomicanje formule SUM OFFSET za jedan red dolje kako bi se napravio prostor za ukupnu prodaju petog dana. Ovo se postiže umetanjem novog reda 6, koji pomera formulu u red 7.
Kao rezultat premještanja, Excel automatski ažurira argument Referenca u ćeliju B7 i dodaje ćeliju B6 u raspon zbrojen po formuli.
- Odaberite ćeliju B6, što je lokacija na kojoj će se rezultati formule inicijalno prikazati.
-
Odaberite karticu Formule na traci.
Image -
Odaberite Math & Trig.
Image -
Odaberite SUM.
Image - U dijaloškom okviru Function Arguments, postavite kursor u okvir za tekst Number1.
-
U radnom listu izaberite ćeliju B2 da unesete ovu referencu ćelije u dijaloški okvir. Ova lokacija je statička krajnja tačka za formulu.
Image - U dijaloškom okviru Function Arguments, postavite kursor u okvir za tekst Number2.
-
Unesite OFFSET(B6, -1, 0). Ova funkcija OFFSET formira dinamičku krajnju tačku za formulu.
Image -
Odaberite OK da dovršite funkciju i zatvorite okvir za dijalog. Ukupan iznos se pojavljuje u ćeliji B6.
Image
Dodajte podatke o prodaji sljedećeg dana
Za dodavanje podataka o prodaji sljedećeg dana:
- Kliknite desnim tasterom miša na zaglavlje reda za red 6.
-
Izaberite Insert da umetnete novi red u radni list. Formula SUM OFFSET pomiče se za jedan red dolje do ćelije B7 i red 6 je sada prazan.
Image - Izaberite ćeliju A6 i unesite broj 5 da naznačite da se unosi ukupna prodaja za peti dan.
-
Odaberite ćeliju B6, unesite $1458,25, a zatim pritisnite Enter..
Image - Ćelija B7 ažurira na novi iznos od $7137.40.
Kada odaberete ćeliju B7, ažurirana formula se pojavljuje u traci formule.
=SUM(B2:OFFSET(B7, -1, 0))
Funkcija OFFSET ima dva opciona argumenta: Visinu i širinu, koji nisu korišteni u ovom primjeru. Ovi argumenti govore funkciji OFFSET oblik izlaza u smislu broja redova i kolona.
Izostavljanjem ovih argumenata, funkcija umjesto toga koristi visinu i širinu argumenta Reference, koji je u ovom primjeru visok jedan red i širok jedan stupac.
