Referenciando uma linha com células em branco inbetween e retornando resultados de outra linha sem células em branco entre

Question

Eu tenho uma linha que com células espaçadas uniformemente entre si. Eu gostaria que cada célula 8 nessa linha fizesse referência a uma célula em outra planilha onde não há espaços entre as células. Eu tenho um monte de dados nesta linha, então eu não quero fazer nada que vai retardar o documento já grande e sem código VB.

    

Answer 1

Aqui está a essência de como conseguir isso.

Digamos que os valores de origem na planilha1 estejam na linha 3, começando na coluna C; C3, D3, E3, etc. As células de exibição estão na folha 2 na linha 5, começando na coluna B, depois em cada 8ª célula, então B5, J5, R5, etc. Eu propositadamente usei diferentes linhas e colunas iniciais para ilustração. Pode ser mais simples no seu caso.

Assim, você deseja que B5 exiba C3, J5 exiba D3, R5 exiba E3 etc. Há duas maneiras comuns de fazer isso, usando OFFSET ou INDIRECT. Eu vou ilustrar os dois.

Cálculo de coluna: Para tanto, é necessário calcular o número de incrementos de coluna (um incremento é 8 na folha2 vs. 1 na folha1). A fórmula para isso seria:

(COLUMN()-COLUMN(B5))/8  or in this example:
(COLUMN()-2)/8

A função COLUMN () vazia retorna a coluna da célula em que está. Para cada célula de exibição na planilha2, sua coluna, menos a primeira coluna da série, dividida por 8, fornece o número de incrementos de coluna na planilha1 .

Para o OFFSET , você o usaria assim. A fórmula nas células da folha2 seria:

=OFFSET(sheet1!C3,0,(COLUMN()-2)/8,1,1)

OFFSET funciona a partir de uma célula âncora (C3 neste exemplo), compensa 0 linhas, compensa o número de colunas calculadas pela fórmula acima e retorna um resultado de 1 linha por 1 coluna de tamanho (uma única célula).

INDIRECT traduz algo que você cria, de texto e cálculos, em um endereço. Para esta função, você precisa adicionar o ponto de ancoragem para localizar o endereço real da célula. INDIRECT permite que você use um estilo alternativo de especificação de endereço que seja útil para calcular colunas; Refere-se a colunas como números em vez de letras. A célula C3 seria R3C3, ou linha 3, coluna 3.

Adicionar na coluna da âncora faria com que o cálculo da coluna fosse assim:

(COLUMN()-COLUMN(B5))/8+COLUMN(sheet1!C3)  or in this example:
(COLUMN()-2)/8+3

A criação da referência de célula é feita pela concatenação de texto e números calculados:

=INDIRECT("sheet1!R3C"&(COLUMN()-2)/8+3,false)

O "falso" indica que a referência da célula está no estilo "R1C1".

Configure a fórmula na primeira célula de exibição na planilha2, ajustando-a para o layout da sua planilha. Em seguida, copie-o para cada uma das outras células de exibição.