Kui teie Exceli tööleht sisaldab arvutusi, mis põhinevad muutuval lahtrivahemikul, kasutage SUM ja OFFSET funktsioone koos SUM OFFSET valemis, et lihtsustada arvutuste ajakohasena hoidmist.
Selles artiklis olevad juhised kehtivad Exceli jaoks Microsoft 365, Excel 2019, Excel 2016, Excel 2013 ja Excel 2010 jaoks.
Looge dünaamiline vahemik SUM ja OFFSET funktsioonide abil
Kui kasutate arvutusi pidev alt muutuva ajaperioodi kohta (nt kuu müügimäärade määramine), kasutage Exceli funktsiooni OFFSET, et seadistada dünaamiline vahemik, mis muutub iga päeva müüginumbrite lisamisel.
Iseenesest suudab funktsioon SUM tavaliselt kohandada uute andmelahtrite lisamist summeeritavasse vahemikku. Üks erand esineb siis, kui andmed sisestatakse lahtrisse, kus funktsioon praegu asub.
Altoodud näites lisatakse iga päeva uued müüginumbrid loendi lõppu, sundides kogusummat iga kord, kui uusi andmeid lisatakse, ühe lahtri võrra allapoole nihkuma.
Selle õpetuse järgimiseks avage tühi Exceli tööleht ja sisestage näidisandmed. Teie tööleht ei pea olema vormindatud nagu näites, kuid sisestage andmed kindlasti samadesse lahtritesse.
Kui andmete summeerimiseks kasutatakse ainult funktsiooni SUM, tuleb funktsiooni argumendina kasutatavate lahtrite vahemikku muuta iga kord, kui lisatakse uusi andmeid.
Funktsiooni SUM ja OFFSET koos kasutades muutub summeeritud vahemik dünaamiliseks ja muutub vastav alt uutele andmelahtritele. Uute andmelahtrite lisamine ei tekita probleeme, sest vahemiku kohandamine jätkub iga uue lahtri lisamisel.
Süntaks ja argumendid
Selles valemis kasutatakse SUM-funktsiooni argumendina esitatud andmevahemiku liitmiseks. Selle vahemiku alguspunkt on staatiline ja identifitseeritakse lahtri viitena esimesele arvule, mis liidetakse valemiga.
Funktsioon OFFSET on pesastatud funktsiooni SUM sees ja loob valemiga summeeritud andmevahemikule dünaamilise lõpp-punkti. See saavutatakse, määrates vahemiku lõpp-punktiks ühe lahtri valemi asukohast kõrgemal.
Valemi süntaks on:
=SUM(vahemiku algus:NIHKE(viide, read, veerud))
Argumendid on:
- Range Start: lahtrivahemiku alguspunkt, mille funktsioon SUM summeerib. Selles näites on lähtepunktiks lahter B2.
- Viide: nõutav lahtriviide, mida kasutatakse vahemiku lõpp-punkti arvutamiseks. Näites on argument Reference valemi lahtriviide, kuna vahemik lõpeb ühe lahtri võrra valemist kõrgemal.
- Read: Nõutav on ridade arv, mis on nihke arvutamisel kasutatavast võrdlusargumendist üleval või all. See väärtus võib olla positiivne, negatiivne või null. Kui nihke asukoht on argumendi Reference kohal, on väärtus negatiivne. Kui nihe on allpool, on ridade argument positiivne. Kui nihe asub samal real, on argument null. Selles näites algab nihe ühe rea võrra argumendist Reference kohal, seega on argumendi väärtus negatiivne üks (-1).
- Veerbad: nihke arvutamiseks kasutatud viiteargumendist vasakul või paremal asuvate veergude arv. See väärtus võib olla positiivne, negatiivne või null. Kui nihke asukoht on argumendist Reference vasakul, on see väärtus negatiivne. Kui nihe on õige, on Colsi argument positiivne. Selles näites on summeeritavad andmed valemiga samas veerus, seega on selle argumendi väärtus null.
Kasutage müügiandmete kogumiseks SUM OFFSETi valemit
See näide kasutab SUM OFFSET valemit, et tagastada töölehe veerus B loetletud igapäevaste müüginumbrite summa. Algselt sisestati valem lahtrisse B6 ja see summeeris nelja päeva müügiandmed.
Järgmine samm on nihutada SUM OFFSET valemit ühe rea võrra allapoole, et teha ruumi viienda päeva müügi kogusummale. See saavutatakse uue rea 6 lisamisega, mis viib valemi reale 7.
Liigutamise tulemusena värskendab Excel automaatselt argumendi Viide lahtrisse B7 ja lisab lahtri B6 valemiga summeeritud vahemikku.
- Valige lahter B6, mis on koht, kus valemi tulemused algselt kuvatakse.
-
Valige lindi vahekaart Valemid.
Image -
Valige Math & Trig.
Image -
Valige SUM.
Image - Viige dialoogiboksis Funktsiooniargumendid kursor tekstikasti Number1.
-
Valige töölehel lahter B2, et sisestada see lahtri viide dialoogiboksi. See asukoht on valemi staatiline lõpp-punkt.
Image - Viige dialoogiboksis Funktsiooni argumendid kursor tekstikasti Number2.
-
Sisestage OFFSET(B6, -1, 0). See funktsioon OFFSET moodustab valemi dünaamilise lõpp-punkti.
Image -
Funktsiooni lõpetamiseks ja dialoogiboksi sulgemiseks valige OK. Kogusumma kuvatakse lahtris B6.
Image
Lisa järgmise päeva müügiandmed
Järgmise päeva müügiandmete lisamiseks:
- Paremklõpsake 6. rea päisel.
-
Valige töölehele uue rea lisamiseks Insert. SUM OFFSET valem liigub ühe rea võrra allapoole lahtrisse B7 ja rida 6 on nüüd tühi.
Image -
Valige lahter A6 ja sisestage number 5, mis näitab, et viienda päeva müügi kogusumma sisestatakse.
-
Valige lahter B6, sisestage $1458.25, seejärel vajutage Sisestus.
Image - Cell B7 värskendused uuele kogusummale 7137,40 $.
Kui valite lahtri B7, kuvatakse valemiribale värskendatud valem.
=SUM(B2:NIHETUS(B7, -1, 0))
Funktsioonil OFFSET on kaks valikulist argumenti: Kõrgus ja Laius, mida selles näites ei kasutatud. Need argumendid määravad funktsioonile OFFSET väljundi kuju ridade ja veergude arvu järgi.
Kui need argumendid välja jätta, kasutab funktsioon selle asemel Reference argumendi kõrgust ja laiust, mis antud näites on ühe rea kõrgus ja ühe veeru laius.
