Excel SUM ja OFFSET valem

Kasutage SUMi ja OFFSETi, et leida dünaamiliste andmevahemike kogusummasid

Kui teie Exceli tööleht sisaldab arvutusi, mis põhinevad lahtrite muutuvas vahemikus , lihtsustab SUM ja OFFSET funktsioonide kasutamine SUM OFFSETi valemiga ülesannete hulka, et arvutused oleksid ajakohased.

Dünaamilise vahemiku loomine SUMi ja OFFSETi funktsioonidega

© Ted French

Kui kasutate pidevalt muutuva aja jooksul arvutusi - nagu kuu kogutoodang - OFFSET-funktsioon võimaldab teil seadistada dünaamilist vahemikku, mis muutub, kuna iga päeva müügiarve lisatakse.

SUM-funktsioon võib iseenesest kokku võtta uued andmeelemendid, mis sisestatakse vahemikku summeerides.

Üks erand tekib siis, kui andmed sisestatakse raamisse, kus funktsioon asub praegu.

Selles artiklis kaasasolevas näites lisatakse igasse päeva uued müügiartiklid loendi alumises osas, mis sunnib kogu arvu pidevalt ühelt lahtrisse iga kord, kui uusi andmeid lisatakse, nihutama.

Kui SUM-i funktsiooni kasutatakse andmete kogumiseks eraldi, siis oleks vaja muuta funktsiooni argumendina kasutatud rakkude vahemikku iga kord, kui uued andmed lisati.

SUMi ja OFFSET-funktsioonide koos kasutamisel muutub see kogu vahemik dünaamilisemaks. Teisisõnu muutub see uute andmete lahtrite mahutamiseks. Andmete uute rakkude lisamine ei tekita probleeme, kuna vahemik jätkab kohandamist, kui iga uus lahtris on lisatud.

Süntaks ja argumendid

Vaadake käesoleva juhendiga kaasnevat pilti, et järgida seda juhendamist.

Selles valemis kasutatakse SUM-i funktsiooni, et koguda esitatud argumentide hulka. Selle vahemiku alguspunkt on staatiline ja identifitseeritakse kui raku viide esimesele numbrile, mis valemiga kokku tuleb.

OFFSET funktsioon on SUM-i funktsiooni sees pesastatud ja seda kasutatakse dünaamilise lõpp-punkti loomiseks valemiga kokku leitavate andmete ulatuses. See saavutatakse, seadistades vahemiku lõppväärtuse ühele lahtrile valemi asukoha kohal.

Valemi süntaks :

= SUM (kaugus algus: OFFSET (viide, ridad, kolooniad))

Vahemiku algus - (vajaduse korral) SUM-funktsiooniga liitunud lahtrite vahemiku alguspunkt. Näidispildil on see lahtris B2.

Viide - (nõutav) raamistiku viide, mida kasutatakse vahemiku lõpp-punkti arvutamiseks, mis asuvad paljude ridade ja veergude kaugusel. Näidispildil on viide argumendiks valemi enda raku viide, kuna me alati soovime, et vahemik lõpetaks ühe valemi kohal oleva lahtri.

Ridad - - (nõutav) nihke arvutamisel kasutatud võrdlusgaradest kõrgem või madalam rida. See väärtus võib olla positiivne, negatiivne või seatud nulli.

Kui nihke asukoht on Viide argumendi kohal, on see väärtus negatiivne. Kui see on allpool, on Rowide argument positiivne. Kui nihe asub samas reas, on see argument null. Selles näites algab nihke algväärtus argumendi kohal ühe rida, nii et selle argumendi väärtus on negatiivne (-1).

Cols - (nõutav) tasakaalusumma arvutamiseks kasutatud võrdlusgarantii vasakule või paremale arv. See väärtus võib olla positiivne, negatiivne või seatud nulli

Kui nihke asukoht on Viide argumendi vasakul, on see väärtus negatiivne. Kui paremale, Colsi argument on positiivne. Selles näites on kogutud andmed samas veeris koos valemiga, nii et selle argumendi väärtus on null.

SUM OFFSETi valemi kasutamine kogumüügiandmete abil

See näide kasutab SUM OFFSETi valemit, et tagastada töölehe veerus B loetletud igapäevaste müügiartiklite summa.

Esialgu sisestati valem lahtrisse B6 ja koguti müügiandmed neli päeva.

Järgmine samm on viia SUM OFFSETi valem rea alla, et ruumi viiendaks päevaks müüdud kogusumma kohta.

Selle saavutamiseks lisage uus rida 6, mis liigub valemi alla kuni rida 7.

Liikumise tulemusena värskendab Excel automaatselt viidete argumendi lahtrisse B7 ja lisab raku B6 valemi summeeritud vahemikku.

SUM OFFSETi valemi sisestamine

  1. Kliki raku B6-le, kus valemite tulemusi esialgu kuvatakse.
  2. Klõpsake lindi menüü vahekaardil Vormid .
  3. Funktsiooni rippmenüü avamiseks valige lintistest Math & Trig .
  4. Klõpsake loendis SUM , et avada funktsioonide dialoogiboks .
  5. Klõpsake dialoogiboksis real Number1 .
  6. Klõpsake lahtris B2, et sisestada selle lahtri viide dialoogiaknasse. See asukoht on valemi staatiline tulemusnäitaja;
  7. Klõpsake dialoogiboksis real Number2 .
  8. Sisestage järgmine OFFSET funktsioon: OFFSET (B6, -1,0), et moodustada valemi dünaamiline lõpp-punkt.
  9. Klõpsake funktsiooni täitmiseks OK ja sulgege dialoogiboks.

Kokku 5679,15 dollarit ilmub lahtrisse B7.

Kui klõpsate lahtris B3, kuvatakse töölehe kohal olevas valemiribal täielik funktsioon = SUM (B2: OFFSET (B6, -1,0)) .

Järgmise päeva müügiandmete lisamine

Järgmise päeva müügiandmete lisamiseks tehke järgmist.

  1. Kontimenüü avamiseks paremklõpsake rea 6 rida päist .
  2. Klõpsake menüüs nuppu Lisa, et lisada uus rida töölehele.
  3. Selle tulemusena liigub SUM OFFSET valem lahtrisse B7 ja rida 6 on nüüd tühi.
  4. Klõpsake lahter A6 .
  5. Sisestage number 5, mis näitab, et sisestatakse viiendiku päeva müügi kogusumma.
  6. Klõpsake lahtris B6.
  7. Sisestage number 1458,25 dollarit ja vajutage klaviatuuril Enter klahvi.

Lahtris B7 värskendatakse uut summat 7137,40 dollarit.

Kui klõpsate lahtris B7, kuvatakse valemiribal värskendatud valem = SUM (B2: OFFSET (B7, -1,0)) .

Märkus : OFFSET-funktsioonil on kaks valikulist argumenti: kõrgus ja laius, mida selles näites jäeti välja.

Neid argumente saab kasutada selleks, et öelda OFFSET-i funktsioonile väljundi kuju nii, et nii palju ridu oleks kõrge ja nii palju veerge lai.

Nende argumentide ärajätmise tõttu kasutab funktsioon vaikimisi võrdluse argumendi kõrgust ja laiust, mis selles näites on üks rida kõrge ja üks veerg lai.