Izmantojiet SUM un OFFSET, lai atrastu kopsummas dinamiskiem datu diapazoniem
Ja jūsu Excel darblapā ir aprēķini, kas pamatojas uz mainīgo šūnu diapazonu , izmantojot SUM un OFFSET funkcijas kopā SUM OFFSET formula vienkāršo uzdevumu saglabāt aprēķinus.
Izveidojiet dinamisko diapazonu, izmantojot SUM un OFFSET funkcijas
Ja jūs izmantojat aprēķinus periodam, kas nepārtraukti mainās, piemēram, mēneša kopējais pārdošanas apjoms, OFFSET funkcija ļauj iestatīt dinamisko diapazonu, kas mainās, jo tiek pievienoti ikdienas pārdošanas rādītāji.
Parasti SUM funkcija parasti var uzņemt jaunās datu šūnas, kas tiek ievietotas diapazonā, kas tiek summēts.
Viens izņēmums rodas tad, kad dati tiek ievietoti šūnā, kurā šobrīd atrodas funkcija.
Piemēram, attēlā, kas pievienots šim rakstam, katras dienas jaunie pārdošanas rādītāji tiek pievienoti saraksta apakšdaļai, kas liek kopsummai katru reizi, kad tiek pievienoti jauni dati, nepārtraukti pārvietot vienu šūnu.
Ja SUM funkcija tiktu izmantota atsevišķi, lai apkopotu datus, vajadzētu mainīt to šūnu diapazonu, kuras tika izmantotas kā funkciju arguments katru reizi, kad tika pievienoti jauni dati.
Tomēr, izmantojot kopā SUM un OFFSET funkcijas, kopējais diapazons kļūst dinamisks. Citiem vārdiem sakot, tas mainās, lai pielāgotos jaunām datu šūnām. Jaunu datu šūnu pievienošana nerada problēmas, jo diapazons turpina pielāgoties, pievienojot katru jauno šūnu.
Sintakse un argumenti
Skatiet attēlu, kas pievienots šim rakstu, lai sekotu līdzi šai apmācībai.
Šajā formulā SUM funkcija tiek izmantota, lai apkopotu iesniegto datu diapazonu kā savu argumentu. Sākuma punkts šim diapazonam ir statisks un tiek identificēts kā šūnu atsauce uz pirmo skaitli, kas jāaprēķina pēc formulas.
OFFSET funkcija tiek ievietota SUM funkcijas funkciju iekšpusē, un to izmanto, lai izveidotu dinamisku gala punktu datu kopumam, ko veido formula. Tas tiek paveikts, nosakot diapazona gala punktu uz vienu šūnu virs formulas atrašanās vietas.
Formulas sintakse :
= SUM (diapazona sākums: OFFSET (atsauce, rindas, kolonnas))Diapazons Sākums - (nepieciešams) sākuma punkts šūnu diapazonam, ko summē SUM funkcija. Piemērā attēlā tas ir B2.
Atsauce - (vajadzīga) šūnas atsauce, ko izmanto, lai aprēķinātu diapazona gala punktu, kas atrodas daudzās rindās un kolonnās prom. Piemēra attēlā Atsauces arguments ir šūnas atsauce uz pašu formulu, jo mēs vienmēr gribam diapazonu, lai beigtu vienu šūnu virs formulas.
Rindas - (nepieciešams) rindu skaits virs vai zem atsauces argumenta, ko izmanto kompensācijas aprēķināšanai. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli.
Ja kompensācijas atrašanās vieta pārsniedz atsauces argumentu, šī vērtība ir negatīva. Ja tas ir zemāk, rindu arguments ir pozitīvs. Ja nobīde atrodas tajā pašā rindā, šis arguments ir nulle. Šajā piemērā nobīde sākas vienā rindā virs Atsauces argumenta, tāpēc šī argumenta vērtība ir negatīva (-1).
Cols - (vajadzīgs) kolonnu skaits, kas atrodas Atskaites argumenta labajā vai labajā pusē, ko izmanto ieskaita aprēķināšanai. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli
Ja kompensācijas vieta atrodas Atsauces argumenta kreisajā pusē, šī vērtība ir negatīva. Ja pa labi, kolāžu arguments ir pozitīvs. Šajā piemērā apkopotie dati ir tādā pašā slejā kā formula, tāpēc šī argumenta vērtība ir nulle.
Izmantojot SUM OFFSET formulu kopējiem pārdošanas datiem
Šajā piemērā tiek izmantota SUM OF OFFSET formula, lai atgrieztu kopējo summu ikdienas pārdošanas rādītājiem, kas uzskaitīti darblapas B slejā.
Sākotnēji formula tika ievadīta šūnā B6, un pārdošanas dati tika apkopoti četras dienas.
Nākamais solis ir pārvietot SUM OFFSET formulu rindā, lai atļautu vietu piektajā dienā.
Tas tiek paveikts, ievietojot jaunu rindu 6, kas pārvieto formulu uz 7 rindu.
Kustības rezultātā Excel automātiski atjauno Atsauces argumentu uz šūnu B7 un pievieno šūnu B6 diapazonam, ko summē formula.
SUMMAKSAS IESLĒGŠANAS formulas ievadīšana
- Noklikšķiniet uz šūnas B6, kas ir vieta, kur sākotnēji parādīsies formulas rezultāti.
- Noklikšķiniet uz lentas izvēlnes cilnes Formulas .
- Izvēlieties Math & Trig no lentes, lai atvērtu nolaižamo sarakstu funkciju.
- Sarakstā noklikšķiniet uz SUM , lai atvērtu funkciju dialoglodziņu .
- Dialoglodziņā noklikšķiniet uz rindas Number1 .
- Noklikšķiniet uz šūnas B2, lai dialoglodziņā ievadītu šo šūnu atsauci. Šī vieta ir formas galīgais parametrs;
- Dialoglodziņā noklikšķiniet uz līnijas Number2 .
- Ievadiet šādu OFFSET funkciju: OFFSET (B6, -1,0), lai veidotu dinamisko gala punktu formulai.
- Noklikšķiniet uz Labi, lai pabeigtu funkciju un aizveriet dialoglodziņu.
Kopējā summa ir 5679,15 ASV dolāri parādās šūnā B7.
Noklikšķinot uz šūnas B3, formulas joslā virs darblapas parādās pilnā funkcija = SUM (B2: OFFSET (B6, -1,0)) .
Nākamās dienas pārdošanas datu pievienošana
Lai pievienotu nākamās dienas pārdošanas datus:
- Lai atvērtu kontekstizvēlni, ar peles labo pogu noklikšķiniet uz 6. rindas rindkopas .
- Izvēlnē noklikšķiniet uz Ievietot, lai darblapā ievietotu jaunu rindu.
- Tā rezultātā SUM OFTSS formulas pārvietojas uz leju līdz šūnai B7, un 6. rindiņa tagad ir tukša.
- Noklikšķiniet uz šūnas A6 .
- Ievadiet skaitli 5, lai norādītu, ka tiek ievadīta pārdošanas summa piektajai dienai.
- Noklikšķiniet uz šūnas B6.
- Ievadiet numuru 1458,25 $ un nospiediet tastatūras taustiņu Enter .
Cell B7 atjaunina jauno kopējo summu 7137,40 ASV dolāru apmērā.
Kad jūs noklikšķināsiet uz šūnas B7, formulējuma joslā parādīsies atjauninātā formula = SUM (B2: OFFSET (B7, -1,0)) .
Piezīme . OFFSET funkcijai ir divi izvēles argumenti: Augstums un platums, kas šajā paraugā tika izlaisti.
Šos argumentus var izmantot, lai parādītu OFFSET funkcijai izejas formu, ņemot vērā, ka tā ir tik daudz rindu augsta un tik daudz kolonnu platas.
Izlaižot šos argumentus, funkcija pēc noklusējuma izmanto atsauces argumenta augstumu un platumu, kas šajā piemērā ir viena rinda augsta un viena sleja platuma.