Nëse fleta juaj e punës në Excel përfshin llogaritjet që bazohen në një varg qelizash në ndryshim, përdorni funksionet SUM dhe OFFSET së bashku në një formulë SUM OFFSET për të thjeshtuar detyrën e mbajtjes së llogaritjeve të përditësuara.
Udhëzimet në këtë artikull zbatohen për Excel për Microsoft 365, Excel 2019, Excel 2016, Excel 2013 dhe Excel 2010.
Krijoni një gamë dinamike me funksionet SUM dhe OFFSET
Nëse përdorni llogaritjet për një periudhë kohore që ndryshon vazhdimisht - si p.sh. përcaktimi i shitjeve për muajin - përdorni funksionin OFFSET në Excel për të vendosur një interval dinamik që ndryshon kur shtohen shifrat e shitjeve të çdo dite.
Në vetvete, funksioni SUM zakonisht mund të akomodojë futjen e qelizave të reja të të dhënave në diapazonin që përmblidhet. Një përjashtim ndodh kur të dhënat futen në qelizën ku ndodhet aktualisht funksioni.
Në shembullin më poshtë, shifrat e reja të shitjeve për çdo ditë shtohen në fund të listës, duke e detyruar totalin të zhvendoset vazhdimisht nga një qelizë poshtë çdo herë kur shtohen të dhënat e reja.
Për të ndjekur së bashku me këtë tutorial, hapni një fletë pune të zbrazët të Excel dhe futni të dhënat e mostrës. Fleta juaj e punës nuk ka nevojë të formatohet si shembulli, por sigurohuni që t'i vendosni të dhënat në të njëjtat qeliza.
Nëse përdoret vetëm funksioni SUM për të mbledhur të dhënat, diapazoni i qelizave të përdorura si argumenti i funksionit do të duhet të modifikohet sa herë që shtohen të dhëna të reja.
Duke përdorur funksionet SUM dhe OFFSET së bashku, diapazoni i përmbledhur bëhet dinamik dhe ndryshon për të akomoduar qelizat e reja të të dhënave. Shtimi i qelizave të reja të të dhënave nuk shkakton probleme sepse diapazoni vazhdon të rregullohet ndërsa çdo qelizë e re shtohet.
Sintaksa dhe Argumentet
Në këtë formulë, funksioni SUM përdoret për të mbledhur gamën e të dhënave të ofruara si argument. Pika e fillimit për këtë interval është statike dhe identifikohet si referenca e qelizës për numrin e parë që do të mblidhet me formulë.
Funksioni OFFSET është i vendosur brenda funksionit SUM dhe krijon një pikë fundore dinamike për gamën e të dhënave të mbledhura nga formula. Kjo arrihet duke vendosur pikën fundore të gamës në një qelizë mbi vendndodhjen e formulës.
Sintaksa e formulës është:
=SUM(Fillimi i diapazonit:OFFSET(referenca, rreshtat, kolonat))
Argumentet janë:
- Range Start: Pika e fillimit për gamën e qelizave që do të mblidhen nga funksioni SUM. Në këtë shembull, pika fillestare është qeliza B2.
- Referenca: Referenca e kërkuar e qelizës e përdorur për të llogaritur pikën përfundimtare të diapazonit. Në shembull, argumenti Referenca është referenca e qelizës për formulën sepse diapazoni përfundon një qelizë mbi formulën.
- Rreshtat: Kërkohet numri i rreshtave mbi ose poshtë argumentit të referencës që përdoret në llogaritjen e kompensimit. Kjo vlerë mund të jetë pozitive, negative ose e vendosur në zero. Nëse vendndodhja e kompensimit është mbi argumentin e referencës, vlera është negative. Nëse kompensimi është më poshtë, argumenti Rreshtat është pozitiv. Nëse kompensimi ndodhet në të njëjtin rresht, argumenti është zero. Në këtë shembull, kompensimi fillon një rresht mbi argumentin e referencës, kështu që vlera për argumentin është negative një (-1).
- Cols: Numri i kolonave në të majtë ose në të djathtë të argumentit Referencë të përdorur për të llogaritur kompensimin. Kjo vlerë mund të jetë pozitive, negative ose e vendosur në zero. Nëse vendndodhja e kompensimit është në të majtë të argumentit Referencë, kjo vlerë është negative. Nëse zhvendosja është djathtas, argumenti Cols është pozitiv. Në këtë shembull, të dhënat që mblidhen janë në të njëjtën kolonë me formulën, kështu që vlera për këtë argument është zero.
Përdor formulën SUM OFFSET për të dhënat totale të shitjeve
Ky shembull përdor një formulë SUM OFFSET për të kthyer totalin për shifrat e shitjeve ditore të renditura në kolonën B të fletës së punës. Fillimisht, formula u fut në qelizën B6 dhe arriti në total të dhënat e shitjeve për katër ditë.
Hapi tjetër është të zhvendosni formulën SUM OFFSET një rresht poshtë për të krijuar vend për totalin e shitjeve të ditës së pestë. Kjo arrihet duke futur një rresht të ri 6, i cili e zhvendos formulën në rreshtin 7.
Si rezultat i lëvizjes, Excel përditëson automatikisht argumentin e referencës në qelizën B7 dhe shton qelizën B6 në diapazonin e përmbledhur nga formula.
- Zgjidh qelizën B6, që është vendi ku do të shfaqen fillimisht rezultatet e formulës.
-
Zgjidhni skedën Formulat të shiritit.
Image -
Zgjidh Math & Trig.
Image -
Zgjidh SUM.
Image - Në kutinë e dialogut Function Arguments, vendoseni kursorin në kutinë e tekstit Number1.
-
Në fletën e punës, zgjidhni qelizën B2 për të futur këtë referencë qelize në kutinë e dialogut. Ky vend është pika përfundimtare statike për formulën.
Image - Në kutinë e dialogut Function Arguments, vendoseni kursorin në kutinë e tekstit Number2.
-
Fut OFFSET(B6, -1, 0). Ky funksion OFFSET formon pikën përfundimtare dinamike për formulën.
Image -
Zgjidhni OK për të përfunduar funksionin dhe mbyllni kutinë e dialogut. Totali shfaqet në qelizën B6.
Image
Shto të dhënat e shitjeve të ditës tjetër
Për të shtuar të dhënat e shitjeve të ditës tjetër:
- Klikoni me të djathtën mbi kokën e rreshtit për rreshtin 6.
-
Zgjidhni Insert për të futur një rresht të ri në fletën e punës. Formula SUM OFFSET lëviz një rresht poshtë në qelizën B7 dhe rreshti 6 tani është bosh.
Image - Zgjidh qelizën A6 dhe fut numrin 5 për të treguar se totali i shitjeve për ditën e pestë po futet.
-
Zgjidh qelizën B6, fut $1458.25, më pas shtyp Enter.
Image - Përditësime të qelizave B7 në totalin e ri prej 7137,40 $.
Kur zgjidhni qelizën B7, formula e përditësuar shfaqet në shiritin e formulave.
=SUM(B2:OFFSET(B7, -1, 0))
Funksioni OFFSET ka dy argumente opsionale: Lartësia dhe Gjerësia, të cilat nuk u përdorën në këtë shembull. Këto argumente i tregojnë funksionit OFFSET formën e daljes në lidhje me numrin e rreshtave dhe kolonave.
Duke hequr këto argumente, funksioni përdor lartësinë dhe gjerësinë e argumentit të referencës, i cili, në këtë shembull është një rresht i lartë dhe një kolonë i gjerë.
