00017

Иногда нам нужно скопировать чередующиеся ячейки из диапазона, или каждое третье значение, или каждое четвертое значение, и так далее. Во всех этих случаях копирование вручную будет утомительной задачей. Так какое же решение? Как скопировать значение из каждой n-й строки? Есть ли для этого формула?

Да, есть. Мы можем использовать функцию OFFSET в Excel для получения значений из n-х строк. Как? Давай увидим это.

=OFFSET(First_Cell_in_List,(ROW(A1)*N)-1,0)

First_Cell_in_Range: это абсолютная ссылка на первую ячейку в диапазоне списка, из которого вы хотите скопировать. Например, если ваш список находится в A5: A100, тогда First_Cell_in_Range составляет $ A $ 5. Не включайте заголовок столбца.

N: это число, которое вы получите кратное значение. Например, если вы хотите получить каждое 2-е значение из списка, тогда N равно 2. Для каждого 3-го значения это 3 и так далее.

СТРОКА (A1): для получения кратных N.

Теперь давайте посмотрим на пример, а затем я объясню, как это работает.

Пример копирования каждого второго значения из списка в Excel

Здесь у нас есть список предметов. Нам нужно скопировать каждое второе значение из списка в новый диапазон.

Итак, как нам это сделать?

0018

Поскольку у нас в списке 11 пунктов, мы получим пять альтернативных пунктов (2, 4, 5, 6, 10).

Запишите приведенную ниже формулу в E4 и скопируйте ячейки.

=OFFSET($B$4,(ROW(A1)*2)-1,0)

Здесь $ B $ 4 — первая ячейка в диапазоне списка, и поскольку мы хотим скопировать каждое второе (альтернативное) значение из списка, у нас N равно 2.

0019

Если вы хотите скопировать каждый третий элемент, используйте эту формулу.

=OFFSET($B$4,(ROW(A1)*3)-1,0)

На каждые 4-е:

=OFFSET($B$4,(ROW(A1)*4)-1,0)

И так далее. Вы можете скопировать любое N-е значение из a, используя эту формулу Excel.

Как это работает

Формула выполняет эту задачу с помощью двух функций Excel: link: / counting-the-offset-function-in-excel [OFFSET]

и link: / lookup-and-reference-excel-row-function [ROW].

Функция OFFSET возвращает значение из смещения строки и столбца из данной ячейки.

Функция СТРОКА возвращает номер строки данной ссылки.

Начиная изнутри, link: / lookup-and-reference-excel-row-function [ROW] (A1)

вернет 1. Поскольку A1 принадлежит 1-й строке.

(link: / lookup-and-reference-excel-row-function [ROW] (A1) 2 он станет 2.

(`link: / lookup-and-reference-excel-row-function [ROW] `(A1) 2) -1, это станет (12) -1 = 1.

Когда мы копируем формулу, ссылка в ROW изменится на A2, A3, A4 и т. Д. on. Для A2 мы получим окончательное значение (22) -1 = 3. Для A3 мы получим (33) -1 = 5 и т. д.

Теперь наша формула в E4 решена = link: / counting-the-offset-function-in-excel [OFFSET] ($ B $ 4,1,0).

Это вернет значение на 1 строку ниже из B4, которое является вторым значение из B4.

В E4 формула будет иметь вид = link: / counting-the-offset-function-in-excel [OFFSET] ($ B $ 4,3,0), что является четвертым значение из B4. Точно так же мы получаем все переменные затраты.

Теперь вы знаете, как скопировать каждое N-е значение из списка в Excel. Это метод, который я знаю. Если вы знаете какой-либо другой трюк для копирования n-й ячейки из диапазона, не стесняйтесь делиться в разделе комментариев ниже.

Если у вас есть какие-либо вопросы относительно этой или любой другой функции Excel 2019, 2016, 2013 и 2010, используйте раздел комментариев.

Статьи по теме:

link: / lookup-formulas-how-to-retrieve-latest-price-in-excel [Как получить последнюю цену в Excel]

`link: / lookup-formulas-retrieving-the-first-value-in-a-list-that-is-more-less-than-a-specified-number [Получение первого значения в списке, которое больше / меньше чем указанное значение] `

link: / lookup-formulas-retrieving-the-all-row-of-a-matched-value [Как получить всю строку совпадающего значения]

Популярные статьи:

link: / формулы-и-функции-введение-функции vlookup [Функция ВПР в Excel]

link: / tips-countif-in-microsoft-excel [СЧЁТЕСЛИ в Excel 2016]

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]