Copiar uma fórmula de uma planilha do Excel para outra geralmente resulta em referências quebradas ou cálculos incorretos. Isso acontece porque a referência relativa padrão do Excel muda com base no novo local. Você precisa controlar como as referências de células se ajustam durante a operação de cópia. Este artigo explica os métodos para copiar fórmulas preservando sua lógica original.
Principais Conclusões: Copiar Fórmulas Entre Planilhas
- Referências absolutas com $: Trava uma coluna, linha ou endereço de célula inteiro para que não mude ao ser copiado.
- Copiar e Colar Especial > Fórmulas: Cola apenas o texto da fórmula, o que pode evitar alguns deslocamentos de referência.
- Localizar e Substituir com nomes de planilhas: Converte referências relativas em referências explícitas de planilha antes de copiar.
Entendendo os Tipos de Referência de Célula do Excel
O Excel usa três tipos principais de referências de célula: relativa, absoluta e mista. Uma referência relativa como A1 muda quando você a copia para outra célula. O Excel ajusta a letra da coluna e o número da linha com base na direção e distância do movimento. Uma referência absoluta como $A$1 usa cifrões para travar tanto a coluna quanto a linha. Essa referência permanece fixa na célula A1, não importa para onde você copie a fórmula.
Uma referência mista trava apenas uma parte do endereço, como $A1 ou A$1. O cifrão antes da letra da coluna trava a coluna. O cifrão antes do número da linha trava a linha. O comportamento dessas referências é consistente dentro da mesma pasta de trabalho. No entanto, copiar uma fórmula para uma planilha diferente introduz o nome da planilha na referência. Uma fórmula com uma referência relativa a B5 se torna =Planilha2!B5 quando colada em outra planilha, se Planilha2 for a origem.
Como os Nomes das Planilhas Afetam as Referências
Quando você copia uma fórmula dentro da mesma planilha, as referências se ajustam em relação à nova posição. Copiar para uma planilha diferente adiciona o nome da planilha de origem à referência. Por exemplo, copiar =A1+B1 da Planilha1 para a célula C5 na Planilha2 resulta em =Planilha1!A1+Planilha1!B1. Essa referência explícita de planilha impede que a fórmula procure células em sua nova planilha. A fórmula ainda aponta para as células originais da Planilha1. Isso geralmente é o resultado desejado ao consolidar dados.
Métodos para Copiar Fórmulas Entre Planilhas
Use os seguintes métodos para copiar fórmulas enquanto controla como as referências de células são atualizadas. O melhor método depende se você deseja que as referências apontem para a planilha original ou se ajustem à nova.
Método 1: Usar Referências Absolutas Antes de Copiar
- Edite a fórmula original
Selecione a célula que contém a fórmula que deseja copiar. Clique na barra de fórmulas ou pressione F2 para editar. - Aplique cifrões para travar referências
Coloque um cifrão antes da letra da coluna e do número da linha para qualquer referência que não deva mudar. Por exemplo, altere =SOMA(B2:B10) para =SOMA($B$2:$B$10). - Copie a fórmula
Selecione a célula e pressione Ctrl+C ou clique com o botão direito e escolha Copiar. - Cole na nova planilha
Navegue até a planilha de destino, selecione a célula alvo e pressione Ctrl+V. As referências absolutas apontarão para as mesmas células exatas na planilha original.
Método 2: Copiar e Colar Usando Colar Especial
- Copie a célula de origem
Selecione a célula com a fórmula e copie-a com Ctrl+C. - Abra Colar Especial na planilha de destino
Clique com o botão direito na célula alvo na nova planilha. No menu de contexto, passe o mouse sobre Colar Especial e clique na opção Colar Especial na parte inferior. - Selecione a opção Fórmulas
Na caixa de diálogo Colar Especial, selecione o botão de opção Fórmulas. Clique em OK. Isso cola o texto da fórmula sem alterar sua formatação. - Verifique as referências
Confira a fórmula colada. Ela conterá o nome da planilha original para todas as referências, como =Planilha1!A1+Planilha1!B1. Este método cria efetivamente referências absolutas para a planilha de origem.
Método 3: Usar Localizar e Substituir para Adicionar Nomes de Planilhas
- Selecione as células de fórmula na planilha de origem
Destaque o intervalo que contém as fórmulas que você pretende copiar. - Abra a caixa de diálogo Localizar e Substituir
Pressione Ctrl+H. Isso abre a caixa de diálogo Localizar e Substituir com a guia Substituir ativa. - Converta referências para incluir o nome da planilha
No campo Localizar, digite um sinal de igual =. No campo Substituir por, digite =’Planilha1′! onde ‘Planilha1′ é o nome real da sua planilha. Clique em Substituir Tudo. Isso altera =A1 para =’Planilha1’!A1. - Copie e cole as fórmulas modificadas
Agora copie as células e cole-as normalmente na nova planilha. As fórmulas farão referência explícita à planilha original.
Erros Comuns e Limitações
Evite estes erros ao transferir fórmulas entre planilhas para prevenir erros de cálculo.
Copiar Fórmulas Que Referenciam Outras Planilhas
Uma fórmula como =Planilha2!A1+Planilha3!B2 já contém referências de planilha. Copiar isso da Planilha1 para a Planilha4 manterá as referências para Planilha2 e Planilha3. Isso geralmente está correto. O problema ocorre se você renomear Planilha2 posteriormente. Todas as fórmulas que a referenciam mostrarão um erro #REF!. Sempre atualize os nomes das planilhas nas fórmulas após renomear uma planilha.
Usar Referências Relativas para Tabelas Estruturadas
Referências de Tabela do Excel como =SOMA(Tabela1[Vendas]) são estruturadas e não usam nomes de planilhas. Copiar uma fórmula com uma referência de Tabela para outra planilha ainda aponta para a mesma Tabela. Se a Tabela existir apenas na planilha de origem, a fórmula funciona. Se você precisar de uma fórmula semelhante para uma Tabela diferente na nova planilha, deve editar o nome da Tabela manualmente.
Esquecer dos Intervalos Nomeados
Intervalos nomeados definidos no nível da pasta de trabalho funcionam em qualquer planilha. Uma fórmula usando um nome como =SOMA(ReceitaProjetada) se referirá ao mesmo intervalo em todos os lugares. Se o intervalo nomeado tiver escopo em uma planilha específica, copiar a fórmula para outra planilha pode causar um erro #NOME?. Verifique o escopo dos intervalos nomeados no Gerenciador de Nomes, na guia Fórmulas.
Comparação de Métodos de Referência
| Item | Referência Relativa (A1) | Referência Absoluta ($A$1) | Referência Explícita de Planilha (Planilha1!A1) |
|---|---|---|---|
| Comportamento ao copiar na mesma planilha | Ajusta com base no novo local | Permanece fixa na célula original | Normalmente não usado dentro de uma única planilha |
| Comportamento ao copiar para uma planilha diferente | Ganha o nome da planilha de origem, torna-se Planilha1!A1 | Mantém os $ e ganha o nome da planilha de origem | Permanece exatamente igual, apontando para a planilha nomeada |
| Melhor caso de uso | Criar padrões como um total acumulado em uma coluna | Vincular a uma célula de entrada fixa, como uma taxa de imposto ou custo unitário | Criar planilhas de resumo que buscam dados de guias de origem específicas |
| Edição necessária antes de copiar | Nenhuma | Deve adicionar $ manualmente ou pressionar F4 | Deve adicionar o nome da planilha digitando ou usando Localizar e Substituir |
Agora você pode copiar fórmulas entre planilhas mantendo as referências de células intactas. Use referências absolutas com a tecla F4 para alternar rapidamente os tipos de referência. Para controle avançado, use a opção Colar Especial > Fórmulas para duplicar a lógica da fórmula exatamente. Lembre-se de que referências estruturadas para Tabelas do Excel se comportam de forma diferente e mantêm sua conexão de origem automaticamente.