Vispārēja formula
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Kopsavilkums
Lai kopētu vērtības vai ģenerētu atsauces ar tādu rakstu kā katra 3. rinda, katra 7. rinda utt., Varat izmantot formulu, kuras pamatā ir funkcijas OFFSET un ROW. Parādītajā piemērā formula D5 ir:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Ko var nokopēt lejup D slejā, lai no B slejas iegūtu n-tās vērtības.
Paskaidrojums
Programmā Excel jūs nevarat viegli kopēt formulas, kurām jāizlaiž rindas vai kolonnas pēc noteikta modeļa, jo atsauces formulā automātiski mainīsies, ievērojot sākotnējās avota šūnas un jaunās mērķa šūnas attiecības. Tomēr ar nelielu darbu ir iespējams izveidot formulu atsauces, kas seko noteiktiem modeļiem.
Šajā gadījumā mēs izmantojam funkciju OFFSET, kas ir paredzēta, lai izveidotu 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 tiek sniegta OFFSET kā atsauces arguments un fiksēta kā absolūta atsauce, tāpēc tā nemainīsies, kopējot formulu:
=OFFSET($B$5
Lai aprēķinātu pareizo vērtību rindu argumentam, mēs izmantojam apakšformulu, pamatojoties uz funkciju ROW:
(ROW(D1)*3)-1
Rindai tiek dota pirmā šūna tajā pašā kolonnā, un tā atgriež 1. Šis rezultāts tiek reizināts ar n, kas šajā piemērā ir 3, lai iegūtu 3.
Formulu nokopējot kolonnā, 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, ieskaitot vienu rindu no A1 atgriež A2:
=OFFSET(A1,1,0) // returns A2
Atņemot 1, šī rīcība tiek ņemta vērā.
Sākot ar 1
Ja vēlaties sākt kopēšanu pirmajā rindā, pēc tam izpildiet katru n-to modeli, formulu var pielāgot šādi:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
