Uneori dorim să copiem celule alternative dintr-un interval sau fiecare valoare a treia sau fiecare valoare 4 și așa mai departe. În toate aceste cazuri, copierea manuală va fi o sarcină de scurgere. Deci, care este soluția? Cum copiați valoarea de pe fiecare al doilea rând? Există o formulă pentru asta?
Da este. Putem utiliza funcția OFFSET a Excel pentru a obține valori de pe rândurile a n-a. Cum? Să vedem.
= OFFSET (First_Cell_in_List, (ROW (A1) *N)-1,0)
First_Cell_in_Range: este referința absolută a primei celule din intervalul listei din care doriți să copiați. De exemplu, dacă lista dvs. este în A5: A100, atunci First_Cell_in_Range este $ A $ 5. Nu includeți antetul coloanei.
N: este de număr pentru care veți obține valoarea multiplilor. De exemplu, dacă doriți să obțineți fiecare a doua valoare din listă, atunci N este 2. Pentru fiecare a 3-a valoare, este 3 și așa mai departe.
ROW (A1): pentru obținerea multiplilor de N.
Acum să vedem un exemplu și apoi voi explica cum funcționează.
Exemplu Copiați fiecare valoare secundară dintr-o listă în Excel
Aici avem o listă de articole. Trebuie să copiem fiecare a doua valoare din listă într-un interval nou.
Deci, cum facem asta?
Deoarece avem 11 articole pe listă, vom primi cinci articole alternative (2, 4, 5, 6, 10).
Scrieți formula de mai jos în E4 și copiați celulele.
= OFFSET ($ B $ 4, (ROW (A1) *)2)-1,0)
Aici $ B $ 4 este prima celulă din intervalul listei și, din moment ce dorim să copiem fiecare valoare secundară (alternativă) din listă, avem N-ul nostru ca 2.
Dacă doriți să copiați fiecare al treilea element, utilizați această formulă.
= OFFSET ($ B $ 4, (ROW (A1) *)3)-1,0)
Pentru fiecare 4:
= OFFSET ($ B $ 4, (ROW (A1) *)4)-1,0)
Si asa mai departe. Puteți copia orice valoare N dintr-o folosind această formulă excel.
Cum functioneaza
Formula realizează această sarcină utilizând două funcții Excel, OFFSET și ROW.
Funcția OFFSET returnează valoarea de la rândul și offsetul coloanei dintr-o celulă dată.
Funcția ROW returnează numărul rândului referinței date.
Începând din interior, ROW (A1) va reveni 1. Deoarece A1 aparține primului rând.
(ROW (A1) *2 va deveni 2.
(ROW (A1) * 2) -1, va deveni (1 * 2) -1 = 1.
Când copiem formula, referința din ROW se va schimba ca A2, A3, A4 și așa mai departe. Pentru A2, vom obține valoarea finală (2 * 2) -1 = 3. Pentru A3, vom obține (3 * 3) -1 = 5 și așa mai departe.
Acum, formula noastră din E4 ne-a rezolvat la = OFFSET ($ B $ 4,1, 0). Aceasta va returna valoarea 1 rând de mai jos din B4, care este a doua valoare din B4.
În E4, formula va fi = OFFSET ($ 4,3,0 $ B), care este a patra valoare din B4. În mod similar, obținem toate costurile alternative.
Acum știi cum să copiezi fiecare valoare N din listă în Excel. Aceasta este tehnica pe care o cunosc. Dacă știți orice alt truc pentru a obține copierea celulei a n-a dintr-un interval, nu ezitați să distribuiți în secțiunea de comentarii de mai jos. Dacă aveți întrebări cu privire la această sau la orice altă funcție a Excel 2019, 2016, 2013 și 2010, utilizați secțiunea de comentarii.
Cum se recuperează cel mai recent preț în Excel
Preluarea primei valori dintr-o listă mai mare / mai mică decât o valoare specificată
Cum se recuperează întregul rând al unei valori potrivite
Articole populare:
Funcția VLOOKUP în Excel
COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel