Dacă foaia de lucru Excel include calcule care se bazează pe un interval de celule în schimbare, utilizați împreună funcțiile SUM și OFFSET într-o formulă SUM OFFSET pentru a simplifica sarcina de a menține calculele la zi.
Instrucțiunile din acest articol se aplică Excel pentru Microsoft 365, Excel 2019, Excel 2016, Excel 2013 și Excel 2010.
Creați un interval dinamic cu funcțiile SUM și OFFSET
Dacă utilizați calcule pentru o perioadă de timp care se modifică continuu - cum ar fi determinarea vânzărilor pentru luna - utilizați funcția OFFSET din Excel pentru a configura un interval dinamic care se modifică pe măsură ce se adaugă cifrele de vânzări din fiecare zi.
De la sine, funcția SUM poate găzdui de obicei inserarea de noi celule de date în intervalul însumat. O excepție apare atunci când datele sunt inserate în celula în care se află funcția în prezent.
În exemplul de mai jos, noile cifre de vânzări pentru fiecare zi sunt adăugate în partea de jos a listei, forțând ca totalul să se deplaseze continuu în jos cu o celulă de fiecare dată când sunt adăugate noile date.
Pentru a urma acest tutorial, deschideți o foaie de lucru Excel goală și introduceți datele eșantionului. Foaia de lucru nu trebuie să fie formatată ca în exemplu, dar asigurați-vă că introduceți datele în aceleași celule.
Dacă se folosește numai funcția SUM pentru a totaliza datele, intervalul de celule folosit ca argument al funcției ar trebui modificat de fiecare dată când se adaugă date noi.
Folosind împreună funcțiile SUM și OFFSET, intervalul totalizat devine dinamic și se modifică pentru a se adapta la noi celule de date. Adăugarea de noi celule de date nu cauzează probleme, deoarece intervalul continuă să se ajusteze pe măsură ce se adaugă fiecare celulă nouă.
Sintaxă și argumente
În această formulă, funcția SUM este utilizată pentru a totaliza intervalul de date furnizate ca argument. Punctul de pornire pentru acest interval este static și este identificat ca referință de celulă la primul număr care trebuie totalizat prin formulă.
Funcția OFFSET este imbricată în interiorul funcției SUM și creează un punct final dinamic pentru intervalul de date totalizat prin formulă. Acest lucru se realizează prin setarea punctului final al intervalului la o celulă deasupra locației formulei.
Sintaxa formulei este:
=SUM(Început interval:OFFSET(Referință, Rânduri, Coloane))
Argumentele sunt:
- Range Start: Punctul de pornire pentru intervalul de celule care va fi totalizat de funcția SUM. În acest exemplu, punctul de pornire este celula B2.
- Reference: referința necesară pentru celulă utilizată pentru a calcula punctul final al intervalului. În exemplu, argumentul Referință este referința de celulă pentru formulă, deoarece intervalul se termină cu o celulă deasupra formulei.
- Rânduri: este necesar numărul de rânduri de deasupra sau dedesubtul argumentului Referință utilizat la calcularea compensației. Această valoare poate fi pozitivă, negativă sau setată la zero. Dacă locația offset este deasupra argumentului Reference, valoarea este negativă. Dacă offset-ul este mai mic, argumentul Rânduri este pozitiv. Dacă offset-ul este situat pe același rând, argumentul este zero. În acest exemplu, decalajul începe cu un rând deasupra argumentului Referință, astfel încât valoarea argumentului este negativă (-1).
- Cols: numărul de coloane din stânga sau din dreapta argumentului Referință utilizat pentru a calcula compensarea. Această valoare poate fi pozitivă, negativă sau setată la zero. Dacă locația offset este la stânga argumentului Reference, această valoare este negativă. Dacă offset-ul este la dreapta, argumentul Cols este pozitiv. În acest exemplu, datele însumate sunt în aceeași coloană cu formula, așa că valoarea acestui argument este zero.
Utilizați formula SUM OFFSET pentru datele totale de vânzări
Acest exemplu folosește o formulă SUM OFFSET pentru a returna totalul pentru cifrele zilnice de vânzări enumerate în coloana B a foii de lucru. Inițial, formula a fost introdusă în celula B6 și a totalizat datele vânzărilor timp de patru zile.
Următorul pas este să mutați formula SUM OFFSET în jos cu un rând pentru a face loc pentru totalul vânzărilor din a cincea zi. Acest lucru se realizează prin inserarea unui nou rând 6, care mută formula pe rândul 7.
Ca urmare a mutarii, Excel actualizează automat argumentul Referință la celula B7 și adaugă celula B6 la intervalul însumat prin formulă.
- Selectați celula B6, care este locația în care vor fi afișate inițial rezultatele formulei.
-
Selectați fila Formule din panglică.
Image -
Alege Math & Trig.
Image -
Selectați SUMA.
Image - În caseta de dialog Argumente ale funcției, plasați cursorul în caseta de text Number1.
-
În foaia de lucru, selectați celula B2 pentru a introduce această referință de celulă în caseta de dialog. Această locație este punctul final static al formulei.
Image - În caseta de dialog Argumente ale funcției, plasați cursorul în caseta de text Number2.
-
Introduceți OFFSET(B6, -1, 0). Această funcție OFFSET formează punctul final dinamic al formulei.
Image -
Selectați OK pentru a finaliza funcția și a închide caseta de dialog. Totalul apare în celula B6.
Image
Adăugați datele de vânzări pentru ziua următoare
Pentru a adăuga datele vânzărilor de a doua zi:
- Clic dreapta pe antetul rândului pentru rândul 6.
-
Selectați Inserați pentru a insera un nou rând în foaia de lucru. Formula SUM OFFSET se deplasează în jos cu un rând la celula B7, iar rândul 6 este acum gol.
Image - Selectați celula A6 și introduceți numărul 5 pentru a indica faptul că se introduce totalul vânzărilor pentru a cincea zi.
-
Selectați celula B6, introduceți $1458.25, apoi apăsați Enter.
Image - Celula B7 se actualizează la noul total de 7137,40 USD.
Când selectați celula B7, formula actualizată apare în bara de formule.
=SUMA(B2:OFFSET(B7, -1, 0))
Funcția OFFSET are două argumente opționale: Height și Width, care nu au fost folosite în acest exemplu. Aceste argumente indică funcției OFFSET forma rezultatului în ceea ce privește numărul de rânduri și coloane.
Omitând aceste argumente, funcția folosește în schimb înălțimea și lățimea argumentului Referință, care, în acest exemplu, are un rând înălțime și o lățime de coloană.
