Dažreiz mēs vēlamies kopēt mainīgas šūnas no diapazona vai katras trešās vērtības vai katras ceturtās vērtības utt. Visos šajos gadījumos manuāla kopēšana būs iztukšošanas uzdevums. Tātad, kāds ir risinājums? Kā jūs kopējat vērtību no katras n rindas? Vai tam ir formula?
Jā tur ir. Mēs varam izmantot Excel funkciju OFFSET, lai iegūtu vērtības no n. Rindām. Kā? Redzēsim.
= OFFSET (First_Cell_in_List, (Rinda (A1)*N)-1,0)
First_Cell_in_Range: tā ir absolūtā atsauce uz pirmo šūnu sarakstā, no kura vēlaties kopēt. Piemēram, ja jūsu saraksts ir A5: A100, tad First_Cell_in_Range ir 5 USD. Neiekļaujiet slejas virsrakstu.
N: tas ir skaitlis, kura vērtību jūs iegūsit vairākkārt. Piemēram, ja vēlaties iegūt katru otro vērtību no saraksta, tad N ir 2. Katrai trešajai vērtībai tas ir 3 utt.
Rinda (A1): tas, lai iegūtu daudzkārtņus N.
Tagad apskatīsim piemēru, un tad es paskaidrošu, kā tas darbojas.
Piemērs Kopējiet katru otro vērtību no saraksta programmā Excel
Šeit mums ir priekšmetu saraksts. Mums ir jākopē katra otrā vērtība no saraksta jaunā diapazonā.
Tātad, kā mēs to darām?
Tā kā mūsu sarakstā ir 11 vienumi, mēs iegūsim piecus alternatīvus vienumus (2, 4, 5, 6, 10).
Ierakstiet zemāk esošo formulu E4 un nokopējiet šūnas.
= OFFSET ($ B $ 4, (ROW (A1)*2)-1,0)
Šeit $ B $ 4 ir pirmā saraksta diapazona šūna, un, tā kā mēs vēlamies no saraksta nokopēt katru otro (alternatīvo) vērtību, mūsu N ir 2.
Ja vēlaties kopēt katru trešo vienumu, izmantojiet šo formulu.
= OFFSET ($ B $ 4, (ROW (A1)*3)-1,0)
Katram ceturtajam:
= OFFSET ($ B $ 4, (ROW (A1)*4)-1,0)
Un tā tālāk. Izmantojot šo Excel formulu, varat kopēt jebkuru N vērtību no a.
Kā tas strādā
Formula šo uzdevumu veic, izmantojot divas Excel funkcijas - OFFSET un ROW.
Funkcija OFFSET atgriež vērtību no rindas un kolonnas nobīdes no noteiktas šūnas.
Funkcija ROW atgriež dotās atsauces rindas numuru.
Sākot no iekšpuses, ROW (A1) atgriezīsies 1. Tā kā A1 pieder 1. rindai.
(Rinda (A1)*2 tas kļūs par 2.
(Rinda (A1)*2)-1, tas kļūs (1*2) -1 = 1.
Kad mēs nokopējam formulu, atsauce ROW mainīsies kā A2, A3, A4 utt. A2 gadījumā mēs iegūsim galīgo vērtību (2*2) -1 = 3. Attiecībā uz A3 mēs iegūsim (3*3) -1 = 5 un tā tālāk.
Tagad mūsu formula E4 mums atrisināja līdz = OFFSET ($ B $ 4,1, 0). Tādējādi tiks atgriezta vērtība 1 rinda zem B4, kas ir otrā vērtība no B4.
E4 formulā formula būs = OFFSET ($ B $ 4,3,0), kas ir ceturtā vērtība no B4. Līdzīgi mēs saņemam visas mainīgās izmaksas.
Tagad jūs zināt, kā kopēt Excel katru sarakstu no Excel saraksta. Šī ir tehnika, ko es zinu. Ja jūs zināt kādu citu triku, kā nokopēt n. Šūnu no diapazona, nekautrējieties dalīties komentāru sadaļā zemāk. Ja jums ir kādi jautājumi par šo vai kādu citu Excel 2019, 2016, 2013 un 2010 funkciju, izmantojiet komentāru sadaļu.
Kā iegūt jaunāko cenu programmā Excel
Saraksta pirmās vērtības izgūšana, kas ir lielāka / mazāka par norādīto vērtību
Kā iegūt visu atbilstošās vērtības rindu
Populāri raksti:
Funkcija VLOOKUP programmā Excel
COUNTIF programmā Excel 2016
Kā lietot SUMIF funkciju programmā Excel