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 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.
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.
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.
Para outras publicações de Excel Básico, clique aqui!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?
Quer participar do maior evento de Power BI da América Latina? Só preencher seu e-mail abaixo que sua vaga estará garantida!