Excel SUM ak OFFSET Fòmil

Sèvi ak SUM ak OFFSET pou jwenn total pou chenn dinamik de done

Si fichye Excel ou a gen ladan kalkil ki baze sou yon seri chanjman nan selil yo, lè l sèvi avèk SUM a ak OFFSET fonksyon ansanm nan yon fòmil SUM OFFSET senplifye travay la pou kenbe kalkil yo jiska dat.

Kreye yon Range dinamik Avèk Fonksyon yo SUM ak OFFSET

Si ou sèvi ak kalkil pou yon peryòd tan ki toujou chanje - tankou totalite lavant pou mwa a - fonksyon an OFFSET pèmèt ou mete kanpe yon seri dinamik ki kenbe chanje kòm chak jou nan lavant figi yo te ajoute.

Pou kont li, fonksyon an SUM ka anjeneral akomode nouvo selil nan done ke yo te antre nan seri a ke yo te adisyone.

Yon eksepsyon fèt lè done yo ap antre nan selil la kote se fonksyon an kounye a.

Nan imaj la egzanp ki akonpaye atik sa a, nouvo lavant figi yo pou chak jou yo ajoute nan pati anba a nan lis la, ki fòs total la toujou chanje yon sèl selil chak fwa done yo nouvo te ajoute.

Si fonksyon SUM la te itilize sou pwòp li yo nan total done yo, li ta nesesè yo modifye seri a nan selil yo itilize kòm agiman fonksyon an chak fwa nouvo done te ajoute.

Lè l sèvi avèk fonksyon yo SUM ak OFFSET ansanm, sepandan, ranje a ki totalize vin dinamik. Nan lòt mo, li chanje pou akomode nouvo cellules de done. Anplis de sa nan nouvo selil nan done pa lakòz pwoblèm paske seri a ap kontinye ajiste kòm chak nouvo selil te ajoute.

Sentaks ak agiman

Gade nan imaj la ki akonpaye atik sa a yo swiv ansanm ak Tutorial sa a.

Nan fòmil sa a, se fonksyon an SUM itilize nan total ranje a nan done apwovizyone kòm agiman li yo. Pwen nan kòmansman pou sa a ranje se estatik ak idantifye kòm referans a selil nan premye nimewo a yo dwe totalize pa fòmil la.

Fonksyon nan OFFSET se enbrike andedan fonksyon an SUM epi yo itilize yo kreye yon final dinamik nan seri done ki totalize pa fòmil la. Sa a se akonpli pa mete fen nan seri a nan yon sèl selil anwo a kote fòmil la.

Sentaks fòmil la:

= SUM (Range Start: OFFSET (Referans, Ranje, Cols))

Range Start - (egzije) pwen an kòmanse pou seri a nan selil ki pral totalize pa fonksyon an SUM. Nan imaj la egzanp, sa a se selil B2.

Referans - (egzije) referans a selil yo itilize pou kalkile endepandan ranje a ki sitiye ranje anpil ak kolòn lwen. Nan imaj la egzanp, agiman an Referans se referans a selil pou fòmil la tèt li depi nou toujou vle ranje a nan fen yon sèl selil pi wo a fòmil la.

Ranje - (egzije) kantite ranje pi wo a oswa anba a agiman an Referans yo itilize nan kalkile konpansasyon an. Sa a valè ka pozitif, negatif, oswa mete nan zewo.

Si kote konpanse a se pi wo a agiman an Referans , valè sa a se negatif. Si li anba a, agiman an ranje pozitif. Si konpansasyon an sitiye nan menm ranje a, agiman sa a se zewo. Nan egzanp sa a, konpansasyon an kòmanse yon sèl ranje pi wo a agiman an Referans , se konsa valè a pou agiman sa a se negatif yon sèl (-1).

Cols - (egzije) kantite kolòn ki sou bò gòch la oswa dwa nan agiman an Referans yo itilize nan kalkile konpanse. Sa a valè ka pozitif, negatif, oswa mete nan zewo

Si kote konpanse a se sou bò goch agiman an Referans , valè sa a negatif. Si sou bò dwat la, agiman an Cols pozitif. Nan egzanp sa a, done yo ke yo totalize se nan kolòn nan menm jan ak fòmil la pou valè a pou agiman sa a se zewo.

Sèvi ak SUM OFFSET Fòmil nan Total Komèsyal Done

Egzanp sa a sèvi ak yon SUM OFFSET fòmil pou retounen total la pou figi lavant chak jou ki nan lis nan kolòn B nan Fèy travay la.

Okòmansman, fòmil la te antre nan selil B6 ak totalize done lavant yo pou kat jou.

Pwochen etap la se pou avanse pou pi SUM OFFSET fòmil la desann yon ranje fè plas pou lavant total senkyèm jou a.

Sa a se akonpli pa mete yon ranje nouvo 6, ki deplase fòmil la desann nan ranje 7.

Kòm yon rezilta nan mouvman an, Excel otomatikman à agiman an Referans nan selil B7 ak ajoute B6 selil nan seri a adisyone nan fòmil la.

K ap antre nan SUM OFFSET Fòmil la

1. Klike sou selil B6, ki se kote kote rezilta fòmil la pral parèt an premye.
2. Klike sou tab la Fòmil nan meni an riban .
3. Chwazi Matematik & Trig soti nan riban an yo louvri fonksyon gout-desann lis la.
4. Klike sou SUM nan lis la pote moute bwat dyalòg fonksyon an.
5. Nan bwat dyalòg la, klike sou liy Number1 la .
6. Klike sou selil B2 pou rantre nan referans selil sa a nan bwat dyalòg la. Kote sa a se pwen final la pou fòmil la;
7. Nan bwat dyalòg la, klike sou liy Number2 la .
8. Mete fonksyon OFFSET sa yo: OFFSET (B6, -1,0) pou fòme pwen final la pou fòmil la.
9. Klike sou OK pou konplete fonksyon an epi fèmen bwat dyalòg la.

Total $ 5679,15 parèt nan selil B7.

Lè ou klike sou selil B3, fonksyon konplè = SUM (B2: OFFSET (B6, -1,0)) parèt nan ba fòmil anwo a Fèy travay la.

Ajoute Done Komèsyal Jou kap vini an

Pou ajoute dat lavant pwochen jou a:

1. Dwa-klike sou header nan ranje pou ranje 6 yo louvri meni an kontèks.
2. Nan meni an, klike sou Insert insert yon ranje nouvo nan Fèy travay la.
3. Kòm yon rezilta, fòmil la SUM OFFSET deplase desann nan selil B7 ak ranje 6 se kounye a vid.
4. Klike sou selil A6 .
5. Antre nimewo 5 la pou montre ke total lavant la pou senkyèm jou a ap antre.
6. Klike sou selil B6.
7. Tape nimewo a $ 1458,25 epi peze kle a Mete sou klavye a.

Telefòn B7 ki dènye a nouvo total $ 7137,40.

Lè ou klike sou selil B7, fòmil la ki ajou = SUM (B2: OFFSET (B7, -1,0)) parèt nan ba fòmil la.

Remak : Fonksyon OFFSET la gen de agiman opsyonèl: Wotè ak Lajè, ki te omisyon nan egzanp sa a.

Sa yo agiman yo ka itilize yo di fonksyon an OFFSET fòm nan pwodiksyon an an tèm de ke yo te ranje anpil ewo ak anpil kolòn lajè.

Pa omisyon sa yo agiman, fonksyon an, pa default, sèvi ak wotè a ak lajè nan agiman an Referans olye, ki, nan egzanp sa a se youn ranje segondè, epi yon sèl kolòn lajè.