Links no Excel – absolutos, relativos e mistos. Erros ao trabalhar com links relativos no Excel

Para facilitar os cálculos usando fórmulas no modo automático, são utilizadas referências a células. Dependendo do tipo de escrita, eles são divididos em três tipos principais:

1. Ligações relativas. Usado para cálculos simples. Copiar uma fórmula implica alterar as coordenadas.
2. Ligações absolutas. Se você precisar produzir cálculos mais complexos, esta opção é adequada. Para fixação utilize o símbolo “$”. Exemplo: $A$1.
3. ligações mistas. Este tipo de endereçamento é utilizado em cálculos quando é necessário fixar uma coluna ou linha separadamente. Exemplo: $A1 ou A$1.

Caso seja necessário copiar os dados da fórmula inserida, são utilizadas referências com endereçamento absoluto e misto. O artigo revelará com exemplos como os cálculos são feitos usando vários tipos de links.

Referência de célula relativa no Excel

Este é um conjunto de caracteres que definem a localização de uma célula. Os links no programa são gravados automaticamente com endereçamento relativo. Por exemplo: A1, A2, B1, B2. Mover para uma linha ou coluna diferente altera os caracteres na fórmula. Por exemplo, a posição inicial A1. Movendo-se horizontalmente muda a letra para B1, C1, D1, etc. Da mesma forma, as mudanças ocorrem ao mover ao longo de uma linha vertical, só que neste caso o número muda – A2, A3, A4, etc. Se for necessário duplicar um cálculo do mesmo tipo em uma célula adjacente, um cálculo é realizado usando uma referência relativa. Para usar esse recurso, siga alguns passos:

1. Assim que os dados forem inseridos na célula, mova o cursor e clique com o mouse. O destaque com um retângulo verde indica a ativação da célula e a prontidão para trabalhos futuros.
2. Ao pressionar a combinação de teclas Ctrl + C, copiamos o conteúdo para a área de transferência.
3. Ativamos a célula para a qual você deseja transferir dados ou uma fórmula previamente escrita.
4. Ao pressionar a combinação Ctrl + V, transferimos os dados salvos na área de transferência do sistema.

Conselho de profissional! Para realizar o mesmo tipo de cálculos na tabela, use um truque de vida. Selecione a célula que contém a fórmula inserida anteriormente. Passando o cursor sobre um pequeno quadrado que aparece no canto inferior direito e segurando o botão esquerdo do mouse, arraste para a linha inferior ou coluna extrema, dependendo da ação realizada. Ao soltar o botão do mouse, o cálculo será realizado automaticamente. Essa ferramenta é chamada de marcador de preenchimento automático.

Exemplo de link relativo

Para ficar mais claro, considere um exemplo de cálculo usando uma fórmula com uma referência relativa. Suponha que o dono de uma loja de esportes após um ano de trabalho precise calcular o lucro das vendas.

A ordem das ações:

1. O exemplo mostra que as colunas B e C foram usadas para preencher a quantidade de mercadorias vendidas e seu preço. Assim, para escrever a fórmula e obter a resposta, selecione a coluna D. A fórmula se parece com isso: = B2*C

Preste atenção! Para facilitar o processo de escrever uma fórmula, use um pequeno truque. Coloque o sinal “=”, clique na quantidade de mercadorias vendidas, coloque o sinal “*” e clique no preço do produto. A fórmula após o sinal de igual será escrita automaticamente.

2. Para a resposta final, pressione “Enter”. Em seguida, você precisa calcular o valor total do lucro recebido de outros tipos de produtos. Bem, se o número de linhas não for grande, todas as manipulações poderão ser realizadas manualmente. Para preencher um grande número de linhas ao mesmo tempo no Excel, existe uma função útil que permite transferir a fórmula para outras células.
3. Mova o cursor sobre o canto inferior direito do retângulo com a fórmula ou o resultado final. O aparecimento de uma cruz preta serve como um sinal de que o cursor pode ser arrastado para baixo. Assim, é feito um cálculo automático do lucro recebido para cada produto separadamente.
4. Ao soltar o botão pressionado do mouse, obtemos os resultados corretos em todas as linhas.

Ao clicar na célula D3, você pode ver que as coordenadas da célula foram alteradas automaticamente e agora estão assim: =B3*C3. Segue-se que as ligações eram relativas.

Possíveis erros ao trabalhar com links relativos

Sem dúvida, esta função do Excel simplifica muito os cálculos, mas em alguns casos podem surgir dificuldades. Vamos considerar um exemplo simples de cálculo do coeficiente de lucro de cada item de mercadoria:

1. Crie uma tabela e preencha: A – nome do produto; B – quantidade vendida; C – custo; D é o valor recebido. Suponha que haja apenas 11 itens no sortimento. Portanto, levando em consideração a descrição das colunas, 12 linhas são preenchidas e o valor total do lucro é D
2. Clique na célula E2 e digite =D2 /D13.
3. Após pressionar o botão “Enter”, aparece o coeficiente da participação relativa das vendas do primeiro item.
4. Estique a coluna para baixo e aguarde o resultado. No entanto, o sistema dá o erro “#DIV/0!”

O motivo do erro é o uso de uma referência relativa para os cálculos. Como resultado da cópia da fórmula, as coordenadas mudam. Ou seja, para o E3, a fórmula ficará assim =D3 /D13. Como a célula D13 não está preenchida e teoricamente tem valor zero, o programa dará um erro com a informação de que a divisão por zero é impossível.

Importante! Para corrigir o erro, é necessário escrever a fórmula de forma que as coordenadas D13 sejam fixas. O endereçamento relativo não tem essa funcionalidade. Para fazer isso, existe outro tipo de links – absolutos. 

Como você faz um link absoluto no Excel

Graças ao uso do símbolo $, tornou-se possível fixar as coordenadas das células. Como isso funciona, vamos considerar mais adiante. Como o programa usa endereçamento relativo por padrão, para torná-lo absoluto, você precisará executar várias ações. Vamos analisar a solução para o erro “Como encontrar o coeficiente da venda de vários itens de mercadorias”, realizando o cálculo usando endereçamento absoluto:

1. Clique em E2 e insira as coordenadas do link =D2 /D13. Como o link é relativo, um símbolo deve ser definido para fixar os dados.
2. Fixe as coordenadas da célula D Para realizar esta ação, anteceda a letra que indica a coluna e o número da linha colocando um sinal de “$”.

Conselho de profissional! Para facilitar a tarefa de inserir, basta ativar a célula de edição da fórmula e pressionar a tecla F4 várias vezes. Até obter valores satisfatórios. A fórmula correta é a seguinte: =D2/$D$13.

3. Pressione o botão “Entrar”. Como resultado das ações executadas, o resultado correto deve aparecer.
4. Arraste o marcador para a linha inferior para copiar a fórmula.

Devido ao uso de endereçamento absoluto nos cálculos, os resultados finais nas linhas restantes serão corretos.

Como colocar um link misto no Excel

Para cálculos de fórmulas, não são usadas apenas referências relativas e absolutas, mas também mistas. Sua característica distintiva é que eles fixam uma das coordenadas.

• Por exemplo, para alterar a posição de uma linha, você deve escrever o sinal $ na frente da designação da letra.
• Pelo contrário, se o cifrão for escrito após a designação da letra, os indicadores na linha permanecerão inalterados.

Daí resulta que, para resolver o problema anterior com a determinação do coeficiente de vendas de mercadorias usando endereçamento misto, é necessário fixar o número da linha. Ou seja, o sinal $ é colocado após a letra da coluna, pois suas coordenadas não mudam mesmo em uma referência relativa. Vamos dar um exemplo:

1. Para cálculos precisos, insira =D1/$D$3 e pressione “Entrar”. O programa dá a resposta exata.
2. Para mover a fórmula para as células subsequentes na coluna e obter resultados precisos, arraste a alça para a célula inferior.
3. Como resultado, o programa fornecerá os cálculos corretos.

Atenção! Se você definir o sinal $ na frente da letra, o Excel dará um erro “#DIV/0!”, o que significa que esta operação não pode ser executada.

Endereçamento “SuperAbsoluto”

No final, vamos ver outro exemplo de um link absoluto – endereçamento “SuperAbsolute”. Quais são suas características e diferenças. Pegue o número aproximado 30 e insira-o na célula B2. É esse número que será o principal, é necessário realizar uma série de ações com ele, por exemplo, para elevá-lo a um poder.

1. Para a execução correta de todas as ações, insira a seguinte fórmula na coluna C: =$B$2^$D2. Na coluna D inserimos o valor dos graus.

2. Após pressionar o botão “Enter” e ativar a fórmula, esticamos o marcador pela coluna.
3. Obtemos os resultados corretos.

A conclusão é que todas as ações realizadas se referem a uma célula fixa B2, portanto:

• Copiar uma fórmula da célula C3 para a célula E3, F3 ou H3 não alterará o resultado. Ele permanecerá inalterado – 900.
• Se você precisar inserir uma nova coluna, as coordenadas da célula com a fórmula serão alteradas, mas o resultado também permanecerá inalterado.

Essa é a peculiaridade do link “SuperAbsolute”: se você precisar mover o resultado não mudará. No entanto, existem situações em que os dados são inseridos de fontes de terceiros. Assim, as colunas são deslocadas para o lado e os dados são definidos da maneira antiga na coluna B2. O que acontece nesse caso? Quando misturada, a fórmula muda de acordo com a ação realizada, ou seja, não apontará mais para B2, mas para C2. Mas como a inserção foi feita em B2, o resultado final estará errado.

Referência! Para poder inserir macros de fontes de terceiros, você precisa habilitar as configurações do desenvolvedor (elas estão desabilitadas por padrão). Para fazer isso, vá para Opções, abra as configurações da faixa de opções e marque a caixa na coluna da direita ao lado de “Desenvolvedor”. Depois disso, o acesso a muitas funções que antes estavam ocultas dos olhos do usuário comum será aberto.

Isso levanta a questão: é possível modificar a fórmula da célula C2 para que o número original seja coletado da célula B, apesar da inserção de novas colunas de dados? Para garantir que as alterações na tabela não afetem a determinação do total ao instalar dados de fontes de terceiros, você deve executar as seguintes etapas:

1. Em vez das coordenadas da célula B2, insira os seguintes indicadores: =INDIRETO(“B2”). Como resultado, depois de mover a composição de formulação ficará assim: =INDIRETO(“B2”)^$E2.
2. Graças a esta função, o link sempre aponta para o quadrado com as coordenadas B2, independentemente de serem adicionadas ou removidas colunas na tabela.

Deve ser entendido que uma célula que não contém nenhum dado sempre mostra o valor “0”.

Conclusão

Graças ao uso de três variedades dos links descritos, aparecem muitas oportunidades que facilitam o trabalho com cálculos no Excel. Portanto, antes de começar a trabalhar com fórmulas, leia primeiro os links e as regras para sua instalação.