Algumas vezes, queremos copiar células alternadas de um intervalo ou a cada três valores ou a cada quatro valores e assim por diante. Em todos esses casos, copiar manualmente será uma tarefa desgastante. Então qual é a solução? Como você copia o valor de cada enésima linha? Existe uma fórmula para isso?
Sim existe. Podemos usar a função OFFSET do Excel para obter valores das enésimas linhas. Como? Vamos ver isso.
= OFFSET (First_Cell_in_List, (ROW (A1) *N)-1,0)
First_Cell_in_Range: É a referência absoluta da primeira célula no intervalo da lista da qual você deseja copiar. Por exemplo, se sua lista estiver em A5: A100, então First_Cell_in_Range é $ A $ 5. Não inclua o título da coluna.
N: é um número que você obterá valores múltiplos. Por exemplo, se você deseja obter cada segundo valor da lista, N é 2. Para cada terceiro valor, é 3 e assim por diante.
ROW (A1): para obter múltiplos de N.
Agora vamos ver um exemplo e depois explicarei como funciona.
Exemplo de cópia a cada segundo de valor de uma lista no Excel
Aqui temos uma lista de itens. Precisamos copiar cada segundo valor da lista em um novo intervalo.
Então, como fazemos isso?
Como temos 11 itens na lista, obteremos cinco itens alternativos (2, 4, 5, 6, 10).
Escreva a fórmula abaixo em E4 e copie as células.
= OFFSET ($ B $ 4, (LINHA (A1) *2)-1,0)
Aqui $ B $ 4 é a primeira célula no intervalo da lista e, como queremos copiar cada segundo (alternativo) valor da lista, temos nosso N como 2.
Se você deseja copiar a cada terceiro item, use esta fórmula.
= OFFSET ($ B $ 4, (LINHA (A1) *3)-1,0)
Para cada 4:
= OFFSET ($ B $ 4, (LINHA (A1) *4)-1,0)
E assim por diante. Você pode copiar qualquer enésimo valor de a usando esta fórmula do Excel.
Como funciona
A fórmula realiza essa tarefa usando duas funções do Excel, OFFSET e ROW.
A função OFFSET retorna o valor do deslocamento de linha e coluna de uma determinada célula.
A função ROW retorna o número da linha da referência fornecida.
Começando de dentro, a linha (A1) retornará 1. Uma vez que A1 pertence à 1ª linha.
(ROW (A1) *2 ele se tornará 2.
(ROW (A1) * 2) -1, ele se tornará (1 * 2) -1 = 1.
Quando copiamos a fórmula, a referência em ROW mudará para A2, A3, A4 e assim por diante. Para A2, obteremos o valor final (2 * 2) -1 = 3. Para A3, teremos (3 * 3) -1 = 5 e assim por diante.
Agora, nossa fórmula em E4 nos resolveu = OFFSET ($ B $ 4,1, 0). Isso retornará o valor 1 linha abaixo de B4, que é o segundo valor de B4.
Em E4, a fórmula será = OFFSET ($ B $ 4,3,0), que é o quarto valor de B4. Da mesma forma, obtemos todos os custos alternados.
Agora você sabe como copiar cada enésimo valor da lista no Excel. Esta é a técnica que conheço. Se você conhece algum outro truque para copiar a enésima célula de um intervalo, sinta-se à vontade para compartilhar na seção de comentários abaixo. Se você tiver alguma dúvida sobre esta ou qualquer outra função do Excel 2019, 2016, 2013 e 2010, use a seção de comentários.
Como recuperar o preço mais recente no Excel
Recuperando o primeiro valor em uma lista que é maior / menor que um valor especificado
Como recuperar toda a linha de um valor correspondente
Artigos populares:
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel