Aprenda a copiar fórmulas para várias células, para baixo de uma coluna, para células não adjacentes, copiar fórmulas com referências de células absolutas ou mistas, etc.
Copiar fórmulas é uma das tarefas mais comuns e fáceis que você realiza em uma planilha típica que depende principalmente de fórmulas. Em vez de digitar a mesma fórmula repetidamente no Excel, você pode simplesmente copiar e colar uma fórmula de uma célula para várias células.
Depois de escrever uma fórmula no Excel, você pode usar os comandos copiar e colar em várias células, várias células não adjacentes ou colunas inteiras. Se você não fizer isso direito, vai acabar com aqueles erros # REF e / DIV0 terríveis. Neste artigo, mostraremos os diferentes métodos que você pode usar para copiar fórmulas no Excel.
Como copiar e colar fórmulas no Excel
O Microsoft Excel fornece várias maneiras de copiar fórmulas com referências de células relativas, referências de células absolutas ou referências mistas.
- Copie a fórmula de uma célula para outra
- Copie a fórmula uma célula para várias células
- Copiando fórmula para a coluna inteira
- Copiando fórmula sem formatação
- Copie fórmulas para células não adjacentes
- Copie as fórmulas sem alterar as referências das células
Como copiar uma fórmula de uma célula para outra no Excel
Às vezes, você pode querer copiar uma fórmula de uma célula para outra no Excel para evitar redigitar a fórmula inteira novamente e economizar algum tempo ao fazer isso.
Digamos que temos esta tabela:
Existem alguns métodos para copiar a fórmula de uma célula para outra.
Primeiro, selecione a célula com a fórmula e clique com o botão direito e, no menu de contexto, selecione ‘Copiar’ para copiar a fórmula. Ou você pode usar a opção ‘Copiar’ na seção ‘Área de transferência’ da guia ‘Página inicial’.
Mas você também copia fórmulas simplesmente pressionando o atalho de teclado Ctrl + C. Este é um método mais eficiente e que economiza tempo.
Então vamos para a célula que queremos colar apertando o atalho Ctrl + V para colar a fórmula. Ou clique com o botão direito na célula que deseja colar e clique nas opções em ‘Opções de colagem’: simples opção ‘Colar (P)’ ou colar como opção ‘Fórmula (F)’.
Como alternativa, você também pode clicar em ‘Colar especial’ abaixo dos seis ícones de colagem para abrir a caixa de diálogo ‘Colar especial’. Aqui, você tem várias opções, incluindo as seis opções de colagem do menu de contexto. Selecione ‘Tudo’ ou ‘Fórmulas’ na seção Colar e clique em ‘OK’.
Agora, a célula com a fórmula colada deve ter as mesmas fórmulas (da célula copiada), mas com referências de célula diferentes. O endereço da célula é autoajustado pelo Excel para corresponder ao número da linha da célula colada.
Copie a fórmula de uma célula para várias células
A mesma operação de colagem funciona da mesma forma se selecionarmos várias células ou um intervalo de células.
Selecione a célula com a fórmula e pressione Ctrl + C para copiar a fórmula. Em seguida, selecione todas as células onde deseja colar a fórmula e pressione Ctrl + V para colar a fórmula ou use um dos métodos de colagem acima para colar a fórmula (como fizemos para a célula única).
Copiar a fórmula para uma coluna ou linha inteira
No Excel, você pode copiar rapidamente uma fórmula para uma coluna ou linha inteira.
Para copiar uma fórmula para uma coluna ou linha, primeiro, insira uma fórmula em uma célula. Em seguida, selecione a célula de fórmula (D1) e passe o cursor sobre um pequeno quadrado verde no canto inferior direito da célula. Conforme você passa o mouse, o cursor muda para um sinal de mais preto (+), que é chamado de alça de preenchimento. Clique e segure a alça de preenchimento e arraste-a em qualquer direção desejada (coluna ou linha) sobre as células para copiar a fórmula.
Quando você copia uma fórmula para um intervalo de células, as referências de célula da fórmula se ajustam automaticamente com base na localização relativa das linhas e colunas e a fórmula executa cálculos com base nos valores dessas referências de célula (veja abaixo).
No exemplo acima, quando a fórmula em D1 (= A1 * B1) / 2) copiada para a célula D2, a referência relativa muda de acordo com sua localização (= A2 * B2) / 2) e assim por diante.
Da mesma forma, você pode arrastar a fórmula para células adjacentes à esquerda, à direita ou para cima.
Outra maneira de copiar a fórmula para uma coluna inteira é clicando duas vezes na alça de preenchimento em vez de arrastá-la. Quando você clica duas vezes na alça de preenchimento, ela aplica imediatamente a fórmula, desde que haja dados, à célula adjacente.
Copiar uma fórmula para um intervalo sem copiar a formatação
Quando você copia uma fórmula para um intervalo de células com a alça de preenchimento, ela copia a formatação da célula de origem também, como cor da fonte ou cor de fundo, moeda, porcentagem, tempo, etc (conforme mostrado abaixo).
Para evitar a cópia da formatação da célula, arraste a alça de preenchimento e clique em ‘Opções de preenchimento automático’ no canto inferior direito da última célula. Em seguida, no menu suspenso, selecione ‘Preencher sem formatação’.
O resultado:
Copiar uma fórmula do Excel apenas com formatação de números
Se você deseja copiar a fórmula apenas com a fórmula e a formatação, como formato de porcentagem, casas decimais, etc.
Copie a fórmula e selecione todas as células para as quais deseja copiar a fórmula. Na guia ‘Página inicial’, clique na seta abaixo do botão ‘Colar’ na faixa. Em seguida, clique no ícone "Fórmulas e formatação de números" (o ícone com% fx) no menu suspenso para colar apenas a fórmula e a formatação de números.
Esta opção copia apenas fórmulas e formatação de números, mas ignora todas as outras formatações de células, como cor de fundo, cor da fonte, etc.
Copie uma fórmula para células não adjacentes / não contíguas
Se você deseja copiar uma fórmula para células não adjacentes ou intervalos não adjacentes, você pode fazer isso com a ajuda de Ctrl chave.
Selecione a célula com a fórmula e pressione Ctrl + C para copiá-lo. Em seguida, selecione células / intervalos não adjacentes enquanto pressiona e segura o Ctrl chave. Então aperte Ctrl + V para colar a fórmula e clicar Entrar completar.
Copiar fórmulas sem alterar referências de células no Excel
Quando uma fórmula é copiada para outra célula, o Excel altera automaticamente as referências da célula para corresponder ao seu novo local. Essas referências de célula usam a localização relativa de um endereço de célula, portanto, são chamadas de referência de célula relativa (sem $). Por exemplo, se você tiver a fórmula ‘= A1 * B1’ na célula C1 e copiar esta fórmula para a célula C2, a fórmula mudará para ‘= A2 * B2’. Todos os métodos que discutimos acima usam referências relativas.
Quando você copia uma fórmula com referências de células relativas, ela altera automaticamente as referências para que a fórmula se refira às linhas e colunas correspondentes. Se você usar referências absolutas em uma fórmula, a mesma fórmula será copiada sem alterar as referências de célula.
Quando você coloca um cifrão ($) na frente da letra da coluna e do número da linha de uma célula (por exemplo $ A $ 1), ele transforma a célula em uma célula absoluta. Agora, não importa onde você copie a fórmula que contém a referência de célula absoluta, a fórmula nunca o fará. Mas se você tiver referência de célula relativa ou mista em uma fórmula, use qualquer um dos seguintes métodos para copiar sem alterar as referências de célula.
Copiar fórmula com referência de célula absoluta usando o método Copiar-Colar
Ocasionalmente, você pode precisar copiar / aplicar a fórmula exata para baixo na coluna, sem alterar as referências das células. Se você deseja copiar ou mover uma fórmula exata com referência absoluta, faça o seguinte:
Primeiro, selecione a célula com a fórmula que deseja copiar. Em seguida, clique na barra de fórmulas, selecione a fórmula com o mouse e pressione Ctrl + C para copiá-lo. Se você quiser mover a fórmula, pressione Ctrl + X para cortá-lo. Em seguida, acerte o Esc para sair da barra de fórmulas.
Como alternativa, selecione a célula com a fórmula e pressione F2 ou clique duas vezes na célula. Isso colocará a célula selecionada no modo de edição. Em seguida, selecione a fórmula na célula e pressione Ctrl + C para copiar a fórmula na célula como texto.
Em seguida, selecione a célula de destino e pressione Ctrl + V para colar a fórmula.
Agora, a fórmula exata é copiada para a célula de destino sem nenhuma alteração de referência de célula.
Copiar fórmulas com referências de células absolutas ou mistas
Se desejar mover ou copiar fórmulas exatas sem alterar as referências de células, você deve alterar as referências relativas às células para referências absolutas. Por exemplo, adicionar o sinal ($) à referência de célula relativa (B1) o torna uma referência absoluta ($ B $ 1), portanto, permanece estático, independentemente de onde a fórmula é copiada ou movida.
Mas às vezes, você pode precisar usar referências de células mistas ($ B1 ou B $ 1) adicionando um cifrão ($) na frente da letra da coluna ou o número da linha para bloquear uma linha ou coluna no lugar.
Deixe-nos explicar com um exemplo. Suponha que você tenha esta tabela que calcula a economia mensal subtraindo o aluguel (B9) dos ganhos (na coluna B) a cada mês.
No exemplo abaixo, a fórmula usa uma referência de célula absoluta ($ B $ 9) para bloquear o valor do aluguel para a célula B9 e uma referência de célula relativa para a célula B2 porque ela precisa ser ajustada para cada linha para corresponder a cada mês. B9 torna-se referência de célula absoluta ($ B $ 9) porque você deseja subtrair o mesmo valor do aluguel dos ganhos de cada mês.
Digamos que você queira mover os saldos da coluna C para a coluna E. Se você copiar a fórmula (pelo método usual de copiar / colar) da célula C2 (= B2- $ B $ 9) mudará para = D2- $ B $ 9 quando colada na célula E2, fazendo seus cálculos todos errados!
Nesse caso, altere a referência de célula relativa (B2) para uma referência de célula mista ($ B2) adicionando o sinal '$' na frente da letra da coluna da fórmula inserida na célula C2.
E agora, se você copiar ou mover a fórmula da célula C2 para E2, ou qualquer outra célula, e aplicar a fórmula para baixo na coluna, a referência da coluna permanecerá a mesma enquanto o número da linha será ajustado para cada célula.
Copiar e colar fórmulas do Excel sem alterar as referências usando o Bloco de notas
Você pode ver todas as fórmulas em sua planilha do Excel usando as opções Mostrar fórmula. Para fazer isso, vá para a guia ‘Fórmulas’ e selecione ‘Mostrar fórmulas’.
Alternativamente, você pode entrar no modo de visualização de fórmula pressionando o Ctrl + ` atalho, que exibe todas as fórmulas em sua planilha. Você pode encontrar a tecla de acento grave (`) no canto superior esquerdo do teclado na fileira com as teclas numéricas (abaixo do ESC e antes da tecla número 1).
Selecione todas as células com as fórmulas que deseja copiar e pressione Ctrl + C para copiá-los, ou Ctrl + X para cortá-los. Em seguida, abra o bloco de notas e pressione Ctrl + V para colar as fórmulas no bloco de notas.
A seguir, selecione a fórmula e copie (Ctrl + C) do bloco de notas e cole-o (Ctrl + V) na célula para a qual deseja que a fórmula exata seja copiada. Você pode copiar e colar um por um ou todos de uma vez.
Depois de colar as fórmulas, desative o modo de visualização da fórmula pressionando Ctrl + ` ou vá novamente para ‘Fórmulas’ -> ‘Mostrar fórmulas’.
Copie as fórmulas exatas usando Find and Replace do Excel
Se você deseja copiar um intervalo de fórmulas exatas, também pode usar a ferramenta Localizar e substituir do Excel para fazer isso.
Selecione todas as células que contêm as fórmulas que você deseja copiar. Em seguida, vá para a guia ‘Página inicial’, clique em ‘Localizar e selecionar’ no grupo Edição e selecione a opção ‘Substituir’ ou simplesmente pressione Ctrl + H para abrir a caixa de diálogo Localizar e substituir.
Na caixa de diálogo Localizar e substituir, insira o sinal de igual (=) no campo ‘Encontrar o que’. No campo ‘Substituir por’, insira um símbolo ou caractere que ainda não faça parte de suas fórmulas, como #, ou etc. Em seguida, clique no botão ‘Substituir tudo’.
Você receberá uma caixa de mensagem dizendo "Fizemos 6 substituições" (porque selecionamos 6 células com fórmulas). Em seguida, clique em ‘OK’ e ‘Fechar’ para fechar as duas caixas de diálogo. Isso substitui todos os sinais de igual a (=) por sinais hash (#) e transforma fórmulas em strings de texto. Agora, as referências de células das fórmulas não serão alteradas quando copiadas.
Agora, você pode selecionar essas células, pressione Ctrl + C para copiá-los e colá-los nas células de destino com Ctrl + V.
Finalmente, você precisa mudar os sinais (#) de volta para sinais (=). Para fazer isso, selecione ambos os intervalos (intervalo original e copiado) e pressione Ctrl + H para abrir a caixa de diálogo Localizar e substituir. Desta vez, digite o sinal de hash (#) no campo ‘Encontrar o que’ e igual a (=) no campo ‘Substituir por’ e clique no botão ‘Substituir tudo’. Clique em ‘Fechar’ para fechar a caixa de diálogo.
Agora, as strings de texto são convertidas de volta para as fórmulas e você obterá este resultado:
Feito!