Excel SUM și formula OFFSET

Dacă foaia dvs. de lucru Excel include calcule bazate pe o gamă de celule în schimbare, folosirea împreună a funcțiilor SUM și OFFSET într-o formulă SUM OFFSET simplifică sarcina de a menține calculele actualizate.

Creați o gamă dinamică cu funcțiile SUM și OFFSET

Dacă utilizați calculele pentru o perioadă de timp care se modifică continuu - cum ar fi vânzările totale pentru luna - funcția OFFSET din Excel vă permite să configurați un interval dinamic care se schimbă odată cu adăugarea cifrelor de vânzări ale fiecărei zile.

Funcția SUM poate acomoda de obicei introducerea de noi celule de date în intervalul care este însumat.

Există o excepție atunci când datele sunt introduse în celula în care este localizată funcția.

În exemplul imaginii de mai sus, noile cifre de vânzări pentru fiecare zi sunt adăugate în partea de jos a listei, forțând totalul să treacă continuu în jos câte o celulă de fiecare dată când se adaugă noile date.

Dacă funcția SUM a fost utilizată ca atare pentru a completa datele, ar fi necesar să se modifice intervalul de celule utilizate ca argument al funcției de fiecare dată când s-au adăugat date noi.

Prin utilizarea împreună a funcțiilor SUM și OFFSET, totuși, intervalul total este dinamic. Cu alte cuvinte, aceasta se modifică pentru a se potrivi cu noi celule de date. Adăugarea de noi celule de date nu provoacă probleme deoarece intervalul continuă să se adapteze la adăugarea fiecărei celule noi.

Sintaxă și argumente

Consultați imaginea de mai sus pentru a urmări împreună cu acest tutorial.

În această formulă, funcția SUM este utilizată pentru a completa intervalul de date furnizate ca argument. Punctul de pornire pentru acest interval este static și este identificat ca referință celulară la primul număr care urmează să fie completat de formula.

Funcția OFFSET este imbricată în interiorul funcției SUM și este utilizată pentru a crea un punct final dinamic pentru domeniul de date completat de formula. Aceasta se realizează prin setarea punctului final al intervalului la o celulă deasupra locației formulei.

Sintaxa formulării:

= SUM (interval de pornire: OFFSET (referință, rânduri, coloane))

• Domeniul de pornire: Este necesar punctul de plecare pentru intervalul de celule care vor fi completate de funcția SUM. În exemplul imaginii, aceasta este celula B2.
• Referinţă: Referința celulară necesară pentru calcularea punctului final al gamei este localizată în mai multe rânduri și coloane. În exemplul imaginii, argumentul de referință este referința celulară pentru formula însăși, deoarece doriți întotdeauna ca intervalul să se încheie cu o celulă deasupra formulei.
• rânduri: Numărul de rânduri deasupra sau sub argumentul de referință utilizat pentru calcularea offsetului este necesar. Această valoare poate fi pozitivă, negativă sau setată la zero. Dacă locația offsetului este mai mare de Referinţă argument, valoarea este negativă. Dacă este mai jos, argumentul Rows este pozitiv. Dacă offsetul este localizat în același rând, argumentul este zero. În acest exemplu, offsetul începe cu un rând deasupra argumentului Referință, deci valoarea pentru acest argument este negativă (-1).
• cols: Este necesar numărul de coloane din stânga sau din dreapta argumentului de referință utilizat la calcularea offsetului. Această valoare poate fi pozitivă, negativă sau setată la zero. Dacă locația offsetului este la stânga argumentului de referință, această valoare este negativă. Dacă în dreapta, argumentul Cols este pozitiv. În acest exemplu, datele care se totalizează sunt în aceeași coloană cu formula, deci valoarea pentru acest argument este zero.

Folosirea formulei SUM OFFSET la totalul datelor de vânzări

Acest exemplu folosește o formulă SUM OFFSET pentru a returna totalul cifrelor de vânzări zilnice listate în coloana B a foii de lucru.

Inițial, formula a fost introdusă în celula B6 și a totalizat datele de vânzări timp de patru zile.

Următorul pas este să mutați formula SUM OFFSET în jos pentru a face loc celui de-al cincilea an de vânzări total. Aceasta se realizează prin introducerea unui rând nou 6, care mută formula până la rândul 7.

Ca rezultat al mutare, Excel actualizează automat argumentul referință la celulăB7 și adaugă celulaB6 la intervalul însumat de formula.

Introducerea formulei SUM OFFSET

1. Faceți clic pe celula B6, care este locul unde vor fi afișate inițial rezultatele formulei.
2. Faceți clic peFormulele din meniul panglicii.
3. AlegeMath & Trigdin panglică pentru a deschide lista verticală de funcții.
4. Click peSUMĂ din listă pentru a afișa caseta de dialog a funcției.
5. În caseta de dialog, faceți clic peNumărul 1linia.
6. Faceți clic pe celulă B2 pentru a introduce această referință de celule în caseta de dialog. Această locație reprezintă obiectivul static pentru formula.
7. În caseta de dialog, faceți clic peNumarul 2linia.
8. Introduceți următoarea funcție OFFSET: OFFSET (B6, -1,0) pentru a forma punctul final dinamic pentru formula.
9. Clic O.K pentru a finaliza funcția și a închide caseta de dialog.

Totalul apare în celula B7.

Adăugarea datelor privind vânzările următoarei zile

Pentru a adăuga datele de vânzare a doua zi:

1. Faceți clic dreapta pe antetul rândului pentru rândul 6 pentru a deschide meniul contextual.
2. În meniu, faceți clic peIntroduce pentru a insera un nou rând în foaia de lucru.
3. Ca rezultat, formula SUM OFFSET se mută în jos la celula B7, iar rândul 6 este acum gol.
4. Faceți clic pe celulă A6.
5. Introduceți numărul5 pentru a indica faptul că este introdus totalul vânzărilor pentru a cincea zi.
6. Faceți clic pe celula B6.
7. Introduceți numărul$1458.25 și apăsați tastaintroduce cheie de pe tastatură.

Celula B7 actualizează noul total de$7137.40.

Când faceți clic pe celula B7, formula actualizată= SUM (B2: OFFSET (B7, -1,0)) apare în bara de formule.

Funcția OFFSET are două argumente opționale: Înălțime și Lățime , care nu au fost utilizate în acest exemplu.

Aceste argumente pot fi folosite pentru a indica funcției OFFSET forma de ieșire în termeni atât de multe rânduri înalte și atât de multe coloane largi.

Prin omiterea acestor argumente, funcția, în mod implicit, utilizează în schimb înălțimea și lățimea argumentului de referință, care, în acest exemplu, este un rând înalt și o lățime de o coloană.