Ja jūsu Excel darblapā ir iekļauti aprēķini, kuru pamatā ir mainīgs šūnu diapazons, izmantojiet funkcijas SUM un OFFSET kopā SUM OFFSET formulā, lai vienkāršotu aprēķinu atjaunināšanu.
Šajā rakstā sniegtie norādījumi attiecas uz Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013 un Excel 2010.
Izveidojiet dinamisko diapazonu, izmantojot SUM un OFFSET funkcijas
Ja izmantojat aprēķinus par laika periodu, kas pastāvīgi mainās, piemēram, nosakot pārdošanas apjomu mēnesī, izmantojiet funkciju OFFSET programmā Excel, lai iestatītu dinamisko diapazonu, kas mainās, pievienojot katras dienas pārdošanas rādītājus.
Funkcija SUM pati par sevi parasti var pielāgot jaunu datu šūnu ievietošanu summējamajā diapazonā. Viens izņēmums rodas, kad dati tiek ievietoti šūnā, kurā pašlaik atrodas funkcija.
Tālāk esošajā piemērā katras dienas jaunie pārdošanas rādītāji ir pievienoti saraksta apakšā, liekot kopējai summai nepārtraukti pārvietoties par vienu šūnu uz leju katru reizi, kad tiek pievienoti jauni dati.
Lai turpinātu šo apmācību, atveriet tukšu Excel darblapu un ievadiet datu paraugu. Jūsu darblapa nav jāformatē kā piemērā, taču noteikti ievadiet datus tajās pašās šūnās.
Ja datu summēšanai tiek izmantota tikai funkcija SUM, šūnu diapazons, kas tiek izmantots kā funkcijas arguments, būs jāmaina katru reizi, kad tiek pievienoti jauni dati.
Izmantojot SUM un OFFSET funkcijas kopā, summētais diapazons kļūst dinamisks un 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 jaunu šūnu.
Sintakse un argumenti
Šajā formulā funkcija SUM tiek izmantota, lai apkopotu kā argumentu sniegto datu diapazonu. Šī diapazona sākuma punkts ir statisks un tiek identificēts kā šūnas atsauce uz pirmo skaitli, kas jāsaskaita pēc formulas.
Funkcija OFFSET ir ligzdota funkcijā SUM un izveido dinamisku beigu punktu datu diapazonam, ko apkopo formula. To panāk, iestatot diapazona beigu punktu uz vienu šūnu virs formulas atrašanās vietas.
Formulas sintakse ir:
=SUM(diapazona sākums: OFFSET(atsauce, rindas, kolonas))
Argumenti ir:
- Diapazona sākums: sākuma punkts šūnu diapazonam, ko summēs funkcija SUM. Šajā piemērā sākuma punkts ir šūna B2.
- Reference: vajadzīgā šūnas atsauce, ko izmanto diapazona beigu punkta aprēķināšanai. Piemērā arguments Atsauce ir formulas šūnas atsauce, jo diapazons beidzas vienu šūnu virs formulas.
- Rindas: ir nepieciešams rindu skaits virs vai zem atsauces argumenta, ko izmanto nobīdes aprēķināšanā. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli. Ja nobīdes vieta ir virs argumenta Atsauces, vērtība ir negatīva. Ja nobīde ir zemāka, arguments Rows ir pozitīvs. Ja nobīde atrodas tajā pašā rindā, arguments ir nulle. Šajā piemērā nobīde sākas vienu rindu virs atsauces argumenta, tāpēc argumenta vērtība ir negatīva (-1).
- Cols: kolonnu skaits pa kreisi vai pa labi no atsauces argumenta, ko izmanto, lai aprēķinātu nobīdi. Šī vērtība var būt pozitīva, negatīva vai iestatīta uz nulli. Ja nobīdes vieta atrodas pa kreisi no atsauces argumenta, šī vērtība ir negatīva. Ja nobīde ir pareiza, Cols arguments ir pozitīvs. Šajā piemērā summējamie dati atrodas tajā pašā kolonnā kā formula, tāpēc šī argumenta vērtība ir nulle.
Izmantojiet SUM OFFSET formulu, lai iegūtu kopējos pārdošanas datus
Šajā piemērā tiek izmantota SUM OFFSET formula, lai atgrieztu kopējo ikdienas pārdošanas rādītāju summu, kas norādīta darblapas B slejā. Sākotnēji formula tika ievadīta šūnā B6, un tajā tika apkopoti pārdošanas dati par četrām dienām.
Nākamais solis ir pārvietot SUM OFFSET formulu par vienu rindu uz leju, lai atbrīvotu vietu piektās dienas pārdošanas kopsummai. To panāk, ievietojot jaunu 6. rindu, kas pārvieto formulu uz 7. rindu.
Pārvietošanas rezultātā programma Excel automātiski atjaunina atsauces argumentu šūnā B7 un pievieno šūnu B6 diapazonam, kas summēts ar formulu.
- Atlasiet šūnu B6, kas ir vieta, kur sākotnēji tiks parādīti formulas rezultāti.
-
Atlasiet lentes cilni Formulas.
Image -
Izvēlēties Math & Trig.
Image -
Atlasiet SUM.
Image - Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā Number1.
-
Darblapā atlasiet šūnu B2, lai dialoglodziņā ievadītu šīs šūnas atsauci. Šī vieta ir formulas statiskais beigu punkts.
Image - Dialoglodziņā Funkciju argumenti novietojiet kursoru tekstlodziņā Number2.
-
Ievadiet OFFSET(B6, -1, 0). Šī funkcija OFFSET veido formulas dinamisko beigu punktu.
Image -
Atlasiet OK, lai pabeigtu funkciju un aizvērtu dialoglodziņu. Kopējā summa tiek parādīta šūnā B6.
Image
Pievienojiet nākamās dienas pārdošanas datus
Lai pievienotu nākamās dienas pārdošanas datus:
- Ar peles labo pogu noklikšķiniet uz 6. rindas galvenes.
-
Atlasiet Ievietot, lai darblapā ievietotu jaunu rindu. SUM OFFSET formula pārvietojas par vienu rindu uz leju uz šūnu B7, un 6. rinda tagad ir tukša.
Image - Atlasiet šūnu A6 un ievadiet skaitli 5, lai norādītu, ka tiek ievadīta piektās dienas pārdošanas kopsumma.
-
Atlasiet šūnu B6, ievadiet $1458.25, pēc tam nospiediet Enter.
Image - Šūnas B7 atjauninājumi līdz jaunajai summai 7137,40 USD.
Atlasot šūnu B7, atjauninātā formula tiek parādīta formulu joslā.
=SUM(B2:OFFSET(B7, -1, 0))
Funkcijai OFFSET ir divi izvēles argumenti: Augstums un Platums, kas šajā piemērā netika izmantoti. Šie argumenti norāda funkcijai OFFSET izvades formu rindu un kolonnu skaita izteiksmē.
Izlaižot šos argumentus, funkcija izmanto atsauces argumenta augstumu un platumu, kas šajā piemērā ir vienu rindu augsts un vienu kolonnu plats.
