Quer aprender como trancar células no Excel para replicar suas fórmulas de forma correta dentro do Excel? Eu vou te mostrar como fazer isso!
Caso prefira essa explicação em vídeo pode assistir o vídeo abaixo ou acessar o nosso Canal do YouTube para mais vídeos!
Para baixar a planilha utilizada nessa aula, preencha seu e-mail abaixo:
Trancamento é uma forma que temos dentro do Excel para travar as referências, seja por coluna, linha ou coluna e linha. Isso quer dizer que, ao trancarmos utilizando alguma dessas opções, podemos deixa-las fixas, facilitando na hora de expandir uma fórmula.
Vamos utilizar essa opção quando precisamos trancar uma referência para quando copiarmos/arrastarmos/expandirmos a fórmula ela fique com o dado que queremos. Isso é muito importante, pois algumas vezes temos uma fórmula e ao arrastar o Excel automaticamente já muda a referência para acompanhar, no entanto dependendo o que esteja fazendo isso pode vir a ser um problema.
Então para resolver esse problema temos a opção de trancamento das referências, evitando assim problemas na hora de arrastar uma fórmula que tenha uma referência que precise ser fixa.
O trancamento é bem simples de ser utilizado, basta colocar o símbolo $ antes da coluna ou linha que deseja trancar na hora de fazer a referência, ou utilizando a tecla F4. Vamos utilizar 4 exemplos para demonstrar o que muda em cada um deles, ou seja, vamos observar o que acontece ao arrastarmos as fórmulas entre linhas e colunas.
Essa será a planilha utilizada para demonstrar cada um dos trancamentos, veja que para exemplificar temos algumas referências das 3 primeiras colunas e linhas.
Na parte à direita temos a classificação de cada um para verificar o que acontece ao arrastarmos a fórmula da primeira célula para as duas direções.
As células inicialmente coloridas possuem exatamente a referência mostrada na descrição, então estão todas se referindo a célula A1, no entanto com diferentes tipos de trancamento.
Para expandir a fórmula vamos selecionar cada uma das células coloridas, clicar no canto inferior direito da célula (terá um quadrado verde) e vamos arrastar para baixo e em seguida para a direita.
Vamos as análises de cada um dos trancamentos.
Normal – Aqui vamos ter exatamente as referências que temos nas 3 primeiras colunas, pois o Excel ao arrastar uma fórmula ele move a referência de acordo com a movimentação que o usuário está fazendo para acompanhar a fórmula. Isso é muito útil para que o usuário não tenha que ficar reescrevendo fórmulas quando possui várias células com a mesma fórmula mudando apenas a referência.
Coluna – Nesta parte temos o $ antes da letra, pois estamos trancando a coluna indicada. Feito isso é possível perceber que ao arrastar a fórmula para baixo o Excel responde normalmente, no entanto ao arrastar a fórmula para a direita (o que iria acarretar a mudança de coluna) o Excel não permite justamente pelo trancamento. Então ele irá manter a referência que é a coluna A.
Linha – Neste caso temos o $ antes do número, pois agora estamos trancando a linha indicada. Feito isso é possível perceber que temos o oposto do exemplo anterior. Então o Excel irá responder normalmente ao arrastar para a direita, no entanto para baixo teremos os mesmos valores, pois a linha da referência deve se manter, e essa é a linha 1.
Coluna e Linha – Este caso é a junção dos dois trancamentos, ou seja, vamos trancar tanto a linha quanto a coluna, portanto nos sobra apenas uma única célula. Isso quer dizer que ao trancar coluna e linha vamos estar travando completamente aquela referência e quando arrastar a fórmula essa referência ficará fixa. Isso é muito utilizado quando precisamos referenciar um valor, como por exemplo de total para calcular porcentagem, mas não queremos que ele mude ao copiar a fórmula para as outras diversas células da tabela.
A utilização do trancamento é muito comum dentro do Excel, pois permite que o usuário continue utilizando a mesma fórmula, mas sem que tenha que alterar sua fórmula manualmente para que atenda suas necessidades.
Não há uma atividade em específico que vamos utilizar o trancamento, isso vai depender muito da atividade que o usuário esteja executando. No exemplo de cálculo da porcentagem é necessário o trancamento da referência de total, pois ao arrastar a célula o Excel não irá pegar dados de outras células para assumir como total.
Por isso é importante a utilização do trancamento, pois nesse caso se o usuário não trancar a referência de total pode ser que o Excel pegue valores impróprios e retorne com um resultado errado, o que não é bom para o usuário.
Você já viu como fazer o trancamento com um exemplo mais simples, só que é importante que você entenda como isso funciona dentro do Excel em uma atividade do dia a dia.
Por esse motivo vou te mostrar um exemplo prático com 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.
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.
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.
É 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.
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.
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.
É 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.
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.
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.
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.
É possível entrar para editar qualquer fórmula para que o usuário verifique se as informações estão corretas.
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.
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.
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.
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.
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.
Nesta eu te mostrei como travar celula excel e a facilidade que o trancamento 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, diminui o trabalho que teria para preencher todas elas e ainda evita erros!
Para acessar outras publicações de Excel Básico, clique aqui!
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.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador