Përdorni SUM dhe OFFSET për të gjetur totals për vargjet dinamike të të dhënave
Nëse fleta juaj e Excel-it përfshin llogaritjet e bazuara në një varg ndryshimesh të qelizave, duke përdorur funksionet SUM dhe OFFSET së bashku në një formulë SUM OFFSET thjeshton detyrën e mbajtjes së llogaritjeve deri tani.
Krijo një gamë dinamike me funksionet SUM dhe OFFSET
Nëse përdorni llogaritje për një periudhë kohore që ndryshon vazhdimisht - si shitjet e përgjithshme për muajin - funksioni OFFSET ju lejon të krijoni një gamë dinamike që vazhdon të ndryshojë pasi që shifrat e shitjeve të çdo dite janë shtuar.
Në vetvete, funksioni SUM zakonisht mund të akomodojë qeliza të reja të të dhënave që futen në vargun që përmblidhet.
Një përjashtim ndodh kur të dhënat futen në qelizë ku funksioni aktualisht gjendet.
Në imazhin e shembullit që shoqëron këtë artikull, shifrat e reja të shitjeve për çdo ditë shtohen në fund të listës, e cila detyron totalin të zhvendos vazhdimisht poshtë një qelize çdo herë që të shtohen të dhënat e reja.
Nëse funksioni SUM është përdorur vetvetiu për të mbledhur të dhënat, do të ishte e nevojshme të modifikohet sfera e qelizave të përdorura si argumenti i funksionit çdo herë që të shtohen të dhëna të reja.
Duke përdorur funksionin SUM dhe OFFSET së bashku, megjithatë, vargu që arrin në total bëhet dinamik. Me fjalë të tjera, ai ndryshon për të akomoduar qeliza të reja të të dhënave. Shtimi i qelizave të reja të të dhënave nuk shkakton probleme, sepse vargu vazhdon të përshtatet kur çdo qelizë e re shtohet.
Sintaksa dhe Argumentet
Referojuni imazhit që shoqëron këtë artikull për të ndjekur së bashku me këtë tutorial.
Në këtë formulë, funksioni SUM përdoret për të plotësuar gamën e të dhënave të dhëna si argument i saj. Pika e fillimit për këtë varg është statike dhe identifikohet si referencë e qelizës në numrin e parë që do të arrijë në formulën.
Funksioni OFFSET është i mbivendosur brenda funksionit SUM dhe përdoret për të krijuar një pikë përfundimtare dinamike në gamën e të dhënave të totalizuara nga formula. Kjo arrihet duke vendosur fundin e intervalit të një qelize mbi vendndodhjen e formulës.
Sintaksa e formulës:
= SUM (Start Range: OFFSET (Referenca, Rreshtat, Kolonat))Start Range - (kërkohet) pikën e fillimit për gamën e qelizave që do të arrijnë në totalin e funksionit SUM. Në imazhin e shembullit, kjo është qeliza B2.
Referenca - (e nevojshme) referenca e qelizës e përdorur për llogaritjen e pikave përfundimtare të vargut të vendosura shumë rreshta dhe kolona larg. Në imazhin e shembullit, argumenti i referencës është referenca e qelizës për formulën në vetvete pasi ne gjithmonë duam që diapazoni të përfundojë një qelizë mbi formulën.
Rreshtat - (kërkohet) numri i rreshtave mbi ose poshtë argumentit të referencës të përdorur në llogaritjen e kompensimit. Kjo vlerë mund të jetë pozitive, negative ose të cilësohet si zero.
Nëse pozicioni i kompensimit është mbi argumentin e referencës , kjo vlerë është negative. Nëse është më poshtë, argumenti i rreshtave është pozitiv. Nëse kompensimi ndodhet në të njëjtën rresht, ky argument është zero. Në këtë shembull, kompensimi fillon një rresht mbi argumentin e referencës , kështu që vlera për këtë argument është negative (-1).
Cols - (kërkohet) numri i kolonave në të majtë ose të djathtë të argumentit të Referencës të përdorur për llogaritjen e kompensimit. Kjo vlerë mund të jetë pozitive, negative ose të cilësohet si zero
Nëse pozicioni i kompensimit është në të majtë të argumentit të referencës , kjo vlerë është negative. Nëse në të djathtë, argumenti Cols është pozitiv. Në këtë shembull, të dhënat që arrihen janë në të njëjtën kolonë si formula kështu që vlera për këtë argument është zero.
Përdorimi i formulës 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ë shënuara në kolonën B të fletës së punës.
Fillimisht, formula u hodh në qelizën B6 dhe arriti të dhënat e shitjeve për katër ditë.
Hapi i ardhshëm është që të lëvizni formulën SUM OFFSET poshtë një rresht për të bërë hapësirë për shitjet e ditës së pestë.
Kjo arrihet duke futur një rresht të ri 6, i cili e çon formulën poshtë në rreshtin 7.
Si rezultat i kësaj lëvizje, Excel automatikisht rifreskon argumentin e referencës tek qeliza B7 dhe shton qelizën B6 në rangun e përmbledhur nga formula.
Hyrja e formulës SUM OFFSET
- Klikoni në qelizën B6, e cila është vendi ku do të shfaqen fillimisht rezultatet e formulës.
- Klikoni në tab Formulat e menysë së fjongo .
- Zgjidhni Math & Trig nga shiriti për të hapur drop-down listën e funksionit.
- Klikoni në SUM në listën për të shfaqur kutinë e dialogut të funksionit.
- Në kutinë e dialogut, klikoni në vijën Numër1 .
- Klikoni në qelizën B2 për të hyrë në këtë referencë të qelizës në kutinë e dialogut. Ky vend është pika përfundimtare statike për formulën;
- Në kutinë e dialogut, klikoni në vijën Numër2 .
- Fut funksionin OFFSET vijues: OFFSET (B6, -1,0) për të formuar pikën dinamike të formulës.
- Kliko OK për të përfunduar funksionin dhe mbyllni kutinë e dialogut.
Totali i $ 5679.15 shfaqet në qelizën B7.
Kur klikoni në qelizën B3, funksioni i plotë = SUM (B2: OFFSET (B6, -1,0)) shfaqet në shiritin e formulës mbi fletën e punës.
Shtimi i të Dhënave të Shitjes së Ditës së ardhshme
Për të shtuar të dhënat e shitjeve të ditës së ardhshme:
- Klikoni me të djathtën në krye të rreshtit për rreshtin 6 për të hapur menunë e kontekstit.
- Në menynë, klikoni në Fut për të futur një rresht të ri në fletën e punës.
- Si rezultat, formula SUM OFFSET shkon poshtë në qelizën B7 dhe rreshti 6 është bosh.
- Klikoni në qelizën A6 .
- Futni numrin 5 për të treguar se shuma e shitjeve për ditën e pestë është duke hyrë.
- Klikoni në qelizën B6.
- Shkruani numrin $ 1458.25 dhe shtypni butonin Enter në tastierë.
Cell B7 përditëson totalin e ri prej 7137.40 $.
Kur klikoni në qelizën B7, formulari i përditësuar = SUM (B2: OFFSET (B7, -1,0)) shfaqet në shiritin e formulës.
Shënim : Funksioni OFFSET ka dy argumente opsionale: Lartësia dhe Gjerësia, të cilat janë lënë jashtë në këtë shembull.
Këto argumente mund të përdoren për të treguar funksionin OFFSET formën e prodhimit në drejtim të të qenit kaq shumë rreshta të larta dhe kaq shumë kolona të gjerë.
Duke lënë anash këto argumente, funksioni, sipas parazgjedhjes, 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ë.