Excel SUM ir OFFSET formulė

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., Bendrojo mėnesio pardavimai, "OFFSET" funkcija "Excel" leidžia nustatyti dinaminį diapazoną, kuris keičia, kai pridedami kiekvienos dienos pardavimų duomenys.

Savo ruožtu, SUM funkcija paprastai gali apimti naujų duomenų ląstelių įterpimą į apibendrinamą diapazoną.

Viena išimtis įvyksta, kai duomenys įterpiami į ląstelę, kurioje šiuo metu yra funkcija.

Viršuje esančiame pavyzdiniame paveikslėlyje sąrašo apačioje pateikiami nauji kiekvienos dienos pardavimų duomenys, todėl kiekvieną kartą, kai pridedami nauji duomenys, visada persijungia po vieną langelį.

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ėlyje aukščiau, 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))

• Diapazono pradžia: Reikalingas ląstelių diapazono pradinis taškas, kuris bus sumotas SUM funkcija. Pavyzdiniame paveikslėlyje tai yra langelis B2.
• Nuoroda: Reikalinga ląstelių nuoroda, naudojama diapazono galutinei vertei apskaičiuoti, yra daugelyje eilučių ir stulpelių. Pavyzdiniame paveikslėlyje "Reference" argumentas yra pati formulės langelio nuoroda, nes visada norite, kad diapazonas baigtų vieną langelį virš formulės.
• Eilutės: Reikalingas eilučių skaičius aukščiau arba žemiau referencinio argumento, naudojamo apskaičiuojant poslinkį. Ši vertė gali būti teigiama, neigiama arba nustatyta nuline. Jei kompensacijos vieta yra didesnė už Nuoroda argumentas, vertė yra neigiama. Jei žemiau, eilučių argumentas yra teigiamas. Jei nuokrypis yra toje pačioje eilutėje, argumentas yra lygus nuliui. Šiame pavyzdyje nuokrypis prasideda viena eilute virš nuorodinio argumento, todėl šio argumento vertė yra neigiama (-1).
• Cols: Būtina skaičiuoti stulpelius į kairę arba į dešinę nuo standartinio 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 prideda langelįB6 į diapazoną, susumuotą pagal formulę.

Įvesdami SUM OFFSET formulę

1. Spustelėkite ląstelę B6, kuri yra vieta, kurioje iš pradžių bus rodomi formulės rezultatai.
2. Spustelėkite antFormulės juostos meniu skirtukas.
3. PasirinkiteMatematika ir treniruotėiš juostos atidaryti išplečiamojo sąrašo funkciją.
4. SpustelėkiteSUMA sąraše, kad pamatytumėte funkcijos dialogo langą.
5. Dialogo lange spustelėkite mygtukąNumeris 1linija
6. Spustelėkite langelį B2 į dialogo langą įvesti šią nuorodą. Ši vieta yra statinė galutinė formulė.
7. Dialogo lange spustelėkite mygtukąNumeris2linija
8. Įveskite šią OFFSET funkciją: OFFSET (B6, -1,0) formuoti dinaminę galutinę formulę.
9. Spustelėkite Gerai užbaigti funkciją ir uždaryti dialogo langą.

Bendras rodomas langelyje B7.

Kitų dienų pardavimo duomenų pridėjimas

Norėdami pridėti kitų dienų pardavimo duomenis:

1. Dešiniuoju pelės klavišu spustelėkite eilutės 6 antraštę, kad atidarytumėte kontekstinį meniu.
2. Meniu spustelėkiteĮdėti įterpti naują eilutę į darbalapį.
3. Todėl SUM OFFSET formulė juda žemyn į langelį B7, o eilutė 6 yra tuščia.
4. Spustelėkite langelį A6.
5. Įveskite numerį5 nurodant, kad įvedama penktos dienos pardavimų suma.
6. Spustelėkite langelį B6.
7. Įveskite numerį$1458.25 ir paspauskiteĮveskite raktas ant klaviatūros.

B7 ląstelės atnaujinamos naujais$7137.40.

Kai spustelėsite langelį B7, atnaujinsite formulę= SUM (B2: OFFSET (B7, -1,0)) pasirodo formulės juostoje.

OFFSET funkcijai yra du neprivalomi argumentai: aukštis ir plotis , kurie šiame pavyzdyje nebuvo naudojami.

Šie argumentai gali būti naudojami norint, kad OFSET funkcija atitiktų produkcijos formą tiek, kad tiek daug eilučių būtų aukšta ir tiek daug stulpelių.

Praleidus šiuos argumentus, funkcija pagal nutylėjimą naudoja standartinio argumento aukštį ir plotį, kuris šiame pavyzdyje yra vienas aukščiausias ir vienas plotis.