Copiar fórmulas sem deslocamento de link

Problema

Suponha que temos uma tabela simples como esta, na qual os valores são calculados para cada mês em duas cidades, e então o total é convertido em euros pela taxa da célula amarela J2.

O problema é que, se você copiar o intervalo D2:D8 com fórmulas em outro lugar da planilha, o Microsoft Excel corrigirá automaticamente os links nessas fórmulas, movendo-os para um novo local e parando de contar:

Tarefa: copie o intervalo com fórmulas para que as fórmulas não mudem e permaneçam as mesmas, mantendo os resultados do cálculo.

Método 1. Links absolutos

Método 2: desativar fórmulas temporariamente

Para evitar que as fórmulas sejam alteradas ao copiar, você precisa (temporariamente) garantir que o Excel pare de tratá-las como fórmulas. Isso pode ser feito substituindo o sinal de igual (=) por qualquer outro caractere normalmente não encontrado em fórmulas, como um sinal de cerquilha (#) ou um par de e comercial (&&) para tempo de cópia. Por esta:

1. Selecione o intervalo com fórmulas (no nosso exemplo D2:D8)
2. Clique Ctrl + H no teclado ou em uma guia Home – Localizar e Selecionar – Substituir (Início — Localizar e Selecionar — Substituir)

   

3. Na caixa de diálogo que aparece, digite o que estamos procurando e com o que substituímos, e em parâmetros (Opções) não se esqueça de esclarecer Escopo da pesquisa - Fórmulas. Nós pressionamos Substitua tudo (Substitua tudo).
4. Copie o intervalo resultante com fórmulas desativadas para o lugar certo:

   

5. Substituir # on = de volta usando a mesma janela, retornando a funcionalidade às fórmulas.

Método 3: Copiar via Bloco de Notas

Este método é muito mais rápido e fácil.

Pressione o atalho do teclado Ctrl+Ё ou botão Mostrar fórmulas aba Fórmula (Fórmulas — Mostrar fórmulas), para ativar o modo de verificação de fórmulas – em vez dos resultados, as células exibirão as fórmulas pelas quais são calculadas:

Copie nosso intervalo D2:D8 e cole-o no padrão Portátil:

Agora selecione tudo colado (Ctrl + A), copie para a área de transferência novamente (Ctrl + C) e cole na planilha no local que você precisa:

Resta apenas pressionar o botão Mostrar fórmulas (Mostrar Fórmulas)para retornar o Excel ao modo normal.

Observação: esse método às vezes falha em tabelas complexas com células mescladas, mas na grande maioria dos casos funciona bem.

Método 4. Macro

Se você costuma fazer essa cópia de fórmulas sem alterar as referências, faz sentido usar uma macro para isso. Pressione o atalho de teclado Alt + F11 ou botão Visual Basic aba revelador (Desenvolvedor), insira um novo módulo através do menu Inserir - Módulo  e copie o texto dessa macro lá:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Selecionar células com fórmulas para copiar.", _ "Copiar fórmulas exatamente", Default:=Selection.Address, Type := 8) Se copyRange for Nothing Then Exit Sub Set pasteRange = Application.InputBox("Agora selecione o intervalo de colagem." & vbCrLf & vbCrLf & _ "O intervalo deve ser igual em tamanho ao original " & vbCrLf & _ " intervalo de células para copiar." , "Copiar fórmulas exatamente", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Copiar e colar intervalos variam em tamanho!", vbExclamation, "Erro de cópia" Exit Sub End If If pasteRange is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Você pode usar o botão para executar a macro. Macros aba revelador (Desenvolvedor — Macros) ou atalho de teclado Alt + F8. Após executar a macro, ela solicitará que você selecione o intervalo com as fórmulas originais e o intervalo de inserção e copiará as fórmulas automaticamente:

• Visualização conveniente de fórmulas e resultados ao mesmo tempo
• Por que o estilo de referência R1C1 é necessário nas fórmulas do Excel
• Como encontrar rapidamente todas as células com fórmulas
• Ferramenta para copiar fórmulas exatas do complemento PLEX