Koristite SUM i OFFSET da biste pronašli ukupne vrednosti za dinamičke opsege podataka
Ako vaš Excel radni list uključuje proračune bazirane na promjenljivom opsegu ćelija, korištenjem funkcija SUM i OFFSET zajedno u SUM OFFSET formulaciji pojednostavljuje zadatak održavanja proračuna.
Kreirajte dinamički opseg pomoću funkcija SUM i OFFSET
Ako koristite proračune za vremenski period koji se neprestano mijenja - kao što je ukupna prodaja za mjesec - funkcija OFFSET vam omogućava da podesite dinamički opseg koji se mijenja kako se dodaju brojke prodaje svakog dana.
Sama po sebi, funkcija SUM može obično da primi nove ćelije podataka koji se unose u opseg koji se sumira.
Jedan izuzetak se dešava kada se podaci ubacuju u ćeliju u kojoj se trenutno nalazi funkcija.
Na primeru slike koja prati ovaj članak, nove brojke za prodaju za svaki dan se dodaje na dnu liste, što dovodi do toga da se ukupan broj neprekidno pomera po jednu ćeliju svaki put kada se dodaju novi podaci.
Ako je SUM funkcija korišćena samostalno za potpunu podjelu podataka, bilo bi potrebno izmijeniti opseg ćelija koji se koriste kao argument funkcije svaki put kada su dodati novi podaci.
Međutim, koristeći SUM i OFFSET funkcije zajedno, opseg koji je totaliran postaje dinamičan. Drugim riječima, ona se mijenja kako bi prilagodila nove ćelije podataka. Dodavanje novih ćelija podataka ne uzrokuje probleme, jer opseg nastavlja da se prilagođava kada se dodaju nove ćelije.
Sintaksa i argumenata
Pogledajte sliku koja prati ovaj članak da pratite ovaj vodič.
U ovoj formuli, funkcija SUM se koristi za potpuni raspon podataka koji se dobijaju kao njegov argument. Startna tačka za ovaj opseg je statična i identifikovana je kao referenca ćelije na prvi broj koji će se izračunati formulom.
Funkcija OFFSET je ugnežena unutar funkcije SUM i koristi se za kreiranje dinamičke krajnje tačke do opsega podataka koji se formuliše u formuli. Ovo se postiže postavljanjem krajnje tačke opsega na jednu ćeliju iznad lokacije formule.
Sintaksa formula:
= SUM (početak opsega: OFFSET (referenca, redovi, colovi))Opseg Početak - (potrebno) polazna tačka za opseg ćelija koje će se izvršiti pomoću funkcije SUM. Na slici slike, ovo je ćelija B2.
Referenca - (obavezno) referenca ćelije koja se koristi za izračunavanje krajnje tačke opsega nalazi se na više redova i kolona. Na slici slike, referentni argument je reference ćelija za samu formulu, jer mi uvek želimo da opseg završi jednu ćeliju iznad formule.
Redovi - (potreban) broj redova iznad ili ispod Referentnog argumenta koji se koristi za izračunavanje ofseta. Ova vrijednost može biti pozitivna, negativna ili podešena na nulu.
Ako je lokacija offsa iznad referentnog argumenta, ova vrednost je negativna. Ako je ispod, argument Rows je pozitivan. Ako se offset nalazi u istom redu, ovaj argument je nula. U ovom primeru offset počinje jedan red iznad referentnog argumenta, tako da je vrijednost za ovaj argument negativna (-1).
Cols - (potreban) broj kolona sa leve ili desne strane referentnog argumenta koji se koristi za izračunavanje ofseta. Ova vrijednost može biti pozitivna, negativna ili podešena na nulu
Ako je položaj offset-a levo od referentnog argumenta, ova vrednost je negativna. Ako je desno, argument Colsa je pozitivan. U ovom primjeru, podaci koji se totaliraju nalaze se u istoj koloni kao formula, tako da je vrijednost za ovaj argument nula.
Korišćenjem SUM OFFSET Formule na ukupne prodajne podatke
Ovaj primjer koristi SUM OFFSET formulu za vraćanje ukupnih vrijednosti dnevne prodaje navedene u koloni B na radnom listu.
U početku, formula je uneta u ćeliju B6 i iznosila je podatke o prodaji za četiri dana.
Sledeći korak je da premestite SUM OFFSET formulu niz nizom kako biste napravili prostor za ukupni ukupan iznos petog dana.
Ovo se postiže unošenjem novog reda 6, koji pomera formulu na red 7.
Kao rezultat tog poteza, Excel automatski ažurira referentni argument na ćeliju B7 i dodaje ćeliju B6 u opseg koji se svodi prema formuli.
Ulazak u SUM OFFSET Formulu
- Kliknite na ćeliju B6, na kojoj će se na početku prikazati rezultati formule.
- Kliknite na karticu Formule u meniju trake .
- Odaberite Math & Trig sa trake da biste otvorili funkciju padajuće liste.
- Kliknite na SUM na listi da biste prikazali dijaloški okvir funkcije.
- U dijalogu kliknite na liniju Number1 .
- Kliknite na ćeliju B2 da biste uneli ovu referencu ćelije u dijaloški okvir. Ova lokacija je statička krajnja tačka za formulu;
- U dijalogu kliknite na liniju Number2 .
- Unesite sledeću OFFSET funkciju: OFFSET (B6, -1,0) da biste formirali dinamičku krajnju tačku za formulu.
- Kliknite OK da biste dovršili funkciju i zatvorili dijaloški okvir.
Ukupno 5679,15 dolara pojavljuje se u ćeliji B7.
Kada kliknete na ćeliju B3, kompletna funkcija = SUM (B2: OFFSET (B6, -1,0)) se pojavljuje u traci formule iznad radnog lista.
Dodavanje podataka o sledećem danu prodaje
Da biste dodali sledeće podatke o prodaji:
- Desnim tasterom miša kliknite na zaglavlje redova za red 6 da biste otvorili kontekstni meni.
- U meniju kliknite na Insert da biste uneli novi red u radni list.
- Kao rezultat, formula SUM OFFSET se pomera na ćeliju B7, a red 6 je sada prazan.
- Kliknite na ćeliju A6 .
- Unesite broj 5 da biste naznačili da se unosi ukupan promet petog dana.
- Kliknite na ćeliju B6.
- Unesite broj $ 1458.25 i pritisnite taster Enter na tastaturi.
Cell B7 ažurira novi iznos od 7137,40 dolara.
Kada kliknete na ćeliju B7, ažurirana formula = SUM (B2: OFFSET (B7, -1,0)) se pojavljuje u traci formule.
Napomena : OFFSET funkcija ima dva opciona argumenta: visina i širina, koji su izostavljeni u ovom primeru.
Ovi argumenti se mogu koristiti da se OFFSET funkciji pokaže oblik izlaza u smislu da je toliko redova visok i toliko kolona.
Izostavljanjem ovih argumenata, funkcija podrazumevano koristi visinu i širinu referentnog argumenta, što je u ovom primeru jedan red i jedan širok kolona.