Intervalo de células contínuas em células filtradas em outra folha ou na mesma folha

Question

Eu tenho uma fonte de dados na qual desejo atribuir um intervalo de células contínuo apenas às células filtradas. Por exemplo, se eu tiver filtrado a célula A1,A40,A79,A118,A157 e assim por diante, tenho um intervalo de 39 células / linhas e quero atribuir intervalo de células contínuo a partir de outra folha, e. B1,B2,B3,B4,B5 e assim por diante.

Mas no momento em que coloco =B1 na célula A1 filtrada e depois a arrasto no restante das células filtradas, em vez de usar B2 em A40 , ela está tomando B40 . Assim, tenho que atribuí-los manualmente digitando o intervalo de células, ou seja, em A40 eu digite =B2 , em A79 eu digite =B3 e assim por diante.

É possível atribuir uma fórmula às linhas filtradas para que ela receba o intervalo de células contínuas?

    

Answer 1

Selecione as células A1 a A352 (ou qualquer que seja o final do seu intervalo). Digite

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

na caixa de fórmula e digite Ctrl + Enter ( não Ctrl + Deslocar + Entrar ). Isso preencherá cada célula visível com essa fórmula. Isso calcula o número da linha relativa da célula B que você deseja referenciar. Por exemplo:

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

A função OFFSET , em seguida, conta que muitas linhas da célula B$1 , então A1 referencia B1 , A40 referencia B2 , A79 referencia B3 , etc.