Včasih želimo kopirati izmenične celice iz obsega ali vsake tretje vrednosti ali vsake 4. vrednosti itd. V vseh teh primerih bo ročno kopiranje izčrpavajoča naloga. Kaj je torej rešitev? Kako kopirate vrednost iz vsake n -te vrstice? Ali obstaja formula za to?
Ja, obstaja. Za pridobitev vrednosti iz n -ih vrstic lahko uporabimo Excelovo funkcijo OFFSET. Kako? Poglejmo.
= OFFSET (First_Cell_in_List, (ROW (A1)*N)-1,0)
First_Cell_in_Range: To je absolutna referenca prve celice v obsegu seznama, iz katere želite kopirati. Na primer, če je vaš seznam v A5: A100, potem First_Cell_in_Range je $ 5 $. Ne vključite naslova stolpca.
N: je številka, za katero boste dobili večkratno vrednost. Na primer, če želite iz seznama dobiti vsako drugo vrednost, potem je N 2. Za vsako tretjo vrednost je 3 itd.
RED (A1): to za pridobivanje večkratnikov N.
Zdaj pa poglejmo primer, nato pa bom razložil, kako deluje.
Primer Kopirajte vsako drugo vrednost s seznama v Excelu
Tukaj imamo seznam predmetov. Vsako drugo vrednost s seznama moramo kopirati v nov obseg.
Torej, kako to storimo?
Ker imamo na seznamu 11 elementov, bomo dobili pet nadomestnih postavk (2, 4, 5, 6, 10).
Spodnjo formulo zapišite v E4 in celice prepišite.
= OFFSET ($ B $ 4, (ROW (A1)*2)-1,0)
Tu je $ B $ 4 prva celica v obsegu seznama, in ker želimo kopirati vsako drugo (nadomestno) vrednost s seznama, imamo N kot 2.
Če želite kopirati vsak tretji element, uporabite to formulo.
= OFFSET ($ B $ 4, (ROW (A1)*3)-1,0)
Za vsakega četrtega:
= OFFSET ($ B $ 4, (ROW (A1)*4)-1,0)
In tako naprej. S to formulo Excel lahko kopirate poljubno N -to vrednost.
Kako deluje
Formula to nalogo opravi z uporabo dveh Excelovih funkcij, OFFSET in ROW.
Funkcija OFFSET vrne vrednost iz zamika vrstice in stolpca iz dane celice.
Funkcija ROW vrne številko vrstice dane reference.
Začenši od znotraj, se bo ROW (A1) vrnil 1. Ker A1 spada v prvo vrsto.
(ROW (A1)*2 postala bo 2.
(ROW (A1)*2)-1, postal bo (1*2) -1 = 1.
Ko kopiramo formulo, se bo sklic v vrstici ROW spremenil v A2, A3, A4 itd. Za A2 bomo dobili končno vrednost (2*2) -1 = 3. Za A3 bomo dobili (3*3) -1 = 5 in tako naprej.
Zdaj smo našo formulo v E4 rešili na = OFFSET ($ B $ 4,1, 0). To bo vrnilo vrednost 1 vrstice spodaj iz B4, kar je druga vrednost iz B4.
V E4 bo formula = OFFSET ($ B $ 4,3,0), kar je četrta vrednost iz B4. Podobno dobimo vse izmenične stroške.
Zdaj veste, kako kopirati vsako N -to vrednost s seznama v Excelu. To je tehnika, ki jo poznam. Če poznate kakšen drug trik za kopiranje n -te celice iz obsega, vas prosimo, da to delite v spodnjem razdelku za komentarje. Če imate kakršna koli vprašanja glede te ali katere koli druge funkcije Excela 2019, 2016, 2013 in 2010, uporabite razdelek s komentarji.
Kako pridobiti zadnjo ceno v Excelu
Pridobivanje prve vrednosti na seznamu, ki je večja / manjša od podane vrednosti
Kako pridobiti celotno vrstico ujemajoče se vrednosti
Priljubljeni članki:
Funkcija VLOOKUP v Excelu
COUNTIF v Excelu 2016
Kako uporabljati funkcijo SUMIF v Excelu