Koristite SUM i OFFSET za pronalaženje ukupnih podataka za dinamičke raspone podataka
Ako vaš Excel radni list uključuje izračune na temelju promjenjivog raspona ćelija, SUM i OFFSET funkcije zajedno u SUM OFFSET formatu pojednostavljuju zadatak održavanja izračuna do datuma.
Izradite dinamički raspon s funkcijama SUM i OFFSET
Ako koristite izračune za neprekidno vremensko razdoblje - kao što je ukupna prodaja mjeseca - funkcija OFFSET omogućuje postavljanje dinamičkog raspona koji se mijenja kako se dodaju prodajne brojke za svaki dan.
Sam po sebi, SUM funkcija obično može ugostiti nove stanice podataka koji se umetnu u raspon koji se zbraja.
Izuzetak se događa kada se podaci umetnu u ćeliju u kojoj se trenutno nalazi funkcija.
U primjeru slike koja prati ovaj članak, nove dimenzije prodaje svakog dana dodaju se na dno popisa, što prisiljava ukupno da neprekidno mijenja jednu ćeliju svaki put kad se dodaju novi podaci.
Ako je SUM funkcija korištena sama za ukupno podatke, bilo bi neophodno mijenjati raspon ćelija koje se koriste kao argument funkcije svaki put kad su dodani novi podaci.
Korištenjem SUM i OFFSET funkcija, međutim, raspon koji je zbrojen postaje dinamičan. Drugim riječima, ona se mijenja kako bi se prilagodile nove stanice podataka. Dodavanje novih ćelija podataka ne uzrokuje probleme jer se raspon i dalje podešava prilikom dodavanja svake nove ćelije.
Sintaksa i argumente
Pogledajte sliku koja prati ovaj članak da biste slijedili zajedno s ovom udžbenikom.
U ovoj formuli, SUM funkcija koristi se kako bi ukupni raspon podataka koji su dobili kao njezin argument. Polazna točka za ovaj raspon je statična i identificira se kao referenca ćelije na prvi broj koji treba zbrojiti formulom.
OFFSET funkcija je ugniježđena unutar funkcije SUM i koristi se za stvaranje dinamičke krajnje točke za raspon podataka ukupne formule. To se postiže postavljanjem krajnje točke raspona na jednu ćeliju iznad položaja formule.
Sintaksa formule:
= SUM (Početak raspona: OFFSET (Referenca, redci, Cols))Raspon Start - (potrebna) polazna točka za raspon ćelija koji će biti zbrojeni SUM funkcijom. U primjeru slike, to je stanica B2.
Referenca - (potrebno) referenca ćelije koja se koristi za izračun krajnje točke raspona koja se nalazi daleko od redaka i stupaca. U primjeru slike referentni argument je referenca ćelije za samu formulu jer uvijek želimo da raspon završi jednom ćelijom iznad formule.
Redci - (potrebno) broj redaka iznad ili ispod Referentnog argumenta koji se koristi za izračun offseta. Ta vrijednost može biti pozitivna, negativna ili postavljena na nulu.
Ako je položaj offseta iznad referentnog argumenta, ta je vrijednost negativna. Ako je ispod, argument Rows je pozitivan. Ako je pomak smješten u istom retku, taj je argument nula. U ovom primjeru offset započinje jedan redak iznad referentnog argumenta, pa je vrijednost za ovaj argument negativna (-1).
Cols - (potrebno) broj stupaca s lijeve ili desne strane Referentnog argumenta koji se koristi za izračun offseta. Ta vrijednost može biti pozitivna, negativna ili postavljena na nulu
Ako je položaj offset-a lijevo od referentnog argumenta, ta je vrijednost negativna. Ako je desno, Colsov argument je pozitivan. U ovom primjeru ukupni podaci iznose isti stupac kao i formula pa vrijednost za ovaj argument iznosi nula.
Upotreba SUM OFFSET formule do ukupnih podataka o prodaji
Ovaj primjer koristi SUM OFFSET formulu kako bi vratio ukupan broj dnevnih prodanih podataka navedenih u stupcu B radnog lista.
U početku, formula je unesena u ćeliju B6 i ukupni podaci o prodaji četiri dana.
Sljedeći korak je premjestiti SUM OFFSET formu niz red kako bi napravili mjesta za petodnevni prodajni iznos.
To se postiže umetanjem novog retka 6, koji pomiče formulu na redak 7.
Kao rezultat premještanja, Excel automatski ažurira referentni argument na ćeliju B7 i dodaje ćeliju B6 u raspon zbrajan formulom.
Unos SUM OFFSET formule
- Kliknite na ćeliju B6, koja je mjesto na kojem će se početno prikazivati rezultati formule.
- Kliknite karticu formule na traci izbornika.
- Izaberite Math & Trig iz trake za otvaranje padajućeg popisa funkcije.
- Kliknite na SUM na popisu kako biste otvorili dijaloški okvir funkcije.
- U dijaloškom okviru kliknite redak Number1 .
- Kliknite na ćeliju B2 da biste unijeli ovu ćeliju u dijaloški okvir. Ova je lokacija statična krajnja točka za formulu;
- U dijaloškom okviru kliknite redak Number2 .
- Unesite sljedeću funkciju OFFSET: OFFSET (B6, -1,0) kako biste stvorili dinamičku krajnju točku za formulu.
- Kliknite U redu da biste dovršili funkciju i zatvorili dijaloški okvir.
Ukupno 5679,15 dolara pojavljuje se u ćeliji B7.
Kada kliknete na ćeliju B3, u baru formule iznad radnog lista pojavit će se cjelovita funkcija = SUM (B2: OFFSET (B6, -1,0)) .
Dodavanje podataka o prodaji sljedećeg dana
Da biste dodali prodajne podatke sljedećeg dana:
- Desnom tipkom miša kliknite zaglavlje retka za redak 6 kako biste otvorili kontekstni izbornik.
- Na izborniku kliknite Umetni da biste umetnuli novi redak u radni list.
- Kao rezultat toga, formula SUM OFFSET se spušta do ćelije B7, a redak 6 je sada prazan.
- Kliknite na ćeliju A6 .
- Unesite broj 5 kako biste naznačili da se ukupni prodajni iznos za petu godinu unosi.
- Kliknite na ćeliju B6.
- Upišite broj 1458,25 dolara i pritisnite tipku Enter na tipkovnici.
Stanica B7 ažurira se na novi iznos od 7137,40 dolara.
Kada kliknete na ćeliju B7, ažurirana formula = SUM (B2: OFFSET (B7, -1,0)) pojavljuje se u traci s formulom.
Napomena : OFFSET funkcija ima dva opcionalna argumenta: Visina i Širina, koji su izostavljeni u ovom primjeru.
Ti se argumenti mogu koristiti za detekciju OFFSET funkcije oblik izlaza u smislu da je toliko redaka visok i toliko širokih stupaca.
Izostavljajući ove argumente, funkcija, prema zadanim postavkama, umjesto toga koristi visinu i širinu referentnog argumenta, što je u ovom primjeru jedan red visok i jedan široki stupac.