Naudokite SUM ir OFFSET, kad galėtumėte rasti dinaminių duomenų diapazonų sumas
Jei jūsų "Excel" darbalapyje yra skaičiavimai, pagrįsti kintama ląstelių diapazone , naudojant SUM ir OFFSET funkcijas kartu SUM OFFSET formulėje supaprastinama užduotis išlaikyti skaičiavimus naujausią.
Sukurkite dinaminį diapazoną su SUM ir OFFSET funkcijomis
Jei naudojate skaičiavimus tam tikrą laikotarpį, kuris nuolat keičia, pvz., Mėnesio pardavimo apimtys, OFFSET funkcija leidžia jums nustatyti dinaminį diapazoną, kuris nuolat keičiasi, nes kiekvieną dieną pateikiami pardavimo duomenys.
Savo ruožtu funkcija SUM dažniausiai gali priderinti naujas duomenų ląsteles, įterptas į apibendrinamą diapazoną.
Viena išimtis įvyksta, kai duomenys įterpiami į ląstelę, kurioje šiuo metu yra funkcija.
Pavyzdiniame šiame straipsnyje pateikiamame paveikslėlyje nauji kiekvienos dienos pardavimų duomenys pridedami prie sąrašo apačios, todėl bendras skaičius kiekvieną kartą pridedant naujus duomenis nuolat kinta.
Jei SUM funkcija būtų naudojama atskirai, kad būtų bendri duomenys, reikės keisti ląstelių asortimentą, naudojamą kaip funkcijos argumentas kiekvieną kartą, kai buvo pridėta naujų duomenų.
Vis dėlto naudodamiesi SUM ir OFFSET funkcijomis bendras diapazonas tampa dinamiškas. Kitaip tariant, jis pasikeičia, kad atitiktų naujas duomenų ląsteles. Naujų duomenų ląstelių pridėjimas nesukelia problemų, nes diapazonas toliau derinamas, nes kiekviena nauja ląstelė yra pridėta.
Sintaksė ir argumentai
Žiūrėkite paveikslėlį, kuris pridedamas prie šio straipsnio, kad galėtumėte sekti kartu su šia mokymo programa.
Šioje formulėje naudojama SUM funkcija, kad būtų galima palyginti pateiktų duomenų asortimentą. Šio diapazono pradinis taškas yra statinis ir identifikuojamas kaip ląstelių nuoroda į pirmąjį skaičių, kuris turi būti skaičiuojamas pagal formulę.
OFFSET funkcija yra įdėta į SUM funkcijos viduje ir naudojama dinamiškam galutiniam taškui kurti pagal duomenų formulę. Tai pasiekiama nustatant diapazono galutinę reikšmę į vieną langelį virš formulės vietos.
Formulės sintaksė :
= SUM (diapazono pradžia: OFFSET (nuoroda, eilutės, kolonos))Diapazonas Pradėti - (reikalingas) ląstelių asortimento pradžios taškas, kuris bus skaičiuojamas pagal SUM funkciją. Pavyzdiniame paveikslėlyje tai yra langelis B2.
Nuoroda - (reikalinga) ląstelių nuoroda, naudojama diapazono galutinei vertei apskaičiuoti, šalia daugelio eilučių ir stulpelių. Pavyzdiniame paveikslėlyje " Reference" argumentas yra pati formulės langelio nuoroda, nes visada norime, kad diapazonas baigtų vieną langelį virš formulės.
Eilutės - (reikalaujama) eilučių skaičius virš arba žemiau referencinio argumento, naudojamo apskaičiuojant poslinkį. Ši vertė gali būti teigiama, neigiama arba nustatyta nuline.
Jei nuokrypio vieta viršija " Reference" argumentą, ši reikšmė yra neigiama. Jei žemiau, eilučių argumentas yra teigiamas. Jei nuokrypis yra toje pačioje eilutėje, šis argumentas yra lygus nuliui. Šiame pavyzdyje nuokrypis prasideda viena eilute virš nuorodinio argumento, todėl šio argumento vertė yra neigiama (-1).
Cols - (reikalingas) stulpelių skaičius į kairę arba į dešinę nuo pamatinio argumento, naudojamo apskaičiuojant poslinkį. Ši vertė gali būti teigiama, neigiama arba nustatyta nuline
Jei nuokrypio vieta yra " Reference" argumento kairėje, ši reikšmė yra neigiama. Jei į dešinę, kolonų argumentas yra teigiamas. Šiame pavyzdyje visi duomenys yra toje pačioje skiltyje kaip ir formulė, todėl šio argumento vertė yra lygi nuliui.
SUM OFFSET formulės naudojimas bendriems pardavimo duomenims
Šis pavyzdys naudoja SUM OFFSET formulę, kad grąžintų bendrą skaičiuotę kasdienių pardavimų skaičiavimams, pateiktiems darbalapio B stulpelyje.
Iš pradžių formulė buvo įtraukta į ląstelę B6 ir pardavimų duomenys buvo pateikti keturias dienas.
Kitas žingsnis - perkelti SUM OFFSET formulę į eilę, kad būtų galima užimti penktos dienos pardavimų apimtį.
Tai atliekama įterpiant naują 6 eilutę , kuri perkelia formulę į 7 eilutę.
Perėjimo metu "Excel" automatiškai atnaujina "Reference" argumentą į langelį B7 ir įterpia langelį B6 į diapazoną, suminį pagal formulę.
Įvesdami SUM OFFSET formulę
- Spustelėkite ląstelę B6, kuri yra vieta, kurioje iš pradžių bus rodomi formulės rezultatai.
- Spustelėkite skirtuką Formulės juostelės meniu.
- Pasirinkite " Math & Trig" iš juostos, kad atidarytumėte išskleidžiamąjį sąrašo funkciją.
- Spustelėkite SUM sąrašą, kad pamatytumėte funkcijos dialogo langą .
- Dialogo lange spustelėkite eilutės numerį1 .
- Spustelėkite langelį B2, kad įvedėte šią langelio nuorodą į dialogo langą. Ši vieta yra pastovi formulė;
- Dialogo lange spustelėkite eilutę Number2 .
- Įveskite šią OFFSET funkciją: OFFSET (B6, -1,0), kad sudarytumėte dinaminę galutinę formulę.
- Spustelėkite Gerai, kad užbaigtumėte funkciją ir uždarytumėte dialogo langą.
Iš viso $ 5679,15 pasirodo langelyje B7.
Kai paspausite langelį B3, formulės juostoje virš darbalapio pasirodys visa funkcija = SUM (B2: OFFSET (B6, -1,0)) .
Kitų dienų pardavimo duomenų pridėjimas
Norėdami pridėti kitų dienų pardavimo duomenis:
- Dešiniuoju pelės klavišu spustelėkite eilutės 6 antraštę , kad atidarytumėte kontekstinį meniu.
- Meniu spustelėkite Insert, jei norite įterpti naują eilutę į darbalapį.
- Todėl SUM OFFSET formulė juda žemyn į langelį B7, o eilutė 6 yra tuščia.
- Spustelėkite langelį A6 .
- Įveskite 5 numerį, kad būtų nurodyta, kad įvedama penktos dienos pardavimų suma.
- Spustelėkite langelį B6.
- Įveskite numerį $ 1458,25 ir paspauskite Enter klavišą.
B7 korpusas atnaujina naują sumą $ 7137,40.
Kai spustelėsite langelį B7, formulės juostoje pasirodys atnaujinta formulė = SUM (B2: OFFSET (B7, -1,0)) .
Pastaba : OFFSET funkcijai yra du neprivalomi argumentai: aukštis ir plotis, kurie šiame pavyzdyje buvo praleisti.
Šie argumentai gali būti naudojami norint, kad OFSET funkcija būtų išvesties forma, atsižvelgiant į tai, kad tiek daug eilučių yra didelės ir tiek daug stulpelių pločio.
Praleidus šiuos argumentus, funkcija pagal nutylėjimą naudoja standartinio argumento aukštį ir plotį, kuris šiame pavyzdyje yra vienas aukščiausias ir vienas plotis.