Jei jūsų „Excel“darbalapyje yra skaičiavimų, pagrįstų besikeičiančiu langelių diapazonu, naudokite SUM ir OFFSET funkcijas kartu SUM OFFSET formulėje, kad supaprastintumėte užduotį nuolat atnaujinti skaičiavimus.
Šiame straipsnyje pateiktos instrukcijos taikomos „Excel“, skirta „Microsoft 365“, „Excel 2019“, „Excel 2016“, „Excel 2013“ir „Excel 2010“.
Sukurkite dinaminį diapazoną naudodami SUM ir OFFSET funkcijas
Jei naudojate nuolat besikeičiančio laikotarpio skaičiavimus, pvz., nustatote mėnesio pardavimą, naudokite „Excel“funkciją OFFSET, kad nustatytumėte dinaminį diapazoną, kuris keičiasi, kai pridedami kiekvienos dienos pardavimo duomenys.
Pati funkcija SUM paprastai gali pritaikyti naujų duomenų langelių įterpimą į sumuojamą diapazoną. Viena išimtis įvyksta, kai duomenys įterpiami į langelį, kuriame šiuo metu yra funkcija.
Toliau pateiktame pavyzdyje nauji kiekvienos dienos pardavimų skaičiai pridedami sąrašo apačioje, todėl bendra suma kiekvieną kartą, kai pridedami nauji duomenys, nuolat keičiasi vienu langeliu žemyn.
Jei norite tęsti šią mokymo programą, atidarykite tuščią „Excel“darbalapį ir įveskite pavyzdinius duomenis. Jūsų darbalapio nereikia formatuoti kaip pavyzdyje, bet būtinai įveskite duomenis į tuos pačius langelius.
Jei duomenims sumuoti naudojama tik funkcija SUM, langelių diapazonas, naudojamas kaip funkcijos argumentas, turėtų būti keičiamas kiekvieną kartą, kai pridedami nauji duomenys.
Kartu naudojant SUM ir OFFSET funkcijas, sumuojamas diapazonas tampa dinamiškas ir keičiasi, kad būtų pritaikytos naujos duomenų ląstelės. Naujų duomenų langelių pridėjimas nesukelia problemų, nes diapazonas ir toliau koreguojamas, kai pridedamas naujas langelis.
Sintaksė ir argumentai
Šioje formulėje funkcija SUM naudojama duomenų, pateiktų kaip argumentas, diapazonui sumuoti. Šio diapazono pradžios taškas yra statinis ir identifikuojamas kaip langelio nuoroda į pirmąjį skaičių, kuris turi būti sumuojamas pagal formulę.
Funkcija OFFSET yra įdėta funkcijos SUM viduje ir sukuria dinaminį galutinį tašką duomenų diapazonui, kurį sumuoja formulė. Tai pasiekiama nustatant diapazono galinį tašką vienu langeliu virš formulės vietos.
Formulės sintaksė yra:
=SUM (diapazono pradžia: OFFSET(nuoroda, eilutės, stulpeliai))
Argumentai yra šie:
- Range Start: langelių diapazono, kurį susumuoja funkcija SUM, pradžios taškas. Šiame pavyzdyje pradžios taškas yra langelis B2.
- Reference: reikalinga langelio nuoroda, naudojama diapazono galutiniam taškui apskaičiuoti. Pavyzdyje argumentas Nuoroda yra formulės langelio nuoroda, nes diapazonas baigiasi vienu langeliu aukščiau formulės.
- Eilutės: būtinas eilučių skaičius virš arba žemiau nuorodos argumento, naudojamo skaičiuojant poslinkį. Ši vertė gali būti teigiama, neigiama arba nustatyta į nulį. Jei poslinkio vieta yra aukščiau nuorodos argumento, reikšmė yra neigiama. Jei poslinkis yra žemiau, eilučių argumentas yra teigiamas. Jei poslinkis yra toje pačioje eilutėje, argumentas yra nulis. Šiame pavyzdyje poslinkis prasideda viena eilute virš argumento Nuorodos, todėl argumento reikšmė yra neigiama viena (-1).
- Cols: stulpelių, esančių kairėje arba dešinėje nuo nuorodos argumento, naudojamo poslinkiui apskaičiuoti, skaičius. Ši vertė gali būti teigiama, neigiama arba nustatyta į nulį. Jei poslinkio vieta yra argumento Reference kairėje, ši reikšmė yra neigiama. Jei poslinkis yra teisingas, Cols argumentas yra teigiamas. Šiame pavyzdyje sumuojami duomenys yra tame pačiame stulpelyje kaip ir formulė, todėl šio argumento reikšmė yra nulis.
Visiems pardavimo duomenims naudokite SUM OFFSET formulę
Šiame pavyzdyje naudojama SUM OFFSET formulė, kad būtų grąžinta bendra dienos pardavimo duomenų suma, nurodyta darbalapio B stulpelyje. Iš pradžių formulė buvo įvesta į langelį B6 ir susumuoti keturių dienų pardavimo duomenys.
Kitas žingsnis – SUM OFFSET formulę perkelti viena eilute žemyn, kad būtų vietos penktos dienos bendram pardavimui. Tai atliekama įterpiant naują 6 eilutę, kuri perkelia formulę į 7 eilutę.
Dėl perkėlimo „Excel“automatiškai atnaujina nuorodos argumentą į langelį B7 ir įtraukia langelį B6 į diapazoną, susumuotą pagal formulę.
- Pasirinkite langelį B6, tai yra vieta, kurioje iš pradžių bus rodomi formulės rezultatai.
-
Pasirinkite juostelės skirtuką Formulės.
Image -
Pasirinkite Math & Trig.
Image -
Pasirinkite SUM.
Image - Dialogo lange Funkcijų argumentai įveskite žymeklį į Number1 teksto laukelį.
-
Darbalapyje pasirinkite langelį B2, kad dialogo lange įvestumėte šią langelio nuorodą. Ši vieta yra statinis formulės galutinis taškas.
Image - Dialogo lange Funkcijos argumentai įveskite žymeklį į Number2 teksto laukelį.
-
Įveskite OFFSET(B6, -1, 0). Ši OFFSET funkcija sudaro formulės dinaminį galinį tašką.
Image -
Pasirinkite OK, kad užbaigtumėte funkciją ir uždarytumėte dialogo langą. Bendra suma rodoma langelyje B6.
Image
Pridėkite kitos dienos pardavimo duomenis
Jei norite pridėti kitos dienos pardavimo duomenis:
- Dešiniuoju pelės mygtuku spustelėkite 6 eilutės antraštę.
-
Pasirinkite Insert, kad į darbalapį įterptumėte naują eilutę. SUM OFFSET formulė perkeliama viena eilute žemyn į langelį B7, o 6 eilutė dabar tuščia.
Image - Pasirinkite langelį A6 ir įveskite skaičių 5, kad parodytumėte, jog įvedamas bendras penktos dienos pardavimas.
-
Pasirinkite langelį B6, įveskite $1458.25, tada paspauskite Enter.
Image - Cell B7 atnaujinimai iki naujos bendros 7137,40 USD vertės.
Kai pasirenkate langelį B7, formulės juostoje pasirodo atnaujinta formulė.
=SUM(B2:OFFSET(B7, -1, 0))
Funkcija OFFSET turi du pasirenkamus argumentus: Aukštis ir Plotis, kurie šiame pavyzdyje nebuvo naudojami. Šie argumentai nurodo funkcijai OFFSET išvesties formą pagal eilučių ir stulpelių skaičių.
Praleidus šiuos argumentus, funkcija naudoja argumento Nuorodos aukštį ir plotį, kuris šiame pavyzdyje yra vienos eilutės aukščio ir vieno stulpelio pločio.
