Formula Excel SUM i OFFSET

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 proračun koristite za neprekidno vremensko razdoblje - kao što je ukupna prodaja za mjesec - funkcija OFFSET u programu Excel omogućuje vam 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 umetanje novih stanica podataka u raspon koji se zbraja.

Izuzetak se događa kada se podaci umetnu u ćeliju u kojoj se trenutno nalazi funkcija.

U gornjoj primjernoj slici novim prodajnim brojevima za svaki dan dodaju se na dnu popisa, prisiljavajući ukupno da neprestano pomakne 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 gornju sliku 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))

• Početak raspona: Potrebna je polazna točka za raspon ćelija koji će biti zbrojeni SUM funkcijom. U primjeru slike, to je stanica B2.
• upućivanje: Potrebna referenca ćelije koja se koristi za izračun krajnje točke raspona nalazi se daleko od redaka i stupaca. U primjeru slike Referentni argument je referenca ćelije za samu formulu jer uvijek želite da raspon završi jednom ćelijom iznad formule.
• redovi: Potreban je 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 upućivanje argument, vrijednost je negativna. Ako je ispod, argument Rows je pozitivan. Ako je offset smješten u istom retku, argument je nula. U ovom primjeru offset započinje jedan redak iznad referentnog argumenta, pa je vrijednost za ovaj argument negativna (-1).
• stupaca: Potreban je 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 je vrijednost za ovaj argument 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 u ćelijuB7 i dodaje ćelijuB6 do raspona sažeto formulom.

Unos SUM OFFSET formule

1. Kliknite na ćeliju B6, koja je mjesto na kojem će se početno prikazivati ​​rezultati formule.
2. Klikni naformule karticu izbornika vrpce.
3. izabratiMath & Trigs trake za otvaranje padajućeg popisa funkcije.
4. Kliknite naIZNOS na popisu da biste otvorili dijaloški okvir funkcije.
5. U dijaloškom okviru klikniteBroj 1crta.
6. Kliknite na ćeliju B2 za unos ove reference ćelije u dijaloški okvir. Ta je lokacija statična krajnja točka za formulu.
7. U dijaloškom okviru kliknitebroj2crta.
8. Unesite sljedeću OFFSET funkciju: OFFSET (B6, -1,0) za stvaranje dinamičke krajnje točke za formulu.
9. Klik u redu za dovršetak funkcije i zatvaranje dijaloškog okvira.

Ukupno se pojavljuje u ćeliji B7.

Dodavanje podataka o prodaji sljedećeg dana

Da biste dodali prodajne podatke sljedećeg dana:

1. Desnom tipkom miša kliknite zaglavlje retka za redak 6 kako biste otvorili kontekstni izbornik.
2. Na izborniku klikniteUmetnuti za umetanje novog retka u radni list.
3. Kao rezultat toga, formula SUM OFFSET se spušta do ćelije B7, a redak 6 je sada prazan.
4. Kliknite na ćeliju A6.
5. Unesite broj5 da bi se naznačilo da se unosi prodajni iznos za petog dana.
6. Kliknite na ćeliju B6.
7. Upišite broj$1458.25 i pritisniteUnesi na tipkovnici.

Stanični B7 ažurira se na novi$7137.40.

Kada kliknete na ćeliju B7, ažuriranu formulu= SUM (B2: OFFSET (B7, -1,0)) pojavljuje se u traci s formulama.

OFFSET funkcija ima dva opcionalna argumenta: Visina i Širina , koji nisu korišteni u ovom primjeru.

Ovi argumenti se mogu koristiti za reći OFFSET funkciji oblik izlaza u pogledu tolikih redova visokih 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.