Понякога искаме да копираме редуващи се клетки от диапазон или всяка трета стойност или всяка четвърта стойност и т.н. Във всички тези случаи копирането ръчно ще бъде източваща задача. И така, какво е решението? Как да копирате стойността от всеки n -ти ред? Има ли формула за това?
Да, има. Можем да използваме функцията OFFSET на Excel, за да получим стойности от n -ти редове. Как? Нека го видим.
= ОФСЕТ (First_Cell_in_List, (ROW (A1)*н)-1,0)
First_Cell_in_Range: Това е абсолютната препратка към първата клетка в диапазона от списъци, от която искате да копирате. Например, ако вашият списък е в A5: A100, тогава First_Cell_in_Range е $ A $ 5. Не включвайте заглавието на колоната.
н: той е с число, на което ще получите кратна стойност. Например, ако искате да получите всяка 2 -ра стойност от списъка, тогава N е 2. За всяка 3 -та стойност тя е 3 и така нататък.
ROW (A1): it за получаване на кратни на N.
Нека сега да видим пример и след това ще обясня как работи.
Пример Копиране на всяка втора стойност от списък в Excel
Тук имаме списък с елементи. Трябва да копираме всяка втора стойност от списъка в нов диапазон.
И така, как да направим това?
Тъй като имаме 11 елемента в списъка, ще получим пет алтернативни елемента (2, 4, 5, 6, 10).
Напишете формулата по -долу в E4 и копирайте клетките.
= OFFSET ($ B $ 4, (ROW (A1)*2)-1,0)
Тук $ B $ 4 е първата клетка в диапазона на списъка и тъй като искаме да копираме всяка втора (алтернативна) стойност от списъка, имаме нашето N като 2.
Ако искате да копирате всеки трети елемент, използвайте тази формула.
= OFFSET ($ B $ 4, (ROW (A1)*3)-1,0)
За всеки 4 -ти:
= OFFSET ($ B $ 4, (ROW (A1)*4)-1,0)
И така нататък. Можете да копирате всяка N -та стойност от a, използвайки тази формула на Excel.
Как работи
Формулата изпълнява тази задача, използвайки две Excel функции, OFFSET и ROW.
Функцията OFFSET връща стойността от изместване на ред и колона от дадена клетка.
Функцията ROW връща номера на ред с дадена препратка.
Започвайки отвътре, ROW (A1) ще се върне 1. Тъй като A1 принадлежи към 1 -ви ред.
(ROW (A1)*2 ще стане 2.
(ROW (A1)*2)-1, ще стане (1*2) -1 = 1.
Когато копираме формулата, препратката в ROW ще се промени като A2, A3, A4 и т.н. За A2 ще получим крайната стойност (2*2) -1 = 3. За A3 ще получим (3*3) -1 = 5 и така нататък.
Сега нашата формула в E4 решихме на = OFFSET ($ B $ 4,1, 0). Това ще върне стойността 1 ред по -долу от B4, което е втората стойност от B4.
В E4 формулата ще бъде = OFFSET ($ B $ 4,3,0), което е четвъртата стойност от B4. По същия начин получаваме всички редуващи се разходи.
Сега знаете как да копирате всяка N -та стойност от списъка в Excel. Това е техниката, която познавам. Ако знаете друг трик за получаване на копиране на n -та клетка от диапазон, не се колебайте да споделите в секцията за коментари по -долу. Ако имате въпроси относно тази или друга функция на Excel 2019, 2016, 2013 и 2010, използвайте секцията за коментари.
Как да извлечете най -новата цена в Excel
Извличане на първата стойност в списък, който е по -голям / по -малък от посочена стойност
Как да извлечете целия ред на съответстваща стойност
Популярни статии:
Функцията VLOOKUP в Excel
COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel