Defina uma fórmula em muitas células com edições manuais mínimas

Question

Eu preciso definir a seguinte fórmula em várias células:

 =VLOOKUP(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$FZ$99999,7,1)

Esta fórmula pesquisa a data mais recente na tabela Histórico e retorna o valor especificado. Aqui estou retornando a coluna 7.

Como posso colar as células e incrementar automaticamente o valor onde "7" é. Em outras palavras, eu preciso colar essa fórmula de resumo em várias centenas de células e alterar o número 7 para cada uma.

    

Answer 1

Uma opção é usar a função INDEX e MATCH em vez de VLOOKUP. Isso pode ser benéfico na medida em que você vê explicitamente de qual coluna o resultado é de ...... e usando a combinação correta de $ signs que você pode copiar para incrementar a coluna a cada vez, por exemplo, use esta fórmula para retornar o valor da coluna B

=INDEX(Historical!B$5:B$99999,MATCH(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$A$99999,1))

e copie em e a coluna de referência mudará para C, D, etc.

Se você quiser copiar a fórmula para baixo , eu usaria uma variação da sugestão de Lèse majesté. usando a função ROWS no original para maior robustez, por exemplo para a coluna B novamente

=VLOOKUP(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$FZ$99999,ROWS(D$2:D2)+1,1)

assume que você deseja iniciar a fórmula em D2

    

Answer 2

Normalmente, sim. Contanto que você não esteja usando referências absolutas, sempre que colar ou preencher, o Excel (e a maioria dos outros programas de planilhas, incluindo o Calc e o Google Docs) atualizará o número da linha e os números das colunas nas suas referências.

No entanto, como você está usando uma função que recebe um índice de coluna em vez de uma referência de célula, é um pouco mais complicado. Não acredito que o Excel saiba reconhecer esse argumento como uma referência de coluna, pois não há como o Excel saber se você deseja uma referência absoluta ou relativa.

Por sorte, existem as funções ROW e COLUMN , que, dada uma referência, retornarão os índices de linha e coluna, respectivamente.

No seu caso, basta substituir 7 por COLUMN(A:1) ou qualquer referência de célula que você queira usar. O Excel alterará A:1 com base na posição relativa da nova célula.