Copie a fórmula, pulando células, mas faça com que ela extraia dados em incrementos únicos de outra planilha

Question

Tenho uma planilha de horas que estou ajudando a criar e ela extrai dados da planilha 1 e os adiciona a uma lista na planilha 2. Os dados precisam fazer referência à planilha 1 em A42, depois em A73, A115, A146, A188, etc. , seguindo um padrão de espaçamento de 42, depois 31, depois 42 e depois 31; 100 ocorrências disso. Como posso referenciar dados dessa maneira?

Answer 1

Como parece que há um padrão constante com as células da Planilha1 que você precisa referenciar, é apenas uma questão de construir uma fórmula para produzir o número de linha correto da Planilha1 com base no número da linha da Planilha2. É bastante fácil produzir a fórmula de referência usando algumas funções ROW(), ISEVEN()ou ISODD(), MOD()e FLOOR().

número da linha da Planilha1 =31*FLOOR((ROW())/2;1)+42*FLOOR((ROW()+1)/2;1)

Do número da linha à fórmula real com INDEX()função:

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

(na fórmula, obviamente você precisa modificar o intervalo do primeiro parâmetro para caber nos dados de origem e o número no último parâmetro que indica o número da coluna no intervalo).

Como o número da linha da Planilha2 é um fator principal para obter a linha correta na Planilha1, o procedimento acima funciona apenas quando os dados de origem na Planilha1 começam na linha 42 e os dados de resultado na Planilha2 começam na linha 1. Se isso mudar, você poderá é claro, subtrair o deslocamento da linha na fórmula para cada chamada para ROW()(para deslocamento na Planilha2) e/ou adicionar/subtrair ao número da linha resultante (para alteração do deslocamento na Planilha1).

Answer 2

Aqui está um exemplo de como você pode fazer isso com fórmulas:

Estou usando a coluna A para ilustrar o padrão que a carne da fórmula produz. Isso começa na linha 2, pois a linha 1 provavelmente conteria títulos. Isso estaria na planilha 2, onde você deseja construir uma lista de itens selecionados na planilha 1. A fórmula em A2 que calcula os números das linhas de destino é:

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

A próxima captura de tela ilustra como você usaria isso para extrair uma célula da outra planilha:

Coloquei algo para copiar apenas na célula A42, por isso apenas uma célula é preenchida na coluna B. A fórmula usa a função INDIRETA para construir uma referência de célula. A fórmula em B2 é:

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

A célula A42 da planilha 1 contém ABC.