Utilizați SUM și OFFSET pentru a găsi totaluri pentru intervalele dinamice de date
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 vă permite să configurați un interval dinamic care continuă să se schimbe pe măsură ce cifrele de vânzări din fiecare zi sunt adăugate.
Funcția SUM poate, de obicei, să găzduiască noi celule de date care sunt inserate î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 de imagine care însoțește acest articol, noile cifre de vânzări pentru fiecare zi sunt adăugate în partea de jos a listei, ceea ce obligă totalul să treacă continuu în jos 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 care însoțește acest articol ca să urmați î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))Interval de pornire - este necesar punctul de pornire pentru intervalul de celule care vor fi completate de funcția SUM. În exemplul imaginii, aceasta este celula B2.
Referință - (obligatorie) referința celulei utilizată pentru calcularea punctului de referință al gamei, amplasat la mai multe rânduri și coloane. În exemplul imaginii, argumentul de referință este referința celulară pentru formula însăși, deoarece dorim mereu intervalul să se termine o celulă deasupra formulei.
Rânduri - (obligatoriu) numărul de rânduri deasupra sau sub argumentul de referință utilizat la calcularea offsetului. Această valoare poate fi pozitivă, negativă sau setată la zero.
Dacă poziția offsetului este mai mare decât argumentul Referință , această valoare este negativă. Dacă este mai jos, argumentul Rows este pozitiv. Dacă offsetul este localizat în același rând, acest argument este zero. În acest exemplu, offsetul începe cu un rând deasupra argumentului Referință , deci valoarea pentru acest argument este negativă (-1).
Coloane - (obligatoriu) numărul de coloane din stânga sau din dreapta argumentului de referință utilizat pentru 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ă ca și formula, astfel încât 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 urmare a mutare, Excel actualizează automat argumentul de referință la celula B7 și adaugă celula B6 la intervalul însumat de formula.
Introducerea formulei SUM OFFSET
- Faceți clic pe celula B6, care este locul unde vor fi afișate inițial rezultatele formulei.
- Faceți clic pe fila Formule din meniul de panglică .
- Alegeți Math & Trig din panglică pentru a deschide lista derulantă funcțională.
- Faceți clic pe SUM din listă pentru a afișa caseta de dialog a funcției.
- În caseta de dialog, faceți clic pe linia Număr1 .
- Faceți clic pe celula B2 pentru a introduce această referință de celule în caseta de dialog. Această locație reprezintă obiectivul static pentru formula;
- În caseta de dialog, faceți clic pe linia Number2 .
- Introduceți următoarea funcție OFFSET: OFFSET (B6, -1,0) pentru a forma punctul final dinamic pentru formula.
- Faceți clic pe OK pentru a finaliza funcția și a închide caseta de dialog.
Suma totală este de 5679,15 USD apare în celula B7.
Când faceți clic pe celula B3, funcția completă = SUM (B2: OFFSET (B6, -1,0)) apare în bara de formula de deasupra foii de lucru.
Adăugarea datelor privind vânzările următoarei zile
Pentru a adăuga datele de vânzare a doua zi:
- Faceți clic dreapta pe antetul rândului pentru rândul 6 pentru a deschide meniul contextual.
- În meniu, faceți clic pe Inserare pentru a insera un nou rând în foaia de lucru.
- Ca rezultat, formula SUM OFFSET se mută în jos la celula B7, iar rândul 6 este acum gol.
- Faceți clic pe celula A6 .
- Introduceți numărul 5 pentru a indica că a fost introdus totalul vânzărilor pentru a cincea zi.
- Faceți clic pe celula B6.
- Introduceți numărul 1458.25 $ și apăsați tasta Enter de pe tastatură.
Celula B7 actualizează noul total de 7137,40 $.
Când faceți clic pe celula B7, formularul actualizat = SUM (B2: OFFSET (B7, -1,0)) apare în bara de formule.
Notă : Funcția OFFSET are două argumente opționale: Înălțime și Lățime, care au fost omise în acest exemplu.
Aceste argumente pot fi folosite pentru a indica funcției OFFSET forma ieșirii în ceea ce privește faptul că sunt 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ă.