Excel SUM in OFFSET Formula

Č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 časovno obdobje, ki se nenehno spreminja - na primer vsota prodaje za mesec - funkcija OFFSET v programu Excel vam omogoča, da nastavite dinamičen obseg, ki se spremeni, saj se dodajo dnevne prodajne številke.

Funkcija SUM lahko sam po sebi običajno prilagodi vstavljanju novih celic podatkov v obseg, ki se sumira.

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

Na zgornji sliki primera so nove številke prodaje za vsak dan dodane na dnu seznama, s čimer se skupna vsota nenehno premika 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 zgornjo sliko, da 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))

• Začetek obsega: Izhodiščna točka za obseg celic, ki jo bo obsegala funkcija SUM, je obvezna. V sliki primera je to celica B2.
• Referenca: Zahtevana referenca celic, ki se uporablja za izračun končne točke območja, je veliko več vrstic in stolpcev. V sliki primera je referenčni argument referenca celice za samo formulo, ker vedno želite, da se obseg konča eno celico nad formulo.
• Vrstice: Potrebno je število vrstic nad ali pod referenčnim argumentom, uporabljenim pri izračunu kompenzacije. Ta vrednost je lahko pozitivna, negativna ali nastavljena na nič. Če je položaj odmika nad Referenca argument, je vrednost negativna. Če je spodaj, je argument Rows pozitiven. Če je odmik v isti vrstici, je argument nič. V tem primeru offset začne eno vrstico nad referenčnim argumentom, zato je vrednost za ta argument negativna (-1).
• Cols: Potrebno je število stolpcev levo ali desno od referenčnega argumenta, uporabljenega pri izračunu kompenzacije. 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 celicoB7 in dodaja celicoB6 do območja, 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 naFormule jeziček v meniju traku.
3. IzberiteMath & Trigod traku odprete spustni seznam funkcije.
4. Kliknite naSUM na seznamu, da odpre pogovorno okno funkcije.
5. V pogovornem oknu kliknite naŠtevilka 1line.
6. Kliknite na 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Številka2line.
8. Vnesite naslednjo funkcijo OFFSET: OFFSET (B6, -1,0) za oblikovanje dinamične končne točke za formulo.
9. Kliknite v redu da dokončate funkcijo in zaprete pogovorno okno.

Skupaj se pojavi v celici B7.

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 naVstavi vstaviti novo vrstico v delovni list.
3. Zato se formula SUM OFFSET premakne v celico B7, vrstica 6 pa je prazna.
4. Kliknite na celico A6.
5. Vnesite številko5 da se navede, da se prodaja peti dan vnese.
6. Kliknite na celico B6.
7. Vnesite številko$1458.25 in pritisniteVnesite tipko na tipkovnici.

Celica B7 posodobi novo skupno število$7137.40.

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

Funkcija OFFSET ima dva izbirna argumenta: Višina in Širina , ki v tem primeru niso bili uporabljeni.

Te argumente lahko uporabite, da funkciji OFFSET poveste obliko proizvodnje v smislu toliko vrstic, ki so visoke 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.