Kopējiet vērtību no katras n rindas

Lai kopētu vērtības vai ģenerētu atsauces ar modeli, piemēram, katru 3. rindu, katru 7. rindu utt., Varat izmantot formulu, kuras pamatā ir funkcijas OFFSET un ROW. Parādītajā piemērā D5 formula ir šāda:

= OFFSET ($B,( ROW (A1)*n)-1,0)

Kuru var nokopēt D slejā, lai no B slejas iegūtu n -tās vērtības.

Programmā Excel jūs nevarat viegli kopēt formulas, kurām jāizlaiž rindas vai kolonnas, ievērojot noteiktu modeli, jo atsauces formulā automātiski mainīsies pēc attiecībām starp sākotnējo avota šūnu un jauno mērķa šūnu. Tomēr ar nelielu darbu ir iespējams izveidot formulas atsauces, kas atbilst noteiktiem modeļiem.

Šajā gadījumā mēs izmantojam funkciju OFFSET, kas ir veidota, lai veidotu atsauces uz citām šūnām vai šūnu diapazoniem, pamatojoties uz noteiktu sākuma punktu vai izcelsmi.

Parādītajā piemērā sākuma šūna ir B5, kas norādīta OFFSET kā atsauces arguments un fiksēta kā absolūta atsauce, lai tā nemainītos, kad formula tiek kopēta:

aprēķināt naudas plūsmas pašreizējo vērtību Excel

 
= OFFSET ($B,( ROW (D1)*3)-1,0)

Lai aprēķinātu rindu argumenta pareizo vērtību, mēs izmantojam apakšformulu, kuras pamatā ir funkcija ROW:

 
= OFFSET ($B

Rindai tiek dota pirmā šūna tajā pašā kolonnā, un tā atgriež 1. Šo rezultātu reizina ar n, kas šajā piemērā ir 3, lai iegūtu 3.

Kad formula tiek nokopēta kolonnā, ar ROW atgrieztā vērtība palielinās par 1, kas rada “n -to modeli”.

Iemesls, kāpēc mēs katrā gadījumā atņemam 1, ir tāpēc, ka funkcija OFFSET neietver atsauces šūnu, kad tiek izmantots rindu arguments. Citiem vārdiem sakot, nobīde par vienu rindu no A1 atgriež A2:

 
( ROW (D1)*3)-1

Atņemot 1, šī uzvedība tiek ņemta vērā.

Sākot no 1

Ja vēlaties sākt kopēšanu pirmajā rindā, tad izpildiet katru n -to modeli, varat pielāgot formulu šādi:

 
= OFFSET (A1,1,0) // returns A2