Непрерывный диапазон ячеек в отфильтрованных ячейках на другом или том же листе

Question

У меня есть источник данных, в котором я хочу назначить непрерывный диапазон ячеек только отфильтрованным ячейкам. Например, если я отфильтровал ячейки A1,A40,A79,A118,A157 и т.д. , Имеет разрыв в 39 ячеек / строк, и я хочу назначить непрерывный диапазон ячеек из другого листа, например B1,B2,B3,B4,B5 и скоро.

Но в тот момент, когда я помещаю =B1 в отфильтрованную ячейку A1 и перетаскиваю его вниз на оставшиеся отфильтрованные ячейки, вместо того, чтобы брать B2 в A40 он принимает значение B40 . Таким образом, я должен вручную назначить их, введя диапазон ячеек, то есть в A40 I type =B2 , в A79 I type =B3 и так далее.

Можно ли назначить формулу для отфильтрованных строк, чтобы она занимала непрерывный диапазон ячеек?

Answer 1

Выберите ячейки от A1 до A352 (или любой другой конец диапазона).  Тип

=OFFSET(B$1, (ROW()-1)/39, 0)

в поле формулы введите Ctrl+Enter (не Ctrl+Shift+Enter).  Это заполнит каждую видимую ячейку этой формулой.  Это вычисляет относительный номер строки ячейки B которую вы хотите сослаться.  Например:

 (1-1)/39 =  0/39 = 0
(40-1)/39 = 39/39 = 1
(79-1)/39 = 78/39 = 2

и т. д. Функция OFFSET затем отсчитывает столько строк из ячейки B$1 , поэтому A1 ссылается на B1 , A40 ссылается на B2 , A79 ссылается на B3 и т. д.