Copiar fórmulas Excel com uma célula fixa

Resolvido

mendes - Atualizado em 20 dez 2018 às 04:05
 Samuel - 7 mar 2020 às 18:03

Bom dia, Como faço para manter cópia automática de uma célula fixa no Excel para multiplicação com diversas células? Ex. a célula C7 vai ser predominante para multiplicação com d3, d4, d5, d6....

Grato

Melhor resposta

aprovada por Ana Spadari e Ana Spadari

FlaSP
Atualizado em 21 dez 2018 às 09:45

Olá Utilize o simbolo $ para fixar uma célula Exemplo =soma(a1*$f$4) se quiser fixar só a linha A$1 se quiser fixar só a coluna $A1 para fixar tudo $A$1

Você pode encontrar mais conteúdo sobre os recursos e fórmulas do Excel, nesta página do CCM.

Abcs

Para a célula ficar fixada de maneira correta vamos utilizar o atalho do teclado F4 em nossa célula fixa que seria o H2. Portanto perceba abaixo como o desconto será aplicado em todas as células, e também como ficará a apresentação da célula bloqueada.

Como deixar uma célula constante no Excel?

Abra sua planilha no Microsoft Excel. Dê um duplo clique na célula que você deseja manter constante ao copiar para outro local . Adicione um cifrão ( $ ) antes de cada número de linha e letra da coluna nas referências para torná-los absoluta.

Como funciona a fixação de célula?

• A fixação de célula é um recurso simples, no entanto, muito poderoso. Vejamos abaixo como funciona. Planilha da aula disponível para download. No primeiro momento vamos utilizar o preenchimento automático com células relativas. Tomando como exemplo temos uma planilha que contém dados sobre o pagamento de uma empresa.

Por que usamos a célula Excel?

• Em primeiro lugar, precisamos intender o por que usamos a ferramenta de travar célula no excel, e quando precisamos utilizar. Com isso, separamos algumas vantagens de utilizar essa ferramenta, confira. Em primeiro lugar, suas principais vantagens são muitas, pois trazem ganhos reais no que se diz respeito a tempo.

Como usar o preenchimento automático da célula fixa?

• No entanto, nesse caso utilizamos o preenchimento automático abaixo, onde você pode notar que o desconto não foi aplicado em nenhuma célula. Para a célula ficar fixada de maneira correta vamos utilizar o atalho do teclado F4 em nossa célula fixa que seria o H2.

Como corrigir a lógica do Excel?

• Calma, isso foi só para exemplificar e você entender a lógica do Excel. Para corrigir o problema clique duas vezes na célula F4, selecione H4 e pressione a tecla F4 do seu teclado para fixar células: Sua fórmula ficará assim: =E4+$H$4. Pressione Enter.

Postado em em 27 de agosto de 2020

Se você usa o Excel regularmente ou começou a utilizá-lo há pouco tempo você vai ver o quanto travar célula no Excel pode te ajudar!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou clique aqui!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

O que é o Trancamento no Excel?

O trancamento no Excel é uma ferramenta que podemos utilizar para facilitar a utilização de determinadas fórmulas, ou seja, auxiliar o usuário para que não seja necessário escrever uma mesma fórmula diversas vezes sem necessidade.

Quando Travar Célula no Excel?

O trancamento será utilizado sempre que o usuário tiver que replicar uma fórmula que segue o mesmo padrão, seja multiplicação, soma, subtração ou qualquer tipo, mas evitando com que ele tenha que escrever a mesma fórmula diversas vezes. Desta forma é possível manter uma linha, coluna ou linha e coluna fixas para que ao replicar a fórmula ela mantenha as informações corretas.

Como Travar uma Célula no Excel?

Nesta aula vamos aprender como efetuar o trancamento no Excel. É uma forma bem simples, no entanto muitas pessoas acabam se confundindo e até deixando de utilizar o trancamento o que gera alguns erros nas fórmulas.

Temos dois tipos de trancamento, o trancamento total que faz o congelamento tanto de linha e de coluna e temos também o trancamento parcial que faz o congelamento de linha ou coluna.

Vamos iniciar pelo trancamento total, ou seja, quando fixamos tanto a linha quanto a coluna de uma célula. Isso quer dizer que ao fixar a linha e coluna a célula vai permanecer a mesma ao arrastar uma fórmula (ou copiar e colar).

Desta forma o Excel não vai “conseguir” alterar essa referência, pois ela foi fixada em linha e coluna.

Planilha de bônus dos vendedores

Neste primeiro caso vamos calcular o bônus de cada um dos vendedores. É uma fórmula simples onde faremos a multiplicação do valor de venda de cada um deles pelo percentual que está na célula G7.

Fórmula para o cálculo do bônus

Inicialmente o que as pessoas iriam fazer logo de cara é simplesmente multiplicar, o que não está errado, no entanto repare que ao dar um clique duplo no quadrado inferior direito dessa célula o Excel vai replicar essa fórmula para as demais células.

Resultado da fórmula sem o trancamento

É possível observar que somente o primeiro vendedor ficou com as informações corretas. Isso acontece porque quando arrastamos ou copiamos e colamos uma fórmula dentro do Excel que possua uma referência o Excel vai “mover” aquela referência de acordo onde estamos inserindo essa informação.

Então para a segunda linha teremos um deslocamento de C8*G7 para C9*G8 porque descemos uma linha, então teremos o aumento de uma linha dentro da referência.

Verificando a fórmula da linha logo abaixo

A referência de vendas realmente precisa sempre se mover em uma linha, pois se trata de um novo vendedor, mas veja que a referência do bônus foi alterada e com isso a fórmula passa a multiplicar o valor da venda por 0, pois não temos informações nessa célula.

Então o que temos que fazer para corrigir esse problema é trancar a referência G7 antes de replicar a fórmula para as demais células.

Trancamento total na referência G7

Para isso o usuário terá que editar a fórmula utilizando a tecla F2. Em seguida basta clicar em cima da referência que deseja alterar e pressionar a tecla F4. Essa tecla é para inserir automaticamente o símbolo de $ antes da coluna e antes da linha, com isso o Excel entende que aquela referência está trancada.

OBS: Caso o usuário pressione F4 mais de uma vez o Excel vai alterar para o trancamento somente em linha e depois somente em coluna.

Agora sim o usuário será capaz de replicar a fórmula para as demais células, pois agora o programa entende que ao andar uma linha ou uma coluna a referência não terá nenhuma alteração, pois foi feito o trancamento total dela.

Verificando as outras células (forma correta)

É possível observar que agora todos os resultados estão corretos e qualquer célula desse intervalo vai fazer referência a venda daquela linha, mas fará referência a célula G7 que possui o valor de bônus.

Para o segundo caso vamos ver um exemplo de trancamento de linha, ou seja, a linha da referência ficará fixa, mas a coluna poderá ser modificada quando o usuário replica a fórmula para diferentes colunas.

Planilha para o trancamento em linha

Nesse exemplo temos o mesmo cálculo de bônus, no entanto queremos calcular para cada um dos vendedores em diferentes meses do ano. Então para evitar com que o usuário tenha que escrever uma fórmula para cada mês e depois replicar para os demais funcionários, vamos escrever apenas uma e replicar para toda a tabela.

Para fazer isso aprendemos no exemplo anterior que o trancamento total ele fixa uma célula para que não seja movimentada em linhas ou em colunas, neste caso vamos efetuar o trancamento de linha, pois as informações que temos de bônus estão na linha 9 da tabela, ou seja, ao replicar a fórmula para os vendedores (que estão em diferentes linhas) não queremos que o percentual de bônus mude.

Então vamos até a fórmula de bônus, clicar na referência do primeiro bônus que se encontra na célula D9 e apertar F4 duas vezes, ou escrever manualmente o $ antes do número da linha.

Fórmula para o cálculo de bônus com trancamento

Feito isso o usuário poderá clicar no quadrado verde que fica no canto inferior direito da célula e arrastar para baixo para preencher o bônus de cada um dos vendedores naquele mês.

Replicando a fórmula para as demais células

Feito isso ainda com essa seleção o usuário pode repetir o procedimento, mas agora arrastar até o último mês. Assim teremos toda a tabela preenchida de forma muito rápida e eficiente.

Replicando a fórmula para o restante da tabela

É possível entrar para editar qualquer fórmula para que o usuário verifique se as informações estão corretas.

Verificando se a fórmula está correta

Neste caso estamos no mês de fevereiro e pegando o bônus do vendedor Uelliton. Como podemos observar a fórmula está de fato pegando o valor de venda desse vendedor exatamente do mês indicado.

Então ao invés de escrever diversas fórmulas uma a uma bastou escrever uma única fórmula com o trancamento parcial e replicá-la para as demais células.

Para o último caso temos o trancamento parcial de colunas, ou seja, nesse caso temos algumas metas para os anos de 2015 a 2019, mas queremos dividir essa meta em partes iguais para completar a tabela e saber quanto teremos de meta em cada um dos meses.

Planilha para o trancamento em coluna

Para simplificar o exemplo vamos de fato fazer uma divisão igual para todos os meses, ou seja, vamos pegar o valor da meta e dividir pela quantidade de meses.

Fórmula para a média mensal

A fórmula é bem simples, no entanto queremos que a meta seja diferente para cada linha, pois são anos diferentes, desta forma não podemos trancar as linhas, pois queremos que ao alterar a linha a informação também seja alterada.

Por outro lado, ao replicar a fórmula para a direita para preencher os outros meses não queremos que o valor da meta pegue a coluna vazia, depois pegue a coluna de janeiro, fevereiro e assim por diante.

Se isso acontecer vamos ter somente o primeiro mês com as informações corretas e os demais com as informações erradas. Então para resolver o problema vamos efetuar o trancamento de coluna, desta forma ao replicar a fórmula para as outras colunas a coluna D ficará fixa.

Fixando a coluna D

Neste caso o usuário pode pressionar a tecla F4 3 vezes, ou escrever manualmente o símbolo $ logo antes da letra que indica a coluna. Esse é um atalho indispensável quando o assunto é travar célula no Excel.

Feito isso basta repetir o procedimento de clicar e arrastar no quadrado verde que fica no canto inferior direito da célula.

Replicando a fórmula para toda a tabela

Assim é possível notar que os valores estão divididos igualmente, pois todos estão pegando a referência correta que está na coluna D.

Verificando se a fórmula está correta

Nesta aula aprendemos como travar célula no excel e a facilidade que ele traz até mesmo em atividades simples, pois evita com que o usuário tenha um trabalho muito grande tendo que reescrever uma mesma fórmula diversas vezes e ainda faz com que o usuário evite erros durante esse procedimento.

Então ao utilizar o trancamento o usuário garante que as informações das outras células estarão corretas e diminui o trabalho que teria para preencher todas elas.

Hashtag Treinamentos

Para outras publicações de Excel Básico, clique aqui!

Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?

Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.