Скопируйте формулу вниз, пропуская ячейки, но заставив ее извлекать данные по одному приращению с другого листа.

Question

У меня есть табель учета рабочего времени, который я помогаю создать, и он извлекает данные из листа 1 и добавляет их в список на листе 2. Данные должны ссылаться на лист 1 в A42, затем A73, A115, A146, A188 и т. д., следуя шаблону интервалов 42, затем 31, затем 42, затем 31; 100 экземпляров этого. Как я могу ссылаться на данные таким образом?

Answer 1

Поскольку, по-видимому, существует постоянная закономерность с ячейками Sheet1, на которые вам нужно ссылаться, это просто вопрос построения формулы для получения правильного номера строки Sheet1 на основе номера строки Sheet2. Довольно легко создать формулу ссылки, используя некоторые функции ROW(), ISEVEN()или ISODD(), MOD()и FLOOR().

номер строки Листа1 =31*FLOOR((ROW())/2;1)+42*FLOOR((ROW()+1)/2;1)

От номера строки до фактической формулы с INDEX()функцией:

=INDEX(Sheet1!A:A;30*FLOOR(ROW()/2;1)+41*FLOOR((ROW()-1)/2;1)+42;1)

(в формуле вам, очевидно, необходимо изменить диапазон первого параметра, чтобы он соответствовал вашим исходным данным, и число в последнем параметре, которое указывает номер столбца в диапазоне).

Поскольку номер строки на Листе2 является основным фактором для получения правильной строки на Листе1, приведенное выше работает только в том случае, если исходные данные на Листе1 начинаются со строки 42, а результирующие данные на Листе2 начинаются со строки 1. Если это изменится, вы, конечно, можете вычесть смещение строки в формуле для каждого вызова ROW()(для смещения на Листе2) и/или прибавить/вычесть к полученному номеру строки (для изменения смещения на Листе1).

Answer 2

Вот пример того, как это можно сделать с помощью формул:

Я использую столбец A, чтобы проиллюстрировать шаблон, который производит суть формулы. Это начинается в строке 2, поскольку строка 1, вероятно, будет содержать заголовки. Это будет на листе 2, где вы хотите построить список элементов, выбранных с листа 1. Формула в A2, которая вычисляет целевые номера строк, выглядит так:

=INT(ROW()/2)*42+INT((ROW()-1)/2)*31

На следующем снимке экрана показано, как это можно использовать для извлечения ячейки с другого листа:

Я вставил что-то для копирования только в ячейку A42, поэтому заполнена только одна ячейка в столбце B. Формула использует функцию INDIRECT для построения ссылки на ячейку. Формула в B2:

=INDIRECT("sheet1!A" & INT(ROW()/2)*42+INT((ROW()-1)/2)*31)

Ячейка А42 листа 1 содержит ABC.