Excel SUM in OFFSET Formula

Uporabite SUM in OFFSET, da najdete vsote za dinamične razpone podatkov

Če vaš delovni list Excel vključuje izračune, ki temeljijo na spreminjajočem se območju celic, z uporabo funkcij SUM in OFFSET skupaj v obliki SUM OFFSET poenostavlja nalogo, da se izračuni posodabljajo.

Ustvarite dinamični obseg s funkcijami SUM in OFFSET

Če uporabljate izračune za določeno časovno obdobje, ki se nenehno spreminja - na primer vsota prodaje za mesec - funkcija OFFSET vam omogoča, da nastavite dinamično območje, ki se spreminja, saj so dodani podatki o prodaji vsak dan.

Funkcija SUM sama po sebi lahko običajno prilagodi nove celice podatkov, ki se vstavijo v obseg, ki se sumira.

Ena izjema se zgodi, ko se podatki vstavijo v celico, kjer se trenutno nahaja funkcija.

V vzorčni sliki, ki je priložena temu članku, se nove številke prodaje za vsak dan doda na dno seznama, zaradi česar se skupni stalno pomika navzdol po eni celici vsakič, ko se dodajo novi podatki.

Če je bila funkcija SUM sam uporabljena za skupno ujemanje podatkov, bi bilo treba ob vsakem dodajanju novih podatkov spremeniti obseg celic, uporabljenih kot argument funkcije.

Vendar z uporabo funkcij SUM in OFFSET skupaj obseg, ki se ujema, postane dinamičen. Z drugimi besedami, se spremeni, da se prilagodi nove celice podatkov. Dodajanje novih podatkovnih celic podatkov ne povzroča težav, ker se obseg še naprej prilagaja, ko je dodana vsaka nova celica.

Sintaksa in argumenti

Oglejte si sliko, ki spremlja ta članek in sledite tej vadnici.

V tej formuli se funkcija SUM uporablja za združevanje obsega podatkov, ki jih dobimo kot argument. Začetna točka za to območje je statična in je označena kot referenca celice na prvo številko, ki jo je treba izračunati po formuli.

Funkcija OFFSET je ugnezdena znotraj funkcije SUM in se uporablja za ustvarjanje dinamične končne točke v obsegu podatkov, ki jih ujema formula. To se doseže z nastavitvijo končne točke območja na eno celico nad lokacijo formule.

Sintaksa formule:

= SUM (obseg začetka: OFFSET (referenca, vrstice, cols))

Območje Začetek - (potrebno) izhodiščno točko za obseg celic, ki se bo združila s funkcijo SUM. V sliki primera je to celica B2.

Referenca - (zahteva) sklic celice, ki se uporablja za izračun končne točke območja, ki se nahaja več vrstic in stolpcev. V sliki primera je referenčni argument referenca celice za samo formulo, saj vedno želimo, da območje konča eno celico nad formulo.

Vrstice - (zahtevano) število vrstic nad ali pod referenčnim argumentom, uporabljenim pri izračunu odmika. Ta vrednost je lahko pozitivna, negativna ali nastavljena na nič.

Če je lokacija odmika nad referenčnim argumentom, je ta vrednost negativna. Če je spodaj, je argument Rows pozitiven. Če se odmik nahaja v isti vrstici, je ta argument nič. V tem primeru offset začne eno vrstico nad referenčnim argumentom, zato je vrednost za ta argument negativna (-1).

Cols - (potrebno) število stolpcev na levi ali desni strani referenčnega argumenta, uporabljenega pri izračunu odmika. Ta vrednost je lahko pozitivna, negativna ali nastavljena na nič

Če je lokacija odmika levo od referenčnega argumenta, je ta vrednost negativna. Če je desno, je argument Cols pozitiven. V tem primeru so skupni podatki v istem stolpcu kot formula, zato je vrednost za ta argument nič.

Uporaba formule SUM OFFSET za skupne prodajne podatke

V tem primeru je uporabljena formula SUM OFFSET, da se vrednosti za dnevne prodajne številke, navedene v stolpcu B delovnega lista, vrnejo.

Prvotno je bila formula vnesena v celico B6 in štiri dni prodala podatke o prodaji.

Naslednji korak je premakniti formulo SUM OFFSET navzdol v vrstico, da bi dosegli prostor za peti dan prodaje.

To se doseže z vstavitvijo nove vrstice 6, ki premakne formulo v vrstico 7.

Kot rezultat premika, Excel samodejno posodablja referenčni argument na celico B7 in doda celico B6 v obseg, ki ga povzema formula.

Vstop v formulo SUM OFFSET

1. Kliknite na celico B6, ki je mesto, na katerem bodo najprej prikazani rezultati formule.
2. Kliknite jeziček Formule v meniju traku .
3. Od traku izberite Math & Trig, da odprete spustni seznam funkcije.
4. Na seznamu kliknite SUM, da odpre pogovorno okno funkcije.
5. V pogovornem oknu kliknite na številko 1.
6. Kliknite celico B2, da vnesete referenco celice v pogovorno okno. Ta lokacija je statična končna točka za formulo;
7. V pogovornem oknu kliknite na številko 2.
8. Vnesite naslednjo funkcijo OFFSET: OFFSET (B6, -1,0), da oblikujete dinamično končno točko za formulo.
9. Za dokončanje funkcije kliknite gumb V redu in zaprite pogovorno okno.

Skupaj $ 5679.15 se pojavi v celici B7.

Ko kliknete celico B3, se v vrstici s formulo nad delovnim listom prikaže celotna funkcija = SUM (B2: OFFSET (B6, -1,0)) .

Dodajanje prodajnih podatkov naslednjega dne

Če želite dodati podatke o prodaji naslednjega dne:

1. Z desno miškino tipko kliknite vrstico vrstice za vrstico 6, da odprete kontekstni meni.
2. V meniju kliknite na Vstavi, da v delovni list vstavite novo vrstico.
3. Zato se formula SUM OFFSET premakne v celico B7, vrstica 6 pa je prazna.
4. Kliknite na celico A6 .
5. Vnesite številko 5, da označite, da se vnese prodajna vsota za peti dan.
6. Kliknite na celico B6.
7. Vnesite številko $ 1458.25 in pritisnite tipko Enter na tipkovnici.

Celica B7 posodobi novo skupno vrednost 7137,40 USD.

Ko kliknete celico B7, se v vrstici s formulo prikaže posodobljena formula = SUM (B2: OFFSET (B7, -1,0)) .

Opomba : Funkcija OFFSET ima dva izbirna argumenta: Višina in Širina, ki sta bili v tem primeru izpuščeni.

Te argumente lahko uporabite, če želite funkciji OFFSET sporočiti obliko izhoda v smislu, da je toliko vrstic visoko in toliko stolpcev široko.

Z izpuščanjem teh argumentov funkcija privzeto uporabi višino in širino referenčnega argumenta, ki je v tem primeru ena vrstica visoka in en stolpec širok.