Como congelar uma célula em uma fórmula do Excel

Muitas vezes, os usuários se deparam com a necessidade de fixar uma célula em uma fórmula. Por exemplo, ocorre em situações em que você deseja copiar uma fórmula, mas para que o link não se mova para cima e para baixo no mesmo número de células que foi copiado de seu local original.

Nesse caso, você pode corrigir a referência de célula no Excel. E isso pode ser feito de várias maneiras ao mesmo tempo. Vamos dar uma olhada em como atingir esse objetivo.

O que é um link do Excel

A folha é composta de células. Cada um deles contém informações específicas. Outras células podem usá-lo em cálculos. Mas como eles entendem de onde obter os dados? Isso os ajuda a fazer links.

Cada link designa uma célula com uma letra e um número. Uma letra representa uma coluna e um número representa uma linha. 

Existem três tipos de links: absolutos, relativos e mistos. O segundo é definido por padrão. Uma referência absoluta é aquela que tem um endereço fixo de uma coluna e uma coluna. Assim, misto é aquele em que uma coluna ou uma linha separada é fixa.

O 1 método

Para salvar os endereços de coluna e linha, siga estas etapas:

1. Clique na célula que contém a fórmula.
2. Clique na barra de fórmulas para a célula que precisamos.
3. Pressione F4.

Como consequência, a referência da célula mudará para absoluta. Ele pode ser reconhecido pelo sinal de dólar característico. Por exemplo, se você clicar na célula B2 e depois clicar em F4, o link ficará assim: $B$2.

O que significa o cifrão antes da parte do endereço por célula?

1. Se for colocado na frente de uma letra, indica que a referência da coluna permanece a mesma, não importa para onde a fórmula foi movida.
2. Se o cifrão estiver na frente do número, isso indica que a string está fixada. 

O 2 método

Este método é quase o mesmo que o anterior, só que você precisa pressionar F4 duas vezes. por exemplo, se tivéssemos a célula B2, depois disso ela se tornaria B$2. Em palavras simples, desta forma conseguimos consertar a linha. Nesse caso, a letra da coluna será alterada.

É muito conveniente, por exemplo, em tabelas onde você precisa exibir o conteúdo da segunda célula de cima para baixo na célula. Em vez de fazer essa fórmula muitas vezes, basta fixar a linha e deixar a coluna mudar.

O 3 método

Isso é completamente igual ao método anterior, só que você precisa pressionar a tecla F4 três vezes. Então, apenas a referência à coluna será absoluta e a linha permanecerá fixa.

O 4 método

Suponha que tenhamos uma referência absoluta a uma célula, mas aqui foi necessário torná-la relativa. Para fazer isso, pressione a tecla F4 tantas vezes que não haja nenhum sinal de $ no link. Em seguida, ele se tornará relativo e, quando você mover ou copiar a fórmula, o endereço da coluna e o endereço da linha serão alterados.

Fixando células para um grande intervalo

Vemos que os métodos acima não apresentam nenhuma dificuldade de execução. Mas as tarefas são específicas. E, por exemplo, o que fazer se tivermos várias dezenas de fórmulas ao mesmo tempo, cujos links precisam ser transformados em absolutos. 

Infelizmente, os métodos padrão do Excel não atingirão esse objetivo. Para fazer isso, você precisa usar um addon especial chamado VBA-Excel. Ele contém muitos recursos adicionais que permitem que você execute tarefas comuns com o Excel muito mais rapidamente.

Inclui mais de uma centena de funções definidas pelo usuário e 25 macros diferentes, e também é atualizado regularmente. Ele permite que você melhore o trabalho com quase qualquer aspecto:

1. Células.
2. Macro.
3. Funções de vários tipos.
4. Links e matrizes.

Em particular, este suplemento permite corrigir links em um grande número de fórmulas de uma só vez. Para fazer isso, você deve executar as seguintes etapas:

1. Selecione um intervalo.
2. Abra a guia VBA-Excel que aparecerá após a instalação. 
3. Abra o menu “Funções”, onde está localizada a opção “Bloquear fórmulas”.
   

   

4. Em seguida, aparecerá uma caixa de diálogo na qual você precisa especificar o parâmetro necessário. Este addon permite fixar uma coluna e uma coluna separadamente, em conjunto, e também remover uma fixação já existente com um pacote. Depois que o parâmetro necessário for selecionado usando o botão de opção correspondente, você precisa confirmar suas ações clicando em “OK”.

Exemplo

Vamos dar um exemplo para ficar mais claro. Digamos que temos informações que descrevem o custo das mercadorias, sua quantidade total e receita de vendas. E nos deparamos com a tarefa de fazer com que a tabela, com base na quantidade e no custo, determine automaticamente quanto dinheiro conseguimos ganhar sem deduzir as perdas.

Em nosso exemplo, para isso, você precisa inserir a fórmula =B2*C2. É bem simples, como você pode ver. É muito fácil usar o exemplo dela para descrever como você pode corrigir o endereço de uma célula ou sua coluna ou linha individual. 

Claro, neste exemplo, você pode tentar arrastar a fórmula para baixo usando o marcador de preenchimento automático, mas neste caso, as células serão alteradas automaticamente. Assim, na célula D3 haverá outra fórmula, onde os números serão substituídos, respectivamente, por 3. Além disso, de acordo com o esquema – D4 – a fórmula terá a forma = B4 * C4, D5 – de forma semelhante, mas com o número 5 e assim por diante.

Se for necessário (na maioria dos casos, acontece), não há problemas. Mas se você precisar corrigir a fórmula em uma célula para que ela não mude ao arrastar, isso será um pouco mais difícil. 

Suponha que precisamos determinar a receita em dólares. Vamos colocá-lo na célula B7. Vamos ficar um pouco nostálgicos e indicar o custo de 35 rublos por dólar. Assim, para determinar a receita em dólares, é necessário dividir o valor em rublos pela taxa de câmbio do dólar.

Aqui está o que parece em nosso exemplo.

Se nós, de forma semelhante à versão anterior, tentarmos prescrever uma fórmula, falharemos. Da mesma forma, a fórmula mudará para a apropriada. No nosso exemplo, ficará assim: =E3*B8. Daqui podemos ver. que a primeira parte da fórmula se transformou em E3, e nos propusemos a essa tarefa, mas não precisamos alterar a segunda parte da fórmula para B8. Portanto, precisamos transformar a referência em uma referência absoluta. Você pode fazer isso sem pressionar a tecla F4, apenas colocando um cifrão.

Depois que transformamos a referência à segunda célula em absoluta, ela ficou protegida de alterações. Agora você pode arrastá-lo com segurança usando a alça de preenchimento automático. Todos os dados fixos permanecerão os mesmos, independentemente da posição da fórmula, e os dados não confirmados serão alterados de forma flexível. Em todas as células, a receita em rublos descrita nesta linha será dividida pela mesma taxa de câmbio do dólar.

A fórmula em si ficará assim:

=D2/$B$7

Atenção! Indicamos dois cifrões. Dessa forma, mostramos ao programa que tanto a coluna quanto a linha precisam ser corrigidas.

Referências de células em macros

Uma macro é uma sub-rotina que permite automatizar ações. Ao contrário da funcionalidade padrão do Excel, uma macro permite definir imediatamente uma célula específica e executar determinadas ações em apenas algumas linhas de código. Útil para processamento em lote de informações, por exemplo, se não houver como instalar complementos (por exemplo, um computador da empresa é usado, não um pessoal).

Primeiro você precisa entender que o conceito chave de uma macro são objetos que podem conter outros objetos. O objeto Workbooks é responsável pelo livro eletrônico (ou seja, o documento). Inclui o objeto Sheets, que é uma coleção de todas as planilhas de um documento aberto. 

Assim, as células são um objeto Cells. Ele contém todas as células de uma determinada planilha.

Cada objeto é qualificado com argumentos entre parênteses. No caso de células, elas são referenciadas nesta ordem. O número da linha é listado primeiro, seguido pelo número ou letra da coluna (ambos os formatos são aceitáveis).

Por exemplo, uma linha de código contendo uma referência à célula C5 ficaria assim:

Pastas de trabalho(“Livro2.xlsm”).Planilhas(“Lista2”).Células(5, 3)

Pastas de trabalho(“Livro2.xlsm”).Planilhas(“Lista2”).Células(5, “C”)

Você também pode acessar uma célula usando um objeto Arrumado. Em geral, pretende-se dar uma referência a um intervalo (cujos elementos, aliás, também podem ser absolutos ou relativos), mas você pode simplesmente dar um nome de célula, no mesmo formato de um documento do Excel.

Nesse caso, a linha ficará assim.

Pastas de trabalho(“Livro2.xlsm”).Planilhas(“Lista2”).Intervalo(“C5”)

Pode parecer que essa opção seja mais conveniente, mas a vantagem das duas primeiras opções é que você pode usar variáveis ​​entre colchetes e dar um link que não é mais absoluto, mas algo como um relativo, que vai depender dos resultados do os cálculos.

Assim, as macros podem ser efetivamente usadas em programas. Na verdade, todas as referências a células ou intervalos aqui serão absolutas e, portanto, também podem ser corrigidas com elas. É verdade que não é tão conveniente. O uso de macros pode ser útil ao escrever programas complexos com um grande número de etapas no algoritmo. Em geral, a maneira padrão de usar referências absolutas ou relativas é muito mais conveniente. 

Conclusões

Descobrimos o que é uma referência de célula, como funciona, para que serve. Entendemos a diferença entre referências absolutas e relativas e descobrimos o que precisa ser feito para transformar um tipo em outro (em palavras simples, corrigir o endereço ou desafixá-lo). Descobrimos como você pode fazer isso imediatamente com um grande número de valores. Agora você tem a flexibilidade de usar esse recurso nas situações certas.